Dbd Ma Jose Salazar.docx

Universidad Andina Simón Bolívar Sede Ecuador

Área de Gestión

Maestría en Gestión Financiera y Administración de Riesgos Financieros

DISEÑO DE BASE DE DATOS CON METODOLOGÍA KIMBALL

Autor: María José Salazar Silva

Quito, 2018

METODOLOGÍA KIMBALL 1. PLANIFICACIÓN: DOCUMENTO FINAL DEL PLAN DEL PROYECTO a.

b.

Alcance: i.

Alcance del proyecto: ○ Extracción y carga (Diseño y construcción de ETL´s) ○ Monitoreo (auditoría y fraude) de casos de posibles desvíos de fondos en reembolsos pagados a clientes

ii.

Alcance de la herramienta: La herramienta deberá permitir al usuario tener independencia para diseñar, rediseñar, elaborar, guardar, deshacer, eliminar querys y datos de nuevos y antiguos cruces, reportes y extracciones. Adicionalmente deberá permitir realizar agrupaciones, filtros, creación y uso de parámetros de cruce, inserción de fórmulas, es decir el usuario debe poder realizar las actividades similares a las definidas en los capítulos detallados a continuación sin la necesidad de un experto.

Identificación de tareas: i. ii. iii. iv.

c.

Instalación del ambiente de desarrollo Capacitación Qlik Sense Kick off - Fase de definición Desarrollo 1. Modelado de nube de datos 2. Desarrollo 3. Pruebas 4. Lanzamiento 5. Cierre

Programación de tareas, Planificación del uso de recursos y Asignación de carga de trabajo a recursos:

2. ANÁLISIS DE REQUERIMIENTOS: El requerimiento principal es el de realizar un monitoreo continuo que permita identificar alertas sobre posibles desvíos de fondos. En el proceso normal los clientes indican si desean que se les acredite el dinero del reembolso directamente a las cuentas indicadas por ellos o si lo quieren recibir mediante cheque. Al momento en que los liquidadores ingresan los números de cuenta, existe el riesgo que ingresen números de cuenta erróneos de los que ellos pueden ser propietarios o cercanos y así obtener el dinero de los reembolsos de los clientes en sus cuentas personales. Por ello, es necesario obtener reportes periódicos de los cambios que se realicen de números de cuenta o de casos en los que a pesar de que los clientes solicitan reembolsos a través de cheques, se ingresen en el sistema como acreditación a cuentas cuando las cuentas no son las de los clientes. Además, es importante conocer cuáles son los movimientos y los usuarios de estos, así como también los montos pagados. Con estos reportes se pretende revisar los soportes físicos con los que los usuarios realizan estos movimientos y en el caso de no contar con ellos, investigar si los beneficiarios de los pagos son los mismos empleados, en este caso se desvincularía al personal de la empresa. 3. MODELADO DIMENSIONAL: Las bases de datos y tablas necesarias son: Base de datos SIGMEP SIGMEP SIGMEP SIGMEP SIGMEP SIGMEP SIGMEP

Tabla de la base de datos CL08 – MOVIMIENTOS CL04 – CONTRATOS CL03 – PERSONAS CL09 - CATÁLOGO TRANSACCIONES LR02 – CABECERA-RECLAMO LR10 – PAGOS LR04 - DETALLE-RECLAMO

Para optimizar el rendimiento del motor de base de datos no se cargan todas las tablas sino solamente los campos necesarios para el modelo y se los almacena en tablas temporales como la tabla “Cambios_Pago_Inteligente” que contiene los siguientes campos: -

CL09_nombre_transacción CL03_apellidos_persona CL03_nombres_persona CL04_numero-contrato CL04_codigo-producto CL04_region

4. DISEÑO FÍSICO: Debido a que este no es el único reporte que se requiere y el alcance de la herramienta es bastante amplio y abarca toda la información de los sistemas empresariales se requiere de un servidor que por políticas de la compañía está bajo la modalidad de Hosting, por lo que las características necesarias son: a. CARACTERÍSTICAS DEL SERVIDOR: REQUERMIENTO NUEVO Características Un procesador de 8 Cores 64 GB RAM Windows Server 2012 R2 Dos discos de 300 GB en RAID 1 (Disco C:) Hosting Almacenamiento 120 GB en Storage RAID 5 (Disco D:) Backup de Información hasta 120 GB diarios con retención de 2 semanas y semanal con retención de 2 semanas

Servicio $

700,00

Instalació n $ 700,00

Iva $

168,00

Total $ 1.568,00

b. CONEXIONES: Se otorga permisos a varios usuarios para realizar diferentes reportes y consultas, pero solamente un usuario tiene el perfil de administrador y es el encargado de manejar la consola. La conexión al servidor

hosteado se realiza a través de los navegadores y requieren de autenticación con usuario y contraseña. Usuario

Perfil

Qlik1

Administrador de contenido

Qlik2

Usuario de consulta

Qlik3

Usuario de consulta

Qlik4

Usuario de consulta

Qlik5

Usuario de consulta

5. DISEÑO DEL SISTEMA DE EXTRACCIÓN, TRANSFORMACIÓN Y CARGA: a. EXTRACCIÓN: i. Extracción y carga de QVD inicial con una fecha de corte ii. Extracción y carga de Incrementales SIGMEP todos los días a las 03:00 am, todos los días se recarga la información del día y se genera un QVD de cada tabla por día (365 días por 32 tablas incrementales) iii. Los QVDs anteriores se unen al QVD de la extracción inicial, donde se otorga valores de 1 para los registros actuales y 0 para los registros históricos iv. Todos los desarrollos apuntan a los registros con valor 1 que significan registros actuales b. TRANSFORMACIÓN: La transformación se realiza a través de scripts que tienen que considerar lo siguiente: i. Considerar solamente los contratos que tengan “código-transaccion” = 38,39,40,58,119 de la tabla cl08-movimientos ii. Se deberá generar un campo que estará conformado de la extracción del campo “dato-anterior” según las siguientes consideraciones: Dato-anterior yes 2 20 1040052247 OTTOPAREDESV7 [email protected] OTTOPAREDESV7 [email protected] ENVIAR A SALUD

Pago-inteligente (Si=0 o No=1) yes

Tipo-cuentacredito 2

Código-bancocredito 20

Numero-cuentacredito 1040052247

iii. La herramienta deberá cruzar la información del punto a y b con la tabla cl04-contratos., para ello deberá considerar únicamente los campos llenos y el siguiente parámetro de cruce: “códigoproducto/región/contrato-numero” iv. Posteriormente debe comparar los campos: “pago-inteligente”, “tipocuenta-credito”, “código-banco-credito”, “numero-cuenta-credito” de ambas tablas (cl08 vs cl04). v. Finalmente generará un reporte agrupado por número de cambios de cuentas bancarias por usuario creador del movimiento y por contrato que tengan al menos un reclamo pagado. El reporte deberá ser de tipo drill-down y contener por lo menos lo siguiente:

Nombre campo Origen campo Usuario movimiento digitador Cantidad de cambios en pago-inteligente Cantidad de cambios en tipo-cuenta Cantidad de contratos con 2 o más cambios Cantidad de cambios en código-banco Cantidad de contratos con 2 o más cambios Cantidad de cambios en numero-cuenta-credito Cantidad de contratos con 2 o más cambios Número de contrato contrato-numero Código producto código-producto Región región Nombre del titular persona-nombre/persona-apellido Nombre del dueño de cuenta nombre-duenio-cuenta

Tipo campo cl08 - movimientos calculado calculado calculado calculado calculado calculado calculado cl08 - movimientos cl08 - movimientos cl08 - movimientos cl03 - personas cl04 - contratos

c. CARGA: La carga se realiza de las tablas indicadas en el punto Modelo dimensional y se realiza de la forma explicada en el punto Transformación con fechas y horas establecidas. 6. ESPECIFICACIÓN Y DESARROLLO DE APLICACIONES DE BI: Las aplicaciones deben contener reportes y gráficos en los dashboards o tableros. a. REPORTES: Los reportes son bastante dinámicos y se manejan a través de filtros que pueden ser parametrizables como, por ejemplo: por mes, por usuario, por región, por producto, etc.

b. GRÁFICOS: Al igual que los reportes los gráficos también varían en función de los filtros.