nteligencia de negocios aplicada a seguros

(1)

6t"\

55

cy ei'

(2)

UNIVERSIDAD TECNICA PARTICULAR DE LOJA La Universidad Técnica Particular de Loja

ESCUELA DE INFORMATICA

MODALIDAD ABIERTA Y A DISTANCIA

INTELIGENCIA DE NEGOCIOS APLICADA A

SEGUROS

TESIS DE GRADO PREVIA A LA OBTENCION DEL TITULO DE INGENIERO EN INFORMATICA

AUTOR:

RODRIGO VINICIO VIVA S AMAYA

DIRECTOR:

ING. GUIDO RIOFRIO

CENTRO UNIVERSITARIO QUITO

(3)

CERTIFICACION

Ing. Guido RiofrIo

DIRECTOR DE THIS

CERTI FICA:

Que el presente trabajo de investigación, previo a la obtención del tItulo de Ingeniero en lnformática, fue revisado durante todo el proceso de desarrollo desde su inicio hasta su culminación, por lo cual autorizo su prese

ofrIo

(4)

CESION DE DERECHOS

Yo Rodrigo Vinicio Vivas Amaya, declaro ser autor del presente trabajo y eximo expresamente a la Universidad Técnica Particular de Loja y a sus representantes legales de posibles reclamos o acciones legales.

Adicionalmente declaro conocer y aceptar Ia disposiciOn del Art. 67 del Estatuto Orgánico de Ia Universidad Técnica Particular de Loja que en su parte pertinente textualmente dice: "Forman parte del patrimonio de la Universidad la propiedad intelectual de investigaciones, trabajos cientIficos o técnicos y tesis de grado que se realicen a través a con el apoyo financiero, académico o institucional (operativo) de la Universidad.

(5)

AUTORIA

(6)

AGRADEC IMI ENTO

Agradezco a ml director de Tesis, Ing. Guido RiofrIo, quien con su aporte valioso permitió

que el presente documento sea realizado de forma ordenada y sobre todo aportando

conocimientos valiosos para futuros proyectos.

Agradezco también a mi querida universidad y todo su cuerpo docente, quienes a Jo largo

de mi carrera aportaron importantes conocimientos que han sido y son muy valiosos en la

(7)

DEDICATORIA

Todo el esfuerzo invertido a lo largo de mi carrera, que culmina con la realización del

presente documento quiero dedicarlo a mis padres quienes con sus valiosas enseflanzas y

su ejemplo de vida inspiraron en mi, grandes valores y sobre todo esfuerzo y entrega en

cada una de mis actividades.

(8)

INTELIGENCIA DE NEGOCIOS APLICADA A SEGUROS

Universidad Técnica Particular de Loja

4PI

Escuela de Ciencias de la Corn putación

Proyecto de Tesis

INTELIGENCIA DE NEGOCIOS APLICADA A

SEGUROS

[RODRIGO VIVAS]

[INTELIGENCIA DE NEGOCIOS APLICADA A SEGUROS]

[01-04-2010]

(9)

-1-INTELIGENCIA DE NEGOCIOS APLICADA A SEGUROS

CONTENIDO

...

2 INTRODUCCION

...4

PROBLEMATICA

...5

OBJETIVOS

Y

ALCANCE

...

6 CAPITULOI

... 7

1.1

DETERMINACION DE NECESIDADES...7

CAPITULOII

...

13 2.1 FUENTES DE INFORMACION ...13

CAPITULOIII

...22

3.1 DISENO DEL ALMACEN DE DATOS...22

3.2.-

Selecciôn del proceso . ... 22

3.3.- Selección de la Granularidad...23

3.4.- Identificación y conformaciôn de las dimensiones...23

3.5.- Selección de los hechos . ... 25

3.6.- Almacenamiento de los valores precalculados en la tabla de hechos . ... 29

3.7.- Terminaciôn de las tablas de Dimensiones.- ... 29

3.8.- SelecciOn de la duraciOn de la base de datos.- ... 30

3.9.- Control de dimensiones lentamente cambiantes.- ... 30

3.10.- Selecciôn de las prioridades de consulta y de los modos de consulta...30

CAPITULOIV

... 33

CARGADE DATOS

...

33

4.1 Carga de Datos a Base Temporal .- ... 34

4.2 Generación de Cubos...35

CAPITULOV

...

39 HERRAMIENTAS DE NAVEGACION...39

5.1 OlapX Aplication . ... 39

5.2 InfoReporter.- ... 40

5.3 Excel .- ... 41

CAPITULOVI

...43

PLANDEPRUEBAS ... 43

CONCLUSIONES

...47

(10)

REFERENCIAS... 50

ANEXO I... 51 MANUAL DE USUARIO DE HERRAMIENTA OLAP X...51 ANEXO 2 ...

MANUAL DE USO DE INFOREPORTER...54

ANEXO3 ... 63 MANUAL DE USO DE EXCEL 2007 CONECTADO A UN SERVIDOR ANALYSIS SERVICES

... 63

ANEXO 4 ... 69 PLANTILLAS DE VERIFICACIÔN DE DATOS ... 69

(11)

INTROD U CC ION

En la actualidad muchas organizaciones buscan soportarse en herramientas tecnolOgicas que les permitan ser más competitivos, las inversiones en tecnologIa en muchos casos ocupan gran porcentaje de sus presupuestos anuales asi como la inversiOn en capacitación al personal de las areas de tecnologia.

Todas las organizaciones tienen un sistema informático que soporta sus operaciones, el mismo almacena todas las transacciones de negocio en una a varias bases de datos.

Estas bases transaccionales contiene toda la informaciOn que genera las operaciones diarias de las organizaciones, muchas veces esta informaciôn simplemente se mantiene almacena sin ser aprovechada para generar una mejor competitividad.

A lo largo de mis años de experiencia en seguros he podido trabajar con algunos de los sistemas que son usados por las empresas de seguros y que soportan sus operaciones diarias.

Estos sistemas poseen mOdulos de reporterla muy simples que solo permiten obtener datos de [as operaciones diarias, pero no aportan en gran medida con conocimiento sabre estos datos.

He podido observar a demás que la gran cantidad de informaciOn que se almacena en

las bases de datos de las companIas de seguros puede ser muy (itil Si usamos

herramientas de Inteligencia de Negocios sobre estos datos.

La Inteligencia de Negocios a más conocida como BI aporta con metodologlas y herramientas que permiten obtener conocimiento de los datos.

A lo largo de mis estudios universitarios BI ha despertado mucho ml interés pues considero que para poder ser competitivos no solo es suficiente tener las mejores tecnologIas, los mejores procesos y personal altamente calificado si no también, considero que es muy importante que las organizaciones puedan contar con herramientas que permitan tomar decisiones importantes de negocio a decisiones estratégicas sobre algün tema organizacional especifico.

Par otro lado, el area de seguros ha venido creciendo en los ültimos años, las personas adquieren cada vez más seguros para sus autos, además de tener que adquirir un seguro par ley (SOAT).

Esto ha generado que el mercado de seguros crezca par Ia que la competencia también ha aumentado, obligando a las companIas de seguros a ser más eficientes en sus operaciones.

Inteligencia de Negocios ha sido aplicada en areas como Marketing y Ventas con gran éxito, es por ello que he decidido realizar el presente documento, con la finalidad de lograr conjugar ml experiencia en el area de sistemas de seguros con los conocimientos adquiridos sabre BI a to largo de mis estudios universitarios.

(12)

PROBLEMATICA

Las compañias Aseguradoras poseen varios problemas de informaciôn, entre ellos se encuentra que a pesar de poseer gran cantidad de datos no poseen las herramientas apropiadas para explotar conocimiento de estos datos.

Las empresas medianas y pequenas de seguros no pueden realizar fácilmente grandes inversiones en tecnologIa para poder adquirir sistemas de soporte de decisiones, por 10 que deben actualmente recargar este trabajo al area de tecnologia en la realizaciôn de reportes y al area técnica de seguros en la interpretaciôn de dichos datos.

Muchas veces estos reportes son grandes bases de datos que el personal técnico de seguros debe realizar trabajos de tratamiento de informaciOn normalmente en Excel sobre estas bases de datos.

Ademãs la generaciOn de estos reportes consumen tiempo del personal de sistemas, por lo que muchas veces la informaciOn se entrega fuera de tiempo y en ocasiones incluso se debe realizar un reproceso pues los usuarios desean agregar un campo a dicho reporte.

En resumen para realizar un reporte para el area técnica de seguros se requiere de mucho esfuerzo y genera pérdida de tiempo para las dos areas.

Par otro lado esta informacián es de gran importancia para la companIa, es muy comün que la empresa necesite obtener informaciOn de par ejemplo Ia marca de vehIculos más siniestrada, el tipo de vehIculo más vendido, la sucursal con mejor ventas, el agente con mayor valor de siniestros pagados, entre otras informaciones de real interés para la toma de decisiones estrategicas.

Por ejemplo se podrIa bajar el costa del seguro para aquellos vehIculos que presenten un bajo valor de siniestros y subir el costo para aquellos que presenten un alto nivel de siniestros.

Para Ilegar a esta información muchas veces como anoté en lineas anteriores deben trabajar las bases en Excel para poder visualizar estos datos, lo que obviamente genera retrasos, pérdida de tiempo y como consecuencia perdida de eficiencia

(13)

OBJETIVOS Y ALCANCE

Los objetivos principales de este documento son:

> Aplicar los conocimientos adquiridos en BI orientándolos al area de seguros

> Aplicar la metodologla de Kimball para realizar un Almacén de Datos que soporte las operaciones de emisiones de vehiculos y que permita obtener los reportes más solicitados de esta area

) Realizar un Almacén de Datos que permita soportar las necesidades del area de siniestros de vehIculos.

) Usar para el desarrollo del Almacén de Datos las herramientas incorporadas en la licencia de Microsoft SQL Server 2005 Estándar

