Creando una Dimensin de tiempo en SQL Server Analysis Services

Si queremos comenzar a crear un cubo para anlisis de datos en SQL Server Analysis Services (SSAS) versiones 2005, 2008 y 2008 R2, en lo primero que debemos pensar es en la dimensin de tiempo. Y es que no tiene sentido crear un cubo para analizar nuestras ventas, inventario, datos de salud, o cualquier otro hecho, sin tener la perspectiva del tiempo que le d sentido a dichos datos.

Una dimensin de tiempo define adems la granularidad en que nuestros datos en las tablas de hechos han sido generados, ya sea a nivel de ao, semestre, trimestre, mes, da, hora, minuto, segundo, por nombrar algunas escalas. Si bien es cierto que a pesar de que nuestros datos en las tablas de hechos estn guardados a un nivel granular de tiempo en especfico, por decir ventas a nivel de mes, ventas a nivel de da, etc.; es una buena prctica crear una dimensin de tiempo que incluya todos los niveles de granularidad que podran usarse no slo en las tablas de hechos (fact tables) que se vayan a crear ahora requieran, sino tambin las que se puedan tener a lo largo de la vida de nuestra solucin. Por tanto es recomendable por lo menos crear una dimensin de tiempo con los niveles: ao>semestre>trimestre>mes>da.

En otro caso, si la industria en la que estn trabajando lo requiere, se podran considerar otros niveles de tiempo menores a da, como hora>minuto>segundo, pero la estrategia de implementacin de ese nivel de granularidad puede ser muy distinta a la que vamos a ver hoy en este artculo para el caso de da como mnimo nivel granular.

Una de las facilidades que nos brinda SSAS con respecto a la dimensin de tiempo, es que l mismo la genere por nosotros inclusosin teneruna tabla fsica de tiempo pre-existente en nuestro data warehouse. Este es el escenario que vamos a explorar en esta oportunidad, hablaremos de las otras opciones que tiene SSAS ms adelante.

El propsito de este artculo es entender cmo SSAS nos facilita la vida en trminos de generar nuestra propia dimensin de tiempo, que luego adems podremos personalizar a nuestro gusto. El segundo propsito es que podamos aprender cmo SSAS hace el trabajo y entender su funcionamiento, de modo que nosotros podamos crear nuestra propia dimensin de tiempo desde cero si algo no nos gusta. Comencemos!

1. Creando el Data WarehouseVamos a crear una nueva base de datos con nombreMiDataWarehouseen SQL Server 2008 R2, la cual ser nuestro DataWarehouse ficticio sin tabla u otro objeto alguno:

2. Creando el proyecto de Analysis ServicesAhora crearemos un nuevo proyecto de SSAS en elBusiness Intelligence Development Studio (BIDS)de la versin de SQL Server 2008R2:

4. Creando el Data SourceCreamos un nuevo Data Source que apunte hacia nuestra base de datosMiDataWarehouse:

5. Creando el Data Source ViewAhora procedemos a crear nuestro Data Source View, en donde posteriormente SSAS generar la definicin de nuestra dimensin de tiempo de manera automtica:

Como mencion anteriormente, no tenemos tabla alguna en nuestra base de datos, as que no tenemos nada que agregar al Data Source View:

6. Creando la dimensin de tiempoAhora procedemos a crear nuestra dimensin de tiempo en SSAS:

La pantalla que se muestra a continuacin es clave, ya que nos permite indicar a SSAS, de qu forma generaremos nuestra nueva dimensin, en este caso la dimensin de tiempo:

Nos detenemos aqu un momento para explicar las opciones que se muestran:

Use an existing table: Se crear una nueva dimensin en nuestra base de datos OLAP (SSAS) basada en una tablapre-existentede nuestro Data Source View (y por tanto en nuestro DW).

Generate a time table in the data source: Se crear una dimensin de tiempo en nuestro DW, su respectiva definicin en el Data Source View y la dimensin de tiempo en nuestra base de datos OLAP.

Generate a time table on the server: Se crear la dimensin de tiempo en nuestra base de datos OLAP similar a la opcin anterior. La posible desventaja de esta opcin es que no nos crear nada en nuestro Data Source View que podamos modificar si as lo deseamos. Tampoco necesita de una dimensin existente en nuestro Data Warehouse.

Generate a non-time table in the data source: Se crear una dimensin distinta a una dimensin de tiempo en nuestro DW, su respectiva definicin en el Data Source View y la dimensin correspondiente en nuestra base de datos OLAP.

Como se muestra en la imagen anterior, seleccionamos la opcinGenerate a time table in the data sourcepara que SSAS sea el que se encargue de todo el trabajo.

La siguiente pantalla del asistente (Dimension Wizard) nos pide el rango de fechas para los cuales queremos generar datos en nueva dimensin de tiempo. De igual manera nos pide seleccionar cul son los periodos de tiempo que queremos generar. En mi caso he seleccionado todas las opciones pero es muy probable que no todos necesiten la opcin de Half Year (semestre) o la de Ten Days (diez das) por mencionar alguna de las menos comunes. Finalmente, esta pantalla nos pide indicar cul ser el idioma en que se deben generar los datos de nuestra dimensin de tiempo y como se muestra a continuacin, no incluye el idioma Espaol (lo cual es una deficiencia siendo el Espaol el segundo o tercer lenguaje ms hablado del mundo).

