Excel 2010 on SSAS 2008 R2 Named Set Creation

I have started with some tests of new Excel features as a client for SSAS 2008 R2. I am comparing with what was supported and not supported in ProClarity 6.3.

 

This blog post is about creating named sets in Excel 2010 with SSAS 2008 R2 as the server version. In Excel 2007 you had the built in feature to build groups for dimensions members but the result was a long response time before the groups appeared. In Excel 2010 it is a different story. You can create named sets by a graphical click of members or you can write custom MDX for the sets. This has been possible in ProClarity since several releases but in Excel 2010 this is new.

 

You start with connecting to SSAS 2008 R2 and create a Pivot Table in Excel 2010. I assume that you know about that step.

 

With the Pivot Table available and active you click the Field, Items and Sets button in the Ribbon. It is part of the Pivot Table tools in Excel 2010. Click on the Manage Set button. I have selected the two last years from the date dimension in the Adventure Works 2008 R2 cube project. I selected the Create Set Based on Column Items but that option is also available directly when you press the Field, Item and Sets button. Notice that you have option to Create Set using MDX option when you select the Manage Set option under this button.

 

 The graphical UI looks like this:

 

 

 

Delete the members that you do not need. You can also create a display folder under the dimension that you are building this set on.

 

The result look like this after I have removed 2005 and 2006 in Excel 2010.

 

 

Let us continue with something that is a new feature and big news in Excel 2010 that did not work at all in ProClarity 6.3

 

Is it possible to save named sets that are created from two different dimensions?

 

Here comes the create set using MDX option.

 

 

 I have crossjoined the customer.country members and the product.category members above. This set will appear under its own parent Sets in the Pivot Table Field List. The result is seen in the next picture.

 

 

 I have not continued with the issue why the totals disappears but that is a subject for another blog entry. I still think that this new feature will be helpful for Excel 2010 users.