How to build a dimension from two separate hierarchies in the source data

I am trying to create a dimension for the organisation (called DimOrganisation), but I am not sure what to use as the key.

Here is a diagram of the organisation tables...

Enterprises is the top parent table. All data relating to Stock is stored by LocationID (this is called the Stock hierarchy) and all data relating to sales is stored by RevenueCentreID (this is called the Revenue Hierarchy). At the moment, the cubes are only required to show data by ProfitCentreID (we would group the dimension by开发者_Go百科 ProfitCentreID in a view before loading the cube).

Here are a few solutions that I have been considering:

1. Create a composite key (RevenueCentreLocationKey). You would never want to see the data like that, but I would link the fact table to the correct key when building the cubes (e.g. ProfitCentreKey)
2. Only go as far a ProfitCentreID, because that is all that we need. The source data is stored in the data warehouse (together with the fact and dimension tables), so we can get to the more granular data later.
3. Create a separate RevenueCentre and Location dimension in the database. However, in the cube, you would only see a dimension going to ProfitCentreKey

I know this is quite subjective, but I would like any advice or ideas that you think might be helpful. Thanks.

I'm using SQL Server 2008 (both for the data warehouse database and the Analysis Services cubes)

I would go with solution 3.

While your company doesn't look at RevenueCentres by Location now. having that ability could prove useful in the future.

Are your CostCentres and RevenueCentres real entities, or are they just logical mechanisms to aid in the bookkeeping?

Is there a CostCentre for each RevenueCentre?