Tuesday, September 20, 2011

Empower your BI using Powerpivot with the datasets of the Windows Azure Marketplace.

To empower you BI solutions, you want to merge data from different data providers. In most companies data is merged from databases within the company. As of today a lot more information is available which can be useful to empower your BI solution. I will explain this with an example.
Assume your company is making ice creams. The sales revenue of ice cream depends on the weather. The higher the tempature, the more ice creams will be consumed. So your revenue of ice creams should be compared with the tempature. By comparing your revenue with the tempature you can see if the increase in revenue is caused by a good job of your sales people or by extreem hot temparture of the last period. How do you know what the tempature has been in the last year? This kind of data will come available in the Windows Azure Market Place for premium data. It will enable information workers to integrate premium datasets available on DataMarket with their on-premise business data to perform analytics.

How to use premium Windows Azure DataMarket data?
In this example I will make use of a dataset which is for free: Datestream. Datestream is a date table feed designed for import into an Excel PowerPivot model. The table contains columns particularly suitable for time business intelligence applications.

  1. Sign on to the Windows Azure market Place.
  2. Select the Datestream dataset.
  3. Select the hyperlink: Explorer this dataset.
  4. Specify YearKey 2010. In my example I will analyze only the year 2010. Press on the Run Query button.
  5. To generate a DataFeed. Select Export and press on the Export to Excel Powerpivot Download button.
  6. Specify a name for your data feed and save it to a location on your PC.
  7. Open Excel PowerPivot and import your on premise data you want to analyze. For instance your ice cream sales per day.
  8. Press the From Azure DataMarket button to import the Datestream datafeed.
  9. Specify the Azure DataMarket dataset URL. Browse to the datafeed file you made in step 6.
  10. Specify your Account key. This can be found in the Windows Azure market place under the tab My account. Select account keys.
  11. Refresh your datasets in the PowerPivot window. You will get 365 rows to import the date details for the year 2010.
  12. Create a relationship between the date from the DateStream and your ice scream sales dates. This can be done in the design tab of the PowerPivot window.
  13. Now you can start analyzing your data. For instance, which day of the week do you sell the most ice creams.

Enjoy it to use the Azure DataMarket to empower your BI experience.

No comments: