Constellation schema report both fact tables

[CS4]

Hi,

I have the above model (very much simplified for this example): two fact tables, linked by a dimension table, all with CustomerID field. Both fact tables have OrderID.

Fact table A has volumes delivered to each customer depot - a customer can have many depots. Each order to a depot would have its own OrderID.  Fact table B has volumes returned by customers, with the OrderID, but no depot.

I have been asked to report on volumes returned from each customer depot, i.e. I need to somehow "link" the OrderID in each fact table when there is no actual link.  The report would be e.g. a simple table with: customer, depot, volume ordered, volume returned.

I'm fairly new to DAX.  Can I use something like Crossfilter or Treatas or another function?

Or do I need to scrap the model and merge the fact tables into one, which would be a big headache?

All help gratefully received.

[gmsamborn · Accepted solution]

Hi @CS4 

If OrderID is unique in FactDeliveries, it can already be used as a dimension table.

I created an inactive one-to-many relationship between FactDeliveries and FactReturns on OrderID.

1 measure is required:

Returned = 
    CALCULATE(
        SUM( 'FactReturns'[Volume Returned] ),
        USERELATIONSHIP( 'FactReturns'[OrderID], 'FactDeliveries'[OrderID] )
    )

I hope I understood your requirements.  Let me know if you have any questions.

2 Fact Tables.pbix

[CS4]

Yes it is complicated - I vastly simplified what my data was all about for the sake of making this question easy to read.  However I am happy with how it is working now, with the inactive relationship.  It may be a 'bodge' but if it works that is good enough for now.

I need to find some good resources that really explain how to work with constellation schemas in detail - any ideas?

Many thanks again.

[CS4 · Accepted solution]

Hi gmsamborn,

This is just what I was looking for.

At first the inactive relationship between fact tables appeared as bi-directional 1:1, but I was able to manually amend it to 1:many single direction.  (This may be partly because, whilst OrderID is unique in each fact table, there are some orders in Returns that don't appear in Delivered, and vice versa, for complicated reasons not worth explaining).

I have searched high and low all over the internet for this. So many sites say to use a Constellation Schema for two fact tables, but then don't go any further and explain what relationship and DAX you need to make it work.

Thank you so much.

[gmsamborn]

Hi @CS4 

I'm glad this is working for you.

However, one thing you said has me worried.

"there are some orders in Returns that don't appear in Delivered, and vice versa, for complicated reasons not worth explaining"

The odd situation that you have a OrderID in FactReturns that doesn't exist in FactDeliveries will be a problem that I'm not sure how to get around.  (I'm guessing that this is some type of adjustment since IMHO it logically doesn't make sense to return something that hasn't been delivered.)

Comments?

[CS4]

Just to add, I should have made it clear in my first post what was the unique primary key in each table.

It is not actually a 1:many situation, as each OrderID appears only once in both fact tables, but forcing it to be a 1:many makes the Userelationship function work.

This solution could be used in so many ways, e.g. budget and actual fact tables using accounting nominal codes as unique keys.

[CS4]

I'll try re-creating the OrderID dim from scratch by duplicating it from Fact table A instead of B and see if that makes any difference. That will be tomorrow as I have to log off now.

[gmsamborn · Accepted solution]

Hi @CS4 

If OrderID is unique in FactDeliveries, it can already be used as a dimension table.

I created an inactive one-to-many relationship between FactDeliveries and FactReturns on OrderID.

1 measure is required:

Returned = 
    CALCULATE(
        SUM( 'FactReturns'[Volume Returned] ),
        USERELATIONSHIP( 'FactReturns'[OrderID], 'FactDeliveries'[OrderID] )
    )

I hope I understood your requirements.  Let me know if you have any questions.

2 Fact Tables.pbix

[CS4]

Hi again,

Thanks for your quick response.  I basically created the OrderID dim by duplicating Fact table B and then removing all uneccesary rows - this would be why there is a 1:1 relationship on OrderID, as it is already a unique value in Fact table B, so now it is the same unique values in both tables. It won't let me change it from bi-directional to one direction.

I have merged Fact table A into the OrderID dim, again removing unwanted rows after merging. This brought the depot field into OrderID dim.

Surely I can't just keep the depot field in OrderID dim, I need to keep the OrderID field, as otherwise there will be no way to link back to Fact table B?

It is working at present, though I haven't yet tried to put in the Volume Delivered from Fact table A with a 'Userelationship' DAX formula.  It just looks very odd in model view to have a bi-directional 1:1 arrow to one fact table and an inactive (but 1:many and directional) arrow to the other fact table.

[CS4]

I've just tried to implement this.  It is partially working - managed to create the OrderID dim table, and none of my other visuals have broken, so far so good. However, the relationship from OrderID dim to Fact table A has defaulted to inactive. When I try to activate it, I get an error message saying it can't be done as it would "introduce ambiguity" between the tables, presumably as they are already linked on the Customer ID.

Strangely the link to Fact table B has defaulted to a 1:1 bi-directional, even though that is also linked on CustomerID.

I will carry on investigating and trying to fix it, but meanwhile has else out there has got another approach?  I was hoping to find some DAX measures to avoid changing the model, but I don't know if that is possible.

[audreygerred]

I'm pretty sure the bi-directional nature of the one join is causing the ambiguity. I didn't realize your Fact Table A had Order ID as primary key. OK..... let's try this, in Power Query, merge the OrderID dim table to Fact Table B and bring in the Depot field. Then make another table (you can duplicate your OrderID Dim), and then in that table just keep the Depot and remove empty and duplicates), then you can join the depot table to both fact tables.

[CS4]

Great - I would never have thought of creating an OrderID dim table as that is the main key in each fact table. The CustomerIDs are not unique in each fact table, but the OrderID's are unique in each table, though common to both tables (unless there was no returned volume).

[audreygerred]

You can create an Order ID dim table that has Order ID and Depot, then connect that to both tables using Order ID. In Power Query you can dupllicate Fact Table A and group by Order ID and Depot.