Basic data warehouse schema (SQLite code)

DefaultDatawarehouseSchema.sql

DROP INDEX IF EXISTS IDX_DimTimeZone_Conversion;
DROP TABLE IF EXISTS DimTimeZone;
CREATE TABLE DimTimeZone(
  TimeZoneKey INT PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Description VARCHAR(150) NOT NULL,
	StandardConversion INT NOT  NULL DEFAULT 0,
	DSTConversion INT NOT NULL DEFAULT 0,
	ObservesDST TINYINT NOT NULL DEFAULT 0
);
CREATE INDEX IDX_DimTimeZone_Conversion ON DimTimeZones(Name, StandardConversion, DSTConversion, ObservesDST);

DROP INDEX IF EXISTS IDX_DimTime_Hour;
DROP TABLE IF EXISTS DimTime;
CREATE TABLE DimTime(
	TimeKey INT PRIMARY KEY,
	Hour TINYINT NOT NULL CHECK(Hour >= 0 AND Hour < 24),
	Minute TINYINT NOT NULL CHECK(Minute >= 0 AND Minute < 60)
);
CREATE INDEX IDX_DimTime_Hour ON DimTime(Hour);

DROP TABLE IF EXISTS DimHoliday;
CREATE TABLE DimHoliday(
	HolidayKey TINYINT PRIMARY KEY,
	Name VARCHAR(50) NOT  NULL,
	Description VARCHAR(150) NOT NULL
);
INSERT INTO DimHoliday (Id, Name, Description) VALUES (0, 'None', 'Not a Holiday');

DROP INDEX IF EXISTS IDX_DimDate_Calendar;
DROP INDEX IF EXISTS IDX_DimDate_Fiscal;
DROP INDEX IF EXISTS IDX_DimDate_Weeks;
DROP INDEX IF EXISTS IDX_DimDate_Attributes;
DROP TABLE IF EXISTS DimDate;
CREATE TABLE DimDate(
	DateKey INT PRIMARY KEY,
	CalendarYear SMALLINT NOT NULL CHECK(CalendarYear > 1970 AND CalendarYear < 10000),
	CalendarQuarter TINYINT NOT NULL CHECK(CalendarQuarter IN (1, 2, 3 , 4)),
	FiscalYear SMALLINT NOT NULL CHECK(FiscalYear > 1970 AND FiscalYear < 10000),
	FiscalQuarter SMALLINT NOT NULL CHECK(FiscalQuarter IN (1, 2, 3 , 4)),
	QuarterName VARCHAR(15) NOT NULL,
	MonthOfYear TINYINT NOT NULL CHECK(MonthOfYear > 0 AND MonthOfYear < 13),
	MonthName VARCHAR(10) NOT NULL,
	WeekOfYear TINYINT NOT NULL CHECK(WeekOfYear > 0 AND WeekOfYear < 54),
	DayOfMonth TINYINT NOT NULL CHECK(DayOfMonth > 0 AND DayOfMonth < 32),
	DayOfWeek TINYINT NOT NULL CHECK(DayOfWeek > 0 AND DayOfWeek < 8),
	DayOfYear SMALLINT NOT NULL CHECK(DayOfYear > 0 AND DayOfYear < 367),
	DayName VARCHAR(10) NOT NULL,
	IsLastDayOfMonth TINYINT NOT NULL DEFAULT 0,
	IsWorkday TINYINT NOT NULL DEFAULT 1,
	IsHoliday TINYINT NOT NULL DEFAULT 0,
	IsDST TINYINT NOT NULL DEFAULT 0,
	FOREIGN KEY(IsHoliday) REFERENCES DimHoliday(HolidayKey)
);
CREATE INDEX IDX_DimDate_Calendar ON DimDate(CalendarYear, CalendarQuarter, MonthOfYear);
CREATE INDEX IDX_DimDate_Fiscal ON DimDate(FiscalYear, FiscalQuarter, MonthOfYear);
CREATE INDEX IDX_DimDate_Weeks ON DimDate(CalendarYear, WeekOfYear, DayOfWeek);
CREATE INDEX IDX_DimDate_Attributes ON DimDate(IsWorkday, IsLastDayOfMonth, IsHoliday, IsDST);

