Search code examples
sqlssasdata-warehousebusiness-intelligence

Data warehouse design shared branch scenario

Let's say we have the following data source view for one of the cube. The Fact table it's in the middle and relates the amount /branch/cost center/Cost category/ date/scenario (current vs budget).

DSV

The current Amount measure means the value of the 100% of the branch. But in my scenario, the company has branches that are owned only by a percent, not 100%. An example: BranchNo 7 was owned 50% from the Amount until january 2013, 80% from january 2013 until january 2014 and 100% until today. This information it's keept in a table like the one below. The table stores the history of the SharePercent changes...

Fk_branch, FK_Date, SharePercent

7, 201301, 80.000
7, 201401 , 100

Wich is the best approach to integrate this in the schema presented in the link above? I think about a new measured called SharedAmount, but how should the deisgn looks like given the table of sharings ?

Solution

  • The problem was solved by adding a new measure in the fact table called SharedAmount. The shares are calculated in the fact table during the ETL Process as being Amount(100%) * SharePercent saved in the Branch Dimension for a given period.