PowerMap – Population of cities in India

In my last post During the September Bangalore UG meeting, I had presented on PowerBI, Power Query, Power Map and Online Search to be specific! In this post, I shall talk specifically about the Power Map feature.

Online Search is an option available in the Power Query tab (screenshot 1) in Excel. Online Search allows you to search public datasets to import data from a large collection of public data sources. Displays the Online Search pane so you can search for public data from Wikipedia. Search results list items that contain the search term anywhere in the title, description, or key words.


Using online search, I was able to search for a public dataset having the most populated cities in India during 2011. A screenshot of the data is shown below. I am not going to elaborate on how to get the dataset using online search as I had explained that in my earlier post.


Now that I had this data, I thought it would be a great idea to represent this in a format which forms an overlay on a map. You will need PowerMap installed before you can use this functionality. Power Map allows you to quickly visualize geospatial data that you have already brought into Excel with Power Query and mashed-up with Power Pivot. Power Map can now be found on the “Insert” tab in Excel for Office 365 ProPlus customers. Subscription customers will have access to all the new and upcoming features of Power Map. See screenshot below.


When you launch the Power Map add-in using the “Launch Power Map” drop-down option, you will have the option of creating a new tour or editing an existing tour. The new workspace will provide you options to:

1. Add a new layer and modify it

2. Modify the scene options and animations

3. Define the scale for the visualizations and the type of visualization

4. Define the type of maps being used

The screenshot below shows the work surface. You can see that I have multiple scenes added in my tour.


The final video of the PowerMap demo is available below. And all this took me less than 15 minutes! The file is available on OneDrive.




Default Trace–Performance Issues

There are multiple events that a default trace in SQL Server 2005 and above tracks which can be significantly useful for finding out areas of improvement. The events that I will be concentrating on are:

1. Missing Column Statistics – This event class indicates that column statistics that could have been useful for the optimizer are not available due to which an incorrect cardinality estimation could occur. This can cause the optimizer to choose a less efficient query plan than expected. You will not see this event produced unless the option to auto-create statistics is turned off.

2. Missing Join Predicate – This event class indicates that a query is being executed that has no join predicate. (A join predicate is the ON search condition for a joined table in a FROM clause.) This could result in a long-running query. This event is produced only if both sides of the join return more than one row.

3. Sort Warnings – This event class indicates that sort operations do not fit into memory. This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement). The EventSubClass field in this event shows whether this was a single pass or a multiple pass. A single pass (EventSubClass = 1) is when the sort table was written to disk, only a single additional pass over the data was required to obtain sorted output. A multiple pass (EventSubClass = 2) is when the sort table was written to disk, multiple passes over the data were required to obtain sorted output. A multiple pass is an enemy of query performance.

4. Hash Warnings – This event class can be used to monitor when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.  Hash recursion (EventSubClass = 0) occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. Hash bailout (EventSubClass = 1) occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. Hash bailout usually occurs because of skewed data. Another enemy of performance!

5. Server Memory Change – This event class occurs when Microsoft SQL Server memory usage has increased or decreased. You can even determine what is the current memory usage after the increase or decrease.

6. Log File Auto Grow – This event class indicates that the log file grew automatically. This event is not triggered if the log file is grown explicitly through ALTER DATABASE. Frequent log file growths are not food for performance.

7. Data File Auto Grow – This event class indicates that the data file grew automatically. This event is not triggered if the data file is grown explicitly by using the ALTER DATABASE statement.

Since this information is already available in the default trace, I decided to use my Default Trace Statistics Power View Excel sheet to track this information graphically. And this is what I got (see screenshot 1)!


So what is the above Excel sheet displaying?

1. The information available in the first column chart will show the Data and Log file grow events per database.

2. The first matrix in the middle of the Excel sheet shows the number of Sort Warnings and Hash Warnings with drill-down capabilities for each database to see the EventSubClass fields.

3. The second matrix shows the Missing Column Statistics and the Missing Join Predicate events for each database. The drill-down capability gives the name of the column statistics that was missing.

4. The line graph shows the change in memory for the SQL Server database engine.

Happy monitoring!

Previous posts in this series:

Schema Changes History Report

WOOT: Schema Changes History Report on Power View

The last post in this series talked about using Power View to analyze the data stored in the SQL Server’s default trace. I decided to take this a step further by creating the Schema Changes History report with the help of the data that I retrieved from the Default Traces. The advantage of a report created in Power View is that the interactivity which is missing in the standard report is available.

The way I created this report was to filter the data in the Power Pivot table using EventClass ID 46, 47 and 164 for only looking at the create, drop and alter commands which the default trace tracks. After that I created a table with a tile on the Database Name and a 100% Stacked Bar Chart to show the activity at a database level.

I also had to create linked tables for getting the Object Type and the Event Class Name that you see in the table below.

I will provide a final version of the Excel sheet once I have completed the other dashboards and sanitized the information available in the Power Pivot table.


Previous Post in the Series:

Default Trace Dashboard