This is the second blogpost on the creation of a Meta Data Driven SSIS Solution using Biml. In this post we are settings the stage. We are going to create a simple Kimball based data warehouse with 3 dimensions:
- dimCustomer
- dimEmployee
- dimProduct
and one fact table: factOrder.
Furthermore we'll implement a staging area and a meta database.
1. Create the databases:
USE [master]
GO
CREATE DATABASE [MyDwh]
GO
CREATE DATABASE [MyDwh_st]
GO
CREATE DATABASE [MyDwh_meta]
GO
2. Create the staging tables:
USE [MyDwh_st]
GO
CREATE TABLE [dbo].[Northwind_Suppliers](
[SupplierID] [int] NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_Products](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](40) NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_Orders](
[OrderID] [int] NOT NULL,
[CustomerID] [nchar](5) NULL,
[EmployeeID] [int] NULL,
[OrderDate] [datetime] NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_OrderDetails](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[Quantity] [smallint] NOT NULL,
[Discount] [real] NOT NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_Employees](
[EmployeeID] [int] NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[ReportsTo] [int] NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[City] [nvarchar](15) NULL,
[Country] [nvarchar](15) NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
CREATE TABLE [dbo].[Northwind_Categories](
[CategoryID] [int] NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[InsertAuditID] [int] NULL,
[UpdateAuditID] [int] NULL,
[RowStartDate] [datetime] NULL,
[RowEndDate] [datetime] NULL);
3. Create the dimension and fact tables
USE [MyDwh]
GO
CREATE TABLE dimCustomer (
CustomerKey INT IDENTITY(1,1) NOT NULL
,CustomerID NCHAR(5) NOT NULL
,CustomerName NVARCHAR(40)
,City NVARCHAR(15)
,Country NVARCHAR(15)
,InsertAuditID INT
,UpdateAuditID INT
,RowStartDate DATETIME
,RowEndDate DATETIME);
GO
CREATE TABLE dimEmployee (
EmployeeKey INT IDENTITY(1,1) NOT NULL
,EmployeeID INT NOT NULL
,EmployeeName NVARCHAR(50)
,Title NVARCHAR(30)
,Manager NVARCHAR(50)
,InsertAuditID INT
,UpdateAuditID INT
,RowStartDate DATETIME
,RowEndDate DATETIME);
GO
CREATE TABLE dimProduct (
ProductKey INT IDENTITY(1,1) NOT NULL
,ProductID INT NOT NULL
,ProductName NVARCHAR(40)
,Category NVARCHAR(15)
,Supplier NVARCHAR(40)
,InsertAuditID INT
,UpdateAuditID INT
,RowStartDate DATETIME
,RowEndDate DATETIME);
GO
CREATE TABLE factOrder (
OrderDateKey INT NOT NULL
,CustomerKey INT NOT NULL
,EmployeeKey INT NOT NULL
,ProductKey INT NOT NULL
,OrderQuantity INT
,OrderAmount money
,DiscountAmount money
,InsertAuditID INT
,UpdateAuditID INT
,RowStartDate DATETIME
,RowEndDate DATETIME);
GO
4. Create the views for the dimensions and fact tables
USE [MyDwh]
GO
CREATE VIEW [dbo].[vwDimCustomer] AS
SELECT [CustomerID]
,[CompanyName] AS CustomerName
,[City]
,[Country]
FROM MyDwh_st.dbo.Northwind_Customers;
GO
CREATE VIEW vwDimProduct AS
SELECT pr.ProductID
, pr.ProductName
, ca.CategoryName AS Category
, su.CompanyName AS Supplier
FROM MyDwh_st.dbo.Northwind_Categories AS ca
INNER JOIN MyDwh_st.dbo.Northwind_Products AS pr ON ca.CategoryID = pr.CategoryID
INNER JOIN MyDwh_st.dbo.Northwind_Suppliers AS su ON pr.SupplierID = su.SupplierID;
GO
CREATE VIEW vwDimEmployee AS
SELECT em.[EmployeeID]
,em.[FirstName] + ' ' + em.[LastName] AS EmployeeName
,em.Title
,ma.[FirstName] + ' ' + ma.[LastName] AS Manager
FROM MyDwh_st.dbo.Northwind_Employees em
LEFT OUTER JOIN MyDwh_st.dbo.Northwind_Employees ma ON ma.[EmployeeID]=em.[ReportsTo];
GO
CREATE VIEW vwFactOrders AS
SELECT OrderDateKey=YEAR(o.OrderDate)*10000+MONTH(o.OrderDate)*100+DAY(o.OrderDate)
, CustomerKey=ISNULL(cu.CustomerKey,-1)
, EmployeeKey=ISNULL(em.EmployeeKey,-1)
, ProductKey=ISNULL(pr.ProductKey,-1)
, OrderQuantity=od.Quantity
, OrderAmount=od.Quantity * od.UnitPrice
, DiscountAmount=CONVERT(money, od.Quantity * od.UnitPrice * od.Discount)
FROM MyDwh_st.dbo.[Northwind_OrderDetails] od
INNER JOIN MyDwh_st.dbo.Northwind_Orders o ON o.OrderID = od.OrderID
LEFT OUTER JOIN dbo.dimCustomer cu ON cu.CustomerID=o.CustomerID AND cu.RowEndDate>o.OrderDate
LEFT OUTER JOIN dbo.dimEmployee em ON em.EmployeeID=o.EmployeeID AND em.RowEndDate>o.OrderDate
LEFT OUTER JOIN dbo.dimProduct pr ON pr.ProductID=od.ProductID AND pr.RowEndDate>o.OrderDate;
GO
5. Create the objects for the meta database
USE [MyDwh_meta]
GO
CREATE TABLE [dbo].[SsisPackages](
[PackageID] [int] IDENTITY(1,1) NOT NULL,
[PackageType] [int] NULL,
[PackageName] [nvarchar](50) NULL,
[PackageGroup] [nvarchar](50) NULL,
[SourceInput] [nvarchar](MAX) NULL,
[SourceConnection] [nvarchar](50) NULL,
[DestinationName] [nvarchar](50) NULL,
[DestinationConnection] [nvarchar](50) NULL,
[ErrorFile] [nvarchar](MAX) NULL,
[TruncateDestination] [bit] NULL,
CONSTRAINT [PK_ssisPackages] PRIMARY KEY CLUSTERED
( [PackageID] ASC));
GO
CREATE TABLE [dbo].[SsisConfiguration](
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](1000) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL);
GO
CREATE TABLE [dbo].[SsisAudit](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[PackageID] [int] NOT NULL,
[PackageGUID] [uniqueidentifier] NULL,
[PackageVersionGUID] [uniqueidentifier] NULL,
[ExecutionID] [uniqueidentifier] NULL,
[PackageStartDate] [datetime] NULL,
[PackageEnddate] [datetime] NULL,
[ExtractRowCount] [int] NULL,
[InsertRowCount] [int] NULL,
[UpdateRowCount] [int] NULL,
[ErrorRowCount] [int] NULL,
[TableInitialRowCount] [int] NULL,
[TableFinalRowCount] [int] NULL,
[IsProcessed] [bit] NULL,
CONSTRAINT [PK_SsisAudit] PRIMARY KEY CLUSTERED
( [AuditID] ASC));
GO
CREATE PROCEDURE [dbo].[uspUpdateAuditRow]
@AuditID INT
,@ExtractRowCount INT
,@InsertRowCount INT
,@UpdateRowCount INT
,@ErrorRowCount INT
,@TableFinalRowCount INT
AS
UPDATE [dbo].[SsisAudit]
SET [PackageEnddate]=SYSDATETIME()
,[ExtractRowCount]=@ExtractRowCount
,[InsertRowCount]=@InsertRowCount
,[UpdateRowCount]=@UpdateRowCount
,[ErrorRowCount]=@ErrorRowCount
,[TableFinalRowCount]=@TableFinalRowCount
,[IsProcessed]=1
WHERE [AuditID]=@AuditID;
GO
CREATE PROCEDURE [dbo].[uspNewAuditRow]
@PackageID INT
,@PackageGUID UNIQUEIDENTIFIER
,@PackageVersionGUID UNIQUEIDENTIFIER
,@ExecutionID UNIQUEIDENTIFIER
,@PackageStartDate DATETIME
,@TableInitialRowCount INT
AS
INSERT INTO [dbo].[SsisAudit]
([PackageID]
,[PackageGUID]
,[PackageVersionGUID]
,[ExecutionID]
,[PackageStartDate]
,[TableInitialRowCount]
,[IsProcessed])
VALUES
(@PackageID
,@PackageGUID
,@PackageVersionGUID
,@ExecutionID
,@PackageStartDate
,@TableInitialRowCount
,0);
SELECT CAST(SCOPE_IDENTITY() AS INT) AS AuditID
GO
6. Create an environment variable to store the connection
I'll use package configurations to make it easier to deploy the solution to different servers (production, test, development) These package configuration will hold the information for the several connections. The connection to the meta database is stored in an environment variable. The other package configurations will be stored in the SsisConfigurations table in the meta database.
So create an environment variable with the name Northwind_Config and value Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;
Windows Explorer - right click the (my)computer node - select Properties - under tab Advanced click the buttton Environment Variables - under System variables click New.
