Powerbi creating KPI reports from profiling data from the Scom database

Tag: Cloud Platform for Cloud computing

I have a Microsoft private cloud environment, Windows Server R2 built the underlying virtualization platform, management is using the system Center R2, because Powerbi can do more advanced bi presentation, so here I will take advantage of this private cloud environment, Use Powerbi to access Scom's SQL database to create a report representation of a KPI:

To achieve this KPI report, the presentation is divided into 4 steps:

• Collect data from SQL that needs to be queried

• Use Powerbi desktop to complete extraction of required data (data cleansing)

• Use Powerbi desktop to create reports

• Use reports created by Powerbi desktop to interact with me

Next, the first step is to PowerBI desktop to connect to Scom's SQL database to collect the required data

Open Powerbi Desktop for SQL data

Configure parameters for a connected SQL database

Select Vme2. Managedentityrowid,vme2. DisplayName as ComputerName, (case is pr.objectname = ' LogicalDisk ' then Vme2.displayname + ': ' + pri.instancename Else v Me2. DisplayName end) as Chartname,vme. DisplayName as CLASSNAME,VR. Ruledefaultname, PR. Objectname,pr. Countername,pri. InstanceName, VPD. DATETIME,VPD. SAMPLECOUNT,VPD. AVERAGEVALUE,VPD. MINVALUE,VPD. MAXVALUE,VPD. Standarddeviation from Vperformancerule PR

Join Vperformanceruleinstance pri on PRI.RULEROWID=PR. Rulerowid

Join Vrule VR on VR. Rulerowid=pr. Rulerowid

Join Perf.vperfhourly VPD on VPD. Performanceruleinstancerowid=pri. Performanceruleinstancerowid

Join Vmanagedentity VME on VME. MANAGEDENTITYROWID=VPD. Managedentityrowid

Join Vmanagedentity vme2 on vme2. Managedentityrowid=vme. Toplevelhostmanagedentityrowid

where VPD. DateTime > DATEADD (dd,-7,getutcdate ())

and (pr. ObjectName in (' Processor ', ' Processor information ', ' Memory ', ' LogicalDisk ') or PR. ObjectName like '%sql% ')

ORDER BY vpd.datetime ASC

You can see the preview status of the extracted data, click Load

After loading, we can also edit the query, define a friendly name for each dataset and how to connect the source data, and then close

Next create the report presentation

Click a Visual bar chart

Drag and drop the corresponding item name in the bar attribute to show the average performance of all systems in my private cloud environment

Next I'll set up 2 filters to show the data I want to see:

• Visual Level Filter: Applies only to highlighting the current visual view, in the current case only for bar chart

• Page-level filters: will filter the entire report data, if there are 4 bar charts, the filter will affect each bar chart linkage

Next I'll add a few more filters, such as:

After adding the filter, select 2 items to filter the view, one is the average disk transfer time, one is the logical disk, the relationship is the logical disk and the average disk transfer time is the filter condition

Next, you'll name the visual view: Logical Disk Average transfer time

According to the above method I add an additional three bar chart, a quick way to choose the current bar to copy and paste, and then refer to the above method to configure each visual view of the filter

For example: CPU

For example: Memory

For example, the number of logical disks waiting on the connection queue average

Next, if you want to sort the data, click one of the visual views and click "..." to select the collation

If you want to see the updated data, then only need to click Refresh, Powerbi will automatically connect to the scom SQL to get the latest data to show, very convenient

If you want to view all CPU, memory, and Logical disk metrics on a time-based basis, then you can click DateTime to select a specific time, and all visual views will be linked together to change , very beautiful

You can also select an advanced filter to specify a specific time range

Another way to do this is to add a visual view: the time slicer. So that I can pull the time frame to see the data I want, and adjust the timeframe, then all the other visualizations will be dynamically changed .

Similarly add a computer name slicer, so that you can check the number of systems to see the performance indicators, tick (single) to get other visual views of the linkage dynamic Change report data presentation

If you want multiple selections, you can check them directly in the filter.

Of course, each visual view is able to define many of its own configuration and parameters, such as color, font, etc.

Powerbi creating KPI reports from profiling data from the Scom database