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

by user3469285   Last Updated August 01, 2017 14:06 PM

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.

Tags :

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.

GregGalloway
March 11, 2016 01:32 AM

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.

user3469285
August 01, 2017 14:05 PM