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 dimensions from 1 table or 1 dimension with multiple hierarchies Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 1:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 12:12 PM
Points: 72, Visits: 326
Being accustomed to Cognos DMR (ROLAP based technology) I am trying to wrap my head around SSAS.
My pilot consists of a facttable (Cashflow) with just 2 FK's.

FK1 relates to the time dimension. DMR expects a physical calendertable to be used, SSAS allows for generating the time dimension by creating it within SSAS. Very Nice

FK2 relates to 1 dimension table that stores roughly 60 attributes. About half of these attributes allow for grouping by in aggregates. In DMR we build seperate dimensions. Such a dimension consists - for instance - of 2 high level grouping codes to be used as context filters or high level aggregate groups.

I cannot find any best SSAS practice on this..

Should I create 1 single dimension and build numerous hierarchies mimicing the structure we used in Cognos DMR?

Or should I define multiple dimensions against the same dimension table , each with its own hierarchy?

Technically the dimension table stores only natural hierarchies

As DMR is strictly ROLAP generating a SQL against the relational database without storing pre-aggregates both solution would work. I am not sure whether SSAS supports this..
Post #1385008
Posted Friday, November 16, 2012 4:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:11 AM
Points: 237, Visits: 2,665
I would go with the single dimension with multiple hierarchies to save processing time,
Post #1385593
Posted Friday, November 16, 2012 1:37 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 12:12 PM
Points: 72, Visits: 326
I used my time dimension to create 2 different hierarchies, the first consisting of 4 level, the second 3 level. In both cases the toplevel is year. When adding attribute relations 2 relations now converge on the year:

________________--> monthlevel1 --> quarterlevel1
date(key) ________________________________________________ ---> year
________________----------> monthlevel2

No warnings are displayed, but after processing the dimension and browsing the members the 2nd hierarchy makes no sense at all with members randomly missing.

Do I need to add a second year column to avoid the convergion (on to year)
Should I create seperate dimensions each with its own hierarchy?
Post #1385851
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse