Accessing ignition database using Power BI

I am trying to connect my MS Power BI application to the Ignition Sample_SQLite_Database.
I have searched the KB and reviewed this topic:

[MS Power BI with Ignition Historina data]

but It seems rather sparse and hi-level for me.

I am new to ignition (we haven’t even purchased the licenses yet), but I am looking for some way to
promote its purchase in our company by leveraging and improving on what is already in place here.
Part of that is a home-grown tracking /quality system that uses Power BI as its reporting tool.

Is there a a detailed tutorial or some such thing available specifically for accessing Ignition from Power BI,
or even a more general explanation about connecting Ignition to any other 3rd party software similar to PowerBI ?

Thanks in advance for any help you can offer.

I’ve never used Power BI myself, so I don’t even fully understand what it is. However, I did search up whether Power BI has an API and it looks like it does. This would be one way to easily pull data from an Ignition database and use Power BI to analyze and display it.

@stever Can you include the link to the Ignition Sample_SQLite_Database? I want to try it out for myself but I can’t seem to find it on Google…

I was able to download Power BI and play around with it. In my experience, I didn’t connect directly to Ignition per se. I don’t think you want to do that (unless you want to get metadata about Ignition). Usually you add a database to the Ignition gateway and send / receive data that way. In other words, both Power BI and Ignition will be connected to the same database.

What I did (this may or may not be helpful to you):

  1. Open Power BI. Click on Home → Get data.

  2. Click on the source you need. For example, I have a MySQL database connected to Ignition, so I’ll choose MySQL database from the data sources.

  3. Enter the IP address and port of your database server, along with the database name. In my case it was 10.1.45.89:3306 and the schema name was production. Click the OK button.

  4. You’ll get a window where you will authenticate to your database. Choose Database from the tree on the left (defaults to Windows). This way you’ll enter your database credentials.

  5. If you authenticated successfully, you’ll get a table preview of your database.

At this point, you connected your database to Power BI. This is where you can create reports based on what is in your database (based on the data Ignition can read / write to). If you want to then embed the report in Ignition, one way would be to publish it to the cloud (Office 365). Once you can see it in the online Power BI portal, you create an embed link (after setting the appropriate permissions).

You can then drag on a Perspective Inline Frame component and set the src property to the embed link.

Let me know if I wasn’t clear on anything. Hope this helps!

to YF129701:
Thanks for responding.

Let me explain what I am trying to do. ( I should have done this before).
I want Power BI to display a trend of the Plant Air Pressure over a period of time.
I could have (and did) do this using Ignition Vision client, but the company currently relies on Power BI for there business visualizations, and since I am still waiting for them to approve funding to purchase Ignition, I wanted to show them how I could easily interface the new SCADA system into the applications they are currently using. I am using the Sample_SQLite_Database because it came with Ignition and so I have my historical pressure data logging there.

I can’t send you a link to the Sample_SQLite_Database, because one of my issues is that I don’t know where is located. I found some detailed information on the tables in it from the KB topic that I mentioned in my initial posting.,( [MS Power BI with Ignition Historina data]), but not where it is located.
Installing MySQL and configuring it to work with Ignition is probably the right way to go, especially for the long run. But I was hoping not to have to put that much effort into it. I had hoped that it would be simple, since Inductive Automation and Microsoft are collaborating, right?

I found a couple of YouTube videos that explain how to connect Power BI to a generic SQLite Database, but they both do it by installing an ODBC driver for SQLite and using that. I would rather not use the ODBC drivers, but I tried it anyway (twice). Both times it said that the driver was successfully installed, but when I went to connect to it according the the video, it doesn’t show an SQLite ODBC driver available.

Once I get connected to the database I would still like to find some detailed info on getting the data I need out.

Why is it that there is not a but-load of videos about the nuts and bolts of doing things like this with Ignition? Or am I just not finding them???

That might be a bit too strong a statement. Ignition is a cross-platform product that, at its core, doesn’t depend on Microsoft products at all. Which makes Microsoft sad. (And many of us very happy.)

Microsoft does want to interoperate with Ignition, because Ignition is a growing power in the SCADA world, and Ignition does want to interoperate with Microsoft, because, well, they’re the 800lb gorilla of enterprise computing.

As for videos, have you browsed Inductive University?

This is simple. Read up or watch video on Database connections.

Open you gateway, go to the config section, then Database connections. You’ll see the connection there and can grab the path in the config on your local system.

Is the data you want in Power BI in tag history? That would be stored in the DB and the schema is well documented in the docs. You might need to write some queries in Power BI to pull the data unless you want to build a specific API in Ignition for Power BI to connect to.

1 Like