When you create a PowerPivot workbook or a BISM Tabular model (new in Analysis Services 2012), you store data by using the VertiPaq engine, which is an in-memory columnar database. Instead of considering the row of a table as the main unit of storage, it considers every column as a separate entity and stores data for every column in a separate way. This makes it very fast to query data for a single column, but requires a higher computational effort in order to retrieve data for several columns of a single row.

VertiPaq is very fast and stores data in a highly compressed way. This is possible also thanks to its particular storage architecture. Every table you process is divided in columns, and every column has its own storage. For every column, a dictionary of all the distinct values is created, so that the real storage for that column will be a bitmap index that references the dictionary. Both dictionary and bitmap index are then highly compressed and are stored in a compressed way on both RAM and disk. The disk is used only as a sort of backup of data and all the queries are made by loading all of the column data in memory.

Exactly two years ago I wrote a blog post about how to optimize memory usage in PowerPivot and those suggestions where still valid today in any VertiPaq implementation:

• Reduce the number of columns
• Avoid high-cardinality columns
• Reduce precision of some numbers (the goal is reducing the number of distinct values)

Considering the possible ways to optimize a table in VertiPaq, the best approach is locating the largest column and trying to optimize it if you cannot remove it. It’s interesting to discover that possible optimizations require a completely counterintuitive approach, that would increase table size in a SQL Server table but can reduce space up to 90% in VertiPaq. The basic idea is that you can save this space by splitting the column in two or more columns with a smaller number of distinct values, and I described that in the new article Optimizing High Cardinality Columns in VertiPaq. I’d like to get feedback using this technique, especially on possible unexpected side effects!

In the meantime, I’d like to highlight the next upcoming events in which I’ll talk about PowerPivot and BISM Tabular (hint: even if you don’t understand Italian language, take a look at the conference web site and let me know if you don’t like it!):

• PowerPivot Workshop online on January 30-31, 2012
• Microsoft SQL Server & Business Intelligence Conference 2012 – March 28-29, 2012 (Milan, Italy)
• SQLBits X – March 29-31, 2012 (London, UK)

Analysis Services answers to query in MDX, in DAX (by now just for Tabular models) and has a limited capability to answer SQL queries. It is not useful for any development or client tool, but I wanted to write a blog post on it in order to be able to retrieve these information I gathered during study of DAX and MDX queries sent to Tabular models.

If you create an OLE DB connection using the MSOLAP driver you can send queries to BISM models according to the following schema:

• Multidimensional (aka UDM/OLAP)
• MDX
• SQL
• Tabular
• DAX
• MDX
• SQL

The SQL support exists mainly for creating local cubes from a Multidimensional model and it is also used by BISM Tabular model editor. The feature is not documented and not supported outside the syntax that is required for supporting local cubes (for example, it is used when you create an Offline cube in Excel).

You can see some examples of SQL syntax used to query an OLAP cube in two blog post of 2005/2006: one from Mat Stephen and the other from Chris Webb. You can use the same syntax also to query a BISM Tabular model. In such a case, you can also add the SQLQueryMode=DataKeys in the connection strings in order to improve performance, because there is a more direct translation into a Vertipaq request balanced by further restrictions in SQL syntax.

I haven’t found a good reason to use such a SQL syntax and the fact that it is not documented and not completely supported means that it is not something that should be used anyway. However, I think it is good to know what is the SQL support existent in Analysis Services (someone might remember some presentation of UDM in early days of SQL 2005 beta) and I’d like to get your feedback about reasons for which this feature should be supported (but before answering, please remember this is a very particular SQL dialect, with syntax like NATURAL JOIN and no support for many standard ANSI constructs).

The Microsoft TechEd Europe 2012 will be in Amsterdam this year on June 26-29. The day before (25 June 2012) there are several pre-conference seminars and I’m glad to announce that I and Alberto Ferrari will deliver this one:

