Il BACKUP è disponibile in

(1)

ESEMPIO DELLE VENDITE: MISURE ED AGGREGABILITA’

E’ l’esempio discusso nelle dispense

è Dispense : http://www.dbgroup.unimo.it/SIA/SIA_2014_ProgettazioneDiUnDW_MIsure.pdf

esteso e dettagliato.

Il BACKUP è disponibile in

http://www.dbgroup.unimo.it/SIA/Esercizio_21_Novembre_2013/Esercizio_21_Novembre_2013.bak

Si consiglia di ripristinare il DBO e di provare tutte le query riportate nel seguito!

DBO

Rispetto al DBO delle dispense è stato aggiunto l’attributo AZIENDA dell’entità TIPO

Inoltre l’attributo MESE non è indicato esplicitamente in quanto si intende derivato dalla DATA, prendendo i primi tre caratteri

Schema Relazionale ed Istanze del DBO

PRODOTTO TIPO SCONTRINO VENDITE

Si noti che sulle dispense è stata considerata per semplicità di esposizione la view VENDITA riportata qui sulla destra,

in modo da visualizzare anche la DATA

SCONTRINO VENDITA

PRODOTTO (1,N) (1,N)

PREZZO

TIPO (1,1)

(1,N)

AZIENDA

QTY DATA

(2)

2 Si vogliono considerare le seguenti misure

GLOSSARIO DELLE MISURE

1. NUMERO_VENDITE = COUNT(*) 2. PREZZO_MEDIO = AVG (PREZZO)

3. NUMERO_CLIENTI = COUNT(DISTINCT SCONTRINO) 4. NUMERO_PRODOTTI = COUNT(DISTINCT PRODOTTO) 5. QTY= SUM(QTY)

6. INCASSO= SUM(QTY*PREZZO)

Si noti che nel GLOSSARIO c’è la definizione delle misure rispetto al DBO; sulla base di questa definizione è possibile calcolare le misure rispetto ad ogni possibile raggruppamento.

Ad esempio:

Per ottenere i valori relativi al pattern {TIPO e MESE}

Il MESE non è presente direttamente nel DBO, ma deve essere ricavato dalla DATA; dopo aver

verificato sul DBO che DATA (in questo esempio) è una stringa, si ricerca una funzione stringa per SQL SERVER, si ottiene

http://msdn.microsoft.com/it-‐it/library/ms181984.aspx

MESE=SUBSTRING(DATA,1,3)

SELECT TIPO, MESE=SUBSTRING(DATA,1,3), NUMERO_VENDITE=COUNT(*),

PREZZO_MEDIO=AVG(PREZZO), QTY=SUM(QTY),

INCASSO=SUM(QTY*PREZZO),

NUMERO_PRODOTTI=COUNT(DISTINCT VENDITE.PRODOTTO), NUMERO_CLIENTI=COUNT(DISTINCT VENDITE.SCONTRINO)

FROM VENDITE JOIN SCONTRINO ON VENDITE.SCONTRINO = SCONTRINO.SCONTRINO JOIN PRODOTTO on PRODOTTO.PRODOTTO = VENDITE.PRODOTTO

GROUP BY TIPO, SUBSTRING(DATA,1,3)

Questa interrogazione serve per comprendere il significato delle misure, valutandole direttamente sul DBO; come vedremo è molto simile all’interrogazione per creare la vista FACT_TABLE (quindi non cancellate il codice …)

Per i valori relativi al pattern vuoto (senza raggruppare), basta eliminare il GROUP BY

Nel seguito si discute come le misure devono essere riportate nel DATAMART, considerando vari schemi di fatto sullo stesso DBO, a variare delle dimensioni scelte.

(3)

CASO 1) DIMENSIONI = {TIPO,MESE}, TEMPORALE

NOTA: Nel seguito è evidenziato cosa occorre riportare nella consegna

ETL e DM (30/11), supponendo di aver già realizzato lo (gli) schemi di fatto

A) MISURE

Nella consegna ETL e DM occorre riportare la definizione delle misure come discussa nel seguito

GLOSSARIO DELLE MISURE

1. NUMERO_VENDITE = COUNT(*) 2. PREZZO_MEDIO = AVG (PREZZO)

3. NUMERO_CLIENTI = COUNT(DISTINCT SCONTRINO) 4. NUMERO_PRODOTTI = COUNT(DISTINCT PRODOTTO) 5. QTY= SUM(QTY)

