Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MDK
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
v-jiascu-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.