SSAS 2012 Remove Zeros Using NullProcessing and Improve Query Times

In Analysis Services optimal query response times is key to a successful project.  The cube may contain all sorts of clever logic, processing times may be lightning fast, but if users are having to wait on long running queries each time they access the SSAS database they will soon tire of using it.

As fact and dimension tables grow over time, query performance can slow down.  There are many techniques for improving query performance - good partition design for measure groups, implementing natural hierarchies for dimensions etc.  However, there are also other techniques that often get overlooked.  One such technique is to look at the source data and decide if it is really necessary to import it into the cube. A smaller cube will generally outperform a larger one (all other factors being equal).

I recently worked on an SSAS database with a dimension that had grown to contain over 30 million members at the leaf level.   For any given day a subset of these members would be used - around fifty thousand would actually have data in the cube, and this was massively impacting query performance time for users – around 3 minutes were required to complete the query.

On examining the data returned by the SSAS queries, most of the measure values for these members was zero.  Only a few hundred records per day contained non-zero values.  In the source system relational tables the values were null, but in the cube they were displayed as zero.  As a result, to resolve the cube queries SSAS was having to read and aggregate all these zero values when in actual fact they were of no interest to the users.   If these zeros could be removed we could eliminate a huge chunk of data from the cube and reduce the overhead of processing, reading and returning all that unnecessary data.  

It may seem odd that the cube would convert null values in the source tables into zero's in the cube, but the key to this behaviour lies in the NullProcessing property.  As a simple example, let’s first create some dimension and fact data:

CREATE TABLE dimCustomer (custId int, CustName VARCHAR(25), CustCountry VARCHAR(25))

CREATE TABLE dimDate (DateId INT, CalendarDate DATE )

CREATE TABLE factMeasures (dimCustId INT, dimDateId INT, Sales INT, )

INSERT INTO dimCustomer

SELECT 1, 'Dave', 'England' UNION ALL

SELECT 2, 'Bob', 'Scotland' UNION ALL

SELECT 3, 'Jenny', 'Ireland' UNION ALL

SELECT 4, 'Jill', 'Scotland' UNION ALL

SELECT 5, 'Donny', 'Ireland'

DELCARE @t SMALLDATETIME= GETDATE()

INSERT INTO dimDate

SELECT CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT),CAST(@t-1 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT),CAST(@t-2 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT),CAST(@t-3 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT),CAST(@t-4 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-5, 112) AS INT),CAST(@t-5 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-6, 112) AS INT),CAST(@t-6 AS DATE) UNION ALL

SELECT CAST(CONVERT( VARCHAR(12), @t-7, 112) AS INT),CAST(@t-7 AS DATE)

INSERT INTO factMeasures

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 10 UNION ALL

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), 20 UNION ALL

SELECT 1, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), 20 UNION ALL

SELECT 2, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 30 UNION ALL

SELECT 2, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), 50 UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 3, CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT), NULL UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), 10 UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 4, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-1, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-2, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-3, 112) AS INT), NULL UNION ALL

SELECT 5, CAST(CONVERT( VARCHAR(12), @t-4, 112) AS INT), NULL

SELECT * FROM factMeasures

As the results show, we now have a simple schema with a fact table containing Sales values.  Note that for CustID 5, Donny, all values are null.

Next let’s build a simple SQL Server Analysis Server 2012 cube on top of this schema, using SQL Server Data Tools (SSDT).  The cube design looks like this:

After deploying and processing the cube, we’re now ready to browse the data by customer and date:

Note that Donny is listed with 4 days of data, with a sales value of zero.  The source system showed nulls, but the cube has converted that to zeros, in effect creating values where none existed.  In our example we would prefer that these cells in the cube are empty.  There may be a need to have the record in the source system, e.g. while this measure column for this row in the source table is null,  there may be an additional measure column for which there are valid figures for Donny. However, at present a NON EMPTY query will return these zeros, when we would expect Donny not to be listed at all.

