December 2, 2014 at 2:25 pm
Hi,
I'm very new to SSAS, and cubes in general, and have a question.
We are working on the early stages of a reporting solution for Dynamics AX, and a key piece of that is allowing certain users to connect to cubes from Excel and create their own analyses. I currently have a product dimension that has 10-20 attributes in it, which is used in the sales cube. So far everything has gone smoothly. Now I have a requirement to allow for reporting against product master data from within Excel, meaning the user would like to be able to include anything from the product master record in their analyses. This is a much larger set of columns... probably at least 100+.
I'm wondering what the best practice would be for handling this requirement in a cube. On the Oracle side of our business, we don't use OLAP, so simply enhancing the product dimension to contain all the attributes would be an easy choice. But I'm curious if I'm setting us up for future performance issues doing this in a cube?
My initial ideas were:
1.) Enhance the product dimension to add all necessary attributes. Since it exists in the sales cube and elsewhere, I'm questioning if space and performance could be an issue.
-or-
2.) Create a secondary product master dimension to house the attributes only needed for simple master data reporting. That could then be put in a new master data cube. My thinking there is that we then don't have all of the "extra" columns in other cubes like Sales which will likely become large over time. But doing this is creating a silo that could cause issues in the future when a user wants an attribute from the secondary dimension in a cube that only has the primary dimension.
I'd lean towards #1 assuming performance would be adequate.
Is there a best practice around how you experts would handle this requirement?
Am I right to assume that every additional column I add to the dimension will increase the size of and slow down the processing time of a cube?
I appreciate any advice you can give.
Thanks,
Tony
December 3, 2014 at 2:40 am
Hi Tony,
This is a good question and the answer is.......it all depends 🙂
If you go with your first solution, yes it would affect processing and size and you'd be best keeping them as properties of attributes or placing them in their own display folder (so as not to destroy a coherent UI for the users) (there's a useful article on attribute properties here: http://www.bidn.com/blogs/DustinRyan/bidn-blog/2087/defining-member-properties-for-an-attribute-in-ssas-2008).
If you go with your second solution (which I would lean towards, what with 100+ columns) then you could store this separate dimension as ROLAP. This would mitigate the processing/storage issue at the sacrifice of query performance. It all depends how many of these "certain" users there are and how often they are planning to run queries against this data.
Another thing that you might consider is that OLAP may not be the best technology for this. Cubes are designed for aggregation, if you are displaying a product column for, say, serial number then what analytical gain can be had? Perhaps if these users want to see things at certain grains it would be better to create a report and then define a reporting action in the cube so they can see this detail if they need to? That way it keeps them using excel to do initial analysis and then can show them more detail if they need it. This would solve both problems of cube processing/size and query performance. At this point I suppose it would also be worth mentioning drillthrough actions which would give you this functionality but also give you the problems in solutions 1 and 2.
So yeah, it depends.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply