Sistemi informativi Avanzati
DB ORACLE SALES HISTORY
BROTTO FEDERICA, CONSOLI ALICE, ZANASI IGOR
GRUPPO MISSION RESOLUTION
Corso di Laurea Magistrale in Ingegneria Gestionale
A.A. 2013/2014
1
Ambito del Progetto
Il database preso in esame presenta una parte legata alle vendite e una alle riparazioni.
Nell’ambito del progetto si è scelto di considerare solo la parte relativa alle vendite analizzando
due fatti: SALES e PROMOTIONS.
Il progetto è stato suddiviso in 3 fasi
1. Modello
Concettuale e
Modello Logico
2. ETL E DM
3. REPORTING
2
1,1
1,1
SALES IN
PROMO
UNITCOST
0,N
1,1
PROMOTIONS
1,N
1,N
CHANNELS
UNITPRICE
1,N
1,1
COSTS
CHANNEL_ID
1,N
1,1
1,1
PROMO_ID
PROD_ID
1,N
SALES
1,1
TIMES
1,1
1,N
1,N
SUBCATEGORY
CUSTOMERS
1,1
COUNTRIES_ID
STATEPROVINCE_ID
1,1
1,N
1,N
CUSTOMER_ID
COUNTRIES 1,N
1,1
1,1
TIMES_ID
REGION_ID
PRODUCTS
STATE 1,N
PROVINCE
CITY_ID
1,1
CITY
1,N
SUBCATEGORY_ID
1,1
1,N
CATEGORY
CATEGORY_ID
SUBREGION_ID
3
CHIAVI PRIMARIE
Si è verificato che le chiavi primarie rispettano i criteri di unicità e not null.
Esempio con la chiave prod_id per PRODUCTS
SELECT prod_id, PRODUCTS=COUNT(*)
FROM PRODUCTS
GROUP BY PROD_ID
HAVING COUNT(*)>1
(risulta insieme vuoto cioè non ci sono tuple di prod_id che si ripetono)
SELECT prod_id
FROM PRODUCTS
WHERE PROD_ID IS NULL
(risulta insieme vuoto cioè la condizione is null non si verifica mai)
4
CHIAVI ALTERNATIVE
Nella tabella COUNTRIES country_iso_code è chiave alternativa a country_id
Si può verificare tramite la seguente interrogazione del DB:
SELECT COUNTRY_ISO_CODE, COUNTRIES=COUNT(*)
FROM COUNTRIES
GROUP BY COUNTRY_ISO_CODE
HAVING COUNT(*)>1
la condizione not null è verificata.
Nello stesso modo è stato verificato che sono chiavi alternative:
- Nella tabella PROMOTIONS la chiave alternativa a promo_id è promo_name
- Nella tabella CHANNELS la chiave alternativa a channel_id è channel_desc
5
DIPEDENZE FUNZIONALI
Nel fatto Sales c’è dipendenza funzionale tra le dimensioni:
FD: {TIMES,PRODUCTS,CHANNELS,CUSTOMERS,COUNTRIES}
SALES
Altre dipendenze funzionali:
SubcategoryID
CategoryID
City_id
State Province_id
State Province
Countries
Query a titolo di esempio per
select PROD_SUBCATEGORY_ID
from PRODUCTS
group by PROD_SUBCATEGORY_ID
having COUNT (distinct PROD_CATEGORY)>1
Oss: Si è verificato che due città (Montreal e Bradford) presentano lo stesso nome pur essendo in stati diversi, tuttavia
l’identificatore city_id è univoco
6
ALBERO DEGLI ATTRIBUTI: SALES
Attributi non significativi:
COUNTRY_REGION_ID,
COUNTRY_SUBREGION_ID…
7
DFM PER SALES
Il Fatto esprime una
relazione molti-a-molti
tra le dimensioni
Schema di Fatto
Transazionale
Vantaggio: FLEX
8
ALBERO DEGLI ATTRIBUTI: PROMOTIONS
9
DFM PER PROMOTIONS
Si perde il livello di aggregazione
corrispondente a Channel_ID,
ma non i livelli corrispondenti a
Channel_DESC
Schema di Fatto Temporale
10
2. PROGETTO LOGICO
La modellazione concettuale è indipendente da quella logica, ma non viceversa .Prima era una rappresentazione astratta in forma
grafica, indipendente dall’ implementazione con lo svantaggio che i dati non erano comprensibili dall’utente finale , ci si basava
sul modello concettuale del DFM. In questo caso la progettazione logica ha come obiettivo una rappresentazione strettamente
legata al sistema scelto per l ‘implementazione; lo schema dei dati è utile per semplificare e ottimizzare le operazioni di
manipolazione e interrogazione dei dati. Si usa come Modello logico quello relazionale che si distingue per tre modelli differenti :
• MOLAP = multidimensional on-line analytical processing (strutture multidimensionali) è frenato dalla mancanza di strutture dati
standard ; vengono usati dati strettamente personalizzati che li rendono difficilmente sostituibili e accessibili mediante strumenti
di terze parti.
• ROLAP relazionale snowflake e star schema;
• HOLAP soluzioni ibride che sfruttano le proprietà di entrambi i modelli : il DW ROLAP è ottimale per memorizzare enormi
quantità di dati
IL DM MOLAP massimizzano la velocità di accesso ai dati, ( CUBI MOLAP )possono essere creati al volo per svolgere specifiche
sessioni di analisi.
11
STAR SCHEMA PER SALES
FACT_TABLE_SALES(CUST_ID:DT_CUSTOMER, CHANNEL_ID:DT_CHANNEL, TIME_ID:DT_TIME,PROD_ID:DT_PRODUCTS,PROMO_ID:
DT_SALES_IN_PROMO quantità_venduta, prezzo_listino, ricavi, sconto)
DT_CUSTOMERS(CUST_ID,CUST_LAST_NAME,CUST_FIRST_NAME,CUST_GENDER,CUST_YEAR_OF_BIRTH,CUST_MARITAL_STATUS,CUST
_CITY_ID,CUST_STATE_PROVINCE_ID,CUST_STREET_ADDRESS,CUST_POSTAL_CODE,COUNTRY_ID,COUNTRY_NAME,COUNTRY_REGION
, COUNTRY_SUBREGION)
DT_CHANNELS(CHANNEL_ID,CHANNEL_CLASS,CHANNEL _CLASS_ID,CHIANNEL_DESC)
DT_TIMES(TIME_ID,CALENDAR_YEAR,CALENDAR_MOUTH_NAME,CALENDAR_MONTH_NUMBER, CALENDAR_MONTH_DESC)
DT_PRODUCTS(PROD_ID,PROD_NAME,PROD_SUBCATEGORY,PROD_CATEGORY)
DT_SALES _IN_PROMO(PROMO_ID)
12
SNOWFLAKE SCHEMA PER SALES
FACT_TABLE_SALES(CUST_ID:DT_CUSTOMER, CHANNEL_ID:DT_CHANNEL, TIME_ID:DT_TIME,PROD_ID:DT_PRODUCTS,
quantità_venduta , prezzo_listino, ricavi, sconto)
DT_CUSTOMERS(CUST_ID,CUST_LAST_NAME,CUST_FIRST_NAME,CUST_GENDER,CUST_YEAR_OF_BIRTH,CUST_MARITAL _STATUS,
CUST_STREET_ADDRESS,CUST_POSTAL_CODE,CUST_CITY_ID:DT_CITY )
DT_CITY(CUST_CITY_ID,CUST_CITY,CUST_STATE_PROVINCE:DT_STATEP
DT_STATEP(CUST_STATE_PROVINCE_ID, CUST_STATE_PROVINCE,COUNTRY_ID:DT_COUNTRY)
DT_COUNTRY(COUNTRY_ID,COUNTRY_NAME,COUNTRY_SUBREGION:DT_SUBREGION)
DT_SUBREGION(SUBREGION,REGION)
DT_CHANNELS(CHANNEL_ID,CHANNEL_CLASS,CHANNEL _CLASS_ID,CHIANNEL_DESC)
DT_PRODUCTS(PROD_ID,PROD_NAME,PROD_SUBCATEGORY:DT_SUBCATEGORY)
DT_SUBCATEGORY (SUBCATEGORY_ID,CATEGORY_ID,CATEGORY)
DT_TIMES(TIME_ID,CALENDAR_MONTH_DESC,CALENDAR_YEAR,CALENDAR_MONTH_NUMBER,CALENDAR_MONTH_NAME)
DT_SALES_IN_PROMO(PROMO_ID)
13
STAR SCHEMA PER PROMO
FACT_TABLE_PROMO(PROMO_ID:PROMOTIONS,PROD_ID:DT_PRODUCTS,TIME_ID:DT_TIMES,CHANNEL_DESC:DT_CHANNELS,
costo_unitario,prezzo_unitario,costo_promo_tot,numero_promo)
DT_PRODUCTS (PROD_ID,PROD_NAME,PROD_SUBCATEGORY,PROD_CATEGORY)
DT_TIMES(TIME_ID,CALENDAR_MONTH_DESC,CALENDAR_YEAR,CALENDAR_MONTH_NUMBER,CALENDAR_MONTH_NAME)
DT_CHANNELS(CHANNEL_DESC)
14
MISURE
SALES
MISURE
TIPO
QUANTITA_VENDUTA
RICAVI
NUMERO_VENDITE
PREZZO_LISTINO_MASSIMO
PREZZO_LISTINO_SOMMA
PRODOTTO_MEDIO_LISTINO
SCONTO
NORMALE
NORMALE
NORMALE
NORMALE
NORMALE
CALCOLATA
CALCOLATA
PERC_SCONTO
CALCOLATA
ALIMENTAZIONE
SUM(s.quantity_sold)
MAX(s.AMOUNT_SOLD)
COUNT(*)
MAX(prod_list_price)
SUM(prod_list_price)
SUM(prod_list_price)/count(*)
CASE WHEN MAX (PROD_LIST_PRICE) -MAX(S.AMOUNT_SOLD)<0 THEN '0'
ELSE MAX(PROD_LIST_PRICE) - MAX(S.AMOUNT_SOLD)END
CASE
WHEN 100-(SUM(PROD_LIST_PRICE)/SUM(S.AMOUNT_SOLD))*100 > 0 THEN '0'
AGGREGAZIONE
NA
ADDITIVA
ADDITIVA
ADDITIVA
ADDITIVA
ADDITIVA
ADDITIVA
ADDITIVA
Ø
Ø
Ø
Ø
Ø
Ø
Ø
ADDITIVA
Ø
ELSE (100-(SUM(PROD_LIST_PRICE)/SUM(S.AMOUNT_SOLD))*100)*(-1)END
PROMOTIONS
MISURE
TIPO
ALIMENTAZIONE
AGGREGAZIONE
NA
COSTO_UNITARIO
PREZZO_UNITARIO
COSTO_PROMO_TOT
NUMERO_PROMO
NORMALE
NORMALE
NORMALE
NORMALE
MAX (c.unit_cost)
MAX(c.unit_price)
MAX(PRO.PROMO_COST)
COUNT(*)
ADDITIVA
ADDITIVA
ADDITIVA
ADDITIVA
Ø
Ø
Ø
Ø
15
MIGLIORIE APPORTATE AI DATI
 GESTIONE VALORI NULL
- CREATE VIEW DT_CUSTOMERS AS
SELECT customers.CUST_ID,customers.CUST_LAST_NAME,customers.CUST_FIRST_NAME,customers.CUST_GENDER,customers.CUST_YEAR_OF_BIRTH,
CUST_MARITAL_STATUS = COALESCE(CUST_MARITAL_STATUS,'NON_PERVENUTO'),
countries.COUNTRY_ID,countries.COUNTRY_NAME,countries.COUNTRY_REGION_ID,countries.COUNTRY_REGION,countries.COUNTRY_SUBREGION_ID,
countries.COUNTRY_SUBREGION
FROM CUSTOMERS LEFT JOIN COUNTRIES ON (customers.country_id=countries.COUNTRY_ID)
- CREATE VIEW A AS
SELECT S.PROD_ID,PROMO=CASE WHEN SP.PROMO_ID IS NULL THEN 999 ELSE SP.PROMO_ID END
FROM SALES S LEFT JOIN SALES_IN_PROMO SP ON (S.PROD_ID=SP.PROD_ID)
GROUP BY S.PROD_ID,SP.PROMO_ID
 CORREZIONI ORTOGRAFICHE
UPDATE PROMOTIONS
SET PROMO_SUBCATEGORY = 'NO PROMOTION'
WHERE PROMO_SUBCATEGORY = 'NO RPOMOTION‘
16
3.PRESENTAZIONE ETL E DATA MART
Creazione del database: inizializzazione in MS SQL Server
Management Studio del database in cui saranno introdotte le
tabelle del Data Mart.
Creazione delle viste: creazione degli script SQL per la
realizzazione delle viste relative alle Dimension Table ed alle Fact
Generazione delle tabelle: creazione degli script SQL per
l’alimentazione delle Dimension Table e delle Fact Table nel Data
Mart.
Definizione delle chiavi: creazione del diagramma del Data Mart
mediante l’importazione delle Fact Table e delle Dimension
17
FACT_TABLE_SALES
CREATE VIEW [dbo].[FACT_TABLE_SALES] AS
SELECT S.CHANNEL_ID,S.CUST_ID,S.PROD_ID,S.TIME_ID,
RICAVI = MAX(s.AMOUNT_SOLD),
NUMERO_VENDITE=COUNT(*),
quantità_venduta = SUM(s.quantity_sold),
prezzo_listino_MASSIMO= MAX(prod_list_price)
SCONTO= CASE WHEN MAX (PROD_LIST_PRICE)-MAX(S.AMOUNT_SOLD)<0 THEN '0' ELSE
MAX(PROD_LIST_PRICE) - MAX(S.AMOUNT_SOLD)END,
Promo_id=max(promo_id)
( DIMENSIONE DEGENERE)
FROM SALES S JOIN CHANNELS CH ON (S.CHANNEL_ID=CH.CHANNEL_ID)
JOIN CUSTOMERS CU ON (S.CUST_ID=CU.CUST_ID)
JOIN PRODUCTS P ON (S.PROD_ID=P.PROD_ID)
JOIN TIMES T ON (S.TIME_ID=T.TIME_ID)
◦
LEFT JOIN SALES_IN_PROMO SP ON (SP.PROD_ID=S.PROD_ID and S.TIME_ID=SP.TIME_ID AND S.PROD_ID=SP.PROD_ID AND
S.CUST_ID=SP.CUST_ID)
◦ GROUP BY S.CHANNEL_ID,S.CUST_ID,S.PROD_ID,S.TIME_ID
18
DIAGRAMMA SALES
19
PRESENTAZIONE REPORTING
in SAP Business Objects
Introduzione a BO
Business Objects è uno strumento per la consultazione dei dati e per la produzione di reportistica. Esso infatti consente di:
- modellizzare una base dati
- realizzare dei report sulla base dei modelli prodotti
Designer è stato utilizzato per costruire un modello semantico della base dati detto “universo”.
L’universo ha lo scopo di semplificare la fase di interrogazione e consultazione del database da parte dell’utente finale e quindi la
creazione dei report attraverso il modulo Desktop Intelligence
20
SCHEMA UNIVERSO
21
Report 1
Fatto analizzato: Sales
Dimensioni: prodotto, area geografica e tempo
Misure: quantità venduta, % quantità venduta sul totale
Obiettivo: valutare la copertura dei mercati e i settori con maggior numero di vendite
Conclusioni: Americhe coprono il 60% della quantità venduta totale, nel settore Software si
concentrano la maggior parte delle vendite.
REP.FINALE\DEFINITIVI\REPORT1.pdf
22
Report 2
Fatto analizzato: Promotions
Dimensioni: promo_id, anno
Misure: ricavo, % incidenza
Obiettivo: valutare la % di ricavi dovuta a vendite
in promo e non.
Conclusioni: in tutti gli anni presi in considerazione
il 90% dei ricavi è dovuto a vendite non
in promozione.
23
Report 3
Fatto: Promo
Dimensioni: prodotto,canale,tempo
Misure: numero promo
Obiettivo: valutare il numero di promozioni effettuate
Conclusioni: es. anno:2000, canale:internet si nota che le sottocategorie con il maggior numero
di promo sono Recordarbable disch
24