Hierarchy drill down

Hesh

Hi,

OBIEE stand alone 12C (12.2.1.3.0)

One of my Dimension Hierarchy is not drilling properly.. its skipping few levels and going directly navigating to the end of the hierarchy.

Surprisingly, its navigating to the right column when we click again!

Oracle suggest to redeploy RPD, but its happening frequently and that suggestion is going nowhere!

The drill is going directly from arrow marks mentioned..

Thanks

Hesh

Robert Angel

Have you double checked that all of the keys are correct in the hierarchy and ensure uniqueness and correct level of granularity?

Robert Angel

Also, if this is an intermittent issue; -

can you recreate when you bypass cache?

have you checked the physical data in the table is correct for the key at that level?

Joel

+1 to @Robert Angel

SeeTime hierarrchy issue which is a similar issue to yours I believe.

Hesh

Thanks Robert, Joel

May be you are correct about the data in the table, because, this drill works correctly on one day and it broke on the next day! and again works fine on the next day!

can you please suggest on what would be the best way to check the data in the table? kind of lost here!

Regards

Hesh

Joel

check your levels. check what you're using as the key at each level. check that this value uniquely identifies a "point" in your hierarchy. As mentioned in Time hierarrchy issue , using value 1 as a key for the 1st Quarter of 2015 is unique. 1st quarter of 2016 would also be 1 so make it unique - 2015Q1, 2015Q2.

Robert Angel

Hi,

I would run a select against the underlying physical table,

select distinct year, quarter

from your_table

and

select

distinct year, quarter, quarter_key

from your_table

Substitute your field and table names, if the first result gives the same number of rows as the second then all is okay, if not then your key is out of synch to the granularity.

Hesh

Thanks Joel ,

From RPD what I can find is..

The column 'BXYZ' from where the drill is behaving like this is derived from Four physical tables/table aliases (two tables).

First two are from Master table and the next two are from Detail table. 

So I need to check this 'BXYZ' column is unique at that level in both Master and Detail table? if not that should be an issue?

Regards

Hesh

Robert Angel

Hi,

if your time dimension is based on a detail level folder then you have bigger issues, the time hierarchy is central to so many queries and making it summarise a detail table everytime any analysis (who queries without dates!) runs will be a big impact on performance.

You don't need to check your fact folder, only that the relationship between year / quarter combinations and quarter key used in your hierarchy are 1:1.

Joel

Hi @Robert Angel. This is a different thread - the other was (Time hierarrchy issue ) and thankfully this isn't a time dimension!

Robert Angel

Sorry - too many hierarchies in one day!!