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).
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
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.