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:

Show database and data mart tables from SQL Server Management Studio

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

Generate Scripts...

The Generate Scripts Wizard appeared. I clicked Next:

Generate Scripts Wizard

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

Generate Scripts...

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

Save script as script file

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:

Choose Schema and Data as the Type of data to script

I clicked Next:

Click Next after choosing type of data to script

The Summary window appeared. I clicked Next:

Click Next from Summary window

The Save Scripts window appeared. I clicked Finish:

Click Finish from Save Scripts window

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

Properties of script to create and populate tables

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:

Open Visual Studio 2017

I had installed Microsoft Visual Studio Community 2017 Version 15.9.49:

Show installed version of Visual Studio

I clicked Project...:

Click Project...

I selected

Analysis Services Multidimensional and Data Mining Project. I entered FinancesDM for the Name and set the Location to where I wanted to store the project. I clicked OK to create the project:

Create Project

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

Create new Data Source

The Data Source Wizard appeared:

Data Source Wizard

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...:

Create Data Source based on new connection

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

Data mart server and database settings

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

Select Connection previously created

The Impersonation Information page appeared. I clicked Next:

Ignore Impersonation and click Next

I entered FinancesDM for Data source name and clicked Finish:

Finish creation of new Data Source

The Data Source for my project was created successfully:

New Data Source 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:

Create new Data Source View

The Data Source View Wizard appeared. I clicked Next:

Show Data Source View Wizard

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

Select a Data Source for the View

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:

Move Tables and Views to Included objects

After moving the objects, I clicked Next:

Continue after moving objects

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

Finish creation of Data Source View

My View was created successfully:

View created successfully

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

Create new Cube

The Cube Wizard appeared. I clicked Next:

Cube Wizard

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

Use existing tables

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:

Select Measure Group Table

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:

Select Measures

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:

Select new Dimensions

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

Completing the Cube Wizard

My Cube was created successfully:

Cube created successfully

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

Process Cube

I clicked Yes to build and deploy the project:

Build and deploy Cube

I received the following error message. I clicked Yes:

Continue building and deploying even with errors

I received the following error:

Cube not found 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:

Root cause of problem

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

Go to 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:

Change Impersonation Information

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

Run processing of Cube

The process succeeded:

Success processing Cube

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

Browse Cube

I was able to browse my cube:

Browse cube using multiple dimensions

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

Create new Dimension

The Dimension Wizard dialog box appeared. I clicked Next:

Dimension Wizard

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

Use existing table to create new Dimension

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:

Specify Data Source Information for new Dimension

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:

Select Date as Attribute Type

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:

Select Quarter

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:

Select Year attribute Type

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:

Select Half Year Attribute Type

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:

Select Day Of Week Attribute Type

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:

Select Month Attribute Type

I clicked Next:

Select Dimension Attributes

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

Complete Wizard for creating Time Dimension

The Dimension Design tab appeared with the Dim Time dimension:

Time Dimension Design tab

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

Rename Dimension attribute

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

Renamed Attribute

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

Old nameNew name
Calendar MonthMonth
Calendar QuarterQuarter
Calendar SemesterSemester
Calendar YearYear
Day Of WeekDay of week
Renamed attributes

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

Drag Date and drop to hierarchies area

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

Send Semester to Hierarchy

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

Send Attributes to Hierarchy

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

Rename Hierarchy

I entered Date Hierarchy and pressed ENTER:

Rename Date Hierarchy

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:

Select all Attributes

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

Change AttributeHierarchyVisible 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:

Show wrong Attribute Relationship

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

Define Attribute Relationship between Day of week and Month

I selected the Month and dropped it on the Quarter:

Define Attribute Relationship between Month and Quarter

I selected the Quarter and dropped it on the Semester:

Define Attribute Relationship between Quarter and Semester

I selected the Semester and dropped it on the Year:

Define Attribute Relationship between Semester and Year

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

Select Rigid Relationship Type

The arrows changed to a solid background:

Arrows with 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:

Show FactPurchasesXDimCategory tab

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

Rename Bank ID

I typed Bank and pressed ENTER:

Rename Bank ID to Bank

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

Click NameColumn for Bank attribute

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:

Select BankName

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:

Old nameNew nameName column - Source column
Card IDCardCardNumber
Card Brand IDCard BrandCardBrandName
Card Type IDCard TypeCardTypeName
Category IDCategoryCategoryName
City IDComposite Primary KeyCityID (useless but I selected one just because it was required)
Country IDCountryCountryName
Day IDDateFullDateAlternateKey
Dim City - City IDCityCityName
Parent Category IDParent CategoryParentCategoryName
Payment Method IDPayment MethodPaymentMethodName
Province IDProvinceProvinceName
Seller IDSellerSellerName

The attributes were renamed and their corresponding NameColumn values assigned:

Rename dimension attributes and assign NameColumn

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

Create Bank Hierarchy

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

Create Card Type Hierarchy

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

Create Card Brand Hierarchy

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

Create Parent Category Hierarchy

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

Create Country Hierarchy

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

Set AttributeHierarchyVisible to False

I selected the Attribute Relationships tab:

Select AttributeRelationships tab

I changed all of the Relationship Types to Rigid:

Set Relationship Type to Rigid

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

Open Cube Design tab

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

Select Dimension Usage

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

Add Dimension Cube

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

Add Dim Time Cube Dimension

The Dim Time dimension was added to the cube:

Dim Time dimension added to cube

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

Show ellipsis

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:

Define Relationship for Dim Time dimension

The Dimension Usage tab appeared as shown below:

Show Date Relationship

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

Browse cube displaying all dimensions

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):

Show fact without distribution by dimension

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:

Add Attribute to Hierarchy

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

Process dimension to recognize new hierarchy

I clicked Yes to build and deploy the project:

Build and deploy project

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

Deploy and overwrite database

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

Run to start processing dimension

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

Cube, Fact and Dimension processed successfully

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

Show 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):

Browse Total Purchases by multiple dimensions

I browsed the cube returning Total Purchases by Parent Category:

Browse cube displaying 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...:

Create new measure

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

UsageSource tableSource column
SumFactSalesTotalSales
Provide values to use for new measure

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

New measure created

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):

Issue when trying to create dimensions from Dimension Wizard

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

Create new fake cube

The Cube Wizard appeared. I clicked Next:

Click Next from Cube Wizard

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

Use existing tables to create new cube

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:

Select FactSales as Measure Group Table

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:

Select TotalSales Measure

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

Uncheck existing dimensions

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:

Select dimensions for FactSales

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

Finish creation of new cube

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:

New dimension created with 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:

Open Fact Sales X Dim Category.dim dimension tab

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

Rename Bank ID attribute

I typed Bank and pressed ENTER:

Show renamed attribute

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

Define NameColumn for Bank attribute

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:

Select BankName from Name Column dialog box

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

Old nameNew nameName column - Source column
Buyer IDBuyerBuyerName
Card Brand IDCard BrandCardBrandName
Card IDCardCardNumber
Card Type IDCard TypeCardTypeName
Category IDCategoryCategoryName
City IDComposite Primary KeyCityID (useless but I selected one just because it was required)
Country IDCountryCountryName
Day IDDateFullDateAlternateKey
Dim City - City IDCityCityName
Parent Category IDParent CategoryParentCategoryName
Payment Method IDPayment MethodPaymentMethodName
Province IDProvinceProvinceName

The attributes were renamed and their corresponding NameColumn values assigned:

Renamed attributes for new dimension

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

Show Bank Hierarchy

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

Create Card Type Hierarchy for dimension

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

Create Card Brand Hierarchy for dimension

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

Create Parent Category Hierarchy for dimension

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

Create Country Hierarchy for dimension

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

Create Buyer Hierarchy for dimension

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

Set AttributeHierarchyVisible to False for all attributes

I selected the Attribute Relationships tab:

Select Attribute tab for dimension

I changed all of the Relationship Types to Rigid:

Show solid arrows for Relationship Type Rigid

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

Open Design tab of cube

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

Select Dimension Usage tab of 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:

Ellipsis appearing to connect Fact Sales to Dim Time

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:

Define Relationship for Time dimension

The Dimension Usage tab appeared as shown below:

Link Time Dimension to Fact Sales Measure Group

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

Add Cube Dimension

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

Add Fact Sales X Dim Category Cube Dimension

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

Add Fact Sales X Dim Category dimension

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:

Click ellipsis

Everything was correct with the definition of the relationship:

Correct definition of 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:

Click ellipsis for Fact Purchases and Fact Purchases X Dim Category

Everything was correct with the definition of the relationship:

Everything OK with Composite Primary Key attribute

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

Process cube again

I clicked Yes to build and deploy:

Click Yes to build and deploy

I clicked Yes to overwrite the database:

Click Yes to overwrite database

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

Provide username and password to connect to database

I clicked Run...:

Click 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:

Process new Dimension and old Facts

I right-clicked FinancesDM.cube and clicked Browse:

Browse processed cube

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

Browse cube to see sales

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):

Show structure of cube in diagram

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

Distribution of money spent by city

How much have I spent by payment method?:

Distribution of money spent by payment method

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

Distribution of money spent by 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?:

Money spent by a combination of multiple dimensions

How much money have I spent by date?

Show money 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:

Open Data Source View

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

Create new Named Calculation

I created two Named Calculations with the following values:

Colum nameExpression
Month
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
DayCONVERT(int, RIGHT(DayID, 2))

I created the Named Calculation for Month:

Create Named Calculation for Month

I created the Named Calculation for Day:

Create Named Calculation for Day

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

Calculated columns in Data Source View

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

Explore Data from Data Source View

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

Explore DimDay table

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):

Explore DimDay table data from SQL Server Management Studio

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

Include Calculated Columns in cube diagram

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

Delete Time dimension

I clicked OK to delete the Dim Time object:

Delete Dim Time object

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

Create new Time dimension

The Dimension Wizard dialog box appeared. I clicked Next:

Start Dimension Wizard

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

Use an existing table to create dimension

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:

Specify Source Information for Time dimension

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:

Define Attribute Type for Day ID

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:

Select Date Attribute Type

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:

Select Quarter Attribute Type

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:

Select Year Attribute Type for Dimension

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:

Select Half Year Attribute Type for Dimension

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:

Select Month Attribute Type for Dimension

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:

Select Month Attribute Type for Dimension

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:

Select Day of Month Attribute Type for Dimension

I clicked Next:

Click Next after selecting Time Dimension attributes

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

Click Finish to complete creation of Time Dimension

The Dimension Design tab appeared with the Dim Time dimension:

Time Dimension Design tab for new Time Dimension

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

Rename Day ID Attribute

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

Day ID renamed to Date Key

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

Old nameNew name
Calendar QuarterQuarter
Calendar SemesterSemester
Calendar YearYear
DayDay of Month
Day Of WeekDay of Week
Full Date Alternate KeyDate
Rename Time Attributes

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

Move Year Attribute to Hierarchy

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

Move Semester Attribute to Hierarchy

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

Move all attributes to hierarchy

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

Rename Hierarchy for Time

I entered Time Hierarchy and pressed ENTER:

Rename to Time Hierarchy

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

Select all eight attributes

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

Set AttributeHierarchyVisible to False for all Time Attributes

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:

Show Attribute Relationships

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

Day of Month with Month relationship

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

Day of Week with Month relationship

I selected the Date and dropped it on the Month:

Date with Month relationship

I selected the Month and dropped it on the Quarter:

Month with Quarter relationship

I selected the Quarter and dropped it on the Semester:

Quarter with Semester relationship

I selected the Semester and dropped it on the Year:

Semester with Year relationship

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

Select Rigid Relationship Type in Attribute Relationships

The arrows changed to a solid background:

Arrow changed to solid background

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

Open Cube Design

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

Go to Dimension Usage

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

Add Time Cube Dimension

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

Add Dim Time Cube Dimension from Dimension Usage

The Dim Time dimension was added to the cube:

Dim Time Dimension added to Cube from Dimension Usage

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

