SSAS 2008 Dynamic Sets in Analytic Reports

The primary aim of PPS SP2 is really platform support, particularly the addition of SQL 2008 as a data source. One of the subtle new features of Analysis Services 2008 is the support for dynamic named sets. Now that we can connect to 2008 cubes we can take advantage of them.

In 2005 we could create named sets but the members they contained were static did not change once the cube had been processed. This is probably why the 2005 AdventureWorks cube doesn't contain any TOP / BOTTOMCOUNT named sets - they were static and not of much use. In 2005 if we want a dynamic TOP n result set we need to write an MDX statement containing a session-scoped WITH SET statement.

WITH
  SET [Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10 
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].[Sales Amount] ON 0
,[Top 10 Selling Products] ON 1
FROM [Adventure Works]
WHERE
  [Date].[Calendar].[Calendar Year].&[2002];

If we wanted a PPS analytic report displaying TOP / BOTTOM n data from an SSAS 2005 data source we had to plug an MDX statement into the analytic report definition like the one above. Because we had to write manual MDX in order get the desired result the user lost their right-click interactivity in the deployed dashboard.

Enter SSAS 2008 dynamic named sets.  By setting a named set's Type property to Dynamic we now have a set that will recalculate its members based on the context of the query it is referenced in.

CREATE DYNAMIC SET CurrentCube.[Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10
     ,[Measures].[Sales Amount]
    ) ;

Because the named set is an object within the cube we can now build out an analytic report using drag & drop and don't have to touch the MDX to get what we need. As a result users have full access to the right-click menu.

Demo

I set up 3 analytic grid reports using a named set called Top 10 Selling Products and embedded them in a dashboard.

1. References a Top 10 Selling Products dynamic named set I defined in the AdventureWorksDW2008 cube
2. Manual MDX implementation of Top 10 Selling Products completely defined in the query tab of the Analytic View Designer (using the MDX code above)
3. References a Top 10 Selling Products named set that I defined in AdventureWorks for SSAS 2005 (a static named set)

I created a single date filter and hooked each of the grids up to it. As you can see from the shot below. At the All Periods level all three grids show the same 10 products and the same Sales Amount totals. All is well.

When we filter on a year, say CY2002, we see several things.

1. The SSAS 2008 grid is noticeably faster and completes rendering first with accurate results
2. The manual MDX implementation results match those returned by the SSAS 2008 version, as it should, but it was slower.
3. The members that make up the SSAS 2005 named set do not change. We get gaps for the products that we didn't sell in 2002 - like the Mountain-200... bikes. We must have sold plenty of them in later years as the static set put them at the top of the list for all time sales.

Now, let's try interacting with our two accurate grids. As expected the Manual MDX version has no drilldown capabilities. Remember that this limited drill functionality is by design - if you change even one character of the MDX on an Analytical report user interactivity features are disabled.

Now here's the good bit. Because the SSAS 2008 grid was built using drag & drop with no hand-altered MDX we have full right-click access to the data behind it.

So, if you're using (or about to start using) SSAS 2008 get stuck into Dynamic Named Sets and put them to use in your cubes.

The main advantages:

• Drag & drop analytic report design = faster, simpler development
• Enhanced user experience in the dashboard
• More centralized logic in the cube, less code in the element definitions
• Faster performance