Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Stars means multiple cubes? Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 12:30 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:50 AM
Points: 229, Visits: 250
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


  Post Attachments 
Modelo Logico_cut.gif (9 views, 20.59 KB)
Post #1488902
Posted Tuesday, August 27, 2013 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 13,046, Visits: 10,815
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).




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488920
Posted Tuesday, August 27, 2013 1:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:06 AM
Points: 1,187, Visits: 1,225
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
Post #1488927
Posted Thursday, August 29, 2013 9:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
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:

http://www.ssas-info.com/VidasMatelisBlog/40_splitting-analysis-services-2005-cubes-based-on-measure-groups


Post #1489776
Posted Thursday, August 29, 2013 12:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:50 AM
Points: 229, Visits: 250
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
Post #1489831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse