Power View for Geographical Analysis with Excel 2013

Earlier this month, I have presented “Power View for Geographical Analysis” as part of my Pacific Northwest US tour. My apologies for posting this fairly late in the month, following the presentations, as I have a few other commitments earlier this month.

Without further ado, below are the sample files:

1. Earthquake Last 30 Days

Download Excel 2013 file.

The data is retrieved from the USGS website. This particular workbook was manually prepared (i.e. data copied and pasted from the webstite). The workbook has been cleansed and manipulated using Excel flash fill functionality and contains from 11 September 2012 to 11 October 2012.

The workbook does not retrieve the data from the USGS website automatically.

Earthquake Analysis - About 1000 miles radius of Portland

Earthquake Analysis - About 1000 miles radius of Portland


Earthquake Analysis - by the hour

Earthquake Analysis - by the hour


2. PASS Chapter and SQL Saturday Growth

Download Excel 2013 file.

Taking the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them together.

There is also a section here that shows implications of incomplete Location hierarchy. In this example, Australia has complete Country – StateProv – City definition, whereby Singapore is missing StateProv information.

PASS Chapter and SQL Saturday Growth

PASS Chapter and SQL Saturday Growth



Data Integrity - Incomplete StateProv Level

Data Integrity - Incomplete StateProv Level


3. Internet Sales by Customer Occupation

Download Excel 2013 file.

A simple Adventure Works sample which shows well structured data model with good quality data. In this case we analyse Internet Sales by an attribute that doesn’t directly link to the sales, such as Customer Occupation.

Internet Sales by Customer Occupation

Internet Sales by Customer Occupation


Wrap up

These sample files contain static data – i.e they do not retrieve data from data sources auotomatically. Hence the data is as at the time of preparation. The aim of the sample files is to provide an overview of Geographical Analysis techniques using Power View in Excel 2013. As I have mentioned in my sessions, having good data model and data quality is important to build good interactive analysis using Power View. However, in real life, we may be far from a perfect data quality as shown in the Earthquake example and the PASS Growth example. However, with creativity we can generate something meaningful for analysis.

Hope you enjoy the sample files.

Note: the workbook should work with Office 2013 Preview, if it does not, please let me know.

My version of Excel 2013 is: Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.



Measure Selection Using Slicers – Excel 2013

I enjoy reading Jason Thomas’ blog, especially on SSRS. He has very good techniques that he describes very well.

In his latest post, Measure Selection Using Slicers, using Excel 2010 he explores how you can allow measure selection using slicers, which I think is really great. I have used the technique multiple times in Tabular Model.

The Excel 2013 version of his is here, in case you are thinking of testing it out on Excel 2013. I upgraded the PowerPivot first when I opened the original workbook in Excel 2013. Below is a reusable set of steps that you can use to upgrade an Excel 2010 workbook with PowerPivot to Excel 2013.

Caveat: upgrading from Excel 2010 to Excel 2013 may be problematic Read Jason Thomas discussion here. This discussion is related to having different Excel table names to the PowerPivot table names.

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

1. Open the workbook. in Excel 2013. Ensure you click on “Enable Content”

Open Excel 2010 workbook in Excel 2013

If you click on one of the slicers, you may get an error similar to the following.

“The workbook has a PowerPivot data model created using a previous version of the PowerPivot add-in. You’ll need to upgrade this data model with PowerPivot for Excel 2013″.


2. Click on PowerPivot tab, and choose Manage

Click on "Manage" PowerPivot

You will get a warning similar to when you click on a slicer. Clicking OK will open up a dialog box to upgrade.


3. Click OK on the PowerPivot upgrade option.

Upgrade PowerPivot to Excel 2013 Dialog Box


4. Once PowerPivot is finished upgrading, a dialog box will appear to notify you that the workbook is still running in compatibility mode. Choose Yes to upgrade the workbook fully.

Upgrade the Workbook to Excel 2013


The workbook will be closed and reopened as an Excel 2013 version.

The version I am using is Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

Update: I spoke with Jason Thomas and we confirmed that we are running different versions of Microsoft Office 2013. I have downloaded mine from my Technet Subscription which is the Microsoft Office Professional Plus 2013, whereas he obtained his copy from Office Preview website.



My 2012 US Tour with #SQLFamily

My Pacific North West US tour earlier this month officially concluded on 11 Nov 2012. I am very honoured and grateful that I met new friends, caught up with old friends and delivered 4 presentations too. The greatest thing about this trip is the community and networking, especially at SQL Saturday 172 in Portland, PASS Summit 2012 in Seattle, and SQL Saturday 166 in Olympia.  The sessions delivered in these two SQL Saturday events were superb and attendees were so eager in learning.

Last year, I was just a book-worm (or a session worm?) where I went to every single session slot, as I had two very specific aims; i.e. learning SSAS performance tuning and finding out more about SQL Server 2012. I achieved what I wanted and was really happy to find a gateway to learning. I met lots of new people during the sessions too.

This year, I decided to take a different approach. Throughout 2012, I have talked to people from other parts of the world via twitter, LinkedIn and emails. So, it was important during PASS Summit 2012 for me to meet them, and to thank them in person for sharing their thoughts and opinions. The result is, throughout my US trip, I met more people, from different parts of Australia, United States (outside of Seattle), United Kingdom, Germany, Portugal, Spain, Mexico, Denmark, Slovenia, Philippines, Netherland, Norway, Brazil, New Zealand, to name a few.

