How to Visualize CloudQuery Data with Microsoft Power BI

In this guide, we will walk you through how to set up CloudQuery to build your cloud asset inventory in PostgreSQL and connect it to Microsoft Power BI for visualization, monitoring and reporting.

General Architecture

• ETL (Extract-Transform-Load) ingestion layer: CloudQuery
• Datastore: PostgreSQL
• Data Visualization and Exploration Platform: Microsoft Power BI

What you will get

• Raw SQL access to all your cloud asset inventory to create views or explore any questions or connection between resources.
• Multi-Cloud Asset Inventory: Ingest configuration from all your clouds to a single data store with a unified structure.
• Avoid yet-another-dashboard fatigue: Reuse your existing Power BI setup to build a cloud asset inventory.

Walkthrough

Step 1: Install or Deploy CloudQuery

If it’s your first time using CloudQuery we suggest you first run it locally to get familiar with the tool, take a look at our quickstart guide and Azure source integration.

If you are already familiar with CloudQuery, take a look at how to deploy it to Azure on AKS at https://github.com/cloudquery/terraform-azure-cloudquery.

Step 2: Downloading Microsoft Power BI

Unfortunately, Power BI does not support the PostgreSQL connector from the web application.

We can still use the PostgreSQL connector by downloading the Power BI Desktop from here.

Step 3: Connecting Microsoft Power BI to PostgreSQL

Connection to Local / Remote server requires you to authorize the IP of the machine you’re running Power BI Desktop on.

See connection full walkthrough.

Click Get Data and choose PostgreSQL database (In this tutorial we will connect to publicly accessible PostgreSQL server with authorized IP) and fill-in the connection details:

Step 4: Visualize the Data

Choose the table you want to visualize, in this case we will choose the azure_resources view.

💡 To create the azure_resources view, run the following view before importing to the data studio.

Choose the table to visualize

Design your report

You can reuse Power BI to export/share those reports as well!

Summary

In this post we showed you how to build an open-source cloud asset inventory with CloudQuery as the ETL (Extract-Transform-Load) / data-ingestion layer and Microsoft Power BI as the visualization platforms. This approach eliminates the yet-another-dashboard fatigue and gives you the ability to pick the best-in-class visualization tools and/or reuse your current stack.

Want to see CloudQuery in action? Schedule a demo with our team or check out the platform documentation to learn more.

Want help getting started? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.