Conceptually, it's not a problem, it's just hierarchical data. I wasn't born with the understanding of hierarchical data...I just kept messing around with it.
Anyway, I've run across a little problem. After perusing the usual places for answers, I couldn't find exactly what I was looking for. It's probably due to the fact that I'm still learning the BI lingo (Levels, Dimensions, Hierarchy, etc.). I did find a useful article from Mr. Rittman, but it didn't help me.
As I build and rebuild these dimensions (specifically Time), when I get to a certain level, the records are not rolling up. Here's what it should look like:
Time
-->Total
---->Year
------>Quarter
-------->Month
---------->Week
------------>Day
Easy enough right?
For the purposes of this demonstration, I'm only going to go down to the month level. This is also to keep you somewhat engaged.
Let's start with a screenshot of the Time Dimension in the BMM:
Instead of doing a million screenshots, I'll just explain how they are set up.
I pulled CALENDAR_YEAR and CALENDAR_YEAR_ID into the Year Level. From the Logical Level --> Keys tab, CALENDAR_YEAR is set up as a key with "Use for Drilldown" checked. I create another logical key on CALENDAR_YEAR_ID but I uncheck "Use for Drilldown". I then select CALENDAR_YEAR_ID as the Primary key for the table and also check off the Chronological Key for CALENDAR_YEAR_ID.
I did the exact same thing for the Quarter Level pulling in CALENDAR_QUARTER_DESC and CALENDAR_QUARTER_ID.
I switch over to Answers and run the report.
Now I drill down and I should only have one record.
This is where the trouble starts for me.
I do the exact same thing with the Month Level, pulling in CALENDAR_MONTH_DESC and CALENDAR_MONTH_ID.
When I run the report and click on a Quarter, I get the following:
You might not be able to fully see what it's doing...it's giving me a record for each day in that given month (all 3 quarters are represented). Suppressing the values (Column Format --> Suppress), it does the same thing.
If I remove CALENDAR_MONTH_DESC as a logical key and recheck "Use for Drilldown" on CALENDAR_MONTH_ID, it works just fine.
Only I don't care to have the users see the CALENDAR_MONTH_ID, it's meaningless to them.
When I looked at the SQL being issued to the database, I did notice that the first query (from year to quarter) had a DISTINCT clause and the second (from quarter to month) did not.
I've been beating my head against the wall because I know there is something small I am missing.
Any help would be much appreciated.
Update (12 minutes later)
Check out this one for my resolution.
3 comments:
If you are using an OBI dimension object in the business (logical) layer then you need to make sure that the all of the columns belonging to a level are added to the level - so the month level in the dimension should have both the month key and the month description. By default columns not associated with a level are in the lowest level (day in your case)
Thanks Pete, that's helpful.
I've sort of given up on the documentation for OBIEE and relied mostly on forums and blogs. I shall be updating my dimensions shortly.
chet
i know this type of dim....but if there is fiscal and Calendar year..how to setup dim for fiscal please let me know ASAP i tried but itz getting error...Thanks in advance
Post a Comment