[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

SQL 2005 Multiple fact tables

Hi all,

The bit i'm stuck on;
Using SQL 2005, I have a cube with 2 fact tables in it.  If I bring in a dimension related to the 1st fact and a measure from the 2nd fact, the measure values are all the same i.e.
Product          Total_Copies
Magazine1      2500
Magazine2      2500

Each record in Fact one has multiple records in fact2 so I joined it on fact1's ID.  Is there some other relationship I need to add in so the Total Copies gets split up as per the dimensions bought in from Fact1?

(full explanation to follow; just typing it!)
  • 2
  • 2
1 Solution
If the Product dimension is related to fact 2, then you need to define the relationship in the 'Dimension Usage' tab of the cube designer.

You should add in all dimension keys that relate to fact 2 into the source table.
i.e. it should contain its own product key, so that it can reference the product dimension directly.

kintonAuthor Commented:
In that case I might not need to write my full explanation (not done it yet, just had a bit of a nightmare as msmdpump.dll stopped working for no reason - reboot fixed it).

Interesting.  I suppose when I think about it logically, having to go through Fact table 1 to get to the dimension creates joins (thus slows it down) which defeats the point of BI.

I think I'm going to be re-writing some ETL this afternoon!
If you already have a link to fact 1, you could get around changing your ETL by changing the source of Fact 2 to a view
  SELECT F1.ProductKey, F2.Total_Copies,.......
     FROM Fact2 F2 INNER JOIN Fact1 F1 ON Fact2.Fact1ID = Fact1.Fact1ID

However, I would recommend changing your ETL as you will gain signifcant performance benefit - SSAS doesn't really like running from views. I works but slows down significantly in my experience.
kintonAuthor Commented:
Yeah I've had that same experience :)
Thanks PFrog!


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now