I’m trying to complete a report comparing data based on contracts and Contract categories.
I have 4 fact tables. One table is missing the Contract number. Another Table is missing the dates. Another is missing the invoice numbers.
I tried to use TREATAS and USERRELATIONSHIP. The TREATAS kind of works.
I created and brought in some lookup tables and tried to create one-to-many relationships.
I also added an inactive many-to-many relationship, but that didn’t work either.
I mocked up some data so I can share my problem.
Basically I am starting with the GL table because I want everything to tie to the GL.
Next I am using the Posted Table to get the amounts that posted using a TREATAS between the document number in the GL table and the invoice number in the Posted Table.
I’m then trying to compare budget to the GL (but I can’t use TREATAS because I don’t have an invoice number column in the budget table). I’m then also trying to compare the Spread, between the Budget and the GL and them create some profitability measures between them.
I’m not sure what direction I need to go. Do I create a virtual table of Contract and Invoice and build a relationship to all 4 tables? The USERELATIONSHIP didn’t work for me.
I posted a previous post, Data Modeling. Building Relationships with Multiple Fact Tables where I needed to compare two fact tables. I read more into TREATAS and starting to understand, but with four tables and each missing certain elements, not quiet sure what to do.
I’ve added the details of the Dates, Contract Number and Document Number in all the tables that were missing. I’ve also removed the duplicate records that were there into the tables.
Since “Posted” table is just an extension of the “General Ledger” table so therefore I’ve merged the records that were missing into the “General Ledger” table from the “Posted” table.
Also have created few of the dimension tables to simplify and reduce the size of the model. And lastly, have also added the “Date” table and have marked date column as a “Date” table.
I’m not explaining the transformation steps here since it’ll just simply increase the length of the post. So I request you to please go through the transformation steps that have been applied in each of the Fact Tables to understand it more better.
Lastly, here’s the screenshot provided for the reference about how your model looks like -
You’ll also observe that the size of the PBIX file which I’ve attached is way smaller and efficient than the size of the Excel and PBIX file that you had provided earlier.
Below is the working of the Excel and PBIX file attached for the reference.
And lastly, I sincerely recommend you to please go through the “Advanced Data Transformations and Modelling” course which is available onto our education portal. Since most of the transformations performed into this file is of simple and basic nature. Below is the link of the course provided for the reference as well.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Hi @ibesmond, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
