Metadaten Management im Kontext eines Data Warehouses bezieht sich auf die Organisation, Verwaltung und Kontrolle von Meta Daten, die Informationen über die im DWH gespeicherten Daten enthalten. Meta Daten bieten einen Kontext für Daten, erleichtern das Verständnis der Datenstruktur, Herkunft und Nutzung und unterstützen somit effektive Datenverwaltung und -analyse.
Typen von Meta Daten im DWH
- Technische Meta Daten: Informationen über Datenbanken, Tabellen, Spalten, Indizes, Datentypen usw.
- Betriebs-Meta Daten: Informationen über ETL-Prozesse, Datenladungen, Job-Logs, Ausführungsstatus usw.
- Geschäfts-Meta Daten: Informationen über Geschäftsregeln, Datenqualitätsmetriken, KPI-Definitionen usw.
Beispiel: Meta Daten Management im DWH
Angenommen, ein E-Commerce-Unternehmen hat ein DWH, das Daten aus verschiedenen Quellen wie Website-Tracking, CRM-System, ERP-System und externen Datenfeeds integriert.
- Technische Meta Daten könnten Informationen über:
- Tabellen, die Kundeninformationen, Transaktionsdaten und Produktinformationen speichern.
- Die Beziehungen zwischen verschiedenen Tabellen.
- Datentypen und Formatierungen der einzelnen Attribute.
- Betriebs-Meta Daten könnten Informationen über:
- ETL-Jobs, die Daten aus verschiedenen Quellen extrahieren, transformieren und in das DWH laden.
- Fehler oder Warnungen, die während der ETL-Prozesse aufgetreten sind.
- Die Aktualität der Daten und den Zeitpunkt der letzten Aktualisierung.
- Geschäfts-Meta Daten könnten Informationen über:
- Geschäftsregeln, wie z.B. die Definition eines „aktiven Kunden“ oder KPIs wie „Customer Lifetime Value“.
- Beschreibungen von Datenfeldern, um den Geschäftsanwendern zu helfen, die Daten korrekt zu interpretieren.
- Datenqualitätsmetriken, wie z.B. die Anzahl der fehlenden oder ungültigen Werte in kritischen Feldern.
Aufbau eines Meta Daten Management Systems im DWH
- Meta Daten Repository:
- Ein zentrales Repository, das alle Meta Daten speichert und verwaltet.
- Muss in der Lage sein, verschiedene Arten von Meta Daten zu speichern und zu kategorisieren.
- Meta Daten Extraktion und Laden:
- Prozesse und Tools, um Meta Daten aus verschiedenen Quellen (Datenbanken, ETL-Tools, BI-Tools usw.) zu extrahieren und in das Meta Daten Repository zu laden.
- Meta Daten Integration:
- Mechanismen, um Meta Daten aus verschiedenen Quellen zu integrieren und eine konsistente Sicht auf die Meta Daten zu bieten.
- Meta Daten Nutzung:
- Tools und Schnittstellen, um Meta Daten für verschiedene Zwecke zu nutzen, z.B. Datenkataloge, Datenlinienage-Visualisierung, Impact-Analyse usw.
- Meta Daten Sicherheit und Governance:
- Richtlinien und Werkzeuge, um sicherzustellen, dass Meta Daten sicher gespeichert und nur von autorisierten Benutzern eingesehen oder geändert werden können.
- Meta Daten Wartung:
- Prozesse, um sicherzustellen, dass Meta Daten aktuell, genau und vollständig sind.
- Mechanismen, um Änderungen in den Quellsystemen zu erkennen und die Meta Daten entsprechend zu aktualisieren.
Ein effektives Meta Daten Management im DWH ermöglicht es den Organisationen, ihre Daten besser zu verstehen, zu verwalten und zu nutzen. Durch die Bereitstellung von Kontext und zusätzlichen Informationen über die Daten unterstützt es sowohl technische als auch geschäftliche Benutzer in ihren täglichen Aufgaben und Entscheidungsprozessen.
Um in unserem Data Warehouse alle Arten der Änderung auf Datenbankebene zu Historisieren, können wir folgendes verwenden:
Als erstes müssen wir die folgenden Objekte anlegen:
Entweder eine bestehende Datenbank für die Meta Daten verwenden, oder aber eine neue anlegen:
CREATE DATABASE [META]
GO
Als nächstes benötigen wir 3 Tabellen für die Inhaltliche Struktur unserer DWH Objekte, diese legen wir auch auf der META Datenbank an:
USE [META]
GO
CREATE TABLE [dbo].[DWH_Objects](
[ObjectDatabase] [varchar](255) NOT NULL,
[ObjectSCHEMA] [nvarchar](50) NOT NULL,
[ObjectTYPE] [varchar](50) NOT NULL,
[ObjectName] [sysname] NOT NULL,
[ObjectDefinition] [ntext] NULL,
[GueltigVon] [datetime] NOT NULL,
[GueltigBis] [datetime] NOT NULL,
CONSTRAINT [PK_DWH_Objects] PRIMARY KEY CLUSTERED
(
[ObjectDatabase] ASC,
[ObjectSCHEMA] ASC,
[ObjectTYPE] ASC,
[ObjectName] ASC,
[GueltigVon] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[DWH_Objects] ADD DEFAULT (getdate()) FOR [GueltigVon]
GO
ALTER TABLE [dbo].[DWH_Objects] ADD DEFAULT ('01/01/2099') FOR [GueltigBis]
GO
-- hier die 2. Detail Tabelle
CREATE TABLE [dbo].[DWH_Objects_detail](
[TABLE_CATALOG] [nvarchar](120) NOT NULL,
[TABLE_SCHEMA] [nvarchar](50) NOT NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NOT NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](255) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL,
[GueltigVon] [datetime] NOT NULL,
[GueltigBis] [datetime] NOT NULL,
CONSTRAINT [PK_DWH_Objects_detail] PRIMARY KEY CLUSTERED
(
[TABLE_CATALOG] ASC,
[TABLE_SCHEMA] ASC,
[TABLE_NAME] ASC,
[COLUMN_NAME] ASC,
[GueltigVon] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DWH_Objects_detail] ADD DEFAULT (getdate()) FOR [GueltigVon]
GO
ALTER TABLE [dbo].[DWH_Objects_detail] ADD DEFAULT ('01/01/2099') FOR [GueltigBis]
GO
CREATE TABLE [dbo].[DWH_SpaceInfo](
[DBName] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NOT NULL,
[Table] [nvarchar](128) NOT NULL,
[RowCount] [int] NULL,
[SizeInMB] [int] NULL,
[CreateDate] [datetime] NOT NULL,
[DateID] [int] NULL
) ON [PRIMARY]
GO
Jetzt legen wir eine Prozedur an, diese gleicht beim Aufruf die vorhandenen Objekte ab und schreibt jede Änderung in unsere 2 Tabellen:
USE [META]
GO
CREATE PROCEDURE [dbo].[MetaUpdate]
-- Add the parameters for the stored procedure here
AS
DECLARE @Script NVARCHAR(max)
DECLARE @DB SYSNAME
DECLARE cMetaUpdate CURSOR READ_ONLY FOR
select name
from sys.databases
WHERE name not In ( 'master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
-- Hier die Datenbanken ausschließen, die wir nicht jeden Tag überwachen wollen.
CREATE TABLE #tmp_Objects
(
[ObjectDatabase] [varchar](255) NOT NULL,
[ObjectSCHEMA] [nvarchar](50) NOT NULL,
[ObjectTYPE] [varchar](50) NOT NULL,
[ObjectName] [sysname] NOT NULL,
[ObjectDefinition] [ntext] NULL
);
CREATE TABLE #tmp_Objects_detail
(
[TABLE_CATALOG] [nvarchar](120) NOT NULL,
[TABLE_SCHEMA] [nvarchar](50) NOT NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NOT NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](255) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
);
OPEN cMetaUpdate
FETCH NEXT FROM cMetaUpdate INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Script = 'insert into #tmp_Objects_detail
SELECT * FROM '+ QUOTENAME(@DB) +'.Information_Schema.Columns with (nolock) ORDER BY [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME]'
exec sp_executesql @Script
SELECT @Script = 'insert into #tmp_Objects ([ObjectDatabase],[ObjectSCHEMA], [ObjectTYPE],[ObjectName],[ObjectDefinition])
SELECT '''+ QUOTENAME(@DB) +''' AS ObjectDatabase,
s.name as [ObjectSCHEMA],
o.type_desc AS ObjectTYPE, o.name as ObjectName,
cast(sm.definition as ntext) as ObjectDefinition
FROM '+ QUOTENAME(@DB) +'.sys.sql_modules AS sm with (nolock)
JOIN '+ QUOTENAME(@DB) +'.sys.objects AS o with (nolock) ON sm.object_id = o.object_id
JOIN '+ QUOTENAME(@DB) +'.sys.schemas as s with (nolock) ON o.schema_id = s.schema_id
WHERE o.type IN (''P '', ''V'')
UNION ALL
SELECT '''+ QUOTENAME(@DB) +''' AS ObjectDatabase,
TABLE_SCHEMA as [ObjectSCHEMA],
''Table'' AS ObjectTYPE ,TABLE_NAME as ObjectName,
'''' as ObjectDefinition
FROM '+ QUOTENAME(@DB) +'.INFORMATION_SCHEMA.TABLES with (nolock)
where TABLE_TYPE = ''BASE TABLE'' ORDER BY [ObjectDatabase],[ObjectTYPE],[ObjectName]'
exec sp_executesql @Script
FETCH NEXT FROM cMetaUpdate INTO @DB
END
---DWH_Objects_detail
MERGE [dbo].[DWH_Objects_detail] AS TARGET
USING #tmp_Objects_detail AS SOURCE
ON (
TARGET.[TABLE_CATALOG]= SOURCE.[TABLE_CATALOG]
AND TARGET.[TABLE_SCHEMA]= SOURCE.[TABLE_SCHEMA]
AND TARGET.[TABLE_NAME]= SOURCE.[TABLE_NAME]
AND TARGET.[COLUMN_NAME]= SOURCE.[COLUMN_NAME]
AND TARGET.[ORDINAL_POSITION]= SOURCE.[ORDINAL_POSITION] AND TARGET.[GueltigBis] = '01.01.2099')
WHEN MATCHED AND TARGET.[COLUMN_DEFAULT] <> SOURCE.[COLUMN_DEFAULT] OR TARGET.[IS_NULLABLE] <> SOURCE.[IS_NULLABLE] OR TARGET.[DATA_TYPE] <> SOURCE.[DATA_TYPE] OR TARGET.[CHARACTER_MAXIMUM_LENGTH] <> SOURCE.[CHARACTER_MAXIMUM_LENGTH]
OR TARGET.[CHARACTER_OCTET_LENGTH] <> SOURCE.[CHARACTER_OCTET_LENGTH] OR TARGET.[NUMERIC_PRECISION] <> SOURCE.[NUMERIC_PRECISION] OR TARGET.[NUMERIC_PRECISION_RADIX] <> SOURCE.[NUMERIC_PRECISION_RADIX]
OR TARGET.[NUMERIC_SCALE] <> SOURCE.[NUMERIC_SCALE] OR TARGET.[DATETIME_PRECISION] <> SOURCE.[DATETIME_PRECISION] OR TARGET.[CHARACTER_SET_CATALOG] <> SOURCE.[CHARACTER_SET_CATALOG] OR TARGET.[CHARACTER_SET_SCHEMA] <> SOURCE.[CHARACTER_SET_SCHEMA] OR TARGET.[CHARACTER_SET_NAME] <> SOURCE.[CHARACTER_SET_NAME] OR TARGET.[COLLATION_CATALOG] <> SOURCE.[COLLATION_CATALOG]
OR TARGET.[COLLATION_SCHEMA] <> SOURCE.[COLLATION_SCHEMA] OR TARGET.[COLLATION_NAME] <> SOURCE.[COLLATION_NAME] OR TARGET.[DOMAIN_CATALOG] <> SOURCE.[DOMAIN_CATALOG] OR TARGET.[DOMAIN_SCHEMA] <> SOURCE.[DOMAIN_SCHEMA] OR TARGET.[DOMAIN_NAME] <> SOURCE.[DOMAIN_NAME]
THEN UPDATE SET TARGET.[GueltigBis] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[GueltigBis] = '01.01.2099' THEN UPDATE SET TARGET.[GueltigBis] = getdate()
WHEN NOT MATCHED BY TARGET
THEN INSERT ([TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[COLUMN_NAME]
,[ORDINAL_POSITION]
,[COLUMN_DEFAULT]
,[IS_NULLABLE]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[DOMAIN_CATALOG]
,[DOMAIN_SCHEMA]
,[DOMAIN_NAME]) VALUES (SOURCE.[TABLE_CATALOG]
,SOURCE.[TABLE_SCHEMA]
,SOURCE.[TABLE_NAME]
,SOURCE.[COLUMN_NAME]
,SOURCE.[ORDINAL_POSITION]
,SOURCE.[COLUMN_DEFAULT]
,SOURCE.[IS_NULLABLE]
,SOURCE.[DATA_TYPE]
,SOURCE.[CHARACTER_MAXIMUM_LENGTH]
,SOURCE.[CHARACTER_OCTET_LENGTH]
,SOURCE.[NUMERIC_PRECISION]
,SOURCE.[NUMERIC_PRECISION_RADIX]
,SOURCE.[NUMERIC_SCALE]
,SOURCE.[DATETIME_PRECISION]
,SOURCE.[CHARACTER_SET_CATALOG]
,SOURCE.[CHARACTER_SET_SCHEMA]
,SOURCE.[CHARACTER_SET_NAME]
,SOURCE.[COLLATION_CATALOG]
,SOURCE.[COLLATION_SCHEMA]
,SOURCE.[COLLATION_NAME]
,SOURCE.[DOMAIN_CATALOG]
,SOURCE.[DOMAIN_SCHEMA]
,SOURCE.[DOMAIN_NAME]);
MERGE [dbo].[DWH_Objects_detail] AS TARGET
USING #tmp_Objects_detail AS SOURCE
ON (
TARGET.[TABLE_CATALOG]= SOURCE.[TABLE_CATALOG]
AND TARGET.[TABLE_SCHEMA]= SOURCE.[TABLE_SCHEMA]
AND TARGET.[TABLE_NAME]= SOURCE.[TABLE_NAME]
AND TARGET.[COLUMN_NAME]= SOURCE.[COLUMN_NAME]
AND TARGET.[ORDINAL_POSITION]= SOURCE.[ORDINAL_POSITION] AND TARGET.[GueltigBis] = '01.01.2099')
WHEN NOT MATCHED BY TARGET
THEN INSERT ([TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[COLUMN_NAME]
,[ORDINAL_POSITION]
,[COLUMN_DEFAULT]
,[IS_NULLABLE]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[CHARACTER_OCTET_LENGTH]
,[NUMERIC_PRECISION]
,[NUMERIC_PRECISION_RADIX]
,[NUMERIC_SCALE]
,[DATETIME_PRECISION]
,[CHARACTER_SET_CATALOG]
,[CHARACTER_SET_SCHEMA]
,[CHARACTER_SET_NAME]
,[COLLATION_CATALOG]
,[COLLATION_SCHEMA]
,[COLLATION_NAME]
,[DOMAIN_CATALOG]
,[DOMAIN_SCHEMA]
,[DOMAIN_NAME]) VALUES (SOURCE.[TABLE_CATALOG]
,SOURCE.[TABLE_SCHEMA]
,SOURCE.[TABLE_NAME]
,SOURCE.[COLUMN_NAME]
,SOURCE.[ORDINAL_POSITION]
,SOURCE.[COLUMN_DEFAULT]
,SOURCE.[IS_NULLABLE]
,SOURCE.[DATA_TYPE]
,SOURCE.[CHARACTER_MAXIMUM_LENGTH]
,SOURCE.[CHARACTER_OCTET_LENGTH]
,SOURCE.[NUMERIC_PRECISION]
,SOURCE.[NUMERIC_PRECISION_RADIX]
,SOURCE.[NUMERIC_SCALE]
,SOURCE.[DATETIME_PRECISION]
,SOURCE.[CHARACTER_SET_CATALOG]
,SOURCE.[CHARACTER_SET_SCHEMA]
,SOURCE.[CHARACTER_SET_NAME]
,SOURCE.[COLLATION_CATALOG]
,SOURCE.[COLLATION_SCHEMA]
,SOURCE.[COLLATION_NAME]
,SOURCE.[DOMAIN_CATALOG]
,SOURCE.[DOMAIN_SCHEMA]
,SOURCE.[DOMAIN_NAME]);
--DWH_Objects
MERGE [dbo].[DWH_Objects] AS TARGET
USING #tmp_Objects AS SOURCE
ON (
TARGET.[ObjectDatabase]= SOURCE.[ObjectDatabase]
AND TARGET.[ObjectSCHEMA]= SOURCE.[ObjectSCHEMA]
AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
AND TARGET.[ObjectName]= SOURCE.[ObjectName]
AND TARGET.[GueltigBis] = '01.01.2099')
WHEN MATCHED AND CONVERT(NVARCHAR(MAX), TARGET.[ObjectDefinition]) <> CONVERT(NVARCHAR(MAX), SOURCE.[ObjectDefinition] )
THEN UPDATE SET TARGET.[GueltigBis] = getdate()
WHEN NOT MATCHED BY SOURCE AND TARGET.[GueltigBis] = '01.01.2099' THEN UPDATE SET TARGET.[GueltigBis] = getdate()
WHEN NOT MATCHED BY TARGET
THEN INSERT ([ObjectDatabase]
,[ObjectSCHEMA]
,[ObjectTYPE]
,[ObjectName]
,[ObjectDefinition]
) VALUES (SOURCE.[ObjectDatabase]
,SOURCE.[ObjectSCHEMA]
,SOURCE.[ObjectTYPE]
,SOURCE.[ObjectName]
,SOURCE.[ObjectDefinition]
);
MERGE [dbo].[DWH_Objects] AS TARGET
USING #tmp_Objects AS SOURCE
ON (
TARGET.[ObjectDatabase]= SOURCE.[ObjectDatabase]
AND TARGET.[ObjectTYPE]= SOURCE.[ObjectTYPE]
AND TARGET.[ObjectSCHEMA]= SOURCE.[ObjectSCHEMA]
AND TARGET.[ObjectName]= SOURCE.[ObjectName]
AND TARGET.[GueltigBis] = '01.01.2099')
WHEN NOT MATCHED BY TARGET
THEN INSERT (
[ObjectDatabase]
,[ObjectSCHEMA]
,[ObjectTYPE]
,[ObjectName]
,[ObjectDefinition]
) VALUES (
SOURCE.[ObjectDatabase]
,SOURCE.[ObjectSCHEMA]
,SOURCE.[ObjectTYPE]
,SOURCE.[ObjectName]
,SOURCE.[ObjectDefinition]
);
CLOSE cMetaUpdate
DEALLOCATE cMetaUpdate
GOJetzt können wir mit folgenden SQL Code die Prozedur ausführen:
USE [Meta]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[MetaUpdate]
---da diese Prozedure ein Merge nutzt und bei diesem kein kombinierter update insert Statement funktioniert, führe ich die eigentliche Prozedure zwei mal aus :(
EXEC @return_value = [dbo].[MetaUpdate]
SELECT 'Return Value' = @return_value
GOAls nächstes benötigen wir eine Prozedur um täglich die Anzahl der Rows jeder Tabelle zu ermitteln. Diese Prozedur ist sehr Ressourcen schonend und läuft auch in großen DWH sehr schnell.
USE [META]
GO
/****** Object: StoredProcedure [dbo].[MetaSpaceRefresh] Script Date: 04.10.2023 21:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[MetaSpaceRefresh]
AS
--hier alles löschen was heute schon geladen wurde
Delete from [dbo].[DWH_SpaceInfo] WHERE [DateID] = CONVERT(int,Convert(varchar(10),getDate(),112));
Declare @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
Select @sql = N'', @src = N' UNION ALL
Select ''$d'' AS ''DBName'',
s.name COLLATE SQL_LAtin1_General_CP1_CI_AI AS ''Schema'',
t.name COLLATE SQL_LAtin1_General_CP1_CI_AI AS ''Table'',
ind.rows AS [RowCount],
(ind.used* 8) / 1024 AS SizeInMB,
t.Create_Date,
Convert(int,convert(varchar(10),getdate(),112)) AS DateID
From [$d].sys.schemas as S
inner Join [$d].sys.tables as t on s.[schema_id] = t.[schema_id]
inner join [$d].sys.sysindexes as ind on t.[object_id] = ind.[id]
where ind.indid < 2'; Select @sql = @sql + REPLACE(@src, '$d', name) from sys.databases where database_id>4
AND [state] =0
--auch hier können wir wieder Datenbanken ausschließen
AND name not in ('master', 'tempdb', 'model')
AND HAS_DBACCESS(Name) =1;
Set @sql= STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
EXEC('Insert into [META].[dbo].[DWH_SpaceInfo]' + @sql);
GOAuch diese Prozedur können wir wie folgt ausführen:
USE [META]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[MetaSpaceRefresh]
SELECT 'Return Value' = @return_value
GOWenn wir das ganze jetzt täglich automatisiert aktualisieren, erstellen wir uns hierfür einen SQL Server Agent Job mit diesem Code:
Achtung: Bitte die Ausführungszeit noch anpassen, aktuell ist der Job so eingestellt, dass dieser jeden Tag um 23:00 Uhr läuft.
USE [msdb]
GO
/****** Object: Job [DWH Meta aktualisieren] Script Date: 04.10.2023 21:29:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 04.10.2023 21:29:06 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DWH Meta aktualisieren',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'führt 2 Prozedurn aus um veränderungen an den Datenbanken zu versionieren und um Spaceinfos für jeden Tag weg zu schreiben.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Prozeduren ausführen] Script Date: 04.10.2023 21:29:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Prozeduren ausführen',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @RC int
EXECUTE @RC = [dbo].[MetaUpdate] ;
EXECUTE @RC = [dbo].[MetaSpaceRefresh]',
@database_name=N'META',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'täglicher Call',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20210502,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959,
@schedule_uid=N'3428681e-ab00-4010-aa77-1dad488faabd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GODarüber hinaus gibt es noch viele weitere nützliche SQL Abfragen für das Meta-Datenmanagement in einem Data Warehouse
Wie hat dir der Artikel gefallen?
Vielen Dank für dein Feedback!
Diese Seite wurde am: 4. Oktober 2023 veröffentlicht und zuletzt am: 31. Oktober 2023 aktualisiert.
