November 15, 2012 at 1:58 am
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..
November 16, 2012 at 4:17 am
I would go with the single dimension with multiple hierarchies to save processing time,
November 16, 2012 at 1:37 pm
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?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply