After installation, Smart View is automatically enabled for Excel, Word, and PowerPoint. The Hyperion menu appears in the menu bar; in Excel, the Smart View toolbar is displayed by default.

From the Start menu, select All Programs > Microsoft Office > Microsoft Excel 2003. The Office interface for Smart View includes the Hyperion menu, the point of view (POV), and Smart View toolbars. The Hyperion menu is available in Excel, Word, and PowerPoint., but the toolbars are available only in Excel.

If this is your first time accessing Data Source Manager, you must supply the administrator-defined URL to Provider Services. Select Hyperion > Options. The Options dialog box appears.

In the Hyperion Provider Service URL, Default URL text box, enter the URL for Provider Services. The server and port number represent the location where the actual software has been deployed for Provider Services, running on a specific machine. This URL is in the following format:

http://<host>:<port>/aps/SmartView

Example: http://ab-123.hyperion.com:13080/aps/SmartView

The Smart View Data Source Manager panel appears.

Note: If this is not your first time accessing Data Source Manager, simply select Hyperion > Data Source Manager to connect to the default URL.

In the Smart View Data Source Manager panel, click Connect to Provider Services. The list of supported Product Areas appears in the docked panel.

The Data Source Manager displays an expandable tree list of available data sources nested as follows:

Double-click Oracle Essbase to select the data source type for ad hoc analysis.

During any given Smart View session, the first time you access a server, you must provide your user credentials.

Double-click the server name where Demo/Basic and Sample/Basic are installed at your organization.
Note: In this OBE, the sample databases are installed on ajindal2.hyperion.com.

The Connect to Data Source dialog box appears. Enter your username and password for authentication and click Connect.

The connection is successfully established and a list of applications appears.

Note: The Views () icon, located on the Smart View Data source Manager toolbar, allows you to toggle between the following data sources:

In this step, you select the database against which you want to perform ad hoc query analysis. Double-click Demo.

The Basic cube appears. Right-click Basic and select Ad-hoc Analysis from the shortcut menu.

When you retrieve data into an empty worksheet, Essbase returns the top-level data, also known as the default ad hoc grid. The default grid layout is specific to the database outline. For this example, Year and Market (the first and second dimensions in the Basic outline) are the default row and column dimensions, respectively. The other dimensions are represented on the POV toolbar. You can use the top-level data as a starting point to drill down into levels of detailed data.

The following table provides a description of Smart View components:

When the default ad hoc grid appears with the top-level data, you can begin your investigation. You can drill down and pivot your data and use all of Excel's robust data manipulation against your grid. You can select the dimensions and members for the rows and columns of the ad hoc grid. When selected, the grid updates with the new dimension members.

A. Double-click B1 (Market) to drill down into the Market dimension.

B. Double-click B1 (East) to drill down into the Eastern region and select New York, Boston, and Chicago.

Hint: To select multiple cells, press and hold down the Ctrl key and click each cell.

With the Eastern region cells still selected (B1 through D1), click the Keep Only () icon to keep just the selected cells and their corresponding data. The grid should look like this:

You use the Member Selection dialog box to select and change dimension members on your grid. You can filter the list of members and you can view labels or descriptions for dimension members.

A. Select A2 and click the Member Selection () icon. The Member Selection dialog box appears for the member that is currently selected on the grid, namely Year.

B. The Filter drop-drown box allows you to selectively limit the data displayed on your grid. Click the Filter drop-down list and select Children.

C. Click the Select () icon to select the children of Year (the four quarters) and click the Add () icon. The Member Selection dialog box should look like this:

Note: If Year appears in the selection pane, click Year and then click the Remove () icon.

D. Click OK.

E. Click the Refresh () icon to view the data.

The POV menu allows you to add dimensions and attributes to the grid.

Note: You can anchor this menu to create a toolbar.

A. Click Product inside the POV menu and drag it to the grid, placing it on top of New_York.

B. Click the Pivot () icon to move it to the row.

C. With A2 still selected, click the Zoom In () icon to drill into Products for the Eastern region.

A. Right-click any Product member name that contains data and drag it back to the POV menu. This removes Product from the grid.

B. Select the entire Smart View grid.

C. Click the Chart () icon. The Chart Wizard appears. Develop the chart as follows:

D. Click Finish to add the chart to your grid.

Save this workbook as Book1. This workbook will be used in a subsequent lesson.

Creating Smart Slices involves setting Smart Slice data boundaries and setting Smart Slice
preferences. This first series of steps sets Smart Slice data boundaries.

Open a new workbook. In the Data Source Manager, select the Basic data source.

Click the Add () icon.

The Select Alias Table dialog box appears.

Aliases are alternate names for database members. You can perform ad hoc retrievals by using the database member name, which is often a stock number or a product code, or an alias, which can be more descriptive. Each Essbase database to which you connect can contain up to 10 alias tables.

From the Select Alias Table, select Default and click OK.

The Smart Slice Designer interface appears.

The Smart Slice Designer interface allows you to create a logical definition for the selected data source. You can select columns and rows, add filters by selecting family lineage and so forth, add POV members, and set specific options (preferences) for your data.

Drag DESCENDANTS([Product]) from the POV and place it directly on DESCENDANTS([Period]).The grid should look like this:

A. Select the column header C, DESCENDANTS([Market]) and click Market in the POV to choose only the members that you want to see in the columns of your grid. The Member Selection dialog box appears.

B. Select Descendants from the Filter drop-down list.

C. Select Boston, Seattle, Denver, and Houston from the Members pane and click the Add () icon to move the members to the Selection pane.

D. Select DESCENDANTS([Market]) in the Selection pane and click the Remove () icon. The Selection pane should look like this:

E. Click OK.

5.

A. Select the column header B, DESCENDANTS([Period]) and click Period in the POV. The Member Selection dialog box appears.

B. Select Q1, Q2, Q3, and Q4 and click the Add () icon to move the members to the Selection pane. Select DESCENDANTS([Period]) in the Selection pane and click the Remove () icon.

Ensure the Use Descriptions checkbox is selected.

C. Click OK.

A. Select the row header A, DESCENDANTS([Product]) and click Product in the POV. The Member Selection dialog box appears.

B. Select Audio and Visual and click the Add () icon to move the members to the Selection pane. Select DESCENDANTS([Product]) in the Selection pane and click the Remove () icon.

Ensure the Use Descriptions checkbox is selected.

C. Click OK. The grid appears with the filtered members.

Note: If you want to add a specific Account, or Scenario to your grid, you can select it from the POV by clicking the drop-down list and then the ellipsis (...) to launch the Member Selection dialog box .

Next, you change a preference for your data. The preferences that you specify are stored as part of the Smart Slice definition, and they override the global preferences set in the Options dialog box.

Click Options on the POV.

The Preferences dialog box appears.

There are two types of user preferences: ad hoc and display. The ad hoc options control how data is retrieved into the spreadsheet and which data is retrieved in ad hoc queries. The display options control how data is displayed in the spreadsheet. Additionally, you can also set cell styles. Each of the Smart View Data Source Providers have a set of default cell styles to identify cell types. You can modify default styles in Smart View and change cell styles by selecting different fonts, background colors, or border colors.

Select #Missing for the missingLabelText val and enter Null. This replaces the literal #Missing with the literal Null when a Null value is encountered in a cell.

Click OK.

Click the Done () icon to save your Smart Slice. The Member Selection dialog box appears. Once again, you can change the options, select other members, and so forth.

Click OK and then enter the name TopCities for your Smart Slice.

Click the OK () icon to save the Smart Slice and refresh the Data Source Manager. Your Smart Slice appears in the tree associated with the Basic data source.

This Smart Slice is now available for use by the end user.

Open a new workbook. Right-click your Smart Slice and select Ad-hoc Analysis. Your query appears and contains precisely the members specified in the Smart Slice.

Next, you use the Smart Slice to create a report.

Ad hoc and Report Designer objects cannot be added to the same worksheet. Open a new workbook.

With the Smart Slice still selected, click the drop-down list for the Insert Into Report () icon and select Query. The Report Designer pane appears with a query named exactly the same as your Smart Slice.
Note: You can also select Subquery to create a report based on a subset of your data.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your workbook.

B. Click the Refresh () icon to view your data. The refreshed workbook appears.

Hint: You can use the Expand All () icon to see each object within the Report Designer pane.

You can create separate reports for any or all of the members of one dimension in a report based on a Smart Slice query in Report Designer or an ad hoc grid, and cascade these reports separately across the worksheets of an Excel workbook. For reports created in Report Designer, you can also cascade reports across slides in a PowerPoint presentation (worksheets or slides are created as needed to accommodate all reports).

A. Cascading reports allow you to showcase your data on separate worksheets within the workbook. Click the Cascade Report Across the Workbook () icon. The Member Selection dialog box appears. Select the following detail:

The Member Selection dialog box should look like this:

B. Click OK. A new workbook is created with three separate worksheets to reflect each of the member selections: Profit, Margin, and COGS. Additionally, the Report Designer pane details your changes.

Queries inserted in Report Designer cannot be modified. However, you can control the POV of a query in any of its reports that display on a sheet by using a slider. The slider displays a selected set of dimension members from a query; when you drag the slider marker to a member, its data appears in all reports associated with the query on the sheet. Sliders can contain dimensions from more than one query in Report Designer if the dimensions have the same boundaries.

A. Open PowerPoint. In Normal View, from the Slides tab, right-click and select Slide Layout.

B. From the Content Layouts pane, select the Blank layout.

The new layout appears. Close the Slide Layout pane.

Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the TopCities Smart Slice.

Select the TopCities Smart Slice. Right-click and select Insert Query Into Report.

The Report Designer pane appears.

A. In Report Designer, click the drop-down list for the Insert () icon and select Function Grid. The grid is added to your slide.

B. Select Hyperion > Refresh. The Function Grid appears with the cells refreshed.

You can add formatting, include a title, reposition the object, and so forth, using PowerPoint's capabilities.

Note: If you select a cell, detail about that member appears in a callout.

A. Once again within the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your slide.

B. Select Hyperion > Refresh. The Chart appears.

Finally, add a slider by using Report Designer. As mentioned in step 1, the slider displays a selected set of dimension members from a query. When you drag the slider marker to a member, the data appears in all reports associated with the query on the sheet.

A. Click the drop-down list for the Insert () icon and select Slider. The Member Selection dialog box appears. Select the following detail:

The Member Selection dialog box should look like this:

B. Click OK. A slider is added.

As you move the slider, the member changes and the associated data in the graph and grid are updated to reflect the new member values.

This step focuses on copying a grid from Excel into Word. Open Book1 created in the first lesson.

Select the grid as in step 5 B , Creating a Smart View Query in Excel.

A. From the Hyperion menu in Excel, select Copy Data Points.

B. Open Word and, from the Hyperion menu within Word, select Paste Data Points.

When prompted to connect to a data source, enter your username and password for authentication.

Your blank document appears with the requested data points. Your Word document should look like this:

From the Hyperion menu, select Refresh. Your Word document now contains dynamically linked data.

You can use Word's powerful formatting capability to create a more visually appealing report.

After pasting Excel data points into Word or PowerPoint, you can use the POV Manager to
change the POV, enabling you to view a different slice of data. From the Hyperion menu, select POV Manager.

The POV Manager dialog box appears.

A. Click the connection to view the data source connection information and active POV members.

The POV Manager refreshes with connection and member detail.

B. Double-click the connection to view the Member Selection list.

Ensure that Accounts is selected in the Dimension drop-down list. Select Descendants from the Filter drop-down list.

A. Select Cost_of_Goods_Sold in the Members pane.

B. Click the Add () icon. The Member Selection pane should look like this:

C. Click OK. The POV Manager appears.

D. Click Close.

From the Hyperion menu, select Refresh to see the cost of goods sold for each quarter for cities in the Eastern region.

Close Word.

A. In the Smart View Data Source Manager panel, double-click Oracle BI Server to select the data source type. The list of servers appears.

Note: If prompted to connect to a data source, enter your username and password for authentication.

B. Double-click OBIEE and then double-click the Sample Sales Reduced application.

C. Select the Sample Sales Reduced database.

D. Click the Add () icon.

The Smart Slice Designer appears with the default ad hoc grid and top-level data.

In this step, you begin to create a Customer query. Choose the appropriate time frame.

A. Drag DESCENDANTS([Time].[[Year]) from the POV to B2.

The dimension is added to the grid.

B. Right-click and drag B2, DESCENDANTS([Time].[[Day Date]) from the grid to the POV. DESCENDANTS([Time].[[Day Date]) is removed from the grid.

Next, choose the Revenue detail that you wish to see in the report.

A. Click B1, DESCENDANTS([MEASURES].[[ALL]). The Member Selection dialog box appears.

Select the Use Descriptions check box.

B. In the Members panel, select the following and add the members to the Selection pane:

	[Facts Revenue].[Year To Date Revenue]
	[Facts Revenue].[Revenue]
	[Facts Revenue].[Year ago YTD Revenue]
	[Facts Other].[Inventory Total]

Remove DESCENDANTS([MEASURES].[[ALL]) from the Selection pane. The Member Selection dialog box should look like this:

C. Click OK. The grid appears.

A. Click the Done () icon to save your Smart Slice. The Member Selection dialog box appears.

B. Click OK.

C. Name the Smart Slice Customer Detail and click the OK () icon to save the Smart Slice and refresh the Data Source Manager. Your Smart Slice appears in the tree associated with the Sample Sales Reduced data source.

This Smart Slice is now available for use by the end user.

A. Right-click Customer Detail and select Insert Query Into Report. The Report Designer pane appears.

B. Click the Insert () icon and select Function Grid. The grid is added to your workbook.

C. Select Hyperion > Refresh to view your data. The refreshed workbook appears.

Using Excel formatting, you can resize the columns, set text wrap on, and so forth to create a more appealing report. This is a sample of what the formatted report might look like:

Hint: To change #Missing to NULL, select Hyperion > Options. On the Display tab, in the #NoData/Missing Label text box, enter NULL. This is a global user preference.

Select the Customer Detail Smart Slice that you just created. Right-click and select Insert Subquery Into Report. Your ad hoc query contains only those members from within your Smart Slice.

Subqueries allow you to create a report based on a subset of your data.

In the POV, select [Time].[[Year]

The Member Selection dialog box appears.

A. In the Member Selection dialog box, make the following selections:

The Member Selection dialog box should look like this:

B. Click OK. A subset of the Time dimension is added.

A. In the POV, select [MEASURES].[ALL].

The Member Selection dialog box appears.

B. In the Member Selection dialog box, make the following selections:

The Member Selection dialog box should look like this:

C. Click OK. A subset of the Measures dimension is added.

Click the Done () icon to save your subquery. The Member Selection dialog box appears.

Click OK. In the Enter New Name text box, enter Customer Revenue and click the OK () icon to save the subquery. The subquery appears in the Report Designer pane.

A. Select A6—this specifies the placement of the object to be added. In the Report Designer pane, click the drop-down list for the Insert () icon and select Chart. The chart is added to your workbook.

B. Select Hyperion > Refresh to view your data. The refreshed workbook appears and features only those members selected to participate in this subquery.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Slider. The slider is always placed in the first cell within Excel.

The Member Selection dialog box appears.

In the Member Selection dialog box, make the following selections:

The Member Selection dialog box should look like this:

B. Click OK. The slider is added.

As exhibited in the previous topic, when you move the control on the slider, the member data in the chart automatically updates.

C. In the Report Designer pane, click the Expand All () icon to view the objects added to the subquery.

A. Open PowerPoint. In Normal View, from the Slides tab, right-click and select Slide Layout.

B. From the Content Layouts pane, select the Title Only layout.

The new layout appears. Close the Slide Layout pane.

C. Click the placeholder for the title on the slide and enter Customer Qtr Revenue for 2008.

A. Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the Customer Detail Smart Slice.

B. Select the Customer Detail Smart Slice.

C. In the Smart View Data Source Manager pane, click the drop-down list for the Insert Into Report () icon and select Query. The Report Designer pane appears.

A. In Report Designer, click the drop-down list for the Insert () icon and select Chart. The chart is added to your slide.

B. Select Hyperion > Refresh. The chart refreshes.

Click the Cascade Report Across the Workbook () icon. The Member Selection dialog box appears. Select the following detail:

The Member Selection dialog box should look like this:

B. Click OK. A new presentation is created with four separate slides (one for each quarter) to reflect each of the member selections.

Additionally, the Report Designer pane details your changes.

Open Word.

A. Select Hyperion > Data Source Manager > Connect to Provider Services to establish a connection and navigate to the Customer Detail Smart Slice.

B. Select the Customer Detail Smart Slice.

C. In the Smart View Data Source Manager pane, click the drop-down list for the Insert Into Report () icon and select Subquery. Hyperion automatically opens Excel, enabling you to create a new subquery.

A. In the POV, select [Time].[[Year].

The Member Selection dialog box appears. Make the following selections:

The Member Selection dialog box should look like this:

B. Click OK.

A. In the POV, select [MEASURES].[ALL].

The Member Selection dialog box appears. Make the following selections:

The Member Selection dialog box should look like this:

B. Click OK.

Click the Done () icon to save your subquery. The Member Selection dialog box appears.

Click OK. Word reappears.

In the Enter New Name text box, enter Customer Revenue for 2007 and 2008 and click the OK () icon to save the subquery. The subquery appears in the Report Designer pane.

A. In the Report Designer pane, click the drop-down list for the Insert () icon and select Function Grid. The grid is added to your workbook.

B. Select Hyperion > Refresh to view your data. The refreshed document appears.

You can now format the report to suit your requirements.