The same data source may end up difference value when different options are selected. Please see sample below.
Sample: one sales rep SRP1 has two sales $10 and $20
SRP1, SM1, $10
SRP1, SM1, $20
Load it to dimension D1 as
Load data to cube X when using option store values
Cube X shows
Load data to cube X when using option accumulating values
Cube X shows
Obviously, accumulating values option gives us the right result. The reason of getting different results is that two records are same. These two options generate different result only if multiple records from source end up in the same cell.
Option stored values: every new record loading into same cell will overwrite the previous loaded values; in this case $20 (second record) will overwrite the 1st record.
Accumulating values: every new record loading into same cell will be added to previous loaded values; in this case $20 (second record) will be added to $10 from the 1st record.
Multiple parent hierarchy vs. alternate hierarchy
The same data source may end up difference value when dimension hierarchy defined as Multiple parent hierarchy or alternate hierarchy
Sample: one sales rep SRP1 has two sales $10 and $20, but under different managers. It could happen when inputting data for spreadsheet
SRP1, SM1, $10
SRP1, SM2, $20
We can generate dimension as below, where sales SRP 1 belongs to two manager
Cube X shows
It is obviously wrong result, as the total should be $30. In this case, we need to identify data error. The best way to handle it is to use database with SQL statement. Clean data first and then load it
However, if we use alternate hierarchy, then it should be OK
Cube X will show
From business requirement point of view, it should be mostly the first case. Therefore, when data doesn’t match the original, then to check multiple parents becomes very important. This problem happened normally with spreadsheet data source.
No comments:
Post a Comment