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
TDouglassAFG
Frequent Visitor

Allow reporting by old hierarchy and new hierarchy for legacy

Hi,

 

I am looking for suggestions on the best way to model the following.  We are implementing a new ERP that has different levels than our legacy and I would like to allow them to view the data using the old hierarchy as well as the new hierarchy.  I am able to establish a relationship to both with data in the sources.  Eventually the old hierarchy will become obsolete.

 

Here is an example

New hierarchy

Level 1

Level 2

Level 3

Level 4

 

Old hierarchy

Level 1 

Level 2

 

Level 1 in both are the same.  Level 3 in the new will be the same as Level 2 in the old.

 

Should I 

1) create multiple dimensions for the old and the new with multiple sk fields on the fact table

2) Create one denormalized dimensions that has the old and the new codes/descriptions on one record with only one sk on the fact

3) Create multiple dimensions for each of the 4 levels with old and new codes/descriptions (currently we do have row level security on the level 1 and 2 of legacy data)

4) Use a xref/identify table that will include the natural key and sources that relate to one dimension table

 

Thoughts?

 

 

2 REPLIES 2
lbendlin
Super User
Super User

is the new hierarchy derived from the old one, or are there cases where you cannot do a 1-to-1 map between the levels?

 

Have you looked into using TREATAS() ?

Hi lbendlin,

 

I didn't see your reply until just now.  the heirarchies will be maintained seperately in both environments.  I will either have hard coding to map the old to the new, or there will be a cross reference table.  Since they are both maintained separately, it would be possible that the new would have one that the old doesn't and visa versa.  Does that answer the question?

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.