Office 2013

Power Map Preview – Exploring the NEW features!

Power Map Preview – Exploring the NEW features!

Woot! Microsoft just announced the pre-release availability of the refreshed builds for Power Map with some really cool new features and boy, I must say that I am PUMPED! For folks that know me, I have been doing quite a number of presentations on Power Map (and geospatial analytics in general) and there were a couple of features that I really really wished for in Power Map. Well, looks like the Power Map team heard my requests and I feel this release is a big step forward.

Power Map preview for Excel - new features

First of all, here are some useful links (including the download link for the refreshed bits):-

Now let’s see some of the new features that has come up since my last post on Power Map.

I) Recognizing geographic data and Plotting them on launching Power Map On launching Power Map, the columns with geographic data are automatically recognized and plotted. For example, I had some data of the population of North Carolina by zip code as shown below

sample data

Now when I launched Power Map, this is what I saw

recognizes geo data and plots them

The best part is that the geographic columns seemed to be recognized correctly even if the they are named something other than City / State / Zip Code, etc. For eg, I renamed the City to CVDS and still was able to see it correctly mapped to City.

works with different column names also

And anyways, you still have the option to change it from the drop down. All in all, great feature and saves me a couple of unnecessary clicks.

II) Support for Flat Map

I know, I know, all of you would be thinking – what’s so special about this? After all, what made Power Map unique was that it had the ability to do 3-D geospatial analysis. Well, that is pretty cool but there are certain times when only a flat map would do. For eg, when you are looking at a worldwide data like population by countries, you want to see both sides of the globe. Earlier in Power Map, it used to look like shown below:-

3D map - power map

We could see only one half of the globe and had to rotate to see the other half. Now we have this icon to convert the 3D map to a flat map (and back to 3D if required!)

Flat map - Power Map

This is REALLY cool! Open-mouthed smile

III) Support for Regions Visualization (Chloropeth Mapping)

Now this is the most awesome enhancement according to me – support for chloropeth mapping or shading a geographical area (and I had actually requested as a Connect suggestion during the beta release of GeoFlow). Let me show you how the visualization looks for the population data for North Carolina

Region Visualization - Power Map

Best part is that it is damn right simple – just change the visualization from Column to Region (and yes, that also means that we have a fourth visualization type now – apart from Column, Bubble and HeatMap)

new visualization type in Power Map - Region

The Region visualization is only present at a Country / Region, State / Province, County or Zip / Postal Code level (which means City is not available) and is available across all countries (not just the USA). If you try to use a geographical level which is not one of the above, you will receive the following error and the chart type will be changed to Column.

Power Map - Region charts work with Country/Region, State/Province, County, or Zip/Postal Code data

I LOVE this feature and more than that, adore the way how simple it is for users to geocode at that level (no need to bring in complex shapefiles to geocode county/zip code level data like in SSRS!). I can already see a lot of use for it.

IV) Change Color for Data Series

Now this is another simple but very useful new feature – the ability to change colors for data series. You can see this new option in the Layer options.

change color for data series

You can also see that the display options of the Themes button has changed and now the colors also show up.

themes

V) Exporting Tours as Videos

A question that consistently makes the rounds when I present on Power Map is – “How do I “unlock” the tours from the Excel workbook? Is it possible to share it through any other medium?” Well, the answer has been No until now. The current build allows you to export your tours as a video and now this can be shared across all your channels. For creating videos, there is a new Create Video button on the ribbon and then you can select the video quality also.

image

To demonstrate the feature, I have exported a tour from one of my recent presentations. This tour is around 5 mins long and investigates the myth – Are Refugees flooding Industrialized countries? It took quite some time for me to export the tour as a video even in the 360p mode, but that is understandable I guess.

Power Map Tour exported as a video

Conclusion

Overall, I feel this is a build with some really awesome features. Apart from the new features I described, there are also some UI changes but these changes are simple enough to understand, so I am not going over them. There are still some really basic functionalities that are desired like filtering your data and drilling down on the geographic levels (and I found out at least one bug which I will be filing on Connect soon), but we should not forget that this is not the final version. So I am happy and I would encourage everyone to try out the new build.

Posted by SQLJason, 11 comments
Drill-down Behaviour in Power View

Drill-down Behaviour in Power View

I usually don’t write a lot of posts asking for favours from my readers (in fact, this is the first one), but an incident happened today at my client site which has aggravated a pet peeve that I have with Power View – drill-down behaviour in Power View. The drill-down behaviour in Power View is really unintuitive and this is at least the the third client of mine that has raised this issue of drill-down. That is when I decided to raise my voice and ask for your help too in letting Microsoft know about this issue, so that they can consider changing this in a future release. For those of you who don’t have the time to read this post of mine, I would really appreciate if you could vote this Connect issue that Leonard Murphy has raised. For those of you who have the time, follow my post below to see the current drill-down behaviour in Power View and why I feel that this is not right.

Drill down in Power View

For this demonstration, I am using Power View in Excel 2013 (although this behaviour can be reproduced in Power View for SharePoint also) and a very simple table (which shows the sales by countries and states) as my source.

Source Data

I) How to Implement Drill-Down in Matrix

1) Open Power View and then select the three columns to make a table.

Simple table visualization

2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.

Convert table to Matrix

3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.

Enable drill down

4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.

Drill down in matrix - Power View

Also note the drill down and drill up icons.

II) How to Implement Drill-Down in Chart

1) As in Step 1 of previous section, select all three columns to make a table. Then click on the Bar Chart option and select Stacked Bar. Ensure that the Country and State are in the Axis and Sales is on Values.

Chart in Power View

2) You can see the drill-down functionality by clicking on Canada. Also note the drill-up icon on the top right of the chart once you have drilled down.

Drill Down in Chart - Power View

III) Unintuitive Cross-filtering behaviour

1) Everything looks fine when you just have one chart in the canvas. Now let’s see what happens when we have 2 visualizations – lets say, a bar chart with drill-down and a table which just has State and Sales.

Chart and table in Power View

2) Now when you single click on a country (say India) in the chart, the table gets cross filtered for the states of the selected country.

Chart cross-filters table

3) Next, see what happens when you double click on a country (say India).

Double clicking in chart removes cross-filter in table

You can see the bar chart has drilled down into the states for the selected country as expected but look what has happened to the table. The table is now showing states for all the countries and not just the states of the selected country. This is so unintuitive as a person seeing the report will expect to see only the sales of the three states of India. This can get even more confusing if the states are not displayed on the table, and there will be no way of knowing what has been cross-filtered on the table.

4) To add to the confusion, I can press CTRL and then click on all three states. Look at the image below for the results.

CTRL+Click on the three bars -> table is cross-filtered

You can see that the bar chart looks exactly the same as the previous image, but the table looks different. In short, the report looks exactly the same and there is no way to differentiate whether the data in the table is correct and if some cross-filter has been applied or not. What I would expect is that the drill-down always cross-filters the rest of the visualizations. Honestly, I can’t think of a scenario where I would not want the cross-filtering to happen and I would love to hear your views as comments.

IV) Call for Action – Vote up the Connect issue

If you agree with me that the cross-filtering behaviour is so non-intuitive, please vote up the connect issue. You can do so by clicking on this link, log-in and then clicking on the green up arrow.

Click to vote

Hopefully we will have enough votes for Microsoft to look into the issue seriously!

Posted by SQLJason, 11 comments
Power BI for Office 365

Power BI for Office 365

Great news!!! Today, at the Worldwide Partner Conference, Microsoft announced a new offering – Power BI for Office 365. Quoted from Microsoft – “Power BI for Office 365 is a cloud-based business intelligence (BI) solution that enables our customers to easily gain insights from their data, working within Excel to analyze and visualize the data in a self-service way. It works with Office 365 to help customers share insights, find answers and stay connected to their data from their favorite mobile devices.” Announcing Power BI for Office 365 For those of you who have the time, I would recommend going through the links that I have collected below. And for those who don’t have the time, let me do a very quick summary:- 1) Discover, analyze, and visualize with Power BI for Office 365, which includes Power Query (formerly known as Data Explorer), Power Map (formerly known as GeoFlow), Power Pivot (formerly known as PowerPivot – notice the space between the words) and Power View (for a change, I like that the names are standardized with a space after Power). 2) Create Power BI Sites to share live interactive reports. Power BI Sites are dedicated collaborative BI workspaces in Office 365 for sharing data and insights with colleagues. 3) Ask questions and get back instant answers with the new Q&A feature that uses natural language query technology to provide you with immediate answers in the form of interactive charts and graphs based on the questions you type into the speech bubble. 4) Browse Excel and Power View reports with HTML 5. Stay connected with the touch-optimized Power BI mobile app. BI users can access and receive live updates on their reports through their browser with HTML5 or through a mobile application designed for their tablet or touch-enabled device, either Microsoft Power BI for Windows or Microsoft Power BI for iPad. 5) Refresh your data on demand or define a schedule. The Data Management Gateway allows your IT department to enable data refresh from Office 365 to your favourite on-premises data sources. 6) No announcement on pricing or preview date (except for “later this summer”). The natural language feature looks pretty impressing even though I doubt whether the experience is going to be as seamless as shown in the demo. Also, the bubble chart shown in the demo (also shown in the image below) looked extremely jazzy but may end up just being eye-candy in real life. I would love to finally see the Mobile (and ofcourse iPad) solution come through with Power BI, although I am a tad bit disappointed that this feature is not there for the SharePoint version. Bubble chart from Amir Netz keynote demo - Power BI There are a couple of official posts on this offering and I have collated them below:- 1) What powers Power BI in Office 365?
2) Introducing Power BI for Office 365
3) Announcing Power BI for Office 365
4) Register for Power BI Preview Watch the Day 1 Vision Keynote from WPC 2013 on demand here! You can also read some of the initial reactions to Power BI below:- 1) Some Thoughts About Power BI – Chris Webb
2) Microsoft Office 365 Cloud Power BI – Jen Underwood
3) Microsoft Announces Power BI for Office 365 – Andrew Brust
4) Power Business Intelligence for Office365 – Jen Stirrup Exciting times indeed for Microsoft BI and Self Service BI in general. I can’t wait to get my hands on the preview.