Click ellipsis for Fact Purchases with Dim Time

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:

Define Relationship for Dim Time Dimension against Fact Purchases

The Dimension Usage tab appeared as shown below:

Date Key between Dim Time and Fact Purchases

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

Ellipsis appearing for connecting Fact Sales to Dim Time

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:

Define Relationship for Dim Time Dimension against Fact Sales

The Dimension Usage tab appeared as shown below:

Date Key between Dim Time and Fact Sales

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:

Overwrite database to process cube

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

Provide username and password to connect to database and process cube

I clicked Run...:

Run for processing cube

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

Cube processed successfully

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

Browse cube displaying all dimensions for purchases

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:

Open Data Source View

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

Create New Named Calculation for Day of Week

I created a Named Calculation with the following values:

Colum nameExpression
Day of Week
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:

Named Calculation for Day of Week

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

Day of Week Named Calculation

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

Explore Data from Data Source View for DimDay Table

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

Explore DimDay Table with Day of Week

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

Day of Week Named Calculation from Cube Design

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

Delete Dim Time object again

I clicked OK to delete the Dim Time object:

Delete Dim Time object

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

Create New Dimension for Time

The Dimension Wizard dialog box appeared. I clicked Next:

Start Dimension Wizard

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

Use existing table to create Time dimension

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:

Specify Source Information for Time Dimension with Days of Week

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:

Select Day ID Attribute Type

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:

Select Attribute Type for Full Date Alternate Key

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:

Select Quarter Attribute Type for Time Dimension

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:

Select Year Attribute Type for Time Dimension again

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:

Select Half Year Attribute Type for Time Dimension

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:

Select Month Attribute Type for Time Dimension

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:

Select Day of Month Attribute Type for Time Dimension

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:

Select Day of Week Attribute Type for Time Dimension

I clicked Next:

Click Next after selecting Time Dimension attributes again

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

Complete Wizard for creating Time Dimension for cube

The Dimension Design tab appeared with the Dim Time dimension:

Time Dimension Design tab for Time Dimension

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

Rename Day ID Attribute for Time Dimension

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

Renamed attribute to Date Key

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

Old nameNew name
Calendar QuarterQuarter
Calendar SemesterSemester
Calendar YearYear
DayDay of Month
Dim Day - Day Of WeekDay of Week
Full Date Alternate KeyDate
Rename attributes

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

Drag Year and drop to Hierarchies area

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

Drag Semester and drop to Hierarchies area

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

Dragging Attributes to Hierarchies

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

Rename Hierarchy for Time Dimension

I entered Time Hierarchy and pressed ENTER:

Time Hierarchy renamed

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

Select all attributes for Time Dimension

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

Set AttributeHierarchyVisible to False for all attributes in Time Dimension

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:

Show Attribute Relationships for Time Dimension

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

Select Day of Month and drop it on the Month

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

Select Day of Month and drop it on the Month

I selected the Date and dropped it on the Month:

Select Day of Month and drop it on the Month

I selected the Month and dropped it on the Quarter:

Select Month and drop it on the Quarter

I selected the Quarter and dropped it on the Semester:

Select Quarter and drop it on the Semester

I selected the Semester and dropped it on the Year:

Select Semester and drop it on the Year

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

Change Relationship Type from Flexible to Rigid

The arrows changed to a solid background:

Relationship Type changed to Solid

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

Open Cube

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

Select Dimension Usage from Cube Design

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

Add Cube Dimension from Cube Design

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

Add Dim Time Cube Dimension from Cube Design

The Dim Time dimension was added to the cube:

Add Dim Time Dimension to Cube

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

Select intersection between Fact Purchases and Dim Time

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:

Define relationship between Dimension and Measure Group

The Dimension Usage tab appeared as shown below:

Date Key relationship set correctly

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

Click intersection between Fact Sales and Dim Time

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:

Define relationship between Fact Sales and Dim Time

The Dimension Usage tab appeared as shown below:

Show Date Key relationship between Fact Sales and Dim Time

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

Browse cube displaying Day of Week in correct format
Published: 6:14 AM GMT · Dec 31, 2023