Post on 19-Mar-2016
description
1
Sistemi informativi AvanzatiDB ORACLE SALES HISTORY
BROTTO FEDERICA, CONSOLI ALICE, ZANASI IGOR
GRUPPO MISSION RESOLUTION
Corso di Laurea Magistrale in Ingegneria Gestionale
A.A. 2013/2014
2
Ambito del ProgettoIl 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
3
SALES
CHANNELS
PRODUCTS
CUSTOMERS
TIMES 1,N
1,1
1,11,1 1,1
1,N
1,N
CITY 1,N
1,1
1,N
PROD_ID
CUSTOMER_ID
TIMES_ID
COUNTRIES_ID
CHANNEL_ID
COUNTRIESSTATE
PROVINCE1,11,11,N
SUBCATEGORY
CATEGORY1,N
1,N
1,N
1,1
1,1
SUBREGION_ID
REGION_ID
SALES IN PROMO
COSTS0,N
1,1
1,N
1,1
1,1
1,N
1,N1,N
STATEPROVINCE_IDCITY_ID
SUBCATEGORY_ID
CATEGORY_ID
UNITCOSTUNITPRICE
PROMOTIONS1,1
1,1
PROMO_ID
4
CHIAVI PRIMARIESi è 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 PRODUCTSGROUP BY PROD_IDHAVING COUNT(*)>1
(risulta insieme vuoto cioè non ci sono tuple di prod_id che si ripetono)
SELECT prod_idFROM PRODUCTSWHERE PROD_ID IS NULL
(risulta insieme vuoto cioè la condizione is null non si verifica mai)
5
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
6
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
7
ALBERO DEGLI ATTRIBUTI: SALES Attributi non significativi:COUNTRY_REGION_ID,COUNTRY_SUBREGION_ID…
8
DFM PER SALES
Schema di FattoTransazionale
Vantaggio: FLEX
Il Fatto esprime una relazione molti-a-moltitra le dimensioni
9
ALBERO DEGLI ATTRIBUTI: PROMOTIONS
10
DFM PER PROMOTIONS
Schema di Fatto Temporale
Si perde il livello di aggregazionecorrispondente a Channel_ID,ma non i livelli corrispondenti a Channel_DESC
11
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.
12
STAR SCHEMA PER SALESFACT_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)
13
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)
SNOWFLAKE SCHEMA PER SALES
14
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)
STAR SCHEMA PER PROMO
15
MISUREMISURE TIPO ALIMENTAZIONE AGGREGAZIONE NAQUANTITA_VENDUTA NORMALE SUM(s.quantity_sold) ADDITIVA ØRICAVI NORMALE MAX(s.AMOUNT_SOLD) ADDITIVA ØNUMERO_VENDITE NORMALE COUNT(*) ADDITIVA ØPREZZO_LISTINO_MASSIMO NORMALE MAX(prod_list_price) ADDITIVA ØPREZZO_LISTINO_SOMMA NORMALE SUM(prod_list_price) ADDITIVA ØPRODOTTO_MEDIO_LISTINO CALCOLATA SUM(prod_list_price)/count(*) ADDITIVA ØSCONTO CALCOLATA CASE WHEN MAX (PROD_LIST_PRICE) -MAX(S.AMOUNT_SOLD)<0 THEN '0'
ELSE MAX(PROD_LIST_PRICE) - MAX(S.AMOUNT_SOLD)ENDADDITIVA Ø
PERC_SCONTO CALCOLATA CASE WHEN 100-(SUM(PROD_LIST_PRICE)/SUM(S.AMOUNT_SOLD))*100 > 0 THEN '0' ELSE (100-(SUM(PROD_LIST_PRICE)/SUM(S.AMOUNT_SOLD))*100)*(-1)END
ADDITIVA Ø
SALES
PROMOTIONSMISURE TIPO ALIMENTAZIONE AGGREGAZIONE NACOSTO_UNITARIO NORMALE MAX (c.unit_cost) ADDITIVA ØPREZZO_UNITARIO NORMALE MAX(c.unit_price) ADDITIVA ØCOSTO_PROMO_TOT NORMALE MAX(PRO.PROMO_COST) ADDITIVA ØNUMERO_PROMO NORMALE COUNT(*) ADDITIVA Ø
16
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‘
17
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
18
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
19
DIAGRAMMA SALES
20
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
21
SCHEMA UNIVERSO
22
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
23
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.
24
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