Системи баз даних OLAP - javascript.org.ua

ВСТУП

Ласкаво просимо до блогу, присвяченого системам баз даних OLAP (Online Analytical Processing). Цей простір створено для пояснення як теоретичних, так і практичних аспектів цієї ключової технології в аналізі даних.

Основна мета блогу — надати чіткий і корисний ресурс для студентів і професіоналів, які хочуть глибше зрозуміти OLAP, включаючи вправи, що обговорюються на заняттях, та практичні посібники. Тут ви знайдете:

Вступ до концепцій та архітектури OLAP.
Порівняння з іншими моделями, такими як OLTP.
Детальні посібники по налаштуванню та використанню баз даних OLAP.
Розв'язані вправи та приклади з реальних кейсів.
Додаткові ресурси для безперервного навчання.

ТЕОРЕТИЧНА ЧАСТИНА

Основні концепції

Системи OLAP — це інструменти, розроблені для швидкого і ефективного аналізу великих обсягів даних, що зберігаються в багатовимірних базах даних.

Основні компоненти:

OLAP куби: Зберігають дані у багатовимірному форматі для полегшення швидких запитів.
Вимірювання (Dimensions): Представляють категорії, за якими аналізуються дані (наприклад, час, місцезнаходження, продукт).
Факти (Facts): Містять числові або інші показники, що мають значення для аналізу.

Приклади використання:

Аналіз продажів у роздрібній торгівлі.
Оцінка фінансової ефективності.
Дослідження споживчих патернів у телекомунікаціях.

Архітектура OLAP

OLAP проти OLTP:

OLTP (Online Transaction Processing) оптимізовано для частих та швидких транзакційних операцій, таких як реєстрація продажів або оновлення запасів.
OLAP призначено для складних запитів і аналізу великих масивів даних.

Потік даних між OLTP та OLAP:

Дані генеруються в системах OLTP.
Процес витягування, трансформації та завантаження (ETL) даних у сховище даних (data warehouse).
3.
> ОПИС

Вітаємо на блозі, присвяченому системам OLAP (Online Analytical Processing). Цей простір був створений для пояснення як теоретичних, так і практичних аспектів цієї ключової технології для аналізу даних.

Основна мета цього блогу — надати чіткий і корисний ресурс для студентів і професіоналів, які хочуть глибше вивчити OLAP, включаючи вправи, що розглядаються на заняттях, а також практичні посібники. Тут ви знайдете:

Вступ до концепцій та архітектури OLAP.
Порівняння з іншими моделями, такими як OLTP.
Детальні посібники щодо налаштування та використання баз даних OLAP.
Розв'язані вправи та реальні приклади.
Додаткові ресурси для безперервного навчання.

ПРАКТИЧНА ЧАСТИНА

Хмарні обчислення

Хмарні обчислення дозволяють зберігати та обробляти великі обсяги даних без необхідності наявності локальної інфраструктури.

Ключові постачальники:

Amazon Web Services (AWS)
Google Cloud Platform (GCP)
Microsoft Azure

Важливі сервіси:

Хмарні сховища даних: Amazon Redshift, Google BigQuery, Snowflake.
Обробка даних: Azure Synapse, AWS Glue.

ВИРІШЕННЯ КЕЙСІВ

Кейс: Datapath хоче розробити сховище даних (DW) для зберігання історичних даних ПРОДАЖІВ таким чином, щоб воно допомагало розробляти проекти для аналізу даних, що підтримують прийняття рішень.

Розробка:

Фізична схема: Кейс з продажів Datapath

Створення та використання бази даних “dbventasdatapath”_

#створення бази даних  
CREATE DATABASE dbventas_datapath;  

#використовуємо створену базу даних  
USE dbventas_datapath;

Створення таблиці “vendedor”

/* СТВОРЮЄМО ТАБЛИЦЮ VENDEDOR */  
CREATE TABLE vendedor (  
 idvendedor INTEGER PRIMARY KEY NOT NULL,  
 nombre VARCHAR(100) NOT NULL,  
 sexo CHAR(1) NOT NULL,  
 dirección VARCHAR(100)  
);  

