ФАЗА 6 - НАПРЕДНА ТЕМА / Data Cube

Analitiki.sql

Овој дел од проектот претставува аналитички дел на базата за автосалон. Во кодот се креира посебна шема:

car_dealership_analytics

Оваа шема се користи за анализа на продажбите, приходите, попустите, клиентите, возилата, вработените и начините на плаќање.

Главна идеја

Во аналитичкиот дел се користи структура со димензионални табели и една главна факт табела.

Главната идеја може да се претстави вака:

DimDate
DimEmployee
DimCustomer
DimVehicle
DimPaymentType
DimContractType
        ↓
     FactSales

Димензионалните табели содржат описни податоци, а факт табелата FactSales ги содржи податоците за продажбите и приходите.

Димензионални табели

Во кодот се креираат следните димензионални табели:

• DimDate
• DimEmployee
• DimCustomer
• DimVehicle
• DimPaymentType
• DimContractType

DimDate содржи информации за датумот, како година, квартал, месец, ден, име на ден и дали денот е викенд.

DimEmployee содржи податоци за вработените, како employee id, име на вработен и позиција.

DimCustomer содржи податоци за клиентите, како customer id, име, email, телефон, град и држава.

DimVehicle содржи податоци за возилата, како VIN, бренд, модел, година, тип на возило, боја, година на производство и основна цена.

DimPaymentType ги содржи различните типови на плаќање.

DimContractType ги содржи различните типови на договори.

FactSales

Главна табела во аналитичкиот дел е FactSales.

Оваа табела ги поврзува сите димензии и ги чува главните мерки за продажбата.

Во FactSales се чуваат:

• sale_id
• payment_id
• date_key
• employee_key
• customer_key
• vehicle_key
• payment_type_key
• contract_type_key
• gross_revenue
• discount_percentage
• discount_amount
• net_revenue
• sale_count

Табелата FactSales користи foreign key врски кон сите димензионални табели.

Најважните мерки во оваа табела се:

gross_revenue       → приход пред попуст
discount_amount     → износ на попуст
net_revenue         → приход после попуст
sale_count          → број на продажби

Индекси

Во аналитичкиот дел се креираат индекси за побрзо извршување на аналитичките прашалници.

Индекси се креираат на FactSales според:

• employee_key
• customer_key
• vehicle_key
• payment_type_key
• contract_type_key
• date_key + vehicle_key
• date_key + contract_type_key

Исто така се креираат индекси и на димензионалните табели:

• DimDate(year, month)
• DimVehicle(brand, model)
• DimCustomer(city)

Овие индекси се користат за побрзо филтрирање, групирање и пребарување во аналитичките прашалници.

Обични аналитички views

Во кодот се креираат неколку аналитички views:

• RevenueByBrandAndYear
• RevenueByEmployeeAndMonth
• SalesByPaymentType
• RevenueByCustomerCity
• RevenueByVehicleType
• RevenueByContractType

Овие views служат за анализа на приходите од различни аспекти.

RevenueByBrandAndYear покажува приходи по бренд и година.

RevenueByEmployeeAndMonth покажува приходи по вработен и месец.

SalesByPaymentType покажува продажби и приходи според тип на плаќање.

RevenueByCustomerCity покажува приходи според град на клиентот.

RevenueByVehicleType покажува приходи според тип на возило.

RevenueByContractType покажува приходи според тип на договор.

Data Cube views

Во кодот има и materialized views кои користат Data Cube логика.

Главниот cube view е:

SalesCubeBrandYearPaymentContract

Овој view користи:

GROUP BY CUBE (dv.brand, dd.year, dpt.payment_type, dct.contract_type)

Со ова се добива анализа на продажби и приходи според:

• бренд
• година
• тип на плаќање
• тип на договор

Исто така се добиваат и вкупни вредности, на пример:

• сите брендови
• сите години
• сите типови на плаќање
• сите типови на договори

Во кодот се користи и функцијата GROUPING за да се означи дали редот е вкупен ред или ред за конкретна вредност.

SalesCubeBrandYearPayment

SalesCubeBrandYearPayment е view кој се базира на SalesCubeBrandYearPaymentContract.

Овој view ги прикажува податоците според:

• бренд
• година
• тип на плаќање

Притоа се земаат редовите каде што типот на договор е вкупен, односно:

is_contract_type_total = 1

EmployeeSalesRollup

EmployeeSalesRollup е materialized view кој прави анализа на продажбите според вработени.

Во него се анализираат:

• позиција
• employee_id
• име на вработен
• година
• вкупен приход
• попуст
• нето приход
• број на продажби
• просечна вредност на продажба

Овој view користи:

GROUP BY GROUPING SETS

Со тоа се добиваат различни нивоа на групирање, на пример по конкретен вработен, по позиција, по година и вкупно.

EmployeeCubePositionEmployeeYear

EmployeeCubePositionEmployeeYear е view кој ги зема податоците од EmployeeSalesRollup.

Овој view служи за полесно прикажување на податоците од rollup анализата за вработените.

VehicleCubeTypeBrandYear

VehicleCubeTypeBrandYear е materialized view кој прави cube анализа според возилата.

Овој view користи:

GROUP BY CUBE (dv.vehicle_type, dv.brand, dd.year)

Со ова се анализираат приходите според:

• тип на возило
• бренд
• година

Исто така се добиваат и вкупни вредности за сите типови на возила, сите брендови и сите години.

Analyze и проверки

По креирањето на табелите, views и materialized views, во кодот се извршува ANALYZE.

Тоа се прави за базата да ги освежи статистиките и подобро да ги извршува прашалниците.

Потоа има и дополнителни проверки:

• проверка на бројот на редови во FactSales
• проверка дали gross_revenue - discount_amount = net_revenue
• проверки дали индексите се користат
• проверки дали резултатите од views се совпаѓаат со рачно пресметани резултати

Краток заклучок

Овој дел од проектот претставува аналитички модел за автосалон.

Прво се креира посебна analytics шема. Потоа се креираат димензионални табели за датум, вработен, клиент, возило, тип на плаќање и тип на договор. Главната факт табела е FactSales, која ги содржи продажбите, приходите, попустите и нето приходот.

Потоа се креираат индекси за побрзи аналитички прашалници, како и повеќе views за анализа на приходите.

Најважниот дел е Data Cube делот, каде што со GROUP BY CUBE и GROUPING SETS се добиваат агрегирани резултати на повеќе нивоа, на пример по бренд, година, тип на плаќање, тип на договор, вработен и тип на возило.