Oracle Smart View for Office (Smart View) can be immensely powerful, convenient, and very useful for Hyperion Planning on-premise or Oracle Planning and Budgeting Cloud Service (PBCS) users. Smart View allows end users to retrieve their planning data via an add-on to MS Office for PowerPoint, Word and Excel.
Most end users will use Smart View to see their raw data and drill into it from top to bottom; to add new members; to change parents of dimensions; or to have a user-friendly view of their forms and reports outside of the user interface. This blog focuses on quickly establishing a connection to Smart View and creating an ad hoc report.
Install Smart View
To install Smart View, you need to download the add-on from either on-premise Hyperion Planning or PBCS. I’ve added a screenshot of where to go for each install.
Once Smart View is installed on your machine, the Smart View ribbon will appear when you launch Excel, PowerPoint or Word.
Set Up Options
This is usually a one-time setup, but is important because this connects you to the database and permits you to set preferences that have important effects on your Smart View experience.
Press the “Options” icon. The Options window will open. Select “Advanced” from the left pane:
In the “General” section of the right pane is a field for inputting the connection string to the “Plan Essbase” cube. You will enter your unique URL into the “Shared Connections URL” field (I have blacked out for confidentiality purposes):
Connecting to the Data Source
Once your options are properly set, you can connect to the database from Excel.
- From the Smart View ribbon, press the “Panel” icon:
- The Smart View connection panel will appear on the right side of the Excel worksheet. From the Smart View panel, select “Shared Connections:”
You will be prompted for your on-premise or PBCS user ID and password in a pop-up window. Enter them here and press the “OK” button.
- Expand the “Server Tree” and select an application (ours is called ‘Plan’ in this example):
- At the bottom of the Smart View pane, select “Connect:”
- At the bottom of the Smart View panel, select “Ad hoc analysis:”
- The Excel worksheet will be populated with the dimensions in the database:
Change the Point of View (POV) Selections
To change a selected member in the POV Selector:
- Select the down arrow next to the dimension to change:
- Select the ellipsis (…)
- A selection box will appear:
- Press the Refresh button in the POV selector and the data in the Excel grid will change to match your POV selection:
Move POV Selections to the Grid
You can drag each POV selection to the Excel grid. This is generally a preference to match your work style, but if you’re going to be doing side-by-side analysis (e.g., comparing two scenarios in the Excel grid such as actuals to budget), this is mandatory since the POV selector can hold only one member for the same scenario at a time.
To move the POV selections to the grid:
- Select the down arrow next to the dimension you want to move to the Excel grid:
- Drag to the grid while depressing the mouse button. The dimension member will now be in the grid. Use the basic Excel features to cut-and-paste until you get the report format you want.
This is the report layout and the members in each axis of the report are defined. All reports are defined in the Excel grid by three elements:
- Rows: The x-axis in the Excel grid of the report
- Columns: The y-axis in the Excel grid of the report
- Point-of-View: All the other dimensions that define the context of what shows up in the grid
From this standard ad hoc starting point, you can double-click each of the dimension names to drill in one level (this can be done multiple times all the way to level zero); you can click and highlight the dimension name and select “Member Selection” from the toolbar above; or you can click and highlight the dimension name and simply type in the name of the member you’re looking for.
Below, I double-clicked “Account,” which expanded out to the next level of the hierarchy below the top of the house “Account” member:
Next, I highlighted “Account” and clicked “Member Selection:”
Then I began typing the name of an account (when the name shows up in the box, simply click “Refresh”):
Once you add a member to each of the dimensions (in our example, we had nine dimensions) and refresh, you should be able to see your data and continue to use the double-click or member selection to get to the level of member(s) that you want to see. With a little practice and experimenting with different configurations, you’ll be a Smart View wizard in no time!
Several other features of Smart View are immensely helpful for an end user, but I don’t have enough time in one blog entry to discuss them all. Please feel free to reach out to email@example.com if you have any questions about using Smart View.