Recently I have been asked a question, I have defined my
measure with aggregate function as “None” and all the values are returned as Blank.
What is the reason for this? So I have created a below demo to explain the
concept of “None” aggregate function and how it works. Most you may be having
the same question. So I am documenting this here to help you to understand
about this.
Below is the sample model which will be used for
demonstrating this
I have created a cube using the wizard with the above data
model. Below is the structure of the
cube
Dimension : Product
Attributes :ProductId
Dimension : Region
Attributes
:RegionID
Dimension :Chk (Dummy
Dimension)
Attributes
:ChkId
Measures : Sum(SoldQty)
BillNumber
(No aggregate)
Measures : Sum(Valx)
(Dummy Measure)
The cube is deployed and processed. Now in the browser, let
us take a look how this measure behaves.
Now when using Region Dimension alone measure BillNumber is returning
blank.
Now let us try adding the Product Dimension also inside. But
still we could see only blanks in the BillNumber.
But if you try expanding for each region you will start
seeing the Billnumber values.
The reason for this is in the first two tries there was an
attribute at the level “All” . As we have mentioned BillNumber as NoAggregate ,
there will not be any value available for a “All” member.
Now you may get a question that in the first screenshot we
have used Region at the granular level only. But why BillNumber is still Blank
? As RegionId has been used in the query , by default ProductId will
be at the “All” level. In the below MDX query , the product dimension is not
used in the query , But by default it “All” member has been considered for getting the values.
But in the last screen shot , we have the leaf level of all
the attributes available in the cube so the BillNumber measure displays the
value.Does this means do we need all the dimension attributes in
the query to display the no aggregate measure ? Not necessary if you notice we
have one other dummy dimension DimChk and other dimension attribute Color also.
Without using the chk dimension attributes they will be considered as All
member. But still we are getting the BillNumber measure displaying the
value.This makes clear that we need to have attributes which is having the IDs
as keys which are linked to the fact table which has this measure. In the above
example if we use the ProductID&RegionIDattributes , No aggregate measure
BillNumber will display the value.
If we remove one of the key attribute values, the bill
number will start displaying Blank.
Conclusion
No Aggregate Measures will display values only when attributes
with all keys linked to the fact is available in the MDX query or browser as it
displays the leaf level data.
I have uploaded the SSAS project and relevant scripts in the
below link for your reference.
Hope this blog is helpful .Will meet you next week with
another blog. So far I am happy that I could keep up my resolution on doing a
blog per week and hope I will be able to keep it up JJJJ.
