Esempio Misure

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 PREZZO
Rispetto al DBO delle dispense è stato aggiunto VENDITA
PRODOTTO
(1,N)
(1,N)
SCONTRINO
l’attributo AZIENDA dell’entità TIPO (1,1)
DATA
QTY
Inoltre l’attributo MESE non è indicato esplicitamente in quanto si intende derivato dalla DATA, prendendo i primi tre caratteri (1,N)
TIPO
AZIENDA
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 1 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. 2 CASO 1) DIMENSIONI = {TIPO,MESE}, TEMPORALE AZIENDA
VENDITA
TIPO
MESE 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) AGGREGAZIONE: ADDITIVA NON AGGREGABILITA : nessuna (additiva rispetto a tutte le dimensioni) INCASSO= SUM(QTY*PREZZO) TIPOLOGIA : E’ una misura normale ALIMENTAZIONE: SUM(QTY*PREZZO) AGGREGAZIONE: ADDITIVA NON AGGREGABILITA : nessuna (additiva rispetto a tutte le dimensioni) 3 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 4 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), 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) 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 5 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), 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 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 6 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 7 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/ 8 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 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} 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 9 {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), INCASSO=SUM(INCASSO), 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, NUMERO_VENDITE=SUM(NUMERO_VENDITE), QTY=SUM(QTY), INCASSO=SUM(INCASSO), 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. 10 CASO 2) DIMENSIONI = {TIPO,SCONTRINO}, TEMPORALE AZIENDA
MESE
VENDITA
TIPO
SCONTRINO 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) C) VISTE PER L’ALIMENTAZIONE Si usa ALTER per modificare la view; non serve più il JOIN con SCONTRINO ALTER VIEW FACT_TABLE AS SELECT TIPO, SCONTRINO, NUMERO_VENDITE=COUNT(*), PREZZO_SUM=SUM(PREZZO), QTY=SUM(QTY), INCASSO=SUM(QTY*PREZZO), 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 11 Come verifica, calcoliamo nel nuovo DATAMART, il pattern MESE 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), 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), INCASSO=SUM(INCASSO), PREZZO_MEDIO=SUM(PREZZO_SUM)/SUM(NUMERO_VENDITE), NUMERO_PRODOTTI=SUM(NUMERO_PRODOTTI), NUMERO_CLIENTI=COUNT(DISTINCT F.SCONTRINO) FROM FACT_TABLE F 12 CASO 3) DIMENSIONI = {PRODOTTO,SCONTRINO}, TRANSAZIONALE AZIENDA
MESE
VENDITA
TIPO PRODOTTO
SCONTRINO 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 NON AGGREGABILITA : nessuna (additiva rispetto a tutte le dimensioni) INCASSO= SUM(QTY*PREZZO) TIPOLOGIA : E’ una misura derivate, non si riporta nella FACT_TABLE ALIMENTAZIONE: SUM(QTY*PREZZO) AGGREGAZIONE: ADDITIVA NON AGGREGABILITA : nessuna (additiva rispetto a tutte le dimensioni) 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_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 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) 13 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) C) VISTE PER L’ALIMENTAZIONE Si usa ALTER per modificare la view; non serve più il JOIN con PRODOTTO; non si deve più raggruppare ALTER VIEW FACT_TABLE AS 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 14

Download Report