Aplicación de técnicas de Business Intelligence en la gestión del Sistema de Dependencia de la

280 Pages·2010·9.11 MB·Spanish

by  Rafael Izquierdo Borrallo

Aplicación de técnicas de Business
Intelligence en la gestión del Sistema de
Dependencia de la Conselleria de Bienestar

Social

Proyecto Final de Carrera
Ingeniería Superior en Informática
15 de julio de 2010

ESCUELA TECNICA SUPERIOR DE INGENIERIA INFORMATICA

UNIVERSIDAD POLITECNICA DE VALENCIA
AUTOR: RAFAEL IZQUIERDO BORRALLO
DIRECTOR: CÉSAR FERRI RAMÍREZ

TUTORA: AMPARO BELMONTE ORTS
0
1
0
2
/
7
0
/
5
1
|
la
ic
o
S
r
a
t
s
e
n
e
iB
e
d
a
ire
lle
s
n
o
C
a
l e
d
a
icn
e
d
n
e
p
e
D
e
d
a
m
e
t
s
iS
le
d
n
óit
s
e
g
a
l n
e
e
c
n
e
g
ille
t
n
I s
s
e
n
is
u
B
e
d
s
a
c
in
c
é
t
e
d
n
ó
ic
a
c
ilp
A

1
Agradecimientos

El desarrollo de este trabajo ha sido posible gracias a la ayuda de varias personas a las cuales quiero
mostrar todo mi agradecimiento. 0
1
0
2
/
En primer lugar quiero agradecer a Amparo Belmonte, mi tutora en la Conselleria de Bienestar Social, la 7
0
/
ayuda y la confianza que ha depositado en mí. Gracias por ofrecerme este proyecto y por creer en mí. 5
1
|
a l
Asimismo quiero dar las gracias a Luis Albors, cuya ayuda ha sido inestimable para que pudiese realizar ic
o
este proyecto. Gracias por haberme resuelto cada una de las dudas que me han surgido. S
r
a
t
s
e
También quiero destacar la gran ayuda de César Ferri, mi director de proyecto, sin el cual la finalización n
e
iB
de este proyecto no habría sido posible. e
d
a
Por último, gracias a toda mi familia por animarme y darme vuestro apoyo a lo largo de estos meses. ire
lle
s
n
o
C
a
l e
d
a
ic
n
e
d
n
e
p
e
D
e
d
a
m
e
t
s
iS
le
d
n
ó
it
s
e
g
a
l n
e
e
c
n
e
g
ille
t
n
I s
s
e
n
is
u
B
e
d
s
a
c
in
c
é
t
e
d
n
ó
ic
a
c
ilp
A

2
0
1
0
2
/
7
0
/
5
1
|

la
ic
o
S
r
a
t
s
e
n
e
iB
e
d
a
ir
e
lle
s
n
o
C
a
l e
d
a
ic
n
e
d
n
e
p
e
D
e
d
a
m
e
t
s
iS
le
d
n
ó
it
s
e
g
a
l n
e
e
c
n
e
g
ille
t
n
I s
s
e
n
is
u
B
e
d
s
a
c
in
c
é
t
e
d
n
ó
ic
a
c
ilp
A

3
Aplicación de técnicas de Business
Intelligence en la gestión del Sistema de
Dependencia de la Conselleria de Bienestar
Social 0
1
0
2
/
7
Proyecto Final de Carrera 0
/
5
1
|
a l
1. INTRODUCCIÓN AL PROYECTO .......................................................................................... 8 ic
o
1.1. OBJETIVOS GENERALES ............................................................................................. 8 S r
a
1.2. ANTECEDENTES ...................................................................................................... 8 ts
e
2. CONCEPTOS BÁSICOS ................................................................................................... 11 ne
2.1 LEY DE DEPENDENCIA .............................................................................................. 11 iB
e
2.2 DESCRIPCIÓN DEL FUNCIONAMIENTO DEL SISTEMA DE DEPENDENCIAS ..................................... 13 d a
2.2.1 VALORACIÓN INICIAL ........................................................................................ 15 ire
2.2.2 REVISIÓN ....................................................................................................... 18 lles
n
2.2.3 HOMOLOGACIÓN ............................................................................................. 19 oC
2.3 BUSINESS INTELLIGENCE ........................................................................................... 20 al e
2.3.1 FUENTES DE DATOS .......................................................................................... 21 d a
2.3.2 ALMACÉN DE DATOS O DATAWAREHOUSE ............................................................... 22 icn
e
2.3.3 PROCESO ETL ................................................................................................. 26 d
n
e
2.3.4 HERRAMIENTAS BI ............................................................................................ 27 p
e
D
3. ANÁLISIS DEL SISTEMA ................................................................................................... 29 e
3.1 BASE DE DATOS ..................................................................................................... 29 d a
m
3.1.1 DISEÑO BASE DE DATOS ..................................................................................... 30
e
t
3.1.2 APLICACIONES ................................................................................................. 31 siS
3.1.2.1 APLICACIÓN DEPENDENCIA ........................................................................... 31 le
d
3.1.2.1.1 SOLICITUDES ...................................................................................... 32 n
ó
3.1.2.1.2 VALORACIONES ................................................................................... 34 its
e
g
3.1.2.1.3 RESOLUCIONES ................................................................................... 35 a
3.1.2.1.4 PIA ................................................................................................. 36 l n
e
3.1.2.1.5 MANTENIMIENTO ................................................................................. 37 ec
n
3.1.2.1.6 LISTADOS/ESTADÍSTICAS ......................................................................... 40 eg
3.1.2.1.7 ADMINISTRACIÓN ................................................................................. 41 ille
t
n
3.1.2.1.8 CAMBIO DE CONTRASEÑA ....................................................................... 41 I s
s
3.1.2.2 APLICACIÓN INFORMES DEPENDENCIA .............................................................. 41 e
n
3.1.2.2.1 IMPRESIONES ....................................................................................... 42 isu
B
3.1.2.2.2 INTERCAMBIO TELEMÁTICO ..................................................................... 44 e
d
3.2 ALMACÉN DE DATOS ............................................................................................... 45 s
a
c
3.2.1 DISEÑO ALMACÉN DE DATOS ............................................................................... 45 in
c
4. PROCESO ETL ............................................................................................................ 49 ét
e
4.1 SPOON .............................................................................................................. 49 d
n
4.1.1 CONCEPTOS PREVIOS ......................................................................................... 49 ó
ic
4.1.2 ESQUEMA GENERAL DEL PROCESO ETL ................................................................... 52 ac
4.1.2.1 DELETE_DW_DEPENDENCIA .......................................................................... 53 ilpA

4
4.1.2.2 TIPORES .................................................................................................. 53
4.1.2.3 POBLACIONES............................................................................................ 55
4.1.2.4 ZCOBER ................................................................................................. 60
4.1.2.5 ZCOBER_MUNI ....................................................................................... 61
4.1.2.6 ESTADOSOL ........................................................................................... 63
4.1.2.7 USUARIOS .............................................................................................. 65
0 4.1.2.8 SOLICITANTE ......................................................................................... 67
1
0
2 4.1.2.9 SOLICITUDES_F ...................................................................................... 75
/
70 4.1.2.10 RESOLUCIONES ..................................................................................... 78
/
5 4.1.2.11 OTRAS_DIM_SOL .................................................................................. 83
1
| 4.1.2.12 OTRAS_DIM_RES ................................................................................... 96
la
ic 4.1.2.13 RES_SOL_DIM ...................................................................................... 107
o
S r 4.2 KITCHEN .......................................................................................................... 108
a
ts 5. EXPLOTACIÓN DE LA INFORMACIÓN MEDIANTE LA HERRAMIENTA MICROSTRATEGY ........................ 109
e
n 5.1 INTRODUCCIÓN HERRAMIENTA MICROSTRATEGY ........................................................... 109
e
iB e 5.2 CREACIÓN DEL PROYECTO ....................................................................................... 111
d a 5.3 CREACIÓN DE OBJETOS DE APLICACIÓN. ....................................................................... 117
ire 5.3.1 FILTROS. CREACIÓN EJEMPLO: VAL+HOMORE ........................................................ 117
lle
s 5.3.2 INDICADORES. CREACIÓN EJEMPLO: VALHOMINDICADORRE ........................................ 120
n
oC 5.3.3 GRUPOS PERSONALIZADOS. CREACIÓN EJEM: INFORMESOCIALRESUMENESTA .................... 123
a
l e 5.3.4 CONSOLIDACIONES. CREACIÓN EJEMPLO: DISCA-PRESTACIONES-RE ............................... 125
d
a 5.4 INFORMES. CREACIÓN EJEMPLO INFORMESOCIAL-RE ........................................................ 128
icn 5.4.1 CREACIÓN NUEVO INFORME. .............................................................................. 128
e
dn 5.4.2 INCLUSIÓN DE LOS OBJETOS EN LA CUADRICULA ........................................................ 129
e
p 5.4.3 CREACIÓN DEL FILTRO DE OBJETO. ....................................................................... 130
e
D
e 5.4.4 EJECUCIÓN DE INFORMES. .................................................................................. 132
d
a 5.4.5 APLICACIÓN FILTROS DE VISUALIZACIÓN. ............................................................... 133
m
e 5.4.6 CREACIÓN DE GRÁFICOS. .................................................................................. 134
t
s
iS 5.4.7 EXPORTACIÓN INFORMES. ................................................................................. 135
led 5.5 DOCUMENTOS MICROSTRATEGY ................................................................................ 135
n
ó 5.5.1 CREACIÓN DOCUMENTOS. ................................................................................. 136
it
se 5.5.2 CONFIGURACIÓN PAGINA................................................................................... 136
g
a 5.5.3 CREACIÓN ENCABEZADO ................................................................................... 137
l n
e 5.5.4 AGREGACIÓN DE LOS INFORMES. .......................................................................... 138
e
c 5.5.5 APLICACIÓN DE ESTILOS A LA CUADRICULA. ............................................................. 139
n
e
g 5.5.6 CREACIÓN GRÁFICOS. ...................................................................................... 140
illet 5.5.7 INSERCIÓN DE SECCIONES. ................................................................................. 142
n
I s 5.5.8 CREACIÓN TEXTO AUTOMÁTICO. ........................................................................ 142
s
e
n 5.5.9 CONVERSIÓN EN PDF. ...................................................................................... 142
is
u 5.6 CUADRO DE MANDO .............................................................................................. 142
B
ed 5.6.1 CREACIÓN PROTOTIPO CUADRO DE MANDO. .......................................................... 143
sa 5.6.2 CONFIGURACIÓN DE PÁGINA. ............................................................................. 143
c
in 5.6.3 AGREGACIÓN DEL CONJUNTO DE DATOS. ............................................................... 144
c
é
t e 5.6.4 CREACIÓN DE PANELES. ..................................................................................... 144
d
n 5.6.5 INSERCIÓN DE IMÁGENES. .................................................................................. 146
ó
ic 5.6.6 INSERCIÓN DE SELECTORES. ................................................................................ 146
a
cilp 5.6.7 INSERCIÓN DE FORMAS. ..................................................................................... 148
A 5.6.8 INSERCIÓN DE CUADRICULAS .............................................................................. 148

5
5.6.9 INSERCIÓN DE WIDGETS Y GRÁFICOS ..................................................................... 148
5.7 ACCESO WEB. ...................................................................................................... 151
6. IMPACTO ................................................................................................................. 152
7. CONCLUSIONES ......................................................................................................... 153
8. BIBLIOGRAFÍA Y ENLACES WEB ........................................................................................ 155
ANEXO A: DISEÑO DE LAS TABLAS DE LA FUENTE DE DATOS ................................................... 157
SOLICITANTE ......................................................................................................... 158 0
DEP_CENTROS ....................................................................................................... 164 1
0
2
DEP_DIR_SMAD ...................................................................................................... 165 /
7
0
DEP_AETSOL .......................................................................................................... 167 /
5
SOLICITUD ............................................................................................................ 169 1
|
DEP_PIA ................................................................................................................ 178 a l
ic
DEP_UNIDADFAMILIAR ........................................................................................... 182 o
S
MEDICOS ............................................................................................................... 183 ra
t
s
DEP_RESOLUCION .................................................................................................. 184 e
n
e
DOC_REQ .............................................................................................................. 187 iB
VALORADORES ...................................................................................................... 189 e
d
a
DEP_SITUACION ..................................................................................................... 190 ir
e
DEP_CUIDADOR ..................................................................................................... 191 lle
s
REPRESENTANTE .................................................................................................... 193 n
o
C
DEP_REL_CUIDADORES .......................................................................................... 195 a
DEP_AVISOS ........................................................................................................... 197 l ed
a
ANEXO B: TABLAS DE INDICADORES Y DIMENSIONES DEL ALMACÉN DE DATOS............................ 199 ic
n
INDICADORES............................................................................................................ 200 e
d
DIMENSIONES ............................................................................................................ 201 ne
p
ANEXO C: DISEÑO DE LAS TABLAS DEL ALMACÉN DE DATOS ................................................... 208 eD
DEP_ENVIOAEAT_LU .............................................................................................. 209 e
d
DEP_EPIA_LU ......................................................................................................... 209 a
m
DEP_ESTADOSOL_LU .............................................................................................. 210 et
s
DEP_GYN_LU ......................................................................................................... 211 iS
le
DEP_INFSOCIAL_LU ................................................................................................ 211 d
n
DEP_MEXTINCION_LU ............................................................................................ 212 óit
s
DEP_POBLACION_LU .............................................................................................. 213 e
g
a
DEP_RESOLUCIONES_F ........................................................................................... 214 l n
DEP_SOLICITANTE_LU ............................................................................................ 215 e e
c
DEP_SOLICITUDES_F ............................................................................................... 216 n
e
g
DEP_TIPOSOL_LU ................................................................................................... 218 ille
DEP_TIPORES_LU ................................................................................................... 219 tn
DEP_USUARIOS_LU ................................................................................................ 220 I ss
e
DEP_ZCOBER_LU ................................................................................................... 221 nis
u
LU_MES ................................................................................................................. 221 B
e
SECTOR ................................................................................................................. 222 d
s
TAB_COMA ............................................................................................................ 223 ac
in
TAB_MUNI ............................................................................................................. 224 c
é
t
TAB_PROV ............................................................................................................. 224 e
d
TIEMPO_LU ........................................................................................................... 225 n
ó
ANEXO D: EJEMPLO LOG QUE SE PRODUCE AL EJECUTAR EL ESQUEMA ETL ................................. 227 ica
c
ilp
A

6
ANEXO E: TABLA DE OBJETOS INFORME MICROSTRATEGY .................................................... 232
ATRIBUTOS .............................................................................................................. 233
FILTROS .................................................................................................................. 240
INDICADORES............................................................................................................ 242
GRUPOS PERSONALIZADOS ............................................................................................ 242
CONSOLIDACIONES ..................................................................................................... 255
0 ANEXO F: COMPOSICIÓN INFORMES ................................................................................ 260
1
0 ANEXO G: COMPOSICIÓN DE DOCUMENTOS ..................................................................... 268
2
/7 ANEXO H: INFORME ESTADÍSTICO SITUACIÓN DE DEPENDENCIA ............................................. 271
0
/ ANEXO I: RESUMEN ESTADÍSTICO SITUACIÓN DE DEPENDENCIA .............................................. 272
5
1 ANEXO J: PANTALLAS CUADRO DE MANDO ....................................................................... 273
|

la
ic
o
S
r
a
t
s
e
n
e
iB
e
d
a
ir
e
lle
s
n
o
C
a
l e
d
a
ic
n
e
d
n
e
p
e
D
e
d
a
m
e
t
s
iS
le
d
n
ó
it
s
e
g
a
l n
e
e
c
ne
g
ille
t
n
I s
s
e
n
is
u
B
e
d
s
a
c
inc
é
t
e
d
n
ó
ic
a
c
ilp
A

7
1. Introducción al proyecto

Este documento describe el proyecto “Aplicación de técnicas de Business Intelligence en la gestión del
Sistema de Dependencia de la Conselleria de Bienestar Social”. Dicho proyecto explica el desarrollo de las
estrategias y aplicación de herramientas propias de la inteligencia empresarial que proporcionan a la directiva y los
mandos intermedios de este ente, la posibilidad de analizar los datos estadísticos manejados sobre el sistema de
dependencia mediante el uso de informes, gráficos y cuadros de mando. 0
1
0
2
/
1.1. Objetivos Generales 7
0
/
5
1
El objetivo de este proyecto es analizar las fases y metodologías que se desarrollan para convertir la |
información residente en las distintas fuentes de datos que alimentan el sistema de dependencia de la Conselleria a l
ic
o
de Bienestar Social en conocimiento, de manera que se otorgue apoyo al directivo en la toma de decisiones. Este S
r
análisis abarca diversas fases: a
t
s
e
 El estudio de las fuentes de datos que proporcionan la información sobre la situación de dependencia en n
e
la Comunidad Valenciana. iB
e
d
 La creación del almacén de datos, es decir, el lugar donde se depositaran los datos depurados y a
ir
normalizados por el proceso ETL. e
lle
 Diseño y ejecución del proceso ETL (Extract, Load and Transform), con el fin de obtener, depurar y sn
o
transformar los datos de las distintas fuentes de información que componen el sistema de dependencia. C
a
 La explotación de cubos OLAP mediante la herramienta B.I (Business Intelligence o Inteligencia Empresarial) l e
d
Microstrategy con el fin de aprovecharse de las numerosas ventajas que aporta esta herramienta en la a
ic
n
generación de informes y en el análisis a posteriori de los datos. e
d
n
e
p
1.2. Antecedentes e
D
e
d
El desarrollo de informes en el departamento de informática de la Conselleria de Bienestar Social se ha a
m
estado realizando mediante documentos Excel. Un claro ejemplo es el documento “Informe Estadístico”, formado e
t
s
por dos hojas de cálculo. En la primera hoja se presentaban las tablas y el texto que daban formato a la iS
le
presentación. En la segunda hoja se incluían los datos que eran referenciados desde la primera hoja. d
n
ó
it
Primera Hoja: se
g
a
l n
e
e
c
n
e
g
ille
t
n
I s
s
e
n
is
u
B
e
d
s
a
c
in
c
é
t
e
d
n
ó
ic
a
cilp
A

8
Segunda Hoja:

0
1
0
2
/
7
0
/
5
1
|

la
ic
o
S
r
a
t
s
e
n
e
iB
e
d
a
ir Para que el documento recogiese los datos estadísticos, se “pegaban” las columnas obtenidas de las tablas
e
lle DEP_ESTADISTICAS y DEP_ESTADISTICAS2 en la segunda hoja. Estas tablas fueron creadas específicamente
s
n
o en la base de datos de la dependencia (es decir, la base de datos donde se encuentran las tablas de las cuales
C
a derivan toda la información que se maneja en la Conselleria de Bienestar Social sobre la ley de dependencia) para
l e
d sacar los informes. Las tablas recogían automáticamente cada 7 días la información necesaria para la creación de
a
ic los documentos, de manera que permitía tener un seguimiento detallado de lo que iba ocurriendo con el sistema
n
e
d de dependencia en la Comunidad Valenciana.
n
e
p
e
D La realización de los informes mediante hojas Excel presentaba una serie de inconvenientes. Primero se
e
d constataba como un proceso costoso en tiempo ya que el encargado de generar el informe debía realizar
a
m manualmente el copiado y pegado de la información desde la base de datos al documento Excel. Así mismo,
e
ts también existía una baja eficiencia computacional y temporal debido a la sobrecarga que generaba en la base de
iS
le datos la existencia de las tablas DEP_ESTADISTICAS y DEP_ESTADISTICAS2 y la de los paquetes que realizaban
d
n la carga de datos. El coste de calcular todos los datos mediante las consultas SQL (COUNT) era demasiado alto
ó
it
s
e
g Un segundo inconveniente surgía en la flexibilidad a la hora de poder sacar los informes actualizados. Los
a
l n paquetes de la base de datos de la dependencia que extraían los datos estadísticos estaban programados cada 7 días,
e
e esto imposibilitaba sacar los datos actualizados en tiempo real o por lo menos al día. Naturalmente los paquetes se
c
ne podrían haber desarrollado para extraerse los datos diariamente, pero el coste y la sobrecarga que se producía en
g
ille una base de datos que soporta tanta información eran demasiado altos.
t
n
I s
s El tercer inconveniente era el propio manejo de los documentos Excel, ya que dificultaban la
e
n
is incorporación de gráficos, tablas, tipos de informe o cualquier otro elemento a gusto del usuario. La realización de
u
B los informes estadísticos tenía un rango muy limitado ya que no se podía explotar todo el potencial necesario en la
e
d creación de informes, lo que suponía una gran restricción a la hora de poder analizar los datos.
s
a
c
in
c A un nivel más funcional, se apreciaron problemas de centralización en el departamento de informática a
é
t
e la hora de crear los informes. Es decir, los trabajadores de la Conselleria de Bienestar Social que necesitaban en
d
n algún momento un informe, lo pedían directamente al departamento de informática. Esto provocaba un
ó
ic solapamiento en las funciones, por lo que al final existía una cierta saturación y un cierto riesgo de menor
a
c
ilp rendimiento.
A

9