Two facts, two dimensions, one query, one cube, can it be done and how?

Question

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:

Please, I do want to do this in the cube. I know it can be done via a stored proc with an SSRS report, but I want to do it in the cube.

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.

Answer 1

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.

Answer 2

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, 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 pointing to the Product dimension.

Alternatively, 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". The same table can then be used as the source for both the Product measure group and the Product Dimension in the cube.

To calculate the ratio, it would then have been much easier to have done this in the ETL, as opposed to a calculation in the cube.

The problem with Many-to-Many relationships in the cube, is that they relate many dimension members in one dimension, to many dimension members in another dimension. They do not relate many fact rows in one fact table, to many fact rows in another fact table. That is not how kimball or MS cube technology is designed to work.