Mini data warehouse building learning: daily household shopping expenses

Objective: To learn about Data Warehouses
(The following is only a personal attempt to learn data warehouses.
Is superficial
I hope you will be able to provide guidance after reading this article.
I will learn more on this basis in the future)

Steps:
1. Create a database data table to fill in the data
2. Create a data warehouse database data source dimension multi-dimensional dataset
3. Run mdx
======================================
1. Create a database data table to fill in the data
======================================
---- Create a home table
Create Table [familylist] (
[Familylist_id] [int] identity (1, 1) not null,
[Family_id] [int] not null,
[Cy_id] [int] not null,
[Cy_name] [char] (20) Collate chinese_prc_ci_as not null
) On [primary]
Go

---- Create a supermarket table
Create Table [supermarket] (
[Superm_id] [int] identity (1, 1) not null,
[Superm_name] [char] (20) Collate chinese_prc_ci_as not null,
[Superm_address] [char] (30) Collate chinese_prc_ci_as not null
) On [primary]
Go

---- Create Date and Time Table
Create Table [datelist] (
[Date_id] [int] identity (1, 1) not null,
[The_date] [datetime] not null,
[The_month] [int] not null,
[The_quertar] [int] not null,
[The_year] [int] not null
) On [primary]
Go

---- Create a shopping table
Create Table [paylist] (
[Paylist_id] [int] identity (1, 1) not null,
[Payerid] [int] not null,
[Paydate] [int] not null,
[Paysuperm] [int] not null,
[Payprice] [money] not null,
[Paynote] [char] (20) Collate chinese_prc_ci_as not null
) On [primary]
Go

/*
---- Family member table
Insert into familylist
Select '1', '1', 'zhang san'
Union select '1', '2', 'three wives'
Union select '1', '3', 'three sub'
Union select '1', '4', 'Ladies and gentlemen'
Union select '2', '1', 'lily'
Union select '2', '2', 'Lee's wife'
Union select '2', '3', 'Li Sifu'
Union select '2', '4', 'Li simu'
Union select '3', '1', 'wang wu'
Union select '3', '2', 'wang wuyouwife'

Select * From familylist

*/

/*
---- Shopping supermarket table
Declare @ I int
Set @ I = 1
While @ I <11
Begin
Insert into supermarket (superm_name, superm_address)
Values ('supermarket '+ convert (char, @ I), 'supermarket address' + convert (char, @ I ))
Set @ I = (@ I + 1)
End

Select * from supermarket

*/

/*
---- Time and date table
Declare @ currdate datetime
Set @ currdate = convert (datetime, '2017/01 ')

While @ currdate <convert (datetime, '2017/01 ')
Begin
Insert into datelist
Values (@ currdate,
Datename (month, @ currdate ),
Datename (quarter, @ currdate ),
Datename (year, @ currdate ))

Set @ currdate = dateadd (day, 1, @ currdate)
End

Select * From datelist

*/

/*
---- Daily shopping record table
Declare @ I int, @ randid int

Set @ I = 1

While @ I <62 -- daily consumption
Begin
Set @ randid = @ I % 10

Insert into paylist (payerid, paydate, paysuperm, payprice, paynote)
Values (@ randid, @ I, @ randid, ceiling (100 * rand (@ I), 'shopping '+ convert (char, @ I ))
 
Set @ I = (@ I + 1 );
End

Select * From paylist
Order by paylist_id ASC

*/

========================================================== ===
2. Create a data warehouse database data source dimension multi-dimensional dataset
========================================================== ===
Data source:
Fact table
Paylist (payerid, paydate, payadd, payprice)
Dimension Table
Time Table datelist (date_id, the_date, the_month, the_quertar, the_year)
Supermarket (ID, supermarketname, address)
Family member table familylist (ID, family_id, cy_id, cy_name)
Multi-dimensional dataset
Paylist

==========
3. Run mdx
==========

Total shopping expenses of each household in each quarter
Select
{[Datelist]. [quarter]. Members} on columns,
{[Familylist]. [family ID]. Members} on rows
From
Paylist

Total spending on shopping for each household member in each quarter
Select
{[Datelist]. [quarter]. Members} on columns,
{[Familylist]. [Family list id]. Members} on rows
From
Paylist

Total quarterly shopping expenses of members of 1st families
Select
{[Familylist]. [All familylist]. [1]} on columns,
{[Datelist]. [month]. Members} on rows
From paylist

Monthly income of supermarkets
Select
{[Supermlist]. [Super m id]. Members} on columns,
{[Datelist]. [month]. Members} on rows
From paylist