Page 1 of 1

Dimension Hierarchies

Posted: Thu Oct 28, 2010 1:53 pm
by jim wood

We have a product hierarchy that goes like this:

Division, department, category, sub category, range, line, option

We have a logical dimension table that has 2 sources. One source table only goes down to the category level and the other contains the whole hierarchy. What we would like it to do is use the summary table until you go below category level. This sodesn't seem to happen. We have checked the log and if you extract Division in answers OBIEE still queries the detail table.

We have tried making the summary table the number one source but it doesn't help. It only works when you extract Division with Fact information.

This is causing us an issue with prompts which are taking too long as they are querying the detail table.

Does anybosy know how we can force OBI to use the summary table at the top levels?

(We have also looked in to forcing OBI to always use the summary table at certain levels but this doesn't work. The summary table links on a category code and the detail table links on a option code. If detailed fact tables tried to join to the summary table it wouldn't work.)

Thanks in advance,


Re: Dimension Hierarchies

Posted: Thu Oct 28, 2010 6:31 pm
by Marcus Scherer
Hi Jim,

I don't know if it is possible to force OBI to use one of the tables. Smart OBI should use the detail table automatically if there is no information from the detail table needed for the query. This worked for me in the past. It seems that your query requires information from the detail table. Is it because of the prompt? Which dimension(s) are you prompting? Did you try a static filter in the first run and check the generated SQL?


Re: Dimension Hierarchies

Posted: Fri Oct 29, 2010 7:45 am
by jim wood
Hi Marcus,

The queries work well. When we extract category (Fact) level information it joins to the category xref (dim) table which is the product summary level table. When we extract (Fact) detail it joins to the (dim) detail. The issue we have is that in both answers and in prompts when we extract or prompt product category it always goes to the detail table instead of using the category xref summary table. I have checked the rpd, both are setup as a source and the xref table is set as the top source. My only nagging doubt is that it is a dimension and as such it has a hierarchy with obviously the detailed table being set as the detail level. I was wndering if adding a different summary hierarchy would change things? This however might make drilling on product more confussing for the users,