Many to Many Relationships
h there,
i have built ssas cube before, using straight forward star schema , works well. faced entering budget values have many - many relationships item dimension. let me try explain
normal cubes sales cube, 1 fact table, , multiple dimensions, customer, item, date, shipping.
my budget fact table holds budget values @ year/month level. have 2 fields in budget table relate item dimension. brand , class 2
in item table, brand @ class 1 level (lower class 2), situation have many brands relate many class 2 fields. fi reverse that, have class 2 has many brands. sales guys wish able build reports combine both levels, either class 2 --> brand or brand --> class 2..
i have setit far can have both fields, 1 one relationship.
my pk in budget table have linked item table class2+brand joined together..
i desperatly looking help.. have looked @ many many dimensions, problem sales measures @ day level , budget measures @ month level.
any ideas or appriciated.
thanks
scotty
scott lancaster - sys admin
".. my pk in budget table have linked item table class2+brand joined together.. " - seems inconsistent earlier statement:
".. my budget fact table holds budget values @ year/month level. have 2 fields in budget table relate item dimension. brand , class 2 .."
is there single budget (fact) table - if so, how class2+brand (without date field) constitute primary key?
anyway, i'll assume meant fk (data examples helpful here) - so, based on understanding, approach comes mind:
- create new "budgetitem" dimension with class2+brand as composite key. can have brand , class2 attributes - , 2 (unnatural) user hierachies can added navigation: class 2 --> brand and brand --> class 2.
- directly relate "budgetitem" dimension budget fact table / measure group
- directly relate existing date dimension to budget fact table / measure group @ month granularity (see bol entry below)
- assuming that the budget fact table / measure group needs related original item dimension, create "intermediate" item measure group item dimension table - measure group has "fact" relation item dimension
- configure new item measure group direct relation "budgetitem" dimension (since includes the class2+brand composite key)
- configure many-to-many relation between the budget fact table / measure group , item dimension, using item measure group intermediate
users want dimension fact data @ different granularity or specificity different purposes. example, sales data reseller or internet sales may recorded each day, whereas sales quota information may exist @ month or quarter level. in these scenarios, users want time dimension different grain or level of detail each of these different fact tables. while define new database dimension time dimension different grain, there easier way analysis services.
by default in analysis services, when dimension used within measure group, grain of data within dimension based on key attribute of dimension. example, when time dimension included within measure group , default grain of time dimension daily, default grain of dimension within measure group daily. many times appropriate, such internet sales , reseller sales measure groups in tutorial. however, when such dimension included in other types of measure groups, such in sales quota or budget measure group, monthly or quarterly grain more appropriate.
to specify grain cube dimension other default grain, modify granularity attribute cube dimension used within particular measure group on dimension usage tab of cube designer.
- deepak
SQL Server > SQL Server Analysis Services
Comments
Post a Comment