DROP INDEX IF EXISTS IDX_DimGeography_Countries;
DROP INDEX IF EXISTS IDX_DimGeography_StateProvinces;
DROP INDEX IF EXISTS IDX_DimGeography_Cities;
DROP TABLE IF EXISTS DimGeography;
CREATE TABLE DimGeography (
	GeographyKey INT PRIMARY KEY,
	CountryRegionCode NVARCHAR(3) NOT NULL,
	CountryRegionName NVARCHAR(50) NOT NULL,
	StateProvinceCode NVARCHAR(3),
	StateProvinceName NVARCHAR(50),
	City NVARCHAR(50),
	PostalCode NVARCHAR(15),
	DateStart DATETIME NOT NULL,
	DateEnd DATETIME
);
CREATE INDEX IDX_DimGeography_Countries ON DimGeography (CountryRegionCode, CountryName, DateEnd);
CREATE INDEX IDX_DimGeography_StateProvinces ON DimGeography (StateProvinceCode, StateProvinceName, DateEnd);
CREATE INDEX IDX_DimGeography_Cities ON DimGeography (StateProvinceCode, City, PostalCode, DateEnd);

--Fact Tables
--FactSales
--FactPurchases
--FactCashReceipts
--FactCashDisbursements
--FactFinance
--  DateKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey, Amount
--Other Dimension Tables
--DimAccount
--  Id, ParentId, AccountCodeAlternateKey, ParentAccountCodeAlternateKey, AccountDescription, 
--  AccountType(NULL, Assets, Balances, Expenditures, Flow, Liabilities, Revenue, Equity, Statistical)
--  ValueType (Currency, Units)
--  Top Accounts with Null Parents (Balance Sheet, Income Statement)
--DimOrganization
--  Id, ParentId, Name(50), DateStart, DateEnd
--DimDepartmentGroup
--	Id, ParentId, Name(50), DateStart, DateEnd
--DimTeam
--	Id, OrganizationId, DepartmentGroupId, Name, Description, DateStart, DateEnd
--DimTaxTerritory
--	Id, GeographyId, Code, Rate, Description, DateStart, DateEnd
--DimScenario
--	ScenarioKey, ScenarioName(50) [Forecast, Budget, Actual]

Few typos on the above vanilla run. Which is corrected below :)

DROP INDEX IF EXISTS IDX_DimTimeZone_Conversion;
DROP TABLE IF EXISTS DimTimeZone;
CREATE TABLE DimTimeZone(
  TimeZoneKey INT PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Description VARCHAR(150) NOT NULL,
	StandardConversion INT NOT  NULL DEFAULT 0,
	DSTConversion INT NOT NULL DEFAULT 0,
	ObservesDST TINYINT NOT NULL DEFAULT 0
);
CREATE INDEX IDX_DimTimeZone_Conversion ON DimTimeZone(Name, StandardConversion, DSTConversion, ObservesDST);

DROP INDEX IF EXISTS IDX_DimTime_Hour;
DROP TABLE IF EXISTS DimTime;
CREATE TABLE DimTime(
	TimeKey INT PRIMARY KEY,
	Hour TINYINT NOT NULL CHECK(Hour >= 0 AND Hour < 24),
	Minute TINYINT NOT NULL CHECK(Minute >= 0 AND Minute < 60)
);
CREATE INDEX IDX_DimTime_Hour ON DimTime(Hour);

DROP TABLE IF EXISTS DimHoliday;
CREATE TABLE DimHoliday(
	HolidayKey TINYINT PRIMARY KEY,
	Name VARCHAR(50) NOT  NULL,
	Description VARCHAR(150) NOT NULL
);
INSERT INTO DimHoliday (HolidayKey, Name, Description) VALUES (0, 'None', 'Not a Holiday');

DROP INDEX IF EXISTS IDX_DimDate_Calendar;
DROP INDEX IF EXISTS IDX_DimDate_Fiscal;
DROP INDEX IF EXISTS IDX_DimDate_Weeks;
DROP INDEX IF EXISTS IDX_DimDate_Attributes;
DROP TABLE IF EXISTS DimDate;
CREATE TABLE DimDate(
	DateKey INT PRIMARY KEY,
	CalendarYear SMALLINT NOT NULL CHECK(CalendarYear > 1970 AND CalendarYear < 10000),
	CalendarQuarter TINYINT NOT NULL CHECK(CalendarQuarter IN (1, 2, 3 , 4)),
	FiscalYear SMALLINT NOT NULL CHECK(FiscalYear > 1970 AND FiscalYear < 10000),
	FiscalQuarter SMALLINT NOT NULL CHECK(FiscalQuarter IN (1, 2, 3 , 4)),
	QuarterName VARCHAR(15) NOT NULL,
	MonthOfYear TINYINT NOT NULL CHECK(MonthOfYear > 0 AND MonthOfYear < 13),
	MonthName VARCHAR(10) NOT NULL,
	WeekOfYear TINYINT NOT NULL CHECK(WeekOfYear > 0 AND WeekOfYear < 54),
	DayOfMonth TINYINT NOT NULL CHECK(DayOfMonth > 0 AND DayOfMonth < 32),
	DayOfWeek TINYINT NOT NULL CHECK(DayOfWeek > 0 AND DayOfWeek < 8),
	DayOfYear SMALLINT NOT NULL CHECK(DayOfYear > 0 AND DayOfYear < 367),
	DayName VARCHAR(10) NOT NULL,
	IsLastDayOfMonth TINYINT NOT NULL DEFAULT 0,
	IsWorkday TINYINT NOT NULL DEFAULT 1,
	IsHoliday TINYINT NOT NULL DEFAULT 0,
	IsDST TINYINT NOT NULL DEFAULT 0,
	FOREIGN KEY(IsHoliday) REFERENCES DimHoliday(HolidayKey)
);
CREATE INDEX IDX_DimDate_Calendar ON DimDate(CalendarYear, CalendarQuarter, MonthOfYear);
CREATE INDEX IDX_DimDate_Fiscal ON DimDate(FiscalYear, FiscalQuarter, MonthOfYear);
CREATE INDEX IDX_DimDate_Weeks ON DimDate(CalendarYear, WeekOfYear, DayOfWeek);
CREATE INDEX IDX_DimDate_Attributes ON DimDate(IsWorkday, IsLastDayOfMonth, IsHoliday, IsDST);

DROP INDEX IF EXISTS IDX_DimGeography_Countries;
DROP INDEX IF EXISTS IDX_DimGeography_StateProvinces;
DROP INDEX IF EXISTS IDX_DimGeography_Cities;
DROP TABLE IF EXISTS DimGeography;
CREATE TABLE DimGeography (
	GeographyKey INT PRIMARY KEY,
	CountryRegionCode NVARCHAR(3) NOT NULL,
	CountryRegionName NVARCHAR(50) NOT NULL,
	StateProvinceCode NVARCHAR(3),
	StateProvinceName NVARCHAR(50),
	City NVARCHAR(50),
	PostalCode NVARCHAR(15),
	DateStart DATETIME NOT NULL,
	DateEnd DATETIME
);
CREATE INDEX IDX_DimGeography_Countries ON DimGeography (CountryRegionCode, CountryRegionName, DateEnd);
CREATE INDEX IDX_DimGeography_StateProvinces ON DimGeography (StateProvinceCode, StateProvinceName, DateEnd);
CREATE INDEX IDX_DimGeography_Cities ON DimGeography (StateProvinceCode, City, PostalCode, DateEnd);