Choosing an Excel technology
.png "patterns & practices Developer Center")
From: Developing big data solutions on Microsoft Azure HDInsight
Excel offers a wide range of options for consuming and visualizing data. It can be difficult to understand how these technologies work together, or how to choose appropriate technologies when you want to analyze the results generated by HDInsight. The following table shows the specific capabilities of each technology in relation to common data consumption and visualization tasks in a big data solution based on HDInsight.
|
Editions: |
All editions |
Office 2013 Professional Plus Office 365 ProPlus |
Office 365 ProPlus |
|||
|---|---|---|---|---|---|---|
|
Features and tasks |
Native Data Tools |
PowerPivot |
Power Query |
Power View |
Power Map* |
Power BI Sites* |
|
Import data from Hive using ODBC |
Yes (one table at a time) |
Yes (multiple tables) |
|
|
|
|
|
Import data from Azure blob storage |
|
|
Yes |
|
|
|
|
Load data into a data model |
Yes |
Yes |
Yes |
|
|
|
|
Share queries with other users |
|
|
Yes* |
|
|
|
|
Design a data model |
|
Yes |
|
|
|
|
|
Create interactive charts |
Yes |
|
|
Yes |
|
|
|
Show geographic data on a map |
|
|
|
Yes |
Yes* |
|
|
Show geographic data changes over time |
|
|
|
|
Yes* |
|
|
View data in workbooks in a browser |
|
|
|
|
|
Yes* |
|
Support natural language queries (Q&A) |
|
Yes* (define synonyms) |
|
|
|
Yes* |
* Requires Office 365 ProPlus with a Power BI subscription
Note that the table does not reflect qualitative aspects of the technologies, such as their ease of use or flexibility. For example, you can use the native data tools in either Excel or Power View to create interactive charts. However, the range of visualizations available and the user experience when visually exploring data in Power View is generally better than that offered by PivotCharts and other native visualization tools.
In many scenarios you are likely to use a combination of technologies. For example, you may use Power Query to import the results of HDInsight processing jobs into the workbook data model, and then use PowerPivot to refine the data model to define relationships, hierarchies, and custom fields. You may then use native PivotTable functionality to analyze data aggregations, before using Power View to visually explore the data. Finally, you might publish the workbook, including the Power View visualization, as a report in a Power BI site where it can be viewed in a browser, and its data model can be included as a data source for Q&A natural language visualization.
Figure 1 shows how Excel and Office 365 technologies work together to help organizations analyze the big data processing results generated by HDInsight.
.png "Figure 1 - Using Excel and Office 365 technologies to analyze big data processing results")
Figure 1 - Using Excel and Office 365 technologies to analyze big data processing results
The options discussed here assume that you want to use Excel to consume and visualize data directly from HDInsight, or from the Azure blob storage it uses. However, in many scenarios the results of HDInsight processing are transferred to a database (for example, a data warehouse implemented in SQL Server) or an analytical data model (for example, a SQL Server Analysis Services cube). You can use native Excel data connectivity, PowerPivot, and Power Query to consume data from practically any data source, and then use native visualization tools, Power View, Power Map, and Power BI sites as described in this section of the guide.