I've been working with our Spiceworks ticket database to creat a dashboard for our Help Desk metrics.
After the initial problems connecting, I was able to solve the problem, and give a few pointers on how to connect properly.
Connection:
1. Install the SQLite3 ODBC connector from: http://www.ch-werner.de/sqliteodbc/ make sure you get the same version as your DB (32 or 64 bit). And create a ODBC datasource under SystemDSN. Set Lockout to 10000 (default), check "No TXN" (to speed up read), and check "Don't create Database" (you dont want it to create a local copy when accessed).
1a. Your authentication account must be able to -write- to the folder where your DB is stored to create the temp files. I suggest just making a copy of your spiceworks DB and using it exclusively for reporting (maybe a copy job every night, or every few hours.)
2. In power BI, your source will be a standard ODBC datasource, with the connection :
[code]
Odbc.Query("dsn=<your systemDSN name>", "SELECT id, SUBSTR(description,1,255) AS description, status, priority, created_at, updated_at, closed_at, assigned_to, viewed_at, created_by, reopened, category, status_updated_at, master_ticket_id, due_at, c_subcategory FROM tickets WHERE SUBSTR(created_at,1,4) >= '2013'")
[/code]
You can pick what columns you want using the SQLiteBrowser program : http://sqlitebrowser.org/
3. You'll notice that for the description, I had to SUBSTR the descriptions field. This is because the newest version of PowerBI changed (somehow) the formatting that brings in data, and doesn't allow the "description" or "Summary" fields from the "Tickets" table. Basically anything that has a text length over 255 characters will error out with [Data truncated to the right] or a similar message. Also, it wouldn't let me CAST to any SQL datatype that would allow over 255chars - hence the SUBSTR
What I think is a bug: This worked before the (I think January) update to PowerBI, without using the substring. Somewhere in and update to the ODBC data connection logic, it is forcing a field length that doesn't fit the source data type.
Hopefully this helps someone else! I know a lot of us use Spiceworks.
