I have four tables, two Facts and two Dimensions. Below I've listed the relevant columns of these tables, and the results I would like to see in the Cube.
Fact Order table ---------------- SK_Order FirstEverOrder Dim Order --------- SK_Order Fact Product Order ------------------ SK_Order SK_Product Number of Products Dim Product ----------- SK_Product Product Name
In the results I would like to see the First Ever Order Rate. That is to say, out of all the times a product appears on an order, how many of those orders were the first ever order the customer placed?
Products First Ever Order Rate (%) -------- -------------------------- Product 1 3% Product 2 2%
An example situation for the use of this report might be that you want to advertise those products that appear frequently on first orders.
So far I have my tables related together in the cube as follows:
I don't know what to do on either the Calculations or the Dimension Usage relationships tabs, or if I need to do anything more in the Cube Structure tab. Please be detailed in your answer.
In the Dimension Usage tab click the ... In the cell that is Fact Order and Dim Product. Switch that cell to a many-to-many relationship.
Then add a Sum measure on the FirstEverOrder column called First Ever Order Count. Also add a Count measure which is a count of rows called Order Count.
Now create a new calculated measure which is:
IIF([Measures].[Order Count]=0,Null,[Measures].[First Ever Order Count] / [Measures].[Order Count])
Now you should be able to build your report as you have shown. That's the power of many-to-many.
I posted this question back in March 2016 when I didn't fully understand Kimball methodology. I now have a much better understanding, having gained more experience, and can answer my own question.
The answer is, the ratio I wanted above, was measure at the grain of product. Neither the header fact table ("Fact Order table"), nor the line item fact ("Fact Product Order") were at the grain of the product. The header fact table was at the grain of an order, and the detail table was at the grain of a product-order combination (i.e. a line item).
So where I was going wrong, was that I was missing a Fact table! I needed a fact table at the grain of the product (i.e. one product per row in the fact table) to contain my measure ("First Ever Order Rate"). I could then put a foreign key in the fact table, to the Product dimension, or better yet, I could put the measure in the dimension itself. This would effectively make the dimenison a fact table, and all the dimension attributes would become "degenerate dimensions". But this is the proper way to do it. The same table can then be used as the source for both the Product measure group and the Product Dimsiension in the cube.