• Home › Blog › Creating an online analytical processing (OLAP) cube on top of a data mart relational database

Creating an online analytical processing (OLAP) cube on top of a data mart relational database

I wanted to perform multidimensional analysis on the information in my personal finance data mart. In order to achieve that, I needed to define an OLAP cube on top of that database, which contained the following tables:

I right-clicked the Finances database, selected Tasks, and clicked Generate Scripts...:

The Generate Scripts Wizard appeared. I clicked Next:

From the Choose Options window, I left Script entire database and all database objects checked and clicked Next:

From the Set Scripting Options window, I selected Save as script file and clicked Advanced:

From the Advanced Scripting Options window, for Types of data to script I changed the value from Schema only to Schema and data. Then I clicked OK:

I clicked Next:

The Summary window appeared. I clicked Next:

The Save Scripts window appeared. I clicked Finish:

I found the script in the location I specified, saved as C:\Users\jmont\Documents\script.sql and having the content shown below:

USE [master]
GO
/****** Object:  Database [Finances]    Script Date: 2/23/2024 10:00:14 AM ******/
CREATE DATABASE [Finances]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Finances', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Finances.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Finances_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Finances_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [Finances] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Finances].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Finances] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Finances] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Finances] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Finances] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Finances] SET ARITHABORT OFF 
GO
ALTER DATABASE [Finances] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Finances] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Finances] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Finances] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Finances] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Finances] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Finances] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Finances] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Finances] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Finances] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Finances] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Finances] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Finances] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Finances] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Finances] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Finances] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Finances] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Finances] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [Finances] SET  MULTI_USER 
GO
ALTER DATABASE [Finances] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Finances] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Finances] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Finances] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Finances] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Finances] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'Finances', N'ON'
GO
ALTER DATABASE [Finances] SET QUERY_STORE = OFF
GO
USE [Finances]
GO
/****** Object:  Table [dbo].[DimBank]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimBank](
	[BankID] [int] NOT NULL,
	[BankName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DimCardBank] PRIMARY KEY CLUSTERED 
(
	[BankID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimBuyer]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimBuyer](
	[BuyerID] [int] NOT NULL,
	[BuyerName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DimBuyer] PRIMARY KEY CLUSTERED 
(
	[BuyerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCard]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCard](
	[CardID] [int] NOT NULL,
	[CardNumber] [varchar](19) NOT NULL,
	[CardTypeID] [int] NOT NULL,
	[BankID] [int] NOT NULL,
	[CardBrandID] [int] NOT NULL,
 CONSTRAINT [PK_DimCard] PRIMARY KEY CLUSTERED 
(
	[CardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCardBrand]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCardBrand](
	[CardBrandID] [int] NOT NULL,
	[CardBrandName] [varchar](10) NOT NULL,
 CONSTRAINT [PK_DimCardBrand] PRIMARY KEY CLUSTERED 
(
	[CardBrandID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCardType]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCardType](
	[CardTypeID] [int] NOT NULL,
	[CardTypeName] [varchar](7) NOT NULL,
 CONSTRAINT [PK_DimCardType] PRIMARY KEY CLUSTERED 
(
	[CardTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCategory]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCategory](
	[CategoryID] [int] NOT NULL,
	[ParentCategoryID] [int] NOT NULL,
	[CategoryName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DimCategory] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCity]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCity](
	[CityID] [int] NOT NULL,
	[CityName] [varchar](50) NOT NULL,
	[ProvinceID] [int] NOT NULL,
 CONSTRAINT [PK_DimCity] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimCountry]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimCountry](
	[CountryID] [int] NOT NULL,
	[CountryName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_DimCountry] PRIMARY KEY CLUSTERED 
(
	[CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimDay]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimDay](
	[DayID] [varchar](8) NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NOT NULL,
	[DayOfWeek] [tinyint] NOT NULL,
	[CalendarMonth] [smallint] NOT NULL,
 CONSTRAINT [PK_DimDay] PRIMARY KEY CLUSTERED 
(
	[DayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimParentCategory]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimParentCategory](
	[ParentCategoryID] [int] NOT NULL,
	[ParentCategoryName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DimParentCategory] PRIMARY KEY CLUSTERED 
(
	[ParentCategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimPaymentMethod]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimPaymentMethod](
	[PaymentMethodID] [int] NOT NULL,
	[PaymentMethodName] [varchar](50) NOT NULL,
	[CardID] [int] NOT NULL,
 CONSTRAINT [PK_DimPaymentMethod] PRIMARY KEY CLUSTERED 
(
	[PaymentMethodID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimProvince]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimProvince](
	[ProvinceID] [int] NOT NULL,
	[ProvinceName] [varchar](25) NOT NULL,
	[CountryID] [int] NOT NULL,
 CONSTRAINT [PK_DimProvince] PRIMARY KEY CLUSTERED 
(
	[ProvinceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DimSeller]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimSeller](
	[SellerID] [int] NOT NULL,
	[SellerName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DimSeller] PRIMARY KEY CLUSTERED 
(
	[SellerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactPurchases]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactPurchases](
	[TotalPurchases] [money] NOT NULL,
	[Description] [varchar](1000) NOT NULL,
	[CityID] [int] NOT NULL,
	[DayID] [varchar](8) NOT NULL,
	[PaymentMethodID] [int] NOT NULL,
	[SellerID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[CategoryDeduplicate] [int] NOT NULL,
 CONSTRAINT [PK_FactPurchases] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC,
	[DayID] ASC,
	[PaymentMethodID] ASC,
	[SellerID] ASC,
	[CategoryID] ASC,
	[CategoryDeduplicate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactPurchasesXDimCategory]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactPurchasesXDimCategory](
	[CityID] [int] NOT NULL,
	[DayID] [varchar](8) NOT NULL,
	[PaymentMethodID] [int] NOT NULL,
	[SellerID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[CategoryDeduplicate] [int] NOT NULL,
 CONSTRAINT [PK_FactPurchasesXDimCategory] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC,
	[DayID] ASC,
	[PaymentMethodID] ASC,
	[SellerID] ASC,
	[CategoryID] ASC,
	[CategoryDeduplicate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactSales]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactSales](
	[TotalSales] [money] NOT NULL,
	[Description] [varchar](1000) NOT NULL,
	[CityID] [int] NOT NULL,
	[DayID] [varchar](8) NOT NULL,
	[PaymentMethodID] [int] NOT NULL,
	[BuyerID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[CategoryDeduplicate] [int] NOT NULL,
 CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC,
	[DayID] ASC,
	[PaymentMethodID] ASC,
	[BuyerID] ASC,
	[CategoryID] ASC,
	[CategoryDeduplicate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactSalesXDimCategory]    Script Date: 2/23/2024 10:00:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactSalesXDimCategory](
	[CityID] [int] NOT NULL,
	[DayID] [varchar](8) NOT NULL,
	[PaymentMethodID] [int] NOT NULL,
	[BuyerID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[CategoryDeduplicate] [int] NOT NULL,
 CONSTRAINT [PK_FactSalesXDimCategory] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC,
	[DayID] ASC,
	[PaymentMethodID] ASC,
	[BuyerID] ASC,
	[CategoryID] ASC,
	[CategoryDeduplicate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[DimBank] ([BankID], [BankName]) VALUES (1, N'N/A')
INSERT [dbo].[DimBank] ([BankID], [BankName]) VALUES (2, N'BAC Credomatic')
GO
INSERT [dbo].[DimBuyer] ([BuyerID], [BuyerName]) VALUES (1, N'TEAM International, Inc.')
GO
INSERT [dbo].[DimCard] ([CardID], [CardNumber], [CardTypeID], [BankID], [CardBrandID]) VALUES (1, N'N/A', 1, 1, 1)
INSERT [dbo].[DimCard] ([CardID], [CardNumber], [CardTypeID], [BankID], [CardBrandID]) VALUES (2, N'4085-18**-****-0998', 2, 2, 2)
INSERT [dbo].[DimCard] ([CardID], [CardNumber], [CardTypeID], [BankID], [CardBrandID]) VALUES (3, N'4919-49**-****-5863', 2, 2, 2)
INSERT [dbo].[DimCard] ([CardID], [CardNumber], [CardTypeID], [BankID], [CardBrandID]) VALUES (4, N'118961457', 3, 2, 1)
GO
INSERT [dbo].[DimCardBrand] ([CardBrandID], [CardBrandName]) VALUES (1, N'N/A')
INSERT [dbo].[DimCardBrand] ([CardBrandID], [CardBrandName]) VALUES (2, N'VISA')
GO
INSERT [dbo].[DimCardType] ([CardTypeID], [CardTypeName]) VALUES (1, N'N/A')
INSERT [dbo].[DimCardType] ([CardTypeID], [CardTypeName]) VALUES (2, N'Credit')
INSERT [dbo].[DimCardType] ([CardTypeID], [CardTypeName]) VALUES (3, N'Savings')
GO
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (1, 1, N'Restaurant for church fellowship')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (2, 1, N'Cash donation for church')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (3, 2, N'Email')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (4, 2, N'Server')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (5, 3, N'Book')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (6, 4, N'Warehouse club membership')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (7, 5, N'Salary')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (8, 6, N'Health insurance')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (9, 7, N'Parking')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (10, 8, N'Restaurant with nuclear family')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (11, 8, N'Household medicine')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (12, 9, N'Gasoline')
INSERT [dbo].[DimCategory] ([CategoryID], [ParentCategoryID], [CategoryName]) VALUES (13, 10, N'Family debt')
GO
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (1, N'Colonia Escalon', 1)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (2, N'La Mascota', 1)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (3, N'Online', 2)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (4, N'San Benito', 1)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (5, N'Antiguo Cuscatlan', 3)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (6, N'Santa Tecla', 3)
INSERT [dbo].[DimCity] ([CityID], [CityName], [ProvinceID]) VALUES (7, N'Volcan de San Salvador', 3)
GO
INSERT [dbo].[DimCountry] ([CountryID], [CountryName]) VALUES (1, N'El Salvador')
INSERT [dbo].[DimCountry] ([CountryID], [CountryName]) VALUES (2, N'Online')
GO
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231001', CAST(N'2023-10-01' AS Date), 4, 2023, 2, 1, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231002', CAST(N'2023-10-02' AS Date), 4, 2023, 2, 2, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231003', CAST(N'2023-10-03' AS Date), 4, 2023, 2, 3, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231004', CAST(N'2023-10-04' AS Date), 4, 2023, 2, 4, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231005', CAST(N'2023-10-05' AS Date), 4, 2023, 2, 5, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231006', CAST(N'2023-10-06' AS Date), 4, 2023, 2, 6, 10)
INSERT [dbo].[DimDay] ([DayID], [FullDateAlternateKey], [CalendarQuarter], [CalendarYear], [CalendarSemester], [DayOfWeek], [CalendarMonth]) VALUES (N'20231007', CAST(N'2023-10-07' AS Date), 4, 2023, 2, 7, 10)
GO
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (1, N'Donation')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (2, N'Digital subscription')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (3, N'Education')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (4, N'Membership')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (5, N'Employee salary')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (6, N'Insurance')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (7, N'Parking')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (8, N'Household expenses')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (9, N'Transportation')
INSERT [dbo].[DimParentCategory] ([ParentCategoryID], [ParentCategoryName]) VALUES (10, N'Debt')
GO
INSERT [dbo].[DimPaymentMethod] ([PaymentMethodID], [PaymentMethodName], [CardID]) VALUES (1, N'Cash', 1)
INSERT [dbo].[DimPaymentMethod] ([PaymentMethodID], [PaymentMethodName], [CardID]) VALUES (2, N'4085-18**-****-0998', 2)
INSERT [dbo].[DimPaymentMethod] ([PaymentMethodID], [PaymentMethodName], [CardID]) VALUES (3, N'4919-49**-****-5863', 3)
INSERT [dbo].[DimPaymentMethod] ([PaymentMethodID], [PaymentMethodName], [CardID]) VALUES (4, N'Electronic transfer', 1)
GO
INSERT [dbo].[DimProvince] ([ProvinceID], [ProvinceName], [CountryID]) VALUES (1, N'San Salvador', 1)
INSERT [dbo].[DimProvince] ([ProvinceID], [ProvinceName], [CountryID]) VALUES (2, N'Online', 2)
INSERT [dbo].[DimProvince] ([ProvinceID], [ProvinceName], [CountryID]) VALUES (3, N'La Libertad', 1)
GO
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (1, N'Restaurante Royal')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (2, N'Union Church of San Salvador')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (3, N'Google')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (4, N'DigitalOcean')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (5, N'Amazon')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (6, N'Pollo Campero')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (7, N'PriceSmart')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (8, N'Asesuisa')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (9, N'Multiplaza')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (10, N'San Martin')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (11, N'Farmacias Camila')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (12, N'McDonald''s')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (13, N'Restaurante y Jardin Ecologico Cafe del Volcan.')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (14, N'Texaco')
INSERT [dbo].[DimSeller] ([SellerID], [SellerName]) VALUES (15, N'Grandmother')
GO
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (20.0000, N'Table with ????????, for ????????''s birthday with Union Church people.', 1, N'20231001', 1, 1, 1, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (20.0000, N'Donation.', 1, N'20231001', 1, 2, 2, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (6.0000, N'Google Workspace Business Starter for September 2023.', 3, N'20231001', 2, 3, 3, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (6.7800, N'138.197.165.222 server for September 2023.', 3, N'20231001', 2, 4, 4, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (10.7900, N'"Academic Writing for Graduate Students: Essential Tasks and Skills", by John Swales, Christine Feak. Kindle Edition.', 3, N'20231003', 2, 5, 5, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (16.1900, N'"Atomic Habits: An Easy & Proven Way to Build Good Habits & Break Bad Ones", by James Clear. Kindle Edition.', 3, N'20231006', 2, 5, 5, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (53.2800, N'Medical insurance Policy No. 45316, payment No. 12.', 3, N'20231006', 2, 8, 8, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (1500.0000, N'Debt to grandmother.', 3, N'20231007', 4, 15, 13, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (5.0000, N'Breakfast with ???????? and ????????.', 4, N'20231004', 1, 6, 1, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (90.4000, N'Platinum Membership yearly fee.', 5, N'20231005', 3, 7, 6, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (0.5000, N'Parking for dinner with ????????.', 5, N'20231006', 1, 9, 9, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (15.6200, N'Dinner at San Martin Multiplaza with ????????.', 5, N'20231006', 2, 10, 10, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (67.5500, N'By Texaco British School. ???????? ????????.', 6, N'20231007', 2, 11, 11, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (1.6500, N'By Texaco British School. 2da Y 4rta Calle Oriente, Santa Tecla. 1 Sundae Chocolate for ????????.', 6, N'20231007', 2, 12, 10, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (42.3000, N'TEXACO SERVITEX. CAR PANAMERICANA KM11 ST TECLA. ROCELI CONSULTORES, S.A. DE C.V.', 6, N'20231007', 2, 14, 12, 1)
INSERT [dbo].[FactPurchases] ([TotalPurchases], [Description], [CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (19.5300, N'Canton El Progreso KM 20. Volcan de San Salvador. With ????????.', 7, N'20231007', 2, 13, 10, 1)
GO
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (1, N'20231001', 1, 1, 1, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (1, N'20231001', 1, 2, 2, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231001', 2, 3, 3, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231001', 2, 4, 4, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231003', 2, 5, 5, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231006', 2, 5, 5, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231006', 2, 8, 8, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231007', 4, 15, 13, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (4, N'20231004', 1, 6, 1, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (5, N'20231005', 3, 7, 6, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (5, N'20231006', 1, 9, 9, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (5, N'20231006', 2, 10, 10, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (6, N'20231007', 2, 11, 11, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (6, N'20231007', 2, 12, 10, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (6, N'20231007', 2, 14, 12, 1)
INSERT [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate]) VALUES (7, N'20231007', 2, 13, 10, 1)
GO
INSERT [dbo].[FactSales] ([TotalSales], [Description], [CityID], [DayID], [PaymentMethodID], [BuyerID], [CategoryID], [CategoryDeduplicate]) VALUES (9999.0000, N'FAKE VALUE FOR PRIVACY Salary for September 2023.', 3, N'20231006', 4, 1, 7, 1)
GO
INSERT [dbo].[FactSalesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [BuyerID], [CategoryID], [CategoryDeduplicate]) VALUES (3, N'20231006', 4, 1, 7, 1)
GO
ALTER TABLE [dbo].[DimCard]  WITH CHECK ADD  CONSTRAINT [FK_DimCard_DimBank] FOREIGN KEY([BankID])
REFERENCES [dbo].[DimBank] ([BankID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimCard] CHECK CONSTRAINT [FK_DimCard_DimBank]
GO
ALTER TABLE [dbo].[DimCard]  WITH CHECK ADD  CONSTRAINT [FK_DimCard_DimCardBrand] FOREIGN KEY([CardBrandID])
REFERENCES [dbo].[DimCardBrand] ([CardBrandID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimCard] CHECK CONSTRAINT [FK_DimCard_DimCardBrand]
GO
ALTER TABLE [dbo].[DimCard]  WITH CHECK ADD  CONSTRAINT [FK_DimCard_DimCardType] FOREIGN KEY([CardTypeID])
REFERENCES [dbo].[DimCardType] ([CardTypeID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimCard] CHECK CONSTRAINT [FK_DimCard_DimCardType]
GO
ALTER TABLE [dbo].[DimCategory]  WITH CHECK ADD  CONSTRAINT [FK_DimCategory_DimParentCategory] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[DimParentCategory] ([ParentCategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimCategory] CHECK CONSTRAINT [FK_DimCategory_DimParentCategory]
GO
ALTER TABLE [dbo].[DimCity]  WITH CHECK ADD  CONSTRAINT [FK_DimCity_DimProvince] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[DimProvince] ([ProvinceID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimCity] CHECK CONSTRAINT [FK_DimCity_DimProvince]
GO
ALTER TABLE [dbo].[DimPaymentMethod]  WITH CHECK ADD  CONSTRAINT [FK_DimPaymentMethod_DimCard] FOREIGN KEY([CardID])
REFERENCES [dbo].[DimCard] ([CardID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimPaymentMethod] CHECK CONSTRAINT [FK_DimPaymentMethod_DimCard]
GO
ALTER TABLE [dbo].[DimProvince]  WITH CHECK ADD  CONSTRAINT [FK_DimProvince_DimCountry] FOREIGN KEY([CountryID])
REFERENCES [dbo].[DimCountry] ([CountryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DimProvince] CHECK CONSTRAINT [FK_DimProvince_DimCountry]
GO
ALTER TABLE [dbo].[FactPurchases]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchases_FactPurchasesXDimCategory] FOREIGN KEY([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate])
REFERENCES [dbo].[FactPurchasesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [SellerID], [CategoryID], [CategoryDeduplicate])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactPurchases] CHECK CONSTRAINT [FK_FactPurchases_FactPurchasesXDimCategory]
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchasesXDimCategory_DimCategory] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[DimCategory] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory] CHECK CONSTRAINT [FK_FactPurchasesXDimCategory_DimCategory]
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchasesXDimCategory_DimCity] FOREIGN KEY([CityID])
REFERENCES [dbo].[DimCity] ([CityID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory] CHECK CONSTRAINT [FK_FactPurchasesXDimCategory_DimCity]
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchasesXDimCategory_DimDay] FOREIGN KEY([DayID])
REFERENCES [dbo].[DimDay] ([DayID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory] CHECK CONSTRAINT [FK_FactPurchasesXDimCategory_DimDay]
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchasesXDimCategory_DimPaymentMethod] FOREIGN KEY([PaymentMethodID])
REFERENCES [dbo].[DimPaymentMethod] ([PaymentMethodID])
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory] CHECK CONSTRAINT [FK_FactPurchasesXDimCategory_DimPaymentMethod]
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactPurchasesXDimCategory_DimSeller] FOREIGN KEY([SellerID])
REFERENCES [dbo].[DimSeller] ([SellerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactPurchasesXDimCategory] CHECK CONSTRAINT [FK_FactPurchasesXDimCategory_DimSeller]
GO
ALTER TABLE [dbo].[FactSales]  WITH CHECK ADD  CONSTRAINT [FK_FactSales_FactSales] FOREIGN KEY([CityID], [DayID], [PaymentMethodID], [BuyerID], [CategoryID], [CategoryDeduplicate])
REFERENCES [dbo].[FactSalesXDimCategory] ([CityID], [DayID], [PaymentMethodID], [BuyerID], [CategoryID], [CategoryDeduplicate])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactSales] CHECK CONSTRAINT [FK_FactSales_FactSales]
GO
ALTER TABLE [dbo].[FactSalesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactSalesXDimCategory_DimBuyer] FOREIGN KEY([BuyerID])
REFERENCES [dbo].[DimBuyer] ([BuyerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FactSalesXDimCategory] CHECK CONSTRAINT [FK_FactSalesXDimCategory_DimBuyer]
GO
ALTER TABLE [dbo].[FactSalesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactSalesXDimCategory_DimCategory] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[DimCategory] ([CategoryID])
GO
ALTER TABLE [dbo].[FactSalesXDimCategory] CHECK CONSTRAINT [FK_FactSalesXDimCategory_DimCategory]
GO
ALTER TABLE [dbo].[FactSalesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactSalesXDimCategory_DimCity] FOREIGN KEY([CityID])
REFERENCES [dbo].[DimCity] ([CityID])
GO
ALTER TABLE [dbo].[FactSalesXDimCategory] CHECK CONSTRAINT [FK_FactSalesXDimCategory_DimCity]
GO
ALTER TABLE [dbo].[FactSalesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactSalesXDimCategory_DimDay] FOREIGN KEY([DayID])
REFERENCES [dbo].[DimDay] ([DayID])
GO
ALTER TABLE [dbo].[FactSalesXDimCategory] CHECK CONSTRAINT [FK_FactSalesXDimCategory_DimDay]
GO
ALTER TABLE [dbo].[FactSalesXDimCategory]  WITH CHECK ADD  CONSTRAINT [FK_FactSalesXDimCategory_DimPaymentMethod] FOREIGN KEY([PaymentMethodID])
REFERENCES [dbo].[DimPaymentMethod] ([PaymentMethodID])
GO
ALTER TABLE [dbo].[FactSalesXDimCategory] CHECK CONSTRAINT [FK_FactSalesXDimCategory_DimPaymentMethod]
GO
USE [master]
GO
ALTER DATABASE [Finances] SET  READ_WRITE 
GO

I opened Visual Studio 2017:

I had installed Microsoft Visual Studio Community 2017 Version 15.9.49:

I clicked Project...:

I selected

I right-clicked the Data Sources folder in the Solution Explorer window, and selected New Data Source... from the context menu:

The Data Source Wizard appeared:

I clicked Next on the Welcome to the Data Source Wizard page. The Select how to define the connection page appeared. I selected Create a data source based on an existing or new connection and clicked New...:

I provided my server and database settings corresponding to the data mart that I wanted to use:

I selected the PDDMNB955.Finances.jaimemontoya data connection I previously created, and clicked Next:

The Impersonation Information page appeared. I clicked Next:

I entered FinancesDM for Data source name and clicked Finish:

The Data Source for my project was created successfully:

I right-clicked the Data Source Views folder in the Solution Explorer window, and selected New Data Source View... from the context menu:

The Data Source View Wizard appeared. I clicked Next:

The Select a Data Source page appeared. The FinacesDM data source that I created was selected. I clicked Next:

The Select Tables and Views page appeared. I moved all of the dimension and fact tables to the Included objects list by clicking the >> button. I had two fact tables in my list:

After moving the objects, I clicked Next:

The Completing the Wizard page appeared. I entered FinancesDM for Name. I clicked Finish:

My View was created successfully:

I right-clicked the Cubes folder in the Solution Explorer window, and selected New Cube from the context menu:

The Cube Wizard appeared. I clicked Next:

The Select Creation Method page appeared. I kept Use existing tables selected. Then I clicked Next:

The Select Measure Group Tables page appeared. The FinancesDM data source view that I created was selected. I checked FactPurchases in the Measure group tables area. I initially left the measures in the FactSales, FactPurchasesXDimCategory and FactSalesXDimCategory tables out of the cube to add them in at a later time. I clicked Next:

The Select Measures page appeared. The wizard found and checked the measure field in the FactPurchases table. The wizard also added a field called Fact Purchases Count, which counts the number of records. This does not provide me with any useful information in this data mart, so I unchecked the Fact Purchases Count field. I clicked Next:

The Select New Dimensions page appeared. The wizard found all of the tables related to the FactPurchases table in the data source view. It also created hierarchies for the DimCity, DimPaymentMethod, and DimCategory dimensions. I unchecked Fact Purchases and FactPurchases. I clicked Next:

The Completing the Wizard page appeared. I kept FinancesDM for Cube name. Then I clicked Finish:

My Cube was created successfully:

I right-clicked FinancesDM.cube and then clicked Process...:

I clicked Yes to build and deploy the project:

I received the following error message. I clicked Yes:

I received the following error:

The error message that I see reveals the root cause of the problem: The deployment has been cancelled because data source impersonation information was not supplied., as shown below:

I right-clicked FinancesDM.ds and then clicked View Designer:

From the Impersonation Information tab, I changed my selection from Use a specific Windows user name and password to Use the service account. Then I clicked OK:

I tried to process the cube again (as shown in previous steps). From the Process Cube window, I clicked Run...:

The process succeeded:

I right-clicked FinancesDM.cube and then clicked Browse:

I was able to browse my cube:

I right-clicked the Dimensions entry in the Solution Explorer window, and selected New Dimension from the context menu:

The Dimension Wizard dialog box appeared. I clicked Next:

The Select Creation Method page of the wizard appeared. I left the Use an existing table item selected and clicked Next:

The Specify Source Information page appeared. The FinancesDM data source view was already selected. I selected DimDay from the Main table drop-down list. DayID was selected as the first and only Key column. I selected DayID from the drop-down list at the bottom. I clicked Next:

The Select Dimension Attributes page appeared. I left Day ID checked and also checked Calendar Year, Calendar Quarter, Calendar Month, Calendar Semester, and Day Of Week. I clicked in the Attribute Type column across from Day ID. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Date and clicked OK:

I clicked in the Attribute Type column across from Calendar Quarter. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Quarter and clicked OK:

I clicked in the Attribute Type column across from Calendar Year. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Year and clicked OK:

I clicked in the Attribute Type column across from Calendar Semester. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Half Year and clicked OK:

I clicked in the Attribute Type column across from Day Of Week. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Day of Week and clicked OK:

I clicked in the Attribute Type column across from Calendar Month. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Month and clicked OK:

I clicked Next:

The Completing the Wizard page appeared. I changed the name to Dim Time. I clicked Finish:

The Dimension Design tab appeared with the Dim Time dimension:

I right-clicked the entry for Day ID in the Attributes area, and selected Rename from the context menu:

I entered Date for the name of this attribute, and pressed ENTER:

I repeated the steps above to rename the other attributes as follows:

I dragged the Year item from the Attributes column, and dropped it in the Hierarchies area:

I dragged the Semester item from the Attributes column, and dropped it on <new level> in the Hierarchies area:

I repeated the steps above for Quarter, Month, Day of week, and Date:

I right-clicked the word Hierarchy in the Hierarchies area, and selected Rename from the context menu:

I entered Date Hierarchy and pressed ENTER:

I clicked Month in the Attributes area. I held down SHIFT and clicked Day of week in the Attributes area. All six attributes were selected:

In the Properties window, I changed the AttributeHierarchyVisible property to False:

I clicked the Attribute Relationships tab. I noted the Date was related to the other five items, but the relationship structure did not mirror the hierarchy I created:

I selected the Day of week and dropped it on the Month:

I selected the Month and dropped it on the Quarter:

I selected the Quarter and dropped it on the Semester:

I selected the Semester and dropped it on the Year:

I right-clicked each of the relationship arrows, and selected Relationship Type | Rigid from the context menu:

The arrows changed to a solid background:

In the Solution Explorer window, I double-clicked the Fact Purchases X Dim Category.dim dimension entry. The Fact Purchases X Dim Category.dim dimension tab appeared:

I right-clicked the Bank ID attribute in the Attributes area and selected Rename from the context menu:

I typed Bank and pressed ENTER:

Having the Bank attribute selected, I clicked the NameColumn property in the Properties window. Then I clicked the ellipsis (…) button:

The Name Column dialog box appeared. In the Source column area, I clicked BankName. Then I clicked OK to exit the Name Column dialog box:

The purpose of the previous step was to determine what value to display for this attribute. In this case, the BankID was used to uniquely identify a member of the Bank attribute. However, the BankName is what is shown to the user as they use the Bank attribute in a query. For that reason, I selected BankName and not BankID.

I repeated the steps above to modify the other attributes in the Fact Purchases X Dim Category dimension as follows:

The attributes were renamed and their corresponding NameColumn values assigned:

I created a new hierarchy and named it Bank Hierarchy. This hierarchy had the following levels:

• Bank
• Card

I created a second hierarchy and named it Card Type Hierarchy. This hierarchy had the following levels:

• Card Type
• Card
• Payment Method

I created a third hierarchy and named it Card Brand Hierarchy. This hierarchy had the following levels:

• Card Brand
• Card
• Payment Method

I created a fourth hierarchy and named it Parent Category Hierarchy. This hierarchy had the following levels:

• Parent Category
• Category

I created a fifth hierarchy and named it Country Hierarchy. This hierarchy had the following levels:

• Country
• Province
• City

I set the AttributeHierarchyVisible property for all of the attributes to False:

I selected the Attribute Relationships tab:

I changed all of the Relationship Types to Rigid:

I double-clicked the entry for FinancesDM.cube to display the Cube Design tab:

I selected the Dimension Usage tab of the Cube Design tab:

I right-clicked in the empty area of the tab and selected Add Cube Dimension:

The Add Cube Dimension dialog box appeared. I selected the Dim Time dimension, and clicked OK:

The Dim Time dimension was added to the cube:

I clicked the entry in the Fact Purchases column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

I processed and browsed the cube, applying all dimensions available against the Total Purchases fact:

While browsing the cube, I removed all dimensions and kept only the Total Purchases fact. The result was the same total, $1,875.59 (compare screenshot above versus screenshot below):

I noticed that I was missing the Seller dimension in my cube. I opened Fact Purchases X Dim Category.dim. I dragged the Seller item from the Attributes column, and dropped it in the Hierarchies area. I renamed this new hierarchy from Hierarchy to Seller Hierarchy:

I processed the Fact Purchases X Dim Category.dim dimension, which contained the new Seller Hierarchy that I created:

I clicked Yes to build and deploy the project:

I clicked Yes to proceed with the deployment, overwriting the database:

I clicked Run... to start processing the dimension:

Processing Dimension 'Fact Purchases X Dim Category', Cube 'FinancesDM', and Measure Group 'Fact Purchases' completed successfully:

Browsing the cube allowed me to display Total Purchases by Seller:

I browsed the cube returning Total Purchases by Seller, by Parent Category, by Category, and by Date (Year, Semester, Quarter, Month, Day of week, and Date):

I browsed the cube returning Total Purchases by Parent Category:

My cube was working correctly for my purchases. Next I needed to make it work the same way for my sales.

In the Measures pane, I right-clicked FinancesDM, my top and only node. Then I clicked New Measure...:

From the New Measure dialog box, I selected the following options and clicked OK:

My new measure was created, for now not connected to any dimension:

I pretended I was going to create a new Cube, but my real intention was to use the Cube Wizard in order to select new dimensions to be created, based on available tables. My intuition told me that it was more logical to use the Dimension Wizard for the purpose of creating dimensions for my FactSales measure, but this is as far as I could get, and I did not want to create a named calculation in the Data Source View as suggested by the warning (A name column must be specified when a composite key is used. If not already present, it is recommended to create a named calculation in the Data Source View that concatenates the specified key columns.) that did not let me continue (the Next button was greyed out):

I right-clicked Cubes and then clicked New Cube...:

The Cube Wizard appeared. I clicked Next:

The Select Creation Method page appeared. I kept Use existing tables selected. Then I clicked Next:

The Select Measure Group Tables page appeared. The FinancesDM data source view that I created was selected. I checked FactSales in the Measure group tables area. I clicked Next:

The Select Measures page appeared. The wizard found and checked the measure field in the FactSales table. The wizard also added a field called Fact Sales Count, which counts the number of records. This does not provide me with any useful information in this data mart, so I unchecked the Fact Sales Count field. I clicked Next:

The Select Existing Dimensions page appeared. I unchecked both of my existing dimensions: Fact Purchases X Dim Category and Dim Time. I clicked Next:

The Select New Dimensions page appeared. The wizard found all of the tables related to the FactSales table in the data source view. It also created hierarchies for the DimCity, DimPaymentMethod, and DimCategory dimensions. I unchecked Fact Sales and FactSales. I clicked Next:

The Completing the Wizard page appeared. I kept FinancesDM 1 for Cube name. Then I clicked Finish:

My new dimension (Fact Sales X Dim Category.dim) appeared. I deleted the new cube (FinancesDM 1.cube), since creating it was just a workaround for the real purpose of creating the Fact Sales X Dim Category.dim dimension with the Cube Wizard:

In the Solution Explorer window, I double-clicked the Fact Sales X Dim Category.dim dimension entry. The Fact Fact Sales X Dim Category.dim dimension tab appeared:

I right-clicked the Bank ID attribute in the Attributes area and selected Rename from the context menu:

I typed Bank and pressed ENTER:

Having the Bank attribute selected, I clicked the NameColumn property in the Properties window. Then I clicked the ellipsis (…) button:

The Name Column dialog box appeared. In the Source column area, I clicked BankName. Then I clicked OK to exit the Name Column dialog box:

I repeated the steps above to modify the other attributes in the Fact Sales X Dim Category dimension as follows:

The attributes were renamed and their corresponding NameColumn values assigned:

I created a new hierarchy and named it Bank Hierarchy. This hierarchy had the following levels:

• Bank
• Card

I created a second hierarchy and named it Card Type Hierarchy. This hierarchy had the following levels:

• Card Type
• Card
• Payment Method

I created a third hierarchy and named it Card Brand Hierarchy. This hierarchy had the following levels:

• Card Brand
• Card
• Payment Method

I created a fourth hierarchy and named it Parent Category Hierarchy. This hierarchy had the following levels:

• Parent Category
• Category

I created a fifth hierarchy and named it Country Hierarchy. This hierarchy had the following levels:

• Country
• Province
• City

I created a sixth hierarchy and named it Buyer Hierarchy. This hierarchy had the following levels:

• Buyer

I set the AttributeHierarchyVisible property for all of the attributes to False:

I selected the Attribute Relationships tab:

I changed all of the Relationship Types to Rigid:

I double-clicked the entry for FinancesDM.cube to display the Cube Design tab:

I selected the Dimension Usage tab of the Cube Design tab:

I clicked the entry in the Fact Sales column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

I right-clicked in the empty area of the tab and selected Add Cube Dimension:

The Add Cube Dimension dialog box appeared. I selected the Fact Sales X Dim Category dimension, and clicked OK:

The Fact Sales X Dim Category dimension was added to the cube:

I clicked the ellipsis (...) that corresponded to the Composite Primary Key that appeared in the intersection between the Fact Sales Measure Group, and the Fact Sales X Dim Category Dimension:

Everything was correct with the definition of the relationship:

I clicked the ellipsis (...) that corresponded to the Composite Primary Key that appeared in the intersection between the Fact Purchases Measure Group, and the Fact Purchases X Dim Category Dimension:

Everything was correct with the definition of the relationship:

I right-clicked FinancesDM.cube and clicked Process...:

I clicked Yes to build and deploy:

I clicked Yes to overwrite the database:

I provided my username and password to connect to the database and clicked OK:

I clicked Run...:

The cube was processed successfully, which included processing Dimension 'Fact Sales X Dim Category', Measure Group 'Fact Purchases', and Measure Group 'Fact Sales'. I clicked Close:

I right-clicked FinancesDM.cube and clicked Browse:

I browsed the cube applying all dimensions available against the Total Sales fact:

I double-clicked FinancesDM.cube to see the structure of the cube in a diagram, with all of its facts (yellow header) and dimensions (blue header):

Browsing the cube, I can answer questions such as, how much have I spent by city?:

How much have I spent by payment method?:

How much have I spent by payment method and city?:

I could discover how much money I had spent by a combination of multiple dimensions. For example, how much money have I spent by payment method and city and seller and date and category?:

How much money have I spent by date?

The format of dates was difficult to read and ambiguous: Does Day of week = 1 mean Monday or Sunday? Does Date = 20231001 mean October 1st, 2023; or January 10th, 2023? For that reason, I had to find a better way to produce the Time Dimension.

I double-clicked FinancesDM.dsv:

I right-clicked the DimDay table and clicked New Named Calculation...:

I created two Named Calculations with the following values:

CASE
WHEN CalendarMonth = 1 THEN 'January'
WHEN CalendarMonth = 2 THEN 'February'
WHEN CalendarMonth = 3 THEN 'March'
WHEN CalendarMonth = 4 THEN 'April'
WHEN CalendarMonth = 5 THEN 'May'
WHEN CalendarMonth = 6 THEN 'June'
WHEN CalendarMonth = 7 THEN 'July'
WHEN CalendarMonth = 8 THEN 'August'
WHEN CalendarMonth = 9 THEN 'September'
WHEN CalendarMonth = 10 THEN 'October'
WHEN CalendarMonth = 11 THEN 'November'
WHEN CalendarMonth = 12 THEN 'December'
END

I created the Named Calculation for Month:

I created the Named Calculation for Day:

The new Named Calculations appeared in the FinancesDM.dsv Data Source View diagram:

I right-clicked the DimDay table and clicked Explore Data:

The Explore DimDay Table tab appeared with the corresponding results from the view:

The results above were exactly the same as when running SELECT * FROM [Finances].[dbo].[DimDay] from SQL Server Management Studio, except for the two Calculated Columns (Month and Day):

I double-clicked FinancesDM.cube to open the FinancesDM.cube [Design] tab. The Calculated Columns were included in the cube diagram:

I right-clicked Dim Time.dim and clicked Delete:

I clicked OK to delete the Dim Time object:

I right-clicked the Dimensions entry in the Solution Explorer window, and selected New Dimension from the context menu:

The Dimension Wizard dialog box appeared. I clicked Next:

The Select Creation Method page of the wizard appeared. I left the Use an existing table item selected and clicked Next:

The Specify Source Information page appeared. The FinancesDM data source view was already selected. I selected DimDay from the Main table drop-down list. DayID was selected as the first and only Key column. I selected DayID from the drop-down list at the bottom. I clicked Next:

The Select Dimension Attributes page appeared. I left Day ID checked and also checked Full Date Alternate Key, Calendar Quarter, Calendar Year, Calendar Semester, Day Of Week, Month, and Day. I clicked in the Attribute Type column across from Day ID. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Date and clicked OK:

I clicked in the Attribute Type column across from Full Date Alternate Key. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Date and clicked OK:

I clicked in the Attribute Type column across from Calendar Quarter. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Quarter and clicked OK:

I clicked in the Attribute Type column across from Calendar Year. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Year and clicked OK:

I clicked in the Attribute Type column across from Calendar Semester. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Half Year and clicked OK:

I clicked in the Attribute Type column across from Day Of Week. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Day of Week and clicked OK:

I clicked in the Attribute Type column across from Month. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Month and clicked OK:

I clicked in the Attribute Type column across from Day. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Day of Month and clicked OK:

I clicked Next:

The Completing the Wizard page appeared. I changed the name to Dim Time. I clicked Finish:

The Dimension Design tab appeared with the Dim Time dimension:

I right-clicked the entry for Day ID in the Attributes area, and selected Rename from the context menu:

I entered Date Key for the name of this attribute, and pressed ENTER:

I repeated the steps above to rename the other attributes as follows:

I dragged the Year item from the Attributes column, and dropped it in the Hierarchies area:

I dragged the Semester item from the Attributes column, and dropped it on <new level> in the Hierarchies area:

I repeated the steps above for Quarter, Month, Day of Month, Day of Week, and Date:

I right-clicked the word Hierarchy in the Hierarchies area, and selected Rename from the context menu:

I entered Time Hierarchy and pressed ENTER:

I clicked Quarter in the Attributes area. I held down SHIFT and clicked Month in the Attributes area. All eight attributes were selected:

In the Properties window, I changed the AttributeHierarchyVisible property to False:

I clicked the Attribute Relationships tab. I noted the Date Key was related to the other seven items, but the relationship structure did not mirror the hierarchy I created:

I selected the Day of Month and dropped it on the Month:

I selected the Day of Week and dropped it on the Month:

I selected the Date and dropped it on the Month:

I selected the Month and dropped it on the Quarter:

I selected the Quarter and dropped it on the Semester:

I selected the Semester and dropped it on the Year:

I right-clicked each of the relationship arrows, and selected Relationship Type | Rigid from the context menu:

The arrows changed to a solid background:

I double-clicked the entry for FinancesDM.cube to display the Cube Design tab:

I selected the Dimension Usage tab of the Cube Design tab:

I right-clicked in the empty area of the tab and selected Add Cube Dimension:

The Add Cube Dimension dialog box appeared. I selected the Dim Time dimension, and clicked OK:

The Dim Time dimension was added to the cube:

I clicked the entry in the Fact Purchases column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date Key from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

I clicked the entry in the Fact Sales column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date Key from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

When trying to process the cube, this message appeared: The 'FinancesDM' database on 'localhost' has changed since the last time the project was deployed. If you proceed with deployment, the database will be overwritten. Would you like to continue? I clicked Yes:

I provided my username and password to connect to the database and clicked OK:

I clicked Run...:

The cube was processed successfully, which included processing Dimension 'Dim Time', Measure Group 'Fact Purchases', and Measure Group 'Fact Sales':

I browsed the cube, applying all dimensions available against the Total Purchases fact:

The format of Day of Week was difficult to read and ambiguous: Does Day of Week = 1 mean Monday or Sunday? For that reason, I had to find a better way to produce the Time Dimension.

I double-clicked FinancesDM.dsv:

I right-clicked the DimDay table and clicked New Named Calculation...:

I created a Named Calculation with the following values:

CASE
WHEN DayOfWeek = 1 THEN 'Sunday'
WHEN DayOfWeek = 2 THEN 'Monday'
WHEN DayOfWeek = 3 THEN 'Tuesday'
WHEN DayOfWeek = 4 THEN 'Wednesday'
WHEN DayOfWeek = 5 THEN 'Thursday'
WHEN DayOfWeek = 6 THEN 'Friday'
WHEN DayOfWeek = 7 THEN 'Saturday'
END

I created the Named Calculation for Day of Week:

The new Named Calculation appeared in the FinancesDM.dsv Data Source View diagram:

I right-clicked the DimDay table and clicked Explore Data:

The Explore DimDay Table tab appeared with the corresponding results from the view:

I double-clicked FinancesDM.cube to open the FinancesDM.cube [Design] tab. The new Calculated Column was included in the cube diagram:

I right-clicked Dim Time.dim and clicked Delete:

I clicked OK to delete the Dim Time object:

I right-clicked the Dimensions entry in the Solution Explorer window, and selected New Dimension from the context menu:

The Dimension Wizard dialog box appeared. I clicked Next:

The Select Creation Method page of the wizard appeared. I left the Use an existing table item selected and clicked Next:

The Specify Source Information page appeared. The FinancesDM data source view was already selected. I selected DimDay from the Main table drop-down list. DayID was selected as the first and only Key column. I selected DayID from the drop-down list at the bottom. I clicked Next:

The Select Dimension Attributes page appeared. I left Day ID checked and also checked Full Date Alternate Key, Calendar Quarter, Calendar Year, Calendar Semester, Month, Day, and Dim Day - Day Of Week. I clicked in the Attribute Type column across from Day ID. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Date and clicked OK:

I clicked in the Attribute Type column across from Full Date Alternate Key. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Date and clicked OK:

I clicked in the Attribute Type column across from Calendar Quarter. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Quarter and clicked OK:

I clicked in the Attribute Type column across from Calendar Year. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Year and clicked OK:

I clicked in the Attribute Type column across from Calendar Semester. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Half Year and clicked OK:

I clicked in the Attribute Type column across from Month. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Month and clicked OK:

I clicked in the Attribute Type column across from Day. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Day of Month and clicked OK:

I clicked in the Attribute Type column across from Dim Day - Day Of Week. I clicked the drop-down arrow. I expanded the Date entry. I expanded the Calendar entry. I selected Day of Week and clicked OK:

I clicked Next:

The Completing the Wizard page appeared. I changed the name to Dim Time. I clicked Finish:

The Dimension Design tab appeared with the Dim Time dimension:

I right-clicked the entry for Day ID in the Attributes area, and selected Rename from the context menu:

I entered Date Key for the name of this attribute, and pressed ENTER:

I repeated the steps above to rename the other attributes as follows:

I dragged the Year item from the Attributes column, and dropped it in the Hierarchies area:

I dragged the Semester item from the Attributes column, and dropped it on <new level> in the Hierarchies area:

I repeated the steps above for Quarter, Month, Day of Month, Day of Week, and Date:

I right-clicked the word Hierarchy in the Hierarchies area, and selected Rename from the context menu:

I entered Time Hierarchy and pressed ENTER:

I clicked Date in the Attributes area. I held down SHIFT and clicked Year in the Attributes area. All eight attributes were selected:

In the Properties window, I changed the AttributeHierarchyVisible property to False:

I clicked the Attribute Relationships tab. I noted the Date Key was related to the other seven items, but the relationship structure did not mirror the hierarchy I created:

I selected the Day of Month and dropped it on the Month:

I selected the Day of Week and dropped it on the Month:

I selected the Date and dropped it on the Month:

I selected the Month and dropped it on the Quarter:

I selected the Quarter and dropped it on the Semester:

I selected the Semester and dropped it on the Year:

I right-clicked each of the relationship arrows, and selected Relationship Type | Rigid from the context menu:

The arrows changed to a solid background:

I double-clicked the entry for FinancesDM.cube to display the Cube Design tab:

I selected the Dimension Usage tab of the Cube Design tab:

I right-clicked in the empty area of the tab and selected Add Cube Dimension:

The Add Cube Dimension dialog box appeared. I selected the Dim Time dimension, and clicked OK:

The Dim Time dimension was added to the cube:

I clicked the entry in the Fact Purchases column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date Key from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

I clicked the entry in the Fact Sales column and the Dim Time row. An ellipsis (…) button appeared and I clicked it:

The Define Relationship dialog box appeared. I selected Regular in the Select relationship type drop-down list. I selected Date Key from the Granularity attribute drop-down list. In the Relationship grid, I selected DayID from the drop-down list under Measure Group Columns. I clicked OK to exit the Define Relationship dialog box:

The Dimension Usage tab appeared as shown below:

I processed the cube. Then I browsed it, applying all dimensions available against the Total Purchases fact: