USE DW

/* Drop table dbo.dim_audit */
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[dim_audit]')
                    AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
   DROP TABLE dbo.[dim_audit]
GO
CREATE TABLE [dbo].[dim_audit](
	[audit_key] [int] IDENTITY(1,1) NOT NULL,
	[ParentAuditKey] [int] NOT NULL,
	[TableName] [varchar](50) NOT NULL,
	[PkgName] [varchar](100) NOT NULL,
	[PkgGUID] [uniqueidentifier] NULL,
	[PkgVersionGUID] [uniqueidentifier] NULL,
	[PkgVersionMajor] [smallint] NULL,
	[PkgVersionMinor] [smallint] NULL,
	[ExecStartDT] [datetime] NOT NULL,
	[ExecStopDT] [datetime] NULL,
	[ExecutionInstanceGUID] [uniqueidentifier] NULL,
	[ExtractRowCnt] [bigint] NULL,
	[InsertRowCnt] [bigint] NULL,
	[UpdateRowCnt] [bigint] NULL,
	[ErrorRowCnt] [bigint] NULL,
	[InferredMemberRowCnt] [bigint] NULL,
	[TableInitialRowCnt] [bigint] NULL,
	[TableFinalRowCnt] [bigint] NULL,
	[TableMaxDateTime] [datetime] NULL,
	[SuccessfulProcessingInd] [char](1) NOT NULL,
 CONSTRAINT [PK_dbo.dim_audit] PRIMARY KEY CLUSTERED
(
	[audit_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[dim_audit] ON

INSERT [dbo].[dim_audit] ([audit_key], [ParentAuditKey], [TableName], [PkgName], [PkgGUID], [PkgVersionGUID], [PkgVersionMajor], [PkgVersionMinor], [ExecStartDT], [ExecStopDT], [ExecutionInstanceGUID], [ExtractRowCnt], [InsertRowCnt], [UpdateRowCnt], [ErrorRowCnt],[InferredMemberRowCnt], [TableInitialRowCnt], [TableFinalRowCnt], [TableMaxDateTime], [SuccessfulProcessingInd]) VALUES (-1, -1, N'Not Applicable', N'Initial Load', NULL, NULL, 0, 0, getdate(), getdate(), NULL, 0, 0, 0, 0, 0, 0, 0, getdate(), N' ')
SET IDENTITY_INSERT [dbo].[dim_audit] OFF
ALTER TABLE [dbo].[dim_audit] ADD  DEFAULT ('Unknown') FOR [TableName]
GO
ALTER TABLE [dbo].[dim_audit] ADD  DEFAULT ('Unknown') FOR [PkgName]
GO
ALTER TABLE [dbo].[dim_audit] ADD  DEFAULT (getdate()) FOR [ExecStartDT]
GO
ALTER TABLE [dbo].[dim_audit] ADD  DEFAULT ('N') FOR [SuccessfulProcessingInd]
GO

ALTER TABLE [dbo].[dim_audit]  WITH CHECK ADD  CONSTRAINT [FK_dbo_dim_audit_ParentAuditKey] FOREIGN KEY([ParentAuditKey])
REFERENCES [dbo].[dim_audit] ([audit_key])
GO
ALTER TABLE [dbo].[dim_audit] CHECK CONSTRAINT [FK_dbo_dim_audit_ParentAuditKey]
GO