Posted by SQLJason, 2 comments
Download GeoFlow Preview for Excel 2013

Download GeoFlow Preview for Excel 2013

Over the last couple of weeks, many people have been asking me for the download links for GeoFlow. Well, I am pleased to say that GeoGlow has made it’s public preview today and is available for download now. GeoFlow Preview for Excel 2013 Find below some useful links (including the download link)

And I guess it is time to cut down on some of the earlier limitations that I had mentioned about the beta release of GeoFlow:-
Related tables are not supported in this release.All the data should be in one table.
Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
No Undo/Redo is supported in this release.

No way to slice and dice data within the visualization
No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View. Not bad huh? Some new features are also there like the Top / Bottom 100 Chart shown below. top / bottom 100 chart in geoflow It is interesting to note that GeoFlow is not available to all versions of Excel 2013. The supported Microsoft Office versions are:

  • Office Professional Plus 2013
  • Office 365 ProPlus
  • Office 365 Midsize
  • Office 365 E3
  • Office 365 E4

Enough of me telling, it’s time to explore this add-in on your own now. Download GeoFlow today and take your data for a 3-D drive! Smile

Posted by SQLJason, 6 comments
GeoSpatial Analytics, Microsoft BI & John Snow’s Cholera Map

GeoSpatial Analytics, Microsoft BI & John Snow’s Cholera Map

Throughout my childhood, I have always been fascinated by the idea of maps. It could be attributed to the number of pirate and treasure hunter stories that I used to voraciously devour. My dreams were filled with the protagonists in those books and their endless struggles to decipher the coded information in the maps. What used to strike me was that the location of the treasure was always present in the maps but still countless men were misled by them. Most of the times, it took the right person or the right context to get to the bottom of the map. As I grew older, my association with pirate / treasure hunter stories decreased (apart from watching the occasional Pirates of the Caribbean releases) but my bond with maps just went on getting stronger. Though my friends might joke that the reason for this is because I am so spatially disoriented (I still use a navigation system to travel from my home to the office which is just a mile away for fear of getting lost), the real rationale is because I see a lot of value in geospatial analytics as a BI consultant. In today’s world, when more and more product companies are bringing forth tools to easily analyse location based data, the excuses for not trying to analyse and explore geographic patterns in your business data are running dry. The Microsoft BI platform has a very able set of tools for geospatial analysis which includes SQL Server Reporting Services, Power View and Geo Flow (this is without including some of the map apps that are present in Office 2013) and helps me a lot in evangelizing geospatial analytics amongst my clients. While I was reading the news, I came to know that the last week was the 200th birth anniversary of one of the pioneers of geospatial analysis as well as the father of modern epidemiology – John Snow. I decided to pay my homage to him by following his adventure and reproducing his famous 1854 Broad Street Cholera Outbreak map using Microsoft BI.

GeoSpatial Analytics, Microsoft BI & John Snow’s Cholera Map

Before we start, it might be interesting to set the context to the 19th century London. London was evolving into a big city with a large influx of people, but without the modern day facilities and sanitary services. Houses used to have cesspools below their basements and human as well as animal wastes were accumulated there. Initially, people seemed to forget about the cesspools below and somehow thought that the wastes would get disposed magically, till the cesspools started overrunning. That is when the London government decided to dispose the waste into River Thames (which was also the source for water supply). You can imagine London at that time – stinking with all the human and animal wastes and it was no wonder that diseases were rampant at that time. The reason for the diseases were all attributed to the pollution and foul air emanating from those wastes. John Snow was a big sceptic of this theory though he had no evidence to prove otherwise. That is around when there was a major outbreak of Cholera in the Soho district of London and he decided to investigate it further. I am not 100% sure of how he actually did his investigation, but I am pretty sure that he must have at least gone through some variations (if not exact) of the four main sections in any treasure-hunting plot :-

1) Collecting the Data (or acquiring the Map!)
The first section of most treasure hunter stories weave through the part where the protagonist goes through shady alleys and dilapidated shops to find the treasure map from some ancient hawker. Likewise, John Snow might have gone through the disease laden streets to pick up the statistics on the cholera deaths and their locations. Luckily for me, I just had to search the internet and got the data (thanks to Robin Wilson). You can download a copy of the data from here. I did some simple changes to the source data to extract the longitude and the latitude columns from the geometry column as shown below (pro tip: use the Flash Fill feature of Excel 2013).

Cholera death stats

2) Exploring the data patterns (or deciphering the Map!)
The plot continues where the protagonist tries to make sense out of the map. Treasure maps are never straightforward and he takes a lot of time to see whether there are any hidden clues or markers in the map. Similarly, John Snow must have spent a lot of time studying the statistics as well as exploring the data visually for geographic patterns. I decided to visualize the data using Power View initially to check for any patterns.

Cholera death visualization using Power View

Just as the protagonist tries different approaches, I decided to also use GeoFlow to visualize the same data.

Cholera Deaths layer

The heatmap visualization in GeoFlow indicated something that Power View didn’t show that explicitly – unusually large number of deaths around the red area.

3) Investigating the Outliers / Patterns (or Validating the potential Treasure locations)
This is one of the most crucial part of the story, where the protagonist tries to cross check and re-validate the patterns or code that he has found within the map. This is what is going to distinguish him from the countless men that have lost their lives in search of the treasure. He makes sure that the clues that he has got from the map is not a red-herring. This is what John Snow did too, as is his evident from his writings – “On proceeding to the spot, I found that nearly all the deaths had taken place within a short distance of the [Broad Street] pump. There were only ten deaths in houses situated decidedly nearer to another street-pump. In five of these cases the families of the deceased persons informed me that they always sent to the pump in Broad Street, as they preferred the water to that of the pumps which were nearer. In three other cases, the deceased were children who went to school near the pump in Broad Street… With regard to the deaths occurring in the locality belonging to the pump, there were 61 instances in which I was informed that the deceased persons used to drink the pump water from Broad Street, either constantly or occasionally…”. As for all of us, we already know the cause and hence all I had to do currently was to display the pumps also as another layer in GeoFlow and to see the correlation of deaths and proximity of pump.

Cholera Deaths layer with the Pumps layer

Isn’t it amazing to see how accurately GeoFlow has plotted the problem causing Broad Street pump right in the area of the maximum deaths!

4) Visualizing and Sharing the Results (or finding the Treasure!)
This is the final part of the story where the protagonist undertakes the arduous journey to find (and hopefully claims) the treasure. The journey is not easy and there are every chance that he might be misled like the countless many before him. Similarly, in geospatial analysis, it is very easy to get carried away and share wrong results.

xkcd

However, John Snow’s map (shown below) was very instrumental in convincing the London authorities to shut down the problem causing Broad Street Pump and history says that this helped in containing the outbreak.

File:Snow-cholera-map-1.jpg

If done correctly, geospatial analysis is extremely powerful and with technologies like Power View and GeoFlow, this can be done pretty easily and quickly. If you are interested in geospatial analysis or Business Analytics in general, it’s still not late to register for the PASS BA Conference happening on Apr 10-12, 2013. And if you do register, don’t forget to attend my session on GeoSpatial Analytics using Microsoft BI.

GeoSpatial Analytics using Microsoft BI

You can download the completed Excel 2013 file with the Power View and GeoFlow visualizations from here.

Posted by SQLJason, 9 comments
Unpivoting Data in Data Explorer

Unpivoting Data in Data Explorer

Data Explorer is generating a lot of positive buzz within the community and everyone I speak to seems to be really excited about this. (If you are wondering what Data Explorer is, you might want to quickly catch up on my previous post – Introduction to Data Explorer Preview for Excel). And just today, the Data Explorer team announced A new build of “Data Explorer”, and an Auto Update feature (must say I really like the idea of Update button). This was followed by a post from Jamie Thomson aka SSIS Junkie (blog | twitter) on the query language ( M ) in Data Explorer and you can read more about that here. All this made me really excited and I also decided to contribute something through this post. Unpivot rows in data explorer It is a pretty common requirement to unpivot data, especially when you are scourging the net for open data. Data Explorer currently does not have any feature to unpivot data but the query language looked pretty solid and I decided to give it a try (actually, this exercise started as a way to test the new query editing functionality in Data Explorer). Well, I did succeed though it might not look that easy (if you do find an easier way, please share it!). Read on for the solution:- 1) The source for my post is given below source data I just made a simple table in the excel spreadsheet which gives the Sales by State for the years 2010 to 2012. I then click on the From Table option in Data Explorer tab to get the query window as shown below. Import data from excel 2) I add a new column called JCol with a value of 1 using the expression below

= Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1)

Add join column to first table  Now this is my first table, and I store the expression with me. 3) My next objective is to make a transpose of the original table. This can be done with the expression below

= Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content])))

Transpose table 4) Now we need to rename the State Column to Year.

= Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”})

Rename column 5) Now, we need to add a column to this resultant table called JCol (just as we did to the first table in Step 2).

= Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1)

Add join column to second table 6) You might be wondering at this stage why we created the JCol column in both the tables. The reason is that we need to cross join both of these tables so that we get extra rows, and the join column is going to be JCol. Since the values for the join column are all the same, we get a cross join. To do the join of both the tables, delete the existing expression and enter the expression below

= Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1), “JCol”, Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1), “JCol”)

Join both the table expressions 7) Now add a new calculated column which will give you the value in NC column if State is NC, SC column if State is SC and FL column if State is FL. Add custom column for Sales The expression for the calculated column is given below

if [State] = “NC” then [NC] else if [State]= “SC” then [SC] else [FL]

custom column expression Now your result should look like shown below end result of custom calculation 8) Now all you have to do is to rename the Custom column to Sales and hide the unnecessary columns. Then you will be able to see the unpivoted data as shown below rename and hide unnecessary columns 9) Also, have a look at how the actual query looks like in the advanced query editor Advanced query editor I would like to see a word-wrap option for the advanced query editor so that I don’t have to scroll over to the right to see long formulas. Apart from that, looks great! Don’t forget to share your comments as well as your posts / experiences with Data Explorer and it’s query language. Update I got quite some requests asking for the actual query. So here it is

let
    Source = Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1), “JCol”, Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1), “JCol”),
   InsertedCustom = Table.AddColumn(Source, “Custom”, each if [State] = “NC” then [NC] else if [State]= “SC” then [SC] else [FL]),
    RenamedColumns = Table.RenameColumns(InsertedCustom,{{“Custom”, “Sales”}}),
    HiddenColumns = Table.RemoveColumns(RenamedColumns,{“2010”, “2011”, “2012”, “JCol”, “NC”, “SC”, “FL”})
in
    HiddenColumns

Remember to change the name of the table name when you use it.

Posted by SQLJason, 5 comments
Introduction to Data Explorer Preview for Excel

Introduction to Data Explorer Preview for Excel

It’s just been a couple of days since this add-in has been released and I can’t seem to stop using it. I guess I haven’t been this excited about a Microsoft offering since SSRS 2008 R2 (well, I am really passionate about Tabular and PowerPivot now but I wasn’t that hooked onto those technologies when they were released). As a BI consultant, blogger and speaker, I traverse through lots and loads of open data – sometimes trying to get that extra piece of open data which will add value to my clients’ existing data or to get some interesting stuff for my blogs and presentations. Data Explorer might not be the perfect solution, but it sure does a splendid job of reducing my efforts in searching, shaping and preparing my data. I thought of sharing my experiences in the form of a quick introduction to Data Explorer Preview for Excel. Introduction to Data Explorer Preview for Excel 1) What is Data Explorer Preview for Excel and where can I get it from? Microsoft “Data Explorer” Preview for Excel is an add-in for Excel 2013 which provides an intuitive user interface for data discovery, data transformation and enrichment. You can download the add-in from here. 2) Basic Tutorial for Data Explorer You can import data into excel from a wide variety of sources using Data Explorer and the complete list of sources is given here. For the purpose of this demo, I am going to be connecting to my favourite source which is the ‘web page’ source. a) After installing the add-in, you should be able to see the Data Explorer tab in Excel (if not, go to File—>Options—>Add-Ins—>Com Add-Ins and enable the Data Explorer add-in). Click on the From Web option and enter the url – http://www.nuforc.org/webreports/ndxloc.html as shown below. Import From Web b) Now click on Table 0 in the Navigator pane and you should be able to see the UFO sightings by states. The table has some non-US data and so to filter them, click on the dropdown in the reports column and unselect them. Click on OK when you are done. Unselect unnecessary data c) Rename the columns as States and UFO Sightings respectively. Notice that the Steps pane on the right shows the modifications you are making and you can expand the pane. rename columns d) Click on Done and now you can see the data in Excel. This data can be used as a source for your charts / tables and in the image below, I have used the “Geographic Heat Map” Office app to visualize the same. Looks like the aliens have taken a liking for California! Data visualized using Heat Map 3) Interesting Features a) Online Search:- I spend a lot of time trying to search for open datasets and this feature is surely going to reduce that time. You can search for data right from Excel by clicking on the Online Search button. For eg, if I search for ‘richest states’, the results as shown below. Online Search option Just scroll your mouse over the results and click on Use to add the data. b) Filter & Shape Data:- The ability to filter and transform your data is what makes this so useful. You can just click on the Filter & Shape button to start the process as shown below. Filter & shape data For eg, if you just want to show the income for 2011, you can hide the rest of the columns as shown below. Hide unnecessary columns You can also do a lot of other operations like Splitting columns, removing duplicates, replacing values, changing types, group-by, etc. Types of transforms available You can read the complete list from here. Hopefully, there will be more additions to this in the future (I could definitely use a Unpivot / Pivot option). c) Merging & Appending from Multiple Sources:- This is another killer feature in Data Explorer. You can merge or append from multiple sources. For eg, if we have to merge the two sheets that we just created, click on the Merge button and then select the primary and secondary tables from the dropdown. Merge data from two sources Then select the matching columns from both the tables and click on Apply. select matching columns Now, to display the additional columns, click on the expand icon on the New Column and select the UFO sightings. Click on OK. Expand columns Now you have combined both the sources and can use it for your visualization. I have visualized the same data using GeoFlow below. Data visualized using GeoFlow 4) Further Reading There’s lot of interesting stuff you can do with Data Explorer and if this post caught your interest, make sure to check the below ones too

1) Data Explorer Team – Announcing Microsoft “Data Explorer” Preview for Excel

2) Chris Webb – Importing Data From Multiple Log Files Using Data Explorer

