{"id":24,"date":"2017-09-08T00:54:56","date_gmt":"2017-09-08T00:54:56","guid":{"rendered":"http:\/\/www.spktechfit.com\/?p=24"},"modified":"2020-05-25T04:13:19","modified_gmt":"2020-05-25T04:13:19","slug":"spiceworks-reports-with-power-bi","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=24","title":{"rendered":"Spiceworks Reports with Power BI(with Video tutorial)"},"content":{"rendered":"<p>Power BI is a fantastic, and relatively easy, way of getting some great reports and analytics out of your Spiceworks database.<\/p>\n<p><strong>UPDATE<\/strong>: I have added this video tutorial to hopefully make this clearer and easier to follow.<\/p>\n<p><!--more--><\/p>\n<p><iframe loading=\"lazy\" title=\"Setting Up Spiceworks Reports with Power BI\" width=\"708\" height=\"398\" src=\"https:\/\/www.youtube.com\/embed\/LmX8RzCaeGY?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<h2><b>Creating your queries<\/b><\/h2>\n<p>First you will need to know how to extract the data you are looking for. The\u00a0easiest\u00a0way to do this is to use something like\u00a0<a href=\"http:\/\/sqlitebrowser.org\/\">http:\/\/sqlitebrowser.org\/<\/a>\u00a0to browse the database. This will show the database schema and allow you to build and test your queries.\u00a0<b>NOTE: remember to run this on a copy of your database.<\/b><\/p>\n<p>&nbsp;<\/p>\n<h2><b>Create the ODBC Connection to the Spiceworks databas<\/b><b>e<\/b><\/h2>\n<p>You need to create an ODBC connector for your\u00a0spiceworks\u00a0database to input the data to Power BI.<\/p>\n<p><b>Note:\u00a0<\/b>Its best to use a copy of your database instead of trying to connect to your production database<\/p>\n<ol>\n<li data-aria-posinset=\"1\" data-aria-level=\"2\">Go to\u00a0<a href=\"http:\/\/www.ch-werner.de\/sqliteodbc\/\">http:\/\/www.ch-werner.de\/sqliteodbc\/<\/a>\u00a0and download the correct version for system<\/li>\n<li data-aria-posinset=\"2\" data-aria-level=\"2\">Install using default options<\/li>\n<li data-aria-posinset=\"3\" data-aria-level=\"2\">Go to &#8216;Administrative Tools -&gt; ODBC Data Sources&#8217;<\/li>\n<li data-aria-posinset=\"4\" data-aria-level=\"2\">Go to the &#8216;System DSN&#8217; tab<\/li>\n<li data-aria-posinset=\"5\" data-aria-level=\"2\">Click on &#8216;Add&#8217;<\/li>\n<li data-aria-posinset=\"6\" data-aria-level=\"2\">Click on &#8216;SQLite 3 ODBC Driver&#8217; and click Finish<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29\" src=\"http:\/\/www.spktechfit.com\/wp-content\/uploads\/2017\/09\/GetImage.png\" alt=\"\" width=\"460\" height=\"354\" \/><\/li>\n<li data-aria-posinset=\"8\" data-aria-level=\"2\">Give the Data Source a name and browse to the location of the database<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-30\" src=\"http:\/\/www.spktechfit.com\/wp-content\/uploads\/2017\/09\/GetImage-1.png\" alt=\"\" width=\"418\" height=\"341\" \/><\/li>\n<li data-aria-posinset=\"8\" data-aria-level=\"2\">Click OK<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<h2>Connecting to the Data Source in Power BI<\/h2>\n<ol>\n<li data-aria-posinset=\"1\" data-aria-level=\"2\">In Power BI click on &#8216;Get Data&#8217;<\/li>\n<li data-aria-posinset=\"2\" data-aria-level=\"2\">Select ODBC as the connection type<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31\" src=\"http:\/\/www.spktechfit.com\/wp-content\/uploads\/2017\/09\/GetImage-2.png\" alt=\"\" width=\"620\" height=\"178\" \/><\/li>\n<\/ol>\n<p>3. Select the Data Source name from the dropdown list<br \/>\n4. 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 &#8216;Advanced Options&#8217;<br \/>\n6. Paste in your statement (<em><strong>See sample below<\/strong><\/em>) and click OK.<\/p>\n<p><strong><em>Your data is now loaded into Power BI and you can have loads of fun creating reports and visualizations<\/em><\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p><strong>Sample query<\/strong><\/p>\n<p>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<br \/>\n<strong>Note: make sure to change it to your server name\u00a0in the ticket\u00a0link:<\/strong><\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p>select<\/p>\n<p>tickets.id as &#8220;Ticket #&#8221;<\/p>\n<p>, (select first_name as &#8220;Created By&#8221; from users where tickets.created_by=users.id) AS &#8220;Created By&#8221;<\/p>\n<p>, SUBSTR(summary,1,5) AS Summary<\/p>\n<p>, category<\/p>\n<p>, status<\/p>\n<p>, priority<\/p>\n<p>,\u00a0 (select first_name from users where tickets.assigned_to=users.id) AS &#8220;Assigned to&#8221;<\/p>\n<p>,tickets.created_at<\/p>\n<p>,tickets.closed_at<\/p>\n<p>, round(julianday(tickets.closed_at) &#8211; julianday(tickets.created_at),2) AS &#8220;Days Open&#8221;<\/p>\n<p>from tickets<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power BI is a fantastic, and relatively easy, way of getting some great reports and analytics out of your Spiceworks database. UPDATE: I have added this video tutorial to hopefully make this clearer and easier to follow.<\/p>\n","protected":false},"author":1,"featured_media":39,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[7,9,3],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/24"}],"collection":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=24"}],"version-history":[{"count":13,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/24\/revisions"}],"predecessor-version":[{"id":722,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/posts\/24\/revisions\/722"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=\/wp\/v2\/media\/39"}],"wp:attachment":[{"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spktechfit.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}