Power BI In A Jiffy: Embedded Power View

Power BI team has been very busy the past month or so. There have been a few interesting posts around Power BI, some of which related to Power BI Contest (Congratulations to the top 5 winners!). Two blog posts that I find interesting in February 2014 around Power BI are:

1. Let Power BI tell you a Valentine’s Day Story

2. Making Big Data Work for Everyone by Quentin Clark

I find these posts interesting because you can interact with the Power View visualisations embedded on a website that is publicly accessible.

That’s it for the “in-a-jiffy” part. Read on for more details on how the embedded Power View visualisations perform on various devices.

Embedding Interactive Power View Visualisations – Early Samples

I was very excited about it when I first saw the two posts mentioned earlier. I have then decided to test them on a few different devices and asked help from friends / families to help testing out on their device of choice. Below are some screenshots taken from these devices along with a short review on each device.

1. Nokia 1020

The visualisations work well on Nokia 1020.

Note: Clicking on “Sort” to change the sort order can be a little tricky due to the size. Zoom pinch in/out on Power View frame is not recognised, instead it is being treated as “click” / “touch” action. The screen size is big enough to interact with it, but it’s not going to be as easy as with larger real estate.

Nokia 1020 Screenshot of Valientines Visualisation using Power VIew



Nokia 1020 Screenshot of Big Data Visualisation using Power VIew



2. Samsung Galaxy Tab 7.7

It doesn’t work after a few attempts. When it is still loading, it looks like it is going to render OK, but then the Power View charts disappear.


Samsung Galaxy Tab 7.7 - Not loading properly

Instead of showing another example of the Power View visualisation for the second article (Making Big Data Work for Everyone), below is a screen shot of the corresponding Power View on Excel Web App on Samsung Galaxy Tab 7.7, which works 50% of the time.

Excel Web App


3. Surface 2

It works fabulously (as one would expect), better than on Nokia 1020.

Surface2 - Population


Surface2 - Valentine's


4. iPhone 4s

I’m happy to say that it works on iPhone 4s too!

iPhone 4s Power View


5. iPad Mini (iOS 7)

The first article crashes the browser. I have tried a few times unfortunately it yields the same result.

The second article seems to be loading OK, but not interactive. It looks like it would do the highlight filter when you touch on a bar chart column, however it resets back to normal as shown on the screenshot below.

Screenshot on iPad Mini


Thank you to friends and families in lending their devices to me for testing this feature.


Whats The Big Deal?

From mobility point of view, it’s about time … :) As you may know, other “pretty” data visualisation competitors out there have had embedding features for a whlie. Check out this Would you like extra germs with that? article on Sydney Morning Herald that embeds engaging data visualisations on Food Safety Offences.

A few caveats:

  • Power View registers “mouse-hover” actions. On touch-screen devices, it is almost nearly impossible to emulate the “mouse-hover” actions.
  • Pinch actions for zooming in and out on the Power View frame is not smooth and may not work to zoom in/out properly.


Wanna Help?

If you’d like to help, please comment below and send me screenshots of the Power View Visualisation from the two sites mentioned earlier, using devices that are not listed here yet. You may want to try some “pinch” and “touch” actions on the Power View frame, and let me know if interaction with the chart works (for example, filtering/highlights). Thank you in advance.

You can also tweet me @MsSQLGirl and attach the screenshots with #PowerView hashtag.


Wrap Up

Two recent blog posts from Microsoft exhibit ability to embed Power View visualisations. The next question is, how is it done? Stay tuned!


Further Reading

Power BI For Office 365 – Microsoft

Power View: Explore, Visualize And Present Your Data – Microsoft



Power BI In A Jiffy : Myth Busting Driver Fatalities

Instead of posting a quick tip on Power BI, I thought I’d share my quick story about Power BI.

Power BI Contest is near at its end (just under 1.5 day as I write this blog post). Submissions started pouring in and I am very excited to see all the amazing work of BI Professionals out there. Hope to watch your video on the website and may be at PASS BA Conference!

To view the submissions entered so far, including mine, please visit PowerBIContest.com.

I suspect that the moderator needs to approve my video first before it is published. Hopefully my entry qualifies :) So, here is a slightly longer (and less talking) version of it.

Myth Busting Driver Fatalities With Power BI


Oh, and Happy New Year!






Power BI for D&B Data Mashup – Introduction

