Business Intelligence desde Concar SQL con Transact SQL y PowerPivot de Excel

Para lograr con éxito desarrollar una herramienta de Business Intelligence debemos tener algunas consideraciones antes de iniciar este tipo de proyectos. A continuación voy a lanzar algunas ideas sueltas que ayuden a comprender de manera práctica y de modo muy general el objetivo de esta entrada.

Consideraciones:

Para poder elaborar un cuadro de mando es necesario previamente crear un datawarehouse.

Un datawarehouse es aquella base de datos que recopila registros de una o más fuentes, es decir de otros softwares (Los llamados software de usuarios o transaccionales), en esta base de datos se integran, depuran y sintetizan los registros con el objetivo de servir de plataforma para reportes de gerencia o de directorio, es decir para el personal que toma decisiones.

La idea es:

Convertir los datos en información y que la información se convierta en conocimiento a fin de la adecuada toma de decisiones.

Un cuadro de mando es aquella herramienta de gestión empresarial que presenta información de indicadores, de tal forma que el personal de dirección pueda monitorear el desempeño de la empresa que dirige.

Similar a lo que, análogamente seria el tablero de mando de un automóvil, en donde existen indicadores de velocidad, nivel de combustible, sensores de funcionamiento, etc.

Por ejemplo, para el ámbito empresarial el tablero de mando podría estar compuesto de:

• Indicadores de Gestión Comercial (Ventas y Comercialización)

• Indicadores de Gestión Financiera (Ratios Financieros y Económicos)

• Indicadores de Producción (Almacenamiento y Producción)

• Indicadores de Recursos Humanos (Comportamiento y Productividad del personal)

Esta herramienta es útil para poder manejar la empresa y llevarla al cumplimiento de sus objetivos, de tal forma que haga realidad el planeamiento estratégico.

En la práctica la tarea puede ser compleja, algunas empresas no cuentan con ERP, por ello, tienen distintos softwares y estos no comulgan entre sí, es decir, tienen diferente arquitectura de base de datos.

Un cuadro de mando es eficiente si presenta información confiable y oportuna, por ello se requiere que presente información en línea, sin embargo la actualización del datawarehouse puede consumir rendimiento de los softwares de usuario, una alternativa consiste en crear una tarea programada durante un horario fuera de la jornada de laborales.

Con respecto a la confiabilidad de la información, esto es independiente a la creación del cuadro de mando, es decir no podemos esperar a la mejora de los procesos de control para recién crear una herramienta de business intelligence, estos procesos son paralelos e independientes, la mejora de la calidad de la información es una tarea más compleja y depende del establecimiento de controles en la entrada de datos de los usuarios, así como de la interrelación de las áreas en una empresa.

Cada persona y cada área es responsable de monitorear y prevenir que los datos que adquieran constituyan materia prima de la mayor calidad posible.

De esta forma, si la entrada de datos es de calidad, entonces, la salida de datos es de calidad, en otras palabras la información que presente el cuadro de mando será confiable.

Finalmente, es necesario tener en claro el modelamiento de datos, esta tarea es definida previamente en el pensamiento del ser humano, es decir el datawarehouse existe en el cerebro de la persona que desarrolla y lleva a cabo el proyecto.

Se materializa al definir las relaciones, las tablas y los campos necesarios para el cuadro de mando, es recomendable diagramar este proceso y establecer las dimensiones, el objetivo es presentar información relevante, por ejemplo, para el análisis de las ventas, el cuadro de mando no presentará datos detallados por factura, por el contrario se concentra en presentar información resumida para la toma de decisiones, es decir ventas por años, bimestres, trimestres, meses, semanas, días, etc. Como usted puede apreciar aquí la dimensión es el TIEMPO.

De forma similar, las ventas se presentarán por regiones, países, provincias, distritos, tiendas, etc. Como usted puede apreciar aquí la dimensión es la UBICACION.

Excel cuenta con herramientas de business intelligence, las tablas dinámicas consideran a las dimensiones como segmentos, adicionalmente a la gama de gráficos tradicionales ahora cuenta con minigráficos los cuales se ubican en celdas y muestran la tendencia por línea, los formatos condicionales cuentan con escalas de colores y reglas que muestran el avance del cumplimiento de los indicadores respecto a los objetivos trazados, y un complemento llamado PowerPivot que se usa para conectarse a diferentes bases de datos y detectar relaciones entre las tablas, puede transformar millones de registros en segundos, etc.

PowerPivot no es exclusivo de Excel, también se usa con SharePoint y Reportin Services de SQL Server, es decir la herramienta perfecta para la construcción de Business Intelligence.

Si llego hasta aquí, gracias por su interés, ahora vamos a poner en práctica la teoría.

Vamos a crear 4 consultas desde SQL Server Management Studio para obtener los gastos clasificados por centros de costos de Concar desde la tabla de libro diario y le agregaremos dimensiones de tiempo y clasificaciones de rubros para poder colocar estas consultas en el entorno de PowerPivot y mostrar la información en una tabla dinámica junto a segmentos, minigráficos y formatos condicionales, espero les guste y sea de su agrado. La tabla del libro diario en Concar SQL tiene como nombre la siguiente nomenclatura: CT0001COMD11

En donde:

CT es el código para denominar una tabla de movimiento

0001 es el código de la empresa

COMD es el código para la tabla de detalle de movimientos

11 es código para el año 2011

Tanto el código para la empresa y el año son variables, así para el libro diario del año 2013 en la empresa 0003 el nombre de la tabla será CT0003COMD13

La estructura de esta tabla contiene 58 campos o columnas. Sólo vamos a tomar en consideración 5 campos:

DCUENTA, almacena el código de la cuenta

DCENCOS, almacena el código del centro de costos

DDH, almacena el caracter D para importes al debe y el caracter H para importes al haber

DMNIMPOR, almacena los importes expresados en moneda local (Nuevos Soles para Perú)

DFECCOM2, almacena las fechas en formato largo

En este caso en particular la empresa ha optado por registrar sus gastos mediante cuentas contables en el elemento 9 (Contabilidad Analítica de Explotación) del Plan Contable General Empresarial, por ello debemos de filtrar las cuentas que empiecen con el carácter 9

El campo DDH indica si un importe es positivo o negativo, vamos a modificar la consulta a fin que se muestren los valores como positivos o negativos

Ahora desarrollemos la dimensión o segmentación TIEMPO a partir del campo DFECCOM2 obtendremos el año, el trimestre y el mes.

Además podemos extraer los 2 primeros caracteres del código de cuenta para identificar el tipo de gasto y los 4 primeros caracteres del código de cuenta para identificar la cuenta contable por naturaleza.

Ahora transformemos los códigos de cuenta en descripciones, para lograr ello debemos de relacionar nuestra consulta con la siguiente tabla:

CT0001PLEM, almacena el catálogo de cuentas contables

Ahora, rotulemos los campos para una mejor comprensión cuando elaboremos nuestro análisis de gastos.

Hasta aquí fue la construcción de la tabla principal. Sin embargo, faltan 3 tablas más para finalizar este pequeño datawarehouse.

Listo ya tenemos el código Transact SQL, son 4 consultas de selección que conforma el datawarehouse y que vamos a diagramar con PowerPivot

Inicie sesión en Excel, vaya a la ficha PowerPivot y seleccione Ventana de PowerPivot

Desde la ficha Inicio seleccione Obtener datos externos desde SQL Server

Digite o seleccione el nombre del servidor, ingrese su nombre de usuario, contraseña, seleccione la base de datos RSCONCAR, si desea pruebe la conexión y de click a Siguiente.

Seleccione escribir una consulta que especifique los datos a importar y de click en Siguiente

