Los scripts del 8.1 al 8.3 consolidan los resultados del PerfMon en un esquema de estrella. Luego, se puede construir un cubo OLAP sobre esto.
Para limitar las líneas de código, aquí sólo están las dimensiones de objeto y máquina. Una configuración similar hará falta para Fecha, Instancia y dimensiones del contador.
Script 8.1 crea las tabals
--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 refresca todas las dimensiones
--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
Para recuperar espacio en disco, puedes truncar la tabla CounterData después de haber hecho la carga.
