Share via


Choosing an Excel technology

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.

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.

Next Topic | Previous Topic | Home | Community