131 lines
3.8 KiB
Transact-SQL
Executable File
131 lines
3.8 KiB
Transact-SQL
Executable File
-- Target Database Initialization Script
|
|
-- This creates a similar structure with some intentional differences for testing
|
|
|
|
USE master;
|
|
GO
|
|
|
|
-- Create test database
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'TestDB_Target')
|
|
BEGIN
|
|
CREATE DATABASE TestDB_Target;
|
|
END
|
|
GO
|
|
|
|
USE TestDB_Target;
|
|
GO
|
|
|
|
-- Create sample tables (similar to baseline with some differences)
|
|
|
|
-- Dimension: Customers (same structure)
|
|
CREATE TABLE dbo.DimCustomer (
|
|
CustomerID INT PRIMARY KEY IDENTITY(1,1),
|
|
CustomerName NVARCHAR(100) NOT NULL,
|
|
Email NVARCHAR(100),
|
|
City NVARCHAR(50),
|
|
Country NVARCHAR(50),
|
|
CreatedDate DATETIME DEFAULT GETDATE()
|
|
);
|
|
|
|
-- Dimension: Products (slightly different - added column)
|
|
CREATE TABLE dbo.DimProduct (
|
|
ProductID INT PRIMARY KEY IDENTITY(1,1),
|
|
ProductName NVARCHAR(100) NOT NULL,
|
|
Category NVARCHAR(50),
|
|
UnitPrice DECIMAL(10,2),
|
|
IsActive BIT DEFAULT 1,
|
|
LastModified DATETIME DEFAULT GETDATE() -- Extra column for testing
|
|
);
|
|
|
|
-- Fact: Sales (same structure)
|
|
CREATE TABLE dbo.FactSales (
|
|
SaleID INT PRIMARY KEY IDENTITY(1,1),
|
|
CustomerID INT,
|
|
ProductID INT,
|
|
SaleDate DATE,
|
|
Quantity INT,
|
|
UnitPrice DECIMAL(10,2),
|
|
TotalAmount DECIMAL(10,2),
|
|
TaxAmount DECIMAL(10,2),
|
|
FOREIGN KEY (CustomerID) REFERENCES dbo.DimCustomer(CustomerID),
|
|
FOREIGN KEY (ProductID) REFERENCES dbo.DimProduct(ProductID)
|
|
);
|
|
|
|
-- Insert sample data (TEST DATA ONLY - NOT REAL CUSTOMERS)
|
|
|
|
-- Customers
|
|
INSERT INTO dbo.DimCustomer (CustomerName, Email, City, Country) VALUES
|
|
('TestCustomer1', 'test1@test.local', 'City1', 'Country1'),
|
|
('TestCustomer2', 'test2@test.local', 'City2', 'Country2'),
|
|
('TestCustomer3', 'test3@test.local', 'City3', 'Country3'),
|
|
('TestCustomer4', 'test4@test.local', 'City4', 'Country4'),
|
|
('TestCustomer5', 'test5@test.local', 'City5', 'Country5');
|
|
|
|
-- Products (with LastModified)
|
|
INSERT INTO dbo.DimProduct (ProductName, Category, UnitPrice, IsActive, LastModified) VALUES
|
|
('Laptop', 'Electronics', 999.99, 1, GETDATE()),
|
|
('Mouse', 'Electronics', 29.99, 1, GETDATE()),
|
|
('Keyboard', 'Electronics', 79.99, 1, GETDATE()),
|
|
('Monitor', 'Electronics', 299.99, 1, GETDATE()),
|
|
('Desk Chair', 'Furniture', 199.99, 1, GETDATE()),
|
|
('Desk', 'Furniture', 399.99, 1, GETDATE()),
|
|
('Notebook', 'Stationery', 4.99, 1, GETDATE()),
|
|
('Pen Set', 'Stationery', 12.99, 1, GETDATE());
|
|
|
|
-- Sales (95 records - 5 fewer than baseline for testing)
|
|
DECLARE @i INT = 1;
|
|
WHILE @i <= 95
|
|
BEGIN
|
|
INSERT INTO dbo.FactSales (CustomerID, ProductID, SaleDate, Quantity, UnitPrice, TotalAmount, TaxAmount)
|
|
VALUES (
|
|
(ABS(CHECKSUM(NEWID())) % 5) + 1,
|
|
(ABS(CHECKSUM(NEWID())) % 8) + 1,
|
|
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
|
|
(ABS(CHECKSUM(NEWID())) % 10) + 1,
|
|
(ABS(CHECKSUM(NEWID())) % 900) + 100.00,
|
|
0,
|
|
0
|
|
);
|
|
|
|
-- Calculate amounts
|
|
UPDATE dbo.FactSales
|
|
SET TotalAmount = Quantity * UnitPrice,
|
|
TaxAmount = Quantity * UnitPrice * 0.1
|
|
WHERE SaleID = @i;
|
|
|
|
SET @i = @i + 1;
|
|
END
|
|
GO
|
|
|
|
-- Create the same view
|
|
CREATE VIEW dbo.vw_SalesSummary AS
|
|
SELECT
|
|
c.CustomerName,
|
|
p.ProductName,
|
|
s.SaleDate,
|
|
s.Quantity,
|
|
s.TotalAmount
|
|
FROM dbo.FactSales s
|
|
JOIN dbo.DimCustomer c ON s.CustomerID = c.CustomerID
|
|
JOIN dbo.DimProduct p ON s.ProductID = p.ProductID;
|
|
GO
|
|
|
|
-- Create an extra table that doesn't exist in baseline
|
|
CREATE TABLE dbo.TempProcessing (
|
|
ProcessID INT PRIMARY KEY IDENTITY(1,1),
|
|
ProcessName NVARCHAR(100),
|
|
Status NVARCHAR(20),
|
|
CreatedDate DATETIME DEFAULT GETDATE()
|
|
);
|
|
|
|
INSERT INTO dbo.TempProcessing (ProcessName, Status) VALUES
|
|
('DataLoad', 'Completed'),
|
|
('Validation', 'In Progress');
|
|
GO
|
|
|
|
-- Create statistics
|
|
CREATE STATISTICS stat_sales_date ON dbo.FactSales(SaleDate);
|
|
CREATE STATISTICS stat_customer_country ON dbo.DimCustomer(Country);
|
|
GO
|
|
|
|
PRINT 'Target database initialized successfully';
|
|
GO |