27 enero 2011

Creando una Dimensión de tiempo en SQL Server Analysis Services

Si queremos comenzar a crear un cubo para análisis de datos en SQL Server Analysis Services (SSAS) versiones 2005, 2008 y 2008 R2, en lo primero que debemos pensar es en la dimensión 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 dimensión de tiempo define además la granularidad en que nuestros datos en las tablas de hechos han sido generados, ya sea a nivel de año, semestre, trimestre, mes, día, hora, minuto, segundo, por nombrar algunas escalas. Si bien es cierto que a pesar de que nuestros datos en las tablas de hechos estén guardados a un nivel granular de tiempo en específico, por decir ventas a nivel de mes, ventas a nivel de día, etc.; es una buena práctica crear una dimensión de tiempo que incluya todos los niveles de granularidad que podrían usarse no sólo en las tablas de hechos (fact tables) que se vayan a crear ahora requieran, sino también las que se puedan tener a lo largo de la vida de nuestra solución. Por tanto es recomendable por lo menos crear una dimensión de tiempo con los niveles: año>semestre>trimestre>mes>día.

En otro caso, si la industria en la que estén trabajando lo requiere, se podrían considerar otros niveles de tiempo menores a día, como hora>minuto>segundo, pero la estrategia de implementación de ese nivel de granularidad puede ser muy distinta a la que vamos a ver hoy en este artículo para el caso de día como mínimo nivel granular.

Una de las facilidades que nos brinda SSAS con respecto a la dimensión de tiempo, es que él mismo la genere por nosotros incluso sin tener una tabla física 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 más adelante.

El propósito de este artículo es entender cómo SSAS nos facilita la vida en términos de generar nuestra propia dimensión de tiempo, que luego además podremos personalizar a nuestro gusto. El segundo propósito es que podamos aprender cómo SSAS hace el trabajo y entender su funcionamiento, de modo que nosotros podamos crear nuestra propia dimensión de tiempo desde cero si algo no nos gusta. ¡Comencemos!

1. Creando el Data Warehouse

Vamos a crear una nueva base de datos con nombre MiDataWarehouse en SQL Server 2008 R2, la cual será nuestro DataWarehouse ficticio sin tabla u otro objeto alguno:

image

2. Creando el proyecto de Analysis Services

Ahora crearemos un nuevo proyecto de SSAS en el Business Intelligence Development Studio (BIDS) de la versión de SQL Server 2008R2:

image

4. Creando el Data Source

Creamos un nuevo Data Source que apunte hacia nuestra base de datos MiDataWarehouse:

clip_image001

clip_image002

clip_image003

clip_image004

clip_image005

clip_image006

5. Creando el Data Source View

Ahora procedemos a crear nuestro Data Source View, en donde posteriormente SSAS generará la definición de nuestra dimensión de tiempo de manera automática:

clip_image001[4]

clip_image002[4]

clip_image003[4]

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

clip_image004[4]

clip_image005[4]

6. Creando la dimensión de tiempo

Ahora procedemos a crear nuestra dimensión de tiempo en SSAS:

clip_image006[4]

clip_image007

La pantalla que se muestra a continuación es clave, ya que nos permite indicar a SSAS, de qué forma generaremos nuestra nueva dimensión, en este caso la dimensión de tiempo:

clip_image008

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

  • Use an existing table: Se creará una nueva dimensión en nuestra base de datos OLAP (SSAS) basada en una tabla pre-existente de nuestro Data Source View (y por tanto en nuestro DW).
  • Generate a time table in the data source: Se creará una dimensión de tiempo en nuestro DW, su respectiva definición en el Data Source View y la dimensión de tiempo en nuestra base de datos OLAP.
  • Generate a time table on the server: Se creará la dimensión de tiempo en nuestra base de datos OLAP similar a la opción anterior. La posible desventaja de esta opción es que no nos creará nada en nuestro Data Source View que podamos modificar si así lo deseamos. Tampoco necesita de una dimensión existente en nuestro Data Warehouse.
  • Generate a non-time table in the data source: Se creará una dimensión distinta a una dimensión de tiempo en nuestro DW, su respectiva definición en el Data Source View y la dimensión correspondiente en nuestra base de datos OLAP.

Como se muestra en la imagen anterior, seleccionamos la opción Generate a time table in the data source para 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 dimensión de tiempo. De igual manera nos pide seleccionar cuál 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 opción de Half Year (semestre) o la de Ten Days (diez días) por mencionar alguna de las menos comunes. Finalmente, esta pantalla nos pide indicar cuál será el idioma en que se deben generar los datos de nuestra dimensión de tiempo y como se muestra a continuación, no incluye el idioma Español (lo cual es una deficiencia siendo el Español el segundo o tercer lenguaje más hablado del mundo).

Una de las formas superar esta deficiencia sería definiendo una traducción (dimension translation) para cada atributo. Pueden revisar más detalles de cómo hacer esto en “Defining and Browsing Translations” (http://technet.microsoft.com/en-us/library/ms166708(SQL.90).aspx). Nosotros seguiremos enfocados en cómo SSAS genera la dimensión de tiempo (aunque sea en Inglés) y las relaciones entre sus atributos.

clip_image009

La siguiente pantalla del asistente, pregunta por los tipos de calendario que queremos generar en nuestra dimensión. Los más usados son Regular calendar (calendario natural) y Fiscal calendar (calendario fiscal) que son las que seleccionaremos. En el caso del calendario fiscal, es posible indicar cuál será el día y el mes en que se inicia dicho calendario de acuerdo a nuestra organización, así mismo el nombre del año fiscal en comparación con el nombre del año calendario:

clip_image010

Esta es toda la información que SSAS necesita para generar nuestra dimensión de tiempo en el lado OLAP (SSAS). Ahora en la pantalla final que se muestra a continuación seleccionaremos el checkbox que dice Generate schema now para que en este mismo momento SSAS nos cree también la tabla física en nuestro Data Warehouse y su definición en nuestro Data Source View.

clip_image011

Ahora aparecerá un nuevo asistente que nos guiará a través del proceso de la generación del esquema físico y lógico que soportarán nuestra dimensión de tiempo, así como los datos (miembros) que contendrá:

clip_image012

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

clip_image013

Ahora vienen las preguntas con respecto a la creación física de la tabla sobre nuestro Data Warehouse incluyendo si queremos poblar de datos nuestra nueva tabla:

clip_image014

Una pantalla más antes de terminar y esta se refiere a la convención de nombres o estándares a usar en las columnas de la nueva tabla:

clip_image015

Una vez finalizado el asistente, se inicia el proceso de generación del esquema y de la tabla física, el cual puede ser examinado en detalle en la siguiente pantalla:

clip_image017

7. Analizando la estructura de la nueva dimensión de tiempo

Ya tenemos nuestro nuevo esquema y estamos listos para ver la magia detrás del espectáculo, comenzando por la nueva tabla dbo.Time creada en nuestra base de datos MiDataWareHouse.

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

clip_image019

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

clip_image021

Cómo mencionamos anteriormente en este artículo, además de la tabla física en nuestro DW, se ha creado la definición de nuestra dimensión y la dimensión en sí dentro de nuestro proyecto de SSAS. Nuestro Data Source View ahora tiene la tabla “Time”:

clip_image023

Los atributos de nuestra “flamante” y nueva dimensión de tiempo se muestran a continuación:

clip_image025

El asistente también nos ha creado una serie de jerarquías naturales. Estas son:

clip_image026

clip_image027

clip_image028

clip_image029

clip_image030

clip_image031

Después de identificar los componentes básicos de nuestra dimensión, ya estamos listos para explorar los resultados de nuestra nueva dimensión de tiempo pero no sin antes procesar la misma:

clip_image033

8. Explorando los datos de la nueva dimensión de tiempo

Finalmente, ya podemos explorar los resultados de nuestra nueva dimensión de tiempo. Si se posicionan en la pestaña “Browser”, pueden seleccionar cada uno de los atributos de la dimensión o una de las jerarquías. En nuestro caso, seleccionamos la jerarquía Year – Trimester – Month - Ten Days – Date:

clip_image035

Los resultados se muestran como esperábamos, de igual manera para la jerarquía Year – Half Year – Quarter – Month – Ten Days - Date:

clip_image036

También tenemos jerarquías para el calendario fiscal como Fiscal Year – Fiscal Half Year – Fiscal Quarter – Fiscal Month – Fiscal Day:

clip_image037

9. Factores claves en el diseño de una dimensión de tiempo

De acuerdo a mi experiencia, mucho sucede que cuando construimos una dimensión de tiempo por nuestros propios medios, la navegación de las jerarquías 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 año incorrecto). Debido a esto, debemos tener claro que una de las claves de éxito de cualquier dimensión de SSAS es la definición de relaciones entre sus atributos. De esto depende que nuestras jerarquías 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 dimensión.

La dimensión de tiempo generada ha sido definida con las siguientes relaciones entre sus atributos:

clip_image039

He subrayado en color rojo los atributos de la jerarquía Year – Half Year – Quarter – Month – Ten Days – Date, para profundizar un poco en los detalles de sus atributos Clave Primaria y Columna a Mostrar. La propiedad Clave Primaria (KeyColumns), define cómo 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 más columnas. Esto nos ayudará a evitar posibles errores de navegación tanto de la dimensión de tiempo como de cualquier otra dimensión.

En el caso del atributo Year, éste define como su KeyColumn a la columna Year de nuestra tabla Time; y como NameColumn a la columna Year_Name:

clip_image041

Si exploramos los datos que contienen estás dos columnas vemos lo siguiente:

clip_image043

Hago énfasis nuevamente sobre la importancia de la propiedad KeyColumn de un atributo, el cual debe contener un valor único para cada miembro de datos incluyendo a las jerarquías a donde esté asignado. En el caso del atributo Year, no se tiene mayores problemas ya que no es posible “confundir” un año con otro, ya que el valor del año en sí es su identificador único.

En cambio, con un atributo como Half Year (semestre) deberíamos tener un poco más de cuidado, ya que si revisan su relación con el atributo Year en la imagen del Dimension Usage, esta es de varios-a-uno, es decir, varios (dos) semestres en un año. Por tanto, en este contexto un semestre no vive por sí solo, sino que le pertenece a un año en específico. Es decir, si nos piden el semestre 1, nos tienen que decir siempre el año al que se están refiriendo. Por ejemplo: semestre 1 del 2010, semestre 2 del 2010, etc.

Por tanto el KeyColumn para HalfYear debe de identificar únicamente a cada semestre dentro de un año. En el caso de nuestra dimensión, las propiedades KeyColumn y NameColumn están definidas de la siguiente manera:

clip_image045

Los datos que almacenan dichas columnas se muestran a continuación:

clip_image046

Noten, que la columna Half_Year definida como KeyColumn, tiene sus valores incluyendo no sólo el mes de inicio del semestre (01 – enero - ó 07 - julio), sino también el año 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 sólo tuviera el semestre y no el año (por ejemplo: Semestre 1, Semestre 2), habría la necesidad de definir un KeyColumn compuesto que no sólo incluya la columna Half-Year sino también la columna Year, ya que ambas en su combinación, harían único a cada miembro de datos.

El comportamiento de Half-Year es similar al de Trimester y Month de nuestra jerarquía. Es decir, requiere que la columna que se use como KeyColumn, incluya en su definición, el año al que pertenece y no solamente el nombre del periodo. En el caso del atributo Date, éste se comporta de manera similar a Year, ya que cada fecha por sí misma es auto-suficiente para no ser confundida con otra fecha del calendario.

Así se ve el atributo Month (mes) de nuestra dimensión:

clip_image047

Ahora veamos sus propiedades:

clip_image048

clip_image049

Como vimos, la definición de Month es idéntica a la de Half-Year. Pero ahora veamos cómo se ven los datos del atributo Month Of Year, el cuál no pertenece a ninguna jerarquía sino como atributo independiente de nuestra dimensión de tiempo:

clip_image050

En este caso Month Of Year sólo nos muestra los doce meses del año sin indicar a qué año pertenece. Este tipo de atributo es necesario, para cuando necesitemos crear algún informe que permita el análisis comparativo de los mismos meses para distintos años, como por ejemplo:

 

Month 1

Month 2

Month 3…

2009

     

2010

     

2011…

     

Debido a esto las propiedades del atributo Month of Year han sido definidas como sigue:

clip_image051

Las columnas Month_Of_Year y el Month_Of_Year_Name contienen los siguientes valores:

clip_image053

Como se muestra en la última imagen, a diferencia de la columna Month, Month_Of_Year no contiene el año 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 año o trimestre, aparezca como si fuese uno solo. Lo mismo para todos los demás meses del año.

10. El cierre

Como resumir, podemos señalar lo siguiente:

  • SQL Server Analysis Services, nos puede ahorrar mucho el trabajo de crear una dimensión de tiempo para nuestra solución OLAP incluso sin tener previamente la dimensión creada en nuestro Data Warehouse relacional.
  • Podemos crear una dimensión de tiempo y su tabla en nuestro Data Warehouse usando el asistente para la creación de dimensiones de SSAS, y luego personalizar la misma para dejarla a nuestro gusto como borrar algún atributo o modificar alguna jerarquía.
  • Podemos optar también por crear nuestra dimensión de tiempo desde cero (por ejemplo si la queremos hacer en Español) siguiendo las mejores prácticas de acuerdo a como SSAS crea su dimensión de tiempo.
  • El asistente para la creación de la dimensión de SSAS no soporta el idioma Español. Una forma para tener nuestra dimensión de tiempo en este idioma, es crearla por ejemplo en Inglés y modificarla para usar la capación de Translations de SSAS.
  • A diferencia de una base de datos relacional en donde el centro de todo son las relaciones entre tablas. La definición de relaciones entre atributos es 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 jerarquías y atributos es la definición de las claves. Debemos indicar en la propiedad KeyColumn, la columna que haga a nuestro atributo único de acuerdo al contexto en donde se vaya a usar, ya sea como parte de una jerarquía o como atributo independiente.
  • Ya con nuestra nueva dimensión de tiempo, estamos listos para agregando las demás dimensiones a nuestra solución OLAP y posteriormente el o los cubos que sean necesarios.

Hasta la próxima,

Alan Koo

8 comentarios:

Mark Willium dijo...

Great article .......

SQL Recuperar

gequaire dijo...

Plain and simple! I like your work!

aroma therapy

Luchito Pucutay León dijo...

disculpe las molestias del caso, pero me urge hacerle una consulta porfavor!

Consulta: Imaginemos el escenario del poblamiento de dimensiones y hechos de un Datamart en el SSIS (Sql Server Integration Services) y teniendo en cuenta que las tablas de hechos son dependientes de los llenados de las dimensiones correspondientes..

¿las tablas de hechos son necesariamente poblados con origen de datos de las dimensiones llenadas? ó son llenados desde la base de datos transaccional.?

caso real: tengo dos tablas,



una de clientes
otra de ventas .
Clientes(miles) que si están registrados en venta pero ya fueron dados de baja en la tabla cliente y a la hora de realizar la relacion foranea entre estas 2 tablas pobladas no permite!! debido a que faltan registrar estos clientes dados de baja en la tabla cliente..todo esto porque se realizo un ETL desde la BD Transaccional...? aqui viene la pregunta inicial... ¿en la tabla de hechos, se trabajan con datos de las dimensiones??? ó se trabajan con datos de la BD transaccional de origen(Original)..???



Gracias por su Tiempo.

cabe dijo...

En la tabla de hechos si hay algun id de alguna dimension que en esta dimensión no exista ese registro en el cubo no te lo mostrara cuando lo proceses y lo uses. Entonces tiene que existir el cliente en la dimension cliente
Para que no ocurran errores en la dimension cliente no tienes que borrar los clientes si se borran en la transaccional
Saludos

Hernyk dijo...

buenas, quisiera saber si le puedo add un nuevo campo id q sea int en la tabla dimtiempo q acaba de generar el analisis services

Carlos F. dijo...

Hola tengo una tabla de hechos que tiene datos en tiempo y la relaciono con mi dimensión de tiempo, mi pregunta es si al realizar un reporte de mi cubo yo puedo filtrar los datos de tiempo de tal forma que solo me permita ver el año y los meses que existan en mi tabla de hechos. Mi pregunta se basa en que mi dimensión de tiempos tiene fechas que no existen en mi tabla de hechos y al realzar el reporte, en los filtros me muestra años y meses que no tienen datos en mi tabla de hechos.

Agradezco mucho tu ayuda.

Castillo dijo...

Tiene sentido crear una dimensión con su tabla de horarios, es decir con su dimensión TIME no DATE! sino TIME con horas:minutos:segundos?.
Imaginemos la gestión de llamadas por tiempos no solo por fechas.
Y si es que si tiene sentido, entiendo que los registros de esa dimensión sería tantos valores como 24 horas x 60 minutos x 60 segundos. ¿sería así?
GRACIAS

pedferdev dijo...

Hola esperemos me puedas ayudar. Digamos que creastes la dimension Tiempo hasta el anio 2014 como puedes hacer para que se aniadan mas fechas??, existe alguna configuracion de sqlServer o hay que aniadirlas manualmente.???

Gracias