Connect to Turbot Pipes from Power BI
Power BI is a business intelligence tool for data exploration and visualization that connects to many databases, including Postgres, and enables users to explore, query, and visualize data.
Steampipe provides a single interface to all your cloud, code, logs and more. Because it's built on Postgres, Steampipe provides an endpoint that any Postgres-compatible client -- including Power BI -- can connect to.
The Connect tab for your workspace provides the details you need to connect Power BI to Turbot Pipes.
Connect to Steampipe CLI from Power BI
You can also connect Power BI to
Steampipe CLI. To do that, run
steampipe service start --show-password and use the displayed connection
Steampipe service is running:Database:Host(s): localhost, 127.0.0.1, 192.168.29.204Port: 9193Database: steampipeUser: steampipePassword: 99**_****_**8cConnection string: postgres://steampipe:99**_****_**8c@localhost:9193/steampipe
Power BI is available for Windows. Here we will create a Turbot Pipes connection from Power BI.
To create a new connection, first install the
PostgreSQL ODBC driver. (Remote
databases are not accessible with the built-in data source support.) Enter the
Turbot Pipes connection details, set the SSL Mode to require and click
Verify then click
To connect the database to Power BI, click
Get data from the ribbon, select
ODBC, and connect to the data source.
Once the data source is connected, select and load the database from the navigator.
Create a report dashboard to analyze resources
We'll focus here on creating a dashboard to monitor and analyze AWS services. To
Transform data from the ribbon to open the
Power Query editor,
then add the data source either by clicking on
New Source or by selecting it
Recent Sources. Since tables from the plugins are not listed, we
will create a visual
for instance types per region that uses this query.
= Odbc.Query("dsn=<data source name>", "selectcount(instance_type),locationfromaws_ec2_instance_availabilitygroup by location")
Once Power BI previews the data, click on
Close & Apply to save. You may also
save it into a CSV file using the export option.
New Visual from the ribbon and create visuals for CPU utilization,
buckets with default encryption disabled, and top 10 cost by service with these
= Odbc.Query("dsn=<data source name>", "selectinstance_id,timestamp,minimum,maximum,average,sample_countfromaws_ec2_instance_metric_cpu_utilizationorder byinstance_id,timestamp;")
= Odbc.Query("dsn=<data source name>", "selectcount(name)fromaws_s3_bucketwhereserver_side_encryption_configuration is null;")
= Odbc.Query("dsn=<data source name>", "selectservice,sum(unblended_cost_amount)::numeric::money as sum,avg(unblended_cost_amount)::numeric::money as averagefromaws_cost_by_service_monthlygroup byserviceorder byaverage desclimit 10;")
Power BI provides a variety of visuals under the Visualizations tab. Here we use
Stacked column chart for instance types per region,
Funnel for CPU
Gauge to show buckets with default encryption disabled, and
Pie chart to show top 10 cost by service. The dashboard can also be downloaded
as a PDF file to be shared by simply using
Export to PDF from the Export
option under the file menu.
With Power BI and Turbot Pipes you can:
Write custom queries to preview data from the tables in your Turbot Pipes workspace
Create interactive dashboards driven by your custom queries
Export and share dashboards