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):
Open Power BI. Click on Home → Get data.
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.
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.
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.
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!
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.
That looks super easy.
That looked like a live connection though.
Is it just as easy to do an import, so I don't add work to my Ignition DB?
Is there a way to do the same thing with SAP, so you can have both connected to Power BI in under an hour?
I read there was a way to import a db to Power BI from SAP, but after hours of reading pages and pages of SAP stuff, I still don't know how to do it.
It seems like it must be really easy, but also that it is extremely obscured for some reason.