Looking for suggestion on data model for a PnL display

[sangdelattre]

Hi All,

I have an Accounting Ledger table with significant number of rows

Management is looking for a PnL BUT the sequence and the values are not simple sum on this Ledger

1) they want to display Account/Sub Account but not folllwing COA

2) they want to group some sub accounts or split some of them using another dimension

3) some detail is not coming from the Ledger, but from other sources

I managed to find a solution using a configuration table. In this table, I would list all filter criteria and where to display in the PnL table

ex:

Account | Sub Account (channel) |  .... | displayAccount | displaySubAccount |  .... | 

Automative | Retail |  .... | Revenues | Offline

Automative | marketplace |  ....  | Revenues | Online

For the sake of simplicity, I skip the part where I need to display result in different currencies, etc ...

Then I have set of measures that will apply the filter calculate correct amount.

It does work.

but my problem now is that it may take up to 1 minute to refresh data, same if I change time period or change other slicer

if I understand correctly the extendable table that Power bi create, it means that wether I filter on the column of the dimension table OR on the column of the fact table, performance would be the same?!

I assume that because I dont have relationships between my configuration table and my ledger, this is where It is propbably not optimum as filter does not propagate on my ledger table.

I thought about building some relationship, but it becomes "complex", as I would need to 10ish new dimenship tables + 20 relationship (2 for each "filter" table of my configuration table).

This for only one of the PnL I need to build a report.

Configuration table <---- DimFilterTable[1,2,...,10]  ---> Ledger table  

I do understand that I should prepare my data much better in the datawarehouse so that PBI calculation is kept simple.

But for other considerations, I would like to see if there is no "improvement" I could do on how I produce my PnL before adopting a complete different strategy.

Sorry I cannot share more particular data, but I hope my explanation are sufficient.

Thank again for reading this poste. Thank even more if you have any suggestion 😉

Regards

Sang

[DataInsights · Accepted solution]

@sangdelattre,

Relationships between fact and dimension tables are essential for optimization. It’s preferable to filter columns in dimension tables rather than fact tables. The configuration table is a good solution for creating groupings that don’t exist in the COA table. The COA table would have a relationship with the configuration and fact tables. Visuals would use columns in the configuration table. For example, you could put Configuration[displayAccount] in a matrix (rows), and that would slice the fact table by Revenues.

Additional groupings can be created as additional columns in the configuration table, if they are based on the same underlying dimension or combination of dimensions. If a grouping based on the combination of SubAccount and Department is required, for example, then you would create a second configuration table.

As far as some detail coming from other sources, you would have multiple fact tables and each fact table would have a relationship with the COA and configuration tables. Create measures for each fact table, and then an overall measure that sums each fact table’s measure. Example:

Total Amount = [FactTable1 Amount] + [FactTable2 Amount]