Step 5: Set Up Visualization in Excel
All your data is now stored in SQL server tables and the interfaces have been created using views. Now you can visualize them as graphs in a tool like Excel. If you feel more comfortable with Reporting Services, please do so. Using Reporting services is a more professional way of working, but for simplicity, I use Excel here. Set up will not fundamentally differ from the steps described here.
As this is no training in Excel, I’ll just cover this topic briefly.
All steps need to be performed on the client machine where you want to view your dashboard.
Step 5.1: Create a System DSN to make a connection to your monitoring database.
Step 5.2: Click Data — Import External Data — New Database Query .
Step 5.3: Select the data source you created in step 1.
Step 5.4: Go through all steps and join different views using the CounterDateTime field.
Step 5.5: Load the results in a new worksheet.
Step 5.6: Click Data — Import External Data – Data Range Properties: select Refresh every 1 minute.
Step 5.7: Start the Chart wizard.
Step 6: Alerts
Our motto “keep things simple” also counts for our design. I use the alerts mechanism in PerfMon to generate alerts and to log events in the application event log where a centralized tool can capture and transfer it following company standards. (Control room, beepers, ….)
Step 7: Digital Dashboard
If you want more flexibility how to handle alerts, you can also store threshold values in a database table. This way, a batch process and/or a graphical user interface can compare stored counter values with threshold values and generate all kinds of alert mechanisms. This is the domain of .NET developers, so I’ll take one step back. I’ll probably end up in jail if I publish the C# code I wrote for a similar project. So I won’t. But believe me, it’s not that difficult.
Step 8: Data Consolidation
Scripts 8.1-8.3 will consolidate your PerfMon results in a star schema. Later an OLAP cube can be build on this. BI is a topic for a later SQL Server 2005 article.
You database design will look like this. To limit my lines of code, I’ll only cover Machine and object dimensions here. A similar setup needs to be performed for Date, Instance, and Counter dimensions.
Script 8.1 creates all required tables.
–Script 8.1
CREATE TABLE dbo.Fact_Perfmon (
PerfMonID int IDENTITY (1, 1) NOT NULL ,
DateID int NULL ,
MachineID int NULL ,
ObjectID int NULL ,
InstanceID int NULL ,
CounterID int NULL ,
CounterValue float NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Machine (
MachineID int IDENTITY (1, 1) NOT NULL ,
MachineName varchar (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.Dim_Object (
ObjectID int IDENTITY (1, 1) NOT NULL ,
ObjectName varchar (100) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT PK_Fact_Perfmon PRIMARY KEY CLUSTERED
(
PerfMonID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Machine ADD
CONSTRAINT PK_Dim_Machine PRIMARY KEY CLUSTERED
(
MachineID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Dim_Object ADD
CONSTRAINT PK_Dim_Object PRIMARY KEY CLUSTERED
(
ObjectID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Fact_Perfmon ADD
CONSTRAINT FK_Fact_Perfmon_Dim_Machine FOREIGN KEY
(
MachineID
) REFERENCES dbo.Dim_Machine (
MachineID
),
CONSTRAINT FK_Fact_Perfmon_Dim_Object FOREIGN KEY
(
ObjectID
) REFERENCES dbo.Dim_Object (
ObjectID
)
GO
Script 8.2 refreshes all dimensions.
–Script 8.2
INSERT Dim_Machine (MachineName) SELECT DISTINCT MachineName FROM MonitoringDB.DBO.CounterDetails WHERE MachineName not in (SELECT DISTINCT MachineName from Dim_Machine)
INSERT Dim_Object (ObjectName) SELECT DISTINCT ObjectName FROM MonitoringDB.DBO.CounterDetails WHERE ObjectName not in (SELECT DISTINCT ObjectName from Dim_Object)
Script 8.3 refreshes your fact table
–Script 8.3
SELECT dd.DateID
, dm.MachineID
, do.ObjectID
, dc.CounterID
, di.InstanceID
, dat.CounterValue
FROM MonitoringDB.DBO.CounterData Dat
INNER JOIN MonitoringDB.DBO.CounterDetails Det ON Dat.CounterID = Det.CounterID
INNER JOIN Dim_Date dd ON DateValue = Dat.CounterDateTime
INNER JOIN Dim_Machine dm ON dm.MachineName = Det.MachineName
INNER JOIN Dim_Object do ON do.ObjectName=Det.ObjectName
INNER JOIN Dim_Counter dc ON dc.CounterName= Det.CounterName
INNER JOIN Dim_Instance di ON di.InstanceName = Det.InstanceName
To regain disk space you can truncate table CounterData after you data are successfully loaded.
]]>