CODEWITHSUNDEEP
sql-serverssascube
jayt.dev
jayt.dev

Reputation: 1015

SSAS cube does not SUM identical records in fact table

My fact/measures table contains two identical records (all columns contain same data).

When I process the cube it appears that it only sums up one of the two records. So instead of getting the result 100 that I expected, I get 50 units for 2015/01/01.

It seems like the cube performs something like a group by function on the measures when processing resulting in getting only 1 line from the two identical records.

Fact Table:

Code   name       date       units
----------------------------------
 1     productA   20150101    50
 1     productA   20150101    50

How can I overcome this problem?

Upvotes: 1

Views: 491

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7979

From the fact\dimension design point of view you should have only distinct combinations of dimension keys in your fact table. So, for example in your case, if

  • Code column is a key to Codes dimension,
  • Name column is a key to Products dimension and
  • Date column is a key to Date dimension and
  • Units is a numeric fact value

then you should have distinct dimension keys combinations (Code, Name, Date) and preaggregated to this level of granularity, numeric unit values.

So, if in your case you need to have both these rows (they are not duplicates by business point of view) it looks like you should change the granularity level of this fact table, or in another words, add one more dimension attribute (key) to your fact table, which will distinguish these rows, by some hidden (for now) dimension, for example Operator. After this manipulation your fact table might look something like this

Code  Operator   name       date        units
1     1          productA   20150101    50
1     2          productA   20150101    50

Upvotes: 0

Related Questions