ESERCIZIO DEL 27 MAGGIO 2011 TIPO
CDL
FACOLTA
ANNO
ESAME
CITTA
CFU
EXTRACFU
TIPO
STUDENTE
NUMERO_CFU_EXTRA
SELECT [CDL].[Citta].MEMBERS ON COLUMNS FROM ESAME WHERE [TIPO_CDL].[All CDL].[M] SELECT NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS)ON COLUMNS FROM ESAME WHERE [TIPO_CDL].[All CDL].[M] WITH MEMBER [Measures].CONTEGGIO AS 'COUNT(NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS)) ' SELECT { [Measures].CONTEGGIO } ON COLUMNS FROM ESAME WHERE [TIPO_CDL].[All CDL].[M] è 1 Questo non è possibile quando la condizione riguarda la stessa dimensione [CDL]: ad esempio per limitarsi alla città relativa al membro [CDL].[All CDL].[RE].[AGRARIA].[Agraria] SELECT NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS) ON COLUMNS FROM ESAME WHERE [CDL].[All CDL].[RE].[AGRARIA].[Agraria] è ASSI DUPLICATI WITH MEMBER [Measures].CONTEGGIO AS 'COUNT(NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS)) ' SELECT { [Measures].CONTEGGIO } ON COLUMNS FROM ESAME WHERE [CDL].[All CDL].[RE].[AGRARIA].[Agraria] è 2 (NON CORRETTO) Se una dimensione (nell’esempio CDL) è sia in un membro calcolato (nella WITH) che nella WHERE: si considera la specifica della WITH: si considera [CDL].[Citta].MEMBERS e quindi vengono contate tutte le citta e non la limitazione a [CDL].[All CDL].[RE].[AGRARIA].[Agraria], e quindi non si ottiene la città relativa al membro [CDL].[All CDL].[RE].[AGRARIA].[Agraria] UN altro esempio WITH MEMBER [Measures].CONTEGGIO AS 'COUNT(NONEMPTYCROSSJOIN ( [CDL].[All CDL].[MO].[AGRARIA_MO].CHILDREN)) ' SELECT { [Measures].CONTEGGIO} ON COLUMNS FROM ESAME WHERE [CDL].[All CDL].[RE].[AGRARIA] è 2 Per imporrre una condizione sulla stessa dimensione [CDL], si deve usare Ancestor Ancestor(«Member», «Level») Restituisce l'antenato di «Member» al livello specificato in «Level». IsAncestor IsAncestor(«Member1»,«Member2») Questa funzione restituisce TRUE se il membro indicato in «Member 1» è un antenato del membro specificato in «Member 2». Ad esempio per limitarsi alla città relativa al membro [CDL].[All CDL].[RE].[AGRARIA].[Agraria] SELECT FILTER(NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS), ISANCESTOR([CDL].CURRENTMEMBER, [CDL].[All CDL].[RE].[AGRARIA].[Agraria])) ON COLUMNS FROM ESAME èRE SELECT FILTER(NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS), ISANCESTOR([CDL].CURRENTMEMBER, [CDL].[All CDL].[RE].[AGRARIA].[Agraria])) ON COLUMNS FROM ESAME èRE WITH MEMBER [Measures].CONTEGGIO AS 'COUNT(FILTER(NONEMPTYCROSSJOIN([CDL].[Citta].MEMBERS), ISANCESTOR([CDL].CURRENTMEMBER, [CDL].[All CDL].[RE].[AGRARIA].[Agraria]))) ' SELECT { [Measures].CONTEGGIO } ON COLUMNS FROM ESAME è1 Applichiamolo ad una query più complessa Pattern {[CDL].[Citta],[STUDENTE].[Citta] } limitato alle città con lo stesso nome e alla città relativa al membro [CDL].[All CDL].[RE].[AGRARIA].[Agraria] SELECTFILTER(NONEMPTYCROSSJOIN ([CDL].[Citta].MEMBERS, [STUDENTE].[Citta].MEMBERS), [CDL].CURRENTMEMBER.NAME = [STUDENTE].CURRENTMEMBER.NAME AND ISANCESTOR([CDL].CURRENTMEMBER, [CDL].[All CDL].[RE].[AGRARIA].[Agraria])) ON COLUMNS FROM ESAME Q1 Q1_1 Pattern { [CDL].[Cdl],[STUDENTE].[CITTA] }, limitato ai CDL di RE e alla città di MO COME STUDENTI Q1_2 CDL di RE con CFU > 5 degli esami sostenuti da studenti di MO (non interessa cosa viene visualizzato come misura) SOLUZIONE Q1_1 SELECT Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]) ON COLUMNS, { [STUDENTE].[All STUDENTE].[MO] } ON ROWS FROM ESAME Q1_2_A: SELECT FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5) ON COLUMNS FROM ESAME Q1_2_B: SELECTFILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu] > 5)) ON COLUMNS FROM ESAME WHERE [STUDENTE].[All STUDENTE].[MO] Si noti che la seguente espressione non è corretta Q1_2_C: SELECT FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu] > 5)) ON COLUMNS, { [STUDENTE].[All STUDENTE].[MO] } ON ROWS FROM ESAME In quanto([Measures].[Cfu] > 5)) è valutata su tutti gli studenti Quindi Q1_2_A e Q1_2_B sono equivalenti se ci limitiamo solo al contenuto di COLUMNS; in Q1_2_A però vengono selezionati solo gli esami degli studenti di MO e questo può non essere sufficiente se vogliamo fare sul risultato ottenuto in COLUMNS, un analisi che riguarda gli esami di tutti gli studenti.Ad esempio QX: Tra iCDL di RE con CFU > 5 degli esami sostenuti da studenti di MO Riportare quello con il max numero di CFU in assoluto (cioè considerando tutti gli studenti). QX_1: SELECT TOPCOUNT(FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5) , 1, [Measures].[Cfu]) ON COLUMNS FROM ESAME Q1_BIS Q1_BIS_1 CDL di RE con CFU > 5 degli esami sostenuti da studenti di MO; quindi visualizzare i CFU degli esami sostenuti da studenti di RE Q1_BIS_2 Tra i CDL di RE con CFU > 5 degli esami sostenuti da studenti di MO individuare il CDL con il maggior numero di CFU degli esami sostenuti da studenti di RE e visualizzare tale numero di CFU SOLUZIONE Q1_BIS_1: SELECT FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5) ON COLUMNS FROM ESAME WHERE ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE]) Q1_BIS_1: SELECT TOPCOUNT( FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5), 1, ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE])) ON COLUMNS FROM ESAME WHERE ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE]) Si noti che SELECT Non è corretta, anche se TOPCOUNT( il risultato ottenuto in FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), questo caso particolare è ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5), lo stesso: si calcola il 1, topcount considerando ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE])) tutti gli esami e non ON COLUMNS FROM ESAME quelli di RE WHERE ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE]) SELECT Non è corretta, in TOPCOUNT( quanto riporta il FILTER (Descendants([CDL].[All CDL].[RE], [CDL].[Cdl]), valore 40, ovvero il ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[MO])> 5), valore dei CFU di 1, tutti gli studenti, non ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE])) limitato a quelli di ON COLUMNS FROM ESAME RE WHERE ([Measures].[Cfu],[STUDENTE].[All STUDENTE].[RE]) Q2 Q2_1Pattern {DRILLDOWNLEVEL([CDL].[Facolta]), [STUDENTE].[Citta] } Q2_2: Per il pattern {[CDL].[Facolta], [STUDENTE].[Citta]} riportare il numero di esami con un CFU < 10 SOLUZIONE Q2_1 SELECT DRILLDOWNLEVEL([CDL].[Facolta].MEMBERS) ON COLUMNS, [STUDENTE].[Citta].MEMBERS ON ROWS FROM ESAME Q2_2: WITH MEMBER [Measures].CONTEGGIO AS 'COUNT(FILTER( NONEMPTYCROSSJOIN({[STUDENTE].CURRENTMEMBER}, [CDL].CURRENTMEMBER.CHILDREN) , ([Measures].[Cfu] < 10 )))' SELECT [CDL].[Facolta].MEMBERS ON COLUMNS, [STUDENTE].[Citta].MEMBERS ON ROWS FROM ESAMEWHERE [Measures].CONTEGGIO Q3 Q3_1 Pattern { DRILLDOWNLEVEL([CDL].[Facolta]), DRILLDOWNLEVEL([STUDENTE].[Citta]) } Q3_2: Per il pattern {[CDL].[Facolta], [STUDENTE].[Citta]} riportare il numero di esami con un CFU < 10 SOLUZIONE Q3_1 SELECT DRILLDOWNLEVEL([CDL].[Facolta].MEMBERS) ON COLUMNS, DRILLDOWNLEVEL ([STUDENTE].[Citta].MEMBERS) ON ROWS FROM ESAME Q3_2 WITH MEMBER [Measures].CONTEGGIO AS 'COUNT( FILTER( NONEMPTYCROSSJOIN([STUDENTE].CURRENTMEMBER.CHILDREN, [CDL].CURRENTMEMBER.CHILDREN) , [Measures].[Cfu] < 10 )))' SELECT [CDL].[Facolta].MEMBERS ON COLUMNS, [STUDENTE].[Citta].MEMBERS ON ROWS FROM ESAME WHERE [Measures].CONTEGGIO Q4 DATO il Pattern { DRILLDOWNLEVEL([CDL].[CITTA], CDL), DRILLDOWNLEVEL([STUDENTE].[Citta]) } Q4_1: Per il pattern {[CDL].[CITTA], [STUDENTE].[Citta]} riportare il numero di esami con un CFU < 10 WITH MEMBER [Measures].CONTEGGIO AS 'COUNT( FILTER( NONEMPTYCROSSJOIN([STUDENTE].CURRENTMEMBER.CHILDREN, descendants([CDL].CURRENTMEMBER, [CDL].[Cdl]) ) , ([Measures].[Cfu] <10)))' SELECT [CDL].[Citta].MEMBERS ON COLUMNS, [STUDENTE].[Citta].MEMBERS ON ROWS FROM ESAME WHERE [Measures].CONTEGGIO Nota SELECT DRILLDOWNLEVEL([CDL].[Citta].MEMBERS, [CDL].[Citta]) ON COLUMNS, DRILLDOWNLEVEL ([STUDENTE].[Citta].MEMBERS) ON ROWS FROM ESAME ma non funziona … allora WITHSET [X] AS ' [CDL].[Citta].MEMBERS' SELECTUNION([CDL].[Citta].MEMBERS, GENERATE( [X], descendants([CDL].CURRENTMEMBER, [CDL].[Cdl])) ON COLUMNS FROM ESAME WITHSET [X] AS ' [CDL].[Citta].MEMBERS' SELECT UNION([CDL].[Citta].MEMBERS, GENERATE( [X], descendants([CDL].CURRENTMEMBER, [CDL].[Cdl])) ) ON COLUMNS FROM ESAME WITHSET [X] AS ' [CDL].[Citta].MEMBERS' SELECT Hierarchize( UNION([CDL].[Citta].MEMBERS, GENERATE( [X], descendants([CDL].CURRENTMEMBER, [CDL].[Cdl])) ) ) ON COLUMNS, DRILLDOWNLEVEL([STUDENTE].[Citta].MEMBERS) ON ROWS FROM ESAME Q5 Q5_1 Pattern {[STUDENTE].[Citta], ANNO, TIPO } E SUB-­‐PATTERN Q5_2: Pattern {[STUDENTE].[Citta], ANNO, TIPO } e subpattern, ad eccezione di (cioè senza mostrare il subpattern){ [STUDENTE].[Citta], ANNO, TIPO } SOLUZIONE (VEDERE http://www.dbgroup.unimo.it/SIA/Esercizio13MaggioPratica) Q5_1: WITH SET SET_CITTA AS '[STUDENTE].[Citta].MEMBERS' SET CITTA_ALL AS ' [STUDENTE].[(All)].MEMBERS' SET SET_TIPO AS '[TIPO_CDL].[Tipo].MEMBERS' SET TIPO_ALL AS '[TIPO_CDL].[(All)].MEMBERS' SET SET_ANNO AS ' [Anno].[Anno].MEMBERS' SET ANNO_ALL AS '[Anno].[(All)].MEMBERS' SELECT UNION(SET_CITTA, CITTA_ALL) ON COLUMNS, UNION(NONEMPTYCROSSJOIN(SET_ANNO,SET_TIPO), UNION(NONEMPTYCROSSJOIN(SET_ANNO,TIPO_ALL), UNION(NONEMPTYCROSSJOIN(ANNO_ALL ,SET_TIPO), NONEMPTYCROSSJOIN(ANNO_ALL ,TIPO_ALL)))) ON ROWS FROM ESAME (A MENO DELL’ORDINE, IRRILEVANTE) Per ottenere lo stesso ordine visualizzato sopra si può riscrivere come WITH SET SET_CITTA AS '[STUDENTE].[Citta].MEMBERS ' SET CITTA_ALL AS ' [STUDENTE].[(All)].MEMBERS ' SET SET_TIPO AS '[TIPO_CDL].[Tipo].MEMBERS ' SET TIPO_ALL AS ' [TIPO_CDL].[(All)].MEMBERS ' SET SET_ANNO AS ' [Anno].[Anno].MEMBERS ' SET ANNO_ALL AS ' [Anno].[(All)].MEMBERS' SELECT UNION(SET_CITTA, CITTA_ALL) ON COLUMNS, NONEMPTYCROSSJOIN(UNION(SET_ANNO, ANNO_ALL), UNION(SET_TIPO, TIPO_ALL)) ON ROWS FROM ESAME Q5_2: WITH SET SET_CITTA AS '[STUDENTE].[Citta].MEMBERS ' SET CITTA_ALL AS ' [STUDENTE].[(All)].MEMBERS ' SET SET_TIPO AS '[TIPO_CDL].[Tipo].MEMBERS ' SET TIPO_ALL AS '[TIPO_CDL].[(All)].MEMBERS ' SET SET_ANNO AS ' [Anno].[Anno].MEMBERS ' SET ANNO_ALL AS '[Anno].[(All)].MEMBERS' SELECT UNION(SET_CITTA, CITTA_ALL) ON COLUMNS, UNION(NONEMPTYCROSSJOIN(SET_ANNO, SET_TIPO), UNION(NONEMPTYCROSSJOIN(SET_ANNO,TIPO_ALL), UNION(NONEMPTYCROSSJOIN(ANNO_ALL ,SET_TIPO), NONEMPTYCROSSJOIN(ANNO_ALL ,TIPO_ALL)))) ON ROWS FROM ESAME Oppure anche senta definire i set Q5_2: SELECT UNION([STUDENTE].[Citta].MEMBERS, [STUDENTE].[(All)].MEMBERS) ONCOLUMNS, UNION(UNION( CROSSJOIN([Anno].[Anno].MEMBERS,[TIPO_CDL].[(All)].MEMBERS), CROSSJOIN( [Anno].[(All)].MEMBERS,[TIPO_CDL].[Tipo].MEMBERS)), CROSSJOIN( [Anno].[(All)].MEMBERS, [TIPO_CDL].[(All)].MEMBERS)) ON ROWSFROM ESAME Q6 Q6_1 Pattern { [CDL].[Cdl],[STUDENTE].[CITTA] }, limitato ai CDL il cui nome inizia con “Agraria” e alla citta MO Funzione Booleana Instr(Str1,Str2) se la parte iniziale di Str1 coincide con Str2 Q6_2 CDL il cui nome inizia con “Agraria”con CFU >9 degli esami sostenuti da studenti di MO (non interessa cosa viene visualizzato come misura) SOLUZIONE Q6_1 SELECT FILTER([CDL].[Cdl].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ) )ON COLUMNS, { [STUDENTE].[All STUDENTE].[MO] } ON ROWS FROM ESAME Q6_2 SELECT FILTER([CDL].[Cdl].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ) AND ([Measures].[Cfu], [STUDENTE].[All STUDENTE].[MO]) >9 ) ON COLUMNS FROM ESAME Q7 Consideriamo WITHSET [FACOLTA_AGRARIA] AS ' FILTER([CDL].[Facolta].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ))' SELECT[FACOLTA_AGRARIA] ON COLUMNS FROM ESAME Come ottenere i children di tutti questi membri ? Dato un insieme SET_X (nell’esempio SET_X = {AGRARIA_MO, AGRARIA }), si deve scrivere un’espressione tipo FOR EACH membro IN SET_X :membro.CHILDREN In MDX: sia D la dimensione usata per definire SET_X (nel nostro esempio D= [CDL]), allora questa espressione si scrive GENERATE(SET_X, D.CURRENTMEMBER.CHILDREN) WITHSET [FACOLTA_AGRARIA] AS ' FILTER([CDL].[Facolta].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ))' SELECT GENERATE([FACOLTA_AGRARIA], [CDL].CURRENTMEMBER.CHILDREN) ON COLUMNS FROM ESAME Come ottenere gli ancestor di questi membri ? Dato un insieme X (nell’esempio X = {AGRARIA_MO, AGRARIA }), si deve scrivere un’espressione tipo FOR EACH membro IN X : ANCESTORS(membro) WITH SET [FACOLTA_AGRARIA] AS ' FILTER([CDL].[Facolta].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ))' SELECT GENERATE([FACOLTA_AGRARIA], ANCESTORS([CDL].CURRENTMEMBER, [CDL].[Citta])) ON COLUMNS FROM ESAME WITHSET [FACOLTA_AGRARIA] AS ' FILTER([CDL].[Facolta].MEMBERS, InStr([CDL].CURRENTMEMBER.NAME, "AGRARIA" ))' SELECT GENERATE([FACOLTA_AGRARIA], [CDL].CURRENTMEMBER.CHILDREN) ON COLUMNS FROM ESAME Q8 Consideriamo la dimensione [NUM_EXTRA_CFU]. In tale dimensione il livello [NUMERO CFU EXTRA] riporta un valore numerico, ovvero il numero di extra CFU conferiti da una certa attività extra individuata dal codice riportato in [Extracfu]: ad esempio l’attività Extracfu=56 conferisce 2 CFU extra, mentre Extracfu=99999 (che codifica l’assenza di attività extra ) conferisce 0 CFU extra. Essendo i membri di [NUMERO CFU EXTRA] valori numerici possono essere filtrati con un operatore relazionale =, >, >= , … . Per ottenere il valore numerico del membro, si deve ancora utilizzare la proprietà NAME del membro e quindi convertirla in valore (infatti il NAME è una stringa) e quindi convertirla in valore tramite STRTOVALUE SELECT FILTER ( [NUM_EXTRA_CFU].[NUMERO CFU EXTRA].MEMBERS, STRTOVALUE([NUM_EXTRA_CFU].CURRENTMEMBER.NAME)> 1 ) ON COLUMNS FROM ESAME Un’altra possibilità è la seguente (si consiglia comunaue di usare la funzione STRTOVALUE): SELECT FILTER ( [NUM_EXTRA_CFU].[NUMERO CFU EXTRA].MEMBERS, [NUM_EXTRA_CFU].CURRENTMEMBER.NAME > "1" ) ON COLUMNS FROM ESAME Come ottenere gli [Extracfu] tali per cui [NUMERO CFU EXTRA] > 1 ? Si hanno due possibili soluzioni. Nella prima soluzione, siccome si vogliono ottenere gli [Extracfu] e siccome la condizione riguarda il livello ancestor (padre) [NUMERO CFU EXTRA], a. si considera l’insieme dei membri di [Extracfu] : [NUM_EXTRA_CFU].[Extracfu].MEMBERS b. si filtra tale insieme con la condizione sul suo padre padre: ANCESTOR([NUM_EXTRA_CFU].CURRENTMEMBER, [Numero Cfu Extra]) si considera il NOME ANCESTOR([NUM_EXTRA_CFU].CURRENTMEMBER, [Numero Cfu Extra]).NAME e quindi la condizione >1 STRTOVALUE ( ANCESTOR([NUM_EXTRA_CFU].CURRENTMEMBER, [Numero Cfu Extra]).NAME ) > 1 SELECT FILTER ( [NUM_EXTRA_CFU].[Extracfu].MEMBERS , STRTOVALUE ( ANCESTOR([NUM_EXTRA_CFU].CURRENTMEMBER, [Numero Cfu Extra]).NAME ) > 1 ) ON COLUMNS FROM ESAME è 56 Una seconda soluzione invece è la seguente. Con la condizione [NUMERO CFU EXTRA] > 1 si individua e si definisce l’insieme [PIU_DI_UN_CFU] : WITH SET [PIU_DI_UN_CFU] AS ' FILTER ( [NUM_EXTRA_CFU].[NUMERO CFU EXTRA].MEMBERS, STRTOVALUE([NUM_EXTRA_CFU].CURRENTMEMBER.NAME)> 1 ) ' Quindi si ottengono, per ogni membro di questo insieme, i relativi figli FOR EACH membro IN [PIU_DI_UN_CFU] :membro.CHILDREN attraverso GENERATE GENERATE([PIU_DI_UN_CFU], [NUM_EXTRA_CFU].CURRENTMEMBER.CHILDREN) WITH SET [PIU_DI_UN_CFU] AS ' FILTER ( [NUM_EXTRA_CFU].[NUMERO CFU EXTRA].MEMBERS, STRTOVALUE([NUM_EXTRA_CFU].CURRENTMEMBER.NAME)> 1 ) ' SELECT GENERATE([PIU_DI_UN_CFU], [NUM_EXTRA_CFU].CURRENTMEMBER.CHILDREN) ON COLUMNS FROM ESAME è 56