Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I'd like to create a datamodel that supports reporting on dimensionens with changeing statuses over time.
The relevant tables in my datamodel is ...
1. A fact table (fFin) that contains financial information from my general ledger.
2. This is tied to a dimension table (dStore) containing metadata about each store (in .xlsx-format).
3. storeHistory
Table 1 & 2 are linked in a one-to-many relationship with a custom store ID as key. Then I have written a piece of VBA code that takes a snapshot of the dim table everytime a status is changed (e.g. store size from Small to Large). This snapshot is placed in a .csv file in a different folder and loaded into the data model in table no. 3: storeHistory. All the snapshots are appended into a single table. To my knowledge, the setup is somewhat similar to how you would handle SCD Type 4 situations with temporal/mini dimension tables. Anyway. I need input on how to handle this setup correctly in terms of keys connecting the tables, how to setup a report returning store size over time and so forth. Is it even the most suitable setup? Status changes often so an SCD Type 2 or similar setup is not approriate.
Suggested solutions should not involve a datawarehouse as this is not an option in this project.
Thanks in advance
/M
Hi @MDK,
Would you like to copy this model in Power BI? It seems an incremental refresh type. Please refer to blog/power-bi-desktop-may-2018-feature-summary/#incrementalRefresh.
There is also similar reference: Save-old-and-append-updated-data-into-an-additional-table.
Where is the original data stored? If it's in the Database, you just need to import it as the history table.
Best Regards,
Dale
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |