Dimension Hierarchies

Post Reply
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Dimension Hierarchies

Post by jim wood »

Guys,

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,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Dimension Hierarchies

Post 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?

Marcus
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Dimension Hierarchies

Post 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,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply