Slow Changing Dimensions

Lo ideal cuando desarrollamos una aplicación de análisis de datos, es que las dimensiones sean estáticas y no cambien con el tiempo, pero no siempre es así.

A veces queremos calcular el coste salarial de los distintos departamentos de una compañía, pero los empleados cambian de departamento o queremos ver las ventas por departamento y los vendedores cambian de mercado. Estas dimensiones que cambian en el tiempo se llaman ‘slow changing dimensions’. En este post se describe una posible solución para tratar con este problema.

Para desarrollar este ejemplo me he creado un pequeño conjunto de datos en Excel con 4 tablas Ventas, Empleados, Departamentos y Histórico. En esta última tabla es un histórico con  los cambios de departamentos de los empleados.

Mi objetivo es crearme una tabla de enlace entre la tabla de Ventas y del Histórico que sitúe una venta en su periodo correspondiente.

 Lo primero que voy a hacer es cargar la tabla de Histórico añadiendo un identificador. Cada registro de esta tabla me señala a que departamento pertenece un comercial durante un periodo determinado.

El paso siguiente es cargar la tabla de Ventas y crear un identificador combinando la Fecha y el EmpID.

Ahora voy a crear la tabla de enlace entre las dos tablas. En la tabla de enlace debería haber un registro por cada fecha en la que un vendedor ha cerrado una oportunidad; en el mismo registro debería haber un enlace con la tabla de histórico. Lo primero que voy a hacer es crearme una tabla temporal con todas las fechas y combinarla con todos los periodos.

El resultado es una tabla que contiene muchos registros que no son válidos, así que para finalizar quitaremos los registros no válidos de la tabla.

Para finalizar, solamente falta cargar las tablas de empleados y departamentos.

BD4BD, Business Discovery for Big Data

Presentación sobre Business Discovery y Big Data que hemos hecho mi compñero Juan Gerardo y yo para el Business Discovery World Tour del 2013.

Business Discovery for Big Data (Spanish) from Ferran Garcia

Calcular un Campo Acumulado

Un cliente necesitaba tener una tabla en QlikView con las ventas diarias y con el acumulado hasta ese día y me ha preguntado como lo podía desarrollar.

Para desarrollar un pequeño ejemplo, lo primero es crear un pequeño set de dados. En mi caso me he creado esta tabla Excel.

Ahora desarrollamos un pequeño script que carga los datos y calcula el acumulado.

Este script utiliza dos funciones interesantes numsum y peek. La primera devuelve el valor numérico de la suma de los argumentos. La función peek devuelve un valor determinado de una tabla; por ejemplo peek(nombre_de_campo,n) devuelve el valor n+1 de del campo ‘nombre_de_campo’. Si no se indica el registro, la función peek devuelve el último valor. Así, peek(Acumulado) no devuelve el último valor de la tabla ‘Acumulado’.

Finalmente, utilizamos una tabla para comprobar que el ejemplo se comporta como esperábamos. 

QlikView for Developers Cookbook, Stephan Redmon.

Acabo de recibir una copia de QlikView Developer Cookbokk de Stephan Redomon y tiene muy buena pinta!!

Durante la próximas semanas voy revisarlo a fondo y en cuanto tenga un rato escribo una crítica sobre el libro.

QlikView for Developersbook Cook

Ya tengo ganas de empezar!!

Como crear un modelo de datos para QlikView con consultas SQL.

A veces puede interesarnos construir un modelo de datos QlikView a través de consultas SQL, en lugar de hacerlo a partir del script de carga. Las razones por las que nos puede iunteresar, generalmente son o que las consultas ya están desarrolladas y probadas o que soy nuevo en QlikView pero me manejo muy bien en SQL

Construir el modelo de datos a partir de consultas SQL es muy fácil. Como ejemplo vamos a crear una tabla QlikView a partir de una consulta SQL. Vamos a utilizar una base de datos Oracle 11g Express Editio y SQL Developer para analizar las tablas. Cuando conozcamos las tablas y la consulta, utilizaremos QlikView Developer y una conexión ODBC para acceder a Oracle y crear la tabla.

La base de datos Oracle trae pre-cargados unos modelos de datos que podemos utilizar como ejemplo, utilizamos Oracle SQL Developer para acceder al esquema SH y crear una consulta.

En esta imagen podemos ver la consulta y el resultado obtenido al ejecutarla. Es importante ver que la consulta nos trae datos que provienen de dos tablas distintas Cost y Sales; el resultado de la consulta son los beneficios y la llamaremos ‘profits’.

Una vez testada la consulta, abrimos QlikView, creamos un nuevo documento y creamos una conexión ODBC contra la base de datos. Con la conexión creada vamos a crear la tabla. Para ello vamos a utilizar este código:

 Analicemos el código. En la fila 12 abrimos la conexión ODBC, en la fila 14 le damos un nombre a la tabla, de las filas 15 a 16 tenemos la sentencia LOAD que generará la tabla QlikView y en la fila 17 tenemos la sentencia SELECT que lanzará la sentencia que hemos escrito entre las filas 18 a 33.

Con este código ya podemos ejecutar la recarga y QlikView creará la tabla y se traerá los datos.

Direct Discovery con Teradata, primeros pasos.

En un post anterior conectamos QlikView con una base de datos Teradata y hoy vamos a probar una nueva funcionalidad llamada Direct Discovery,  que ha aparecido en la versión 11.2 de Qlik View.

Simplificando mucho, las aplicaciones QlikView están basadas en un modelo de datos 100% en memoria. Direct Discovery permite extender esta aproximación y crear un modelo de datos híbrido, donde una parte de los datos están en memoria y otra parte están en una base de datos.

Algunos casos de uso para esta funcionalidad podrían ser:

• Ver información de detalle. Algunas aplicaciones QlikView podrían proporcionar información agregada y Direct Discovery podría facilitar el detalle.
• Acceso a información histórica. Una aplicación podría tener 2 o 3 años de información en memoria y acceder a información histórica mediante Direct Discovery.
• Presentar información reciente. una aplicación QlikView podría recargarse cada noche, pero se podría acceder a información más reciente mediante Direct Discovery.

Para más información sobre Direct Discovery ver este enlace.

El objetivo es crear una pequeña aplicación que nos permita testar Direct Discovery con una base de datos Teradata. Disponemos una máquina virtual con una base de datos y hemos configurado una conexión a esta base de datos. Estaa base de datos tiene un esquema de datos de ejemplo llamado retail que usaremos para nuestro test. El modelo de datos de nuestra aplicación será este:

La tabla 'CONTRACT' contiene todas las órdenes de compra y la tabla 'DirectTable' contiene el detalle de las líneas de estas órdenes de compra. Todo el modelo de datos estará en memoria a excepción de la tabla 'DirectTable' que estará en la base de datos y será accedida directamente.

El primer paso será crea una nueva aplicación QlikView y preparar la carga de las tablas que se almacenarán en memoria. Para ello, tal como hemos visto en un post anterior, abrimos una conexión ODBC contra Teradata y seleccionamos las tablas que queramos cargar; en este caso Contract, Client, Area, region y employee.

Ahora en el editor de scripts de QlikView ya tenemos el código para crear la parte in-memory del modelo de datos y nos falta el código para la parte en base de datos del modelo de datos. Para este código y en la versión 11.2 de QlikView tenemos una nueva sentencia 'DIRECT SELECT'.

Recargamos el modelo de datos y ya podemos empezar a construir nuestra aplicación. A modo de ejemplo voy a construir una aplicación con dos gráficas una con datos que vienen de 'CONTRACT?, una tabla almacenadaen memoria y otra con datos de la tabla 'DirectTable' que es accedida por QlikView directamente a la base de datos Teradata,

A la izquierda puede verse la expresión de la gráfica con datos en memoria y a la derecha la expresión de la gráfica con datos en tabla. Para finalizar la aplicación añado dos filtros que vienen de las tablas en memoria 'Region' y 'Area' y un filtro 'L_STATUS' que viene de la Direct Table; es decir de la tabla almacenada en la base de datos.

El resultado es una aplicación que contiene dos gráficas una, en verde, con datos que provienen de la parte del modelo de datos en memoria de QlikView y otra gráfica, en naranja, con datos que vienen de la tabla almacenada en la basede datos. los filtros Area y Region están en tablas en memoria, mientras que el filtro status viene de la tabla en base de datos.

QlikView, Pruebas de conectividad con Teradata

Para realizar una prueba de conectividad entre QlikView y Teradata he utilizado la máquina virtual 'TD Express 14.0 with 4GB of storage' que esta disponible para descargar en la web de Teradata http://developer.teradata.com/. Es necesario registrase para descargar de forma gratuita esta imagen virtual.

Esta máquina virtual contiene una base de datos Teradata lista para funcionar con algún esquema pre-cargado para poder hacer pruebas.