6. INCASSO= SUM(QTY*PREZZO)

Per ogni misura occorre definire

1) TIPOLOGIA della misura: Normale, calcolata, derivata, misura vuota

2) ALIMENTAZIONE: Cosa deve essere messo nella FACT_TABLE e come deve essere calcolato 3) AGGREGAZIONE: L’operatore di aggregazione da usare nel DATAMART; per le misure calcolate

occorre definire l’operatore di aggregazione delle misure componenti 4) NON AGGREGABILITA: Eventuali non aggregabilità

Consideriamo le sei misure : NUMERO_VENDITE = COUNT(*)

TIPOLOGIA : E’ una misura normale (quindi da riportare nella FACT_TABLE) ALIMENTAZIONE: COUNT(*)

AGGREGAZIONE: ADDITIVA

NON AGGREGABILITA : nessuna (additiva rispetto a tutte le dimensioni)

QTY= SUM(QTY)

TIPOLOGIA : E’ una misura normale ALIMENTAZIONE: SUM(QTY)

INCASSO= SUM(QTY*PREZZO)

TIPOLOGIA : E’ una misura normale ALIMENTAZIONE: SUM(QTY*PREZZO) AGGREGAZIONE: ADDITIVA

VENDITA

AZIENDA

TIPO MESE

(4)

4

PREZZO_MEDIO= AVG(PREZZO)

TIPOLOGIA : E’ una misura calcolata, PREZZO_SUM/PREZZO_COUNT ALIMENTAZIONE: PREZZO_SUM=SUM(PREZZO),

PREZZO_COUNT=COUNT(PREZZO)

AGGREGAZIONE: PREZZO_SUM e PREZZO_COUNT sono additive NON AGGREGABILITA : nessuna

NUMERO_CLIENTI = COUNT(DISTINCT SCONTRINO) TIPOLOGIA : E’ una misura normale

ALIMENTAZIONE: COUNT(DISTINCT SCONTRINO)

AGGREGAZIONE: ADDITIVA rispetto a MESE (in quanto SCONTRINOà MESE) NON AGGREGABILITA : NA = {TIPO}

NUMERO_PRODOTTI = COUNT(DISTINCT PRODOTTO) TIPOLOGIA : E’ una misura normale

ALIMENTAZIONE: COUNT(DISTINCT PRODOTTO)

AGGREGAZIONE: ADDITIVA rispetto a TIPO (in quanto PRODOTTOà TIPO) NON AGGREGABILITA : NA = {MESE}

Eventuali “semplificazioni” :

dopo aver verificato che PREZZO non è NULL, siccome in tal caso COUNT(PREZZO)=COUNT(*), si decide di non riportare PREZZO_COUNT nella FACT_TABLE ma usare NUMERO_VENDITE, cioè

PREZZO_MEDIO = PREZZO_SUM/ NUMERO_VENDITE

(5)

B) SCHEMA LOGICO Schema Logico “su carta” ; se nella tesina

si hanno due schemi di fatto, per uno (a scelta) riportare lo star-‐schema, per l’altro lo snow-‐flake.

Se si ha un solo schema di fatto, riportare prima lo snow-‐flake schema e poi lo star-‐schema.

FACT_TABLE(MESE,TIPO:DT_TIPO,

NUMERO_VENDITE, QTY, INCASSO, PREZZO_SUM,PREZZO_COUNT, NUMERO_CLIENTI , NUMERO_PRODOTTI )

DT_TIPO(TIPO,AZIENDA)

In questo semplice caso lo star-‐schema e lo snow-‐flake coincidono.

C) VISTE PER L’ALIMENTAZIONE

Si riporta lo script (codice SQL) della creazione delle viste corrispondenti alla FACT_TABLE e alle DIMENSION TABLE; queste viste devono essere create sul DBO.

Il MESE non è presente direttamente nel DBO, ma deve essere ricavato dalla DATA; dopo aver

verificato sul DBO che DATA (in questo esempio) è una stringa, si ricerca una funzione stringa per SQL SERVER, si ottiene la seguente pagina web con il manuale SQL SERVER

http://msdn.microsoft.com/it-‐it/library/ms181984.aspx

MESE=SUBSTRING(DATA,1,3)

quindi

CREATE VIEW FACT_TABLE AS

SELECT TIPO, MESE=SUBSTRING(DATA,1,3), NUMERO_VENDITE=COUNT(*),

PREZZO_SUM=SUM(PREZZO), QTY=SUM(QTY),

FROM VENDITE JOIN SCONTRINO ON VENDITE.SCONTRINO = SCONTRINO.SCONTRINO

JOIN PRODOTTO on PRODOTTO.PRODOTTO = VENDITE.PRODOTTO GROUP BY TIPO, SUBSTRING(DATA,1,3)

Si noti che è possibile raggruppare anche su funzioni di un attributo : SUBSTRING(DATA,1,3).

Un modo alternativo di procedere è il seguente

Devo calcolare MESE; mi chiedo da cosa dipende MESE ? ovviamente DATA à MESE Quindi creo una view in cui metto la DATA e MESE calcolato sulla data

(6)

6 CREATE VIEW MESE(DATA,MESE)

AS

SELECT DISTINCT DATA, MESE=SUBSTRING(DATA,1,3) FROM SCONTRINO

Grazie al DISTINCT ed al fatto che MESE è calcolato sulla DATA, deriva che nella view MESE l’attributo DATA sia chiave.

La view MESE viene utilizzata nella creazione della view FACT_TABLE

ALTER VIEW FACT_TABLE AS

SELECT TIPO, MESE,

NUMERO_VENDITE=COUNT(*), PREZZO_SUM=SUM(PREZZO), QTY=SUM(QTY),

FROM VENDITE JOIN SCONTRINO ON VENDITE.SCONTRINO = SCONTRINO.SCONTRINO

JOIN PRODOTTO on PRODOTTO.PRODOTTO = VENDITE.PRODOTTO JOIN MESE ON (SCONTRINO.DATA=MESE.DATA)

GROUP BY TIPO, MESE

La DT_TIPO coincide con la tabella TIPO, quindi CREATE VIEW DT_TIPO

AS SELECT * FROM TIPO

(7)

C) CREAZIONE DEL DM e SUA ALIMENTAZIONE

Deve essere creato un nuovo database (il DM) e deve essere alimentato tramite le viste create al punto precedente:

a. Si crea un nuovo database : lo chiamiamo DM_VENDITE

b. Si creano le table di DM_VENDITE, alimentandole con le relative view

SELECT * INTO DM_VENDITE.DBO.FACT_TABLE FROM FACT_TABLE

SELECT * INTO DM_VENDITE.DBO.DT_TIPO FROM DT_TIPO

c. Si crea il diagramma relazionale di DM_VENDITE, definendo le key/foreign key

(8)

8 NOTE TECNICHE SUI DIAGRAMMI RELAZIONALI in SQL SERVER

Alcuni errori tipici che si hanno nella creazione e nell’uso dei diagrammi relazionali in SQL SERVER:

Error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Soluzione:

http://blog.sqlauthority.com/2012/02/06/sql-‐server-‐error-‐fix-‐database-‐diagram-‐support-‐

objects-‐cannot-‐be-‐installed-‐because-‐this-‐database-‐does-‐not-‐have-‐a-‐valid-‐owner/

SQL SERVER – Fix : Management Studio Error :

Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Soluzione:

http://blog.sqlauthority.com/2009/05/18/sql-‐server-‐fix-‐management-‐studio-‐error-‐saving-‐

changes-‐in-‐not-‐permitted-‐the-‐changes-‐you-‐have-‐made-‐require-‐the-‐following-‐tables-‐to-‐be-‐

dropped-‐and-‐re-‐created-‐you-‐have-‐either-‐made-‐changes-‐to-‐a-‐tab/

(9)

D) VERIFICA DEI RISULTATI OTTENUTI

Durante la creazione del DM e bene verificare il contenuto della view FACT_TABLE , soprattutto in relazione al calcolo e aggregabilità delle misure.

A tale scopo, si riportano alcune “semplici” interrogazioni di raggruppamento GROUP BY che utilizzano esclusivamente le viste FACT_TABLE e DIMENSION_TABLE: in questo modo si simulano le analisi e i report che verranno successivamente creati a partire dal DM.

Ad esempio, per verificare

Si simula il calcolo di NUMERO_CLIENTI sul DM, cioè si calcola sulla vista FACT_TABLE.

Verifichiamo NA = {TIPO}, verificando che per un pattern che contiene {TIPO} i valori sono corretti.

Siccome NUMERO_CLIENTI è additiva rispetto a MESE scriverò:

NUMERO_CLIENTI=SUM(NUMERO_CLIENTI)

SELECT TIPO,

NUMERO_CLIENTI=SUM(NUMERO_CLIENTI) FROM FACT_TABLE

GROUP BY TIPO

Verifichiamo ora che per un pattern che non contiene {TIPO} – ad esempio il pattern {MESE} i valori ottenuti con SUM(NUMERO_CLIENTI) non sono corretti

SELECT MESE,

NUMERO_CLIENTI=SUM(NUMERO_CLIENTI) FROM FACT_TABLE

GROUP BY MESE

Un discorso analogo vale per NUMERO_PRODOTTI.

Per una misura calcolata come PREZZO_MEDIO, il valore viene calcolato dopo aver aggregato i dati, sulla base delle componenti:

SELECT MESE,

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_CLIENTI) FROM FACT_TABLE

GROUP BY MESE

A questo punto riportiamo tutte le misure per il pattern {MESE): siccome non possiamo calcolare la misura NUMERO_CLIENTI essa non viene riportata

SELECT MESE, NUMERO_VENDITE=SUM(NUMERO_VENDITE),

QTY=SUM(QTY),

INCASSO=SUM(INCASSO),

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE), NUMERO_PRODOTTI=SUM(NUMERO_PRODOTTI)

FROM FACT_TABLE GROUP BY MESE

(10)

10 {MESE}

In modo analogo per il pattern {TIPO):

{TIPO}

Infine riportiamo il pattern {}: NUMERO_CLIENTI e NUMERO_PRODOTTI vengono omessi perchè non calcolabili

SELECT

NUMERO_VENDITE=SUM(NUMERO_VENDITE),

QTY=SUM(QTY),

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE) FROM FACT_TABLE

Verifichiamo infine cosa succede per il pattern {AZIENDA}: il dubbio è se togliendo TIPO ma lasciando un elemento della sua gerarchia posso continuare a calcolare NUMERO_CLIENTI

SELECT AZIENDA,

QTY=SUM(QTY),

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE), NUMERO_CLIENTI=SUM(NUMERO_CLIENTI)

FROM FACT_TABLE F JOIN DT_TIPO T ON (F.TIPO = T.TIPO) GROUP BY AZIENDA

{AZIENDA}

Il risultato verifica la non aggregabilità di NUMERO_CLIENTI rispetto a TIPO:

nel pattern {AZIENDA} non c’è più TIPO e quindi non si può più calcolare!

Per concludere: anche se le aggregazioni e quindi i report verranno creati con lo strumento di analisi BO, si ribadisce l’importanza di verificare il funzionamento delle misure (soprattutto quelle calcolate) e delle relative non aggregabilità direttamente tramite l’uso delle viste FACT_TABLE e

DIMENSION_TABLE.

(11)

CASO 2) DIMENSIONI = {TIPO,SCONTRINO}, TEMPORALE

Si riportano solo le differenze rispetto al CASO 1)

A) MISURE

NUMERO_CLIENTI = COUNT(DISTINCT SCONTRINO)

TIPOLOGIA : E’ una misura derivate, non deve essere riportata nella FACT_TABLE ALIMENTAZIONE: COUNT(DISTINCT SCONTRINO) : non serve più

AGGREGAZIONE: aggregabile tramite COUNT(DISTINCT SCONTRINO)

NON AGGREGABILITA : nessuna (è aggregabile rispetto a tutte le dimensioni)

B) SCHEMA LOGICO

FACT_TABLE(SCONTRINO:DT_SCONTRINO,TIPO:DT_TIPO,

NUMERO_VENDITE, QTY, INCASSO, PREZZO_SUM,PREZZO_COUNT, NUMERO_CLIENTI , NUMERO_PRODOTTI )

DT_SCONTRINO(SCONTRINO,MESE)

Si usa ALTER per modificare la view; non serve più il JOIN con SCONTRINO

SELECT TIPO, SCONTRINO, NUMERO_VENDITE=COUNT(*), PREZZO_SUM=SUM(PREZZO), QTY=SUM(QTY),

NUMERO_PRODOTTI=COUNT(DISTINCT VENDITE.PRODOTTO)

FROM VENDITE JOIN PRODOTTO on PRODOTTO.PRODOTTO = VENDITE.PRODOTTO

GROUP BY TIPO, SCONTRINO

CREATE VIEW DT_SCONTRINO AS

SELECT SCONTRINO, MESE=SUBSTRING(DATA,1,3) FROM SCONTRINO

VENDITA

AZIENDA MESE

TIPO SCONTRINO

(12)

12

Come verifica, calcoliamo nel nuovo DATAMART, il pattern MESE

SELECT MESE,

QTY=SUM(QTY),

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE), NUMERO_PRODOTTI=SUM(NUMERO_PRODOTTI),

NUMERO_CLIENTI=COUNT(DISTINCT F.SCONTRINO)

FROM FACT_TABLE F JOIN DT_SCONTRINO S on (F.SCONTRINO=S.SCONTRINO) GROUP BY MESE

ed il pattern vuoto (teniamo anche NUMERO_PRODOTTI per verificare la sua non calcolabilità)

SELECT NUMERO_VENDITE=SUM(NUMERO_VENDITE),

QTY=SUM(QTY),

PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE), NUMERO_PRODOTTI=SUM(NUMERO_PRODOTTI),

NUMERO_CLIENTI=COUNT(DISTINCT F.SCONTRINO) FROM FACT_TABLE F

(13)

CASO 3) DIMENSIONI = {PRODOTTO,SCONTRINO}, TRANSAZIONALE

Si riportano solo le differenze rispetto al CASO 2)

A) MISURE

NUMERO_VENDITE = COUNT(*)

TIPOLOGIA : E’ la misura vuota (fatto vuoto), corrisponde al conteggio degli elementi della FACT_TABLE; non deve essere riportata nella FACT_TABLE

ALIMENTAZIONE: COUNT(*) AGGREGAZIONE: COUNT

NON AGGREGABILITA : nessuna QTY= SUM(QTY)

TIPOLOGIA : E’ una misura normale ALIMENTAZIONE: SUM(QTY) AGGREGAZIONE: ADDITIVA

INCASSO= SUM(QTY*PREZZO)

TIPOLOGIA : E’ una misura derivate, non si riporta nella FACT_TABLE ALIMENTAZIONE: SUM(QTY*PREZZO)

PREZZO_MEDIO= AVG(PREZZO)

TIPOLOGIA : E’ una misura calcolata, PREZZO_SUM/PREZZO_COUNT ALIMENTAZIONE: PREZZO_SUM=PREZZO

PREZZO_COUNT, corrisponde a NUMERO_VENDITE, non si riporta nella FACT_TABLE

AGGREGAZIONE: PREZZO_SUM additiva

NUMERO_VENDITE tramite COUNT NON AGGREGABILITA : nessuna

NUMERO_PRODOTTI = COUNT(DISTINCT PRODOTTO)

TIPOLOGIA : E’ una misura derivata, non deve essere riportata nella FACT_TABLE ALIMENTAZIONE: COUNT(DISTINCT PRODOTTO) : non serve più

AGGREGAZIONE: aggregabile tramite COUNT(DISTINCT PRODOTTO)

NON AGGREGABILITA : nessuna (è aggregabile rispetto a tutte le dimensioni)

VENDITA

AZIENDA MESE

TIPO PRODOTTO SCONTRINO

(14)

14

B) SCHEMA LOGICO

PREZZO_SUM viene chiamata semplicemente PREZZO

Consideriamo lo STAR-‐SCHEMA

FACT_TABLE(SCONTRINO:DT_SCONTRINO,PRODOTTO:DT_PRODOTTO, QTY, PREZZO)

DT_SCONTRINO(SCONTRINO,MESE)

DT_PRODOTTO(PRODOTTO,TIPO,AZIENDA)

Si usa ALTER per modificare la view; non serve più il JOIN con PRODOTTO; non si deve più raggruppare

SELECT PRODOTTO, SCONTRINO, PREZZO, QTY

FROM VENDITE

CREATE VIEW DT_PRODOTTO AS

SELECT PRODOTTO, P.TIPO,AZIENDA

FROM PRODOTTO P JOIN TIPO T ON (P.TIPO=T.TIPO)

Come verifica, calcoliamo nel nuovo DATAMART, il pattern MESE

SELECT MESE,

NUMERO_VENDITE=COUNT(*),

QTY=SUM(QTY),

INCASSO=SUM(QTY*PREZZO),

PREZZO_MEDIO=SUM(PREZZO)/ COUNT(*),

NUMERO_PRODOTTI= COUNT (DISTINCT PRODOTTO), NUMERO_CLIENTI=COUNT(DISTINCT F.SCONTRINO)

FROM FACT_TABLE F JOIN DT_SCONTRINO S on (F.SCONTRINO=S.SCONTRINO) GROUP BY MESE