How can we connect PowerBI to our Orion DB for building dashboards in PowerBI?

From my understanding PowerBI has a connection option to use database as source and most users use that. Alternatively, some users have said you can export orion reports via csv onto a network share and then have PowerBI ingest csv data. But the db connection was the preferred route. The only information in separate databases is Log data and [NTA] Flow data. 

For historical tables, I would typically go for the views as opposed to tables. There is usually a view that will compile all the aggregated data (_detail, _hourly, _daily). For your example: [APM_ComponentStatus] (view). I like to use Solarwinds Platform Database Manager sometimes because it doesn't separate the views and tables like SSMS does. It makes it easier to identify if a view exists.

From my understanding, PowerBI allows for HTTPS/API integration. However, it requires a signed SSL certificate.

Starting in version 2023.1 a feature was added to update the SWIS/API certificate with your own signed certificate. Details on updating that certificate are found here.

I have not tried this personally, but if you do update the SWIS cert with a signed cert, I'd be curious to know if it allows you to connect to Power BI. This would be the ideal route over connecting to the database directly.

Is there a place to see how the views aggregate the data so we could connect to the DB with PowerBI, choose those tables as data sources, and then make matching views or tables in PowerBI to then show in PowerBI visuals?

We're trying to get away from having to do extra steps like exports/imports, manual steps to get data or show the data, etc.

We have the cert set properly, but currently not sure how to use the API to query the data that we would want. This sounds promising though to read how to do this.

From SSMS you can see the query that forms any view a few ways, but i like this way

Right-click the view. Select "Script View as" > "CREATE to" > "New Query Editor Window"

stackoverflow.com/.../sql-views-how-to-determine-underlying-tables

Install SWQL studio on your workstation and use it to connect to your solarwinds server.  SWQL studio will provide a GUI for you to learn how the tables are structured and helps in writing/testing queries to get whatever it is that you need.  The SW API is unusual in that it is really just a thin wrapper over the raw database tables so one way or another you end up writing queries in plain SQL or the slightly different SWQL.

github.com/.../

We've used SWQL studio a little bit, it is the API to use with PowerBI that would be the learning aspect of it. And the SWQL that we've looked at so far to wade into learning has been from enabling Hubble so that we can see the SWQL used in the reports when viewing in Orion on the web.

Another thing to look at is creating dataflows to PBI from SolarWinds using SWQL report queries. The SWQL based api calls would be used to generate the data for those dataflows. 

Besides using Hubble to learn the query behind the scenes, you can also use alerting to develop custom queries. If you want guidance on creating custom queries you can simply build out an alert condition matching the results you want to see queried, then "show swql" to see the text. This is a little easier than enabling Hubble (which can have significant performance impact and not exactly quick to enable/disable)

Like this:

Connecting to the database directly, as we're on-prem, and then mimicking the views is the route that we are going. We do still create some custom tables in PowerBI directly based on the original DB tables we connect to so we can do more filtering for things, and the filtering in PowerBI reports only goes so far for our particular needs.