macmle45/DataWarehouse

University projects

DataWarehouse

University projects

PROJECT 1

Used Datasets:

• Bank Marketing DataSet
• Dow Jones Index DataSet
• Wholesale customers DataSet

SQL statements

• create database

USE [master]
GO

IF DB_ID('BusinessDataDB') IS NOT NULL DROP DATABASE BusinessDataDB
GO

CREATE DATABASE [BusinessDataDB]
GO

• create table for logging

USE [BusinessDataDB]
GO

CREATE TABLE [LoggingData] 
	(
	  [DateOfExectPackg] DATETIME
	 ,[ExectPackgName] VARCHAR (50)
	 ,[UserExectPackg] VARCHAR (50)
	);

PROJECT 2

Used Datasets:

• WizyWydane.csv

SQL statements

• create database

USE [master]
GO

IF DB_ID('VisaDB') IS NOT NULL DROP DATABASE VisaDB
GO

CREATE DATABASE [VisaDB]
GO

PROJECT 3

Used Datasets:

• Obszar.csv
• Pracownik.csv
• Sklep.csv

SQL statements

• create database for DimStore

USE [master]
GO

IF DB_ID('StoreDB') IS NOT NULL DROP DATABASE StoreDB
GO

CREATE DATABASE [StoreDB]
GO

• create table [DimStoreReload]

USE [StoreDB]
GO

DROP TABLE IF EXISTS [dbo].[DimStoreReload];

CREATE TABLE [DimStoreReload] (
    [StoreKey] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [FirstName] varchar(50),
    [LastName] varchar(50),
    [Title] varchar(50),
    [ContinentName] varchar(50),
    [CityName] varchar(50),
    [StateProvinceName] varchar(50),
    [RegionCountryName] varchar(50),
    [StoreBusinessId] int,
    [StoreName] varchar(50),
    [StoreDescription] varchar(50),
    [Status] varchar(50),
	[LoadDate]  datetime NULL DEFAULT (GETDATE()),
	[LoadUser] varchar (100) NULL DEFAULT (SUSER_SNAME())
)

• create table [DimStoreSCD2]

USE [StoreDB]
GO

DROP TABLE IF EXISTS [dbo].[DimStoreSCD2];

CREATE TABLE [dbo].[DimStoreSCD2] (
	[StoreKey] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [FirstName] varchar(50),
    [LastName] varchar(50),
    [Title] varchar(50),
    [ContinentName] varchar(50),
    [CityName] varchar(50),
    [StateProvinceName] varchar(50),
    [RegionCountryName] varchar(50),
    [StoreBusinessId] int,
    [StoreName] varchar(50),
    [StoreDescription] varchar(50),
    [Status] varchar(50),
	[DateFrom] datetime NULL,
	[DateTo] datetime NULL	DEFAULT (NULL),
	[Tech_LoadDate] datetime NULL DEFAULT (GETDATE()),
	[Tech_LoadUser] varchar(100) NULL DEFAULT (SUSER_SNAME())
)

• create table [LoadLogger]

USE [StoreDB]
GO

DROP TABLE IF EXISTS [dbo].[LoadLogger];

CREATE TABLE [dbo].[LoadLogger]
(
	[LogId]			INT IDENTITY	NOT NULL PRIMARY KEY
,	[LogDate]		DATETIME		NOT NULL DEFAULT GETDATE()
,	[User]			VARCHAR(200)	NOT NULL DEFAULT SUSER_SNAME()
,	[Host]			VARCHAR(200)	NOT NULL DEFAULT HOST_NAME()
,	[RCAll]			INT NOT NULL
,	[RCInsert]		INT NOT NULL
,	[RCUpdate]		INT NOT NULL
,	[RCDelete]		INT NOT NULL
,	[RCNoAction]	INT NOT NULL
,	[ISRCSumCorrect] AS CASE WHEN [RCAll] = [RCInsert] + [RCUpdate] + [RCDelete] + [RCNoAction] THEN 1 ELSE 0 END 
)