Big Data/Analytics Zone is brought to you in partnership with:

Pro Direct Delivery Manager on Windows Azure Platform of Microsoft & Senior System Analyst in Sonata Software North America Inc. Anindita is a DZone MVB and is not an employee of DZone and has posted 6 posts at DZone. You can read more from them at their website. View Full User Profile

Big Data Analytics: PowerBI (PowerQuery & PowerMap) Dashboards Using HDInsight

11.25.2013
| 4580 views |
  • submit to reddit

In order to work on Big Data Analytics (ClickStream, Sentiment, RealTime), it's very important to work with PowerBI (PowerQuery & PowerMap) using Office 365. Microsoft Power Query Preview is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery and access. Power Query enables users to easily discover, combine, and refine data for better analysis in Excel.

Power Map Preview for Excel 2013 allows you to plot geographic and temporal data visually, analyze that data in 3D, and create interactive tours to share with others.

  • Download PowerQuery addin for Excel 2013 from here & PowerMap addins for Excel 2013 from here.

Let me show you a brief overview about Power Query after installing the addins for excel. open the PowerQuery tab & check the data importing options.

PowerQuery

  • Under Data sources option on PowerQuery , you will find options to import data from HDFS, Azure HDInsight , even from Facebook too.
  • For this demo, I have selected , HDFS (Hadoop distributed File System) & Azure HDInsight option.
  • Lets first create a HDInsight cluster in portal.

Cluster

Next , provide final configuration values(Azure Storage Account Vaults details) in order to create the cluster.

[a href="http://anindita9.files.wordpress.com/2013/10/addconfig.jpg"]AddConfig

  • Lets integrate the HDInsight cluster data with PowerQuery of Excel. Choose ‘Data Sources‘ as ‘Azure HDInsight Cluster‘.

HDInsight

  • On Query Editor, specify the hdfs table data by selecting appropriate query & click on ‘done‘.

QueryEditor

  • Next, click on PowerView in order to create an interactive dashboard with PowerView using PowerQuery from HDFS data of Azure Storage Vault(ASV).

PowerView

  • To work with PowerMap for Office 365: after installing the powermap addin , lets choose the ‘Map‘ option from ‘insert’ tab of excel 2013.

PowerMap

  • Created a sample report using World Bank’s data on Income Basis from Windows Azure Marketplace which has been imported  into hdfs.

Map

    • Created a video tour of the PowerMap dashboard using World Bank’s statistical data of countries by income level. Access it here.
Published at DZone with permission of Anindita Basak, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)