Microsoft SQL Server Analysis Services 2005/2008 articles - MDX, performance, management

DAX Measures, MDX Measures and Type

author-chris-webb@ssas-info.com (Chris Webb) — Wed, 08 Feb 2012 22:50:32 +0000

Reposted from Chris Webb's blog with the author's permission.

One of the strengths of MDX is the fact that calculated measures in MDX are not strongly typed: they return variants. This means that it’s possible to create calculations that return values of several different types, such as the example below that returns text in some cases and integers in others:

WITH
MEMBER MEASURES.DEMO AS
IIF(
[Measures].[Internet Sales Amount]>7000000
, "TOO HIGH!"
, [Measures].[Internet Sales Amount])

SELECT
{[Measures].[Internet Sales Amount], MEASURES.DEMO}
ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

This gives you a lot of flexibility when writing calculations but it also can be a big problem when you want to extract data from a cube into another system, as anyone who has tried to do this will know.

In DAX and the Tabular model, on the other hand measures, like columns (calculated or otherwise) are strongly typed. Although you can’t see the type of a measure in SQL Server Data Tools when you create it, the automatically inferred type can be found in the MDSCHEMA_MEASURES schema rowset and which be queried as follows:

select *
from $system.mdschema_measures

If you try to use an expression for a measure like this that, as in my first example, could return text or an integer:

testtype:=IF(SUM(‘Internet Sales’[Sales Amount Value])>1000, 1, "test")

Then you get the error:

Measure ‘Internet Sales’[testtype] : The second and third arguments of function IF have different data types. This is not supported.

On balance I think I prefer having measures strongly typed, and for one thing it opens up the possibility of using the Tabular model for certain forms of ETL. I’ve already seen one customer of mine replace a CTE in TSQL by loading their data into Tabular and using the PATH functions instead, getting some significant performance benefits as a result, and I’m sure there will be plenty of other scenarios where ETL requires complex calculations to take place that the incredible performance of DAX will make loading all the data into a Tabular model (even if the final destination of the data is a relational data warehouse) a serious option.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .

SSAS 2012 RC0 DMX IMPORT bug Workaround

webadmin@ssas-info.com (Dejan Sarka) — Wed, 08 Feb 2012 17:48:58 +0000

In SSAS 2012 RC0 Dimensional, importing a model with dependencies using the DMX IMPORT statement does not import data source views from a backup made with SSAS 2005, 2008 or 2008 R2. Thus, the imported models cannot be processed and used. I tried with model exported from SSAS 2005 SP4 and SSAS 2008 R2 RTM. I successfully imported the same models in SSAS 2008 R2 RTM. Therefore, the problem must be in SSAS 2012 RC0. However, when I exported a model with dependencies from SQL 2012 RC0, I successfully imported it back to a new database in SQL 2012 RC0. This time also DSV was imported. it seems like import from earlier versions to 2012 RC0 has problems.

Tabular verse Dimensional Design Concepts

webadmin@ssas-info.com (Paul te Braak) — Wed, 08 Feb 2012 17:48:26 +0000

The multidimensional model appears to provide a richer environment for model design. However, for the case of snapshot fact tables, the tabular design may offer a much faster design and refreshing options. This post looks at the snapshot design and how business requirements are modelled in both multidimensional and tabular platforms.

Examining MDX Query performance using Block Computation

webadmin@ssas-info.com (Gautham KAMATH) — Wed, 08 Feb 2012 17:47:29 +0000

In 2007 I read a very interesting blog by Mosha outlining how to Optimize a Count(Filter(.)) expression in MDX. Since then I have been particularly careful in writing MDX queries and also make sure to run it past the MDX Studio to check it. But quite frequently I come across queries on forums and blogs that take the machine and its resources for a ride and still question why aren't my queries faster when I have enough resources (disk space, RAM, paging, processors etc.). Its quite simple at times to change the query but in most occasions it is simply not possible to change the way the query returns data. Lets first get a sense of how the Analysis Services processes MDX queries. There are two major parts of the Analysis Services engine.

SSAS: optimizing processing query using reference dimension relationship

webadmin@ssas-info.com (Hennie de Nooijer) — Wed, 08 Feb 2012 17:47:24 +0000

In one of my former posts titled "Select Facts with Reference and Many2Many relationships" i blogged about the usage of reference dimension relationships in cubes. In my current clientsituation i'm experiencing a major slowdown processing the cube. Some investigation learned that the reference relationsshiop seems to be the problem. In this blogpost i'll explain the problem, slowdown processing the cube, a solution, by replacing the load query and a conclusion.

Profiling SSAS : where have my milliseconds gone ?!?

webadmin@ssas-info.com (Guillaume Fourrat) — Tue, 07 Feb 2012 17:44:03 +0000

A short post to illustrate a pattern which I only recently clarified. Ever wondered why your "time" columns in a SSAS profiler trace had all .000 values for milliseconds (therefore giving you only a precision level of a second) ?

ProcessUpdate and Partition Scans

webadmin@ssas-info.com (Teo Lachev) — Tue, 07 Feb 2012 17:44:01 +0000

I'm working on minimizing the cube processing for a cube with some two billion rows in a fact table. I put together an ETL package that processes the dimensions with ProcessUpdate following by processing the latest partitions. I've noticed that processing one of the dimensions , which happens to be the largest (some 1 million rows) and most complicated dimension, resulted in partitions scans. The SQL Profiler showed the scans with the following events:

Memory Settings in #Tabular #ssas (and Prepare for #msteched!)

webadmin@ssas-info.com (Marco Russo) — Mon, 06 Feb 2012 22:20:30 +0000

We are going very close to the release of SQL Server 2012 and after using Analysis Services 2012 for almost one year in a relatively small number of scenarios, I'm really curios to see the questions and the issues that will emerge when its adoption will go mainstream. I expect that many will consider a Tabular instance of Analysis Services similar to a Multidimensional one, also from a system engineer perspective, whereas there are many important differences. For example, the hardware required to scale up an Analysis Services instance is different (very different) between Tabular and Multidimensional, but this will be the topic for a future post. By now, I just want to make some consideration about memory settings.

SSAS Cache Isn't Making Cents

webadmin@ssas-info.com (Tim Laqua) — Mon, 06 Feb 2012 22:20:28 +0000

I stole the pun from my Business Analyst, Mr. John Seiler Now on to my issue - when SSAS caches the value for my [Actual] measure, it seems to do so based on the results of the first query that requests that coordinate. In this particular cube, there's bunches of tiny fractions and depending on how you slice it, it aggregates a little different. This is a fun problem in itself, but the part that drives me (and the Finance department) crazy is that if you go and slice on something OTHER than that first query that created the cache, the values they see don't always add up to the "Grand Total" in Excel - aka. "All"

Working with SSAS traces and the dreaded Textdata in SSMS

webadmin@ssas-info.com (Andrew Calvett) — Mon, 06 Feb 2012 22:20:27 +0000

For good or bad i regularly work with SSAS traces that i have saved to a table and many years ago i was regularly frustrated by the fact that the contents of the textdata column containing the query is always truncated at 65535 characters.

You may think 65535 characters should cover most queries but once Excel gets to work it generates some huge queries!