El primer paso es descargar la máquina virtual y descomprimirla utilizando '7 Zip'. Cuando arranquemos la máquina virtual por primera vez, nos aparecerá un diálogo que nos preguntará si hemos copiado o movido la imagen virtual; debemos seleccionar 'Moved it'. La imagen virtual es un Linux donde esta instalada la base de datos, al arrancar la imagen nos pedirá un usuario y un password, debemos poner root en los dos.

Una vez arrancada la imagen virtual ya disponemos de una base de datos Teradata en  funcionamiento.  Para comprobarlo, vamos a echar un vistazo a los esquemas que tiene precargados. Para ello arrancamos Teradata Studio Express desde el icono del escritorio.

Ahora vamos a crear una nueva conexión con nuestra base de datos. En la ventana superior izquierda encontraremos una carpeta vacía con las 'Database Conection'.  Pulsamos el botón derecho y seleccionamos 'New'.

Seleccionamos 'Teradata Database' y le damos un nombre a la conexión, luego pulsamos 'Next'.

Para configurar la conexión, solamente tenemos que introducir localhost como database Server name y dbc como usuario y password. Para acabar presionamos 'Finish'.

Una vez ya tenemos la conexión creada, hacemos qlik con el botón derecho sobre la conexión y seleccionamos 'connect'.

Cuando se establezca la conexión, ya podremos navegar los distintos esquemas que contiene. Para este ejemplo vamos a utilizar el esquema 'retail'.

Ahora ya disponemos de una base de datos Teradata en una máquina virtual. El siguiente paso es conectar QlikView a la base de datos, para ello deberemos instalar el driver odbc de Teradata en la mima máquina donde tengamos instalado QlikView. Para esta prueba de conectividad solamente vamos a utlizar QlikView Desktop.

El primer paso será descargar el driver ODBC de Teradata (http://downloads.teradata.com/download/connectivity/odbc-driver/windows) desde la  web de Teradata. Este fichero zip contiene tres paquetes que tienen que instalarse en el orden correcto: TDICU, TDGSS y TDODBC. Si nos equivocamos de orden, hay que desinstalar y volver a instalar.

Con el ODBC instalado ya podemos configurar el una conexión ODBC. Para ello abrimos el ODBC Administrator.

Creamos un nuevo sistema DSN y configuramos la conexión con la dirección IP de la máquina virtual y con 'dbc' como usuario y password. Si no conocemos la dirección IP de la máquina virtual podemos utilizar el comando IFCONFIG desde una ventana de línea de comandos de la máquina Linux.

Ahora ya tenemos la base de datos y el driver ODBC, ya estamos en disposición de abrir nuestro QlikView y hacer una aplicación con los datos que almacena Teradata.

Arranco QlikView, creo un nuevo documento y lo guardo. Edito el script y voy a crear la conexión con Teradata. Selecciono una conexión de tipo ODBC y presiono 'Connect'.

Selecciono la conexión que acabo de crear y utilizo 'dbc' como usuario y password. Antes de presionar 'Ok', testeo la conexión para estar seguro de que todo va a funcionar.

Volviendo al script QlikView presiono 'Select' para ver los distintos esquemas.

Seleccionamos 'retail' como owner y ya podemos ver las tablas de este esquema. A partir de este momento el desarrollo se realiza como en cualquier aplicación QlikView. En mi caso he cargado todas las tablas y he creado este modelo de datos.

A partir de aquí crear el una aplicación de análisis es fácil. Para este ejemplo, QlikView tiene toda la información del modelo de datos en memoria; es decir se trata de una aplicación QlikView clásica. Mediante una nueva funcionalidad llamada Direct Discovery, existe la posibilidad de crear un modelo de daots mixto con datos en memoria y datos que se acceden directamente en las tablas de Teradata, pero esto lo dejamos para otro día.

Algunos datos cuentan historias.

Para los que no conozcan Google Trends y de forma muy rápida, te permite saber en número de veces que se busca alguna palabra en Google y la evolución en el tiempo de esta búsqueda; es decir, te da una idea del interés que tiene la gente por algo.

Hace unos días se me ocurrió poner en Google Trends la palabra ‘corrupción’, limite la zona de interés a España y a los últimos 12 meses. El resultado fue este:

Pensé: 'Esta claro el caso Bárcenas ha hecho aumentar entre los españoles la preocupación por la corrupción'. Luego pensé que para ver realmente el interés de los ciudadanos era importante compararlo con algo y comparé la palabra 'corrupción' con la palabra fútbol  solamente en España y durante los 12 últimos meses.

Sorpresa!! Algunos datos nos cuentan cosas.

QlikView Vs Microstrategy en Google Trends