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

Row security and hierarchies-part 1

webadmin@ssas-info.com (Cathy Dumas) — Mon, 21 May 2012 21:28:04 +0000

Fair warning: this is going to be complicated. In this post, I am going to assume that you've read the row security whitepaper, you are familiar with how parent/child hierarchies work in the tabular model, and you can deal with fairly complicated DAX concepts. For a primer on hierarchies, see Kasper's post. For DAX, I recommend a few months of experience after learning DAX. If you don't have that much DAX experience, let your eyes gently glaze over while I try to explain the DAX formulas, blindly copy/paste the formulas into your model, and hope for the best without trying to achieve true understanding (the latter is generally how I deal with DAX). Now to the post.

Binding Excel to a custom MDX query supporting PivotTable filters

webadmin@ssas-info.com (Christian Wade) — Mon, 21 May 2012 21:28:03 +0000

Chris Webb wrote this post: Binding an Excel table to the results of an MDX query. In a comment at the bottom, Scott Gall suggested using VBA. I want to take this one step further to allow custom MDX to be filtered by the filters from the Pivot Table Field List, while not actually using a pivot table (similarly to formulas mode).

Consolidation and Intercompany Elimination made easy using PowerPivot and DAX

webadmin@ssas-info.com (Gerhard Brueckl) — Mon, 21 May 2012 21:28:00 +0000

In my past years as a consultant for Microsoft BI I had to deal with financial models quite a lot. They are usually small in size but very complex in terms of calculations like currency conversion, margin-calculations, benchmarks and so on. Another topic I came across very frequently was consolidation and intercompany eliminations. Recently I had to deal with this topic again and thought of how this could be solved in PowerPivot/DAX.

SCOPING at different granularities (Part III)

webadmin@ssas-info.com (Javier Guillen) — Mon, 21 May 2012 21:27:59 +0000

In the last two blog entries on this topic (available here and here), we got a bit closer to reproducing - in DAX - the same output one would normally achieve with the MDX scope statement.

In this new entry, we perfect the formula a bit more by ensuring filter context propagates to the higher levels of granularity we are affecting.

What happens when a SSAS Tabular model exceeds memory?

webadmin@ssas-info.com (James Serra) — Mon, 21 May 2012 21:27:57 +0000

If you are using the Tabular model in SSAS, it will use the xVelocity technology to load your entire database in memory (greatly compressing the database). So what happens if your database is too big to fit in memory? You will get this error when you process the model:

"The following system error occurred: Insufficient quota to complete the requested service.

Excel 5-Calendar Date Table

webadmin@ssas-info.com (Colin Banfield) — Mon, 21 May 2012 21:27:55 +0000

For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a "portable" date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch. Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the "Ultimate Table" lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

Publishing Tabular Models to SSAS

webadmin@ssas-info.com (Mark Kromer) — Mon, 21 May 2012 21:27:53 +0000

This is a follow-up from my blog post last month here on SQL Server Pro about utilizing common traditional reporting tools like Excel and Report Builder with the new SQL Server 2012 Tabular models (http://www.sqlmag.com/blog/sql-server-bi-blog-17/business-intelligence/report-builder-tabular-model-databases-142942) . I've received a few notes about some confusion concerning the processes to publish PowerPivot-generated models and the Visual Studio SSDT-generated models to SSAS 2012. I was sent a question pointing to the currently empty Books on Line entry on the "Restore from PowerPivot" technique that I was advocating: http://msdn.microsoft.com/en-us/library/hh230822.aspx.

Named Sets and Block Computation in SSAS 2012

author-chris-webb@ssas-info.com (Chris Webb) — Thu, 17 May 2012 02:52:40 +0000

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

Greg Galloway (who really should blog more often!) recently came across an otherwise undocumented query performance optimisation in SSAS 2012: using named sets inside aggregate functions in MDX no longer prevents the use of block computation/bulk mode. This was something that was explicitly called out as a Bad Thing To Do in Books Online (the link to the page in question is now dead though, possibly because it’s being updated), but here’s an example of a query that will now run much faster in SSAS 2012 Multidimensional than it used to in R2:

with
set myset as {[Customer].[Customer].[Customer].members}
member measures.demo as
sum(myset,[Measures].[Internet Sales Amount])
select measures.demo on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

There are still situations where block computation can’t be used however, namely when the Current() function is used (which are going to be very rare I think):

with
set myset as {[Customer].[Customer].[Customer].members}
member measures.demo as
sum(myset
, iif(myset.current is [Customer].[Customer].&[20075]
, 0, [Measures].[Internet Sales Amount])
)
select measures.demo on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

Thanks also to Jeffrey Wang for the background on this.

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 .

Filters aren't applied to related tables in PowerPivot unless a measure is added to the PowerPivot Workbook

webadmin@ssas-info.com (John Desch) — Wed, 16 May 2012 22:55:16 +0000

A colleague, with whom I work on a regular basis, recently pointed out a somewhat unexpected feature of PowerPivot PivotTables. What my colleague was seeing was that when creating a PowerPivot PivotTable, placing a column from one of the Dimension tables on rows then adding a column from a related (Parent) table in one of the slicers and selecting a specific member from the slicer the action did not apply a filter to the related dimension that had been placed on rows. When a measure was added to the PivotTable, however, the dimension that was on rows the filter was magically applied. This behavior is relatively easy to reproduce using a few tables from the Adventure Works relational database. So for those who want a reason to just play with PowerPivot (take your pick of the version), here are some relatively simple steps to reproduce the behavior.

Controlling the Position of Subtotals in DAX with GenerateAll()

author-chris-webb@ssas-info.com (Chris Webb) — Wed, 16 May 2012 02:17:14 +0000

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

Today I’m teaching the SSAS 2012 Tabular workshop with Alberto Ferrari in Belgium, and an interesting question came up during my session on DAX queries to do with subtotals that I couldn’t answer immediately. However, I found a solution fairly quickly afterwards and so I thought it was worth blogging about – especially since I think it’s a better solution than the one that Alberto knew about already!

Consider this DAX query that runs on a table sourced from the DimDate table in Adventure Works:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
)

It returns the number of Days in the table by calendar year and day number of week – it’s very similar to a basic GROUP BY query in SQL. I blogged about this use of Summarize() and Rollup() last year here.

The problem with this query is that all of the year subtotals (which appear as rows with blank values returned in the day number of week column) created by this query appear at the end of the result set, as you can see here:

This isn’t very clear though. How can we put each year’s subtotal at the end of the distinct list of day numbers instead? Alberto’s solution (and I think this is the solution we’ve got in our new SSAS 2012 Tabular book) involves using the IsSubtotal() function (see here for more details on this) and ordering, similar to this:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
, "Is Subtotal"
, IsSubtotal(DimDate[DayNumberOfWeek])
)
order by
DimDate[CalendarYear] ASC
, DimDate[DayNumberOfWeek] ASC
, [Is Subtotal] ASC

But I thought there was an alternative, more elegant approach and found one. Here it is:

evaluate
generateall(
values(DimDate[CalendarYear])
,
summarize(
DimDate
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, calculate(countrows(DimDate))
)
)

As you can see, the subtotals appear after the list of day numbers for each year. There are two important things to understand about how this query works:

I’m using the GenerateAll() function to take the list of distinct years returned by the Values() function and then, for each year, return a table of day numbers with a subtotal and crossjoin the result. This gives us our subtotals in the correct position without any sorting required.
I had to wrap my original countrows() with calculate to make sure it returned the correct value.