I have a requirement to create different hierarchy for time dimension. the primary hierarchy is year, quarter, month, date. Also, I am trying to create alternate drill path which will converge to the lowest level Date, so that business user can directly access the dates without drilling down the primary hierarchy. After generating the categories and cube I am seeing 2 problems :
1. when I am seeing the categories diagram, I see that the dates are generated twice. One for the primary hierarchy(drill down year -> quarter -> month -> dates) and another for the alternate drill path(by dates directly). So, my question is for alternate drill down path , does the transformer generates duplicate categories for convergence level. From the diagram, that's what it is showing. I was expecting the transformer will generate the categories only once and will handle intenally(through pointer or something) when I am creating alternate path. If I create a new dimension with only dates, that will also generate the dates again. Then what is the advantage of creating the alternate drill path over creating new dimension? Please help me to understand this.
2. When I am accessing the cube through analysis studio, under the time dimension folder , I was expecting to see 2 dimensions(by year and by date). But instead I am seeing 'by year'(that's the primary hierarchy. And all the dates generated for the alternate hierarchy's are showing as individual dimensions(3 dots icon instead of category icons). So, I am not sure, what's the wrong?
Thanks in advance for your help.
What you are trying to do is certainly possible; I have a time dim with four drill paths all converging on date (from a time dim table we generated). So for example in part it has something like this (hope this is readable):
ALL_TIME
---------------------------------------------------------
STATE_FISCAL_YEAR | FED_FISCAL_YEAR
MONTH_SHORT_NAME | MONTH_SHORT_NAME
---------------------------------------------------------
DATE
SKEY_TIME_DIM
When the user opens it in say Analysis Studio, they see two hierarchies under the "All Time" dim. Note that the names they see are actually set in the 'diagram' view, for example in the above setup I would set it so they see "By state fiscal year" and "By federal fiscal year" under the "All Time" dim.
Does this help any?
Hi
Yes it does works and helps a lot ...!