OLAP servers tend to return numerical data in two ways. The first way is what referred to as a ‘formatted value’. Formatted values are often returned from the OLAP server as strings and include things like currency symbols, thousand separators or other characters that make numbers easy for people to read. Formatted values are often truncated or rounded off so that large numbers of decimal places don’t clutter the display. The second way OLAP servers return data is as an ‘actual value’. An actual value represents an unformatted, un-truncated/rounded data value. Actual values are returned as native types (integers, decimals, dates).
How Voyager handles formatted and actual values
The default way of displaying numbers in the cross-tab will be to use their formatted values. The following example using the sample Adventure Works cube provided for Microsoft Analysis services demonstrates two different measures selected with different types of formats used to generate the formatted values. One measure has a dollar symbol, comma separators for thousands and is truncated to two decimal places. The other measure is formatted as a percentage to two decimal places.
By going to the properties tab for this particular cross-tab there is an option to switch to displaying the actual values (highlighted in red on the picture below).
Applying this setting shows the raw data in the Voyager cross-tab.
Notice there are no currency symbols, thousand separators and the Ratio to Parent Product is represented as a number rather than a percentage and is not rounded off.
Voyager’s exporting functionality in another place in Voyager where the difference between formatted and actual values needs to be understood. Voyager allows you to export to PDF, Excel and CSV. As exporting to PDF renders a static document where a user can only view and interact with the data the formatted values are exported for both component and data exports.
As Excel and CSV are file formats where the data can be further manipulated and interacted with, for example performing calculations in Excel, the exports to these file formats export the actual values.
Troubleshooting Formatted Values using DatasetViewer
If you experiencing problems with formatted and actual values, for example the formatted value might be displaying a blank, it is best to check what values are being delivered by the OLAP server. For Microsoft Analysis Services this can be done with a tool called DatasetViewer. This was a sample application provided by Microsoft in the MDAC SDK samples versions 2.0 and 2.1. It allows you to manipulate the ODBO interfaces Voyager uses to access data from Microsoft Analysis Services by using a series of menus.
Make a connection
First connect to the data source by choosing the Full Connect option from the DataSource menu.
Now choose the correct provider. There may well be multiple entries that say MSOLAP.
You will need to experiment and find the one correct to your version of Analysis Services. You will then get a connection dialogue.
Make sure the ‘Analysis server’ option is selected if you are connecting to a server. Choose ‘Cube file’ if you are connecting to an offline cube file. Enter the name of the server in the Server section of the dialogue. If you leave the User ID and Password section blank it will authenticate using the current windows user i.e. the credentials of the user running the DatasetViewer application.
Next choose the catalogue you want to work with.
Click Finish and your connection to the data source is complete.
First enter the MDX you want to execute in the text box associated with the connection you created.
Now create an MDX command object by choosing the Session menu, IDBCreateCommand and then CreateCommand.
Now you need to choose to execute this command as a Dataset. On the Command menu, choose ICommand and then Execute Dataset.
The MDX should have now been executed and it will have created a Dataset.
Viewing the Results of the Executed MDX
To view the results of the MDX, choose the Dataset menu, IMDDataset and then GetCellData.
You should now see a table with columns for the both the values and the formatted values for the cells returned by the MDX statement.
Double clicking on one of the rows will give extra information about that particular cell. In particular is shows the cell status and type of value that was returned.