Tabular, Multidimensional and Powerpivot which one to go for

 

I came across this wonderful article in the MSDN . Between power pivot and Multidimensional the choice would be quite simple because the moment we want to build large scale DW the ideal choice would be Multidimensional SSAS cube.

Due to the introduction of Tabular model in 2012 and it’s simplicity most of us want to move towards Tabular. I can even see lot of queries related to tabular model in the forum as most of the Mid cab and small cab companies are choosing the Tabular models now. But I would definitely suggest checking the below article as not all of them supports all the feature. The best part of this article is feature comparison.

Before making the model decision please check this Article.

http://technet.microsoft.com/en-us/library/hh212940.aspx

Happy Reading

Exist vs. Existing and Auto Exist–With Measures–Part 3

Please read the other two parts for continuity

Exist–With Measures

Most of the examples around the Existing keyword talks about the effect of Exists on Dimension . But what will happen if i use it against measure

Will that make any difference?

Existing keyword set the current context on measure calculation which is really invaluable

Let us check the below query

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

As expected, the Calculated measure has summed up the sales related to Australia and Canada, which is way lower than total amount against a year.

Let us execute the below query and get the sales against different countries

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Geography].[Geography].[Country].members on 1
from [Adventure Works]

As you can see, it displays the summed value against each country and they are same. Though theoretically this is correct, we would ideally want to get the data only against Australia and Canada for this new measure.

So what is happening here?

Calculated members doesn’t get affected by the current query context . If we need the current context then we need existing keyword

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Geography].[Geography].[Country].members on 1
from [Adventure Works]

What will happen if we  change the query back to Date dimension?

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

It produces the same result as above because in the above query the current context for Geography dimension is “All Geographies”

let us confirm this with below query

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
           [Geography].[Geography].[Country].&[Canada]
          }
         ,[Measures].[Reseller Sales Amount]
         )
member Geographycurrenthier as
[Geography].[Geography].membervalue
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada,Geographycurrenthier} on 0,
        [Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

This is very useful technique for End user reporting.

Happy reading