This discussion is archived
1 Reply Latest reply: Oct 19, 2012 6:39 AM by DavidGreenfield RSS

hierarchy problem in awm

saranya.a Newbie
Currently Being Moderated
Hi,

i have the below hierarchy in my date dimeniosn: year-->quarter-->month-->week-->day.. and my measure is count. i have datas as below:

2012-->Q3-->month 8-->week 1-->01, i have count as 6
2013-->Q3-->month 8-->week 1-->01 , i have count as 7

ie., i have two different years having same quarter,month,week and day. when the cube is populated, i see that am not able to see data under year 2013.

I see that this happens since i have same quarter,month,week getting repeated in both the years. should i have quarter prefixed with year to fix this?? is this the only way??

for example, consider the below simple example with hierarchy: city-->streetname-->doornumber

i have the below data:
chicago-->east-west street-->door 45
sanjose--> east-west street-->door23

when i populate the cube with this data, i should get the proper hierarchy as above in the cube.. but it is not happening since the streetname hierarchy is not unique.

can any one pls help me on this??
  • 1. Re: hierarchy problem in awm
    DavidGreenfield Expert
    Currently Being Moderated
    The member names need to be unique in OLAP. So, for example, you cannot have member "Q3" report up into both 2012 and 2013. The standard solution is to concanate the member names in some way. E.g.

    2012-->2012_Q3-->2012_Q3_month_8-->2012_Q3_month_8_week_1-->2012_Q3_month_8_week_1_01

    There are, of course, many ways to create unique member names and you can certainly do something more elegant than this. But however you do it, the member names must be unique and each unique member name must have only one parent in a hierarchy. The same holds for your geography dimension example.

    There is another solution, which is to have separate YEAR, QUARTER, MONTH, and DAY dimensions instead of a single unified TIME dimension. But you should only do this if you have to since adding three more dimensions to your cube is significantly more expensive that adding unique prefixes to member names. The only reason you would have to do this is if you want to break out your report by the separate levels. e.g. give me the sum of Q3, month 8 data over all days and years. This is not common.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points