Aggregate Aware Snowflake Dimension

building semantic layer
1

Hi

I would like some advice on how best to model the following scenario with only creating one dimension.

I have the following denormalised Dimension
Product > Product Franchise > Product Franchise Group

The Franchise Group ID and Desc exists on all three of the tables.

I have two fact tables:

FactActualSales is at the Product level which has a join to the Product
table

FactBudgetSales is at the Product Franchise Group level with a join straight to the Product Franchise Group table

I have created
[list]
a context for each of the fact tables
a dimension in the universe called Franchise Group that is based on the Franchise Group Desc column from the Product Franchise Group table
[/list]
As expected when I create a query with the actual sales amount and Franchise Group objects the SQL generated joins through the denormalised dim tables up to the Product>Product Franchise>Product Franchise Group.

And the budget query joins directly to the Product Franchise Group table.

My question is (finally) is there a way that I can optimise the Actual Sales query to pick up the Franchise Group Desc column from the Product table without having to join through the snowflaked tables? The goal is to only have one Franchise Group Dimension object in the universe.

I have been trying to implement this using the aggregate aware function on the Franchise Group Desc object but can not get it to work as expected.

Any suggestions would be greatly appreciated.

Thanks
Kevin

keviwilso (BOB member since 2010-07-01)

2

When you set up Aggregate_Aware, did you also remember to set up “Aggregate Navigation”?

I always forget to do that myself.

The general idea is that you’re telling the system, “When I use object X, do not allow the table Y into the query path”. That will force your SQL to seek out alternate joins/contexts to come up with the SQL it needs using your aggregate aware defined object list.

In your example, you’d set up aggregate navigation to exclude access to the other two tables when pulling in your sales amount object. (The one downside to this approach, is it will always disallow the table. So you might get seperate sql statements if/when you ever combine that with other objects from the other table)

JPetlev (BOB member since 2006-11-01)