Observations on interoperability between BISM Tabular and OLAP clients

Some of our customers asked me recently what will happen to their current OLAP investments now that BISM Tabular will be released in some months.  As a consequence, I am becoming more interested in the impact BISM Tabular will have on existing OLAP clients.  It is possible that client software use will drive user adoption on the server side; in other words, if DAX-only clients like PowerView (Crescent) become popular, there is a high chance the technology team will get some pressure to move on the direction of BISM tabular.

In order to better understand how these models interoperate, I decided to test a few things:

UDM advanced functionality

It has been said some advanced features available in the UDM (like scoped assignments) will not be available in the first release of BISM Tabular.  Turns out, every Tabular deployment has an MDX script as part of its XMLA definition.  In other words, you can add scoped assignments by manually editing the <MdxScripts> tag:

image

The interesting thing here is that the assignment only affects MDX queries to the Tabular model.  In other words, when querying the model via DAX the output ignores the scope assignment.

image

UPDATE (3/12/2012).  The above method of manually modifying the XMLA script to add MDX functionality is not supported by Microsoft.   It is used only as an exercise to explore the Tabular model’s environment. Do not use it for actual Tabular development.

Calculated Members

DAX measures are always assigned to a table. At the same time, table columns can be used as either dimension attributes or measures (by default, using a non-numeric column as a measure will create a count aggregate over the column values).

MDX measures on the other hand, belong to the special “[Measures]” dimension and do not appear directly associated to any regular dimension.

So how can we interoperate between both worlds?   Going from DAX to MDX is easy, in fact we just did it in the example above:  [Measure 1] is a DAX calculation, assigned to the FactinternetSales table. MDX was able to query it without at problem, under the unique name of [Measures].[Measure 1].  Interestingly, when browsing the model from an OLAP perspective we see this measure under a DisplayFolder with the same name as the underlying associated table in the DAX definition:

image

Going from MDX to DAX is actually not possible. By this I mean that MDX calculated measures in the tabular model cannot be accessed through DAX. As an example, take the definition below:

image

The first measure highlighted above is defined in MDX whereas the second one is defined in DAX.  They both belong to the same SSAS Tabular model, in fact they are both in the same <MdxScript> tag, though they belong to different <Command> nodes.  When deployed, the OLAP interface looks like this:

image

Even though they look very similar (in fact, they belong to the same DisplayFolder), only [Measure 1] is available to DAX, where as both [Measure 1] and [X] are available to MDX.  If you attempt to query [X] with DAX you will get the following error:

image

Even if you try tricking the script defining a DAX measure that calls the MDX calculation, the result will be an error (even though the Tabular model will let you deploy it):

image

Calculated dimension members will have a similar fate:  They will only work when the model is queried through the multidimensional interface.  A DAX Summarize() or CalculateTable() function will completely ignore any MDX calculated dimension members.

Query Performance

On my tests, MDX queries to a tabular model seem to perform the same or slower than MDX queries to a multidimensional model reflecting the same underlying data model.  On the other hand, DAX queries to a Tabular model seem to outperform both options above in a variety of scenarios.

As an example take, see the MDX query below – the type of calculation you would typically see on a business report.

WITH
SET
RankedCustomers AS
ORDER(
[Customer].[Customer].Children
, [Measures].[Internet Sales Amount]
, BDESC)

MEMBER MEASURES.Ranking AS
RANK([Customer].[Customer].CurrentMember,
RankedCustomers)

SELECT
{[Measures].[Internet Sales Amount], [Measures].Ranking}
ON 0,
RankedCustomers
ON 1
FROM [Adventure Works]

This query against the sample Adventure Works cube comes back on my laptop in 8 seconds in warm cache.   Importing the tables into a Tabular model I can run the following DAX query which yields the same result, but comes back in 1 second. That is 88% faster!!

DEFINE
MEASURE FactInternetSales[SumSalesAmount] = SUM(FactInternetSales[SalesAmount])

EVALUATE
SUMMARIZE
(
DimCustomer,
DimCustomer[Name],
“Sales Amount”,
[Measure 1],
“rank”,
RankX(all(DimCustomer),  FactInternetSales[SumSalesAmount])
)
order by [rank]

If I query the same Tabular model from MDX, though, it performs just slightly better than the UDM query:  It runs in 7 seconds. In other words, MDX to both the UDM/OLAP cube as well the Tabular model performed the almost the same. DAX beat them both, which can perhaps be explained by the fact that in MDX it was necessary to order the set before ranking, whereas DAX has an optimized function for this (RankX).

So lets try a different query, one where the is no use of specialized functions.  Take a look at this purposely slow MDX query:

with member x as
Count(
NonEmpty(
[Reseller].[Reseller].Children *
[Customer].[Education].Children *
[Product].[Product].Children *
[Sales Territory].[Sales Territory Country].Children,
[internet sales amount])
)

select {x} on 0 from [adventure works]

Against the Adventure Works cube, and in warm cache, the query runs in 20 seconds. In DAX against BISM Tabular, the same result is achieved in 12 seconds.

define measure DimReseller[x] =

countrows(
Filter(
summarize (
crossjoin(
values(DimReseller[ResellerAlternateKey])
,values(DimCustomer[EnglishEducation])
,values(DimProduct[ProductKey])
,values(DimSalesTerritory[SalesTerritoryCountry])
)
,DimReseller[ResellerAlternateKey]
,DimCustomer[EnglishEducation]
,DimProduct[ProductKey]
,DimSalesTerritory[SalesTerritoryCountry]
,”Measure1″, SUM ( FactInternetSales[SalesAmount] )
)
, [Measure1] <> 0 )
)

evaluate

row (“test”, DimReseller[x] )

So even in this case in which I count crossjoined dimensions against a specific measure, I still get better performance in DAX.

However, when I try to query the same BISM Tabular model in MDX, I get a little surprised: The query returns in 1 minute 4 seconds!!!

with member x as
Count(
NonEmpty(
[DimReseller].[ResellerAlternateKey].Children *
[DimCustomer].[EnglishEducation].Children *
[DimProduct].[ProductKey].Children *
[DimSalesTerritory].[SalesTerritoryCountry].Children
,
[Measures].[Measure 1]
)
)

select {x} on 0 from [model]

Examining the profiler trace, the only difference between both MDX queries are the number of non empty calculations (Calculate Non Empty Current EventClass).  A trace on the query against the UDM/OLAP cube shows 7 of these entries, whereas the MDX query against the BISM Tabular model shows more than 14,000 entries on SQL Profiler!

image

In Summary, as BISM tabular will perhaps allow for for faster development compared to UDM/OLAP, it is tempting to think you could develop first in this mode and and then leverage MDX to query the OLAP interface of the model.  However, keep in mind that MDX queries over a Tabular model may not perform in the same way as queries to an UDM/OLAP cube.

About these ads

2 Responses to Observations on interoperability between BISM Tabular and OLAP clients

  1. Pingback: SQL Server “Denali”: Details on the next version of SSAS | James Serra's Blog

  2. Pingback: SQL Server 2012: Multidimensional vs tabular | James Serra's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: