0

I have a Fact Table Service1 (open or latest data) from Mart1 and another Fact Table Service2 (historic data) from Mart2. These tables share few common measures and dimensions but the underlying dataset is mutually exclusive.

Now the business wants to merge these two facts into one table in Tabular model to do Year over Year comparison.

Is it possible to combine these two facts, if so, what should be the approach.

Alternatively, do we have to achieve this.

Things to note down are,

  • Records in Fact table Service2 will never change
  • The Dimension keys between Mart1 and Mart2 is not guaranteed to be same
Senthil_Arun
  • 1,008
  • 9
  • 15

1 Answers1

0

Are these Data Marts different databases? If so, you can create a calculated table that brings the two tables together. To do this, in 2016, on the bottom of the designer, there is a little plus sign on the far right next to the last table tab defined. When you hover over it, it will say "Create new table from DAX formula". Create the DAX that selects from the first table union the second table.

If the marts are in the same database you can create a partition for each on the table properties. In order to do this you would create a tabular model, open a connection to data source and bring in the changing data. Then click on table, partitions, click New, then grab the archived data. You would have to make sure the column definitions are in line in order to do this.

As far the other issues that you describe, it sounds as though you are using the Data Marts as your warehouses. Do you have access to the data before it was transformed into the Data Mart where the surrogate keys were applied? I generally keep a Persisted Staging Area around for these cases. If you employ a Data Vault (http://learndatavault.com/) prior to your Data Mart creation, you could simply create a new Data Mart with both sets of data and all of the Dimension keys will be intact.

Michael buller
  • 566
  • 4
  • 11
  • Hello, please elaborate on your answers as one-sentence answers do not fit the SO format. – Chait Nov 03 '16 at 16:47