I have built some cubes for one of two companies owned by a parent company. The execs at the parent company like what they see. They want the same thing done for the second company. They would also like to see a rolled up view of the data (sales, ytd sales by product group, material cost...).
Should I:
Build separate fact tables and cubes. If so how best to roll up the data. I would have to combine sales for identical product groups etc.
Another idea I had was to include an entity_id column in the fact tables and keep everything together.
My feeling is that the entity_id would be easy but some columns will be missing data for one of the company and other columns may be empty for the other company.
There are certainly issues with either method (security, performance, maintenance) but I would like to hear what your thoughts are.
By the way, the parent company just purchased a third company so I that company will eventually be included.
Thanks,
Chris
This is not an SSAS2005 issue but since I cannot find a data warehouse modeling group I can give you some general advice:
Have a look here: http://www.kimballgroup.com/ for design issues. Most of the content is free and of good quality.
A company dimension is recommended. Be careful, though, with internal sales, between the companies in the group, and how this is handled.
Normally the parent company would like a consolidated view of their business and this means, at least, common dimensions and common fact tables.
Kind Regards
Thomas Ivarsson
No comments:
Post a Comment