GO
---------------------------------------------------------------
-- Initialize Process Performance Dataset
--------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@SchemaName sysname
,@ProcessStagingSp sysname
,@ErrorInd bit
,@DebugLevel tinyint
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugLevel = 0
SET @SchemaName = 'ProcessMonitoring'
SET @ProcessStagingSp = 'PerformanceProcessStaging'
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + ' Insert new definition.'
---------------------------------------------------------------------------------------
-- Create the StandardDatasetAggregation for Process Monitoring Performance Fact Tables
----------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting aggregation setting deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText varchar(max)
,@BuildAggregationStoredProcedureName sysname
,@DeleteAggregationStoredProcedureName sysname
,@GroomStoredProcedureName sysname
,@SchemaName sysname
SET @BuildAggregationStoredProcedureName = @SchemaName + '.' + 'PerformanceFactsAggregate'
SET @DeleteAggregationStoredProcedureName = @SchemaName + '.' + 'PerformanceFactsAggregateDelete'
SET @GroomStoredProcedureName = @SchemaName + '.' + 'PerformanceFactsGroom'
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
EXEC @ExecResult = sp_xml_preparedocument @XmlDocHandle OUTPUT, @XmlDocText
IF @ExecResult <> 0 RAISERROR(777971000, 16, 1, 'Process Monitoring: Health State Aggregation', @ExecResult)
IF NOT EXISTS (SELECT 1 FROM dbo.StandardDatasetAggregation WHERE DatasetId = '$Config/DatasetId$' )
BEGIN
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Subhourly' THEN 10
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
WHEN 'Subhourly' THEN AggregationIntervalDurationMinutes
WHEN 'Hourly' THEN 60
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,@BuildAggregationStoredProcedureName
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE @DeleteAggregationStoredProcedureName
END
,@GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName nvarchar(256)
,IndexFileGroupName nvarchar(256)
,AggregationIntervalDurationMinutes int
)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'Aggregation setting deployment was failed. Error: ' + @ErrorMessageText
------------------------------------------------------------------------------------------------------------
-- Create the StandardDataset Aggregation Tables definitions for Process Monitoring Performance Fact Tables
------------------------------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = 'Process Monitoring Perfromance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset table definitions deployment.'
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
,@TableCompressionClause nvarchar(max)
BEGIN TRY
SET @TableCompressionClause = ''
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
-----------------------------------------------------------------------
-- ENBALE PAGE COMPRESSION ON FACT Tables IF AVILABLE
-----------------------------------------------------------------------
IF ( 3 = SERVERPROPERTY('EngineEdition')
OR
('SP1' = SERVERPROPERTY('ProductLevel') AND CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)) like '13.0%')
)
BEGIN
SET @TableCompressionClause = ' WITH (DATA_COMPRESSION = PAGE)'
END
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Hourly' THEN 20
WHEN 'Daily' THEN 30
END
,BaseTableName
,0
,CASE AggregationType
WHEN 'Raw' THEN '
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(BaseTableName + 'RowId') + ' bigint NOT NULL IDENTITY(1, 1)
,DateKey int NOT NULL
,TimeKey int NOT NULL
,ComputerRowId int NOT NULL
,ProcessRowId int NOT NULL
,ProcessCmdRowId int NOT NULL
,UserRowId int NOT NULL
,PID int NOT NULL
,CpuUsage float NOT NULL
,MemoryUsage float NOT NULL
,HandleCount int NOT NULL
,ThreadCount int NOT NULL
,PageFaultCount int NOT NULL
,CpuTime bigint NOT NULL
,TotalProcessTime bigint NOT NULL
,IOReadPerSecond float NOT NULL
,IOWritePerSecond float NOT NULL
,[DateTime] datetime NOT NULL
,CONSTRAINT ' + QUOTENAME('PK_' + BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
' + @TableCompressionClause
ELSE
'
CREATE TABLE ' + QUOTENAME(d.SchemaName) + '.' + QUOTENAME(BaseTableName + '_$Guid$') + '
(
' + QUOTENAME(BaseTableName + 'RowId') + ' bigint NOT NULL IDENTITY(1, 1)
,DateKey int NOT NULL
,TimeKey int NOT NULL
,ComputerRowId int NOT NULL
,ProcessRowId int NOT NULL
,ProcessCmdRowId int NOT NULL
,UserRowId int NOT NULL
,PID int NOT NULL
,CpuUsageSampleCount int NOT NULL
,CpuUsageAverageValue float(53) NOT NULL
,CpuUsageMinValue float(53) NOT NULL
,CpuUsageMaxValue float(53) NOT NULL
,CpuUsageStandardDeviation float(53) NOT NULL
,MemoryUsageSampleCount int NOT NULL
,MemoryUsageAverageValue float(53) NOT NULL
,MemoryUsageMinValue float(53) NOT NULL
,MemoryUsageMaxValue float(53) NOT NULL
,MemoryUsageStandardDeviation float(53) NOT NULL
,HandleCountSampleCount int NOT NULL
,HandleCountAverageValue float(53) NOT NULL
,HandleCountMinValue int NOT NULL
,HandleCountMaxValue int NOT NULL
,HandleCountStandardDeviation float(53) NOT NULL
,ThreadCountSampleCount int NOT NULL
,ThreadCountAverageValue float(53) NOT NULL
,ThreadCountMinValue int NOT NULL
,ThreadCountMaxValue int NOT NULL
,ThreadCountStandardDeviation float(53) NOT NULL
,PageFaultCountSampleCount int NOT NULL
,PageFaultCountAverageValue float(53) NOT NULL
,PageFaultCountMinValue int NOT NULL
,PageFaultMaxValue int NOT NULL
,PageFaultCountStandardDeviation float(53) NOT NULL
,IOReadPerSecondSampleCount int NOT NULL
,IOReadPerSecondAverageValue float(53) NOT NULL
,IOReadPerSecondMinValue float(53) NOT NULL
,IOReadPerSecondMaxValue float(53) NOT NULL
,IOReadPerSecondStandardDeviation float(53) NOT NULL
,IOWritePerSecondSampleCount int NOT NULL
,IOWritePerSecondAverageValue float(53) NOT NULL
,IOWritePerSecondMinValue float(53) NOT NULL
,IOWritePerSecondMaxValue float(53) NOT NULL
,IOWritePerSecondStandardDeviation float(53) NOT NULL
,CpuTimeSampleCount int NOT NULL
,CpuTimeAverageValue float(53) NOT NULL
,CpuTimeMaxValue bigint NOT NULL
,CpuTimeMinValue bigint NOT NULL
,CpuTimeStandardDeviation float(53) NOT NULL
,TotalProcessTimeMaxValue bigint NOT NULL
,[DateTime] datetime NOT NULL
,CONSTRAINT ' + QUOTENAME('PK_' + BaseTableName + '_$Guid$') + ' PRIMARY KEY CLUSTERED ('
+ QUOTENAME(BaseTableName + 'RowId') + ') ON $DataFileGroupName$
) ON $DataFileGroupName$
' + @TableCompressionClause
END
,CASE AggregationType
WHEN 'Raw' THEN '
SELECT
DateKey
,TimeKey
,ComputerRowId
,ProcessRowID
,ProcessCmdRowID
,UserRowId
,PID
,CpuUsage
,MemoryUsage
,HandleCount
,ThreadCount
,PageFaultCount
,CpuTime
,TotalProcessTime
,IOReadPerSecond
,IOWritePerSecond
,[DateTime]
'
ELSE
'
SELECT
DateKey
,TimeKey
,ComputerRowId
,ProcessRowId
,ProcessCmdRowId
,UserRowId
,PID
,CpuUsageSampleCount
,CpuUsageAverageValue
,CpuUsageMinValue
,CpuUsageMaxValue
,CpuUsageStandardDeviation
,MemoryUsageSampleCount
,MemoryUsageAverageValue
,MemoryUsageMinValue
,MemoryUsageMaxValue
,MemoryUsageStandardDeviation
,HandleCountSampleCount
,HandleCountAverageValue
,HandleCountMinValue
,HandleCountMaxValue
,HandleCountStandardDeviation
,ThreadCountSampleCount
,ThreadCountAverageValue
,ThreadCountMinValue
,ThreadCountMaxValue
,ThreadCountStandardDeviation
,PageFaultCountSampleCount
,PageFaultCountAverageValue
,PageFaultCountMinValue
,PageFaultMaxValue
,PageFaultCountStandardDeviation
,IOReadPerSecondSampleCount
,IOReadPerSecondAverageValue
,IOReadPerSecondMinValue
,IOReadPerSecondMaxValue
,IOReadPerSecondStandardDeviation
,IOWritePerSecondSampleCount
,IOWritePerSecondAverageValue
,IOWritePerSecondMinValue
,IOWritePerSecondMaxValue
,IOWritePerSecondStandardDeviation
,CpuTimeSampleCount
,CpuTimeAverageValue
,CpuTimeMaxValue
,CpuTimeMinValue
,CpuTimeStandardDeviation
,TotalProcessTimeMaxValue
,[DateTime]
'
END
,MaxTableRowCount
,MaxTableSizeKb
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,BaseTableName varchar(256)
,MaxTableRowCount int
,MaxTableSizeKb int
,DataFileGroupName varchar(256)
,IndexFileGroupName varchar(256)
)
CROSS JOIN StandardDataset d
WHERE (d.DatasetId = '$Config/DatasetId$')
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'Dataset table definitions deployment was failed. Error: ' + @ErrorMessageText
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Dataset table definitions deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
------------------------------------------------------------------------------------------------------------
-- Create Indexes for the StandardDataset Aggregation Tables for Process Monitoring Performance Fact Tables
------------------------------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
-- create [DateTime] field indexes for all aggregation levels
DECLARE
@AggregationTypeId int
,@Statement nvarchar(max)
,@IndeGuid uniqueidentifier
,@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting dataset indexes deployment.'
SET @AggregationTypeId = -1
WHILE EXISTS (SELECT *
FROM StandardDatasetAggregation
WHERE (DatasetId = '$Config/DatasetId$')
AND (AggregationTypeId > @AggregationTypeId)
)
BEGIN
SELECT @IndeGuid = NEWID()
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Indexes deployment was finished successfully.'
EXEC DebugMessageInsert
@DatasetId = '$Config/DatasetId$'
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
END
GO
------------------------------------------------------------------
--- Create SCHEMA for new Process Monitoring Performance DataSet
-------------------------------------------------------------------
GO
DECLARE
@SchemaName sysname
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = @SchemaName)
BEGIN
EXECUTE('CREATE SCHEMA ' + @SchemaName)
END
DECLARE @Statement nvarchar(max)
-- change schema ownership to allow index rebuild and reorganize
SET @Statement = 'ALTER AUTHORIZATION ON SCHEMA::' + @SchemaName + ' TO OpsMgrWriter'
EXECUTE(@Statement)
SET @Statement = 'GRANT ALTER ON SCHEMA::' + @SchemaName + ' TO OpsMgrWriter'
EXECUTE(@Statement)
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
-----------------------------------------------------------------------
--- Start standard Process Monitoring Performance dataset deployment
-----------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@DebugMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Starting storage deployment.'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Subhourly' THEN '10'
WHEN 'Hourly' THEN '20'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SET @DebugMessage = @DebugPrefix + 'Storage deployment was failed. Error: ' + @ErrorMessageText
--------------------------------------------------------------------------------------
--- Start standard Process Monitoring Performance dataset staging tables deployment
--------------------------------------------------------------------------------------
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@Statement nvarchar(max)
,@DebugMessage nvarchar(max)
,@DebugLevel tinyint
,@DebugPrefix nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorInd = 0
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + ' Create staging table.'
-- insert staging definition into StandardDatasetStagingArea
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT * FROM StandardDatasetStagingArea WHERE DatasetId = '$Config/DatasetId$')
BEGIN
DELETE StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
END
INSERT StandardDatasetStagingArea
(
DatasetId
,StagingTableColumnDefinition
,BatchedProcessingSupportedInd
,MaxRowsToProcessPerTransactionCount
)
VALUES
(
'$Config/DatasetId$'
,'
DatasetId uniqueidentifier NOT NULL
,ManagementGroupGuid uniqueidentifier NOT NULL
,ManagedEntityGuid uniqueidentifier NOT NULL
,CollectedDate datetime NOT NULL
,DateKey int NOT NULL
,TimeKey int NOT NULL
,ProcessId int NOT NULL
,CpuUsage float NOT NULL
,MemoryUsage float NOT NULL
,HandleCount int NOT NULL
,ThreadCount int NOT NULL
,PageFaultCount int NOT NULL
,CpuTime bigint NOT NULL
,TotalProcessTime bigint NOT NULL
,IOReadPerSecond float NOT NULL
,IOWritePerSecond float NOT NULL
,ProcessName nvarchar(256) NOT NULL
,Description nvarchar(2000) NULL
,CommandLine nvarchar(max) NULL
,CommandLineHash varchar(64) NULL
,UserName varchar(256) NULL
,ManagedEntityRowId int NULL DEFAULT(0)
,ComputerRowId int NULL DEFAULT(0)
,ProcessRowId int NULL DEFAULT(0)
,ProcessCmdRowId int NULL DEFAULT(0)
,UserRowId int NULL DEFAULT(0)
,DWCreatedDateTime datetime NULL DEFAULT (GETUTCDATE())
,ProcessStageRowId bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY
'
,1
,100000
)
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
ELSE
BEGIN
DECLARE
@SchemaName sysname
,@TableName sysname
SET @TableName = 'ProcesStaging'
SELECT @SchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
BEGIN TRY
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SELECT @Statement = StagingTableColumnDefinition
FROM StandardDatasetStagingArea
WHERE DatasetId = '$Config/DatasetId$'
IF @Statement IS NOT NULL
BEGIN
SELECT @Statement = 'CREATE TABLE ' + @SchemaName + '.' + @TableName + '(' + @Statement + ')'
EXECUTE (@Statement)
END
ELSE
BEGIN
SET @DebugMessage = @DebugPrefix + 'Staging Data definition does not exist.'
GO
-------------------------------------------------------------------------
-- Create aggregation procedure for Process Monitoring Performance DataSet
-------------------------------------------------------------------------
DECLARE
@SchemaName sysname
,@TableSchemaName sysname
,@defaultSchemaName sysname
,@spName sysname
,@fullSpName sysname
,@Statement nvarchar(max)
,@DataSetMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@index int
,@spLength int
,@DebugLevel tinyint
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DataSetMessage = 'Process Monitoring: Process Performance DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure.'
SELECT
@TableSchemaName = SchemaName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
SELECT
@fullSpName = BuildAggregationStoredProcedureName
FROM
dbo.StandardDatasetAggregation
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Aggregation processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' + @fullSpName +
'
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
SET @DebugPrefix = ''Process Monitoring Performance DataSet: Aggregation procedure: ''
---------------------------------------------------------------
-- Parameter block sanity check
---------------------------------------------------------------
IF @DatasetId IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Aggregation dataset id is empty. Exiting procedure without calculation.''
RAISERROR (@DebugMessage, 16, 1)
END
IF @InsertTableGuid IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Aggregation table postfix is empty. Exiting procedure without calculation.''
RAISERROR (@DebugMessage, 16, 1)
END
IF @IntervalStartDateTime IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Interval Start Time is empty. Exiting procedure without calculation.''
RAISERROR (@DebugMessage, 16, 1)
END
IF @IntervalEndDateTime IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Interval End Time is empty. Exiting procedure without calculation.''
RAISERROR (@DebugMessage, 16, 1)
END
IF @AggregationTypeId IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Aggregation type is empty. Exiting procedure without calculation.''
RAISERROR (@DebugMessage, 16, 1)
END
---------------------------------------------------------------
-- End of Parameter block sanity check
---------------------------------------------------------------
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
SELECT @CoverViewName = ''v'' + BaseTableName
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
SELECT @InsertTableName = BaseTableName + ''_'' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @FullViewName = QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@CoverViewName)
SET @FullInsertTableName = QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@InsertTableName)
--------------------------------------------------------------------------------------------------
-- Sanity check for table and view
--------------------------------------------------------------------------------------------------
IF @InsertTableName IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Aggregation table does not exist for provided aggegation type '' + CONVERT(nvarchar(50),@AggregationTypeId) + '' .''
RAISERROR (@DebugMessage, 16, 1)
END
IF @SchemaName IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Schema name is not defined for DataSet Id: '' + CONVERT(nvarchar(50),@DatasetId) +'' .''
RAISERROR (@DebugMessage, 16, 1)
END
IF @CoverViewName IS NULL
BEGIN
SET @DebugMessage = @DebugPrefix + ''Source view does not exist for DataSet Id: '' + CONVERT(nvarchar(50),@DatasetId) +'' .''
RAISERROR (@DebugMessage, 16, 1)
END
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = @SchemaName)
BEGIN
SET @DebugMessage = @DebugPrefix + ''Schema '' + @SchemaName + '' does not exist in the datawarehouse.''
RAISERROR (@DebugMessage, 16, 1)
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = @CoverViewName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SET @DebugMessage = @DebugPrefix + ''Source view '' + @FullViewName + '' does not exist in the datawarehouse.''
RAISERROR (@DebugMessage, 16, 1)
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @InsertTableName AND TABLE_SCHEMA = @SchemaName)
BEGIN
SET @DebugMessage = @DebugPrefix + ''Target table '' + @InsertTableName + '' does not exist in the schema '' + @SchemaName + ''.''
RAISERROR (@DebugMessage, 16, 1)
END
-------------------------------------------------------------------------------------------------
-- End of Sanity check for table and view
--------------------------------------------------------------------------------------------------
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessage = ''Starting aggregation of type '' + CAST(@AggregationTypeId AS varchar(10)) + '' for interval from ''
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + ''UTC to ''
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + ''UTC''
SET @AggregationStartDateTime = GETUTCDATE()
IF (@DebugLevel > 2)
BEGIN
SET @DebugMessage = ''Finished aggregation of type '' + CAST(@AggregationTypeId AS varchar(10)) + '' for interval from ''
+ CONVERT(varchar(20), @IntervalStartDateTime, 120) + ''UTC to ''
+ CONVERT(varchar(20), @IntervalEndDateTime, 120) + ''UTC''
DECLARE @OperationDurationMs bigint
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @AggregationStartDateTime))
EXEC DebugMessageInsert
@DatasetId = @DatasetId
,@MessageLevel = @InfoLevel
,@MessageText = @DebugMessage
,@OperationDurationMs = @OperationDurationMs
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N''Failed to build aggregates for process mmonitoring performance data set. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
SET @DebugMessage = @DebugPrefix + 'Cannot create Aggregation processing stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
GO
------------------------------------------------------------------------------------
-- Create Aggregation delete procedure for Process Monitoring Performance DataSet
------------------------------------------------------------------------------------
DECLARE
@SchemaName sysname
,@TableSchemaName sysname
,@defaultSchemaName sysname
,@spName sysname
,@fullSpName sysname
,@Statement nvarchar(max)
,@DataSetMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@index int
,@spLength int
,@DebugLevel tinyint
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DataSetMessage = 'Process Monitoring: Process Performance DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure.'
SELECT
@TableSchemaName = SchemaName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
SELECT
@fullSpName = DeleteAggregationStoredProcedureName
FROM
dbo.StandardDatasetAggregation
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create AggregationDelete processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' + @fullSpName +
'
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
,@ErrorLevel int
,@InfoLevel int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in state data set
SELECT @TableName = BaseTableName + ''_'' + REPLACE(CAST(@TableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
-- Index hint in the query below ensures that SQL won''t
-- consider index on DateTime field to be a good source
-- to find rows to delete - it may happen periodically
-- if SQL thinks there''s no rows to delete due to
-- lack of [recently updated] statistics
SET @Statement = ''DELETE t'' +
+ '' FROM '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@TableName) + '' t ''
+ '' WHERE (t.[DateTime] = CONVERT(datetime, '''''' + CONVERT(varchar(50), @AggregationDateTime, 120) + '''''', 120))''
IF (@DebugLevel > 2)
BEGIN
SELECT @DebugMessage = ''Starting delete of existing process monitoring performance aggregated data. Table: '' + @TableName + '', aggregation date: '' + CONVERT(varchar(20), @AggregationDateTime, 120)
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N''Failed to delete aggregated data for process monitoring performance data set. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
SET @DebugMessage = @DebugPrefix + 'Cannot create AggregationDelete processing stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
GO
-------------------------------------------------------------------------
-- Create Grooming procedure for Process Monitoring Performance DataSet
-------------------------------------------------------------------------
DECLARE
@SchemaName sysname
,@TableSchemaName sysname
,@defaultSchemaName sysname
,@spName sysname
,@fullSpName sysname
,@Statement nvarchar(max)
,@DataSetMessage nvarchar(max)
,@DebugPrefix nvarchar(max)
,@DebugMessage nvarchar(max)
,@index int
,@spLength int
,@DebugLevel tinyint
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @DataSetMessage = 'Process Monitoring: Process Performance DataSet'
SET @defaultSchemaName = 'dbo'
SET @index = 0
SET @spLength = 0
----------------------------------------------------------------------------------
--- 0 - Logging disabled
--- 1 - Informational
--- 2 - Warning
--- 3 - Error
-----------------------------------------------------------------------------------
SET @DebugPrefix = 'Process Monitoring Performance DataSet: '
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure.'
SELECT
@TableSchemaName = SchemaName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
SELECT
@fullSpName = GroomStoredProcedureName
FROM
dbo.StandardDatasetAggregation
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create Grooming stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' + @fullSpName +
'
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
,@ErrorLevel int
,@InfoLevel int
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
FROM StandardDataset
WHERE DatasetId = @DatasetId
-- there are no dependent tables in HealthState data set
SELECT @TableName = BaseTableName + ''_'' + REPLACE(CAST(@TableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = @AggregationTypeId)
AND (DependentTableInd = 0)
SET @Statement = ''DELETE TOP ('' + CAST(@MaxRowsToGroom AS varchar(15)) + '')''
+ '' FROM '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@TableName)
+ '' WHERE ([DateTime] < CONVERT(datetime, '''''' + CONVERT(varchar(50), @CutoffDateTime, 120) + '''''', 120))''
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
DECLARE @DebugMessageText nvarchar(max)
SET @DebugMessageText = N''Failed to groom data for Process Monitoring Performance data set. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
SET @DebugMessage = @DebugPrefix + 'Cannot create Grooming stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SELECT
@TableSchemaName = SchemaName
,@fullSpName = StagingProcessorStoredProcedureName
,@DebugLevel = DebugLevel
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
------------------------------------------------------------------------------------------------
-- Check if ETL procedure is defined in dataset
------------------------------------------------------------------------------------------------
IF @TableSchemaName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not defined.'
IF @fullSpName IS NULL
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
GOTO Endproc
END
ELSE
BEGIN
SET @index = CHARINDEX('.',@fullSpName)
SET @spLength = LEN(@fullSpName)
--------------------------------------------------------------
-- if procedure does not have schema name use dbo by default
--------------------------------------------------------------
IF (0 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
SET @spName = @fullSpName
SET @fullSpName = @SchemaName + '.' + @fullSpName
END
ELSE
BEGIN
IF (1 = @index)
BEGIN
SET @SchemaName = @defaultSchemaName
END
ELSE
BEGIN
SET @SchemaName = LEFT(@fullSpName,@index - 1)
END
SET @spname = LTRIM(RTRIM(RIGHT(@fullSpName,@spLength - @index)))
IF (0 = LEN(@spname) )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not defined', 16, 1, @DataSetMessage )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not defined.'
IF (CHARINDEX('.',@spName) > 0 )
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Stored procedure name is not correct [%s]', 16, 1, @DataSetMessage,@fullSpName)
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Stored procedure name is not correct.'
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)
BEGIN
RAISERROR ('Cannot create ETL stored procedure for %s. Dataset schema %s is not exist.', 16, 1, @DataSetMessage,@SchemaName )
IF @DebugLevel > 0
BEGIN
SET @DebugMessage = @DebugPrefix + 'Trying to create stage processing stored procedure. Cannot create ETL stored procedure. Dataset schema is not exist.'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @SchemaName AND ROUTINE_NAME = @spName)
BEGIN
EXECUTE ('CREATE PROCEDURE ' + @fullSpName + ' AS RETURN 1')
END
SET @Statement = 'ALTER PROCEDURE ' +@fullSpName +
'
@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
,@ErrorLevel int
,@InfoLevel int
SET @ErrorLevel = 3
SET @InfoLevel = 1
SET @ErrorInd = 0
DECLARE
@DebugLevel int
,@SchemaName sysname
,@StagingTableName sysname
,@ExecResult int
,@LockResourceName sysname
,@InsertTableName sysname
,@InsertTableGuid uniqueidentifier
,@InsertStartedDateTime datetime
,@Statement nvarchar(max)
,@MessageText varchar(max)
,@OperationDurationMs bigint
,@RawDataMaxAgeDays int
,@MaxStagingRowsToProcess int
,@DebugMessageText nvarchar(max)
,@StageSp nvarchar(max)
,@ExecResultDim int
,@LockResourceNameDim sysname
BEGIN TRY
SELECT
@DebugLevel = DebugLevel
,@SchemaName = SchemaName
,@StageSp = StagingProcessorStoredProcedureName
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
----------------------------------
-- Sanity check for datasetId
----------------------------------
IF (@StageSp <> '''
SET @Statement = @Statement + @fullSpName + ''')
BEGIN
Goto EndProc
END
------------------------------------
-- Get dataset staging settings
----------------------------------
SELECT
@Statement = StagingTableColumnDefinition
,@MaxStagingRowsToProcess = ISNULL(MaxRowsToProcessPerTransactionCount,10000)
FROM StandardDatasetStagingArea
WHERE DatasetId = @DatasetId
IF (@Statement IS NULL)
BEGIN
Goto Endproc
END
-- get the minimum data retention period
-- to make sure we do not re-aggregate something
-- that is too old and all raw data is gone
SELECT @RawDataMaxAgeDays = MaxDataAgeDays
FROM StandardDatasetAggregation
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
--------------------------------------------------------------------------
-- Delete old records from staging
--------------------------------------------------------------------------
DELETE ' + @SchemaName + '.ProcesStaging
WHERE (DatasetId = @DatasetId)
AND ([CollectedDate] < DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()) )
'
SET @Statement = @Statement +
'
-------------------------------------------------------
-- Tables Sanity Check
--------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ComputerDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ComputerDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''UserDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find UserDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ProcessDim table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessCmdDim'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find ProcessCMdDim table exiting..''
Goto SanityCheck
END
SELECT @InsertTableName = BaseTableName + ''_'' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @InsertTableName AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
SET @DebugMessageText = N''Cannot find '' + @InsertTableName + '' table exiting..''
Goto SanityCheck
END
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessStaging2Process'' AND TABLE_SCHEMA = @SchemaName)
BEGIN
BEGIN TRAN
-- need to process subset of rows
-- create extra column to store original row id
SELECT @Statement =
''CREATE TABLE '' + @SchemaName + ''.ProcessStaging2Process (''
+ @Statement
+ '',OriginalProcessStageRowId bigint NOT NULL''
+ '')''
EXECUTE (@Statement)
COMMIT
'
SET @Statement = @Statement +
'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ProcessStaging2Process'' AND TABLE_SCHEMA = ''' + @SchemaName + ''')
BEGIN
Goto Endproc
END
COMMIT
END -- IF NOT EXISTS ' + @SchemaName + '.ProcessStaging2Process
-- create index on datetime field
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_ProcessStaging2Process_DateTime'')
BEGIN
CREATE INDEX IX_ProcessStaging2Process_DateTime ON ' + @SchemaName + '.ProcessStaging2Process(CollectedDate)
END
BEGIN TRAN
-- lock dimension tables to ensure we insert into dimensions opened for insertion
SET @LockResourceNameDim = ''ProcessMonitoringDimensions_TableMap''
SELECT @InsertTableName = BaseTableName + ''_'' + REPLACE(CAST(@InsertTableGuid AS varchar(50)), ''-'', '''')
FROM StandardDatasetAggregationStorage
WHERE (DatasetId = @DatasetId)
AND (AggregationTypeId = 0)
AND (DependentTableInd = 0)
-------------------------------------------------------------
-- Updating Dimensions
--------------------------------------------------------------
--------------------------------------------------------------
--Update Computer Dimension
--------------------------------------------------------------
--- 2.1 Update Staging Table with ManagedEntityRowId from Microsoft Windows Computer
UPDATE stage
SET
ManagedEntityRowid = me.ManagedEntityRowid
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN vManagementGroup mg ON (stage.ManagementGroupGuid = mg.ManagementGroupGuid)
JOIN vManagedEntity me ON ((me.ManagedEntityGuid = stage.ManagedEntityGuid) AND (mg.ManagementGroupRowId = me.ManagementGroupRowId))
JOIN vManagedEntityType met ON (met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId)
WHERE (stage.DatasetId = @DatasetId) AND ( met.ManagedEntityTypeGuid = ''EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD'')
---2.2 Insert new Computers in Computer Dimension
INSERT INTO ' + @SchemaName + '.ComputerDim
(
ManagementEntityRowId
,Name
,NetbiosName
,PrincipalName
)
SELECT DISTINCT
me.ManagedEntityRowid
,UPPER(me.Name)
, CASE CHARINDEX(''.'',me.Name)
WHEN 0 THEN UPPER(me.Name)
ELSE
UPPER(LEFT(me.Name,CHARINDEX(''.'',me.Name) - 1))
END
,LOWER(me.Name)
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN vManagedEntity me ON me.ManagedEntityRowId = stage.ManagedEntityRowId
WHERE
NOT EXISTS (SELECT *
FROM ' + @SchemaName + '.ComputerDim
WHERE (ManagementEntityRowId = me.ManagedEntityRowId)
)
AND
stage.ManagedEntityRowid <> 0
AND
me.Name IS NOT NULL
---2.3 Update Staging with Computer Dimension Key
UPDATE stage
SET
ComputerRowId = pc.ComputerRowid
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN ' + @SchemaName + '.ComputerDim pc ON (stage.ManagedEntityRowId = pc.ManagementEntityRowId)
--------------------------------------------------------------
--Update User Dimension
--------------------------------------------------------------
---3.1 Insert new Users in User Dimension
INSERT INTO ' + @SchemaName + '.UserDim
(
LogonName
,Domain
,UserName
)
SELECT DISTINCT
UPPER(stage.UserName)
,UPPER(
CASE CHARINDEX(''\'',stage.UserName)
WHEN 0 THEN ''''
ELSE
LEFT(stage.UserName,CHARINDEX(''\'',stage.UserName) - 1)
END
)
,UPPER(
CASE CHARINDEX(''\'',stage.UserName)
WHEN 0 THEN stage.UserName
ELSE
RIGHT(stage.UserName,LEN(stage.UserName)- CHARINDEX(''\'',stage.UserName))
END
)
FROM
' + @SchemaName + '.ProcessStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.UserDim u ON (ISNULL(stage.UserName,'''') = u.LogonName)
WHERE
u.UserRowid IS NULL
AND
stage.ManagedEntityRowid <> 0
---3.3 Update Staging with new User Dimension Key
UPDATE stage
SET
UserRowId = u.UserRowid
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN ' + @SchemaName + '.UserDim u ON (ISNULL(stage.UserName,'''') = u.LogonName)
WHERE
stage.ManagedEntityRowid <> 0
--------------------------------------------------------------
--Update Process Dimension
--------------------------------------------------------------
---4.1. Insert new Processes in Process Dimension
;WITH ProcessByName AS
(
SELECT ProcessName,ISNULL([Description],'''') AS [Description], ROW_NUMBER() OVER(PARTITION BY ProcessName ORDER BY [Description]) as Id
FROM
' + @SchemaName + '.ProcessStaging2Process
WHERE
ManagedEntityRowid <> 0
),
Processes AS
(
SELECT ProcessName, [Description]
FROM ProcessByName
WHERE
Id = 1
)
INSERT INTO ' + @SchemaName + '.ProcessDim
(
ProcessName
,[Description]
)
SELECT
stage.ProcessName
,stage.[Description]
FROM
Processes stage
LEFT OUTER JOIN ' + @SchemaName + '.ProcessDim p ON (stage.ProcessName = p.ProcessName)
WHERE
p.ProcessRowId IS NULL
---4.2. Update Staging with new Process Dimension Key
UPDATE stage
SET
ProcessRowId = p.ProcessRowid
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN ' + @SchemaName + '.ProcessDim p ON (stage.ProcessName = p.ProcessName)
WHERE
stage.ManagedEntityRowid <> 0
--------------------------------------------------------------
--Update Process Command Line Dimension
--------------------------------------------------------------
---5.1. Insert new Process Command Lines in ProcessCmd Dimension
INSERT INTO ' + @SchemaName + '.ProcessCmdDim
(
CommandLine
,CommandLineHash
)
SELECT DISTINCT
stage.CommandLine
,ISNULL(stage.CommandLineHash, @defaultHash)
FROM
' + @SchemaName + '.ProcessStaging2Process stage
LEFT OUTER JOIN ' + @SchemaName + '.ProcessCmdDim p ON (ISNULL(stage.CommandLineHash,@defaultHash) = p.CommandLineHash)
WHERE
p.ProcessCmdRowId IS NULL
AND
stage.ManagedEntityRowid <> 0
---5.2. Update Staging with new Process Command Line Dimension Key
UPDATE stage
SET
ProcessCmdRowId = p.ProcessCmdRowid
FROM
' + @SchemaName + '.ProcessStaging2Process stage
JOIN ' + @SchemaName + '.ProcessCmdDim p ON (ISNULL(stage.CommandLineHash,@defaultHash) = p.CommandLineHash)
WHERE
stage.ManagedEntityRowid <> 0
BEGIN TRAN
-- lock table map to ensure we insert into table opened for insertion
SET @LockResourceName = CAST(@DatasetId AS varchar(50)) + ''_TableMap''
IF (@ExecResult < 0)
BEGIN
RAISERROR(777971001, 16, 1, ''Shared:StandardDatasetTableMap'', @ExecResult)
END
-------------------------------------------------------------------------------------------------
-- Updating Fact Process Performance Table
--------------------------------------------------------------------------------------------------
IF (@DebugLevel > 0)
BEGIN
SELECT @MessageText = ''Starting to insert '' + CAST(COUNT(*) AS varchar(30)) + '' process performance samples into '' + @InsertTableName
FROM ' + @SchemaName + '.ProcessStaging2Process
-- mark existing affected aggregations as dirty
UPDATE ah
SET DirtyInd = 1
,DataLastReceivedDateTime = GETUTCDATE()
FROM StandardDatasetAggregationHistory ah
JOIN StandardDatasetAggregation a ON (ah.DatasetId = a.DatasetId AND ah.AggregationTypeId = a.AggregationTypeId)
CROSS JOIN ' + @SchemaName + '.ProcessStaging2Process p
WHERE (ah.DatasetId = @DatasetId)
AND (ah.AggregationDateTime > DATEADD(day, -@RawDataMaxAgeDays, GETUTCDATE()))
AND (p.[CollectedDate] >= ah.AggregationDateTime)
AND (p.[CollectedDate] < DATEADD(minute, a.AggregationIntervalDurationMinutes, ah.AggregationDateTime))
-- delete processed rows from staging
DELETE ps
FROM ' + @SchemaName + '.ProcesStaging ps
JOIN ' + @SchemaName + '.ProcessStaging2Process psp ON (ps.ProcessStageRowId = psp.OriginalProcessStageRowId )
-- delete all inserted data from staging
DROP TABLE ' + @SchemaName + '.ProcessStaging2Process
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Performance DataSet: Finished inserting performance state samples into '' + @InsertTableName
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
-- report error if any
IF (@ErrorInd = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to process data in the Process Monitroing Performance DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
DeleteWrongDataSet:
--------------------------------------------------------------------------
-- Delete wrong datasets from staging
--------------------------------------------------------------------------
DECLARE
@delBatchSize int
,@ErrorIndDel int
SET @ErrorIndDel = 0
SET @delBatchSize = @MaxStagingRowsToProcess
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Performance DataSet: Starting deletion of wrong DataSets records from staging ''
IF (@ErrorIndDel = 1)
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @DebugMessageText = N''Failed to delete data from wromg dataset in the Process Monitroing Process Performance DataSet staging area. Error '' + CAST(@ErrorNumber AS varchar(15))
+ '', Procedure '' + @ErrorProcedure
+ '', Line '' + CAST(@ErrorLine AS varchar(15))
+ '', Message: ''+ @ErrorMessageText
END
ELSE
BEGIN
IF (@DebugLevel > 0)
BEGIN
SET @MessageText = ''Process Monitroing Performance DataSet: Finished deleting wrong dataset records from staging ''
SET @OperationDurationMs = ABS(DATEDIFF(ms, GETUTCDATE(), @InsertStartedDateTime))
END --IF
Endproc:
END --Procedure [ProcessMonitoring].[PerformanceProcessStaging]
'
DECLARE @ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
BEGIN TRY
EXECUTE (@Statement)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
SET @DebugMessage = @DebugPrefix + 'Cannot create stage processing stored procedure. Error: ' + @ErrorMessageText
END CATCH
IF (1 = @ErrorInd)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
SELECT
@TableSchemaName = SchemaName
FROM
dbo.StandardDataSet
WHERE DatasetId = '$Config/DatasetId$'
-- drop SPs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PerformanceFactsAggregate')
BEGIN
EXECUTE('DROP PROCEDURE ' + @TableSchemaName + '.PerformanceFactsAggregate')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PerformanceFactsAggregateDelete')
BEGIN
EXECUTE ('DROP PROCEDURE ' + @TableSchemaName + '.PerformanceFactsAggregateDelete')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PerformanceFactsGroom')
BEGIN
EXECUTE('DROP PROCEDURE ' + @TableSchemaName + '.PerformanceFactsGroom')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = @TableSchemaName AND ROUTINE_NAME = 'PerformanceProcessStaging')
BEGIN
EXECUTE ('DROP PROCEDURE ' + @TableSchemaName + '.PerformanceProcessStaging')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ComputerDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.ComputerDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'UserDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.UserDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'TimeDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.TimeDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'DateDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.DateDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ProcessDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.ProcessDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ProcessCmdDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP VIEW ' + @TableSchemaName +'.ProcessCmdDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ComputerDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.UserDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TimeDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.TimeDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.DateDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProcessDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ProcessDim')
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProcessCmdDim' AND TABLE_SCHEMA = @TableSchemaName)
BEGIN
EXECUTE ('DROP TABLE ' + @TableSchemaName +'.ProcessCmdDim')
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO </Uninstall>
<Upgrade/>
</DataWarehouseDataSet>
Home
ENU