Reference dimensions are creating too many joins

I have orders fact; customer, location and age dimensions. I am using age and location as reference dimensions through customer dimension. If I don't use reference dimension cube processing SQL would be like following

select * from factOrders

If I use reference dimensions SQL would be like following

select * from factOrders, dimCustomer as customer1, dimCustomer as customer2

where factOrders.customerid = customer1.customerid and

factorders.customerid = customer2.customerid

If I have 6 reference dimensions through customer there will be 6 joins. Is there any way I can eliminate these joins as I need only one join to get all the information?

I can create a view between factOrders and dimCustomer and use the reference dimensions as regular dimensions which will avoid all the joins but I feel I am not using the intutive features.

Processing time is significantly higher if I use reference dimensions compared to using as a view.

We had the same problem. We resolved it as below.

Add the customerid as another attribute in the age and location dimensions.

( you may the FK relation netween customer and location/age dimension tables.).

Now instead of using the referrence dimension relationship for age/location, use regular dimension and customerid as granular attribute to connect to fact table. This improved the performance a lot.

Hope this may solve your problem.

Regards,

Butchi Satuluri

|||Thank you Butchi!