I'm making a few assumptions here:
You are working with ORG1 (the appliance can have multiple organizations)
There isn't a firewall blocking your machine from accessing port 3306 on your appliance
You have Dell computers in your KACE inventory
First things first - Setup the applianceIn order to connect to the database you need to enable the reporting user. In the K1 admin interface browse to Settings, Security Settings. Check the box for Enable database access. Save your changes.
Note that if you have more than one organization you will need to enable to user for each one.
Next - Setup the ODBC connectionAlthough the KACE database is running MariaDB the MySQL connector will be close enough. You'll need to download the MySQL ODBC Connector and configure a connection to the KACE database. See the KACE administrator guide for more details.
Fire up Power BIYou will need the desktop version of Power BI from Microsoft. Download and install the client and launch it. It should open to a blank workbook. Click the Get Data button and then select Database and MySQL database. If your ODBC connection is setup correctly you should soon be seeing a list of the tables in the KACE SMA database.
Select some tables and make a reportIf you aren't already familiar with the database structure then the list you are viewing will be daunting. Let's start with something rather simple that won't require a lot of database knowledge. In the list of tables select the following tables:
Power BI will normally attempt to detect relationships between the tables for you. Click the Manage Relationships button to see how it did. Regardless of what Power BI made you will need the following, so add them if they aren't there (you may need to delete or de-activate any relationships that are already present).
ORG1 MACHINE CSP_ID_NUMBER to ORG1 DELL_ASSET SERVICE_TAG
ORG1 DELL_ASSET SERVICE_TAG to ORG1 DELL_WARRANTY SERVICE_TAG
Power BI now has a basic understanding of how rows in the MACHINE table relate to the other tables. Let's make a simple report that shows the ship date of your Dell computers.
On the right side of the window in the Fields pane there should be a list of tables, expand the ORG1 MACHINE table and check the box next to NAME. You should get a table visualization that list the computer names in your database. Next, expand the ORG1 DELL ASSET table and check the box next to SHIP_DATE and four additional columns should appear showing Year, Quarter, Month and Day.
That's pretty exciting! Of course, you might not like the format of the date because Power BI defaults to showing dates using a date hierarchy, this is useful for other kinds of visualizations, but not so much for tables. In the Visualizations pane there should be a Values section that shows NAME and SHIP_DATE. SHIP_DATE should be expanded to show the parts, click on the arrow next to SHIP_DATE and change the option from Date Hierarchy to SHIP_DATE. Your table should update to show the dates in the standard form.
The table is still showing all computers, so to make the output look nicer you can use the filters to only show items where the SHIP_DATE is not blank. This should be an option under the Advanced filtering options.
Let's make a pretty picture using this same data. In the visualizations pane click on the Pie chart visual. Drag the SHIP_DATE column from the Fields table to the Legend section. Next, drag the ID column from the SERVICE_TAG column to the Values section. BAM! Now we have a pretty pie chart showing the number of computers shipped per year. For something even cooler, click on one of the pie sections and watch the table you created earlier only show the computers shipped in that year. #MindBlown
This is but a sampling of what you can do with Power BI and your KACE SMA data. I encourage you to try making other types of visualizations using just these three table to start. As you add fields from the different tables you will eventually find that something breaks the visuals, this is normally caused by Power BI not understanding the relationship between what is already in the table and what you just added to it. In a future post I will talk about how to get around those limitations.