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!)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.