February 2008 Archives

Scenario:

• tabella di milioni di righe, dove si può assumere che non ci siano modifiche/eliminazioni a ritroso
• data warehouse in cui si voglia creare un QVD per periodo

Succede abbastanza di frequente, soprattutto con tabelle che si limitano a loggare eventi.
Assumendo che la lunghezza del periodo del singolo QVD sia un mese, ecco quel che si può ragionevolmente voler ottenere:

• evitare di ricaricare i dati già caricati
• evitare l'intervento manuale: deve capire da solo cosa ricaricare
• se un periodo dev'essere ricaricato, dev'essere sufficiente cancellare il qvd

Insomma, la scaletta delle cose da fare sarebbe la seguente:

1. caricare la lista di periodi esistenti sul database
2. per ogni periodo, eccetto quello odierno:
   1. se c'è già il QVD, saltarlo a piè pari
   2. se non c'è già il QVD, caricare e salvarlo
3. per il periodo odierno, salvare e sovrascrivere il qvd

Faccio un esempio con Oracle come database:

// i .qvd saranno del tipo Tabella200802.qvd,
// oppure TabellaMeseOdierno.qvd
set prefissoQvd=Tabella;

trace Carico la lista di tutti i mesi...;
tmpAnniMese:
load
	AAAAMM as annoMese
;
SQL SELECT DISTINCT TO_CHAR(DATA, 'YYYYMM') AAAAMM FROM TABELLA;

let iMax=noofrows('tmpAnniMese')-1;
let meseOggi=peek('annoMese', -1, 'tmpAnniMese');

for i=0 to $(iMax);
	let mese=peek('annoMese', $(i), 'tmpAnniMese');
	let righeFile='0' & qvdnoofrecords('$(prefissoQvd)$(mese).qvd');
	
	if ($(righeFile) = 0) then
		trace Carico $(mese)...;
		AnagJn:
		load ...;
		SQL SELECT ...	FROM TABELLA WHERE TO_CHAR(DATA, 'YYYYMM') = '$(mese)';

		trace Salvo $(mese) su QVD...;
		
		// verifico se è il mese odierno, per scegliere il nome del file
		if ('$(mese)' = '$(meseOggi)') then
			store AnagJn into $(prefissoQvd)MeseOdierno.qvd;
		else
			store AnagJn into $(prefissoQvd)$(mese).qvd;
		end if
		drop table AnagJn;
	else
		trace Salto $(mese)!;
	end if
next i

drop table tmpAnniMese;

Essenzialmente, carica in una tabella temporanea tutti i valori distinti di anno-mese, e poi per ogni riga di quella tabella va a verificare se il file non esiste ancora (o se è il mese corrente), se sì lo carica, altrimenti passa al successivo.
Così facendo i tempi diminuiscono sensibilmente rispetto a caricare tutto ogni volta, nell'ordine di decine di volte, tanto più quanto grande è la tabella.

Una nota di contorno: se sulla tabella c'è un indice sul campo DATA, le due SELECT del codice di esempio non ne fanno uso poiché usano una trasformazione del campo stesso, costringendo il DBMS a leggere tutti i valori, scorrendo tutta la tabella.
Per sfruttare al massimo gli indici, si può sostituire la prima SELECT (che prende tutti i valori distinti di anno-mese con la seguente, che ritornerà tutti i valori pressoché istantaneamente (strettamente legata alla sintassi Oracle):

SELECT TO_CHAR(START_DAY, 'YYYYMM') AAAAMM
	FROM TABLE(CALENDAR.MONTHS((SELECT MIN(DATA) FROM TABELLA),
		(SELECT MAX(DATA) FROM TABELLA)));

La clausola WHERE della seconda, invece, si può sostituire con la seguente:

WHERE DATA BETWEEN TO_DATE('$(mese)', 'YYYYMM') AND
	TO_DATE(TO_CHAR(LAST_DAY(TO_DATE('$(mese)', 'YYYYMM')),
		'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS')

Spesso si hanno grafici con dimensioni numeriche, ad esempio delle date.
In questi casi l'impostazione "continua" (dalle proprietà del grafico, Axes => Continuous nella versione in Inglese) può essere utile: dicendogli che la dimensione contiene valori continui, QlikView mostra meno valori e li avvicina molto di più.
Ad esempio, se una dimensione contiene i numeri da 1 a 20 saltando il 15, senza impostare l'asse continua si farà difficoltà a farli stare tutti dentro un grafico senza impostare una barra di scorrimento, inoltre salterà il 15.
Impostandola continua, mostrerà ad esempio solo i numeri pari e distanzierà opportunamente il 14 dal 16.

Un esempio si vede nella immagine che allego: la temperatura media di Padova. Avendo tutti i giorni da quest'estate ad oggi, QlikView non sarebbe riuscito a mostrare neppure ferragosto se non avessi impostato l'asse continua per la dimensione, senza barra di scorrimento.
Così invece riesce a mostrare tutto il periodo e sotto l'asse delle ascisse mostra un valore ogni 15 giorni.

In verità, il grafico che porto come esempio non mostra la media giornaliera, ma la media mobile centrata a 10 giorni della temperatura media giornaliera. Ma di medie mobili centrate parlerò un'altra volta...

Di default, QlikView decide automaticamente se un campo è testuale o numerico (le date sono considerate numeri).
A volte però risulta utile forzare un tipo piuttosto che un altro mediante text() o num().
Ad esempio avere ID che finiscono per ,00 è brutto, oppure si può voler forzare un formato numerico come default del campo.

In verità per i campi numerici QlikView memorizza sia una rappresentazione testuale che il valore: può far comodo sfruttare ciò specificando esplicitamente testo e numero.
È qui che entra in gioco la funzione dual(rappresentazione testuale, valore numerico).

Spesso si denormalizzano i campi di date per rendere possibile selezionare, ad esempio, tutte le date che ricadono in un certo anno-mese:

load year(data) & ' ' & month(data) as anno_mese, ...;

Ottenendo stringhe tipo "2008 gen". Il problema è che l'ordine alfabetico non ha senso; inoltre non è possibile usarle come dimensione impostando l'asse delle ascisse come continua.
Non sarebbe molto più bello avere "gennaio 08", che sia anche ordinabile come numero?

load dual(text(date(data, 'MMMM YY'))
          ,
          date#(text(date(data, 'MMMM YY')), 'MMMM YY')
     ) as mese_anno,
     ...;

Suggerimento: in caso si voglia giorno/mese/anno (tagliando fuori quindi ore, minuti, secondi), poiché QlikView memorizza le date come giorni passati dal 1900, è sufficiente approssimare per difetto: date(floor(data)) as giorno_mese_anno.

Altro esempio: classi d'età ordinabili.

load if(anni < 1, dual('meno di un anno', 0),
     if(anni < 10, dual('fra 1 e 9 anni', 1),
     dual('da 10 anni in su', 10))) as classe_eta,
     ...;

A volte serve caricare dati da Active Directory via OLE DB.

Esempio pratico: un documento che analizza il file di log di QlikView Server. Se i nomi utenti sono criptici (ad esempio c001957 è il mio utente da un cliente), è bene caricare anche il nome completo via LDAP.
Poiché è stato un parto trovare la soluzione, la annoto qui:

set nomeDominio=dom;
set nomeUtente=dom\usr;
set password=pwd;

CONNECT TO [Provider=ADsDSOObject;
User ID=$(nomeUtente);
Encrypt Password=False;
Location=$(nomeDominio);
Mode=Read;
Bind Flags=0;
ADSI Flag=-2147483648] (Password is $(password));

Utenti:
SQL SELECT sAMAccountName, mail, displayName, sn
FROM 'LDAP://$(nomeDominio)'
WHERE objectClass = 'user'
AND objectCategory = 'person';

Se l'utente che esegue questo codice è di dominio, non serve specificare utente e password.

Poiché ogni query ritorna al massimo 1.500 risultati, in domini con molti utenti può risultare comodo il piccolo e sporco trucchetto qui di seguito:

sub carica(chrMin, chrMax)
	let numMin=ord(chrMin);
	let numMax=ord(chrMax);

	for numLettera=$(numMin) to $(numMax)
		let lettera=chr(numLettera);
		
		Utenti:
		SQL SELECT
			sAMAccountName, mail, displayName, sn
		FROM 'LDAP://$(nomeDominio)'
		WHERE objectClass = 'user'
		AND displayName = '$(lettera)*'
		AND objectCategory = 'person';
	next numLettera
end sub

call carica(' ', '&');
call carica('+', '>'); // comprende i numeri
call carica('@', '_'); // comprende le lettere
call carica('{', '}');

Può far comodo una lista di attributi di Active Directory.

Saluti.