Using BISM Tabular in Microsoft SQL Server Analysis Services 2012
[…] After an initial introduction of the overall architecture with the Microsoft platform, including SharePoint, PowerPivot and Power View, most of the time is dedicated to the definition of a BISM Tabular model. Learn about creating one from the ground up and then defining all the metadata required in order to obtain a rich semantic model that provides better user experience for data exploration in both Excel and Power View. You also learn the basics of DAX language and how to manage role based security and partitioning in a Tabular model. […]

Full-day pre-conference seminar at TechEd Europe 2012 - 25 June 2012 – Amsterdam

This seminar is introductive and is suggested if you (or some of your colleagues!) want to have a complete vision about BISM Tabular and its role in the Microsoft BI platform, including Power View and PowerPivot. We are also preparing other courses on BISM Tabular and DAX that we’ll start delivering them in Europe in April and we’ll announce in a few weeks. These will be 2-day courses dedicated to BI Developers and BI Consultants that want to get a deeper knowledge of Tabular, especially compared to the skills they might already have on UDM/BISM Multidimensional.

The DAX query syntax allows you to insert a parameter by using the @ symbol.

EVALUATE

CALCULATETABLE(

    'Product Subcategory',

    'Product Category'[Product Category Name] = @Category )

Depending on the client and on the driver you are using to execute your DAX query, this syntax may be supported or not. Here is a short recap:

• XMLA Execute Command: supported
• OLE DB MSOLAP driver with ADO connection: supported
• OLE DB MSOLAP driver with ADO.NET connection: not supported
• OLE DB for OLAP MSOLAP driver with ADOMD library: supported
• OLE DB for OLAP MSOLAP driver with ADOMD.NET library: supported
• Reporting Services with Analysis Services driver: supported using DMX editor - not supported in Report Builder
• Reporting Services with OLE DB driver: not supported (but a workaround is available)

Here is an example of an XMLA command that executes the initial DAX query passing Bikes as Category parameter.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">

    <Command>

        <Statement>

            EVALUATE

            CALCULATETABLE(

            'Product Subcategory',

            'Product Category'[Product Category Name] = @Category )

        </Statement>

    </Command>

    <Properties>

        <PropertyList>

            <Catalog>AdventureWorks Tabular Model SQL 2012</Catalog>

        </PropertyList>

    </Properties>

    <Parameters>

        <Parameter>

            <Name>Category</Name>

            <Value>Bikes</Value>

        </Parameter>

    </Parameters>

</Execute> 

Passing the parameter to the OLE DB MSOLAP driver doesn’t work in .NET because of a problem in System.Data.OleDb assembly, as described in a Connect bug that has never been fixed despite the “Fixed” state of the issue (please vote it!). As a workaround, you can use the ADOMD.NET library in .NET and you can find a working example written in C# in this article on SQLBI web site.

Finally, you can use DAX queries in Reporting Services by using the DMX editor in BIDS/SSDT/Visual Studio (you know, there are a lot of names for the same thing!). In fact, standard user interface for MDX only supports MDX queries, but if you select the "Command Type DMX" button (which is used to insert data mining queries) then you will be able to insert a DAX query with a parameter (remember to manually insert the parameters without the @ prefix by using the Query Parameters button).

However, you cannot use DAX queries at all in Reporting Builder by using the standard Microsoft Analysis Services Data Source with Report Builder, because the user interface doesn't support DMX and only supports MDX queries. Instead, you can use the standard OLE DB connection type, by passing MSOLAP as a provider name.

However, even in this case parameters in DAX queries are not supported, probably because of the same bug on System.Data.OleDb. Maybe that when this bug will be fixed, also Reporting Builder (and Reporting Services deisgner in Visual Studio) will work. In the meantime, you have to rely on string concatenation in order to pass a parameter to a query. In other words, you should set the Query.CommandText properties with the following expression:

= "EVALUATE CALCULATETABLE(
'Product Subcategory',
'Product Category'[Product Category Name] = """ & Parameters!Category.Value & """ )"

I hope that the OLE DB support from ADO.NET will be fixed by Microsoft. I understand that OLE DB has been deprecated, but I don’t understand why we should wait a months if not years in order to get an ODBC driver for Analysis Services instead of a quick fix for that. Reporting Services developer using DAX would really appreciate this gift!

UPDATE: I fixed the post on January 5, 2012 thanks to Greg Galloway that pointed me to DMX Builder in Visual Studio

In DAX you don’t have the LIKE operator and you have to use SEARCH instead. However, performance are not very good and it is better to use LEFT and RIGHT if you just need to compare the initial (or ending) match of a string.

I just wrote an article about string comparison in DAX. During my exploration of LIKE replacement functions in DAX, I have found that documentation of SEARCH for PowerPivot v1 is wrong – in DAX the SEARCH function is always case-insensitive, whereas FIND is always case-sensitive. Moreover, in PowerPivot v1 you had to use IFERROR to catch the string not found condition. This was a big issue in performance and in PowerPivot v2 (and in BISM Tabular) the new SEARCH and FIND functions have a fourth parameter that specify the value that should be returned when a match is not found. Using FIND seems to be 10% faster than SEARCH, just because it is case-insensitive.

I'd like to thank Marius Dumitru for the feedback he provided me in this analysis. Here are a few best practices he also suggest:

• Always avoid IFERROR and ISERROR (everywhere, not just for string functions).
• Use LEFT instead of FIND/SEARCH for expressing “starts with” conditional expressions on strings
• Use FIND instead of SEARCH if your comparison is (or can be) case-sensitive.
• When FIND or SEARCH are needed, use their new flavors taking a 4th parameter (added in SQL11)

If you are curious to see a few performance numbers, at the end of the post I includes the timing for a few query over a Tabular model based on an IISLog table. I included a best-practice table conversion in my article. If you think that a regular expression function would be useful in DAX, you can vote this suggestion on Connect.

-- Each DAX query is preceded by a comment with total execution time

-- The original condition over IISLog[cIp] column is this one in SQL:

--   WHERE cIp NOT LIKE '217.57.131.11%'

--     AND cIp NOT LIKE '11.22.33.3%'

--     AND cIp NOT LIKE '127.0.0.1'

--     AND cIp NOT LIKE '192.168.%'

--

-- Other conditions tested in a few queries are:

--     AND csUriStem LIKE '%SQLBI%Methodology%at%work%pdf'

--     AND csUserAgent NOT LIKE '%bot%'

 

-- 28 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[csUserAgent]),

            SEARCH( "bot", IISLog[csUserAgent], 1, 0 ) = 0

    ),

    FILTER( ALL(IISLog[csUriStem]),

            SEARCH( "SQLBI*Methodology*at*work*pdf", IISLog[csUriStem], 1, 0 ) <> 0

    ),

    FILTER( ALL(IISLog[cIp]),

      [cIp] <> "127.0.0.1"

      && LEFT( [cIp], 12 ) <> "11.22.33.3"

      && LEFT( [cIp], 8 ) <> "192.168."

      && LEFT( [cIp], 13 ) <> "217.57.131.11"

    )

)

 

 

-- 15 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[csUserAgent]),

            SEARCH( "bot", IISLog[csUserAgent], 1, 0 ) = 0

    )

)

 

-- 11 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[csUriStem]),

            SEARCH( "SQLBI*Methodology*at*work*pdf", IISLog[csUriStem], 1, 0 ) <> 0

    )

)

 

 

-- 9 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[csUriStem]),

            SEARCH( "SQLBI", IISLog[csUriStem], 1, 0 ) <> 0

    )

)

 

 

-- 54 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[cIp]),

            IFERROR( SEARCH( "217.57.131.11", IISLog[cIp] ), -1 ) < 0

            && IFERROR( SEARCH( "11.22.33.3", IISLog[cIp] ), -1 ) < 0

            && IFERROR( SEARCH( "127.0.0.1", IISLog[cIp] ), -1 ) < 0

            && IFERROR( SEARCH( "192.168.", IISLog[cIp] ), -1 ) < 0

    )

)

 

