How to handle code table attribute in Data warehouse which user can change

Question

I have not been able to come up with a better title for this problem I have, but I will try to describe it:

I am trying to make a data warehouse from two input .csv files, and I am fairly new in this field.

I have one input file with account_number, account_balance, and account_code columns. Account has account_code. User wants to see balance by account_code, and code_group.

Account_code belongs to the code_group, and mappings between account_code and code_group are in the separate file. There are rules for this mapping e.g. if a balance by account_code is positive account_code belongs to one code_group, if it is negative, it belongs to another code_group.

I made fact table where I put account_balance, and Account dimension table where I put account_number and account_code.

But, I don't know how to handle this code_group. If I want to put it as an attribute of the account dimension, I think I have to precalculate the balance by account_code, and according to the sign of the balance put the corresponding code_group in the dimension table.

But, user wants to have possibility to change mappings between account_code and code_group, so that particular account_code will belong to another code_group, and he wants to see report by the new and by the old mapping. So I think I will have to update fact and dimension tables according to this change, and I am a little bit confused how to handle this.

Should I put code_group attribute in account dimension, or should I handle code_group somehow separately?

Any help is appreciated.

Edit: This is staging table:

account_number account_balance        account_code
-------------- ---------------------- ------------
100676         345.87                 1101
100829         200.15                 1101
100312         154.01                 3100

Account code belongs to one code_group. This is example mapping:

account_code code_group
------------ ------------------------------------------
1101         10 (if balance of the account_code is >=0)
1101         20 (if balance of the account_code is <0)
3100         20

User can change this mapping at any time, manually so it can look like this tomorrow:

account_code code_group
------------ ------------------------------------------
1101         30 
3100         20

Also, user can import new rows in dwh, and that will change balance on the account group, and also it can change code group, because of changed balance.

This is example of required report:

sum of account_balances account_code code_group
----------------------- ------------ -------------
546.02                  1101         10
154.01                  3100         20

This is fact table:

account_balance        account_key
---------------------- -----------
345.87                 1
200.15                 2
154.01                 3

This is dimaccount table:

account_number account_code account_key
-------------- ------------ -----------
100676         1101         1
100829         1101         2
100312         3100         3

And I don't know where to put this conditional mapping of account_code and code_group. Should I put code_group in some other dimension or in the account dimension, or should I left it on the side, until I have to calculate the report? I prefer the third option right now, because I think it will be the simplest. Because if I want to put code_group in the account dimension, I first have to calculate all the account_code balances, and if I do so, tomorrow it could change...

Answer 1

I'll try not to be harsh, but your fact table is not ok. Actually, there is two possible cases:

1. You don't need data warehouse, just a report
2. You need it, but you have missing steps because here you're talking about aggregation table or snapshot view for predefined report and/or dashboard.

What's wrong here?

Design! Fact table should represent business process. In your case fact table is just custom summary report by account_no and account_code. Please, correct me if I understood wrong, but when you update fact table, you actually change account_balance where it's needed and insert new accounts if appears. Business process you should monitor balance change by change_code and date.

fact:

change_code    account_code_key    change_date    changeAmount
--------------------------------------------------------------
123456         1                   2014-01-01     +380.00
123457         1                   2014-01-01     -220.00
123458         3                   2014-01-03     -45.67

Using this design you can have historical summary of all your accounts. If client want to know what was his balance on December 1. last year. Or how much he spent (only minus sign) from {start_date} to {end_date}.

This fact table requires two dimension tables: time and account dimensions.

account dimension:

account_code_key    account_number    account_code    actual_code_group
-----------------------------------------------------------------------
1                   100676            1101            10 (sum of changes is positive)

It was hard to understand how do you mean user want to see new and old mapping when you haven't defined date field.

In dwh, all conditional fields are left for front-end. In your case, while printing (calculating) report. There is way to precalculate it using SDC type 2 but I will never suggest it to you because it's much easier to calculate it later.