CODEWITHSUNDEEP
ssascubedrilldown
Andrea Colleoni
Andrea Colleoni

Reputation: 6021

SSAS cube aggregation formula when all dimensions' values are the same

I defined a cube with some dimensions.
When in the fact table, some rows have the same value for every dimension the cube stores only one aggregate row and applies the SUM function on all numeric measures, but I need to calculate the AVG.

Is it possible to choose the aggregation formula for each measure when rows can have same values on all dimenions?

In addition, related to this, I notice that when I browse my cube and drill down (e.g. with an Excel pivot), I don't see a row for each record in the fact table, but a single row for all records having the same values for all dimensions, with the SUM of all numeric fields.
Is it in some way possible to drill down to the orginal table records?

For better comprehension I created this very simple structure:

Dimension data:
enter image description here

Fact data:
enter image description here

Cube structure:
enter image description here

Cube browse result:
enter image description here

An Excel pivot grid:
enter image description here

And the result of the drill down on member [Dimensione 1]:
enter image description here

Upvotes: 0

Views: 66

Answers (1)

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

I'm not 100% sure about this, but I noticed that:

  • in multidimensional databases, the data is stored in aggregated way; I didn't find how to choose what type of aggregation (SUM, AVG, etc.) can be applied during processing, but detail data became unreachable unless you return with some key on OLTP
  • in tabular databases you still can query detail data, following model relationships

So I reproduced the same model on tabular database and solved this issue.
Hope this will be helpful to someone.

Upvotes: 0

Related Questions