OBIEE: Dimension (Hierarchy)
In relation to the previous post on
Report Drilldown(s), I've been playing with the Dimensions to get a really good idea of how they affect drilling capabilities.
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------------>DayEasy 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.
Labels: obiee, oradb, wtf
