This content has been marked as final. Show 8 replies
I think that this is not a bug. There is an incompatibility in design which prevents you from using the same attribute differently for both hierarchies.
NOTE: Unlike parent relationship which depends on <dimension, dimension hierarchy>, Dimension Attribute is dependent on <dimension> alone, not dependent on <dimension, dimension hierarchy> combination. Hence it can only take on 1 value for 1 dimension member.
I think that the time dimension only appears to allow this. The key thing to check is for Time Dimension members which are common to both the hierarchies. Only one of the mappings will take effect (usually the hierarchy which is loaded last will remain in the aw/usable for queries, reports.. it would have over-written the earlier attribute value loaded as per the earlier hierarchy load).
Visualize a dimension as a long list of members which are built up contiguously on a per hierarchy, per level process using the mapping information saved. Once a member is defined (created) via Hierarchy A, it wont be created once again while loading Hierarchy B but is instead updated or redefined based on Hierarchy B's mapping info.
Assuming the dimension load attempts to load Hierarchy A first and then Hierarchy B,
* Dimension load for Hierarchy A will define the various members using MIN_ID and set the END_DATE attribute to value=MIN_END_DATE
* Dimension load for Hierarchy B will re-define the various members using MIN_ID and re-set or over-write the END_DATE attribute to value=SESS_END_DATE
* In this case, it looks like all members are common for both hierarchies (as both members are mapped to same column MIN_ID) and you would end up with END_DATE=SESS_END_DATE.
Actually whether all members are common to both hierarchies or not depends on the quality of data in your snowflake/star table: if parent level for Hierarchy A as well as Hierarchy B is setup fine then the members will be same set (overlapping in whole). If some rows for MIN_ID have parent column for Hierarchy A setup correctly but parent column for Hierarchy B =null or invalid value then that member will exist in Hierarchy A alone and would contain END_DATE=MIN_END_DATE as the corresponding update along Hierarchy B would fail due to hierarchy data quality issues (join from current level to parent level).
As regards a solution to your problem, you should not use the same attribute "SORT" for dual purpose (both hierarchies). Instead define attributes SORT_A and SORT_B and make them enabled for Hierarchy A, Hierarchy B respectively and map/use them appropriately in your reports.
Thank you for the detailed response. Upon further investigation I have determined that only one set of attributes is being loaded (as you described).
What I do need is the ability to have one hierarchy in a different order than the other for functions such as sum or average.
This is a time dimension (hour, minute). One must start at 5PM and loop around (past midnight), while the other must start at midnight. With a user dimension this isn't a problem since I can add an additional level which causes my 5PM start hierarchy to be ordered correctly, but this level appears to be ignored when using a time dimension since only end date seems to be used for any cumulative style functions.
Any suggestions here other than having two separate dimensions?
This is not supported in the AWM UI, but each hierarchy can have a different sort specification. If you specify a "sort attribute" for the dimension through AWM, then the XML for the dimension should have an entry something like this
The CustomOrder is nested inside the <StandardDimension> element, assuming it is not a time dimension. You can also put a CustomOrder inside each <LevelHierarchy> element, which means that you can have a different sort attribute per hierarchy. E.g.
<StandardDimension ... Name="MY_DIM"> ... other elements ... <CustomOrder> <![CDATA[ORDER BY MY_DIM.MY_SORT_ATTR ASC NULLS FIRST ]]> </CustomOrder> </StandardDimension>
Try editing the XML by hand in this way and then recreate the dimension. I would remove the CustomOrder from the StandardDimension element in this case. You will not see any sign of this ordering in AWM, but it should take effect during the dimension compile.
<Hierarchy> <LevelHierarchy ... Name = "H1"> ... other elements ... <CustomOrder> <![CDATA[ORDER BY MY_DIM.MY_H1_SORT_ATTR ASC NULLS FIRST ]]> </CustomOrder> </LevelHierarchy> </Hierarchy> <Hierarchy> <LevelHierarchy ... Name = "H2"> ... other elements ... <CustomOrder> <![CDATA[ORDER BY MY_DIM.MY_H2_SORT_ATTR ASC NULLS FIRST ]]> </CustomOrder> </LevelHierarchy> </Hierarchy>
Nice trick David.
Can this be leveraged in a Time Dimension? I need to cumulative sum based on the order of this sort attribute, basically my reasoning before for having different END_DATE's based on hierarchy.
My trick should let you modify the value in the HIER_ORDER column in the SQL VIEW for any dimension, including a time dimension. But the time based calcs are based on the END_DATE attribute, not the hierarchy or dimension order as specified by the CustomOrder trick (or the sort attribute in AWM).
Does your time dimension also include a DAY level, or is that part of a second time dimension? If it does include DAY, then you could have two variants of the day level -- DAY_FROM_5PM and DAY_FROM_MIDNIGHT with different hours as children.
I have two "Time" dimensions. So the short answer is that my Time dimension does not include a Day level.
D1 is a Date Dimension with Levels Year, Quarter, Month, Week, Day
D2 is a Time Dimension with Levels Hours and Minutes.
Up until now D2 has been configured as a Standard User dimension. It has recently been changed to a Time Dimension since I do not see how one can perform any analytical style functions without the use of an actual Time Type dimension.
D2 is composed of two hierarchies.
H2 effectively goes from 12:00AM - 11:59PM while H1 goes from 5:00PM - 4:59PM. As stated earlier, what I need is the ability to perform my calculations in the same order as my dimension would be. Given that these time based calculations are tied to END_DATE and not hierarchical order are there any other options aside from splitting up H1 and H2 into two separate Time Dimensions with single hierarchies?
Your description of the dimension structure is very clear, but I have a question about requirements. When you say you want the time to run from 5:00PM to 4:59PM, do you mean that the calculation should spand day? e.g. "5:00PM:1 Jan 2012" to "4:59PM:2 Jan 2012". If so, then you cannot do this by looping only the hour-seconds dimension. Or do you really mean that it should loop around the same day. e.g. "5:00PM:1 Jan 2012" to "11:59PM:1 Jan 2012", then back to "00:00AM:1Jan 2012" to "4:59PM:1 Jan 2012" ?
If you want the calculation to span days, then I think you will need to move the HOUR level onto the DATE dimension. You can leave MINUTES in the second dimension, which can remain a standard user dimension. You would then need two different DAY levels (with different members) in the date dimension -- each with a different set of children. You can set the TIME_SPAN of the HOUR level to 1.
The data in it's raw form would start on January 1st 5PM and finish on January 2nd at 4:59PM. My concept of day always spans two calendar days. But reality is that I consider them a single day/session.
So calculations need to span across a physical day. What I have done to get around this is basically shift the day on my facts to be the next day on hours between 5PM and 11:59PM.
So time between January 1st 5PM - 11:59PM EST is shifted to January 2nd. The date with times from 12:00AM EST to 4:59PM EST is left untouched i.e. it remains on the January 2nd slice. This effectively puts all time on the same date slice. I have one cube which works this way and it appears that the calculations work just fine like this.
I have a second cube, which acts like the raw data i.e. no shifting, and it uses a second hierarchy from my time dimension which leverages your hierarchy sorting trick. No calculations are done here at the hour/min in this cube, but data can still be drilled into and it appears in the right order. So one hierarchy sorts time in the order of 5PM - 4:59PM, while the other sorts time from 12AM - 11:59PM. One cube shifts dates to put data on the same date slice, one does not shift.
Regarding your suggestions of moving hours into Date. Would performance take a hit on this?