Una de las formas superar esta deficiencia sera definiendo unatraduccin(dimension translation) para cada atributo. Pueden revisar ms detalles de cmo hacer esto en Defining and Browsing Translations (http://technet.microsoft.com/en-us/library/ms166708(SQL.90).aspx). Nosotros seguiremos enfocados en cmo SSAS genera la dimensin de tiempo (aunque sea en Ingls) y las relaciones entre sus atributos.

La siguiente pantalla del asistente, pregunta por los tipos de calendario que queremos generar en nuestra dimensin. Los ms usados sonRegular calendar(calendario natural) yFiscal calendar(calendario fiscal) que son las que seleccionaremos. En el caso del calendario fiscal, es posible indicar cul ser el da y el mes en que se inicia dicho calendario de acuerdo a nuestra organizacin, as mismo el nombre del ao fiscal en comparacin con el nombre del ao calendario:

Esta es toda la informacin que SSAS necesita para generar nuestra dimensin de tiempo en el lado OLAP (SSAS). Ahora en la pantalla final que se muestra a continuacin seleccionaremos elcheckboxque diceGenerate schema nowpara que en este mismo momento SSAS nos cree tambin la tabla fsica en nuestro Data Warehouse y su definicin en nuestro Data Source View.

Ahora aparecer un nuevo asistente que nos guiar a travs del proceso de la generacin del esquema fsico y lgico que soportarn nuestra dimensin de tiempo, as como los datos (miembros) que contendr:

La primera pregunta del asistente es acerca del Data Source View (DSV) en que deseamos crear la definicin de la nueva dimensin. En nuestro caso seleccionaremos el mismo DSV que ya hemos creado al inicio de esta solucin y que hasta el momento no contiene elemento alguno:

Ahora vienen las preguntas con respecto a la creacin fsica de la tabla sobre nuestro Data Warehouse incluyendo si queremos poblar de datos nuestra nueva tabla:

Una pantalla ms antes de terminar y esta se refiere a la convencin de nombres o estndares a usar en las columnas de la nueva tabla:

Una vez finalizado el asistente, se inicia el proceso de generacin del esquema y de la tabla fsica, el cual puede ser examinado en detalle en la siguiente pantalla:

7. Analizando la estructura de la nueva dimensin de tiempoYa tenemos nuestro nuevo esquema y estamos listos para ver la magia detrs del espectculo, comenzando por la nueva tabladbo.Timecreada en nuestra base de datosMiDataWareHouse.

Noten que el asistente ha creado columnas para cada uno de los periodos de tiempo siguiendo las convenciones de nombre seleccionadas:

Si hacemos una consulta a la tabla generada, veremos que tambin se han generado los datos apropiados para cada una de las columnas en el rango de fechas indicado:

Cmo mencionamos anteriormente en este artculo, adems de la tabla fsica en nuestro DW, se ha creado la definicin de nuestra dimensin y la dimensin en s dentro de nuestro proyecto de SSAS. Nuestro Data Source View ahora tiene la tabla Time:

Los atributos de nuestra flamante y nueva dimensin de tiempo se muestran a continuacin:

El asistente tambin nos ha creado una serie de jerarquas naturales. Estas son:

Despus de identificar los componentes bsicos de nuestra dimensin, ya estamos listos para explorar los resultados de nuestra nueva dimensin de tiempo pero no sin antes procesar la misma:

8. Explorando los datos de la nueva dimensin de tiempoFinalmente, ya podemos explorar los resultados de nuestra nueva dimensin de tiempo. Si se posicionan en la pestaa Browser, pueden seleccionar cada uno de los atributos de la dimensin o una de las jerarquas. En nuestro caso, seleccionamos la jerarquaYear Trimester Month - Ten Days Date:

Los resultados se muestran como esperbamos, de igual manera para la jerarquaYear Half Year Quarter Month Ten Days - Date:

Tambin tenemos jerarquas para el calendario fiscal comoFiscal Year Fiscal Half Year Fiscal Quarter Fiscal Month Fiscal Day:

9. Factores claves en el diseo de una dimensin de tiempoDe acuerdo a mi experiencia, mucho sucede que cuando construimos una dimensin de tiempo por nuestros propios medios, la navegacin de las jerarquas creadas no funciona como nosotros esperamos, con los miembros de datos asignados al padre incorrecto (por ejemplo una fecha dentro del mes incorrecto, o un trimestre dentro del ao incorrecto). Debido a esto, debemos tener claro que una de las claves de xito de cualquier dimensin de SSAS es ladefinicin de relaciones entre sus atributos. De esto depende que nuestras jerarquas funcionen correctamente y por otro lado tienen un gran impacto en los tiempos de respuesta cuando el usuario navegue sobre el cubo al que pertenezca la dimensin.

La dimensin de tiempo generada ha sido definida con las siguientes relaciones entre sus atributos:

He subrayado en color rojo los atributos de la jerarquaYear Half Year Quarter Month Ten Days Date, para profundizar un poco en los detalles de sus atributosClave PrimariayColumna a Mostrar. La propiedadClave Primaria(KeyColumns), define cmo SSAS va a diferenciar internamente a cada uno de los miembros del atributo, los cuales tienen que ser valores nicos. Debemos usar la propiedad Columna a Mostrar (Display Column), en el caso de querer mostrar una columna distinta a la usada en la clave primaria o cuando sta est compuesta de dos o ms columnas. Esto nos ayudar a evitar posibles errores de navegacin tanto de la dimensin de tiempo como de cualquier otra dimensin.

En el caso del atributoYear, ste define como suKeyColumna la columnaYearde nuestra tablaTime; y comoNameColumna la columna Year_Name:

Si exploramos los datos que contienen ests dos columnas vemos lo siguiente:

Hago nfasis nuevamente sobre la importancia de la propiedadKeyColumnde un atributo, el cual debe contener un valor nico para cada miembro de datos incluyendo a las jerarquas a donde est asignado. En el caso del atributoYear, no se tiene mayores problemas ya que no es posible confundir un ao con otro, ya que el valor del ao en s es su identificador nico.

En cambio, con un atributo comoHalf Year(semestre) deberamos tener un poco ms de cuidado, ya que si revisan su relacin con el atributoYearen la imagen delDimension Usage, esta es de varios-a-uno, es decir, varios (dos) semestres en un ao. Por tanto, en este contexto un semestre no vive por s solo, sino que le pertenece a un ao en especfico. Es decir, si nos piden el semestre 1, nos tienen que decir siempre el ao al que se estn refiriendo. Por ejemplo: semestre 1 del 2010, semestre 2 del 2010, etc.

Por tanto elKeyColumnparaHalfYeardebe de identificar nicamente a cada semestre dentro de un ao. En el caso de nuestra dimensin, las propiedadesKeyColumnyNameColumnestn definidas de la siguiente manera:

Los datos que almacenan dichas columnas se muestran a continuacin:

Noten, que la columnaHalf_Year definida comoKeyColumn, tiene sus valores incluyendo no slo el mes de inicio del semestre (01 enero - 07 - julio), sino tambin el ao al que pertenece (1950-01-01 y 1950-07-01 para los que se muestran en la imagen).

MUY IMPORTANTE:Si la columna Half_Year slo tuviera el semestre y no el ao (por ejemplo: Semestre 1, Semestre 2), habra la necesidad de definir un KeyColumn compuesto que no slo incluya la columna Half-Year sino tambin la columna Year, ya que ambas en su combinacin, haran nico a cada miembro de datos.

El comportamiento deHalf-Yeares similar al deTrimesteryMonthde nuestra jerarqua. Es decir, requiere que la columna que se use comoKeyColumn, incluya en su definicin, el ao al que pertenece y no solamente el nombre del periodo. En el caso del atributo Date, ste se comporta de manera similar aYear, ya que cada fecha por s misma es auto-suficiente para no ser confundida con otra fecha del calendario.

As se ve el atributoMonth(mes) de nuestra dimensin:

Ahora veamos sus propiedades:

Como vimos, la definicin deMonthes idntica a la deHalf-Year. Pero ahora veamos cmo se ven los datos del atributoMonth Of Year, el cul no pertenece a ninguna jerarqua sino como atributo independiente de nuestra dimensin de tiempo:

En este casoMonth Of Yearslo nos muestra los doce meses del ao sin indicar a qu ao pertenece. Este tipo de atributo es necesario, para cuando necesitemos crear algn informe que permita el anlisis comparativo de los mismos meses para distintos aos, como por ejemplo:

Month 1Month 2Month 3

2009

2010

2011

Debido a esto las propiedades del atributoMonth of Yearhan sido definidas como sigue:

Las columnasMonth_Of_Yeary elMonth_Of_Year_Namecontienen los siguientes valores:

Como se muestra en la ltima imagen, a diferencia de la columnaMonth,Month_Of_Yearno contiene el ao como parte de sus valores, solamente el periodo de tiempo al que pertenece, en este caso el mes. Esto permite que todos los meses de Enero (Month 1) de cualquier ao o trimestre, aparezca como si fuese uno solo. Lo mismo para todos los dems meses del ao.

10. El cierreComo resumir, podemos sealar lo siguiente:

SQL Server Analysis Services, nos puede ahorrar mucho el trabajo de crear una dimensin de tiempo para nuestra solucin OLAP incluso sin tener previamente la dimensin creada en nuestro Data Warehouse relacional.

Podemos crear una dimensin de tiempo y su tabla en nuestro Data Warehouse usando el asistente para la creacin de dimensiones de SSAS, y luego personalizar la misma para dejarla a nuestro gusto como borrar algn atributo o modificar alguna jerarqua.

Podemos optar tambin por crear nuestra dimensin de tiempo desde cero (por ejemplo si la queremos hacer en Espaol) siguiendo las mejores prcticas de acuerdo a como SSAS crea su dimensin de tiempo.

El asistente para la creacin de la dimensin de SSAS no soporta el idioma Espaol. Una forma para tener nuestra dimensin de tiempo en este idioma, es crearla por ejemplo en Ingls y modificarla para usar la capacin de Translations de SSAS.

A diferencia de una base de datos relacional en donde el centro de todo son las relaciones entre tablas. La definicin derelaciones entre atributoses la parte nuclear que define el comportamiento e influye en los tiempos de respuesta de nuestro cubo.

Otro aspecto clave para obtener el comportamiento deseado de nuestras jerarquas y atributos es la definicin de las claves. Debemos indicar en la propiedadKeyColumn, la columna que haga a nuestro atributo nico de acuerdo al contexto en donde se vaya a usar, ya sea como parte de una jerarqua o como atributo independiente.

Ya con nuestra nueva dimensin de tiempo, estamos listos para agregando las dems dimensiones a nuestra solucin OLAP y posteriormente el o los cubos que sean necesarios.