September 19, 2014 at 10:26 am
Hopefully the title is about right - hard to put this into one line.
I have had an SSAS 2012 cube, accessed via MS Report Builder, dropped on me to try and use by a supplier and while I am waiting for training one real issue is floating about - how to extract lists of data from this cube. I will try and set the issue below and what I have carried out so far to try and resolve this.
Alongside using the cubes provided to count and present aggregates I would like to be able to extract list of records so that the data can be dropped into something like Excel for further use. However these lists need to contain "missing" counts as clear records. For example I may want to create a list containing all Reference Numbers, Vehicle Registration Plates and Date of Delivery. While the Reference Number will always exist the Registration Plate and the Date of Delivery may both be non-existent but it is still necessary to bring out the Reference Number with these fields blank. Obviously this is a null in terms of the measures we have as their is no value on the fact table for this (I assume).
The following are attempted solutions so far.
The cubes have some dimensions mapped to "Not Recorded" for blank values in the source database but I have been told that doing this for all values will too much for the server to handle. We have hundreds of dimensions per cube as the user requirement was "everything needs to be accessible" so the size of just one extra field for every dimension I guess is significant.
I have tried to use "Show Nulls" but this means repeats for each Reference Number against each possible Reg Plate and Date in the dimensions. While I can identify the logical values through a measure (a 1 rather than a null) this does not help with the blanks. This also falls over when too many dimensions are dropped in, I suspect due to the limits on memory on the server. As the end users are likely to try and drop in significant numbers of dimensions this will not work.
I have tried to spoof with some MDX but this is was hacking about from the msdn page of Working with Empty Values and it did not work. But that may be my own failings
Are there any little tricks or ideas, either on the query side or on the server side (which I have to get the supplier to deal with), which helps work around this issue and provides the ability get around the "lost" rows issue as this is a major blocker to the update of OLAP in my business. I guess in essence I would like to falsify any dimensions at run time with an extra "Not Recorded" value and count these as a measure so that I can pull back the full table including the spoofed "Not Recorded".
Thanks,
Steven
September 22, 2014 at 2:21 am
You can do this fairly easily in Excel itself.....Connect to the cube and create a pivot table with your required dimensions. Pick an arbitrary measure and then right click on the pivot table and choose "Pivot Table Options". In the "Display" tab, check the boxes for "Show Items with No Data on Rows" and "Show Items with No Data on Columns".
What this does is modify the MDX that is being produced to suppress the NON EMPTY function in the code. http://msdn.microsoft.com/en-us/library/ms146002.aspx
September 22, 2014 at 3:42 am
We unfortunately have no Excel access to the cubes as they are cloud based and behind a significant security layer. I have edited the MDX to include the Non Empty clause, but this leaves the stacking of dimensions with lots of 0 values with no way to identify the records with blanks as there is no logical value to filter on.
I guess the other question on this is what is the right way to store/access this data in a data warehouse to produce these list style reports? Are cubes right or should we look at tabular views or something else entirely.
Thanks for the advice.
September 22, 2014 at 3:56 am
SSAS is really meant for higher-level, aggregated type of work. There are a couple of ways to get to these "lists" and each have their pros and cons....
1) You could create a Drillthrough Action on the cube which will contextually display details of a particular aggregated cell.
2) You could create the things you want to see in a list as properties of an attribute of a dimension so they can be queried or ignored as is needed.
...moving away from SSAS...
3) You could create these reports in SSRS directly from your data warehouse and then link to the reports from the cube using a Report action.
Without knowing your exact setup it's difficult to say which one will be right for you (and I'm sure there are other approaches - these are just the ones I have used personally).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply