Export EMT data to Cosmos DB to use it with Power BI

In this article you will learn how to export EMT assets data to Cosmos DB and how to create reports and dashboards in Power BI based on that data.

Prerequisites

You will need a Azure Cosmos DB account with NoSQL API. If you don't have it you can follow steps here to create it. You will also need EMT's account and project URL. Lastly you will need a PC with Power BI Desktop installed.

Prepare to run the export script

  1. Go to Azure Portal and open the Azure Cosmos DB account. Save the URI visible on the Overview page.

  2. On the left there's navigation menu. Go to Settings -> Keys and save the Primary Key.


Running the export script

Follow the steps here to run the export script. Use the Cosmos DB account details that you saved earlier. After completing the export come back here to see how to connect to the exported data from Power BI

Connect to CosmosDB from Power BI

  1. Open Power BI and select "Get Data from other sources"

  2. Next select "Azure Cosmos DB v2" connector from the list.

  3. Enter the Cosmos DB endpoint that you saved earlier. Also ensure you have selected "DirectQuery" in Data Connectivity Mode.

  4. Enter the access key that you saved earlier.

  5. A Navigator window should appear. Expand the databases and collections and select which tables you wish to load. Then click on "Transform Data".

  6. A Power Query Editor Window should appear. From there you can use the buttons in the top bar to select which columns you wish to keep, filter rows, sort the data and much more. Click on "Close & Apply" when you are done customizing the query.

  7. You can now drag the fields from Data pane onto the report canvas to build visuals with the data