March 5, 2012 at 4:09 am
System: SQL Server 2008 R2 / SSAS 2008
Hi All,
I have managed to setup an action that will display fields from the fact and/or the dimension tables. But what I need is for the drillthrough action to fetch and display a large number of additional fields from the relational table(s) that are not available in the fact table. This is a requirement for a only certain group of users (which I know I can control via the security settings).
So far I have been trying to setup an action that does this and have not been very successful! I’ve looked at an umpteen number of tutorials, guides, articles, etc on how to do this but I can’t seem to get my head around the concept. I am very new to SSAS and most of my knowledge is self-thought. What is the best way to achieve this? Is it even possible to do this?
Thanks!
March 5, 2012 at 8:47 pm
The only way to do this using Analysis Services is to include all of the required columns as attributes of a dimension (this may be called a fact dimension or a degenerate dimension depending on who is talking about it). Analysis Services will not allow you to drill through to the underlying relational database (it used to do this in SQL 2000 but that functionality has been removed...I suspect because there were too many occassions when the relational database and the cubes were out of step with each other). You should consider setting the properties on these additioanl attributes so that they are not aggregated. This will stop analysis services building aggregations for these attributes which may improve cube processing time.
You may be able to create an application external to analysis services (e.g. a detail report using Reporting Services) which does what you are after and, hence, avoiding the need for extra details in your cube. It would be up to you to make everything integrate correctly if you choose this path.
March 6, 2012 at 2:48 am
Thanks happycat.
This is more or less the same advice I was given on MSDN. I've managed to get it working the way you've described. At first I had the key column and 4 other columns/attributes but for some reason after I added more columns (~40) I've started getting duplicate errors. I'm not sure how this is happening because there is a key column in the dimension and that does not (and cannot) have a duplicate key. I didn't try amending the aggregation properties, so maybe once I do that the error will disappear. Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply