Reference dimensions are creating too many joins

Hot Topic

Grouping and Filters
Replication Field Size Error
ReportViewer-winform
Generate Flat File via Stored Procedure
Lookups and their error flows
MDX Query syntax
Transaction with SSIS / SQL Server / DB2
Which services are needed?
Enable remote connections with an SQL server created with Visual Webdeveloper 2005
Foreach Loop and distributed files

SQL Server

Hypertext link in a report.
SQL Way to Suppress Repeated Values
report on two database
Login Failure
data conversion
Get the list of variables in a package inside a custom component
ISNUll problem. Where clause doesn't work properly when I use isnull clause
SSIS Packages Dynamic Connections
Excel Source Returning NULL
Package failure still causing partial load

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.

Answer this question

Reference dimensions are creating too many joins

  • László Nánássy

    Thank you Butchi!

  • leclerc9

    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



  • Reference dimensions are creating too many joins
Answer this question