Power BI is a fantastic, and relatively easy, way of getting some great reports and analytics out of your Spiceworks database.
Creating your queries
First you will need to know how to extract the data you are looking for. The easiest way to do this is to use something like http://sqlitebrowser.org/ to browse the database. This will show the database schema and allow you to build and test your queries. NOTE: remember to run this on a copy of your database.
Create the ODBC Connection to the Spiceworks database
You need to create an ODBC connector for your spiceworks database to input the data to Power BI.
Note: Its best to use a copy of your database instead of trying to connect to your production database
- Go to http://www.ch-werner.de/sqliteodbc/ and download the correct version for system
- Install using default options
- Go to ‘Administrative Tools -> ODBC Data Sources’
- Go to the ‘System DSN’ tab
- Click on ‘Add’
- Click on ‘SQLite 3 ODBC Driver’ and click Finish
- Give the Data Source a name and browse to the location of the database
- Click OK
Connecting to the Data Source in Power BI
- In Power BI click on ‘Get Data’
- Select ODBC as the connection type
3. Select the Data Source name from the dropdown list
4. At this stage, I find it best to enter an SQL statement, anytime I have tried to pull in the whole database I ran into a lot of errors. To enter an SQL statement click on ‘Advanced Options’
6. Paste in your statement (See sample below) and click OK.
Your data is now loaded into Power BI and you can have loads of fun creating reports and visualizations
This will get the following from open tickets: ticket number as a link, creators full name, creators AD username, Summary, category, status, priority, Assigned to, date created, days open
Note: make sure to change it to your server name in the ticket link:
‘http://servername/tickets/v2#single_ticket/’||tickets.id AS “Ticket Link”
, tickets.id as “Ticket #”
, (select (first_name || ” ” || last_name) as “Created By” from users where tickets.created_by=users.id) AS “Created By”
, (select cn as “Username” from ad_users where tickets.created_by=ad_users.user_id) AS “Username”
, (select (first_name || ” ” || last_name) as “Tech” from users where tickets.assigned_to=users.id) AS “Assigned to”
, round(julianday(‘now’) – julianday(tickets.created_at),2) AS “Days Open”
where status = ‘open’