One thing that I realise is that we are a family, #SQLFamily; we are diverse in knowledge, expertise and other aspects of life, but we are one that we share passion in knowledge sharing and learning. So, I am proud to be part of #SQLFamily and that every time I am around my #SQLFamily, I feel home.

As an Alumni Mentor for the First Timers group at PASS Summit 2012, I sent a few tips. One of them was that everyone’s experience for PASS Summit would be unique. You are entitled to have different experience from your peers. The most important thing is that you are having a good time and learning. I had a blast! So, I’d like to thank the sponsors, organisers, volunteers, speakers and #SQLFamily in making PASS Summit 2012 happen.  The feedback that I have received from other speakers, my First Timers group and attendees have been nothing but positive. So, once again, many thanks.

How do you rate your PASS Summit 2012 experience? Share your thoughts here and let PASS know!

Last but not least, I would also like to extend my thank you to SQL Saturday 172 and SQL Saturday 166 organisers, sponsors, volunteers and attendees.


Highlights from PASS Summit 2012

WIT Luncheon – PASS Summit 2012 by Sarah Strate

PASS 2012 Day 1 – Interviews and Keynote

PASS 2012 Day 2 – Keynote and Session Recording (Query Tuning Mastery with Adam Machanic)

PASS 2012 Day 3 – Interviews and Session recording (Working with Claims and SQL Server BI Technologies with Adam Saxton) 

PASS Global Community Video

PASS 2012 Keynote and Mobile BI Announcement by Marco Russo

PASS 2012 Announcements by James Serra




WIT Luncheon at PASS Summit 2012


Moderator for today’s luncheon is Wendy Pastrick, and the luncheon is spoonsored by SQLSentry, Inc.

Panelist Bios are available from http://www.sqlpass.org/summit/2012/Connect/Luncheons/WomeninTechnologyLuncheon.aspx

  • Stefanie Higgins
  • Denise McInerney
  • Kevin Kline
  • Jen Stirrup
  • Kendra Little


Ballroom 6AB is filled with 720 people attending WIT luncheon for Q&A sessions with the panelists on “Where have been and where are we going?”

12:10 PM

WIT envolvement in education changes throughout time. Number of PASS Events

In 2011 we have 24 HOP organised specificlaly for female speakers. This has encouraged more women to participate and to speak at various PASS events. Wome in all computing application has declined to 25% in US. Women leave the Technology industry twice than men.

Companies like Microsoft, Google, Twitter have been investing in WIT, such as in form of training and process.

DigiGirls at Microsoft is a great example of initiative for WIT.

Let’s start thinking “what can inspire or have inspired women to contribute more in the Computing / Technology field?”


12:17 PM

At PASS, funding / sponsoring is not like those in IBM or larger companies. But we at PASS have great attitude, community. As a community, we need to say “Come in, sit next to me, let me introduce you to my friend”. At PASS we want to be the organisation that says more than just “Welcome”.

Based on experience, younger women needs nurturing and encouragement to see why IT is awesome, and not succumbed to peer pressure.


12:25 PM

International speaker, Jen Stirrup, brings her views and findings of WIT. It seems that not only Women are more disengaged in IT but more so in data.

SQL Saturdays in Europe have managed to attract female attendants. It became apparent that both men and women are interested in WIT and diversity issues.


12:35 PM

Kendra Little shared her experience from working as just an employee to an enterprenuer, via PASS. Speaking at various events, helps growing mentoring skills and evetually develops consulting / consultancy sense essential in becoming an enterpreneur.


12:40 PM

Focus groups with women who dropped out classes, help in understanding why it happens and how we can counteract that.  Money may not be the only incentive for IT career. Most women are interested in the work/life balance.

PASS WIT website (http://wit.sqlpass.org/) contains resources for you to start up WIT community near you!


12:50 PM

Work life balance is quite important to women. Track how you spend your time – get some data – analyse it and make some proposal to do things more effectively.  Web cams and time tracking are ways to show that you are working hard and effectively. Retaining employees by offereing work/life blanace is important.

A great way of proposing more flexibility is “Why don’t we try it for a month, if I don’t work more effectively, we’ll revert back to how it was before”.


12:57 PM

There are organisations that can help you get started in participating in WIT in your area. http://www.girlswhocode.com/ and http://www.microsoft.com/about/diversity/en/us/programs/digigirlz/hightechcamp.aspx some examples in US.


1:00 PM

A comment from an attendee “Women who are in IT seem to be more analytical and less emotional than those who aren’t”. Not eveyone is in IT, it’s best to give opportunities / facilitates opportunities for women to try it out first.


1:15 PM

Culture greatlly affects Women involvement in IT, and people in some countries are being funellled into specific areas of study or work.  PASS Global Growth and WIT can help in reaching out those women in other countries with small participation of Women in IT.

“You can be what you want to be and great at what you do” is a great encouragement for young women to get into IT.


1:25 PM

Closing note: send a tweet, blog about it and mention it to some one to start conversation on  ”Where have been and where are we going?”