In a previous post (http://www.scom2k7.com/performance-reporting-with-power-view/) I presented a simple example of performance reporting using Power View in Excel 2013. The purpose of this post is to update the performance model for scalability and customization. Power View has limits on the volume of data and the presentation area which influence the design of the Power Pivot data model. My primary goal is to maximize the speed at which I can view data in a clear, meaningful way. I will provide an outline of the data model, the SQL queries to populate it, and the DAX statements to manipulate the data. The procedure for adding the data source and datasets has not changed from the previous post, and I will skip the specific steps here.
I use a data model similar to the performance dataset in the Operations Manager Data Warehouse schema (http://technet.microsoft.com/en-us/library/gg508713.aspx). However, I go from the instances (vPerformanceRuleInstance) to the objects and counters (vPerformanceRule) before going to the data collection rules (vRule). The object\counter pairing forms a unique string for combining with technical knowledge. I should point out that my use of the term "technical knowledge" refers to an arbitrary set of attributes for describing performance counters. For example, I might describe "LogicalDisk\Avg. Disk sec/Transfer" like this:
LogicalDisk\Avg. Disk sec/Transfer
This helps keep values visible in tiles and legends while communicating the meaning to an audience. The attributes become useful in filtering, too. For this post I will use an Excel workbook for storing technical knowledge, but these could be columns in a SQL table, SQL query or calculated columns in the data model.
A. Create Performance Data Model
B. Add Measures and Calculated Columns
C. Add Attributes for Object\Counter Values
D. Create Relationships
E. Make a View for All Servers
F. Make a View for Individual Servers
Step by step
FROM Perf.vPerfHourly PERF
INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId
INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId
WHERE PERF.DateTime > (GETUTCDATE() - 7)
ORDER BY PERF.DateTime
/*Object and Counter*/
from vPerformanceRule PR
from vPerformanceRuleInstance PRI
from vRule RU
inner join vPerformanceRule PR on PR.RuleRowId = RU.RuleRowId
SELECT distinct MP.ManagementPackRowId
FROM vManagementPack MP
inner join vRule RU on RU.ManagementPackRowId = MP.ManagementPackRowId
,MET.ManagedEntityTypeDefaultName as METype
,TLMET.ManagedEntityTypeDefaultName as TLMEType
from Perf.vPerfDaily PERF
inner join vManagedEntity ME on ME.ManagedEntityRowId = PERF.ManagedEntityRowId
inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
inner join vManagedEntity TLME on TLME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId
inner join vManagedEntityType TLMET on TLMET.ManagedEntityTypeRowId = TLME.ManagedEntityTypeRowId
where PERF.DateTime > (GETUTCDATE() - 7)
order by ME.ManagedEntityTypeRowId
Note: The vPerformanceRule and vPerformanceRuleInstance views could be added as tables, but I prefer to use queries in case I want to add conditions later. For example, if I want to exclude LogicalDisk counters or dynamic instances.
Measure Name and DAX
Measure Name and DAX
LogicalDisk\Current Disk Queue Length
Network Adapter\Bytes Total/sec
Processor Information\% Processor Time
System\Processor Queue Length
System\System Up Time
D. Create Relationships
Object and Counter: RuleRowId
Object and Counter: Object+Counter
Tech Knowledge: ObjectCounter
I added some steps to describe adding measures. I changed "metric" to "measure," too.
Great post many thanks
Thanks. Very nice work.
This is causing a problem in DAX. (B5)
It's complaining about the "\"
How do I resolve this issue?
Nevermind, replaced the "," with ";" and it worked.
When creating the following link I receive the following error. Please help.
Object and Counter: Object+Counter - Tech Knowledge: ObjectCounter
The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.
Thank you in advance.
Dean, the lookup is a many to one relationship. The Tech Knowledge: ObjectCounter list should have unique values. You can use Data --> Remove Duplicates in Excel to fix the error.