cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular 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
Super User II
Super User II

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.