Database Reference
In-Depth Information
How to do it...
In this example, we will use Microsoft Excel to analyze performance data using ad hoc
PivotTables and PivotCharts. We will use a view in the
OperationsManagerDW
data-
base to provide an abstraction layer from the database model. The view will hold friendly
names for the columns and will be limited to the data we need for our report.
To create the view, open SQL Server Management Studio and connect to the SQL server
that hosts the
OperationsManagerDW
database. Then, execute the following query:
USE OperationsManagerDW
GO
CREATE VIEW v_Custom_r_PerformanceDataDaily AS
SELECT
P.DateTime AS [Date],
YEAR(P.DateTime) AS [Year],
MONTH(P.DateTime) AS [Month],
DAY(P.DateTime) AS [Day],
MET.ManagedEntityTypeDefaultName AS [Managed Entity Type],
ME.Name AS [Managed Entity Name],
ME.FullName AS [Managed Entity Full Name],
R.RuleDefaultName AS [Rule Name],
PR.ObjectName AS [Performance Object],
PR.CounterName AS [Performane Counter],
P.SampleCount AS [Sample Count],
P.MinValue AS [MIN],
P.MaxValue AS [MAX],
P.AverageValue AS [AVG],
P.StandardDeviation AS [STDEV]
FROM
Perf.vPerfDaily P
INNER JOIN vManagedEntity ME ON
P.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN vManagedEntityType MET ON
ME.ManagedEntityTypeRowId = MET.ManagedEntityTypeRowId
INNER JOIN vPerformanceRuleInstance PRI ON
