I am new to PowerBI and have been watching lots of training videos/courses and still trying to wrap my head around the best way to create a model for our financial data.
I am an accountant that taught myself SQL and have written many reports and use ad hoc queries frequently. I understand the Oracle tables used for our financial ERP software very well. I plan to use SQL to model the data in views from our operational data store before bringing it into PowerBI.
I am trying to decide the best way to model my data which comes from our financial ERP software. In that system there is a table that contains the finance transactional details (1.1 million records per fiscal year) and also a table that has summarized the data already per fiscal year, fiscal period, fund, organization and account.
For summary level visuals, it seems like it would be more efficient to use the already summarized/aggregated data since it has less rows, but I also want to be able to drill down into the transactional details. Is it possible to drill down into a different table that isn't being used in a certain visual as long as the relationships are created in the model? I think this would look like two fact tables connecting to each other (possibly using a bridge table?) with each connecting to the same dimension tables.
If it is possible to drill down into the details from a visual based on the summary fact table, does that sound like the best way to be more efficient?
Simplified example of data:
| Transaction Detail Table - 1.1 million records per fiscal year for subsidiary ledger | | |
| Org Code | Fund Code | Account Code | Trans Desc | Trans Date | Trans Amount | Fiscal Year | Fiscal Period |
| 123123 | 120000 | 720001 | Example 1 | 11/24/2023 | 100.00 | 24 | 05 |
| 123123 | 120000 | 720001 | Example 2 | 11/24/2023 | 60.00 | 24 | 05 |
| 456456 | 130001 | 663246 | Example 3 | 11/30/2023 | 250.00 | 24 | 05 |
| 456456 | 130001 | 663246 | Example 4 | 11/30/2023 | 80.00 | 24 | 05 |
| 243243 | 120000 | 663246 | Example 5 | 6/30/2024 | 360.00 | 24 | 12 |
| 243243 | 120000 | 663246 | Example 6 | 6/30/2024 | 220.00 | 24 | 12 |
| 123123 | 120000 | 523012 | Example 7 | 8/4/2024 | (50.00) | 25 | 02 |
| 123123 | 120000 | 523012 | Example 8 | 8/4/2024 | (1,100.00) | 25 | 02 |
| 456456 | 130002 | 720001 | Example 9 | 8/24/2024 | 600.00 | 25 | 02 |
| 456456 | 130002 | 720001 | Example 10 | 8/24/2024 | 1,000.00 | 25 | 02 |
| 456456 | 130003 | 523012 | Example 11 | 8/30/2024 | (900.00) | 25 | 02 |
| 456456 | 130003 | 523012 | Example 12 | 8/30/2024 | (300.00) | 25 | 02 |
| 456456 | 130004 | 884433 | Example 13 | 10/10/2024 | 70.00 | 25 | 04 |
| 456456 | 130004 | 884433 | Example 14 | 10/10/2024 | 65.00 | 25 | 04 |
| | | | | | | | |
| | | | | | | | |
| Summarized Table | | | | | | |
| Org Code | Fund Code | Account Code | Sum of Trans Amount | Fiscal Year | Fiscal Period | | |
| 123123 | 120000 | 720001 | 160.00 | 24 | 05 | | |
| 456456 | 130001 | 663246 | 330.00 | 24 | 05 | | |
| 243243 | 120000 | 663246 | 580.00 | 24 | 12 | | |
| 123123 | 120000 | 523012 | (1,150.00) | 25 | 02 | | |
| 456456 | 130002 | 720001 | 1,600.00 | 25 | 02 | | |
| 456456 | 130003 | 523012 | (1,200.00) | 25 | 02 | | |
| 456456 | 130004 | 884433 | 135.00 | 25 | 04 | | |
| | | | | | | | |
ADVthanksANCE
