Lists from dimensions where there is no logical count

Post Reply
stevenfrancis
Posts: 1
Joined: Fri Sep 19, 2014 3:33 pm
OLAP Product: SSAS
Version: SQL Server 2012
Excel Version: 14.07128.5000

Lists from dimensions where there is no logical count

Post by stevenfrancis » Fri Sep 19, 2014 8:05 pm

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

Marcus Scherer
Community Contributor
Posts: 125
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 9.5 10.1 10.2
Excel Version: 2007
Location: Karlsruhe

Re: Lists from dimensions where there is no logical count

Post by Marcus Scherer » Wed Oct 01, 2014 11:21 am

Hi Steven,
I have no direct trick and I've seen your post a bit late but I suppose you might be better off querying the datasource with SQL directly.
If you are using Report Builder you can connect to the relational source as well and present the report to the users. You don't need to query this massive, huge space with your large cubes.
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.
The business reason I don't understand, you don't query any measure? And what serves a list with all available Ref Nos and no further information in case of blank plate/date?
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.
Wouldn't it just be an additional "Singleton" member in the dimension table instead of an extra field? The number of dimensions in the cubes is a separate question...

When I read "lists" I immediately think of reporting on the relational DB with SQL. Maybe you can present the business requirement in more detail so that the need for cubes is more obvious.

HTH, Marcus

Post Reply