/* ВСТАВКА ПРОДАВЦІВ */  
INSERT INTO vendedor VALUES   
(1,"Fredy Saavedra","M","AV la merced - Colombia"),   
(2,"Maria Cisneros","F","jr manco capac 118 SJM"),  
(3,"Deisser Martinez","M","pasaje la cultura 234 Arequipa Perú");

Створення таблиці “ventasprograma”_

/*СТВОРЮЄМО ТАБЛИЦЮ VENTAS_PROGRAMA*/  
CREATE TABLE ventas_programa (  
 idventas_programa INTEGER PRIMARY KEY NOT NULL,  
 idvendedor INTEGER,  
 idEstudiante VARCHAR(8) NOT NULL,  
 idPrograma INTEGER NOT NULL,  
 costoVenta DECIMAL (10,2),  
 CONSTRAINT fk_ventas_programa_vendedor FOREIGN KEY (idvendedor) REFERENCES vendedor (idvendedor)  
);  

/*ВСТАВКА ЗНАЧЕНЬ У ТАБЛИЦЮ VENTAS_PROGRAMA*/  
INSERT INTO ventas_programa VALUES  
(1,1,"23423442",1,450),  
(2,2,"23423525",2,350),  
(3,3,"34534573",3,500),  
(4,1,"34534576",4,500),  
(5,1,"34534634",5,300),  
(6,2,"34634636",6,700),  
(7,2,"34636567",7,500),  
(8,1,"45645647",6,500),  
(9,3,"45646647",5,400),  
(10,3,"65756756",7,300)

СТВОРЕННЯ БАГАТОВИМІРНОЇ МОДЕЛІ

Створення бази даних “dwventasdatapath”_

#створюємо базу даних  
CREATE DATABASE dwventas_datapath;  

#використовуємо створену базу даних  
USE dwventas_datapath;

Створення відповідних таблиць

/*створюємо таблицю клієнта*/  
CREATE TABLE DimCliente (  
 idCliente VARCHAR(8) PRIMARY KEY,  
 nombres VARCHAR (50),  
 direccion VARCHAR (100)  
);  

/*створюємо таблицю DimPrograma*/  
CREATE TABLE DimPrograma (  
 idPrograma INTEGER PRIMARY KEY,  
 descripcion VARCHAR (100),  
 inversion DECIMAL (10,2)  
);  

/*створюємо таблицю DimTiempo*/  
CREATE TABLE DimTiempo (  
 idTiempo DATE PRIMARY KEY,  
 dia VARCHAR (50),  
 mes VARCHAR (50),  
 año INTEGER  
);  

/*створюємо таблицю DimVendedor*/  
CREATE TABLE DimVendedor (  
 idVendedor INTEGER PRIMARY KEY,  
 nombres VARCHAR (100),  
 sexo CHAR,  
 direccion VARCHAR (100)  
);  

/*створюємо таблицю Factventas*/  
CREATE TABLE FactVentas (  
 idCliente VARCHAR(8),  
 idPrograma INTEGER,  
 idTiempo DATE,  
 idVendedor INTEGER,  
 costoVenta DECIMAL (10,2),  
 cantidadCuotas INTEGER,  
 CONSTRAINT fk_ventas_cliente FOREIGN KEY (idCliente) REFERENCES DimCliente (idCliente),  
 CONSTRAINT fk_ventas_programa FOREIGN KEY (idPrograma) REFERENCES DimPrograma (idPrograma),  
 CONSTRAINT fk_ventas_fecha FOREIGN KEY (idTiempo) REFERENCES DimTiempo (idTiempo),  
 CONSTRAINT fk_ventas_vendedor FOREIGN KEY (idVendedor) REFERENCES DimVendedor (idVendedor)  
);

Вставка даних до таблиць багатовимірної моделі

/* ВИКОРИСТОВУЄМО СТВОРЕНУ БАЗУ ДАНИХ */  
USE dwventas_datapath;  

/* Заповнення таблиці Cliente */

SELECT dni_estudiante,nombre,domicilio  
FROM academico_datapath.estudiante;  

INSERT INTO dimcliente (idCliente,nombres,direccion)   
SELECT dni_estudiante,nombre,domicilio  
FROM academico_datapath.estudiante ;  

/* Заповнення таблиці Programa */  

SELECT codigo_programa,descripcion,inversion  
FROM academico_datapath.programa;  

INSERT INTO dimprograma   
SELECT codigo_programa,descripcion,inversion  
FROM academico_datapath.programa;  

/* Заповнення таблиці Vendedor */  
SELECT *  
FROM dbventas_datapath.vendedor;  

INSERT INTO dimvendedor   
SELECT *  
FROM dbventas_datapath.vendedor;  

/* Отримання таблиці дат */  

SET @fecha_inicio := "2023-01-01" ;  
SET @fecha_fin := "2023-03-25" ;  

INSERT INTO dimtiempo   
SELECT selected_date AS idTiempo , DAY(selected_date) as dia,MONTH(selected_date) as mes, YEAR(selected_date) AS anio   

##Таблиця дат між початковою та кінцевою датами  

FROM   
 (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from  
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,  
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,  
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,  
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,  
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v  
where selected_date between @fecha_inicio and @fecha_fin;  

/* Заповнення таблиці фактів factventas */  

SET FOREIGN_KEY_CHECKS=0;  
INSERT INTO factventas (idCliente,idPrograma,idTiempo,idVendedor, costoVenta)  

SELECT   
matricula.dni_estudiante AS idCliente,  
matricula.codigo_programa AS idPrograma,  
matricula.fecha_matricula AS idTiempo,  

 venta.idVendedor as idVendedor,  
 venta.costoVenta as costoVenta  

FROM academico_datapath.programa_estudiante AS matricula  
LEFT JOIN dbventas_datapath.ventas_programa AS venta  
ON matricula.dni_estudiante = venta.idEstudiante

Запропоновані вправи

1.
Програми, ціна яких менше за 600 доларів

SELECT  
 descripcion AS 'Опис програми',  
 inversion AS 'Сума інвестицій'  
FROM dimprograma  
WHERE inversion < 600

Перерахуйте програми, що коштують від 300 до 500 доларів

SELECT  
 P.descripcion AS 'Програма',  
 V.MontoFinalVenta AS 'Вартість'  
FROM factventas AS V  
JOIN dimprograma AS P  
ON V.idPrograma = P.idPrograma  
WHERE V.MontoFinalVenta BETWEEN 300 AND 500;

Перерахуйте програми, що коштують 700 або 800 доларів

SELECT  
 P.descripcion AS 'Програма',  
 V.MontoFinalVenta AS 'Вартість'  
FROM factventas AS V  
JOIN dimprograma AS P  
ON V.idPrograma = P.idPrograma  
WHERE V.MontoFinalVenta BETWEEN 700 AND 800;

Перерахуйте тільки програми для Data Analyst

SELECT  
 idPrograma AS 'Код програми',  
 descripcion AS 'Опис програми'  
FROM dimprograma  
WHERE descripcion LIKE '%Data Analyst%';

Яка версія програми Data Analyst коштує більше?

SELECT  
 descripcion AS 'Опис програми',  
 inversion AS 'Сума інвестицій'  
FROM dimprograma  
WHERE LOWER(descripcion) LIKE '%data analyst%'  
ORDER BY inversion desc  
LIMIT 1;

Додаткові ресурси

Книги:

“The Data Warehouse Toolkit” Ральфа Кімбала.
“OLAP Solutions” Еріка Томсена.

Статті:

“OLAP and Data Mining: Bridging the Gap” (ResearchGate).

Інструменти та відео:

Серія туторіалів по Pentaho на YouTube.
Документація Google BigQuery.

Сподіваюся, цей блог стане корисним посібником для вашого навчання та застосування систем OLAP.

Перекладено з: Sistemas de Base de Datos OLAP