To change this behaviour to that desired we need to examine the NullProcessing property for the measure. In SSDT open the Cube and go to the Cube Structure tab.  In the measures list on the left select the relevant measure and open the properties window (F4).  At the bottom of the list of properties expand the Source property:

By default the NullProcessing is set to Automatic,  which rather ambiguously means the Analysis Services server decides what to do.  Changing the NullProcessing setting to Preserve, will preserve the nulls from the source data.  Using the above example, setting this to Preserve and reprocessing produces the following results for the same query as before:

Donny no longer appears in the list, and the results have reduced from 48 cells to 18.  Although not apparent on such a small dataset, in the original example of 50k records taking 3 mins to return, applying the NullProcessing changes reduced query time to less than 5 seconds to return only a few hundred rows.

SQL UNPIVOT on Multiple Columns

Some time ago I posted about using the UNPIVOT function in a stored procedure here.  Recently I came across an interesting problem that required further use of the UNPIVOT command.

Suppose you have a number of columns in a wide table that you would like to unpivot into a single column and multiple rows. An example might be when there are many measure columns and you would like to narrow the table by combining all the measure columns into a single column. This would be a fairly standard use of the UNPIVOT command that was covered in the earlier post.

Now suppose that there are two sets of measures that you would like kept as separate columns,  Instead of a single MeasureName and MeasureValue column, you would like 2 of each.  Or perhaps a simpler example might not be measure, but dimensional data.  Let's look at CRM system data:

CREATE TABLE Company

(Id INT,

CompanyName VARCHAR(500),

Contact1 VARCHAR(500),

Contact2 VARCHAR(500),

Email1 VARCHAR(500),

Email2 VARCHAR(500)

)

GO

INSERT INTO Company SELECT

1, 'FastCarsCo', 'Mr Purple', 'Mr Orange', 'purple@fcc.com', 'orange@fcc.com'

UNION ALL SELECT

2, 'TastyCakeShop', 'Mr Brown', 'Mr White', 'brown@tcs.com', 'orange@tcs.com'

UNION ALL SELECT

3, 'KidsToys', 'Mr Pink', 'Mr Black', 'pink@kt.com', 'black@kt.com'

UNION ALL SELECT

4, 'FruitStall', 'Mr Red', 'Mr Blue', 'red@fs.com', 'blue@fs.com'

GO

SELECT Id, CompanyName, contact1, contact2, email1, email2

FROM Company

GO

In this example we have a company table with two contacts for each company, and an email address for each contact.  Instead of two columns for contact names and two for email addresses, we would like to reduce this to one of each: CompanyName, ContactName, EmailAddress.  First let's try and reduce the contact1 and contact2 columns to one:

SELECT ID, CompanyName, ContactName,Contact

FROM

(

SELECT ID, CompanyName, Contact1, Contact2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2)

) pvt

We have now replaced the contact1 and contact2 fields with a single ContactName field.  I have added the new Contact column to indicate the source of the data.  This can be removed from the select list if it is not required.

Next we need to perform a similar unpivot on the email columns.  This is actually easier that you might think.  Rather than nesting queries, or complex CTEs, we need only append our first query with another unpivot statement:

SELECT Id,

CompanyName,

ContactName,

EmailAddress,

Contact

FROM

(

SELECT Id, CompanyName, Contact1, Contact2, Email1, Email2

FROM Company

) src

UNPIVOT

(

ContactName FOR Contact IN (Contact1, Contact2 )

) pvt1

UNPIVOT

(

EmailAddress For Email IN (Email1, Email2 )

) pvt2

WHERE RIGHT(Contact,1) = RIGHT(Email,1)

The WHERE clause in the above query is very important.  Without it, we haven't told SQL Server how the second set of unpivoted data matches the first.  Without the WHERE , it will do a cross join, resulting in Mr Purple occurring twice; once with the correct email address, and once with the address of Mr Orange, his colleague.

In the WHERE clause we state that the new columns we have created, Contact (selected) and Email (not selected) should be joined on the last character. Remember that these two columns are populated with the names of the original fields, ie Contact1, Contact2, and Email1, Email2.  The two columns therefore align as Contact1 → Email1 (1=1) and Contact2 → Email2 (2=2).  Hence it is important that the names of the original columns in our source Company table contain text such that they can be joined together in the WHERE clause at the end of the query.

More details on the PIVOT / UNPIVOT command can be found here:

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

SSAS: Performance of MDX SCOPE() statement

Further to my recent posts on using SCOPE() statements in a cube and potential issues in using the SCOPE() statement, I came across another issue last week.

In a cube I was developing I had used a SCOPE() statement to ensure that when a particular measure was selected, the dimension member used (for one particular dimension) would be the datamember, so that the measure value displayed would be the "fact table" value and not the aggregated value of the children. (for more on using the DATAMEMBER function see this post).

In the calculation tab of the cube editor I wrote the following:

SCOPE([Measures].[MyMeasure]); THIS=[Portfolio].[Portfolio Name].CURRENTMEMBER.DATAMEMBER; END SCOPE;

However, in user testing accross large volumes of data this measure performed very slowly, taking over a minute to return results.

I tried experimenting with different approaches, and found that the desired results could be achieved without using the scope statement.  A calculated measure can be used, that contains the datamember function in its logic:
CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure2]
AS ([Measures].[MyMeasure,[Portfolio].[Portfolio Name].DATAMEMBER), VISIBLE = 1 ;

This measure produced the same result set but surprisingly the response time reduced to around ten seconds.

If anyone has had a similar experience and has explanation why using the DATAMEMBER function in a calculated measure rather than a SCOPE statement should perform faster please leave a comment and let me know!

SSAS 2008R2 - Scope and Assignments - "A set has been encountered that cannot contain calculated members."

In my previous post I discussed using the SCOPE statement in a cube.   I use it quite extensively in my designs and last week I came accross an unexpected issue with it.

I have a cube containing many measures for which I require different functionality under certain conditions.  Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.

The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:

 SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;

Where Area1 and Area2 are both measures defined in the cube.  The above scope would set them both to zero, but this is just for illustration.

The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:

 "A set has been encountered that cannot contain calculated members."

This can be confusing as the error message does not actually identify the SCOPE as the source of the issue.  But you can be fairly sure that that is where the problem is.

For some reason using more than one calculated measure in a single scope statment is a problem for SSAS.  If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:

({[Measures].[Area1],[Measures].[Area2]}) = 0;

Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.

Chris Webb also covers this here.

SSAS: How to run multiple XMLA scripts in a single batch - Processing

This post is a follow-on from this article:

http://sqlbanana.blogspot.co.uk/2013/08/ssas-how-to-create-multiple-partitions.html
-----------


Further to my last post, I thought it would be helpful to extend multiple script to commands to processing tasks.  As we explained, we can perform multiple commands inside a single Batch, and therefore run them at once in a single session.

In this short example we will see how we can easily process multiple partitions or dimensions in serial, parallel or any combination.

Similar to previous, we open the Batch tag and include everything inside.  The other tag to pay attention to is the Parallel tag.  Anything within a single parallel tag runs, surprisingly, in parallel.  Note that you still need a parallel set of tags for the items not run in parallel, but they will appear within their own individual start and end Parallel tags.

In the script below we are processing DIM1 and DIM2 in parallel and then afterwards DIM3 will be processed.  This can easily be extended to refer to measure groups, partitions etc.

Note that if an item in the batch fails, the entire batch is rolled back, not just the failed item.



<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration><
Parallel>
<!--
Process DIM1--><
Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM1</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process><!--
Process DIM2--><
Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM2</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel><
Parallel><!--
Process DIM3--><
Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM3</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel></
Batch>