Dimension Hierarchies

Post Reply
User avatar
jim wood
Site Admin
Posts: 3505
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Dimension Hierarchies

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

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 7 64-bit. TM1 Version: 10.1.1

Marcus Scherer
Community Contributor
Posts: 125
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 9.5 10.1 10.2
Excel Version: 2007
Location: Karlsruhe

Re: Dimension Hierarchies

Post by Marcus Scherer » Thu Oct 28, 2010 6:31 pm

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: 3505
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dimension Hierarchies

Post by jim wood » Fri Oct 29, 2010 7:45 am

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 7 64-bit. TM1 Version: 10.1.1

Post Reply