About this blog

 
About this blog
Your Name 
SQL-Server

Thomas Ivarsson

Excel 2013 and the new Multidimensional Improvements

2012-12-17 16:18:00 av Thomas Ivarsson

 

This release of Excel 2013 brings many features that will improve analysis capacities on top of Analysis Services 2012 or BISM MD as it called in the SQL Server 2012 release. Let us have a look at some of the most important ones.

Power View will support Multidimensional Sources

This is not part of the Excel 2013 yet but at the time of the writing of this blog post it is part of Power View in SharePoint 2010 and 2013.

clip_image001

At the moment of this writing you can only create Power View reports on top of PowerPivot or Tabular data sources but MS has announced that the support for SSAS 2012 or Multidimensional will be available with in this Excel release.

Quick Explore

This feature makes it possible to go from a member like Customer-> Country (=Australia) and select a new dimension for the Australian members like Product Category.

Drill Down and Drill Up buttons in the Ribbon

There are several improvements that will make it easier to navigate a Pivot Table or Pivot Chart more quickly than in earlier versions. The Drill Down button will let you expand the level below the member that you have the cursor on. Drill Up will let you go up and see all members on the parent level.

clip_image003

This is how it looks in Excel 2013 after I have drilled down Australia.

clip_image005

Expand and Collapse

Expand will drill down each parent on rows in the Pivot Table. Under each country the next level will be shown.

clip_image007

This is the partial expanded list.

clip_image009

Recommended Charts

In Excel 2013, when you have created the Pivot Table, you can create the Pivot Chart by having the cursor in the Pivot Table area and select Insert on the Ribbon and recommended charts.

clip_image011

A window will open and you will see a list of all available charts. Note that scatter charts will be available in the Power View report update for Excel 2013 when it is available. The improvement here is that you quickly get a visual feedback of the chart on top of the Pivot Chart,

clip_image013

If you already know what kind of charts you want to create you can click on them in the ribbon without choosing the recommended charts. In this case you will see the chart created directly in the Excel sheet.

§clip_image015

Another important feature regarding charts is that you no longer will have to create a Pivot Table together with the Pivot Chart, like you can see in the first picture when you decide on report types.

Slicers can remove empty members

Below you see the behavior of Excel slicers before Excel 2013. Empty members will be shown but with another color.

clip_image017

In Excel 2013 you got an additional setting.

clip_image018

When you apply this the same slicers will look like this.

clip_image020

Timelines

In Excel 2013 we have a new slicer type for date dimensions called a timeline. In the example below I have created one with the years and in the first picture all years are selected.

clip_image022

When you select a single year the values will adjust accordingly. You can select one or several years by marking them in the timeline.

clip_image024

Office 2013 apps

At the moment of this writing you have a few Office apps that you can download an try for free in Excel 2013. You need a MS live account or some other authentication to log in

clip_image026

I have downloaded the heat map for US and mapped that to my Pivot Table data below.

clip_image028

MDX Support

Excel 2010 supports local named sets and Excel 2013 do both that and supports local calculated measures and calculated members. They are all supported in Excel Services in SharePoint 2013.

Comments

No Comments
Submit Comments
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: