Report Portal

Excel 2010 on SSAS 2008 R2 Named Set Creation

Reposted from Thomas Ivarsson blog with the author's permission.

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.
 

thomas-ivarsson

Thomas Ivarsson has been working with the MS BI platform since SQL Server 7 in 1999. Presently he is working in the telecom industry in Sweden, with a data warehouse based on SQL Server 2005. From 1999 to 2007 he worked as a consultant also on the three SQL Server BI platforms. During the latest years he has spent most of time on SSAS, Reporting Services, ProClarity and Performance Point. He also has several years experience of the ETL process with DTS and SSIS. During 2008 and 2009 he has been working with introducing data mining in his daily business to see patterns in a service network behaviours. His blog can be found here: http://thomasianalytics.spaces.live.com

 

Tags: excel, mdx, set

 

2007-2015 VidasSoft Systems Inc.