Search results matching tags 'Analysis Services' and 'Excel 2007'

DefaultMember, subcubes and non-aggregatable attributes

sqlbi — Mon, 01 Oct 2007 21:35:41 GMT

Today I discovered that DefaultMember might result in a member other thant the default dimension member.

Reading documentation, I got the idea that DefaultMember would be always the default member defined for a dimension into a cube. In reality, the default member could change into a subcube that excludes the original default member from a calculation. This is important because writing MDX Scripts you cannot anticipate any possible use of your cube... and you should carefully consider when DefaultMember is the real appropriate syntax on a case-by-case basis. You have to be particularly careful whenever you use DefaultMember on a non-aggregatable attribute.

Let's look an example.

In Adventure Works you can write the following query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

This query provides this result:

I'm not interested in the real meaning of this query. This is only an excuse to see the DefaultMember in action against a non-aggregatable attribute. The DiffActual calculated member has a formula that use Scenario.Scenario.DefaultMember. The default member for Scenario.Scenario attribute is Actual. The DiffActual row shows the difference between the scenario in column and the Actual scenario.

Now, if we encapsulate the previous query into a subquery that apparently should return the same members, we can write this query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Forecast], 
      [Scenario].[Scenario].[Budget] } ON COLUMNS
FROM [Adventure Works])

Before executing this query, what is the expected result? First time I thought "it should be the same", but in reality it is different, like shown here:

What happened? Simply, the SSAS calculation engine needs a default member included in the "context" - when you use a subquery, you are defining a subset of the cube space that must have an existing default member for each attribute - if a default member is outside of the subcube, it is replaced by another member of the same attribute.

In the case we are exmining, the default member of the subcube becomes Forecast. This default member depends on the order of members of the same attribute used in the subcube. If we invert Forecast and Budget members only in the subcube, the default member becomes Forecast instead of Budget, changing the calculated member result (based on DefaultMember syntax).

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Budget], 
      [Scenario].[Scenario].[Forecast] } ON COLUMNS
FROM [Adventure Works])

This is the result for the query above:

To avoid this issue you can use a specific member instead of the keyword DefaultMember. In our example, the solution is:

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.[Actual], Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

With this syntax, any use of this query as a subcube in another query will not have the side effects we have seen before.

Why this is so important? Simply because Excel 2007, often used as a client by end users, makes heavy use of subqueries in MDX generated by PivotTable. I had to substitute all DefaultMember in MDX Scripts with well-known member names (this also affects my DateTool dimension, which I will update soon with this and other improvements).

Final note: a special thanks to Mosha Pasumansky for illuminating me on hidden secrets of DefaultMember. Mosha also said that using the member name of default member (instead of DefaultMember keyword) improves query performance.

DateTool dimension: an alternative Time Intelligence implementation

sqlbi — Sun, 02 Sep 2007 13:41:00 GMT

Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).

One trivial approach is creating a calculated measure for each combination of measure and date-calculation. While this works, it results in a multiplication of the number of measures, making the cube hard to use. Analysis Services offers a feature called Time Intelligence Wizard that creates some calculated members on dedicated Date attributes. This works in MDX but has several limitations:

· It limits the measures on which the calculation are applied (each time you add a new measure, you need to update the wizard-generated MDX scripts)

· It only applies to selected hierarchies – it doesn’t work, for example, if you cross Month and Years on rows and columns.

· It doesn’t work well with Excel 2007 after you install Analysis Services 2005 SP2 – see my rants here.

While the first two issues can be solved by using the same architectural approach of Time Intelligence Wizard, simply writing a different MDX Scripts, solving the third issue (Excel 2007 compatibility) requires a different architecture. For the sake of Google/MSN Live desperate users, I’m going to describe “how to make calculated members working on non-measures dimension with Analysis Services 2005 SP2 and Excel 2007”. I hope that Microsoft will consider a similar approach on a future version of Time Intelligence Wizard…

You can download the solution I'm going to describe on SQLBI.EU web site, under DateTool Project.

The issue

Let’s start describing the Excel 2007 SP2 issue. I created a small subset of Adventure Works sample cube. Calendar Date Calculations is the wizard-generated attribute. With BIDS browser (built using OWC 11) you can put this dimension on the columns of a pivot table with Calendar hierarchy placed on rows, and you can select any set of members from the Calendar Date Calculations attribute. In this example I unchecked one of the members generated by Time Intelligence Wizard.

With Excel 2007 the same cube is usable only if you enable the “Show calculated members from OLAP server” on the PivotTable Options dialog box (it’s unchecked by default).

Now we can generate a PivotTable similar to the one generated with BIDS browser.

Unfortunately, we cannot select single members from the Calendar Date Calculations attribute. We only have an “all or nothing” option based on the PivotTable Options settings we’ve seen above. This is the resulting Excel 2007 PivotTable.

The solution

I defined a dedicated dimension for time-related calculated members. Each formula has its own “real” members, instead of calculated ones. This solves the Excel 2007 issue at the price to require a dimension process instead of a simple MDX deployment (changing MDX Scripts for new calculated members wouldn’t require cube reprocessing). Since I don’t want a cube space growth, I simply put the dimension in the cube without a relationship with any measure group.

This is the real tricky and most non-intuitive part of the game. The DateTool dimension is seen as a regular dimension by any client, but it can be changed and reprocessed without any need to reprocess any measure group (because it has no relationship!).

The DateTool dimension could contain a single attribute with all time-related calculated members. However, I prefer an approach that uses two sets of different calculated members, which are orthogonal and might be crossed together. For example, I would put Year-To-Date calculation in one attribute and Year-Over-Year Growth in another one, so that I can obtain the Year-Over-Year Growth of a Year-To-Date calculation without the need to create a dedicated calculated member. In other words, I use these two sets to separate aggregations from comparisons formulas.

In practice, I would need two independent dimensions, and I do that from a relational point of view. I define two views in a separate schema on my data source (I could also use a separate Data Source View with self-contained named queries, but I prefer using relational views as a general way to decouple relational schema from multidimensional one – reasons for this would fill a whole dedicated article).

CREATE SCHEMA DateTool
GO

CREATE VIEW [DateTool].[DateAggregation] AS
SELECT 0 AS ID_Aggregation, 'Regular' AS Aggregation
UNION ALL
SELECT 1 AS ID_Aggregation, 'Year To Date' AS Aggregation
UNION ALL
SELECT 2 AS ID_Aggregation, 'Last 12 Months' AS Aggregation
UNION ALL
SELECT 3 AS ID_Aggregation, 'Total Current Year' AS Aggregation
GO

CREATE VIEW [DateTool].[DateComparison] AS
SELECT 0 AS ID_Comparison, 'Regular' AS Comparison
UNION ALL
SELECT 1 AS ID_Comparison, 'Previous Year' AS Comparison
UNION ALL
SELECT 2 AS ID_Comparison, 'Diff. Over Previous Year' AS Comparison
UNION ALL
SELECT 3 AS ID_Comparison, 'Diff. % Over Previous Year' AS Comparison
GO

Since I want to build a single dimension with two attributes, I create another view to build a junk dimension with the desired cardinality.

CREATE VIEW [DateTool].[DateTool] AS
SELECT 
    a.ID_Aggregation, 
    s.ID_Comparison, 
    CAST( ID_Comparison AS VARCHAR ) + ' - ' + CAST( ID_Aggregation AS VARCHAR ) AS Description
FROM DateTool.DateAggregation a
CROSS JOIN DateTool.DateComparison s
GO

At this point I can import these views in the Data Source View. I specify Primary Keys and Relationships manually because they can’t be inferred by relational metadata (they don’t exist at that level).

The resulting DateTool dimension is very simple.

At this point there is the necessary MDX Script that solves all DateTool members. This single part would require another post or two and I don’t have time to explain that now, but you can see the complete AdventureWorks based solution downloading it from SQLBI.EU.

The interesting part is the result provided by Excel 2007 with this solution. Here is an example of the Calendar hierarchy crossed with a selection of the calculated members available in the Aggregation attribute of the DataTool dimension.

With this dimension, there are no limitations like those involved by calculated members (see the previous example made using Time Intelligence Wizard). In the previous PivotTable there is a member unchecked, like you can see in the following picture.

Just using this technique you could translate each calculated measure generated by Time Intelligence Wizard into a “regular” dimension fully usable by Excel 2007 users. The presence of two independent attributes makes the user able to build more complex PivotTables like the following one: the highlighted column displays the Year Over Year Growth over the Year To Date value.

Undoubtedly, such a feature is comfortable more to advanced users that to inexperienced one, since often a rename of the resulting report is required to better describe query results.

Finally, one of the limitations of Time Intelligence Wizard is that generated formulas work on a limited part of the cube: only one time hierarchy and only a selected set of measures. With the MDX Scripts I used, there are no similar constraints, making the cube easier to navigate. In the following example, I put years on columns and months on rows, displaying the Year-To-Date value and the difference with the previous year for selected data.

Unfortunately, with this approach we lose compatibility with other clients, like the Browser provided by BIDS and SQL Server Management Studio. A PivotTable like the previous one would not display any data: the different way Excel 2007 and OWC 11 manage dimensions metadata produces this discrepancy between results obtained by difference OLAP clients. I am not sure if this can be solved just changing MDX Scripts definition – I made many tests until now and I described the best compromise I obtained until now, but I’d like to get feedback about issues, possible improvements and eventually alternative architectural solutions.

If you have time to take a look at the solution, please send me your feedback and/or any other comments at marco.russo (at) sqlbi.eu or using the comments for this post.

SSAS 2005 SP2 breaks Excel calculated member selection

sqlbi — Wed, 07 Mar 2007 20:25:00 GMT

After many tries, today I came to the conclusion that Analysis Services 2005 SP2 breaks the Excel 2007 calculated member selection feature. I already talked about this issue here, but today I found the way to reproduce the error.

This is a scenario that works well:

Analysis Services 2005 SP1 + hotfixes (build 2153)
Excel 2007 RTM
Cube with several calculated members on a hierarchy or attribute (not on measures)
When you build a pivot table with Excel, you can freely select single calculated members from the hierarchy or attribute.

Adding Analysis Services 2005 SP2 even only on the server, you get all the calculated members of the hierarchy or attribute selected, and they are all always visible.

In my opinion, this is a breaking change of SP2. I don't see any reason for this behavior. Remember, is is suffice to update the server to break calculated member selection on any Excel 2007 client, regardless of the client component version you use.

I posted a bug here: please vote the bug to get an higher rating if you think (like me) that this requires a promptly response from Microsoft.

UPDATE February 20, 2010: SQL Server 2008 R2 and Excel 2010 have solved the issue - you need both. Chris Webb posted a description of the fix.