About these ads

PowerMap – Population of cities in India   Leave a comment


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.

image_thumb7

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.

image

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.

image

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.

image

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.

References:

PowerMap
http://blogs.msdn.com/b/powerbi/archive/2014/02/25/power-map-for-excel-now-generally-available-automatically-updated-for-office-365.aspx

About these ads

Posted October 23, 2014 by Amit Banerjee in Excel, PowerBI

Tagged with , , , ,

PowerBI – Online Query   2 comments


This is quite a late post for a presentation that was done in September. But as they say, better late than never! 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 Online Search using Power Query.

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.

image

imageWhen you click on the Online Search button, you will be present with a search column which allows you to search for data sets online. Before you can do that, you will need to sign in using an account which can utilize PowerBI features. For this blog post, I will be using the Online Search to create a trending chart for Microsoft (MSFT) stock prices.

I used the search string “MSFT Stock” which gave me a list of stock quotes available online. You will be presented with two sets of data:

a. From your organization if you have signed in using your Organizational Account and if someone from your organization has shared dataset pertaining to your search string

b. Publicly available datasets which match your search string

The mouse over on the data set (Screenshot 2) will show you multiple things like a sample view of the data, the columns and the data source details.

If you think that the data set is good to use, you can use the Load drop-down option to load the data into a data model or into an Excel sheet (See Screenshot 3).

image

Once you have loaded the data as per your choice, you can cleanse and transform your data using Power Query – Query Editor (see Screenshot 4). Once you have created the necessary transformations, you can create pivot charts and pivot tables using the data. The finished product is shown in Screenshot 5.

I had used a Time dimension from the Azure marketplace to prepare my X-axis that you see in the graphs. How that was done is a topic for another post. Once I had all the relationships built, I was able to build the visualizations shown below. All this took me less than 15 minutes! Smile It is that quick provided you have a decent net connection.

Now here is the awesome part. Going to Data -> Refresh, you can refresh all your data and get the latest view without having to re-design anything again. So this is all ready to be published or shared with anyone you want without worrying if they know how to use Power Query or not!

The Excel file is available on One Drive as well.

As you can see from this post, it is quite easy to create visualizations which provide vital insights using Excel 2013 and Power Query within minutes! Look forward to posts in the future on this topic.

 

image

image

References:

Power Query Ribbon
http://office.microsoft.com/en-ca/excel-help/guide-to-the-power-query-ribbon-HA103993930.aspx

Lessons learnt while using the Cloud Adapter   Leave a comment


During the last week of August, I had blogged about how to get your on-premise database to your SQL Server instance running on an Azure virtual machine. I had run into a few issues while trying to run the wizard provided by Management Studio.

The First Stumble

This error is easy to circumvent and pretty much mentioned in the online documentation. The error message would read as:

Failed to locate a SQL Server of version 12.0.2000 or later installed on the remote machine. Please verify that a SQL Server of the same or higher version than the source SQL Server is installed on the remote machine.

The above error is self-explanatory. There is a requirement that the source database engine version be lower or equal to the version of the SQL Server instance running on Azure. Eg. You cannot deploy a database from a SQL Server 2014 instance to a SQL Server 2012 instance running on an Azure VM.

The Second Stumble

The second common error that you might run into is:

The Cloud Adapter port configuration is not valid. Verify the virtual machine endpoint configurations.

The above error will be encountered when the endpoint is not configured for the Azure virtual to accept connections from the outer realm! This can be easily rectified by adding a TCP endpoint to your Azure virtual machine for 11435 which is the port that the SQL Server Cloud Adapter Service is listening on. This is also mentioned in the online documentation. Once you have created the endpoint for your Azure virtual for your on-premise server to connect with the Cloud Adapter service, your endpoint configuration should look like the one in the screenshot below:

image

The Third Stumble

The next issue could be with permissions/authentication or it might not be as easy as it seems.

Cloud Adapter operation failed due to invalid authentication. Verify the virtual machine name, user name, and password.

So the first thing to check if you have the correct account name and password. If it is due to an authentication error, then the application event log of the Azure Virtual Machine will show the following error with the source as SQL Server Cloud Adapter service as shown in Screenshot 2. The text of the error message is mentioned below.

Access denied for user <user name>

image

The other error that you might encounter is when the SQL Server Cloud Adapter service tries to enumerate the database engines installed on the virtual machine. The error would still be talking about the authentication which is reported by the management studio wizard but a little investigation into the application event logs of the virtual machine will show the following error:

[Error] <ip address> Exception in GetSqlInstances(): SQL Server WMI provider is not available on <machine name>.. Stack trace:    at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.get_Proxy()
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstanceCollection.InitializeChildCollection()
at Microsoft.SqlServer.Management.CloudAdapter.Tasks.GetSqlInstances()
at Microsoft.SqlServer.Management.CloudAdapter.CloudAdapter.GetSqlInstances(String username, String password). Inner Exception: Invalid namespace .

The above error clearly states that the GetSqlInstances() method failed. Microsoft.SqlServer.Management.Smo.Wmi namespace contains classes that provide programmatic access to the Windows Management Instrumentation (WMI) from an SMO application. I had talked about needing the shared management objects in an earlier post. The SQL Server 2014 WMI provider is also required which is available by installing the client connectivity components from any SQL Server 2014 setup including SQL Server Express. The components that I had installed were:

a. Client Tools Connectivity

b. Client Tools Backwards Compatibility

If you are not sure if you have the WMI provider, then look for the file “C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof“. This is the SQL Server 2014 MOF file. Another way to test if the WMI provider is working without running the wizard every time and have it fail is to run the PowerShell commands below on your Azure Virtual Machine. This script will tell you where the instance enumeration being performed by the deployment wizard will work or fail.

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') '.'
foreach ($svi in $m.ServerInstances)
{
	$svi.Name;
}

This post was intended to document that common issues that you might run into while deploying a database from an on-premise SQL Server instance to a SQL Server instance running on an Azure Virtual Machine.

Follow

Get every new post delivered to your Inbox.

Join 1,319 other followers