With Microsoft’s preview release of Power Bi, I wanted to share just how awesome this product is and some potential uses from a SCSM perspective.
So what is Power Bi?
Basically, Power Bi preview is Microsoft’s business intelligence service/tool. It enables the creation of powerful dashboard reports all in one place, accessible anywhere from your Windows and IOS devices. The preview currently doesn’t support refreshing on premise datasets if using the online view. The local designer has the ability to refresh the data on loading or refreshing the document. This is what we’ll be looking at here.
So why is it so good for Service Manager?
Power Bi is smart. I mean, really smart! Imagine being able to produce easy dashboards for the number of incidents based on affected user postal code or city location via a Bing map. Maybe you want monitor the sources where your service requests are coming from. Well, you can…
So let’s have a play…
Before we start, you’ll need to download the Power Bi designer from the here.
Connecting to the SCSM Analysis Cubes
1. Open the Power Bi designer and select the Get data button.
2. Under the file section, select analysis services source.
3. Type the name of the analysis service where your data warehouse cubes reside and click ok.
4. Expand the SMDWASDataBase (Or what name you have called it if different). The SMDWASDataBase is where all the SCSM cubes reside.
5. For this example we’ll use the Service Request cube. Select and expand the SystemCenterServiceCatalogCube.
Querying Cube Data
To begin we’ll add a simple query for all Service Requests by source.
1. You need to expand the ServiceRequestDim folder and select the ServiceRequestDimCount. You can pick one of the other counts inside the ServiceRequestDim but for this example we’ll get them all.
2. Find the ServiceRequestDim_ServiceRequestSource section and select the ServiceRequestSourceValue.
The preview page should load and you should have table with the sources of your service requests.
3. Select the edit button to confirm your query worked then select the report button.
4. It will then load the cube model ready for creating your report.
5. Select the two fields on the right hand side and a report will come up like the below on our dashboard page.
You’ll see the gauge will display all the service requests created, you can then select a source from the slicer to dynamically filter the gauge to the selected field!
Note: To clear the filter from the source just click the “Clear Filter” button.
So that’s a pretty simple gauge right!? lets expand our query with more detail…
7. go back to the query section and select the “Add Items” button.
8. Find the AffectedUserDim and add the City, Country and Zip code fields.
9. Go back to the “Report” section and wait for the query to re-evalute itself. You’ll notice that Power Bi knows that the data type for City and Country relate to maps and even added an icon next to the field. (Note: You’ll need to have Active Directory populated and connector syncing into your scsm environment).
10. Select the ServiceRequestDimCount and AffectedUserDim.City fields to add another report to our dashboard. This time instead of a table, you’ll automatically get a heat map of where your service requests have been raised using the affected user City location!
Now remember we added a splicer earlier? Well if you select a source from the splicer, you’ll notice the map will filter the data too!
11. Optional – repeat step 10 but this time with the AffectedUserDim.Country.
12. So what about the AffectedUserDim.Zip? You might notice that it doesn’t have the globe the same as City and Country. In this case, we need to rename the field to “Postal Code” for Power Bi to recognise the field as relating to a map.
13. Create a new page from the left hand pain.
14. Add a new report with the ServiceRequestDimCount and Postal Code. You should then see a heat map of all the service requests by post code! You can zoom right in and see more precise details too.
Good post. I am taking on a similar project; and leveraging the cireson hardware assets and other related data in the cmdb. Currently I am using the DataMart db to query and join that data (EntityToEntity); have you found a more out of the box way in order to overlay the cireson data using the cubes that Microsoft provides without creating and extending the cube designs?
Hi AJ, thanks for the comment. Are you not using the cubes Cireson provide? I had Part 3 down to go over the cireson hardware asset cube. Can you eleborate a bit on what your doing? I have so far found Ciresons cube to link pretty well but all depends what your trying to get.