VS2012 Ultimate SSIS Start Up Error

If you are using Visual Studio 2012 (VS2012) for Business Intelligence solutions and have SQL Server 2012 SP1 installed, you may be running into a VS2012 Start Up issue. This happened to me this morning when trying to open a non BI solution in VS2012.

It came up with “Microsoft Visual Studio 2012 has stopped working” error message as shown below. I also tried running devenv /safemode as well, but this does not help.

VS2012 Crashed At StartUp

Essentially, the main issue was related to:

System.TypeLoadException: Could not load type ‘Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils

This issue happened on on my machine with SQL Server 2012 SP1 with KB2793634 (v11.0.3128) installed.

After tweeting it online, Jeff Rush (@JeffRush) mentioned a link that suggests SQL Server 2012 SP1 CU4 may need to be applied. Applying SQL Server 2012 SP1 CU4 (and also restarted my machine) didn’t seem to do the trick.  I did some more research and eventually found out that the problem was only solved in SQL Server 2012 SP1 CU5.

Applying the patch (part of hotfix included in SQL Server 2012 SP1 CU 5), 465914_intl_x64_zip.exe has solved the issue. After the patch, my SQL Server 2012 version is 11.0.3373.

Massive thanks to Jeff Rush for giving me a guidance that the issue was probably fixable with a hotfix for SSIS – not sure why I did not think of that. (That’s why Twitter and #SQLFamily rocks!)

Further Reading

Fix: The type initializer for ‘Microsoft.DataTransformationServices.Project.SharedIcons’ threw an exception. (Microsoft Visual Studio 2012) by Roel van Lisdonk

System.TypeLoadException: Could not load type ‘Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils’ on SQL Server Forums

Cumulative Update package 5 for SQL Server 2012 SP1 on Microsoft Support

 

 

Power BI In A Jiffy: Schedule Data Refresh

Schedule Data Refresh allows Windows Azure SQL Database, OData and SQL Server data sources configured in Power Pivot to be refreshed. Any of the data sources that are retrieved via Power Query are not supported for Schedule Data Refresh. Any WASD, OData and/or SQL Server database retrieved using Power Query then added to the Data Model (Power Pivot) cannot be refreshed either. That’s the “in a jiffy: part. The rest of the post is a walk through of the Schedule Data Refresh feature.

 

As Power BI features are updated fairly frequentlly, the following walk through is valid at the time of writing. Future enhancements will be followed up in newer posts.

Schedule Data Refresh Feature

I believe this is a new addition to Power BI that I might have missed recently. So, I decided to check it out a little bit a few minutes before my presentation at SQL Malibu on Feb 12, 2014 on “Building Your Myth Busting Lab With Power BI“. When preparing for this presentation, I purposely left out any specific Data Sources, Data Management Gateway and Schedule Data Refresh features as I know that the Power BI Product Team is releasing new features and fixing bugs quite frequently. The cloud offering makes it possible for them to do so.

The features around the “Data Management” side part of it was OK-ish a few months ago and it has been great, because it means that the Product Team has lots of opportunity to shape the features to meet our needs through feedback that the public give them from their preview / trial offerings.

Now that Power BI is in GA in most countries (except for Australia, where I am), I think it’s a good idea to look into Scheduled Data Refresh. When I first read the article about Data Refresh, I initially thought this would also work for Power Query queries that have been added to Data Model. I was mistaken.

Data Sources in Power Pivot

Only Windows Azure SQL Database (WASD), SQL Server and OData data sources created in Power Pivot are supported for scheduled data refresh. If you are using SQL Server database, you will need to make sure that you configure Data Management Gateway and that the server is up and running at the scheduled data refresh time. Below is a snapshot of Power Pivot Data Sources that I have scheduled for refresh, excluding the Power Query one (as it doesn’t work).

Power Pivot Data Sources

 

If you are accessing a local database (presumably for testing), make sure that you fully qualify the name of the server, i.e. <Server Name>\<SQL Server Instance name>. If you use a shortcut such as “.\<SQL Server Instance name>, the Data Management Gateway will not be able to resolve the name and result in an error.

 

Enabling Schedule Data Refresh

Once the workbook is uploaded to Office 365 and enabled, you can schedule data refresh as shown below from the Power BI Site.

Accessing Schedule Data Refresh

You can select the data sources that you want to schedule the data refresh for. Please note that in my example, I also have Power Query connection to my local SQL Server database. The Power Query data source cannot be refreshed. So, I’m only enabling the OData, WASD and SQL Server data sources for scheduled data refresh. At the time of writing, you cannot schedule each data source at different time. So, either they are scheduled for the same time or not excluded in the schedule at all.

 Schedule Data Refresh Settings
Once you set the data refresh schedule, you can click on “save and refresh report”. If the report has not finished refreshing and you click on it again, it will come up with an error message similar tot the following:

Refresh cannot be performed on already running refresh

Schedule Data Refresh History

If you go to the History page, you can check the status under the “Next Refresh” line. Below is a screenshot of history log after a few on-demand and scheduled refreshes. The list is quite comprehensive and lets you know which part of the data sources that cannot be refreshed. When the current data source hit an issue during refresh, the rest of the data sources enabled for refresh in the workbook will not be refreshed.

Schedule Data Refresh History Log

 

Email Refresh Failure

In the Settings, you can also configure one or more email address to send refresh failure notifications to. This send email feature is definitely handy. The error notification sent by email is similar to the following.

Power BI Schedule Data Refresh Error Email

 

What could be improved?

1. The ability to configure different data sources in the workbook at different times or different refresh rate. This can be a double-edged sword though. Data source synchronisation is never easy.

2. Lower latency for status update to reflect the refresh process. At the time of writing, I find that I need to wait for about 10 – 30 minutes before I find out that the 30 second refresh is successful.

3. When the data source names in Power Pivot have been updated, the Schedule Data Refresh should ideally reflect that. At the time of writing this doesn’t happen at all.

Do you have your own suggestions for improvement / bugs to report? Please comment below and I will do my best to pass them on to the Power BI team. Alternatively, you can write to them in the Power BI forum.

 

Hope this walk through has been useful.

 

Wrap Up

In summary, Schedule Data Refresh allows users to refresh workbooks in Power BI with the latest data at a defined date / time as well as on-demand. You can schedule all the data sources in the workbook to be refreshed or only a selection of them, for the same schedule date and time. Although the feature is a little simple at the moment, it is much anticipated to match its cousin’s, on-premise SharePoint’s “automatic refresh Power Pivot“.

 

Further Reading

Keeping Your Reports Up to Date with Scheduled Data Refresh by Power BI Team

Automatically Refresh PowerPivot Data in SharePoint on Technet

Announcing the General Availability of Power BI for Office 365 by Power BI Team

 

 

 

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

 

 

Free Business Analytics Learning #pass24hop

24 Hour of PASS Business Analytics edition starts on 6 Feb 2014 at 3 AM AEDT (Sydney, Canberra, Melbourne). There will be 12 one hour back to back live sessions where you can ask your questions, followed by 12 one hour replay sessions.

The topics covered in this event includes:

  • Advanced Analysis Techniques
  • Analytics and Visualisation
  • Big Data
  • Information Delivery
  • Strategy and Architecture

For further details on the session and to register, please visit: http://www.sqlpass.org/24hours/2014/ba/Sessions.aspx

I feel very honoured to be able to contribute as a moderator for two 24 Hours of PASS sessions with experts in the SQL Server community:

Hope to see you at the sessions!

24 Hours of PASS BA 2014

Julie

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!

Julie

 

 

 

 

Power BI In A Jiffy : Featured Reports

This is one of the first few series on Power BI In A Jiffy. The series is meant to provide a quick update on Power BI related news. Being on the cloud, sometimes changes happen over night and new features are added. The Power BI developer team has the ability to release updates in a shorter cycle than most on-premise / box applications.

Power BI Preview Featured Reports

Power BI site for Power BI Preview has just recently been updated with a new section called Featured Reports as shown below. If you have signed up for Power BI Preview, you would see the following addition on your Power BI site.

Power BI Site - Featured Reports

 

Excel files that are listed under Documents can be added to the Featured Reports section by clicking on the “” area and choosing Feature option.

 

Add Feature

 

 

 

If you wish to remove it from the Featured Reports list, you can simply click on the “” sign of the report that you wish to unfeature as shown below.

Unfeature

 

Please note that Featured Reports are effective site-wide, meaning that other users / team members would see those reports as featured too. You can feature and unfeature reports that are created by other users. In my example above, I have just featured / unfeatured report created / uploaded by another team member at LobsterPot Solutions.

 

Wrap Up

Featured Reports help the team to access frequently used / popular reports quickly. Reports / Excel files are promoted to Featured Reports by a member of the team, for the team.

 

Further Reading

Power BI for Office 365

 

Power Query for D&B Data Mashup – Step by Step

A couple of weeks ago, I posted an introduction to Power BI for data mash up using D&B data. Generally speaking, before you buy anything, you want to test it out first. The same rule applies to data. You want to make sure that you get the right quality of data and do some testing on this. The easiest way for me to do this is by using Power Query. There are other ways to test it out of course, but in this article I will focus on using Power Query.

Case Study

I have a list of companies with City, State and Country, that may or may not be correct. The requirement is to get more company details, such as Number of Employees, Annual Sales, the year that the company started, the Legal Status and Phone Number. These are common information that competitors or marketers would like to investigate.

After a few minutes of research at Windows Azure Market Place, I have found a couple of interesting data services provided by D&B:

1. Company Cleanse & Match for Microsoft SQL Server Data Quality Services – I will refer this as Company Cleanse & Match for the rest of the article.

2. Company Firmographics – Industry and Sales - I will refer this as Company Firmographics for the rest of the article

Completed Excel Solution is available here: Power Query for DnB Data Mashup.xlsx. You will need Excel 2013 with Power Query installed – check full System Requirements for Power Query here.

Step by Step

1. Prepare test companies

Let’s first start with the data that we want to test out. I have a list of 7 companies in an Excel table, called “CompanyInput” as shown below.

Excel Table: CompanyInput

 

 

2. Create a Power Query function for Company Cleanse & Match

The strategy here is to create a Power Query function that represent the call to Company Cleanse & Match, which can be called by another query.

To do so in the same Excel workbook:

a. Click on Power Query > From Other Sources > From OData Feed from the ribbon.

Create Power Query function for Company Cleanse Match via OData Feed

Note: At the time of testing this, the D&B Company Cleanse & Match service does not work with the Windows Azure MarketPlace option.

b. Enter the Service Root URL as provided on the “Details” tab of the D&B Company Cleanse & Match page, which is:

https://api.datamarket.azure.com/DNB/DQSCompanyMatch/v1/

Specify OData Feed URL of D&B Company Cleanse & Match

Note: This step assumes that your Azure account has a valid subscription to the D&B Company Cleanse & Match service.

c. Clicking OK will display the Query Editor window with the following message:

Power Query dialog box

d. Rename the “Query1″ to “fnSuggestCompanyDetails”. The new name is now the friendly name that we will use in other Queries that we will create using Power Query.

Under the Navigator menu, you can see a “fx” symbol next to SuggestCompanyDetails, which is the function that is exposed by the D&B Company Cleanse & Match service. Clicking on the script symbol on the Formula Bar as shown below will bring up the Query Editor window.

Script Button

So, all the steps that we have done in Step 2 essentially is to produce the script as shown below, which defines “fnSuggestCompanyDetails”.

M query - fnSuggestCompanyDetails

Code:

let
 Source = OData.Feed("https://api.datamarket.azure.com/DNB/DQSCompanyMatch/v1/"),
 SuggestCompanyDetails = Source{[Name="SuggestCompanyDetails"]}[Data]
in
 SuggestCompanyDetails

e. Clicking Done on Edit Query then on Query Editor, will create a new worksheet, Sheet2 containing the fnSuggestCompanyDetails definition.

New Worksheet containing fnSuggestCompanyDetails

 

 

3. Repeat Step 2 for Company Firmographics service. The OData Feed URL for this service is:

https://api.datamarket.azure.com/DNB/CompanyFirmographics/v1/

Don’t forget to rename the Query to fnGetFirmographic, so that you have something similar as below:

fnGetFirmographic

This step should create a new worksheet, called Sheet3 which contains the definition of the new function of fnGetFirmographic.

New Sheet created to contain fnGetFirmographic

Code:

let
 Source = OData.Feed("https://api.datamarket.azure.com/DNB/CompanyFirmographics/v1/"),
 ReturnData = Source{[Name="ReturnData"]}[Data]
in
 ReturnData

4. Go to Power Query and click on Fast Combine.

Note: This step is important as it allows. you to call other Queries (or functions that we have defined earlier) in another Query within the same workbook.

Enable Fast Combine Click on Enable on the next dialog box: Enable Fast Combine     5. Create a new Query based on the CompanyInput table in Excel. a. Place the cursor on CompanyInput table, and click on Power Query > From Table From Table Clicking From Table will bring up the Query Editor dialog box as follows, which brings the data from the table in CompanyInput table.

Query Editor: CompanyInput

b. Right click on Min Confidence column, then click on Insert Column > Custom… Insert Column Custom   c. Enter the function as specified below. The order of the input parameter must match exactly as laid out by D&B Company Cleanse & Match in Step 2d.

Insert Custom Column - fnSuggestCompanyDetails

  d. Clicking OK on the dialog box will return Query Editor with a new column added called “Custom” with “Table” value. Right click on the Custom column header, and choose to Expand a handful of columns (including DunsNumber as you will need it as an input parameter for the fnGetFirmographic call later).

Expand Custom column to get DUNS Number

Clicking OK will cause Power Query to call D&B service to bring back the values for the asked columns, and after a few seconds, the Query Editor dialog box should display more Custom columns as shown below:

Output of Expanding Custom Columns

  e. Scroll right across so you can see Custom.Confidence column, and insert a new Custom column to bring back more data from fnGetFirmogrpahic.

Insert Custom Column - fnGetFirmographic

  f. Similar to Step 5f, expand the Custom column to choose a few columns from the Company Firmographics output. Expand Fimogrpahics Columns   g. Clicking OK from the drop down list above should return Query Editor with additional chosen columns as shown below.   Custom Column Details - fnGetFirmographic h. Clicking Done on Query Editor will create a new sheet that contains the final output of the data from both D&B Company Cleanse & Match as well as Company Firmograhics. Final Output   Code:

let
 Source = Excel.CurrentWorkbook(){[Name="CompanyInput"]}[Content],
 InsertedCustom = Table.AddColumn(Source, "Custom", each fnSuggestCompanyDetails([Company Name],null,null,null,[State],[City],[Country],null,[Max Returned],[Min Confidence])),
 #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom, "Custom", {"RecordId", "DunsNumber", "CompanyName", "AddressLine", "ZIP", "State", "City", "Country", "Confidence", "MatchGradeText", "MatchGradeComponentCount", "MatchGradeComponentTypeText", "MatchGradeComponentRating", "MatchGradeComponentScore"}, {"Custom.RecordId", "Custom.DunsNumber", "Custom.CompanyName", "Custom.AddressLine", "Custom.ZIP", "Custom.State", "Custom.City", "Custom.Country", "Custom.Confidence", "Custom.MatchGradeText", "Custom.MatchGradeComponentCount", "Custom.MatchGradeComponentTypeText", "Custom.MatchGradeComponentRating", "Custom.MatchGradeComponentScore"}),
 InsertedCustom1 = Table.AddColumn(#"Expand Custom", "Custom", each fnGetFirmographic([Custom.DunsNumber])),
 #"Expand Custom1" = Table.ExpandTableColumn(InsertedCustom1, "Custom", {"AnnualSalesUSDollars", "EmployeesHere", "EmployeesTotal", "LegalStatus", "CompanyStartYear", "SingleLocation", "OutOfBusinessInd", "LineOfBusiness", "IndustryCodeType1", "IndustryCode1", "IndustryCodeType2", "IndustryCode2", "FormerCompanyName", "TransactionFlag", "TransactionStatus", "TransactionStatusCode"}, {"Custom.AnnualSalesUSDollars", "Custom.EmployeesHere", "Custom.EmployeesTotal", "Custom.LegalStatus", "Custom.CompanyStartYear", "Custom.SingleLocation", "Custom.OutOfBusinessInd", "Custom.LineOfBusiness", "Custom.IndustryCodeType1", "Custom.IndustryCode1", "Custom.IndustryCodeType2", "Custom.IndustryCode2", "Custom.FormerCompanyName", "Custom.TransactionFlag", "Custom.TransactionStatus", "Custom.TransactionStatusCode"})
in
 #"Expand Custom1"

 

 

You can also rename columns. To do that, click on Filter & Shape under Query Settings side pane. For example, instead of Custom.DunsNumber, it would be more user friendly to call it DUNS Number.

 

Important Notes

Every time you make changes to the query, Power Query will make the calls to D&B Data Service. Since the D&B Services are based on number of transactions per month, it is advisable to make sure that you are using a trial subscription while developing.

The Step by Step section above is accurate as at 1 Sep 2013. There may be some changes to the API which may render the instruction above invalid.

Earlier I mentioned that try it before you buy it. Most companies that sell data, should be able to provide you with reasonable good documentation and would be able to supply you with the latest documentation in case their data service changes. As an example, the Company Cleanse & Match for Microsoft SQL Server Data Quality Services provides Reference Guide, which is helpful in understanding what data you are getting.

Company Cleanse & Match for Microsoft SQL Server Data Quality Services Reference Guide

 

Wrap Up

Data has become a commodity where it is sold, massaged and reused in many different ways. One of the ways to purchase data is through Windows Azure Market Place. Power BI helps us easily to test data and to quickly  prototype self-service analysis. Using Power Query, you can retrieve and perform data mash up, within Excel. The data sources can come from an Excel table and a number of other data sources such as D&B Data services on Windows Azure Market Place, as shown in the case study above.

No doubt when you deal with larger scale of data with more complexities, that it may be better to use SQL Server to do the job, as shown in this example.

 

Further Reading

Company Cleanse & Match for Microsoft SQL Server Data Quality Services - Windows Azure Market Place

Company Firmographics – Industry and Sales - Windows Azure Market Place

Power Query articles by Chris Webb (No doubt that if I have some questions about Power Query, I generally go to Chris Webb’s website first)

Power Query: Creating a Function to Correct and and Verify Addresses via External API – Rafael Salas (Thanks to Rafael for the great tutorial on how to reusing code in Power Query via functions)

Cleansing your Customer Data using Dun & Bradstreet DQS service - MSDN DQS Blog

Power BI for D&B Data Mashup – Introduction - Ms SQL Girl

 

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

Bye bye GeoFlow, Hello Power Map!

If you have been following Worldwide Partner Conference 2013 (#WPC13) and Microsoft SQL Server blog, you would have heard about all the new features that are coming to Office 365. I am excited about the news as it means that small and medium businesses have not been forgotten. I am a little biased about this as I love technology and from a consultant’s point of view, I get to think about how to leverage these new features as a powerful and competitive strategy for our clients.

One of the most impressive new features in Office 365 is Power BI. Yes, it is “Power” themed. GeoFlow is now called Power Map. The new features are not available yet, but you can register just like I have.

Power BI for Office 365

Power BI related tools are:

  1. Power Pivot
  2. Power View
  3. Power Map (formerly known as GeoFlow)
  4. Power Query (formerly known as Data Explorer)

Other exciting news on Office 365 is natural language query engine and a Data Management Gateway as outlined on What powers Power BI in Office 365?).

 

Enjoy the Power BI demo by Amir Netz at WPC 13!

 

Further Reading

What powers Power BI in Office 365? at Office 365 blog

Introducing Power BI for Office 365 at SQL Server blog

Worldwide Partner Conference (#WPC13)

 

 

GeoFlow, SQL Server and Millions of Rows

Over the past few months, I have been asked if it is possible to connect GeoFlow to SQL Server database; and the more intriguing question is, why don’t GeoFlow connect to Tabular Model straight away instead of using PowerPivot. Although I do not know if there is such a plan related to the latter question, I do know that you can visualise data in SQL Server database or Tabular Model using GeoFlow.

The key ingredient for exposing this data is by storing and modelling it in PowerPivot. Why so? Perhaps one of the reasons is that most organisations, big or small, and individuals have or know how to use Excel (yes, they will need Excel 2013). Buying Excel 2013 Professional edition or higher would be cheaper to afford than the cost of buying a license for SQL Server. So, PowerPivot is the middle ground and it is the best win-win situation, for now. Those organisations which have invested time and effort converting their PowerPivot workbooks to Tabular Models however, would probably appreciate being able to expose their data in Tabular Model using GeoFlow.

Exposing SQL Server for GeoFlow

I have recently posted a step-by-step instruction to expose data stored in SQL Server database and Tabular Model to GeoFlow at Microsoft Customer Reviews of STB Products.

In the step-by-step instruction, I did mention that I was using 22 Million of records. The sample workbook is about 580 MB and shared via SkyDrive. PowerPivot in the workbook contains daily data of Internet Speed tests for 5 years and about 2,200 unique locations. The workbook cannot be opened using Excel Web App due to the size and GeoFlow is not supported in Excel Web App either. So, you will need to have Excel 2013 Professional edition or above, and have GeoFlow installed.

I have also created a tabular model based on the 22 Millions records, but only exposing the aggregated data via DAX. The PowerPivot in this workbook contains about 1 Million of records, containing monthly data for 5 years worth of Internet Speed tests conducted in about 2,200 unique locations. The workbook is about 6.2 MB and shared through SkyDrive.

GeoFlow for Excel 2013 - SQL Server Data Refresh 22M rows

 

I am deliberately making the workbooks available via SkyDrive, in case in the future and wouldn’t it be nice if, GeoFlow can be supported in Excel Web App too :)

Wrap Up

GeoFlow Preview for Excel 2013 can connect to SQL Server via PowerPivot. This article has shown how GeoFlow can render geospatial information from millions of rows stored in SQL Server database and Tabular Model.

 

Further Reading

GeoFlow for SQL Server Database and Tabular Model at Microsoft Customer Reviews of STB

Sample Workbook: GeoFlow using SQL Server database

Sample workbook: GeoFlow using Tabular Model