-- 16 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[cIp]),

            SEARCH( "217.57.131.11", IISLog[cIp], 1, 0 ) = 0

            && SEARCH( "11.22.33.3", IISLog[cIp], 1, 0 ) = 0

            && SEARCH( "127.0.0.1", IISLog[cIp], 1, 0 ) = 0

            && SEARCH( "192.168.", IISLog[cIp], 1, 0 ) = 0

    )

)

 

 

-- 14 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[cIp]),

            FIND( "217.57.131.11", IISLog[cIp], 1, 0 ) = 0

            && FIND( "11.22.33.3", IISLog[cIp], 1, 0 ) = 0

            && FIND( "127.0.0.1", IISLog[cIp], 1, 0 ) = 0

            && FIND( "192.168.", IISLog[cIp], 1, 0 ) = 0

    )

)

 

 

-- 12 seconds

EVALUATE ROW( "result",

  COUNTROWS(

    FILTER( DISTINCT(IISLog[cIp]),

            [cIp] <> "127.0.0.1"

      && SEARCH( "11.22.33.3", [cIp], 1, 0 ) <> 1

      && SEARCH( "192.168.", [cIp], 1, 0 ) <> 1

      && SEARCH( "217.57.131.11", [cIp], 1, 0 ) <> 1

    )

  )

)

 

 

-- 12 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[cIp]),

            [cIp] <> "127.0.0.1"

      && SEARCH( "11.22.33.3", [cIp], 1, 0 ) <> 1

      && SEARCH( "192.168.", [cIp], 1, 0 ) <> 1

      && SEARCH( "217.57.131.11", [cIp], 1, 0 ) <> 1

    )

)

 

-- 2 seconds

EVALUATE

CALCULATETABLE(

    ROW( "Rows", DISTINCTCOUNT( IISLog[cIp] ) ),

    FILTER( ALL(IISLog[cIp]),

      [cIp] <> "127.0.0.1"

      && LEFT( [cIp], 12 ) <> "11.22.33.3"

      && LEFT( [cIp], 8 ) <> "192.168."

      && LEFT( [cIp], 13 ) <> "217.57.131.11"

    )

)

 

 

You should have already read that DAX is simpler than MDX. It’s true, and sometime you have to pay a price for simplicity. I am used to say that DAX is somewhere between SQL and MDX. In fact, SQL requires you to put any relationship in the query (using JOIN or subqueries) whereas MDX can only leverage on existing relationships in the underlying data model, without any syntax that allows to create relationships “on the fly” during a query. DAX is in the middle because it can leverage on existing relationships (like MDX) *and* it can also join tables in a query definition (like SQL).

These differences might appear also in unexpected conditions. In the new article Ratio Over Subtotals with Normalized Tables in DAX I show how the calculation of a simple ratio (product vs. model and product vs. category) can be different in DAX according to the underlying data model and the shape of the query used to obtain the result. For example, the fact of showing the category name in the result might affect the way you write the corresponding ratio. After all, this is not so different if you think to a corresponding SQL query, but if you are used to write MDX queries, the effort required in DAX might surprise you.

I would like to your get feedback about these differences between DAX and MDX.

Just a breaking news: you can vote the sessions you'd like to attend at the next SQLbits in London! I already did my job and I have to say that it's very hard to use your 10 sesssions - there are too many sessions that I'd like to watch!

If you missed this, it is finally possible to publish your data on Windows Azure Marketplace. The name DataMarket is no longer present here also because the marketplace also include applications and not only data, but the news introduced by the last service update is that you can finally publish your data without having to run into a manual process with Microsoft.

I think this is a very good opportunity for all these companies that produced data sets for their own internal use but that can be interesting to a wider audience, even just for technical reasons like normalization and cleansing domain specific data. I’d like to investigate on TCO for publishing data “for free” – if someone already did the math, I’d like to get feedback!