Scenario is to compare actual and plan sales. Actuals are on the level of cashdesk, plan is on higher level workstation.
Dimension levels are Cashdesk -> Workstation -> Location, and is denormalized.
Question is what is the best practice to get this to work.
1. Create new dimension starting at level Workstation and join the plan to this dimension. At logical layer make two sources for the attributes Workstation and Location.
2. Split (normalize) dimension to three dimensions Cashdesk, Workstation and Location. Join plan to workstation dimension at logical layer. And create common dimension table by joining the three to one dimensions.
If Cashdesk -> workstation -> location truly represents a single dimension, then you should not split this into multiple dimensions.
You'll create a single dimension with two logical table sources. First table source will go to a dimension table that includes all three levels, set it's content level to "Cashdesk". Second LTS will go to a table that includes only the workstation and location level info. If no such table exists, you can create it in the physical layer using a view (select distinct workstation attributes and location attributes, but no cashdesk attributes)
Then I would create TWO fact tables - one for the actuals, one for plan. In theory you could smash it all into a single fact table...but I prefer to have different facts when the underlying data is at different grains. Make sure you set the content level properly for the facts - the "Actuals" fact will be at the cashdesk level, the "Plan" fact will be at the workstation level.
This is really easy to show, but hard to describe. Send me an email at SPowell@columbus.rr.com if you need additional info.