Split Fact Tables or Single Table with a Type column

DAX DAX Calculations
1

I’m working with AdventureWorksDW, which has 2 fact tables for Sales–FactInternetSales and FactResellerSales. The tables are identical except for a couple of columns. So every dimension in my model has 2 sets of relationships.
I’m tempted to combine the fact tables into one Sales table and add a “SaleType” column. I guess it depends on how many of the potential reports want to access data about all sales ? One way I have to write a measure for every attribute I want to aggregate in the two tables when I want all sales. The other way I think most of the time I can split sales types just by adding the SalesType column to a visual. Anybody had any experience here ? Are there gotchas I’m not seeing ?

2

Have you tried to use parameter harvesting. If you can post the pbix file, it will be easier

3

Sorry, file is to big to upload. This is a design question just about setting up the data model, rather than writing a specific report. Does it make a noticeable difference for the user to deal with 2 fact tables and have to know how to combine them, or to deal with one fact table and having to know how to filter it.

4

thanks Ken for the added notes

i think we can make it work both ways and is a matter of choice. But personally i will go with “Combine the fact tables into one Sales table and add a “SaleType” column.” this option. You can combine table using “Union” command . that will give me a clean data model with a single to many relationship between the dimension and signle fact table.
thanks

1 Like
5

Thanks. My emphasis more and more is with what the data model “looks” like and what it “suggests” to the user who is building reports.

6

Hi @kentyler!

Here’s a question that was answered by the one and only @sam.mckay in regards to multiple tables and he explained very well and goes into every detail.

Also, here’s a link that can I think can help you with the model.

Let me know if I was able to help you!

Cheers!
Yeriel

7

Thanks for the link Yeriel. Good stuff, but not the same as my problem. This link was about how to structure data so that certain relationships would be possible. My issue comes earlier in the process. I wondering if there is a downside to combining data from 2 simliar tables into one table and adding a “type” column to distinguish which records came from each table. It’s a trade off between having to filter any dax measure for the appropriate subtype or having to write dax to create any aggregations over the 2 subtype tables. I’m guessing I will see better downstream results by doing the combination.

8

Thanks for posting your question @kentyler. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.