January 10, 2011 at 4:01 am
Hi,
I am trying to find out if there is any easy way to display only the hierarchy members that are linked to fact data (ie used).
Currently I have built a cube that has a time dimension with date members between 1998 and 2050. One particular hierarchy however that uses this dimension is Monthly Version Date.
When selecting members from this Monthly Version Date hierarchy, the end user has asked if it is possible to see only the dates that are Monthly Version dates ie the ones actually tied to the fact table rather than seeing a list of all dates between 1998 and 1950 and then having to select the right one.
For example if the data has 2 monthly versions in it with dates 6-Oct-2010 and 11-Nov-2011 the user would like to see the following list of members:
+ 6-Oct-2010
+ 11-Nov-2010
Is it possible in SSAS 2005 to hide unsued members in a specific hierarchy? I have been browsing the internet for possible solutions but cannot find exactly what I am after :w00t:
Any advice on this would be very helpful to get me on the right lines.
Thanks
January 11, 2011 at 6:09 am
I would suggest that you could try tying this particular date dimension to a view. The view would join to the fact table and would therefore act as a filter.
I haven't done this myself, so if you try it, please let me know how it works.
January 14, 2011 at 10:16 am
Thanks for this suggestion. I can see this might work though imagine would incur a bit of a performance hit as the view would need to perform an inner join between DimTime (dimension whose members I would want to restrict) and the fact table.
My understanding is that you could also put together a named set in MDX to restrict members but this is fiddly and the client is not keen on MDX! 🙁
Shame really that there isn't a property you can set on a hierarchy to "hide unused members". Such inbuilt functionality would be very useful for cases such as showing version dates where you are only going to have a few members of the time dimension being used.
My view is that it is easy to create basic cubes but to make them truly sophisticated by adding little fine tweaks to increase useability can start getting very complicated :w00t:
January 17, 2011 at 1:42 am
Alternatively you could have a post process script that updates a flag on the Time dimension which can then be filtered on by using a view. It would save the perforamnce hit when you build the OLAP cube.
It doesnt work if are using a roleplaying time dimension, and have several dates in your fact tables.
And may be exensive if you dont index the date column on large data sets.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 18, 2011 at 2:59 am
Thanks Jason. Unfortunately my time dimension is a role-playing one with about 7 or 8 links all to the same fact table so not sure then this way would work. Useful information to know though to store away for another time. I think the solution of creating a view and using this as a dimension should work and is definitely going to be easier than MDX (which will involve a lot of trial for someone who is not used to it and finds it very fiddly) but it involves some development time (including requiring testing to check for any side effects) so would need to think of cost-versus-benefit to the client. Most likely will just have to accept the limitation of the version date having all members shown and not just the members required :w00t:
Thankis again for all your help
January 18, 2011 at 7:27 am
This would be more of a client tool requirement than a server issue. The server itself is actually aware of what items have data and those that don't.
If you can control your queries, you could include non empty in the MDX to ensure only those items with data are returned. Some client tools (like ye olde PowerPlay from IBM Cognos) allow you to suppress zero's and others still allow you to suppress zero or empty.
Steve.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply