if not exists (select * from sysobjects where type = 'P' AND name = N'TransformDataSourceDimProc')
begin
execute ('create procedure dbo.[TransformDataSourceDimProc] @WaterMark xml as return 1')
end
go
DECLARE @Statement NVARCHAR(MAX)
SET @Statement=
N'alter procedure [dbo].[TransformDataSourceDimProc] (@WaterMark xml)
as
begin
set nocount on
merge dbo.DataSourceDim AS target
using (
select distinct src.BaseManagedEntityId,
src.[System.Entity!DisplayName],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceName],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!DatasourceType],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateRegistered],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateUnRegistered],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!ProductVersion],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!RegistrationStatus],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!SecureReferenceId],
src.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceId],
src.IsDeleted,
src.DataSourceId
from inbound.MTV_Microsoft$SystemCenter$DataWarehouse$DataSource src,
(
SELECT MAX(DWTimestamp) as MaxTimestamp, BaseManagedEntityId as EntityId
from inbound.MTV_Microsoft$SystemCenter$DataWarehouse$DataSource
where DWTimestamp >= @source1WM
and DWTimestamp < @source1MaxWM
GROUP BY BaseManagedEntityId
) maxResults
where src.DWTimestamp = MaxTimestamp and src.BaseManagedEntityId = EntityId
) as source
on (target.SourceId = source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceId])
when matched then
update set
BaseManagedEntityId = source.BaseManagedEntityId,
DisplayName = isnull(source.[System.Entity!DisplayName],''NA''),
DataSourceName = isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceName],''NA''),
DatasourceType = source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DatasourceType],
DateRegistered = source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateRegistered],
DateUnRegistered = isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateUnRegistered],cast(''1/1/1900'' as DateTime)),
ProductVersion = source.[Microsoft.SystemCenter.DataWarehouse.DataSource!ProductVersion],
RegistrationStatus = source.[Microsoft.SystemCenter.DataWarehouse.DataSource!RegistrationStatus],
SecureReferenceId = isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!SecureReferenceId],@nullguid),
DataSourceId = isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceId],@nullguid),
IsDeleted = source.IsDeleted,
UpdatedBatchId = @BatchId,
@Updated = @Updated + 1
when not matched
by target then
insert (BaseManagedEntityId, SourceId, DisplayName, DataSourceName, DataSourceType, DateRegistered, DateUnRegistered, ProductVersion,
RegistrationStatus, SecureReferenceId, ManagementGroupId, IsDeleted, UpdatedBatchId, InsertedBatchId, DataSourceId)
values (BaseManagedEntityId,
isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceId], @nullguid),
isnull(source.[System.Entity!DisplayName],''NA''),
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceName],
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceType],
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateRegistered],
isnull(source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DateUnRegistered],cast(''1/1/1900'' as DateTime)),
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!ProductVersion],
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!RegistrationStatus],
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!SecureReferenceId],
@nullguid,
source.IsDeleted,
0,
@BatchId,
source.[Microsoft.SystemCenter.DataWarehouse.DataSource!DataSourceId]);
select @Inserted=@@RowCount,@err=@@error
set @Inserted = @Inserted - @Updated
-- PS 225486 There are cases during upgrade
-- when we are in between upgrading the datawarehouse
-- datasource type and the new properties, we can get
-- null values in the Dimension. Deleting those values.
delete dbo.DataSourceDim
where SourceId = @nullguid and BaseManagedEntityId != @nullguid
if(@err<>0)
begin
raiserror(''Failed to insert into DataSource Dimension'', 16,1)
return
end
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'U' AND name = 'TablePartition' )
BEGIN
EXEC sp_executesql @Statement
END
GO
/*
Adding this to the same file in the interest of time.
We need to move this out to a seperate file
A bug will be opened to track this.
*/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'InferDataSourceDimProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[InferDataSourceDimProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement1 NVARCHAR(MAX)
SET @Statement1=
N'ALTER PROCEDURE [dbo].[InferDataSourceDimProc](
@sourceTableName NVARCHAR(256),
@columnName SYSNAME,
@filterColumnName SYSNAME,
@minTimeStamp DATETIME,
@maxTimeStamp DATETIME,
@batchId INT
)
AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @retval INT = -1,
@err INT = 0,
@startTranCount INT = @@TRANCOUNT,
@queryTemplate NVARCHAR(MAX)
BEGIN TRY
IF OBJECT_ID(@sourceTableName) IS NULL
RETURN;
SELECT @task = ''Shredding WaterMark''
SELECT @queryTemplate = ''
INSERT INTO dbo.DataSourceDim(
BaseManagedEntityId,
SourceId,
DataSourceName,
SecureReferenceId,
DisplayName,
DateRegistered,
DateUnRegistered,
DatasourceType,
ManagementGroupId,
ProductVersion,
RegistrationStatus,
IsDeleted,
InsertedBatchId,
UpdatedBatchId,
DataSourceId
)
SELECT DISTINCT
src.%COLUMNNAME% AS BaseManagedEntityId,
src.%COLUMNNAME% AS SourceId,
NULL AS DataSourceName,
NULL AS SecureReferenceId,
NULL AS DisplayName,
NULL AS DateRegistered,
NULL AS DateUnRegistered,
NULL AS DatasourceType,
NULL AS ManagementGroupId,
NULL AS ProductVersion,
NULL AS RegistrationStatus,
0 AS IsDeleted,
%BATCHID% AS InsertedBatchId,
0 AS UpdatedBatchId,
NULL AS DataSourceId
FROM %TABLENAME% src
LEFT JOIN dbo.DatasourceDim dimDS ON
src.%COLUMNNAME% = dimDS.SourceId
WHERE src.%FILTERTIMESTAMPCOLUMN% > ''''%MINTIMESTAMP%''''
AND src.%FILTERTIMESTAMPCOLUMN% <= ''''%MAXTIMESTAMP%''''
AND dimDS.SourceId IS NULL
AND src.%COLUMNNAME% IS NOT NULL
''
Home