Allow reporting by old hierarchy and new hierarchy for legacy

[TDouglassAFG]

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?

[lbendlin]

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() ?

[TDouglassAFG]

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?