• 3 min read

Unleashing insights from data in DocumentDB with Power BI

As customers develop applications in the cloud and store an increasing amount of data in DocumentDB, it is more important than ever to gain insights from this data. Today we are pleased to announce the Beta release of the Azure DocumentDB connector for Power BI.

Thousands of organizations are already using Azure DocumentDB to store, query and process JSON data at scale. As customers develop applications in the cloud and store an increasing amount of data in DocumentDB, it is more important than ever to gain insights from this data.

Today we are pleased to announce the Beta release of the Azure DocumentDB connector for Power BI. The connector is preinstalled in latest version of Power BI Desktop and is available in PowerBI.com as well. Business analysts and information workers can now leverage Power BI to slice and dice data from DocumentDB and create insightful visualizations for their JSON data.

For more information on Power BI and to download the latest Power BI Desktop, please visit the Power BI site.

Creating insightful visualizations as easy as 1, 2, 3

Creating a report with data from DocumentDB using Power BI Desktop is as easy as Connect, Transform and Visualize.

1. Connect

The DocumentDB connector comes readily available in the latest version of Power BI Desktop.  Once you install and launch the latest version of Power BI Desktop, you can locate DocumentDB connector under the Azure data sources section.  

DocumentDB Connector for Power BI

To connect to a DocumentDB account, all you need is the endpoint URL and the account key (read-only key is recommended), which is available in the Azure Preview portal on the Keys blade. Once connected, you can use the Navigator pane in Power BI Desktop to explore the data within the account.

DocumentDB Power BI Connector

The connector also supports the DocumentDB SQL grammar. You can use supported DocumentDB SQL query features including projections, UDFs and JSON materialization to retrieve the data you need. All you need to do is to specify a database name, a collection name, and a SQL statement in the Connect window.

The connector will then query against the specified collection in the database. The connector also supports fan-out queries to request data from multiple collections, then merges the results. Providing a SQL statement and a database name without a collection name will cause the connector to query across all collections within the database

DocumentDB PowerBI SQL

2. Transform

Power BI Desktop allows you to work with DocumentDB data from multiple accounts. Once data from various sources is loaded to the respective query models, you can use Power BI Desktop Query Editor to shape, transform, and merge these datasets into new models fitting your needs. Power BI Desktop Query Editor also supports flattening of arrays and nested JSON. For nested JSON, you can expand and select the fields needed directly from the Query Editor. The selected fields will be flattened and returned as part of the query result.

DocumentDB Power BI Query Editor

3. Visualize

Power BI Desktop allows you to build a report with a single visualization or full set of visualizations. Once you are done shaping and transforming your data in the Query Editor, you can save the data model and start building your reports. To build a report, simply drag and drop fields from the Field pane into the Report canvas.

Power BI Desktop provides a rich variety of visual types. You can choose the visual type desired from the Visualization pane. Most of the visual types can be customized to fit your needs. For more information on customizing visualizations, please visit Tutorial: Customize visualization title, background and legend.     

DocumentDB Power BI Report

 

To sum up, the DocumentDB connector for Power BI enables you to quickly construct a multi-perspective view full of insights with data from DocumentDB.

For step-by-step instructions on how to use Power BI with DocumentDB, please visit Visualizing DocumentDB Data with Power BI.

Please feel free to share your thoughts on this new capability below, we’d love to hear your feedback.

If you need any help or have questions, please reach out to us through the developer forums on stack overflow or schedule a 1:1 chat with the DocumentDB engineering team.

Stay up-to-date on the latest DocumentDB news and features by following us on Twitter @DocumentDB.