How to sort measure values in an excel 2010 based ad-hoc report using SSAS cubes?

Standard

I helped a user today to sort measure values in his excel based ad-hoc report using SSAS cubes & so I’m posting the tips here for note-keeping. There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

sorting Excel ad hoc report ssas cube#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

sorting data excel analysis services cube

Conclusion:

In this post, I shared two ways you can sort the measure values in an excel based ad-hoc report using SSAS cubes. Do you allow excel based ad-hoc reporting over SSAS cubes in your organization?

 

How to Flatten Data in Excel Pivot Table?

Standard

How to flatten data in Excel Pivot Table?

By default, its hierarchical but I want the data in a Tabular/Flattened format. How do I do that? I am going to show how to do that with Excel 2010.

Default View (Note the Hierarchical view of Pivot Tables)

Before Pivot Table in Compact Form

BEFORE

After I Flattening it, It should look this:

After Pivot Table Tabular Form Flattened

AFTER

Note: it also depends on data, not every data can be represented in the flattened view. In above example, we’re analyzing each product (evident by product id) and so it supports the flattened view.

here are the steps:

1. Select Pivot Table

2. From the Toolbar, Turn off Subtotals. Go to Pivot Table Options > Design > Sub Totals > Do not show Subtotals

3. Go to Pivot Table options > Design > Report Layout > Show in Tabular Form.

Show in Tabular Form Pivot Table

That’s about it. once you do that, you should see data in flattened format.

How to add calculated measures to Pivot Tables using OLAP Pivot Table Extensions add-in?

Standard

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT

Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

excel version 32 bit or 64 bit

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Excel Pivot Table Extensions

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Excel Simple Pivot Table OLAPNow let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

OLAP PIVOT TABLE EXCEL EXTENTSIONS

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

Simple Formula OLAP excel calculated memberYou can now see that the calculated measure Difference got added to the Pivot Table!

Excel olap pivot table with calculated memberNote

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

That’s about it for this post! Your feedback is most welcome!

 

How to Change Data Source of an Excel 2010 Pivot Table?

Standard

Problem:

There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?

Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.

Solution:

1. Establish the connection to a NEW data source via Data Tab in Excel.

2. Select the Pivot Table whose connection needs to be changed.

3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source

Pivot Table Change Data Source4. Choose the NEW connection from here. (you can use the connection that you created in step #1)

Choose the new data source Pivot Table5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.

That’s about it for this post. Your comments are very welcome!

How to Track SSAS cube usage by End-User Tool?

Standard

There’s a SSAS cube.

User’s use more than one end-user tool to access the cube.

How do you track the usage by End-User Tool?

Problem:

By Default the SSAS Trace does not capture all End-User Tools like Excel, SSRS, Third-Party Tools.

So what do you do?

There’s a connection string property called Application Name that you can use to capture this information. Once you set it, the application name property will start showing up in the SSAS Trace:

SSAS Cube Connection String Application Nameyou’ll have to modify ALL connection strings used to point to the cube though.

SSAS Cube SSRS Excel usage

Credit:

Erik Veerman

Resources:

Connection String Properties (Analysis Services)

 

Using Excel as BI Tool? Consider 64-Bit version.

Standard

Excel as Business Intelligence Tool. What do I mean?

For the purpose of this blog post, If you’re creating Excel based reports against Power Pivot Model, SQL Server Analysis Services data or creating reports by combining data from other data sources – then you are using Excel as a Business Intelligence Tool.

What’s the problem?

when you’re using Excel as a Front end tool for creating Business Intelligence reports – there’s a chance that you’ll be bringing in more data than excel could handle which would create “performance problems”.

How can 64-bit help?

Performance power offered by Excel 64-bit > 32-bit version.

Why? Because of the advantages that 64-bit computing has over 32-bit computing

How can YOU decide whether you need 64-bit version? (FOR BI FOLKS ONLY)

(prerequisite: your OS should be 64-bit)

If you don’t have Excel & planning to get it. Consider 64-bit!

If you already have Excel & it’s 32-bit – Do you see slow performance when you create your reports against Power Pivot model or SSAS? Do you have more than 4 GB RAM in your machine? YES? Try 64-bit.

What’s the down-side of 64-bit?

There are compatibility issues with 32-bit Excel add-ins. Read more here: Choose the 32-bit or 64-bit version of Office

Microsoft does not recommend using 64-bit excel.

What version of Excel support 64-bit?

Excel 2010 & onwards.

Case Study

In my current project, a Business Analyst who was creating reports against a SSAS (SQL Server Analysis services) cube & was seeing 1-2 min delays in “data refresh” each time filter values used to change. Clearly, She was not happy!

I looked at her computer configuration. She was using:

  • Excel 2010 32 bit
  • 8 GB RAM
  • 64 Bit edition of Windows 7

so I recommended 64-bit version of Excel. After the version was upgraded, we tried again! This decreased the time delays by 25x. The data was now getting refreshed in 2-4 seconds!

Slicing/Dicing data in multiple Excel Pivot Tables with ONE slicer:

Standard

Background:

This week I created a couple of Excel Dashboard’s for a project that I am working on. As a part of the that, I decided to include slicers for these Excel Dashboards. And the reason I did that was because data in multiple pivot tables needed to be sliced with one click. In this blog post, I’ll show you steps to connect an Excel slicer to multiple pivot tables:

Steps for Excel 2010:

1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click

2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:

two excel reports slicer 1

Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.

3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)

4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar

pivot table charts tools5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers6. you’ll see a slicer on your sheet now:

slicer on pivot tables

7. Let’s format the report to make it look better:

#1: move the slicer such that it does not overlap any of the pivot tables.

#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:

select the slicer > from the toolbar > slicer tools > column

slicer tools option excel

#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:

8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables

9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:

pivot table connections excel

10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:

#1: Sliced by Bikes

excel pivot analysis data 1

#2: Sliced by Accessories

excel pivot analysis data 2That’s about it for this post. Your comments are very welcome!

Related Posts:

Comparing Slicers in Excel 2010 to Standard PivotTable Filters