August 27, 2013 at 12:30 pm
My company uses SQL Server 2008 R2 Enterprise Edition.
I received data and a model from another agency and I have to mount the Data Warehouse to provide my company members the BI solution.
In this model there is a constelation with 21 stars. I thought I would create 21 cubes in BIDS and then link them later. But it seems there is no way to do it in SSAS. Every star is related with, at least, another star.
What is the best strategy to create it? Should it be one unique cube with 21 fact tables?
A piece of the model is attached, but the names are in portuguese.
Thanks in advance
________________
DBA Cabuloso
Lucas Benevides
August 27, 2013 at 1:19 pm
You can build 21 data marts in SSAS and still link them through linked measure groups and linked dimensions (for example: Linked Measure Groups). However, I usually advice against these kind of set-ups, as it really complicates everything.
I'd rather built one (or a few more) SSAS cubes. You can logically seperate the model in the data source view using multiple diagrams and you can present parts of the cubes to users with perspectives. A downside is that it will take significantly longer to process the cube, as it contains everything. An upside is that you can reuse dimensions across different facts (called conformed dimensions).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2013 at 1:36 pm
Create a Kimball bus matrix to see where the separate star schemas have shared/conformed dimensions. Then the question becomes how is the data going to be used/analyzed? Will you ever want to relate one set of data against another? My guess is that you won't want 21 separate marts.
HTH,
Rob
August 29, 2013 at 9:25 am
There used to be a "best practice" of 5 to 15 measure groups (fact tables) per cube by the Kimball Group, but that is a very general rule. It has since been backed off by both the Kimball Group and revised by the SQL CAT team.
It really does depend on how you are going to use the data/uptime requirements/number of scripted calculated members/etc, but here is the experience of a user that had 23 fact tables to deal with in 2005:
August 29, 2013 at 12:10 pm
Thank you all,
Now I figure out better how I'm going to model my DW.
It is not the case of using linked measure groups, because the fact tables are in the same database.
And great link about SSAS.
Best Regards
________________
DBA Cabuloso
Lucas Benevides
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply