Part 2 – Power Bi – Service Manager Library Cube Reporting – SCCM Configuration Items Dashboard

OK – so it’s been a while since Part 1 of my post was released and if you’ve read it, you’ll probably already know how awesome Power BI is! If you haven’t had a chance or just not seen it, you can view part 1 here.

In part 2, We’ll go through creating a new dashboard but this time using configuration items connected via the System Center Configuration Manager connector! We’ll also be going through working with relationships and formatting our data to look how we want it.

Reporting on configuration items through Power BI is great as you can utilize the intelligence to produce some pretty neat dashboards.

If you want to try these yourself, you’ll need to have the System Center Configuration Manager connector setup.

So let’s have a play… 

Before we start, you’ll need to download the Power Bi designer from the here.

Power BI keeps getting better, there’s recently been an update to the designer (24/07/2015) and a load of cool features added.

Power BI Desktop GA Update is available now!

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.

Note: Version: 2.25.4095.554 of Power BI contains additional optional to explore or select. depending on your setup you may need to use the select option like I have.

4. Enter your credentials or use existing if you already have view access to the database. Click connect to continue.

5. Expand the SMDWASDataBase (Or what name you have called it if different). The SMDWASDataBase is where all the SCSM cubes reside.
6. Select and expand the SystemCenterConfigItemCube.

So we have our cube, now time to configure the query.

Querying Config Item Cube
We’ll add a query to show all our deployed computers by manufacturers.
1. You need to expand the DeployedComputerDim folder and select the DeployedComputerDimCount.
2. Find the Deployed Computer Dim section, select the below and load the data.
3. When selected, you should have noticed that the preview of the data was provided for you. Verify the data has been loaded successfully by clicking the data icon. 

Creating the Dashboard – Pie Chart and Table

So lets add something to the dashboard…
1. Select the Deployed Computer Dim.Manufacturer and DeployedComputerDimCount on the right hand side and a report will come up like the below on our dashboard pane.
OK… So I know what your thinking, it looks pretty messy right? Well lets make tidy it up a bit.
2. Right click and rename the columns to the below.
Deployed Computer Dim.Manufacturer | Manufacturer
DeployedComputerDimCount | Computers

3. Looking better but lets change the visualization to something more fitting. Select the pie chart (optionally tweak the colours if you want).

Looking better :). So lets add a data table so we can filter the data!

4. Click an empty section of the dashboard pane. Select the table icon from the visualization pane and then select all the columns from the fields pane. You’ll need to rename some too like before.

Deployed Computer Dim.Chassis Type | Type
Deployed Computer Dim.Display Name (DeployedComputerDim) | Computer Name
Deployed Computer Dim.Model | Model
Deployed Computer Dim.Number Of Processors | # Of Processors
Deployed Computer Dim.System Type | System Type

As you can see, we have a nice data table showing our config information.

So lets filter the data…. Click on one of your manufacturers and you’ll see your table dynamically filter! 

You can see by the screenshot that the table has changed and even shows the amount of that particular device. 

5. Optionally Tidy it up a bit by removing the computers column and re-sizing the table.

Creating the Dashboard – Lets Slice, Dice and Edit our Query 🙂

So we have our pie chart and table, lets add the chassis type information to our dashboard.
1. Add a Slicer to your dashboard from the visualization pane then select the Computers and Type fields

You’ll notice all we have is a load of numbers which doesn’t really show much. Luckily Microsoft provide us with a list of what they actually are here. All we need to do is edit our data… There are a couple of ways to do this, we can rename the value in the query or create a relationship.
I’ll go through one replace value but for this example we’ll be using the relationship method.

2 – Edit Query Data via Replace Value:

2a. From the left hand pane, select the relationships icon and then select the edit queries from the top pane.

2b. Once the query editor has opened, highlight the Type column, right click and select the Replace Values… button.

2c. Replace the numbers with the matching description value from the Microsoft table.

Once completed, you’ll notice the Type column will show the descriptions rather than the number.

Power BI provides a Steps Applied section in Query Settings which allows you to see exactly whats been amended to your query. 

Lets remove this rename step ready to setup the relationship method. 

2d. From the steps applied pane, remove the Replaced Value step by clicking the cross.

3 – Edit Query Data via Relationship (the awesome way!):

For this example, we’re going to use utilise the Web data source option to get the table for the TechNet site.

3a. In the Query Editor, go to New Source and select the Web data source.

3b. Enter the URL in and click ok.

3c. The query will pull the data tables back from the site. Select Table 0 and you’ll see we have our numbers and descriptions!!

3d. Now go back to the designer and reload the data. You will see in the relationship section, we now have our new table.

3d. Click the Manage Relationships button from the home menu and create a new relationship. You’ll need to drop the dropdown box to select the tables, then click both the Value and Type columns to link them.

Once the relationship is setup, you’ll notice it’s indicated in the relationship section too.

So we have our data relationship setup, pretty neat right? Lets go back to the dashboard and add the table back in.

4. Go back to the Report section and delete the slicer we previously created.

5. Add a new Slicer but this time select the description column from table 0. Optionally rename the Column Description to Chassis Type.

6. Filter the Chassis Type and you’ll notice our data is filtered according!

Creating the Dashboard – Donut Chart Time!

To finish our dashboard off, lets add a new donut chart so show our Architecture.
1. Select the Donut Chart icon from the visualizations pane and then pick the Architecture and  Computers field. 
Optionally un-tick the  # Of Processors and Type from the earlier data table to get more space.

You’ll see that because your your querying from the same data, it dynamically updates like the rest of the dashboard!
This is just one example of what you can do with this cube and Power BI.  You could use the same cube to show your Operating systems…

The options are all down to what you need. Hopefully this has given you some insight and maybe a couple of Ideas on who you can use this product in your environment :).

Leave a Reply

Your email address will not be published. Required fields are marked *