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.
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
2) Download OLAP Pivot Table Extensions and Install it
In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP 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:
Now let’s add a calculated measure:
Select the Pivot Table > Right Click > OLAP Pivot Table Extensions
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
You can now see that the calculated measure Difference got added to the Pivot Table!
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.
There’s one thing common between Excel Macro and a Data warehouse: They help an analyst automate tedious tasks. Macro helps automate some of the common excel tasks. Data Warehouse helps analyst automate their “data cobbling/gathering” process. So recently I worked on a task to extract business logic from an Excel 2010 macro. Here’s what I did:
3) Now once the Microsoft Visual Basic for Applications Dialog box opens up, you should see a macro code. Now do NOT press F5 to run the macro! Instead, go to your first line of code and press F8, this will run the macro one line at a time. Open up your excel sheet that had this macro (in second monitor) and see what happens! [Productivity Tip: You don’t need secondary monitor for this but if you do then it will boost your productivity]
The key as you can imagine is to execute the macro one line at a time & visualize it the second monitor. it gives you a good sense of what’s happening (even if you don’t know how to write macro) and you should be able to understand the macro code or document it for data modeller or BI Dev’s so that they can encapsulate the logic in Data Warehouse/Cubes. Or you might be an excel pro trying to understand someone’s macro, then this trick works there too!
Note:
1) I’ve shown the steps that I took in Excel 2010. I haven’t tested if it works with other versions
2) I am not an Excel Macro Expert so If there is a better way, I’d be happy to learn about it. I just happened to figure this out & it helped me understand the logic hidden in a 1300+ lines of macro code.
That’s about it for this post. your feedback is most welcome!
In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP 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:
Now let’s add a calculated measure:
Select the Pivot Table > Right Click > OLAP Pivot Table Extensions
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
You can now see that the calculated measure Difference got added to the Pivot Table!
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.
That’s about it for this post! Your feedback is most welcome!
Microsoft announced a cloud based business intelligence platform called Power BI – as a part of that, the project (in public preview) that was previously called “Data Explorer” will be released as “Power Query”. It’s a great tool that have used to find, clean and shape data in Excel 2010, very useful! So one of the first things I checked was whether Excel 2010 can run Power Query or not. Turns out, it does! It works with Excel 2010 professional plus (Please read the system requirements on the official download page for details)
And of course, I downloaded and installed it on my Excel 2010 professional plus.If you’ve not installed Office 2010 SP1 or higher, do that too.
Please note that this change affects some of the blog posts that I’ve published on this blog, Here’s the list:
That’s about it for this post. Update your “Data Explorer” tab to “Power Query” if you haven’t already! It’s a handy tool and I am glad to see that Data Explorer Power Query runs on Excel 2010 Pro Plus!
Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:
Problem:
Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1
Here’s the data:
SALES DATA(2012 Q1)
East
West
Central
North
South
Coffee
$ 7,348.00
$ 7,238.00
$ 1,543.00
$ 9,837.00
$ 1,823.00
Tea
$ 9,572.00
$ 8,235.00
$ 3,057.00
$ 8,934.00
$ 13,814.00
Herbal Tea
$ 5,782.00
$ 8,941.00
$ 9,235.00
$ 392.00
$ 1,268.00
Espresso
$ 9,012.00
$ 2,590.00
$ 4,289.00
$ 7,848.00
$ 340.00
So it’s easy to give out answer using the data: $8934
But let me CHANGE the business question:
WHICH Products in WHAT regions are doing the best?
Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.
Solution
What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:
Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.
So how do you create data bars?
1. Select the data
2. Home > Conditional Formatting > Data Bars
3.Done! you’ll see this:
4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data
Conclusion:
Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!
I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:
First up, How is Trend-line useful? Here are few answers: – It helps us see how data is changing over time, in other words, it helps us find “trends” – It helps us forecast future.
With that, here is the chart without Trend-line:
Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:
1. select the line > right-click > add trend line
2. configure the trend-line options
3. I also changed the line style
4. And Here’s the chart w/ trend-line
Conclusion:
In this post, we saw how to add trend-line in the time series chart in excel 2010