Utilizar herramientas sin costo para realizar reporterla sobre los almacenes de datos diseñados

Realizar un proyecto de BI aprovechando los bajos costos de Microsoft SQL Server 2005 y la integración de Analisys Services de SQL Server 2005 con Excel o con otras herramientas gratuitas.

Este ültimo objetivo definitivamente es el más importante pues en la actualidad los proyectos de BI son muy costosos siendo esta una de ]as principales razones por las que las empresas medianas y pequeñas de seguros aun no han incursionado en proyectos de BI.

For ello mi propuesta es usar una metodologla muy sencilla, herramientas incorporadas en una suite completa de productos cuyo costo no sobrepasa los 10000$ y ademés usar como herramienta de navegación a nuestros almacenes de datos Excel que es tan difundida y usada en nuestro medio.

Este documento no abarca otras herramientas de la Suite de Microsoft como son Reporting Services e Integration Services asi como tampoco en el desarrollo de complicados programas de carga de datos para los almacenes de datos pues estos pueden variar dependiendo de cada compañIa.

(14)

CAPITULO I

1.1 DETERMINACION DE NECESIDADES

[1] BI (Inteligencia de Negocios) permite usar la tecnologIa para acceder a información

relevante y crItica para toma de decisiones por parte de los directivos, gerentes y otros

funcionarios encargados de analizar esta información, ayudándose de reportes

gráficos y estadIsticos que BI los puede entregar.

BI permite acceder de forma rápida, interactiva y sabre todo independiente del

departamento de sistemas a informaciOn critica, resumida, y sobre todo estratégica del

negocia, esta información permite identificar ]as oportunidades y problemas que el

negocia puede tener.

El poseer excesiva informaciôn no es una ventaja competitiva hoy en dIa. Los

sistemas transaccionales se encuentran inundados de informaciôn diana la misma que

muchas veces por Ia cantidad no permite obtener conocimiento a nivel gerencial.

El conocimiento es pader y sabre todo es una ventaja competitiva especialmente en

aquellos mercados en los que la campetencia es alta y la tecnificaciôn urgente. Es

aqul dande BI permite a las empresas incrementar la eficiencia organizacional y su

efectividad.

En las Ultimos años el mercado de seguros ha tenido un importante incremento, la

demanda en el mercado aseguradar ha aumentado y con ella la necesidad de las

campañias de poseer estadIsticas que les permita saber en cualquier momenta a las

directivos, datos estratégicas y estadisticos para mejarar las condiciones del seguro

coma primas, deducibles, servicios adicionales, entre otros, ayudãndolos a ser más competitivos. El canacimiento en este mercada es muy impartante pues debido a los

estrechas márgenes de tasas de seguros que maneja, se hace vital poder contar con

conacimiento muy exacta de sus clientes, coma por ejemplo marcas, modelos de

vehIculos más aseguradas, tipas de riesgas más vendidas, entre atras, lo que

permitirá a las aseguradoras tomar mejares decisianes sabre las tasas de seguras,

realizar estrategias de marketing arientadas a un sector en especIfico, etc.

En el mercado de seguros tenemas varias ramos sienda el de mayor importancia par

el nümero de clientes actuales y el nUmera de potenciales clientes en el rama de

vehiculos, par ello cansidero importante tomar solo este ramo para pader realizar un

(15)

Pág.-7-INTELIGENCIA DE NEGOCIOS APLICADA A SEGURUS

proyecto que no sea muy extenso, pero que permita cubrir las necesidades de

informaciôn de los directivos para la gran mayoria de companias de seguros.

En mi experiencia laboral he podido tener contacto con dos empresas de seguros y

con dos sistemas informáticos que se encuentran en más de 10 companias de

seguros.

Mi propuesta es realizar un Sistema de Inteligencia de Negocios que pueda ser

aplicado a todas las companIas de Seguros indistintamente del sistema lnformático

que posean, para ello y con la experiencia que he obtenido en 6 años de trabajo en

Seguros, crear u Data Mart que permita soportar las consultas más generales que

todas o casi todas IascompañIas de seguros necesitan.

Estas consultas están orientadas al departamento técnico de seguros. La gran

mayorIa de empresas de seguros poseen en sus organigramas un departamento

técnico, el cual es el encargado de controlar y verificar el rendimiento técnico de las

operaciones de la empresa.

Existen varios factores que influyen en el negocio de seguros y que son analizados por

el departamento técnico con elfin de obtener el rendimiento de las operaciones

A continuación citaré y detallaré los más importantes en el ramo de vehiculos.

> El valor Asegurado.- Es el valor máximo que tendrIa que pagar la aseguradora

en caso de siniestro

> Tasa de Seguro.- Es el porcentaje que se aplica al valor asegurado para

obtener la prima de seguro

> Prima de Seguro.- Es el valor que debe pagar el asegurado para obtener la

cobertura.

> Siniestro.- Es la ocurrencia de un evento que está amparado en la pôliza de

seguro y que obliga a la compañIa a cubrir el valor del mismo.

Valor de Siniestro.- Es el valor final que la companIa aseguradora pagó a la

fecha por un siniestro ocurrido.

(16)

Con esta informaciôn el personal técnico debe realizar algunos análisis como por

ejem plo:

> Marcas y Modelos de vehiculos que se aseguren con mayor frecuencia

Marcas y Modelos de Vehiculos que presenten mayor frecuencia de siniestros

Marcas y Modelos de vehiculos que presenten mayor volumen en primas

> Marcas y Modelos de vehiculos que presenten mayor volumen en pagos de

sin iestros.

Esta informaciOn es importante pues uno de los objetivos de todas las companias de

seguros es mantener un margen de rentabilidad técnico importante, para ello los

factores de ventas y de siniestros juegan un papel decisivo en este análisis pues Si

una compañia se ye afectada por un volumen importante de siniestros y no tiene un

gran volumen de ventas su rentabilidad por ende va a ser negativa.

El objetivo principal del análisis técnico de seguros es mantener un equilibria

adecuado entre el volumen de ventas y el volumen de siniestros, además deben

proporcionar a los directivos recomendaciones de tasas de seguros basados en este

análisis.

Muchas companIas de seguros ofrecen diferentes tasas para los diferentes tipos de

vehIculos no solo coma parte de una estrategia comercial si no también coma

resultado del anãlisis que mencionamos anteriormente.

Además este análisis permite a los directores de reaseguros mejorar las condiciones

propuestas por los reaseguradores para determinados tipos de vehIculos asi coma

decidir los porcentajes de cesiOn que se entregará a los mismos.

Los reaseguradores cumplen una funciOn de aseguradores de las aseguradoras, es

decir las compañias de seguros ceden parte de la prima recibida a los reaseguradores

en un porcentaje determinado, este porcentaje es el mismo que la reaseguradora

tomará en caso de un siniestro.

Es por ella que es importante tener informaciOn exacta pues en base a esta los

directivos cederán mayor a menor porcentaje de sus primas.

Si una aseguradora cede un porcentaje pequeño de su prima en un determinado tipo

de vehiculo y este presenta un elevado volumen de siniestros la aseguradora será

(17)

participe en mayor porcentaje de los siniestros que deba pagar lo que afectará a su

rentabilidad.

For todo lo anotado anteriormente considero importante diseñar un almacén de datos

de vehiculos que permita obtener las siguientes necesidades comunes de informaciOn:

ProducciOn:

1. VehIculos más asegurados por perlodo, marca, modelo, tipo de vehIculo,

sucursal, BrOker, asegurado

2. Valores promedio de tasa de seguro por perlodo, marca, modelo, tipo de

vehIculo, sucursal, BrOker, asegurado

3. Sumas Aseguradas promedio por perlodo, marca, modelo, tipo de vehIculo,

sucursal, BrOker, asegurado

4. Primas vendidas por perlodo, marca, modelo, tipo de vehiculo, sucursal,

BrOker, asegurado

Siniestros

1. VehIculos más siniestrados por periodo, marca, modelo, tipo de vehiculo,

causa de siniestro, sucursal, BrOker, asegurado

2. Valor promedio de costo de siniestro por periodo, marca, modelo, tipo de

vehiculo, causa de siniestro, cobertura, sucursal, BrOker, asegurado, taller.

3. Valores de recuperaciOn (salvamento) de vehiculos por robo total par periodo,

marca, modelo, tipo de vehIculo, sucursal, BrOker, asegurado.

Ademés que permita obtener cuadros comparativos por perlodo, marca, modelo,

tipo de vehiculo, sucursal, BrOker en el que se muestren valores de primas

vend idas y de siniestros pagados.

Esta informaciOn puede aportar de forma significativa en algunas areas coma:

(18)

Area Técnica

> Disminuir la siniestralidad de aquellos tipos de vehiculos que se vean afectados

por un gran nimero de siniestros.

Mejorar las tasas de seguros en aquellos tipos de vehiculos que no sean

afectados por una frecuencia significativa de siniestros.

> Identificar las coberturas que presentan mayor volumen de pago de siniestros y

que por 10 tanto son las de mayor riesgo para la organ ización

> Identificar las causas más comunes de siniestros.

Identificar a los asegurados con una alta frecuencia de siniestros.

Area de Reaseguros:

> Optimizar los contratos de reaseguros en los siguientes puntos:

o Mejorar los porcentajes de cesiôn en aquellos tipos de vehiculos que

presenten mayor nUmero de siniestros

o Disminuir los porcentajes de cesiOn en aquellos tipos de vehiculos que

no presenten altas tasas de siniestralidad

o Mejorar las condiciones con los reaseguradores al poseer un sistema

que permita obtener información estadistica oportuna y eficientemente,

generando confianza en el reasegurador.

Area Comercial:

> Podemos obtener informaciOn estadIstica sobre Ia producciOn y siniestros por

Agente de Seguros

0

Broker, con esta informaciOn podemos identificar que Agente de Seguros es rentable, cual es el que presenta mayor volumen de siniestralidad.

Verificar el cumplimiento de los presupuestos de ventas en las sucursales asI

como de los valores de siniestros pagados.

> Identificar las marcas de vehiculos con menor porcentaje en ventas para iniciar

campanas de comercializaciôn.

(19)

Esta información no solo se la va a poder obtener en forma de listados sino

también de forma grafica lo que permitirá un mejor anAlisis de los puntos que

hemos mencionado anteriormente.

Además de las ventajas que hemos señalado, la más importante es la optimización

del tiempo tanto del area de sistemas asi como del area encargada del análisis de

los datos, pues serãn los mismos analistas quienes podrán realizar sus propios

reportes de forma muy sencilla.

Con este proyecto la organizaciOn no solo obtendrá conocimiento sobre el giro de

negocio de forma oportuna y efectiva si 110 también podrán aprovechar de mejor forma los recursos personales de su organizaciOn.

(20)

-12-Comisión

A

C

a)

I-a)

C C

a)

I- I-.

Asegurado

Pertenece a

—1

N

Ramo

Coberturas

CAPITULO II

2.1 FUENTES DE INFORMACION

[1] Como indiqué al inicio, es mi deseo construir un sistema de BI que pueda ser aplicado a la mayorIa de las compañias de seguros. La principal fuente de

informaciôn será el sistema central de seguros de cada compañIa, de mi

experiencia todos los sistemas poseen estructuras similares que las detallaré en un

diagrama de negocio.

Tiene Tiene

Punto de Venta 4 Broker Poliza

Pertenece a

Tipo Movimiento

-1

Tiene

TipoVh Modelo 10 Marca

[image:20.573.89.557.246.730.2]

Proceso de Pólizas de Vehiculos Figura 2.1

(21)

-13-INTELIGENCIA DE NEGOCIUS APLICADA A SEGUROS

Ajustador/Taller

Causa

I Estado

I -CD

CD

Tiene I Tiene

EstimaciOn 10 Pago Factura

Tiene -u

CD

CD I

CD

o

V CD CD

Clase

Beneficiario

[image:21.574.92.559.99.569.2]

Proceso de Siniestros Vehiculos

Figura 2.2

En base a estos diagramas trabajaremos para el diseño del Almacén de Datos

El sistema principal que será nuestra fuente de informaciOn será el sistema que se

encuentra instalado en más de 10 compañias de seguros.

Es un sistema transaccional disenado por una empresa especializada en desarrollo

de software de seguros, diseñado en Visual Basic 6.0.

(22)

A pesar de ser programado en un lenguaje de programaciOn antiguo, presta los

servicios necesarios para soportar todas las operaciones de negocio de las

companIas de seguros.

Debido a acuerdos de confidencialidad de ml compañia con a empresa

proveedora del software dare una breve descripciôn del sistema orientándome a la

base de datos especialmente a la descripciOn de los campos y tablas que

usaremos en nuestro diseño y procurando la reserva respectiva.

Para efecto de nuestro diseño nos importan dos módulos de este sistema que los

detallaré a continuación:

MOdulo de EmisiOn.- Este modulo permite a los usuarios realizar el proceso

de emisión de pólizas los operadores ingresan los datos de la pOliza como

vigencia, asegurado, brOker, tipo de movimiento, además de los datos del

vehIculo como marca, modelo, tasa de seguro, descuentos si es necesario.

Al realizar el ingreso de esta informaciOn, el sistema automáticamente

genera un nCimero de pOliza as[ como nUmero de endoso en caso de ser

necesario. Además se guardan todas [as tablas que intervienen en este

proceso. De estas tablas obtendremos todos los hechos de nuestro

almacén de datos asI como las dimensiones.

Modulo de Siniestros.- A través de este modulo los operadores realizan el

ciclo de apertura y pago de siniestros. Para nuestro diseno este modulo es

importante pues a través de él se registra el siniestro, la cobedura afectada,

la causa que provocO el siniestro y el monto total pagado. Se generan los

registros en las tablas de la base de datos que usaremos para nuestro

almacén de datos.

Los môdulos secundarios que también intervienen en nuestro diseño son:

> MOdulo de Asegurados.- A través de este modulo los operadores ingresan

los datos de los asegurados coma Nombres, Apellidos, teléfonos, etc.

Modulo de Agentes.- El area comercial usa este mOdulo para cargar todos

los datos concernientes a los agente coma los nombres, direcciones,

contratos de agenciamiento, tipo, etc.

(23)

-15-INTELIGENCIA DE NEGOCIOS APLICADA A SEGUROS Agente --cod agente cod_ti po_ager id_perana FK_POLIZA_ FK_POLIZA_HREF_AGENJE Poliza_pagador id_poliza 1cod_moneda FK_POLIZA_P_REERENCE_MONEDA

_POLIZA_P i Moneda 1 1 Item_pagador cod_moneda id_poliza txt_de cod_item Imp Pdma FKTEMHEAREFITEMPAG Imp_Super

Poliza header Imp_seg_camp

id_poliza FK_POLIZA_H_REF_ITEM_HEA Item_ head r Imp_iva

cod_aseg id_poIiZ _{imp_desuento}

cod_agente cod—item

cod_ttipo_agente cod_grupo_endo

FK TEMHEA REF cod_tipo_endo -.

ecemidi_datos_au FKPoLIZAI-LRFASEGURAD (<PhycaI Dat

Asegurado hea] cod_aseg <U FK_POLIZA_H_REF_TIPO_ENDid—persona <U

FK_AS EG URA 0_RE FE RE NCE_PE RS ONA

Persona ModeloVH TipoEndoaD cod_grupo cod_tipo txt_nombre id_peraDna txt_apellidol txtepellido2 txt_nombre FK_MODELO_V cod_marca :cod_modelo txt_nombre cod_tipo_vh FTIPOVH FK_MODELO_V_REF_MARCA_VH Marca VI-I cod_marca txt_nombre FK_TIPO_END_EF_GRUPO_EN Grupo EndoaD cod_grupo txt_nombre <

I TipoVH cod _tipo txt_nombre

[image:23.573.99.523.52.536.2]

Diagrama Relacional de VehIculos Figura 2.3

(24)

A continuaciOn realizaré una breve descripciOn de ]as tablas que considero más importantes en este desarrollo, pasare por alguno algunas tablas que no creo necesario su explicaciOn.

Tabla: POliza Header

Descripción:

Es la tabla que contiene los datos básicos de la pOliza como son Nümero de POliza, fecha de Emisiôn, y las relaciones a otras tablas del negOcio coma son Tabla de Asegurados, agentes, tipos y grupos de endosos.

Campos Importantes:

> Id_poliza.- Campo que identifica unIvocamente a cada registro dentro de esta tabla.

> Cod_aseg.- Identifica unIvocamente al Asegurado. Hace referencia a la tabla asegurado_header

> Cod_agente.- Identifica univocamente al Agente, hace referencia a la tabla agente, la cual tiene cave compuesta par cod_agente y cod_tipo_agente.

> Cod_tipo_agente.- Identifica unIvocamente al Tipo de Agente, además es parte de la cave compuesta de la tabla agente

> Cod_grupo_endoso.- Identifica unIvocamente el Grupo de endoso al cual pertenece a pOliza

> Cod _tipo_endoso.- dentro de cada grupo de endoso, existe una sub division de los movimientos, este campo identifica a que tipo de movimiento dentro del grupo pertenece la pOliza

e Fecha_emisión.- Es la fecha en la que se registro la pOliza en el sistema

Tabla: POliza Pagador

DescripciOn

Contiene los valores de la pOliza, además de la moneda de pago.

Campos Importantes:

> Codigo_moneda.- Identifica el tipo de moneda en la que se realizó la emisiôn

(25)

Tabla: Item Pagador

Descripcián:

Contiene los valores de Prima, impuestos, descuentos y prima total de cada vehiculo dentro de la pOliza. Una páliza puede contener varios vehiculos, el valor correspondiente de cada uno de ellos se graban en esta tabla.

Campos Importantes:

Id_poliza.- Campo que identifica univocamente a cada registro dentro de esta tab la.

> Cod item.- Es el nümero consecutivo de autos que contiene dicha póliza. Forma parte de la cave compuesta de esta tabla.

Importe Prima.- Es el valor de prima que resulta de aplicar la tasa a la suma asegurada. Es el valor antes de impuestos

> Importe Seguro Campesino.- Es el impuesto al seguros campesino, es el 0.5% de la Prima

Importe SUper de Bancos.- Es el impuesto de la Superintendencia de Bancos, corresponde al 3.5% del valor de la prima.

> lmporte Derechos de EmisiOn.- Corresponde al valor que se cobra par concepto de la emisiOn de la pOliza.

> lmporte IVA.- Corresponde al valor de IVA grabado en este movimiento.

Importe Prima Total.- Corresponde a la sumatoria de todos los componentes Prima Neta, impuestos, descuentos.

Tabla: Item Header

Descripcián

Cada pOliza puede tener varios vehIculos, esta tabla contiene la cabecera de cada uno de los vehiculos que conforma la póliza, asi como la referencia a otras tablas que contienen los detalles de cada auto. Tiene una cave compuesta par Id_poliza y cod—item

Campos

Importantes.-Id_poliza.- Campo que identifica unIvocamente a cada registro dentro de esta tabla.

> Cod _item.- Es el nUmero consecutivo de autos que contiene dicha pOliza. Forma parte de la cave compuesta de esta tabla.

(26)

-18-INTELIGENCIA DE NEGOCIUS APLICADA A SEGUROS

Tabla: Item Datos Auto

DescripciOn:

Esta tabla contiene los detalles de cada vehIculo, asI como las referencias a las tablas de marcas y modelos.

Campos Importantes:

Cod item.- Es el nümero consecutivo de autos que contiene dicha póliza. Forma parte de la cave compuesta de esta tabla.

> Cod_marca.- Identifica unIvocamente a cada marca de vehIculo.

> Cod_modelo.- Dentro de cada marca existen varios modelos, este campo identifica unIvocamente dentro de cada marca al modelo correspond iente.

En el proceso de Siniestros las tablas más importantes tenemos:

Sinieropago Siniero -heade

id_strop_op <1 _idro _<U

id_ro <! id_poliza <U cod_cober < 'cod_causa <U nro_ro <U

FK_SINIEST R_REFERENCE_SINIESTR

FKSINI EST R_REFERENCE_ITEM_DAT

I

FKSINIESTRREFERENCEJTEM_DAT Item_datos_Aj id_poliza - cod item FK_SINI EST R_REFERENCE_COBERTUR

cod_marca cod_modelo

Cobertura cod_cobertura txt_nombre

Diagrama Relacional de Proceso Siniestros Figura 2.4

Siniestro—est id_ro cod_cober cod—item

(27)

labia: Siniestros Header

DescripciOn:

En esta tabla se almacenan los datos de la cabecera del Siniestro es decir el nümero de siniestro, el año, la relaciôn con la pOliza, la causa, la fecha de siniestro, fecha de ocurrencia, entre otros campos.

Campos Importantes:

> Id_stro.- Campo que identifica univocamente al siniestro dentro de la tabla.

> Id_poliza.- Campo que relaciona al siniestro con la pôliza a la cual se afecta dicho siniestro.

Cod _causa.- Campo que relaciona con la tabla de Causas de siniestros. Este campo identifica dentro de la tabla de causas unhvocamente a la causa de un sin iestro.

> Nro_stro.- Campo que almacena el n(imero de siniestro asignado.

Tabla: Siniestro EstimaciOn.

DescripciOn:

Cada vez que se genera un siniestro, antes de realizar el pago se debe reservar un valor de pago o estimaciOn del siniestro. En esta tabla se almacena la información de dicha estimaciôn, como son Ia fecha de la estimación, los valores estimados, entre otros. En nuestro caso lo que nos interesa es saber el valor que se pago por cada siniestro y su cobertura, para ello relacionamos a Ia tabla de pagos con esta tabla.

Campos Importantes:

Id_stro.- Campo que identifica unIvocamente al siniestro dentro de la tabla.

> Cod _item- Campo que identifica al vehIculo al cual se afecta el siniestro. Este campo se vincula al campo cod_item de la tabla item_datos_au para obtener los datos del vehiculo.

> Cod _cober.- Campo que identifica la cobertura a la que se afecta el pago. Un siniestro puede ser afectado a varias coberturas. Este campo se vincula con la tabla de Coberturas.

Tabla: Siniestros Pagos.

DescripciOn:

En esta tabla se almacenan todos los datos relacionados al pago de un siniestro como son el valor de pago, el tipo de pago, fecha, etc. En nuestro diseno nosotros tomamos el valor de pago total de esta tabla por cada cobertura de cada siniestro

(28)

Campos Importantes:

> Id_stro_pago.- Campo que identifica unIvocamente a cada registro dentro de esta tabla

Id_stro.- Campo que identifica univocamente al siniestro dentro de la tabla.

En el proceso de siniestro, si bien es cierto que intervienen otros factores y tablas, a los directivos Ia que les interesa es saber el monto real de pago, pues es el egreso neto que la compañIa ha realizado por cada siniestro.

Sin embargo en proyectos futuros se puede realizar otro almacén de datos que tome las estimaciones y otros datos especificos del proceso de siriiestros, nuestro afán es mostrar las ventajas de contar con un almacén de datos, por ello no abarcamos a todos los aspectos del proceso de siniestros, nos centramos tinicamente en los pagos que coma indiqué anteriormente es el rubro más importante y de mayor impacto en los resultados de la organización.

(29)

CAPITULO III

3.1 DISEFJO DEL ALMACEN DE DATOS

[2] Para el diseño del almacén de datos usaremos la metodologia de Kimball

(1996) la misma que se conforma de nueve pasos que son:

1. Selección del Proceso

2. Selección de la granularidad

3. IdentificaciOn y conformación de las dimensiones

4. Selección de los hechos

5. Almacenamiento de los valores precalculados en la tabla de hechos

6. TerminaciOn de las tablas de dimensiOn

7. SelecciOn de la duraciOn de la base de datos

8. Control de las dimensiones lentamente cambiantes

9. Selección de las prioridades de consulta y de los modos de consulta.

3.2.-

SELECCION DEL PROCESO.

El primer paso de esta metodologla consiste en identificar el proceso de negocio que

será el tema objetivo de nuestro almacén de datos.

Es importante en este paso identificar con claridad el proceso objetivo, conocerlo y

sobre todo entender la importancia de cada componente de este proceso.

Además es importante orientarse a un proceso especifico y correctamente definido

para evitar que nuestro almacén de datos sea extenso y de gran complejidad.

El objetivo final debe ser construir un mercado de datos para toda la organizaciOn,

pero para ello debemos crear correctamente los almacenes de datos necesarios de los

procesos req ueridos.

(30)

Del análisis previo de fuente de datos y de requerimientos de negocio encontramos

que la necesidad más importante está centrada en el proceso de ventas de pôlizas de

VehIculos y de siniestros del mismo ramo.

Iniciamos con el desarrollo del almacén de datos que soporte estos procesos,

realizaremos dos Almacenes de datos uno por cada proceso. (Ventas y Siniestros).

3.3.- SELECCION DE LA GRANULARIDAD.

Luego de haber seleccionado con claridad el proceso objetivo de nuestro diseño,

debemos seleccionar la granularidad de nuestro almacén de datos, es decir determinar

con exactitud que va a representar cada registro de la tabla de hechos de nuestro

almacén de datos.

Para nuestro diseño, la granularidad será:

Proceso de EmisiOn.- Para el proceso de emisiOn y considerando las

necesidades de informaciOn del negocio podemos observar que la entidad

POliza tiene los hechos relevantes para este proceso, por lo tanto la

granularidad serán las pólizas emitidas individuales por fecha.

Proceso de Siniestros.- Para el proceso de siniestros la entidad Siniestro posee

los hechos relevantes de este proceso, por Ia tanto la granularidad serén los

siniestros ocurridos par póliza por fecha.

3.4.- IDENTIFICACION Y CONFORMACION DE LAS DIMENSIONES.

[2] Las tablas de dimensiones son tablas de pequeno tamano que poseen una cave

principal simple y que contienen los datos que describen los hechos, es decir aquella

informaciôn que servirá para analizar los hechos, para recorrer nuestro almacén de

datos.

Para nuestro proyecto tenemos:

> Proceso de Emisiôn: las dimensiones necesarias en el proceso de emisiOn son:

c Sucursal

a PuntodeVenta

o BrOker

(31)

a Asegurado

a Tempo

o Tipo de Vehiculo

o Marca

a Modelo

a Tipo de Movimiento

> Proceso de Siniestros: Las dimensiones necesarias en el proceso de siniestros

son:

o Sucursal

o PuntodeVenta

o BrOker

• Asegurado

• Tiempo

• Tipo de Vehiculo

o Marca

o Modelo

o Cobertura

• Causa

• Pago

Podemos observar que tenemos dimensiones compartidas, Ilamadas dimensiones

conform adas y son:

> Sucursal

Punto de Venta

> BrOker

> Asegurado

(32)

Tiempo

> Tipo de VehIculo

' Marca

> Modelo

Las dimensiones de Tipo de VehIculo, Marca y Modelo las podemos colocar en una

sola llamada Modelo que contendrá la Marca y el tipo de vehIculo.

La dimension Item tan solo nos muestra el nimero de Item que tendria una pOliza.

Se agrega una dimensiOn de Tipo de ProducciOn que indica si la pOliza es solo emitida

o ya se encuentra contabilizada. Esto lo utilizan muchas companIas de Seguros pues

por motivos de regulación aquellas pOlizas que tengan un pago realizado, asi sea de

un centavo, deben ingresar a producción contabilizada.

Se agrega además una dimensiOn Ilamada Moneda, mi intención es realizar un

sistema que permita ser aplicado a varias companias de seguros, algunas de ellas por

cuestiones de negocios realizan emisiones en otras monedas como Euros, con esta

dimensiOn podrIamos realizar reportes incluso por moneda.

3.5.-

SELECCION DE LOS HECHOS.

[2] Las tablas de hechos son tablas con cave principal compuesta que almacena solo

valores numéricos y aditivos que son importantes para el proceso objetivo.

En otras palabras, son los valores que son importantes para el negocio y que se

requieren analizar a través de las dimensiones.

Cada componente de la cave de esta tabla corresponde con una cave primaria de

una tabla de dimensiones.

(33)

Los hechos importantes para nuestro desarrollo serán:

> Proceso de Emisión:

o Valor de Prima Neta

o Valor de Impuestos de Superintendencia de Bancos

o Valor de Impuesto Seguro Campesino

a Valor de Financiamiento

a Valor de IVA

a Valor de Prima Total

a Valor de Suma Asegurada de VehIculo.

Proceso de Siniestros

a Valor de EstimaciOn

a Valor de Pago

a Valor de Reaseguro

a Valor Recupero

a Valor Deducible

a Valor deimpuesto

Los diagramas de los esquemas en estrella de los almacenes de datos se muestran

en la Figura 3.1 de EmisiOn y 3.2 de Siniestros

(34)

-26-INTELIGENCIA DE NEGOCIOS APLICADA A SEGUROS

- RAMO - _[

PUNTOVENTA RamolD

PuntoVenaDnumec(4 T MODELOVH NombreTipoRamo varchar(80)

INombrePuntoVenta varcha8O) _Mod&olDint NombreRarno varchar(80)

NombreSucurI varcha20) _NombreMarca _varcha8O) NombreModelo varcha8O) FK EMISIONV REFERENCE PUNTOVEN FKEMISIONVREFgRENC NombrelipoVeh varcha40) -FK_EMISIONV_REFERENCE_RAMO

EM IS ION VE HICULOS

'BrokeD numec(6) MONEDA

PuntoVentalD numeric(4)

<>

_MonedalD _mt _pj

FK EMISIONV REFERENCE RamolD_- uit <pkfkl> ,REFE NombreMoneda varcha20)

A'

AuradoID numeric(6) <pkfk4>

-MonedalD it <pkfk3> BROKER

PeodoID datetime <pKfk5>

BrokeD numec(4) TipoMovlD int <IDkf> _. ...

TipoBroker varchar(20) TipoProdl int <fk9> Jp ASEGURADO 'NombreBroker varchar(100) ModelolD

AQuradoIDnurneric(6)..

ValorPrimaNeta nume6c(10,2)

FK_EMISIONV_RE/FRENCE_P ValorSuperBan numec(6,2) . NomLrovarc1OO) ValorDerEmi numec(6,2)

ValorSegCampe numeñc(62)

PERIO

i

ValorFinanciamiento nume6,2)

^

-P TiPr)PPnn

VaIova numenc(1O,2)

TIPOPRODUCCION PenodolD datetime ValorPnmaTotal numenc(10,2) I

Anio varcha4) - . . - ... ... --- TipoProdiD nt pj

I Nom bre Prod uccion cha18)

Mes varchar(30)

bK EMISIONV REFERENCE TIPOMOVI -. ... ....-. ... ....

Semere varchar(15) - -

-r TIPOMOVIMIENTO

Ti poMoviD int NombreGrupoMov varcha60) NombreMovimiento varcha60)

[image:34.573.81.541.50.554.2]

Diagrama de Almacén de Datos Emisión

Figura 3.1

(35)

-27-RAMO

COBERTURA CoberturalD int NombreCobertura varchar(40) L... MODELOVH ModelolD int NombreMarca varchar(80) NombreModelo varcha(80) NombreTipoVeh varchar(40) A

FKSINI EST RO_Mode!_286302EC

MONEDA MonedalD int NombreMoneda varchar(20) • raMIPIp\_{tCober1367E6O6} BROKER BrokerlD er numeflcM) _MESTROS

NombreBroker varchar(100) REFERENd BrDkerlD numedc(6) QJ

PuntoVentalD numehc(4) <oFcfkl2> I RamolD mt <pkjk7,fk8>

AguradoID numec(6) <plcfkll> PERIODO

_TR REFERENCE MonedalD nt <Içfk5,fk6> PertodolD datetime PenodolD datetime <pkjkl3> Anio varchar(4) TipoMovlD nt <plçfk9> I

Mes varchar(30) ModelolD mt <pk.fk4> Semere varchar(15) j

CauID mt <pKfkl,fk2>

CoberturalD int <plcfk3> L ASEGURADO

BRO_BrokerlD numec(4) <fklO>

AguradoID numeric(6) ValorEstimacion numerlc(10,2) .

NombreA

ValorPago numenc(1 0,2) gurado varchar(1 0(

-VaIorReaguro numeric(10,2) CAUSASINIESTRO ValorRecupero numenc(1 0,2) VaIorRa numerlc(10,2) CauID int pj Valorimpuestos numehc(10,2) NombreCausa varchar(60)

FK_S I NI ESTRO /TipRv EFRENCE PUNT OVEN

TIPOMOVIMIENTO _PUNTOVENTA ToMovID _PuntoVentalD _numertc(4_<D NombreGrupoMov varchar(60) _{NombrePuntoVenta varchar(80)} NombreMovimntovarchar60) _{NombreSucursal} _varchar(20)

[image:35.573.42.548.73.604.2]

Diagrama de Almacén de Datos de Siniestros Figura 3.2

Pág.

(36)

INTELIGENCIA DE NEGOCIOS APLICADA A SEG!JROS

3.6.-

ALMACENAMIENTO DE LOS VALORES PRECALCULADOS EN LA

TABLA DE HECHOS.

En este paso seleccionamos aquellos valores que podamos calcularlos

Es importante realizar este paso para determinar la posibilidad de usar valores

importantes precalculados que no los tenemos en los sistemas transaccionales de

origen.

En nuestro almacén de datos de Emisión el data que podemos pre calcular es el valor

de la siniestralidad de la pOliza

El valor de la Siniestralidad indica a una empresa de seguro el porcentaje de Pago de

siniestros sobre el valor de prima neta.

Para el caso del Almacén de Datos de Siniestros el valor calculado será el valor de

pago, pues un siniestro puede tener una estimaciOn y una estimaciOn puede tener

varias siniestros.

Esto se da porque par ejemplo en un choque de auto se puede tener dos clases de

estimaciOn par Responsabilidad Civil y par Daños Propios (Daños causados al

veh iculo).

Al pagar los valores estimados par Daños Propios se puede enviar al Vehiculo a dos

talleres par 10 que tendrá dos facturas par Jo tarito tendrá dos pagos. Par ello, para

nuestro Almacén de Datos sumaremos los valores pagados par cada estimaciOn y este

valor representará al hecho precalculado de valor de pago. La mismo pasará can el

valor de Impuestos.

3.7.-

TERMINACION DE LAS TABLAS DE

DIMENSIONES.-En este paso agregamos a las tablas de dimensiOn las descripciones textuales

necesarias. Par ejemplo agregamos el nombre de la sucursal, el nombre del Punta de

Venta, el nombre de los asegurados, direcciones, teléfonos, nombre del rama.

Podemos observar las tablas de dimensiones y las tablas de hechos en las figuras 3.3

y 3.4.

(37)

3.8.-

SELECCION DE LA DURACION DE LA BASE DE

DATOS.-En este caso la informaciOn se tomará hasta 5 años atrás, básicamente se realiza esto

para que se pueda realizar análisis de tendencias y de variaciones que en el ámbito de

seguros son muy importantes.

3.9.-

CONTROL DE DIMENSIONES LENTAMENTE

CAMBIANTES.-Es importante realizar el control de [as dimensiones lentamente cambiantes pues

pueden presentar un serio problema de interpretación de datos si no usamos las

descripciones correctas de estas dimensiones.

Por ejemplo, a lo largo del tiempo [as descripciones de los Asegurados o puntos de

yenta pueden cambiar debido a condiciones propias del negocio, esto puede probar

errores al interpretar los datos de nuestro almacén si no las tenemos bajo control

Si ese fuera el caso, debemos guiarnos por el côdigo de Agente o Punto de Venta y

se mantendrá el nombre actual.

3.10.-

SELECCION DE LAS PRIORIDADES DE CONSULTA Y DE LOS

MODOS DE CONSULTA.

[2].

En este paso tomaremos decisiones sobre el diseño fIsico,

Los problemas más comunes que pueden afectar al rendimiento de nuestro proyecto

son la orientaciOn fisica de la tabla de hechos en el disco duro asi como la utilización

de resümenes o agrupaciones precalculados.

Los dos almacenes de datos se los diseñará en SQL Server 2005, por 10 tanto las

tablas de hecho y dimensiones, asi como Indices se los realizaré orientados a este

servidor.

Además en nuestro diseño no usamos agrupaciones precalculados.

Las agrupaciones que usamos se encuentran en los programas de carga de datos que

serán ejecutados en horarios seleccionados de tal forma que el usuario no se yea

impactado por este proceso.

(38)

Las versiones finales de los diagramas en estrella de los almacenes de datos son:

^

-RAMO _PUNTOVENTA

- -

-amolD int

PuntoVentD numenc(4) - MODELOVH NombrelipoRamo varchar(80) _{NonibrePuntoVenta varchar(80)}

ModelolD int NombreRamo varchar(80) _NombreSucurl _varchar(20)

NombreMarca varchar(80)

/

NombreModelo varchar(80) FKEM ISIONV REFERENCE _PUNTOVENFKEMISIONVREFERENG Nombreli poVeh varchar(40) FK_EMISIONV_REFERENCE_RAMO - ... - ...

EMISIONVEHICULOS

.. .

numeric(6) MONEDA PuntoVentalD numec(4) <pIçflS> _MonedalD _int FKEMISIONVREFEENCEJ '—REFEAT NombreMonedavarchar(20)

AnuradoID numedc(6)

..- _MonedalD _int _<pKfIG> BROKER _i

PeriodolD datetime <pkfk5> BrokrIDnumenc(4) TipoMovi int

TipoBroIr varchar(20) _/TioProdID int <Pk> EFE ASEGURADO 'NombreBroIr varchar(100) ModelolD iP! _AguradoID _numeric(6)

. VaIorPmaNeta numeric(1O,2)

NombreAgurado varchar(100) FK_EMISIONV_REFEENCE_PIVaIorSuperBan numeric(62)

ValorDerEmi numeric(62) ValorSegCampe numeric(6,2) ValorFinanciamiento numenc(62)

EFEP TIPñPOñ . ....

PERI000 _Valorlva _numeric(1O2) RPC

TIPOPRODUCCION PenodolDdatetinie i ValorPnmaTotal numeric(102)

TipoD int Anio varchar(4)

Mes varchar(30)

Semere varchar(15)

[image:38.573.62.507.88.579.2]

Esquema en Estrella de Almacén de Datos de Vehiculos Figura 3.3

NombreProduccion char(18) K_EM ISION V_RE FE RE NCE_T IPOMO VI

TIPOMOVIMIENTO TipoMovl int NombreGrupoMov varchar(60) NombreMovimiento varchar(60)

(39)

-31-RAMO I RamolD uit

NombreTipoRamo varchar(80) NornbreRamo varchar(80)

^Mde I . I int <Dl^,

MODE LOVH NombreMarca varchar(80) NombreModelo varchar80) NombreTipoVeh varchar(40) A MONEDA Monedalint

FK_SINIESTRO_ Model _286302EC _{NombreMoneda varchar(20)} COBERTURA

CoberturalD ifl! NombreCobertura varchar(40)

Irk. cIMIpcTpr Cober 1367E606

BROKER

-BrokeriD numedcM) Q SINIESTROS

TipoBroker varchar(20) 'i.-... NombreBroker varchar(100) 'R!REFERENC. BrokeriD numedc(6)

....- PuntoVentalD numedc(4) RamolD int AseuradoID numehc(6) PERI000 MonedalD

TR REFERENCE

PenodolD datetirne - PenodolD datetime Anlo varchar(4) TipoMoviD nit Mes varchar(30) ModelolD int Semere varchar(15) CausalD kit Cabe rturalD kit BROBrokeriD numedc(4) ValorEstimacion numedc(102) ValorPago numenc(102) - . ValorReaseguro numedc(102) CAUSASJNIESTRO 1 ValorRecupero numenc(102)

ValorRasa nurne6c(10,2)

CausaID If! Valorlmpuestos numeiic(10,2)

NonibreCausa varchar(60) ..

-p .- _{T______...}

TIPOMOVIMIENTO _PUNTOVENTA TioMovID _PuntoVenlalD _numenc(4) NombreGrupoMov varchar(60) _{NombrePuntoVenta varchar(80)} NombreMovirniento varchar60) _{NombreSucursal} _varchar(20)

[image:39.574.74.506.139.580.2]

Esquema en Estrella de Almacén de Datos de Siniestros

Figura 3.4

Usaremos una Base de Datos intermedia para la limpieza y depuraciOn de ciertas

tablas de dimensiones como BrOker, Tipos de Movimiento, Asegurados

INIESTRO oned2BQ38OE <pk,fkl2> <pKfk7fkS> <Qkfkll> <sKfk5fk6> <pkfkl 3> <kfk1 !k2>

3TR_ REFERENCE _A. EtURADO Ase g uradolD numedc(6) NombreAsegurado varchar(1 0(1

(40)

-32-le Datos a lemporal

CAPITULO IV

CARGA DE DATOS

La carga de Datos se realizará en dos pasos, primero se cargaran datos a la tabla

temporal y de allI se cargarán los datos en la tabla definitiva.

Como habiamos indicado en el capitulo de fuentes de información, tomaremos los

datos del sistema transaccional, usando sentencias SQL cargaremos los datos en la

base temporal Posteriormente cargamos de esta base temporal a la base de datos de

nuestro almacén de datos.

Se real izarán dos procesos de carga de Datos, tal como se muestra en la figura 4.1

Carga de Datos a Almacén de Datos

[image:40.574.71.420.331.613.2]

Diagrama de Proceso de Carga de Datos

Figura 4.1

Las bases de datos dbDataTemp y dbDataSeguros se encontrarán en el mismo

servidor SQL 2003, además se deben vincular los servidores de Base OLTP

(transaccional) y el servidor destinado para almacenar los Data Mart.

(41)

4.1 CARGA DE DATOS A BASE

TEMPORAL.-En este proceso se realizará la carga de datos de las tablas de Dimensiones que

deban ser tratadas, es decir aquellas dimensiones a las que se les asignara nuevas

claves principales, además se aplicarán sentencias de control para evitar valores

nulos.

Para la carga de las tablas de esta base usaran sentencias SQL, las mismas que se

detallan en los anexos. Dichas sentencias se podrán colocar en procedimientos

almacenados y calendarizarlo para que sean ejecutadas

Las consideraciones que se tomarán para determinadas tablas son:

Carga de Datos de Broker.- Para esta tabla se unirán los campos de Nombre y

apellidos en uno solo, además se aplicarán sentencias isnull para evitar la presencia

de datos nulos. Se agrega además el Nombre del Tipo de BrOker y se genera una

nueva cave primaria para cada BrOker.

Carga de Datos de Asegurados.- En esta tabla se van a unir al igual que en el caso

de los BrOker, los Nombres y Apellidos del Asegurado, aplicando sentencias isnull para

evitar valores nulos, además se genera una nueva cave primaria para cada

asegurado.

Carga de Datos de Modelo de VehIculo.- Se agregará en esta tabla el Nombre de

la Marca, el Nombre del Modelo y el Tipo de Vehiculo para realizar Jerarquias de

Tipo-Marca-Modelo. Se genera también una nueva cave primaria para cada registro.

Carga de Datos de Tipo de Movimiento.- La tabia tendrá el nombre del Grupo y el

Nombre del Tipo de Movimiento, además se genera una nueva cave primaria para

cada registro.

Carga de Datos en labia Punto de Venta.- En esta tabla agregaremos el nombre de

la sucursal as[ como el nombre del Punto de yenta, generamos una nueva cave

primaria.

Las dimensiones causa, moneda, cobertura y ramo se insertarãn directamente a la

tabla final.

En Ia tabla temporal también agregamos los hechos de VehIculos y de Siniestros.

(42)

Carga de Datos en Almacén de

Mos.-En este proceso cargamos directamente desde la base transaccional, las dimensiones

Causa, Moneda, Tipo de Producción, Cobertura y Ramo con sentencias SQL

Las restantes dimensiones y las tablas de hecho serán cargadas desde la Tabla

temporal.

Los Scripts de Carga de datos se encuentran en el medio adjunto.

En dicho medio se encuentran los scripts de carga de datos de la base temporal, la

base definitiva y la creaciOn de las dos bases de datos.

4.2 GENERACION DE CUBOS.

[3] Los cubos OLAP serán Generados con SQL Bussines Intelligence Development

Studio

Se generan además como campo calculado del Cubo el campo Siniestralidad.

Generaremos tres cubos que son:

CuboMovEmisión.- Este cubo servirá para el análisis de los datos del proceso de emisiOn.

La vista de datos de este cubo se muestra en la figura 4.2

(43)

rchiv3 Edihr Vr rr3yeto Generr Dpurr lorriato Ba5e de Jatos Vi5ta de origen de datw Herramientas \rentana Coniunidad 4yca J Pontata compteta

.:Dbd Dili Seqros.dsv [Diseo]t CuboMovEmiaiorcu IDieñoJ CuboSinietoscube IoienoH Cohn Emian kietomube [DioeoI v X

jJ * X

Orgini de

<loden as

3 BOtER (d, ₃_PERIOD BrokeriD _C; _PIOD Tiperoker _Ai r4cmbrthroker

ILJ

Tables ASEGLRAD)(dl BROKER (dLoF 1sIc\'&fla MOtrELOH(dbc MONEDA (dt.T PERIOD) fcbnF F1JT'YENTA f,d RAMO o.R' TiPOVIM1EN1 flPOQRODUCCI' RarrolD omeeRro

3 iipoRcc*Jc. DpPrlD NorreeProducon

3 -oicic.u.

flpc1n'eID ramçooe

3 EfrUSIO1iI,, Brde<D PuninWeID RanoID AoegJdoID BordaiD PerlodolD ipoMoviD T1POrOID MoIo1D VakiPae Yak rSeen VakiDe iOn VkreçCorn Yak Fnand niniIo Vakrtaa VakrPi*naleial

3 :(J4,

DurVefltatD

VrkreSeo(und

3ASEGRADO (. 4oegura*ID Irkrekepridc

TEGuRD: kobaASEC'LFTO)

FONDA (d

n'Bonada

3 'BL3YI,

Nnbre\lua Nxobreib1eY ftenbreli,Veh

Diagrama de Cubo de Emisión (Tornado de Analisys Services) Figura 4.2

(44)

-36-guraooiL No rnbrecsegurado

' PeriodolD

-Anio SINIESTRO... BrokerlD Punto Vent. RamoID — r AeguradoID

MonedalD PeriodolD TipoMovlD ModeklD CuaID CobeturaID

-KeriL) TipoBroker NmbreBnDker

cuuRurd.LL.' NombreCobertur

PuncVenID N ornbrePuricVerit NombreSucural

NombreI\1.rt NornbreModelo NombreTipcVeh

CeusalD NombreC.aua

Cubo Siniestros.- Este cubo permitirá el análisis de datos del proceso de Siniestros.

IP!'JAI TipoMocID NombreGrupor.'lcv

NombreMovimie. NombreMoneth TIPOPRODUCCI..,

TipoProdlD RmoID

NombreProduccion tiombreTipoRamo NombreRamo

[image:44.573.80.539.140.620.2]

Diagrama de Cubo de Siniestros (Tornado de Analisys Services)

Figura 4.3

(45)

Cubo Emisión Siniestros.- Este cubo permitirá el análisis de los datos de los dos cubos anteriores, agregando solo datos especIficos de ambos cubos.

Vstdecndetic

-i)DMD"ID 14

- EYEION&I.

Ranl

1jcAI, MoD - ____

Va1ri : rcsio.

RaID

Rand

NmCu

CIUI

[image:45.573.80.452.102.554.2]

Diagrama de Cubo Emisiôn-Siniestros (Tornado de Analisys Services)

Figura 4.4

(46)

-38-INTELIGENCIA DE NEGUCIOS APLICADA A SEGUROS

CAPITULO V

HERRAMIENTAS DE NAVEGACION

Una vez elaborado el cuba y levantado el servicio en Analysis Services necesitamos

contar con una herramienta que permita al usuario final navegar a través de este cubo

de forma interactiva, usando sus dimensiones, colocando filtros y de ser posible

obtener esta informaciOn en forma de tabla dinámica y de forma gráfica.

En el desarrollo del presente documento revise algunas herramientas, la gran mayorIa

son pagadas y ofrecen versiones demos para poder probar sus capacidades.

Dentro de ellas he escogido algunas herramientas que pasaré a explicar ligeramente

sus capacidades y uso en este proyecto. Cualquiera de ellas puede ser usada en un

cuba realizado con Analysis Services.

5.1 OLAPX APLICATION.

[4] Es una aplicaciOn cliente-servidor que permite crear, ver y analizar cubos

multidimensionales.

Permite el anãlisis interactivo, realizar reportes de varios formatos en base de cubos

de Analisis Services o de archivos locales.

Esta herramienta posee dos modos de apertura, Cube y Layout. En el modo Cube

permite abrir un reporte ya establecido, mientras que en el modo Layout permite

diseñar el reporte es decir arrastrar dimensiones, seleccionar hechos, etc.

Personalmente considero que es una buena herramienta pero tiene algunos

problemas que son:

> Al realizar cualquier cambio el proceso es muy lento

> Se debe primero diseñar el reporte para poder abrirlo

Una vez diseñado el reporte si se desea aumentar una dimension se debe volver al

men( de diseño.

> Algunas ocasianes el programa tiende a colgarse.

(47)

Para más informaciôn podemos visitar la página web

http://www.olapxsoftware.com/es/default.asp

En el Anexo 1 se detalla un pequeno manual de uso de esta herramienta

5.2

INFOREPORTER.-[5] Personalmente de los programas que revise este es el que tiene todo lo necesario

como herramienta complementaria de navegación de cubos.

Esta herramienta es diseñada por INFOVISION, permite conectarse a cubos

existentes especialmente de Analysis Service, a bases de datos relacionales de SQL o

incluso a archivos de Excel sin formato.

Además permite crear cubos locales con Microsoft Query. Es una herramienta

diseñada para trabajar conjuntamente con Excel, pues permite importar los datos

directamente al mismo.

Los gráficos obtenidos en esta herramienta son muy similares a los que se logran en

Excel, además de poder visualizar los datos paralelamente en forma gráfica como en

forma tabular.

Dentro de las ventajas de esta herramienta resalto las siguientes:

> Es una herramienta sencilla de configurar, además permite grabar conexiones lo

que facilita al usuario final su uso, pues el usuario técnico puede configurar la

conexiOn al cubo, guardarla y asI el usuario final simplemente a través de esa

conexiOn puede realizar varios reportes del cubo mencionado.

> Su sencilla interface permite a un usuario inexperto cambiar de tipo de gráfico,

realizar tareas de Dril Down, e incluso importar los datos a Excel para realizar

reportes impresos.

> Permite mantener a los reportes ordenados dentro de carpetas

> Permite de forma sencilla generar campos calculados.

Por las razones anotadas anteriormente es el software que recomiendo para proyectos

de Inteligencia de Negocios en pequeñas e incluso en medianas empresas, además

de ser una herramienta gratuita.

En el Anexo 2 se encuentra un pequeño manual de uso de esta sencilla herramienta.

(48)

5.3

EXCEL.-[6] Una herramienta que no podemos dejar de mencionar y recomendar es usar Excel

para conectarse a un cubo multidimensional, navegarlo y generar los reportes que se

necesiten.

A partir de Ia version Excel 2007, tiene integraciOn nativa con Analysis Services, la

cual permite conectarse a un servidor de Analysys Services y poder navegar en los

cubos disponibles en el mencionado servidor.

Personalmente considero que esta es otra opción importante para pequeñas

empresas que no tiene presupuestos enormes para soluciones complejas y que

pueden beneficiarse de una ventaja de Excel 2007 y de SQL Server 2005 que trae

Analysis Services.

Para aquellos usuarios que no deseen contar con un interface de navegaciOn entre la

fuente de los datos o cubo y Excel, podemos configurar directamente una conexión

entre Excel y un servidor de Analysis Services.

De esta forma el usuario puede realizar desde Excel reportes sobre los datos

almacenados en cubo de datos usando por un lado las ventajas conocidas de Excel y

el poder de navegación multidimensional de un cubo de datos.,

Muchos usuarios a lo largo de los años han convertido de Excel su companero de

trabajo y en muchas ocasiones la herramienta de mayor uso y por to tanto la que mas

conoces y conflan, por ello considero que aprovechar las ventajas de esta herramienta

para análisis multidimensional es una opción muy interesante para aquellos usuarios

que no deseen experimentar nuevas herramientas o que no deseen salir de las

seguridad de usar Excel.

En el Anexo 3 se presenta un pequeño manual de cOma configurar y aprovechar las

ventajas de Excel 2007 y la conexiOn directa a un servidor Analysis Services.

Adjunto un pequeño cuadro comparativo de estas tres herramientas mencionadas

(49)

Cuaclro Comparativo de Herramientas de ReporterIa

Caracteristica OLAPX INFOREPORTER EXCEL Si posee licencia de Pagado, version libre VersiOn freeware Office 2007 no Licenciamiento con varias limitaciones funcional tiene costo Conexión con Reporting

Services Si SI Si

Dificultad de Configuración Media Baja Baja Dificultad de Navegación

de Cubo Alta Baja Baja

Si-Trabajo en Opción de ExportaciOn a forma de tabla

Excel Si Si dinámica

Complejidad de creaciOn

de reportes gráficos Alta Sencilla Sencilla Nula-Si poseen InversiOn Mediana Nula Office 2007

(50)

CAPITULO VI

PLAN DE PRUEBAS

Las pruebas se realizarán siguiendo los siguientes pasos:

Verificación de Valores Totales de Emisión

VehIculos.-El primer paso a realizar será verificar que los valores totales que se muestran en los

reportes se encuentren acorde a los balances anuales o a los reportes del Sistema

transaccional correspond ientes a Listado de ProducciOn de VehIculos en los años

2006,2007 y 2008. Para ello el usuario deberá tomar el valor Total general del repode

del Cubo EmisiOn VehIculos de cada año de revisiOn y compararlo con el respectivo

Listado de ProducciOn del mismo perlodo.

Luego procederemos a cuadrar cada mes, si se encuentra alguna diferencia se

verificará en los programas de carga de datos los posibles descuadres.

Luego de verificar cada mes, verificaremos los totales por tipo de movimiento con

valores anuales, para eDo tamblén usaremos los listados de ProducciOn del perlodo

correspond iente.

Los valores que verificaremos serán Prima neta, derechos de emisiOn,

superintendencia, valor de financiaciOn, valores de IVA, seguro campesino y prima

total. Estos valores se verificarán tanto para producciOn emitida como para producciOn

pagada.

Estas pruebas deben arrojar valores similares a los presentados en el balance de la

companIa, de haber diferencias considerables se debe proceder a verificar los meses

en los que se encuentre el descuadre.

Verificación de Valores de

Siniestros.-Igualmente procederemos a verificar los valores del cubo de siniestros con el Listado

de Registro de Siniestros del perlodo. En este caso verificaremos que los valores

cuadren tanto a nivel anual como mensual. Para el caso de causa verificaremos con el

area de sistemas un reporte de Siniestros por causa.

Si existen grandes diferencias contra el listado de Registro de Siniestros del PerIodo o

contra el balance se debe reportar en la plantilla de inconsistencias.

(51)

ValidaciOn de Dimensiones.- Las dimensiones deben ser también validadas, se debe

verificar que los Nombres de los Agentes, Puntos de yenta, causas, modelos, tipos de

movimiento y tipos de auto se encuentren correctos.

Para ello el usuario puede solicitar al departamento de sistemas un listado de

Nombres de Agentes, Puntos de Venta, causas de siniestros, modelos de vehiculos,

tipos de movimiento y tipos de auto para que sean verificados.

Una vez validadas que las dimensiones sean correctas, debemos ahora validar que las

combinaciones de dimensiones y valores sean correctos. Para ello podemos obtener

un listado de Emisiones por Agente del Sistema Transaccional y compararlo con los

resultados arrojados por el cubo respectivo.

Podemos verificar listados por agente, punto de yenta, Marcas y modelos, Tipos de

vehiculos, etc., a fin de verificar que los valores sean correctos, acordes con los

listados del sistema transaccional respectivos.

Las plantillas a usar para realizar estas validaciones se encuentran en el Anexo IV, las

mismas que deben ser Ilenadas por el usuario que realizará las validaciones y deberán

contener una firma de verificación.

La dimensiOn Tipo de ProducciOn no se valida pues solo existen dos que se insertan

directamente, sin embargo se debe comprobar con el balance los tipos de producciOn

usando la siguiente planilla:

Corn

Perlodo de Análisis: Usuario

Resoonsable:

Valores a verificar: Prima Neta

Super Bancos Derechos de EmisiOn

Seauro Camoesino Financiamiento IVA

Prima Total

probación de Tipo de Producción vs Balance por Año

ProducciOn Emitida ProducciOn

Balance

I

Cubo BI

I

Diferencia Balance

I

Cubo BI D iferencia

(52)

Luego de verificar mediante las planillas la informaciOn se deberá notificar las

inconsistencias si existieren con la planilla de verificaciôn, además deberán Ilenar y

adjuntar la siguiente planilla de reporte de diferencias, en la cual se clasifica la

inconsistencia por tipo de inconsistencia, es decir si la inconsistencia se refiere a

nombres de dimensiones o a valores de hechos, además se debe asignar una

gravedad de la inconsistencia en baja, media alta.

Las inconsistencias con gravedad alta deberán ser tratadas con maxima prioridad y

mientras no se solucionen no se podrá subir el sistema a producciOn.

El objetivo de las pruebas como se indicO en lineas anteriores es el de verificar los

valores del almacén de datos contra los valores del balance de la compañia.

Los valores tanto para las pruebas de emisiOn como para las pruebas de siniestros

deben ser similares a los valores de balance o de los listados técnicos contra los que

se cuadra el balance.

Se debe tomar en cuenta que en los almacenes de datos creados se considera

ünicamente la parte técnica de las operaciones de la compañIa, es decir no se

consideran los movimientos contables manuales o ajuste manuales que realice la

organizaciOn,

Por ello es importante verificar contra balance pero a su vez Si existen diferencias verificar Si no son debidas a ajustes manuales realizados por el area contable.

(53)

Planilla de Reporte de Inconsistencias

Usuario gue reporta: Fecha de Reporte: Tipo de Inconsistencia

> Inconsistencia de Nombres

Inconsistencia de Valor Gravedad

> Baja > Media

Alta

Reporte de Sistema Transaccional Perlodo de Revision

Valor inconsistente

Diferencia

Prima Neta SUper Bancos

Derechos de EmisiOn Seguro Campesino Financiamiento IVA

Prima Total

Valor de EstimaciOn Valor de Impuestos Valor de Pago Valor deRASA Valor Reaseguro Valor Recupero Detalle de diferencia:

Observaciones: