lunes, 24 de agosto de 2015

Continúa caso práctico 3

Modelo lógico del DataWareHouse

Modelo Lógico

El modelo de datos se realizará en base al Esquema en Estrella, debido a su simplicidad para entender las relaciones entre las perspectivas y los hechos, así como la facilidad para su implementación.

Tablas de Dimensiones


Tablas de dimensiones del indicador de Inversión Ejercida a nivel municipal, Fuente: elaboración propia



Tablas de dimensiones del indicador de Eficiencia del Gasto, Fuente: elaboración propia

Tablas de Hechos


 Tabla de hechos del indicador Inversión Ejercida a nivel municipal, Fuente: elaboración propia 


 Tabla de hechos del indicador de Eficiencia del Gasto, Fuente: elaboración propia

 UNIONES

 

Modelo en Estrella del indicador de Inversión Ejercida a nivel municipal, Fuente: elaboración propia


Modelo en estrella del indicador de Eficiencia del Gasto, Fuente: elaboración propia


 



viernes, 26 de junio de 2015


Nivel de granularidad

... Continúa Caso práctico - 2

Dentro de los modelos para conformar los indicadores, uno de los datos más importantes es la perspectiva “Tiempo”, para nuestro proyecto el nivel de granularidad sólo será a nivel de Ejercicio Fiscal (Año).

Para la perspectiva geográfica “Entidades”, se utilizarán los siguientes niveles, los cuales fueron obtenidos de la base de datos del Consejo Nacional de Población:

  • Nom_Ent.-Nombre de la Entidad Federativa donde se realiza la obra o proyecto.
  • Nom_Mun.- Nombre del Municipio donde se ejecuta la obra o proyecto.
  • Nom_Loc- Nombre de la Localidad.
  • Gm_2010.- Grado de Marginación a nivel Municipal.
  • Pob_Tot.- Población total del Municipio.
La perspectiva de “Ejecutores” corresponde a la instancia quién ejecuta o realiza la obra o proyecto; para los Programas Sociales el 70 % de las obras o proyectos son realizados por los Ayuntamientos Municipales.
  • Ejecutor.- Nombre del Ejecutor
Perspectiva “Programa Social”:

  • Programa_Social.- Nombre del Programa Social.
Perspectiva “Regiones” corresponde a una etiqueta que define el grado de marginación municipal y responde a la pregunta “donde”.

  • Tipo_region.- Nombre del tipo de región.
Para todas las perspectivas las claves primarias son generadas mediante la herramienta de Pentaho Data Integration, también conocida como Spoon y que de acuerdo a las mejores prácticas recomendadas en la conformación del Data WareHouse se basa en claves nuevas conocidas como claves subrogadas (Surrogates Key) y que no tienen relación con las claves primarias de la base transaccional.
Estas claves son números enteros secuenciales que agilizan el acceso a los datos.

Modelo conceptual ampliado

Modelo conceptual ampliado del indicador de Inversión Ejercida a nivel municipal, Fuente: elaboración propia





Modelo conceptual ampliado del indicador de Eficiencia del Gasto, Fuente: elaboración propia



miércoles, 24 de junio de 2015

Caso práctico. Construcción del DataWareHouse

Análisis de los requerimientos


Identificar preguntas

Las actividades de la Institución está basada en áreas funcionales operativas y administrativas; la motivación del proyecto se fundamenta en la necesidad de mayor conocimiento acerca de las operaciones de los Programas Sociales. La institución cuenta con información que se ha recopilado a través de varios ejercicios fiscales, pero dicha información se encuentra dispersa y no se cuenta con informes que permita conocer de manera adecuada la eficiencia de las acciones realizadas.

Principales preguntas realizadas:

  • Se desea conocer la cobertura de las obras o proyectos ejecutados por cada Programa Social a nivel de Municipio, las inversiones realizadas y las metas programadas y las alcanzadas.
  • Se requiere conocer la eficiencia presupuestaria de cada Programa Social, a nivel de Entidad Federativa, al comparar el Presupuesto Asignado contra la Inversión Ejercida registrada en el Sistema Transaccional y respaldada por obras y proyectos.

Queda claro que las preguntas realizadas se encuentran alineadas con la misión y los objetivos institucionales, por lo que el proyecto debe concluir en nuevo conocimiento que permita tomar decisiones adecuadas para la mejor orientación de los recursos del gasto.

Identificar indicadores y perspectivas

Inversiones Ejercidas de cada Programa Social a nivel de Municipio por Ejercicio Fiscal 
Indicador                                        Perspectivas

Eficiencia del gasto a nivel de Entidad Federativa y Programa Social por Ejercicio Fiscal
Indicador                                      Perspectivas

Modelo conceptual




Modelo conceptual del indicador de Inversión Ejercida a nivel municipal, Fuente: elaboración propia



Modelo conceptual del indicador de Eficiencia del Gasto, Fuente: elaboración propia

Análisis de los OLTP'S

Conformar Indicadores

Los indicadores se calcularán de la siguiente manera:

Inversiones Ejercidas
  • Hechos: Inversión Federal Aprobada y Ejercida
  • Función de operación: Sumatoria
Eficiencia del Gasto
  • Hechos: Presupuesto Ejercido, Inversión Ejercida (Federal)
  • Función de operación: División

Establecer correspondencias



Correspondencia entre la información del sistema transaccional y los Modelos Conceptuales, Fuente: elaboración propia











sábado, 27 de septiembre de 2014

Proyecto Sistema BI para Sector Público


El planteamiento teórico plasmado hasta este momento nos debe dar los fundamentos en los que descansará nuestro proyecto. 

El proyecto propuesto se basa en la realización de indicadores de resultados en el seguimiento y control presupuestario de Programas Sociales de la SEDESOL.

Los datos presentados son puramente ilustrativos y no pretenden dar ningún dato oficial. Como ex Empleado de esta Institución lo único que pretendo es demostrar una metodología en la cual cuento con la suficiente experiencia tanto en la parte normativa como operativa.

Las etapas las iré desglosando conforme tenga la disponibilidad de tiempo.

Objetivo

Implementar un Sistema de Inteligencia de Negocios para el seguimiento y control de los Programas Sociales de la SEDESOL, mediante la construcción de indicadores de resultados que serán consultados por diversos ejercicios fiscales en un tablero de control interactivo.

Modelo de Negocios

Se requiere conocer el proceso de operación de los Programas Sociales para poder obtener la información de calidad que nos permitirá tomar las decisiones más adecuadas. 

En razón de ello, a continuación se menciona cada una de las etapas que conforman el proceso de operación, tomando en consideración que algunas etapas ocurren de forma paralela y en donde participan diversas instancias gubernamentales.


Modelo de Negocios simplificado en la ejecución del gasto de los Programas Sociales de la Sedesol.

La Secretaría de Hacienda a inicio de año y con aprobación de la Cámara de Diputados publica en el Diario Oficial el Presupuesto de Egresos de la Federación (PEF).

En dicho documento se establecen las políticas de gastos que deberá observar la Administración Pública Federal. Dicho gasto se encuentra desglosado por Ramo Administrativo, Unidad Responsable, Programa Institucional y demás claves que permiten especificar de manera precisa el destino del gasto.

Paralelamente, la Institución a principios de año, comienza con la difusión de los Programas Sociales que atenderá; y da a conocer los mecanismos que deberán llevar a cabo los beneficiarios para poder acceder a los apoyos requeridos siempre y cuando cumplan con los requisitos establecidos en las Reglas de Operación de cada Programa Social.

Una vez que la Institución ha realizado sus actividades de difusión y promoción se reciben las solicitudes de proyectos, obras o acciones que los beneficiarios entregan directamente a la Institución, a través de alguna representación municipal o a través de alguna Organización Social debidamente reconocida.

La solicitud es capturada dentro del Sistema Integral (Sistema transaccional) y especifica la ubicación geográfica donde se llevará a cabo la Obra o Proyecto; se registra la estructura financiera que la respaldará, también las claves de Programa Social, Programa, Subprograma, Instancias participantes; así como el detalle del destino del gasto ya sea con respecto a los materiales o mano de obra que se requieran, entre otras.

Cada Programa Social se encuentra asignado a una Unidad Responsable de Programa (UARP), quién es la encargada de autorizar mediante Oficio de Autorización, parte o la totalidad de los recursos asignados en el PEF a cada Unidad Responsable (UR).

Una vez autorizada la asignación presupuestal, la Unidad Responsable (UR), podrá aprobar la solicitud del beneficiario mediante la entrega de un Oficio de Aprobación donde la Institución se compromete a entregar los recursos requeridos. 
 
En cuanto la UR cuente con la suficiencia presupuestaria dependiendo de su calendario de gasto, liberará el recurso comprometido al ejecutor, ya sea mediante una Cuenta por Liquidar Certificada (CLC), por dispersión de cheques o efectivo.

Corresponde de aquí en adelante que el ejecutor lleve a cabo la realización de la Obra o Proyecto, comprobando documentalmente los gastos en que incurrió durante el transcurso de ejecución.
 
Cabe mencionar que en el caso de las obras, la entrega de recursos se realizará dependiendo del avance físico que compruebe el ejecutor, por lo que la ministración de los recursos podrá realizarse en dos o tres exhibiciones.
 
Una vez concluido el ejercicio fiscal las Instancias participantes deberán integrar un documento en donde se consignen todas las Obras o Proyectos realizados especificando los montos aprobados, ejercidos y saldos, así como las metas programadas y las alcanzadas. 
 
Dicho documento se denomina Cierre de Ejercicio y da como concluido el ejercicio fiscal correspondiente.

Nota.- La clave para realizar cualquier Sistema Informático se basa en el conocimiento del proceso de negocio, y debe redactarse siempre con el enfoque del Cliente y de los Usuarios, ya que de lo contrario es posible que el Sistema no cumpla con las necesidades de la organización.

martes, 22 de abril de 2014

Etapas para la construcción del DataWareHouse (Cont.)

Modelo lógico del DataWareHouse 

Los modelos conceptuales son representaciones generales de la información y no determinan la forma en que serán representados; en esta siguiente etapa se diseñará el modelo lógico para contener la estructura del depósito de datos. Primeramente se seleccionará el tipo de modelo a utilizar, posteriormente se definirán las dimensiones, los hechos y las uniones correspondientes.

Tipo de modelo lógico del DataWareHouse

En este paso se debe elegir el tipo de esquema que se utilizará, se debe elegir el esquema más adecuado a los requerimientos y necesidades de los usuarios. Los modelos que se pueden emplear son esquemas en estrella, constelación o copo de nieve.

Tablas de dimensiones 

En este paso se definen las tablas de dimensiones que contendrá el DataWareHouse, cada perspectiva del modelo conceptual constituirá una tabla dimensión; para ello se deben realizar las siguientes actividades:

  • Se debe elegir un nombre que identifique a la tabla dimensión y que represente de manera adecuada la perspectiva que representa.
  • Seleccionar el campo que represente su clave principal
  • Se redefinirán los nombres de los campos en caso que sus nombres no sean lo suficientemente específicos de la información que representan.

Tablas de Hechos

La tabla de hechos contiene la representación de los indicadores que se pretende analizar o estudiar; para su construcción se deben realizar las siguientes actividades:

  • Se debe seleccionar un nombre para la tabla de hechos
  • Se definirá su clave primaria, que se compone de la combinación de las claves primarias de cada tabla de dimensión relacionada.

Uniones 

Las uniones representan las relaciones entre las tablas de dimensión y de hechos.

Integración de Datos

Una vez construido el modelo lógico se debe realizar el poblado de las estructuras físicas en apego a las políticas y reglas definidas para tal propósito.

Carga inicial

La carga de datos en el Data WareHouse requiere llevar a cabo algunas tareas que tienen relación con la calidad de los datos, limpieza de los mismos y procesos de ETL (Extract, Transform and Load, del inglés Extracción, Transformación y Carga).

En este paso se realizan las estrategias comentadas en "Procesamiento de Datos", con el propósito de que la información que se utilice cumpla con las condiciones y restricciones de calidad establecidas.
Las herramientas de ETL representan el soporte principal para las tareas de integración de datos, la mayor cantidad de esfuerzo para la construcción y actualización recaen en éstas.
 
El motor ETL de Pentaho ejecuta los trabajos (Job) y transformaciones (Transform) creados con las herramientas de Pentaho Data Integration (PDI, también conocido como Spoon).

El motor ETL es parte de la estructura del BI, pero puede correr en diferentes servidores o aún en múltiples servidores en modo de Cluster.
Para la carga de datos se requiere que primeramente se carguen las tablas de dimensiones y al final las tablas de hechos, teniendo en cuenta siempre la correcta correspondencia entre las claves de las tablas dimensiones y de hecho.
 
Cuando se utilicen esquemas copo de nieve, si existen jerarquías se deben comenzar cargando las tablas de dimensiones del nivel más general al más detallado.

Actualización

La información de los Data WareHouse son dinámicos y los análisis deben realizarse sobre información nueva que se debe actualizar periódicamente, independientemente que el Data WareHouse contenga información histórica.

Ralph Kimball, inventor del modelo multidimensional, propuso tres diferentes estrategias para llevar a cabo las actualizaciones, también conocidas como Slowly Changing Dimensions (SCD, del inglés Dimensiones Lentamente Cambiantes), los cuales se describen a continuación:

SCD Tipo 1.- Sobrescribir 
Las columnas son pobladas con la información nueva, desechando y sin conservar los valores anteriores.
 

SCD Tipo 2.- Añadir renglón
Esta estrategia permite conservar información y preservar información histórica, extrayendo datos actualizados cuando se realizan las consultas a las tablas de dimensión. 

Para seguir la pista de los cambios realizados es necesario agregar campos adicionales, lo más común es agregar campos de tipo TimeStamp al registro de la dimensión; normalmente el campo de Valid_From (válido desde...) y Valid_To (válido hasta...). Además se pueden utilizar campos que definen el registro válido actualmente (Current_Record), llenándose con valores incrementales cada vez que existe un registro nuevo.

SCD Tipo 3.- Añadir columna 
Esta estrategia necesita, al menos una columna extra en la tabla dimensión. Cuando el valor de una columna cambia se añade una columna extra que contiene el valor anterior. Para este caso sólo es posible almacenar una versión extra.

La estrategia a utilizar dependerá del tipo de datos y la periodicidad de las actualizaciones, por lo que se recomienda analizar cuidadosamente la opción seleccionada.

lunes, 21 de abril de 2014

Etapas para la construcción del DW

Hefesto propone una metodología para la construcción del DataWareHouse en cuatro etapas sencillas que permite en un periodo corto de tiempo entregar resultados.
La siguiente figura  muestra las etapas del proceso de construcción y al igual que las fases del proceso de BI, para un mayor entendimiento posteriormente se describirá un caso práctico de la implementación de indicadores.



Etapas para la construcción del Data WareHouse. (Dario, 2013)

Análisis de los Requerimientos

Identificar Preguntas

Dentro del ciclo de vida para el análisis y desarrollo de sistemas, una de las primeras actividades que deben desarrollarse es recopilar información acerca de las necesidades de los usuarios finales y de los directivos de la Institución.

Este conocimiento se lleva a cabo a través de métodos interactivos como son: Las entrevistas, las reuniones, encuestas mediante cuestionarios.
 
Otra forma de recopilar información es a través de métodos no intrusivos, como son: muestreo, la investigación y la observación del comportamiento, y el entorno físico. Este método debe respaldarse del método anterior, de lo contrario el resultado puede llegar a ser deficiente.
 
El conocimiento de los procesos del negocio y de los objetivos organizacionales es requerido por parte de quien realiza las preguntas, ya que éstas deben realizarse con objetividad y enfocados a obtener información vista desde diferentes perspectivas que ayude en la toma de decisiones.

Identificar Indicadores y Perspectivas

Una vez establecidas las preguntas de negocio, se deben analizar para poder obtener los indicadores claves que serán utilizados; estos indicadores deberán ser representados con valores numéricos y basados en la forma propuesta para la Matriz de Indicadores de Resultados.
 
Las organizaciones cuentan con áreas de interés para el análisis; dentro de cada una de ellas existen objetos específicos a los que se deben examinar para extraer el conocimiento requerido.

Modelo Conceptual

Un modelo conceptual es un esquema obtenido a partir de una lista descriptiva de objetos y asociaciones identificadas.
Para el caso del Data WareHouse el modelo se conforma de indicadores y perspectivas, lo que permitirá observar con claridad los alcances de las interrogantes planteadas por los usuarios.
 
El modelo debe ser claro para el implementador ya que a través de él se debe realizar la implementación física en el Data WareHouse, y debe permitir con facilidad ser explicado al usuario quién deberá aprobar si corresponde a las preguntas planteadas en la etapa de recolección de necesidades.
 
El modelo, tal como se ilustra en la figura requiere que las perspectivas se coloquen del lado izquierdo unido a través de un óvalo central que representa la relación que existe entre ellas. La relación constituye el proceso o área de estudio elegida. A la derecha se colocan los indicadores los cuales son entrelazados a la relación central.


Elementos del Modelo Conceptual, Fuente: elaboración propia

Análisis de los OLTP

Conformar Indicadores

En este paso debemos mostrar cómo se realizarán los indicadores; para nuestro proyecto debemos apegarnos a las especificaciones definidas en "Características de los Indicadores"  e indicar las operaciones que se realizarán.

Establecer correspondencias

Este paso tiene como propósito mostrar la información y las características que contienen los Sistemas OLTP para poder identificar las correspondencias entre el modelo conceptual requerido y las fuentes de datos.

Nivel de Granuralidad

Una vez establecidas las relaciones entre el modelo conceptual y las OLTP, se deben seleccionar los atributos que mostrarán las perspectivas por las que se analizarán y filtrarán los indicadores.

El análisis de la información por medio de los atributos seleccionados requiere un conocimiento de los procesos de negocio, por lo que es necesario conocer a detalle el significado de cada campo de información, esto se obtiene investigando los diccionarios de datos, entrevistando a los usuarios y a los administradores de los Sistemas.

El nivel de detalle del análisis de información se define en este paso y uno de los atributos más importantes a definir será la dimensión Tiempo.

Modelo Conceptual Ampliado

Los resultados obtenidos en los pasos anteriores son graficados, colocando las diferentes perspectivas y desglosando los atributos seleccionados por un lado; y los indicadores con sus respectivas fórmulas de cálculo por el otro; ambos lados unidos por medio de la acción de relación.

viernes, 18 de abril de 2014

Operadores OLAP

Los modelos de datos se caracterizan en cómo se organiza la información, dicha organización se denomina estructura, y la forma en que se explota y explora la información se denominan operadores.

Los operadores OLAP son operadores de análisis, realizan las mismas consultas que se hacen mediante SQL, además cuenta con características especializadas para análisis de la información desde la perspectiva de almacenes de datos.

Las consultas realizadas en los modelos multidimensionales tienen la ventaja de realizarse de manera fácil y rápida; dichas consultas suelen ser dinámicas arrastrando simplemente con el ratón las dimensiones y las medidas. Por esa razón se dice que más que operadores son navegadores de informes.

Clasificación de los Operadores OLAP

  • Drill.- La información se presenta desglosada a mayor nivel de detalle, mientras que las sumarizaciones son menores.
  • Roll.- Al contrario de Drill, las sumarizaciones se presentan consolidadas, mientras que las dimensiones se presentan a un menor nivel de detalle.
  • Slice & Dice.- Se seleccionan y se proyectan los datos
  • Pivot.- se reorientan las dimensiones
Operadores OLAP: Roll, Drill y Pivot respectivamente, Fuente: elaboración propia

Expresiones Multidimensionales (MDX) 

MDX es un lenguaje de consulta para bases de datos multidimensionales sobre cubos OLAP, se utiliza en Sistema de Inteligencia de Negocios para generar reportes para la toma de decisiones basados en datos históricos. Tal como el lenguaje SQL representa un estándar para las bases de datos relacionales, MDX lo es para las bases de datos multidimensionales.

Sintaxis básica de MDX

SELECT {[Measures].[Inversion 1], [Measures].[Inversion 2] } ON COLUMNS,
{[Entidades].members} ON ROWS
WHERE [Ejercicio Fiscal].[2010]

Incorporar un Reporte realizado en Report Designer dento de un programa Java

El Pentaho Report Designer (PRD) es una herramienta de la Suite de Pentaho Community que permite transformar datos en información útil para ...

Destacados del Mes