Comunitat DSTSC

1. Introducció

La plataforma Qlik constitueix una eina de Business Intelligence. L’objectiu d'aquesta és  transformar les dades en informació i la informació en coneixement per tal de permetre a qui correspongui la presa de decisions sobre les qüestions d’un àmbit concret.

Aquest programari ha estat adoptat per la Diputació de Barcelona per donar sortida a totes les necessitats d’anàlisi d’informació que es plantegen en els Sistemes d’Informació actuals.

El present document va destinat a tots aquells equips de treball que vulguin desenvolupar una aplicació amb tecnologia Qlik a la Diputació de Barcelona (DIBA).

Abans de continuar la lectura d’aquest document és imprescindible tenir clar el model de relació i el model d’execució d’aquesta tipologia de projectes dins de la DIBA.

Aquest és un document on s’expliquen les normatives a complir per qualsevol que desenvolupi aplicacions de Qlik per la DIBA. Inclou:

• Premisses d’una solució Qlikview o QlikSense
• Arquitectura d’una solució Qlik a la DIBA
• Introducció al catàleg de dades comú de la DIBA
• Estructura de carpetes
• Normatives de programació
• Seguretat i rols d’usuari
• Plantilles de presentació
• Documentació d’acceptació del desenvolupament

2. Les 5 premisses d’una solució Qlik a la DIBA

Tot equip que desenvolupi una aplicació en Qlik a la DIBA ha de tenir en ment les 5 premisses següents:

1. La solució no ha de posar en risc el rendiment de la plataforma de Qlikview de la DIBA.
2. La solució ha de ser de fàcil manteniment un cop lliurada i complir les best practices de programació.
3. S’han de fer servir les dades disponibles al catàleg de dades de la DIBA sempre que es pugi.
4. Totes les dades de la solució han d’estar preparades per fer-les servir en d’altres projectes futurs dins de la seva unitat o formant part del catàleg de dades de la DIBA.
5. El coneixement adquirit durant l’execució del projecte ha de ser transmès a grup de BI de la OTI,  junt amb la documentació necessària al final del projecte.

3. La plataforma Qlik a la DIBA

Per tal de complir la primera de les premisses, hem de saber quin és el dimensionament que té la nostra plataforma.

A la DIBA hi han 5 servidors Qlikview per facilitar la difusió dels informes creats:

​​​​      Portal: 3 Servidors per la gestió dels 3 portals d’accés:

• Publicació corporativa Gestió del portal on es publiquen els informes que han de consultar usuaris interns de la Corporació. L’accés a aquest portal es fa a través del VUS.

  • El portal s'anomena QVS_ACCES_POINT-Portal InfoDades de Qlikview

  • Actualment (novembre 2016) el portal permet 10 llicències de sessió i 86 llicències nominals.

• Publicació municipal Gestió del portal on es publiquen els informes que han de consultar usuaris externs dels ens locals, ja siguis usuaris de la Xarxa de Biblioteques o de la Xarxa de Municipis. L’accés a aquest portal es fa a través del VUS.

  • El portal s'anomena QVS_INFODADES-Portal publicació informes de dades locals

  • Actualment (novembre 2016) el portal permet 23 de sessió.

• Publicació pel ciutadà.  Gestió dels informes que han de consultar usuaris sense identificar. L’objectiu és oferir informació directament al ciutadà. L’accés a aquest portal es farà a través d’una accés (link) des de la pàgina web de la DIBA (www.diba.cat).

  • El portal s'anomena QVS_HERMES

  • Actualment (març 2016) el servidor permet la publicació d’un únic informe sense limitació de consultes concurrents.

Potal QlikSense: 1 cluster de 3 servidors per la difusió dels informes desenvolupats en QlikSense:

• Publicació amb autenticació (corporativa i municipal): portal al qual s'accedeix de forma autenticada.
  • El portal s'anomena QS_INFOANALISIS
  • El portal permet l'accés a usuaris tots els tipus de llicència disponible: Professional, Analysis i Capacity analyzer.
  • Cal diferenciar l'accés dels usuaris de la corporació pel virtual proxy: inten i els usuaris de l'extranet dels ens locals pel virtual proxy: extern
• Publicació en obert (públic): accés anònim a l'Stream PÚBLIC
  • L'accés anònim es fa pel virtual proxy: public

Per Qliksense, es disposi de 3 entorns:

• Entorn de desenvolupament --> Accés per QS_INFOANALISI_PRO
• Entorn de test --> Accés per QS_INFOANALISIS_TST
• Entorn de productiu --> Accés per QS_INFOANALISIS

NAS. Servidor on s’emmagatzemen totes les carpetes de treball dels diferents projectes.

Nprinting. Utilitat de Qlikview pel format d’informes que es volen generar en un format estàtic: PDF, Word, PowerPoint o Excel i/o que es requereixi distribuir per correu a varis destinataris. Actualment es treballa amb la versió 17 o posterior de Nprinting que es pot connectar amb Qlikview i QlikSense.

Geoanalytics Plús: component per la generació de mapes. Es disposa de dos entorns:

Geoanalytics de productiu --> https://geoanalisis.diba.cat, està connectat amb Qlikview i QlikSense de productiu.

Geoanalytics de test --> Connectat amb QlikSense de desenvolupament i de test.

Client per desenvolupadors

El Qlikview Desktop és el programari client que permet desenvolupar informes Qlikview.

L’ús d’aquest programari és nominal i requereix d’una llicència personal.

Actualment (març 2016) la Diputació disposa de 75 llicències nominals.

El QlikSense Desktop és el programari client que permet desenvolupar informes QlikSense.

L'ús del programari requereix una llicència professionarl.

L'entorn del Servidor QlikSEnse de desenvolupament també s'utilitzarà per el desenvolupament d'informes QlikSense.

4. Arquitectura d’una solució Qlik a la DIBA

Per tal de complir la segona premissa “La solució ha de ser de fàcil manteniment un cop lliurada i complir les best practices de programació.”, el primer que hem de fer es definir com entenem l’arquitectura d’una solució Qlikview.

Una projecte Qlikview comporta les següents components:

4.1. Capa de dades

Està formada per totes aquelles dades que han de participar en la creació del núvol de dades de la solució. Como ja em comentat a la quarta premissa, s’han de fer servir les dades disponibles al catàleg de dades de la DIBA sempre que es pugi. Amb la qual cosa, abans de connectar-se a cap origen de dades s’ha de preguntar a l'equi de BI de la OTI, si aquest origen està ja disponible o si s’està fent un projecte actualment que ens el fa disponible en breu.

L’objectiu es no fer esforços duplicats.

4.2. Procés d’extracció (ETL)

La capa d’extracció és l’encarregada de connectar-se als orígens de dades, fer una còpia de la informació necessària des estructurar-la respecte el model relacional de la que majoritàriament provenen i estructurar-la per adaptar-la al model analític.

Com ja em comentant és molt important que la informació no es tracti pensant només en l’ús del projecte actual sinó també en l’ús posterior d’aquesta informació per part d’altres projectes.

Cal fer doncs:

• La transformació de la dada, per tal de facilitar la creació de núvols de dades actuals i futurs
• Optimitzar els accessos als orígens de dades, per tal d’impactar el mínim possible (accessos incrementals)
• Facilitar estructures estàndards sobretot amb les dades transversals de la Diputació de Barcelona.

Per aquesta raó, des de la DIBA volem que la creació dels processos d’ETL estiguin organitzats per capes de QVD’s (veure capa0 i capa1 mes endavant) i organitzats en núvols de dades.

Hi ha dos tipus de dades que es requereix extraure, dades de les BBDD relacionals pròpies de la DIBA i dades externes, provinents de plataformes Open Data, conexions a BBDD externes,....

Les eines TI que actualment s’utilitzen per a fer l’extracció de dades són les següents:

• Qlikview
• Pentaho, sobretot per a dades externes de la DIBA

4.2.1. La capa de QVD’s

Es fa fent servir el QVD generator App. És molt important que tots el QVD’s generats tinguin informació de comentaris, ja que aquesta informació es farà servir posteriorment per identificar clarament a quina capa pertany el QVD i quina semàntica s’està fent servir.

Per exemple, si estem creant un QVD per la CAPA 1 (veure mes endavant capa0 y capa1)

'EMPLEATS_ACTIUS':
LOAD PER_DNI,
     [Numero Empleat Actiu],
     [Nom Empleat Actiu],
     [Orgànic SAP],
     [Codi Dep. SAP],
     [Usuari Actiu]
FROM
EMPLEATS.qvd (qvd);
COMMENT Table 'EMPLEATS_ACTIUS' WITH "C1- Taula d'empleats actius";
STORE 'EMPLEATS_ACTIUS' INTO $(vRutaC1)EMPLEATS_ACTIUS_C1E.QVD(qvd);

Aquesta informació de comentaris és obligatòria ja que es llegeix automàticament per una aplicació de Qlikview que ha fet OTSIM.Qlik per tal de tenir tota la informació documentada i poder publicar-la al catàleg de dades de la DIBA un cop es desplega el projecte.

4.2.2. Capa QVD : La Capa 0 (zero)

La capa cero ha de ser una capa de dades extretes directament de l’origen i sense cap transformació. Si es tracta d’una taula d’un SGBD el nom del QVD generat serà el de la taula extreta, si es tracta d’un fitxer el nom serà el del fitxer.

Aquesta capa és una analogia del que faríem en un datawarehouse clàssic amb la Persistent Staging Area.

(Exemples de taules d’oracle estretes d’un projecte de Qlikview)

Si la dada s’extreu d’una font externa (ex. Idescat, portal dades obertes de la Generalitat,...) es important valorar amb la OTSIM.Qlikview si és necessari emmagatzemar la dada en el magatzem de dades ORACLE o altre. Per aquesta gestió s’hauria d’utilitzar Pentaho.

4.2.3. Capa QVD : La Capa 1

La capa 1 ha de ser una capa de dades tractades per normalització.

Es poden transformar les dades, per exemple donar el format correcte:

• El codi INE en alguns casos es llegeix sense el 0 davant, s’hauria de posar el ID en 5 dígits i zeros davant.

• El CNAE, es requereix visualitzar també els casos que tenen valor 0. Per tant es complimenta la taula amb els valors z.

• Treballar el format de les dades o imports si és necessari.

• Etc...

En aquesta capa cal etiquetar correctament cada dimensió i valor. En aquesta fase alimentem el diccionari de dades del Catàleg de dades de la DIBA susceptibles a ser analitzades.

Aquesta capa és una analogia del que faríem en un datawarehouse clàssic amb les primeres fases del Staging Area abans de carregar el model.

Capa1 --> Dades transformades i etiquetades. Cada qvd representa una entitatt.

L’estructura de capes de les dades té com a objectiu disposar d’un magatzem de dades normalitzades i ben etiquetades, preparades per poder formar part d’un model analític que doni la possibilitat de crear aplicacions analítiques com els quadres de comandament.

4.2.4. Els extractors

Hi ha l’extractor de la capa 0 y l’ extractor de la capa 1. La nomenclatura ha de ser : Extraccio_(nomTaulaoFitxer)_CapaX.qvw

Capa 0 exemple

LOAD *;
       SQL SELECT *
       FROM HG2.$(vTabla);
       store $(vTabla) into $(vRutaQVD)$(vTabla).qvd;

Capa 1 exemple

CNAE:
LOAD text(STC_CODI) AS [Codi CNAE],
     '93'&'..'&text(STC_CODI) AS [CNAE],
     '93' as 'Tipus CNAE',
     len(text(STC_CODI)) as 'CNAE Nivell',
     STC_NOM AS Descri_CNAE,
     text(STC_CODI)&' - '&STC_NOM AS Descri_CODI_CNAE,
     if(len(text(STC_CODI))>1,text(left(text(STC_CODI),2))) AS 'CNAE Nivell 2',
     if(len(text(STC_CODI))>2,text(left(text(STC_CODI),3))) AS 'CNAE Nivell 3',
     STC_DIGIT1 as 'Grup CNAE',
     STC_SECTOR as 'Sector',
     ApplyMap('DESCRIPCIONS',STC_SECTOR) as 'Descri_Sector';

4.2.5. Capa Núvol de dades

És on farem la tasca de modelatge i revisarem la semàntica. És molt important no carregar tota la lògica de l’script de la base de dades analítica sobre el núvol de dades, ha de ser un script net i fàcil de comprendre. Amb el que recomanen crear capes addicionals com per exemple per carregar separadament dimensions i taules de fets.  

Cal que totes les entitats del núvol tinguin una etiqueta entenedora des del punt de vista de l’usuari i que estiguin documentades segons les recomanacions de OTSIM.Qlik.

La nomenclatura ha de ser.

Nom: NUVOL-XXX

Tots els núvols de dades han de complir les següents normatives:

4.2.5.1. Capa de configuració.

Exemple:

Exemple config

//NUVOL_LABTURISME

let vDirQvd = 'QVD\';

let vDirQvdHermes = '\\SWCS445\qvd\HERMES\';

let vDirQvdQlikDades = '\\NAS\APPS\QLIK_DADES\TERRITORI\';

let vRutaTurSostenible= '..\QVD\QLIK_DADES\';

let vEquivTerritories = 'Configuració\';

let vFitxersEnquestes = '..\..\FITXERS\Perfil\';

let vFitxersCCAE = '..\..\FITXERS\INSS_CCAE\';

let vFitxers = '..\..\FITXERS\';

let vTerritoris='\\NAS\APPS\QLIK_DADES\TERRITORI\TERRITORIS.QVD';

let vMunicipis='\\NAS\APPS\QLIK_DADES\TERRITORI\MUNICIPIS.QVD';

let vPaisosRegionsTurisme='\\SWCS445\qvd\HERMES\HG2_PAISOS_REGIONS_TURISME.QVD';

4.2.5.2. Comentaris a les taules

Exemple:

'EMPLEATS_ACTIUS':
LOAD PER_DNI,
     [Numero Empleat Actiu],
     [Nom Empleat Actiu],
     [Orgànic SAP],
     [Codi Dep. SAP],
     [Orgànic Reduït],
     [Descripció Orgànic],
     [Nivell Orgànic actual],
     Nivell_ampliat_actual,
     Nivell_Ini_actual,
     Nivell_Ini_Organizació_actual,
     GGRL_CEN_DESC,
     GGRL_EMP_CEN_CODISAP,
     [Usuari Actiu],
     [Usuari Oracle Actiu]
FROM
EMPLEATS.qvd
(qvd);
COMMENT Table 'EMPLEATS_ACTIUS' WITH "C1- Taula d'empleats actius";

STORE 'EMPLEATS_ACTIUS' INTO $(vRutaC1)EMPLEATS_ACTIUS_C1E.QVD(qvd);

4.2.5.3. Scripts senzill i amb data de càrrega

Exemple:

4.2.6. Estructura de carpetes del procés ETL

Per tal de mantenir eficientment tots el projectes que es desenvolupen a la DIBA, és necessari tenir un ordre en els directoris i carpetes que fem servir per les solucions, en l’entorn productiu, per tant en el servidor.

Dintre del directori QVDs, tenim el Catàleg de dades disponibles de la DIBA.

Catàleg de dades disponible

Els QVDs els organitzem per temes.

I dintre de cada tema, estructurem en capa 0 i 1 i aquelles capes mes pròpies del projecte

Dintre de la part del Núvol, separem el scripts  i els excels amb informació addicional

4.3. Capa Presentació

4.3.1. Aplicacions Qlikview

Per tal de mantenir una imatge uniforme en totes les aplicacions de Qlikview independent de qui l’ha creat, cal utilitzar el full d’estils establert per la Diputació de Barcelona.

Hi ha una carpeta en el NAS, anomenada QLIK_DADES, on hi podreu trobar la descripció del full d’estils corporatiu, plantilles, exemples, per facilitar el seu desenvolupament.

Aquesta uniformitat és molt important en la publicació de dades de cara als Ens locals i de cara al Ciutadà, és a dir, per a tota aplicació que es publiqui en el portal de l’extranet (QVS_INFODADES) i del ciutadà (QVS_HERMES).

Cal treballar bé la visualització de les dades per tal de mostrar la informació de la forma més entenedora.

Es molt important transmetre a OTSIM.Qlik una estimació del nostre projecte (veure model d’execució). Una bona estimació ens facilita la prevenció de riscos i evita l’afectació a d’altres projectes i serveis.

4.3.2. Aplicacions QlikSense

Per defectes, les apps de Qliksense utilitzaran el tema Tema_DIBA, que conté l'estil de la Diputació, tipografia, paletes de colors, etc...

En el cas que sigui necessari es podrà amplicar el Tema_DIBA tot creant un nou tema.

En el cas que sigui necessari, es a dir, que el producte a desenvolupar tingui un format i estil propi, caldrà crear un nou Tema específic del producte. Caldrà anomenar-lo _Tema_XXX on XXX identifica el nou producte.

Cal considerar:

1. En el tema, no s'ha d'insertar definicions per a objectes específics de l'informe. Per fer-ho es pot utilitzar l'element del Multikpi, on hi ha un apartat que ofereix la possibilitat de importar un arxiu css, amb les especificacions.
2. Les opcions de disseny que s'especifiquin en el Tema cal acordar-les amb la Diputació, sobretot si la difusió del document es oberta.
3. Cal utilitzar el menú vertical, de l'extensió MzPanel3

5. Millors pràctiques de programació

5.1. Definició de variables

Les variables susceptibles a ser configurades en un fitxer Qlikview, ja sigui, el núvol de dades com l’aplicació Qlikview, hauràn de definir-se en un fitxer de configuració (*.qvs), que es carregarà a l’aplicació Qlikview mitjançant una ruta relativa. Això permetrà als diferents responsables poder manipular el seu valor segons es requereixi.

Un exemple de les variables de configuració són: rutes dels fitxers que es carreguen al document, variables d’activació de la secció accés, etc.

Per exemple:

5.2. Nomenclatura de les aplicacions Qlikview

Tota aplicació Qlikview, quadre de comandament o aplicació analítica que constitueixi la capa de presentació de la solució, caldrà que s’anomeni amb el prefix QVS_ davant, de la següent forma:

QVS_XXXX-text descriptiu on XXXX sigui un identificador mnemotècnic de l’aplicació.

Tota aplicació QlikSense, quadre de comandament o aplicació analítica que constitueixi la capa de presentació de la solució, caldrà que s'anomeni amb el prefix QS_ davant, de la següent forma:

QS_XXXX-text descriptiu on XXXX sigui un identificador mnemotècnic de l’aplicació.

5.3. Requeriments mínims aplicacions de la capa de presentació

Ha de reflexar les següents dades:

• Última data i hora d’actualització del document

• Data i hora de l’última extracció dels orígens de dades

• Referència a la versió

La capa de presentació no ha de contenir cap tipus de programació que sigui propia del núvol de dades.

La capa de presentació s’ha de connectar amb el núvol de dades a través de la instrucció Binary.

6. Gestió d’usuaris i permisos

Els permisos de les aplicacions Qlik es defineixen en el sistema de gestió QVGPU, programat en Java i generat per la pròpia Diputació.

D’aquest sistema s’extreuen els qvd pertinents que s’hauran de carregar a la secció d’accés de les aplicacions Qlikview o QlikSense, per tal de definir la gestió de permisos.

7. L’entorn de treball de Qlik a la DIBA

Les aplicacions Qlik es desenvolupen amb l’ajuda del programari Qlikview Desktop o QlikSense Desktop que s’instal·la directament en el nostre PC.

Tot i així tota aplicació que es vulgui difondre a través dels portals de difusió de la plataforma Qlik, caldrà gestionar-la en els servidors de Qlikview o QlikSense. Per tant la plataforma Qlik funciona sobre una arquitectura client-servidor. Diverses aplicacions i fitxers conformen una solució Qlik per un client, per tant l’organització d’aquestes aplicacions i fitxers és important tenir-la estandarditzada per poder fer més fàcil el seu manteniment.

A continuació presentem les diferents components que configuren la plataforma i ens ajuden a treballar d’una forma més eficaç:

7.1. Arquitectura client – servidor

Client:

• Qlikview Desktop (programari client que permet desenvolupar informes Qlikview)

• Navegador Web: Accés a portal web Acces Point (client que permet consultar informes Qlikview).

Servidor:

• 3 servidors dedicats a Qlikview. Cada servidor :

  • Qlikview Web Server (proporciona el portal web de publicació dels informes Qlikview).

  • Consola d’administració del servidor

    • Administració de l’execució programada de tots els processos de càrrega

    • Gestió de les llicències

    • Control de la publicació

• Servidor NAS: Servidor on es defineix l’entorn de treball de l’usuari

• Servidor de Nprinting

• 3 entorns dedicats a QlikSense

7.2. Estructura de carpetes de l’entorn de treball de l’usuari

S’habilita pel projecte una carpeta en el servidor NAS, on es donarà accés al proveïdor per tal que pugui fer els lliuraments dels diferents fitxers Qlik o documentació.

Es donarà accés al proveïdor via VPN en aquesta carpeta.

La carpeta s’estructurarà de la següent manera:

La funcionalitat de les diferents carpetes és la següent:

Carpeta APLICACIONS

En aquesta carpeta s’hi ubicarà les aplicacions que es vagin generant durant el projecte. S’obrirà una carpeta per a cada aplicació, on s’hi guardaran les diferents versions.

Addicionalment hi trobem les següents subcarpetes:

Carpeta A PUBLICAR

S’utilitzarà per tal de deixar els diferents fitxers Qlikview que hagin d’ubicar-se en el servidor. Aquests fitxers seràn:

Els que formin part del procés ETL

Quadres de comandament o aplicacions analítiques que es requereixi publicar a nivell corporatiu en el servidor de la Intranet

Quadre de comandament o aplicacions analítiques que es requerixi publicar en el servidor extranet, per tant dirigida als ens locals.

La sol·licitud de traspàs al Servidor (o pas a productiu) haurà de formalitzar-se amb una petició a través del Service Desk assignada al Responsable de la DSTSC.

Carpeta NUVOL

Hi ha un accés directe als núvols de dades que hi ha en el servidor.

Cal considerar que el núvol de dades definitiu sempre s’ubicarà en el servidor.

Carpeta PUBLICACIO_GRUP

Tot fitxer ubicat en aquesta carpeta es publicarà directament al portal web Acces Point per tal que pugui ser consultat per totes les persones que tinguin accés al grup de treball del projecte.

8. Documentació necessària

Per publicar qualsevol projecte Qlikview és imprescindible disposar de la documentació següent:

1. Document de definició del projecte (model annex 1)

2. Document capa d’extracció de dades (model annex 2)

3. Document núvol de dades (model annex 3)