cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MDK New Member
New Member

Changeing Dimensions (SCD)

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

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Changeing Dimensions (SCD)

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.