Using Smart View to help troubleshoot your Answers query.

When one starts using Essbase as a data source for OBI things can get a little confusing, I have previously offered my own explanation of how an Essbase cube functions and how this related to the star schemas with which OBI developers will be familiar but from time to time it can still be frustrating if the results of your answers query aren’t quite what you were expecting. To get to the bottom of these puzzles it helps to learn how to use the Essbase tools to look at the cubes and their structure, lack of data where one was expecting it is one of the most frustrating things I have found but it is one of the quickest things to verify.

The majority of Essbase users will access their data, read it and even update it through MS Excel, previously this has been through the Essbase Add-in for Excel, but now the tool to use going forward is called Smart View (but it is still an Excel add-in). Smart view should be on the radar for all OBI developers as it likely to replace the BI Office plug-in at some stage.

So, a very basic example demonstrates this:

1. Taking a random selection from a demo Hyperion planning cube in BI Answers.

But this returns no results, this isn’t totally surprising as we have only selected a single measure column, whilst we could just add all the measures to the query to see which return data, it would be better to find out where the data is and what we need to use in query to see it.

2. Launching Smart View is simple, once the add-in is installed, users get a Hyperion tool bar option in Excel, the first tool in the Hyperion menu is the ‘Data Source Manager’, this appears as a floating panel on the right of the Excel window and gives access to all the available sources in the EPM suite.

To access a data source just try and drill into the folder and the login dialogue will open. Drill into the appropriate folder to find the cube you are looking for, right click it and select ad-hoc analysis from the context menu.

3. What you now see is the default view of the data with two dimensions, (Account and Period, both required dimensions and arguably the most important) displayed on the X and Y axes of the spreadsheet, and the other dimensions held in a floating palette.

Without going into too much detail, the total level of each dimension, or a member (value) from within that dimension is required in every Essbase query. In OBI this happens behind the scenes, the BI Server query engine will take our selected ‘columns’ in the query, and in the top level member from the dimensions we haven’t used, and will write an MDX query to issue against the cube. To over simplify it, in Smart View this means that what you see either in spreadsheet cells or in the palette will be used in the query.

There are few simple things to remember to help you find the data you are looking for:

Double clicking a member expands it, exposing the next generation (one can also set it to show all);

These two buttons are a revelation the first time you use them, once you have expanded a dimension, Period for example, but you are only interested in the months Jan – Jun, highlight those cells and click ‘Keep Only’, hey presto all the other members below Period disappear. I’m sure I don’t need to explain what ‘Remove Only’ does;

If you expand a member and then decide you don’t want to see all it’s descendants after all, just double right click on it to collapse it;

To move a dimension from the palette onto the spreadsheet, just drag and drop it;

So having dropped Year for example, we could drill down, see which year value we want and then do a Keep Only or a smarter way to do it, and a more practical way with a larger dimension is to select the members first before dragging them onto the sheet;

Click the drop down arrow next to Year and click the ellipsis below ‘Year’.

This opens the Member Selection dialogue, the style of which should be familiar to most people. Remove Year from the right and side and select the year you want from the left. When you close the dialogue the value FY09 will be displayed in the palette, this can be dragged onto the spreadsheet if you want or if you have selected multiple values.

Once the filters have been set by selecting the appropriate members to match the OBI query filters all that remains is to double click the Accounts dimension member to show all the measure accounts. Sure enough we have to scroll along way across to find a column of data.

So there is the reason we have no results in OBI, there is very little data in the cube, this could be for a number of reasons dependant on the nature of the cube. The final anomaly is the title of the column, a member called 411100, odd as all the measures we have in OBI have alpha character names. The reason for this is that all members in Essbase can have aliases, so for financial accounts numeric codes are usually used as the member name as they are more concise, and the account has an alias which is usually a more verbose name or description. Essbase and Smart View display the member name by default, OBI displays the default alias. To view the alias in Smart View, right click the spreadsheet and choose Hyperion>Ad-Hoc Analysis>Change Alias Table, and select the appropriate alias table.

In this case our figures turn out to be against an account who’s alias is ‘Operating Revenue’.

Altering my Answers query accordingly now returns some data.