Questions Power Users Ask about Excel: #4 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #4 of N:

a. How to Filter Measure Values?

Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:

1. Select the Pivot Table.

2. click on a drop down menu besides “row labels”

Excel Pivot Table Value Filter3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”

Pivot Table Excel SSAS cube filter values measure4. On the next dialog box, you can select the measure, rule & the filter value:

Pivot Table excel ssas cubeAfter you’ve set them up, click on ok and the Pivot Table will filter by measure values.

Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.

b. How to move a Pivot Table?

If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:

1. select the Pivot Table.

2. In the menu bar, go to Pivot table Tools > Move Pivot Table:

Pivot Table options Excel Move

3. select your NEW destination and click on OK.

Move Excel Pivot Table 2010That’s it!

Conclusion:

In this post, we saw how to filter measure values & how to move Pivot Tables.

In earlier articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.

Three Power Pivot Installation FAQ’s:

Q1: How Can I upgrade Power Pivot on my machine?

A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.

Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010″?

A2: Yes, go ahead! “SQL server 2012 sp1″ in the name is confusing :) It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.

Q3: How do I check Power Pivot version?

A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.

Power Pivot Settings Excel

I hope this helps!

Questions Power Users Ask about Excel: #3 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #3 of N:

a. How to remove Grand Totals & Sub Totals?

1. Select the Pivot Table.

2. From the toolbar, go to Pivot Table Tools > Design > Sub Totals > Do not Show Sub Totals.

you can also choose to remove it just for rows or columns. it depends on your requirement on how to layout data.

Pivot Tables Sub Totals How to remove

Similarly, you can remove Grand Totals:

Pivot Tables Turn off Grand TotalsAlternatively, you can remove grant totals from Pivot Table options:

Grant Totals Pivot Table Options

There’s also an alternate method for removing subtotals. Put your mouse over the hierarchy level for which you do not need subtotals. Right click > remove subtotals “field name”. But you’ll have to do that for all hierarchy levels if you need to remove all subtotals.

b. How to configure Automatic Data Refresh?

A common question asked by power user is how to make sure that the excel file is pulling the latest data from the cube? Good news for them is that Excel files that are configured to connect to a data source like SSAS cube can be configured to automatically refresh. Here’s how:

1. From the toolbar, Go to Data > Connections

Excel Data Connections Cube Properties2. Select the connection that interested for automatic data refresh > properties

Excel SSAS Cube Property connection3. From here, you can configure the file to do an automatic data refresh every xyz minute and/or configure the file to refresh data every time you open it.

Excel Data Refresh Cubeclick ok when you’re done and close the workbook connections after you’ve configured the data sources that you needed for automatic data refresh.

Conclusion:

In this post, we saw how to remove grand totals & subtotals and, how to configure the automatic data refresh.

In previous articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

Questions Power Users Ask about Excel: #2 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #2 of N:

How to add calculated measures (Excel 2010)?

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 OLAP

Now 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 member

You can now see that the calculated measure Difference got added to the Pivot Table!

 

Excel olap pivot table with calculated member

Note

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.

Conclusion

In this post we saw a very common question “How to add calculated columns” asked by Power users about excel while they analyze data from SSAS cube.

Previous Article: http://parasdoshi.com/2013/12/10/questions-power-users-ask-about-excel-1-of-n/

Questions Power Users Ask about Excel: #1 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #1 of N:

a. How to Sort Data?

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

b. How to add slicers?

They might not know what slicers are but I’ve been asked how can I add filters where users can see the filter values before picking the value. I’ve also been asked by a Power user for the capability for adding “global filters” who had couple of pivot tables in a single sheet. They also come in handy if you’re building Excel Dashboards. With that, here are the steps:

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

5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers

6. 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 2

c. How to change the layout of the Pivot Tables?

Without formatting and changing the layout, the excel pivot table looked like this:

step 1 excel pivot table from power pivot

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Pivot Table Tools Design Excel

Step 2: Now here, Go to Report Layout > Show in Tabular Form

Pivot Table Layout Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

half way pivot table layout

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

pivot table hide expand collapse button

Step 5: so now it looks as follows:

excel pivot table power pivot

To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.

Conclusion:

In part #1 of N, we saw:

1) How to sort data?

2) How to add slicers?

3) How to change Pivot Table layout?

 

Quick Note about the Filters in the Power View Report:

You can put the fields as filters in two different areas:

1. View

2. Chart/Table

An end-user asked me the difference between the them today:

Filter area Power View Chart TableIt’s helpful to have more than two chart/table in a view (think of view as one excel single sheet) to understand the difference. So let’s say you have two charts in a single VIEW:

How do you filter data in both charts?

By putting the field in the VIEW area.

They can be called View-Level Filters

How do you filter data in just one of the chart/table?

By putting the field in the Table/Chart area.

They can be called Visualization-Level filters.

Official Resource:

Filtering, Highlighting, and Slicers in Power View

[Notes] Atlanta Business Intelligence User Group – Mapping Data in Power View & Tableau

Here are my notes from the Atlanta BI user group that I attended today:

Topic: Head-to-Head on Maps: Mapping Twitter in Tableau and Power View

Power View

Tableau

Setup/Installation? Office 2013 [Professional Plus editions], SharePoint 2010 onwards [Enterprise Editions] Public, Online, Desktop, Server
Licensing Get applicable office/SharePoint license Tableau Public is Free.Tableau Online is $500 per user/year.

Tableau Server has Per-User or Per-Server-Core model

*Note that if you’re using Tableau server’s per-user model the cost is $1000 per named user/consumer (min. 10 users) and $2000 seat for 1 developer.

**Contact Tableau Sales. Please don’t evaluate the product based on the information provided here as the information might change in future

Data Sources [SharePoint] Power Pivot Model, Analysis Service Cubes[Excel 2013] You can connect to data sources that excel supports and then create power view report on top of it. Tableau Public Supports Excel, Access & Text filesOther version of Tableau can connect to a variety of data sources.
Product Category Ad-hoc reporting a.k.a Self Service Business Intelligence Ad-hoc reporting
Ease of use easy easy
Visually Compelling? Yes Yes
Custom Shape Files No Limited
Motion effect No Yes
Product maturity Power View is new to the game Tableau is a leader in data visualization space
Additional Notes Power Map is an add-in by Microsoft that focuses on “Mapping”. It’s part of the newly announced Power BI suite. -

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

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?

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?

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!