Digite el nombre descriptivo de la consulta como DATA digite o copie y pegue la consulta que construye la tabla principal que para fines didácticos hemos construido en SQL Server. Si desea valide y de click en Finalizar

Cierre el asistente para ver sus datos en PowerPivot

Repita el paso anterior para agregar las 3 tablas adicionales. Cuando termine tendrá una vista de datos similar a la siguiente imagen:

Ahora presione Vista de diagrama para relacionar las tablas y ver el modelo de datos que usted ha creado.

Relacione las tablas, para ello seleccione el campo COD_LOCACION desde la tabla DATA y arrastre si soltar el mouse hacia el campo COD_LOCACION de la tabla CENTRO DE COSTO

Luego, haga lo mismo para relacionar el campo COD_GASTO desde la tabla DATA y arrastre si soltar el mouse hacia el campo COD_GASTO de la tabla TIPO DE GASTO.

Finalmente, haga lo mismo para relacionar el campo COD_CUENTA desde la tabla DATA y arrastre si soltar el mouse hacia el campo COD_CUENTA de la tabla CUENTAS.

Cuando termine el modelo de datos se verá algo similar a la siguiente imagen

Seleccione Tabla dinámica

Y ubíquela según su elección

Arrastre el campo CENTRO_DE_COSTO hacia el panel de segmentación vertical

Arrastre el campo TIPO_DE_GASTO hacia el panel de segmentación horizontal

Arrastre el campo CUENTA hacia el panel de etiquetas de fila

Arrastre el campo TRIMESTRE hacia el panel de columnas.

Arrastre el campo GASTO hacia el panel de filas.

Finalmente, arrastre el campo IMPORTE al panel de Valores.

Cierre la lista de campos de PowerPivot y aplique formato a su elección.

Botón derecho del mouse sobre la tabla dinámica para configurar la opción de mostrar elementos sin datos en la columna

Expanda toda la tabla dinámica y seleccione los importes desde el trimestre 1 hasta el trimestre 4, presione la ficha Inicio para seleccionar Nueva regla en el comando Formato condicional

Escoja el estilo de formato Conjunto de iconos y configure el color rojo cuando el gasto sea alto, amarillo cuando se encuentre en el promedio, verde cuando sea bajo y presione Aceptar

Luego, seleccione los valores incluidos los totales de fila y columnas.

Presione la ficha Insertar y seleccione Columna para agregar minigráficos.

Ubique los minigráficos en la columna adyacente a la tabla dinámica y presione Aceptar.

Seleccione color de minigráfico y escoja el de su elección

Seleccione color de marcador, punto alto y escoja el de su elección

Seleccione color de marcador, punto bajo y escoja el de su elección

Pruebe a seleccionar GASTOS ADMINISTRATIVOS

Se aprecia rápidamente que los gastos administrativos se encuentran en 4 centros de costos, además la carga de personal y la valuación por deterioro de activo corresponden a los gastos de mayor valor en el 3er. y el 4to. bimestre del año.

Ahora, seleccione el centro de costo DEVELOPMENT BUSINESS, se observa que el gasto en transporte es el más elevado.

Ahora, seleccione el centro de costo CAJAMARCA OFFICE y despliegue el detalle de la carga de personal.

Se aprecia que la participación de utilidades y los sueldos elevan el gasto por encima del promedio constituyéndose en los importes que finalmente colocan en rojo la carga de personal en el 3er. y 4to. bimestre.

Ahora, inserte los gráficos de su elección para mostrar la evolución de los gastos por trimestre y los gastos por rubros, para ello detrás de cada gráfico se han vinculado los totales de columna y de fila de la tabla dinámica.

Este es el aspecto que podría tener su hoja de cálculo cuando finalice.

Grabe el archivo. Los datos se encuentran vinculados a la base de datos de Concar SQL, es decir cada vez que se efectúen registros en la contabilidad este análisis de gastos dinámicamente se actualizará.