3) Chris Webb – Calling A Web Service From Data Explorer, Part 1

4) Jamie Thomson – Traversing the Facebook Graph using Data Explorer

5) Matt Masson – Access the Windows Azure Marketplace from Data Explorer

6) Jake Smillie – Best Oscar winning Film? My first Data Explorer adventure…

7) Ian Morrish – SharePoint OData and the Excel Data Explorer

8) Data Explorer Help

Updates

9) Dan English – Installing Data Explorer Preview & Demo with IMDB Data

10) Alan Koo – Introduction to Microsoft Data Explorer Preview for Excel 2013 – Part 1

Posted by SQLJason, 1 comment
Introduction to Excel GeoFlow (Beta 1)

Introduction to Excel GeoFlow (Beta 1)

I have been playing around for a couple of weeks with the beta release of Excel GeoFlow and I  have got very mixed feelings about the product. No, I am not trying to be the Grinch in this Christmas season and I must say that I sure do like the direction in which Microsoft and this product is going – just that it is not even half as ready as I would have expected (but to be fair to them, it is just the beta 1 release). That said, there are a couple of features that I am really excited about and I thought of jotting down a quick introduction to GeoFlow for those who are interested.

image

1) How to get the beta release of GeoFlow
If you would like to try GeoFlow, send a mail to geoflowbetarequest@microsoft.com and wait for the team to respond. You must have Excel 2013 for GeoFlow to work.

2) Current Limitations
Related tables are not supported in this release.All the data should be in one table.
Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
No Undo/Redo is supported in this release.
No way to slice and dice data within the visualization
No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View.

3) Create a basic GeoFlow report
GeoFlow requires your data to have some geographical fields in it. It can be in a variety of formats including latitude and longitude, street address, city, zipcode, state, country, etc. Be aware that you will need internet connection for the geocoding to take place. For the purpose of this demo, I have got the population and personal income data by year of all the counties in the United States. Follow the steps below to create a basic GeoFlow report:-

a) After installing GeoFlow, you should now be able to see the 3D Map button in the Insert tab. Click on any of the cell within the data table and then select the 3D Map button within the Insert tab. Click on the Explore in 3D option as shown in the image below.

1 Insert 3D Map button

b) Choose the geographical field based on which you need the map. In my case, it is the StateName field. Click on Map It button to proceed.

2 Choose geo field

c) Click on the Population field to see the states visualized by this measure.

3 Basic map of population by states

By default, you have got the Column chart visualization (which can be clustered or stacked if a category is present). This can be changed to a bubble map or a heat map visualization also.

4 Bubble map

4) Navigating the 3D Map
Navigating the 3D map can be tricky so it is useful to know all the navigation features. To use a mouse to navigate in the GeoFlow 3D environment, do any of the following:
·         Double click rapidly on any portion in the globe to zoom closer to it.
·         Use the scroll wheel on your mouse to zoom in and zoom out.
·         Click and drag the globe in any direction to pan without changing the pitch.
·         Hold the Alt key, and then click and drag to change the pitch.
·         If you lose your point of focus, zoom all the way out with the scroll wheel to reset the globe and view. To use the Keyboard to navigate in the GeoFlow 3-D environment, do any of the following:
·         Tap the arrow keys up, down, left and right to pan and spin the globe.
·         Hold the Alt key and then tap the left and right arrow keys to orbit the current target, or the up and down arrow keys to increase and decrease the camera pitch.
·         Tap the plus (+) and minus (-) keys to zoom in and out.

5) Interesting Features
There are quite some interesting features in GeoFlow and these are some of the main ones in my opinion:-

a) Handles multiple geographical levels:- We can add multiple geographical levels to GeoFlow. To add the counties (AreaName field) to our basic report, click on the Edit button in the Layer Manager as shown below.

SNAGHTML5b930dd

Then select the AreaName field and map it to the County in Geography section. Then click on Map It button.

SNAGHTML5bba4d4

Now you should see the map visualized by counties. It might take some time for all the data to be processed on the map and it depends on the number of rows that you have. When the processing is done, you should see the message as Finished in the green bar below the map.

SNAGHTML5bdb42f

You can easily toggle back to the states by selecting the StateName radio button in the Map by section. However, as I mentioned before, drill downs are not possible (as far as I know in this release). Vote to up this request in Connect by clicking here.

b) Visualizing Data over time:- We can visualize the data over time if we have a date  or time field. In my dataset, I have the year but this is not enough as GeoFlow requires a date or time field. So I had to make a new filed called DateYr which converts the year to a date field by adding 1/1/ before every year. Vote here to up this request to have the time play axis use any level of the time dimension. Now in GeoFlow, drag and drop the DateYr field to the Time section. Now you will get an additional section for time settings which has values like Time Accumulation, Instant and Persist the last.

SNAGHTML5cafdfb

You can also see the time play axis on the bottom. Click on play and you can see the data changing over time. c) Adding Annotations and Textboxes:- This is definitely one of my favourite features. We can now add an annotation to a particular data-point by right clicking on it and then selecting the Add annotation option.

image

Similarly, you can add a textbox also. The only difference between them is that an annotation is bound to a data point while the textbox is more like a sticky note on the screen. The textbox stays at the same position even when the map is panned or zoomed. d) Find a Location:- The find a location feature is pretty handy in case you have a lot of data points and want to directly zoom to a particular location.

SNAGHTML5ddebe2

e) Creating and Saving Tours:- I have obviously saved the most exciting feature for the last. Now you can create tours by clicking on the New Tour button and then including the scenes in the tour by selecting the Capture Scene button.

SNAGHTML600fded

Scenes will transition automatically and effects are automatically applied. A scene is auto-saved when you capture a new scene. If you want to change a scene, for example if you want the scene to show a different angle or a different time subset, select the scene, make the changes and then click on Save Scene, Once you have created a tour, you can play it by selecting the Play Tour button. I have posted a video of a sample tour that I created.

Excel GeoFlow

After creating a tour in GeoFlow, the tour is automatically saved back to your workbook. You can create multiple tours by clicking on New Tour. Saving your Excel workbook will save each of the GeoFlow tours you created with the worksheet it was created from. Now you will be able to see a new option to Manage Tours also.

SNAGHTML6063add

To summarize, there are some exciting features but clearly, a lot of work needs to be done before it can be used by data professionals for analysis. With this, we come to the end of today’s post. Wishing all of you a very Merry Christmas from my side and be good!

Posted by SQLJason, 14 comments
Changing PivotTable Names in Excel 2013 + Bug Alert

Changing PivotTable Names in Excel 2013 + Bug Alert

Today is Black Friday here in the US and I don’t have time to write a regular post (as I am scourging for deals online and planning to get out for shopping as soon as the insane lines outside the stores dwindle in numbers). So this is just going to be a quick tip for those who have tried upgrading their PowerPivot models with linked tables from Excel 2010 to Excel 2013 and found out that their PivotTable names don’t match anymore.

1 Changing PivotTable names in Excel 2013

For those who are not familiar, let me give an account of the issue. If you upgrade a PowerPivot model which has linked tables from Excel 2010 to 2013, you will notice that the table names in the field list don’t match the table names in your model. As per my testing, this doesn’t break the measures that you might have created using the old table names (as the measures still use the table names in the data model and this name change looks to be more of a front-end display name) but might be a real inconvenience. Follow the steps below to reproduce and solve the issue:-

1) For the purpose of this demo, let us use the PowerPivot file that I created in Excel 2010 for my last post – Measure Selection using Slicers in PowerPivot. You can download it from this link. If you open it in Excel 2010, you can see the original table names in the field list as well as the linked tables used to create the model above.

2 Table names in Excel 2010

2) Now close the file and then re-open in Excel 2013. Julie Koesmarno (blog | twitter) has put a step by step post to upgrade this workbook from 2010 to 2013 here. Also have a look at this post – Upgrade PowerPivot Data Models to 2013 for any potential issues while upgrading your workbook. Now once you have done that, you will see that all the table names have changed in the field list.

3 Changed table names in Excel 2013

However, there is no change to the table names in the underlying PowerPivot data model.

4 Name in PowerPivot model

3) To change the name in the PivotTable field list, all you have to do is to select a cell in the respective linked table, click on Design and then enter the original / required name in the Table Name cell as shown below

5 Rename table

4) You can see that the changes are reflected in the PivotTable field list now.

6 Name change in Excel 2013

You could avoid doing these steps in Excel 2013 if you had renamed your linked tables in Excel 2010 using the same way before upgrading. If not, it will just take the name of the linked table when you upgrade. Apparently, this is a known issue and will be fixed in the next release as per this thread.

BUG ALERT!!!

If you paid close attention to the file that you just upgraded, you can notice that the slicer for measure name is not working. Now this seems to work for people who have installed the Office Professional Plus (v 15.0.4420.1017) but for people like me who have installed the Excel Preview (v 15.0.4128.1025), the slicers don’t work. Turns out the reason was that FORMAT function doesn’t play well within a measure expression. All you have to do is to change the measure expression for MsrValue from

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
FORMAT ( Sum ( Fct[Sales] ), “$#,#0” ),
Min ( Msr[MsrId] ) = 2,
format ( Sum ( Fct[Quantity] ), “#,#0” ),
Min ( Msr[MsrId] ) = 3,
format ( Sum ( Fct[Profit] ), “$#,#0” )
)

to

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
)

Of course this does mean that you lose the formatting. Now you can see that the slicers are working. This clearly is a bug as it works in Excel 2010 (as well as in the Professional Plus edition of Excel 2013). I have raised a Connect issue here, please vote for it so that it can be rectified in the next release. Ok, time to run for the Black Friday shopping now!

Posted by SQLJason, 2 comments
Creating Maps in Excel 2013 using Power View

Creating Maps in Excel 2013 using Power View

There has been a lot of buzz in the BI community since yesterday, and the reason is none other than the public preview of Office 2013 (Excel 2013 in particular). There’s been a lot of articles written on the new features available and the integration of PowerPivot and Power View has really got everyone talking about. In case you still haven’t looked at the new features, here is the link for it. Meanwhile I can’t wait to blog about the spatial capabilities available in Power View, so I am directly jumping to the topic.

Creating Maps in Excel 2013 using Power View

Power View in Excel 2013 has the ability to create maps from your data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have the ability to zoom and pan as needed. Also, Bing maps automatically detects the location and hence you don’t need to provide a shapefile or even the latitude/longitude information. In this blog, I will take you through the steps to create a map report in Power View.

I) Creating a basic map report

1) Open up a new workbook Excel 2013 and then enter the following data in the cells

Country City Sales
USA Charlotte, North Carolina 100
USA Madison, Wisconsin 50
USA Jacksonville, Florida 140
USA Rochester, New York 40
USA Philadelphia, Pennsylvania 120

2) Select the entire data, go to the Insert tab and click on Power View icon.

select data in excel

You should get a loading screen while it takes a couple of seconds to open Power View

loading power view

3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below

Map in Power View

You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.

enable content in Power View

4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales.

Basic map report in Power View

5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.

map layout properties

It is interesting to note that the data used for creating the Power View report gets imported into PowerPivot by default. This is because Power View can only communicate through DAX currently, and hence needs a tabular model behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab and then selecting the manage tab.

powerpivot

Since there is no way to add additional data into this model without deleting and recreating the table, it would be a good practice to create the PowerPivot model first from linked tables, and then using the PowerPivot fields to create the Power View report. This way, we will be able to keep on adding data as long as there is a link between the table and PowerPivot. I will be showing you how to do this in the next part.

II) Creating a drill down in map report

1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.

Add to data model

Note that you can still create Power View reports directly, but we will be using this technique for the reasons mentioned above earlier. 2) Select the home tab, then click on Pivot table option and select Power View to create Power View report.

PivotTable-->Power View

It is recommended to set the reporting properties of the Country and City field, so that Power View can recognize them as geographical entities, as shown in the image below.

setting reporting properties

3) Now you should be able to see Power View (in case you don’t, you can click on a blank cell, go to the Insert tab and click on Power View icon). Notice that the Country and City fields have a map icon

map icon besides fields

4) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map and then drag both the country and city fields within locations. You can also put the Sales measure in the Size area. Now you should have a map report which drills down from the Country level to the city.

Adding drilldown fields to location

If you double click on the blue dot in the center (which is USA), you will get the drill down report by cities. Notice that the title has automatically changed from Sales by Country to Sales by City. You can also click on the up arrow (highlighted in the image below) to return to the parent report.

return to parent report

You can also create hierarchies in your PowerPivot model, and that can be directly added dragged and dropped to the Locations area. This will ensure that you don’t have to drag and drop each field individually and the drill down would be present across the entire hierarchy. You can also see the benefit of adding the linked table to the PowerPivot model instead of using a range. Now if I have to add data, I can just go to the excel sheet and append the rows that I want as shown below

Adding more data in linked table

Now, all I need to do is to go to my Power View sheet and refresh the report. You can see that the new data is already included in the report

power view report showing new data

III) Creating Pie charts in map report

1) Use the previous report, and then add the city to the Color area. Now you can see that there is a pie chart by cities at a country level.

Add city to color

2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below

Pie charts in Power View

You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below

multiples in Power View

Similarly, you can use the Tile By option also (however, this feature is not available if you use the multiples option) Another best practice is to concatenate your city names with state/country info also (as I have done in all the examples) as there might be duplicate city names and doing this will help Bing in geocoding it better. You can also do this operation as a calculated column in PowerPivot, if you don’t want the city names to be displayed with their country/region info appended.

IV) Creating a map report with latitude and longitude

1) Let’s say we already have fields in the database with longitude and latitude, and we want to use them instead of Bing geocoding for us. For the purpose of this demo, let’s use the data below and paste them into excel

Country City latitude longitude
Australia Melbourne, Australia -37.8136 144.9631
Australia Sydney, Australia -33.8737 151.2069
Australia Brisbane, Australia -27.4709 153.0235
Australia Perth, Australia -31.9529 115.8573
Australia Adelaide, Australia -34.9287 138.5999

I used the site http://www.findlatitudeandlongitude.com/batch-geocode/ to get the latitude and longitude of the address. 2) Now select the data and use that to insert a Power View report. Click on the City field and then select the Map option. Now the latitude and longitude would be mapped to the corresponding columns if the names are the same (else you can always drag and drop them) and your map report would be ready.

Power View report by latitude and longitude

Hopefully this post has given you a good start to start playing with your own map reports in Power View. There are again a lot of best practices embedded within the post and sorry for not doing a great job of consolidating them, but I am hoping that this will serve as an incentive for you to read this long post completely. And well, if you have not yet downloaded the office preview, do it now from this link.

Posted by SQLJason, 22 comments