This article offers a gentle introduction to Dun and Bradstreet (D&B) Data services on Azure Marketplace. Using only Excel 2013 Power BI, you can perform ETL on the D&B data with Power Query, create reports with Power View and generate 3D visualisation with Power Map for story telling. There will be a second  post outlining steps to perform data mashup using Power Query.

Let’s get started with a couple of D&B Data available on Azure Marketplace that are used in this series of Power BI posts.

D&B Data on Azure Marketplace

Recently I have been invited by D&B to review a number of their Data Services on Azure Marketplace. At the time of writing, there were 10 data services provided by D&B. The two that I find quite interesting are:

Company Cleanse & Match for Microsoft SQL Server Data Quality Services

Despite its name, the Company Cleanse & Match for Microsoft SQL Server Data Quality Services is not specific for Microsoft SQL Server Data Services (DQS). For the remaining of this article, I am going to refer to this particular data service as Company Cleanse & Match.

By using the Company Cleanse & Match, you would be able to retrieve more information including Duns Number, Address details, the Confidence level of the matching against the input specified and a few other columns. Based on the current specification, minimally you would need to have the following information:

  • Company Name
  • Country
  • Maximum Suggestions (the number of suggestions to be returned by the D&B service)
  • Minimum Confidence (the minimum confidence level for matching)
  • State

In simpler words, this offering is a sophisticated way of searching companies online, such as local restaurants. After using it for a while, it is more useful than what it first appeared to be as it also provides DUNS Number. D&B introduced DUNS Number, a numbering system back in 1963 to support D&B’s credit reporting practice. The number is key to most business owners based in US for company identification as well as applying for credit (Source: sba.gov.au)

Company Firmographics – Industry and Sales

The Company Firmographics – Industry and Sales, based on the current specification, is more useful, if you have DUNS Numbers of the companies you want to search for. Not only, the service provides the complete address (Mailing and presumably physical address), it also provides other information such as the total number of employees, the legal status, the year company started, the line of business, Industry Code and more.

Using the data generated by the queries from Company Cleanse & Match and Company Firmogrpahics, you could create insightful story / visualisation just using Excel.


Visualisation Example

In this example, I use both Company Cleanse & Match and Company Firmographics to retrieve business details of 50 random camera related companies in California.  The basis of the visualisation is the Power Query output from the data mashup. The output is in a table format as shown below.

Data Mashup Output form Power Query

Using Power View, I can generate a simple bubble map as follows to visualise the distribution of camera related companies and their annual sales.

Data Visualisation using Power View


Using Power Map, I can generate two different visualistions as part of a 3D tour. Below is a snapshot of the geospatial visualisation of camera retailers by Line of Business in my sample data.

Data Mashup visualisation using Power Map (Column bar)

Below is a snapshot of Annual Sales in (’000s) by Legal Status in the Camera Retailers data.

Data Mashup visualisation using Power Map (Heat map)

And just for fun, I have created a 3D tour of the two visualisations to a video, exported directly from Power Map.


Wrap Up

Using data available from Azure, you can perform data mashup to create more insightful information. The information can then be exposed to the different visualisaiton tools of your choice that are appropriate for conveying the story. In this article, we discuss two of the D&B Data Services to get more details of companies and the final visualisation results of the data mash-ups, using nothing but Excel 2013.

Please stay tuned for another post, where I will outline the steps to connect to D&B Data services and performing the data mashup in Power Query 


Further Reading

If you are new to Power BI, the following articles are highly recommended:

Power BI for Office 365 Overview and Learning

Power BI Preview Review by Chris Webb

Power Map Preview – Exploring The New Features! by Jason Thomas

More details on Azure Marketplace:

Learn About Windows Azure Marketplace

D&B Data Services on Azure Marketplace

Speaking at PASS Summit 2013

I am delighted and honored to be selected as a Community speaker for a Regular Session at PASS Summit 2013. My session is “From Impaired to Insightful: Analysis with Geospatial Data“. This year this summit will be held in Charlotte, NC on Oct 15-18, 2013.

I look forward to meeting PASS Summit alumni, speakers and First Timers too! Is this your first visit to PASS Summit outside Seattle? Mine too! Good news, Melissa Coates (@SQLChick) has posted tips on how to get around Charlotte. Last but not least, congratulations to the other selected speakers at PASS Summit! Also special thank you to volunteers and organizers of PASS Summit!

PASS Summit 2013


Check out Coupon Code that may be available from your User Group. Malibu SQL Server User Group members should use the code mentioned here.

See you in October!

SQL Saturday 211 – Geospatial Reporting

Thank you to those who attended my session at SQL Saturday Chicago (#SQLSat211) in Chicago on the weekend. I really appreciate the feedback from you.

The complete demo files are located here:

SQLSat211 – Analytic Cartography – Julie Koesmarno

In this presentation, I discussed from the mature Traditional BI tool such as SSRS to self service BI using Power View and GeoFlow. to mobile BI solution in a small scale such as Excel Apps: Heat Map and Bing Map. Using Earthquake incidents we learned what tools were best at, from data exploration to visually captivating view. Coupling the correct tool with a good story line, we can achieve effective geospatial reporting.

Special thank you to SQL Saturday 201 Organizers, Volunteers and attendees for making it happen!


Further Reading

Power View Map in Excel 2013 Preview  by Julie Koesmarno

Plan a Map Report (Report Builder and SSRS) by Technet Microsoft

Introduction to GIS by University of Missouri – St. Louis

Public Preview GeoFlow for Excel 2013 by Julie Koesmarno

Shape2SQL by Morten Nielsen

Spatial Data Series (SSRS) by Jeffrey Verheul

ArcGIS Tectonic Plate Lines by ArcGIS

SSRS Tips / Tricks by Jason Thomas

SQL Saturday 198 – Calling SQL, Data and Snow Enthusiasts

There are 42 amazing sessions lined up for SQL Saturday 198. There will be a lot of speakers and attendees from different continents, America, Europe as well Australia! So, if you are a SQL Server professional or Data is your bread and butter, this is the event you would not want to miss this month! Register and build your schedule now.

If that doesn’t entice you, SQL Saturday 198 organizers sure know how to throw a great event. They are also organizing a ski trip on Friday, Feb 15, 2013. Check it out at Scott Stauffer’s website.

Josh Fennessy and I are providing you tips and tricks in “The Essential 8: Narrative Reporting Techniques“. This will be a preview to our PASS Business Analytics Conference in April 2013.

I am also honored to showcase “Power View for Geographical Analysis” next week at SQL Saturday 198. A sneak-peek interview video podcast, titled “The Power of Power View in Excel 2013″, with Karen Foster (Director | Microsoft) and me is available from Microsoft Technet Radio site.



A Lightning Talk demonstrating an upcoming GeoFlow add-in for Excel 2013 is also in the schedule. Here’s a little teaser from me:



And last but not least,, SQL Saturday 198 is fantastic way of networking with other passionate SQL Server and Data Professionals at both local and international level.

Hope to see you in beautiful Vancouver next week!


Starting 2013 with BI Talks

After the great PASS Summit 2012 finished, I have been counting down to the next SQL community events. Partly because I have learning withdrawals, and mostly because I miss my SQL Family!

Luckily, there are two SQL Saturdays scheduled near* my area. One of them is SQL Saturday #198 in Vancouver, Canada (16 Feb 2013) and another one is SQL Saturday #177 in Silicon Valley, US (23 Feb 2013). What I love the most about SQL Saturday is that it’s the best bang for your buck! If you look at the schedule for SQL Saturday #198 (Vancouver) here, you can immediately recognize many of the speakers are MVPs! Same goes to SQL Saturday #177 (Silicon Valley), the Partial List of Speakers shows that you will be in for a treat.


Also, if you are in the Business Analytics arena, the PASS BA Conference 2013 in Chicago is surely not to be missed. It’s a one stop shop for business analysts, data scientists, data architect and BA/BI practitioners. Not convinced yet? Twelve of these talks are going to be previewed for free on the upcoming 24 hours of PASS.  So check them out!


I am very delighted to also announce that I will be speaking at all those three events with the following topics:

1. Power View for Geographical Analysis for SQL Saturday #198.

2. The Essential 8: Narrative Reporting Techniques, with an expert BI architect and a friend, Josh Fennessy, for both SQL Saturday 198 and PASS BA Conference.

3. Analytic Cartography: Master Geospatial Reporting for SQL Saturday #177.


That’s my way of starting 2013!

Hope to see you, SQL Family, in some (or all!) of these events.


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.



SQL Server Virtual Presentations

23 Oct 2012 Ready, Steady, Catch! hosted at SQL Lunch

SQLLunch.com Session Recording - available soon

Slide Deck


1 Nov 2012 Power View for Geographical Analysis hosted at PASS DW/BI VC

Coming soon




Power View Map in Excel 2013 Preview

With Office 2013 preview launched in mid July 2012, there have been ubiquitous posts on the new Power View feature in Excel 2013. This is a very welcomed feature and I would sense that it will win Business adoption as an easy to use and “hopefully” affordable Self Service BI.  Here’s my thought on a fairly early version of Power View feature in Excel 2013 Preview.

Power View

Since the first time Power View introduced in SQL Server 2012 and made available in SharePoint 2010, I’ve always been fan of it. With most tools if you have analytical mind and are great with visualization / design, most reports you’ll create will be useful and used – and in my opinion – Power View is definitely one of these tools.

Using the Australia Broadband Guarantee data set provided from http://data.gov.au/dataset/australian-broadband-guarantee/, I am able to quickly and painlessly create an interesting Power View Map in Excel 2013 that I can analyze the investment the Australian government has made, at country level and down to specific Suburbs / Postcodes. The map feature is currently not available in SQL Server 2012 RTM with SharePoint 2010. I do hope that it will be introduced as a Service Pack 1 to SQL Server 2012.

Sample Data and Map

The Australian Broadband Guarantee (ABG) was an Australian Government initiative designed to help residential and small business premises access high-quality broadband services regardless of where they were located. The program targeted premises unable to access commercial metro-comparable services, particularly those living in remote parts of Australia

More information on the project: http://www.dbcde.gov.au/__data/assets/pdf_file/0017/128204/Australian-Broadband-Guarantee-2010-11-Guidelines-July-2010.pdf

The dataset I’m using has a very straight forward data structure. My motive here is to see which areas have the funding gone to? Does the amount of funding yield many connections? Is there any isolated area that gets funding?

Zoomed at the Nation level:

Australia Broadband Guarantee Map - Nation Level


Zoomed at a State level, with funding > $1 million filter: (hovering on one of the bubble gives me a descriptive tooltip)

Australia Broadband Guarantee in NSW


I can zoomed in further to the actual postcode as the granularity of the location is postcode, as shown below:

Australia Broadband Funding in Aarons Pass NSW


Advantages and Disadvantages of Using PowerView Map

Although it’s still at its early stage as Office 2013 is only released in a Preview version, I consider Power View Map feature has the following advantages and disadvantages.

Advantages of PowerView in Office 2013 preview as at the time this post is written:

1. It integrates with Excel. So it allows for rapid prototyping and development.

2. No knowledge of Power Pivot is required. Power View can be created based on a table on a worksheet and it automatically creates Power Pivot data based on the table. Knowing Power Pivot and being able to configure relationship and data categories, will help for performance (so it seems based on my testing).

3. The map feature recognises longitude and latitude pairs which are easily attainable. It can also use information such as Suburb and State.

4. It uses Bing Map as the map layer, so there is no need to acquire ESRI shape files or converting to Spatial data type.

5. Performance of the Power View map feature is amazing. When plotting over 2000 locations, I could barely notice the wait time.

6. Most importantly, the ability to interactively zoom in/out the map – from world to road level – is fantastic. This is something that is not available in Report Builder or SSRS yet.


Disadvantages of PowerView in Office 2013 preview as of the time this post is written:

1. No support for Spatial Data type. So there is no user defined zone that can be layered on top of the map.

2. Location, Longitude and Latitude values are all needed. Location could be PostCode, City/Suburb or Country.

3. The Location category is US Centric. This meaning that if State is used as the Power View Map Location, and there is a State with the same name or abbreviation in US, it would point to US. As an example, “WA” is Western Australia in Australia (http://binged.it/QzFaDn), but it is also Washington in US (http://binged.it/QzF7HG); which is over 9,000 miles away or over 14,000 kilometers.

4. Unable to connect directly to the Analysis Services; i.e. it is required to import the data into Power Pivot first or Excel table, then create a Power View map based on the imported data.

There are a few areas that still don’t work properly. The application crashed a few times on me when converting the initial pre-built table created by Power View to a map, as well as when the column detects text (larger size) instead of numbers for aggregation. I think in time, these issues will be ironed out.

Wrap Up

Office 2013 seems to offer a number of nice features, especially for self-service BI solutions using Excel 2013. I have no doubt that small businesses will start adopting Excel 2013 more and more in the near future, for data analytics using the new readily available Power View feature in Excel.

Further Reading

Excel 2013 Preview with PowerPivot and Power View by Kasper De Jonge

Microsoft Business Intelligence in Excel 2013, SharePoint 2013 and SQL Server 2012 SP1 by Microsoft BI Team

Building a Simple BI Solution in Excel 2013, Part 1 by Chris Webb

Shark Week Special: Mapping Shark Attacks (Drillable Maps and Hyperlinks) by Sean Boon