Thanks everyone who attended, I hope it was helpful!
Here are some ways to follow the Virtual Chapter: Website: http://bavc.sqlpass.org/ Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w Twitter: https://twitter.com/passbavc LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113
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.
DAX (Microsoft’ Data Analysis Expressions Language) does not have a Substring function but I needed something like that for the following problem:
I had domain/username as input and I needed to extract just the username part of the string.
Input format: domain/username
output format needed: username
Input column name: UserID
so here’s the DAX formula I used: RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))
Note: The Formula is shown for demo purpose only, It may not work directly before making appropriate changes to the formula like making sure the column name is right & If the double quotes show problems, try deleting it & typing them back again.
Note that I combined some of the available DAX text functions to achieve what I was looking for. There might be other way to do this and I would be happy to learn about it too. Meanwhile, I hope if you reading this, this might give a good starting point while researching your DAX problem.
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.
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!
Without PowerPivo this is how the user was doing it: “Create one pivot table filtered by INVOICE (WEEK in Columns, CUSTOMER in Rows) and second table filtered by RETURN (WEEK in Columns, CUSTOMER in Rows). Then manually calculate INVOICED pivot – RETURN pivot.”
Let’s see how DAX formula in PowerPivot can help the user so that it eliminates the “manual” calculation.
A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:
Here’s the Data:
(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)
Formula (Calculated Measure):
[code language=”text”] Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day)) [/code]
Results via a Pivot Table:
Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel
Data Explorer let’s you “Explore” (search) for web-based public data. This is a great way to combine data that you may have in your data-sources with public data sources for data analysis purposes. Sometimes your data might not tell you the reason behind the observed trends, so when that happens – you can try to see if a public data-set might give you the much-needed context. Let me give you an Example before we start hands-on w/ data explorer so that you have better understanding of importance of public datasets. Here’s a sample that I found here. So, Here’s a demo:
An auto company is seeing sales trends of Hybrid cars and SUV’s from the sales data-sources. But what is the reason behind that? company data does not show that. Someone hypothesizes that it might be because of gas prices. So they test out the hypothesis by combining gas prices information available via public data. And turns out gas prices might be the driving force of sales trends! SEE:
if the gas prices increase, then the sale of SUV go down and the sale of Hybrids go up:
You know that public data can be helpful! So how can you search for public data-sets? Well, You can manually search online, ask someone, browse through public data repositories like azure data market (and other data markets), there’s also a public data search engine! OR you can directly search for them via Data Explorer.
Here are the steps:
1) Excel 2010/2013 > Data Explorer Tab > Online Search > type “Tallest Buildings”
2) I selected one of the data-sets that said “Tallest completed building…. ”
3) Now let’s do some filtering and shaping. Here are the requirements:
– Hide columns: Image, notes & key
– clean columns that has heights data
– Show only city name in location
OK, let’s get to this one by one!
4) Hiding Columns:
Click on Filter & Shape button from the Query Settings:
Select Image Column > Right Click > Hide:
Repeat the steps for notes & key column.
Click on DONE
5) clean column that has heights data.
Click on Filter & Shape to open the query editor
A) let’s rename it. Select column > Right Click > Rename to Height > press ENTER
B) let’s remove the values in brackets. Select Column > right click > split column > By delimiter > At each occurrence of the delimiter > Custom and enter “(” > OK
This should transform the data like this:
Hide height.2 and rename the height.1 to height
Click on DONE
6) Let’s just have city names in the location column
click on Filter & shape to load query editor:
A) select location > right click > split column > by delimiter > Custom – Enter: ° in the text box like this:
Now, how do select 32 – bit vs. 64 – bit? While 64-bit is recommended, you should have the 64-bit office to do that. Either case the version of the office installed should match the version of the PowerPivot installed. So how do you check that?
Excel > File > Help
3. Since I’ve 64 bit office, I’ll install the 64-bit version of PowerPivot. For that to happen, I downloaded the 64-bit version of PowerPivot.