SSIS - Paso a Paso Ejecutar query MDX OLAP

Que titulo tan enredado.. jeje  si eres novato debes leer primero sobre MDX ( lenguaje de consulta multidimensional, es decir, el equivalente de SQL para consultar tus cubos OLAP).... 

En fin, el cuento es este: tus cubos olap funcionan muy bien, y tu base de datos relacional también, el mundo es un paraíso, el sol brilla y los pájaros cantan; mientras tu admiras la infinita belleza del universo en una gota de agua por la ventana, tu jefe entra a tu oficina con una solicitud: debes leer las ventas promedio de los ultimos 3 meses del cubo OLAP y categorizar los productos deacuerdo a una tabla en la base de datos relacional y generar un archivo plano para otro sistema ....... silencio ..... genio pensando, te preguntas a ti mismo: osea que debo en un paquete SSIS leer del cubo y leer de la BD y cruzar estas dos fuentes de información??.... respuesta: si y ni siquiera sé como leer del cubo en un paquete SSIS...   jajjaja  cualquier parecido con la realidad, sera coincidencia ??

:: Aquí les dejo el paso a paso :::

1. Crea tu consulta MDX.  Ingresa al SQL Server Managment Studio, conectándote a tu analysis services y elije crear un nuevo query MDX; aquí vamos a crear la consulta según la estructura de nuestro cubo, en mi caso algo como esto:

WITH MEMBER [Measures].[Promedio] AS '

(

        ( [Tiempo].[Mes].[Mes_Diciembre_2009], [Measures].[Cantidad] )

+      ( [Tiempo].[Mes].[Mes_Noviembre_2009],[Measures].[Cantidad] )

+      ( [Tiempo].[Mes].[Mes_Octubre_2009],[Measures].[Cantidad] )

)/3 '

SELECT

         NON EMPTY { [Measures].[Promedio] }ON COLUMNS,

         NON EMPTY Crossjoin(

                  [Pais].[Region].MEMBERS, [Productos].[Categorias].ALLMEMBERS

                  )

                 ON ROWS

FROM [VentasCDA]

En esta consulta estoy creando un miembro llamado Promedio, definido como el promedio (pude haber usado la funcion AVG, no sé porque no lo hice); del último trimestre del 2009 (ya estamos en enero del 2010).  Y realizo un SELECT sobre el cubo de VentasCDA, donde las columnas será el promedio y las filas será el cruce entre las diferentes regiones del pais, y todos los productos.

Ejecútala y asegúrate que corre sin errores, una vez hecho, guardala que la usaremos más adelante.

2.  Crea tu paquete SSIS con lo básico. Crea un paquete con estas características:

- crea una variable llamada V_SQL_MDX de tipo String, deja su valor vacío.

-  crea una conexion OLE DB a tus cubos OLAP (base de datos multidimensional) a través de Microsoft OLE DB Provider for OLAP Services 8.0, llamada SERVER-OLAP que se verá como muestra la imagen a la izquierda.

3. Agrega al paquete un Script Task.  Dale doble clic para editarlo y en la sección de Script agrega como variable de escritura a V_SQL_MDX y vamos al código (presiona el botón Design Script).  Dado que dependiendo del mes actual, así mismo van a cambiar los nombres y año de los 3 últimos meses; entonces debo calcularlos.  Yo no sé si esta es la mejor manera de hacer esto, pero estaba cansada y fué lo primero que se me ocurrió, después lo optimizaré:

a.)  Declaro las variables que usaré

Dim agno As String, mes As Integer, fecha As Date

Dim mes_ult As Integer, mes_penu As Integer, mes_antep As Integer
Dim mesNombre_ult As String, mesNombre_penu As String, mesNombre_antep As String
Dim agno_ult As Integer, agno_penu As Integer, agno_antep As Integer

b.)  Obtengo la fecha de hoy, y defino cuales son los valores para los últimos 3 meses

fecha = Today()

agno = CStr(fecha.Year)
mes = fecha.Month
mes_ult = Month(DateAdd(DateInterval.Month, -1, fecha))
agno_ult = Year(DateAdd(DateInterval.Month, -1, fecha))
mes_penu = Month(DateAdd(DateInterval.Month, -2, fecha))
agno_penu = Year(DateAdd(DateInterval.Month, -2, fecha))
mes_antep = Month(DateAdd(DateInterval.Month, -3, fecha))
agno_antep = Year(DateAdd(DateInterval.Month, -3, fecha))


c.)  Defino el nombre de los meses

'Nombre del último mes
mesNombre_ult = CStr(Switch(mes_ult = 1, "Enero", _

                                                 mes_ult = 2, "Febrero", _
                                                 mes_ult = 3, "Marzo", _
                                                 mes_ult = 4, "Abril", _
                                                 mes_ult = 5, "Mayo", _
                                                 mes_ult = 6, "Junio", _
                                                 mes_ult = 7, "Julio", _
                                                 mes_ult = 8, "Agosto", _
                                                 mes_ult = 9, "Septiembre", _
                                                 mes_ult = 10, "Octubre", _
                                                 mes_ult = 11, "Noviembre", _
                                                 mes_ult = 12, "Diciembre"))
' Nombre del penúltimo mes
mesNombre_penu = CStr(Switch(mes_penu = 1, "Enero", _
                                                    mes_penu = 2, "Febrero", _
                                                    mes_penu = 3, "Marzo", _
                                                    mes_penu = 4, "Abril", _
                                                    mes_penu = 5, "Mayo", _
                                                    mes_penu = 6, "Junio", _
                                                    mes_penu = 7, "Julio", _
                                                    mes_penu = 8, "Agosto", _
                                                    mes_penu = 9, "Septiembre", _
                                                    mes_penu = 10, "Octubre", _
                                                    mes_penu = 11, "Noviembre", _
                                                    mes_penu = 12, "Diciembre"))
'Nombre del antepenúltimo mes

mesNombre_antep = CStr(Switch(mes_antep = 1, "Enero", _
                                                     mes_antep = 2, "Febrero", _
                                                     mes_antep = 3, "Marzo", _
                                                     mes_antep = 4, "Abril", _
                                                     mes_antep = 5, "Mayo", _
                                                     mes_antep = 6, "Junio", _
                                                     mes_antep = 7, "Julio", _
                                                     mes_antep = 8, "Agosto", _
                                                     mes_antep = 9, "Septiembre", _
                                                     mes_antep = 10, "Octubre", _
                                                     mes_antep = 11, "Noviembre", _
                                                     mes_antep = 12, "Diciembre"))


d.)  Le asigno a la variable el valor de la consulta MDX del punto 1, adaptado según los valores de los meses de los puntos b y c.

' Ventas promedio por producto por sucursal de los ultimos 3 meses
Dts.Variables("V_SQL_MDX ").Value = "WITH " & _
                                       "MEMBER [Measures].[Promedio] AS " & _
                                       "'(( " & _
                                       "[Tiempo].[Mes].[Mes_" & mes_ult & "_" & mesNombre_ult & "-" & agno_ult & "], " & _
                                       "[Measures].[Cantidad] " & _
                                       ")+( " & _
                                       "[Tiempo].[Mes].[Mes_" & mes_penu & "_" & mesNombre_penu & "-" & agno_penu & "]," & _
                                       "[Measures].[Cantidad] " & _
                                       ")+( " & _
                                       "[Tiempo].[Mes].[Mes_" & mes_antep & "_" & mesNombre_antep & "-" & agno_antep & "]," & _
                                       "[Measures].[Cantidad] " & _
                                       "))/3 '" & _
                                       "SELECT " & _
                                       "NON EMPTY { " & _
                                       "[Measures].[Promedio] " & _
                                       "} " & _
                                       "ON COLUMNS, " & _
                                       "NON EMPTY Crossjoin( " & _
                                       "[Pais].[Region].MEMBERS," & _
                                       "[Productos].[Categorias].ALLMEMBERS" & _
                                       ") " & _
                                       "ON ROWS " & _
                                       "FROM [VentasCDA] "


4. Agrega desde el Toolbox un componente Data Flow Task llámalo TAREA CONSULTAR CUBOS.

a.)  Ingresa a la pestaña data flow de este componente y agrega un OLE DB Source llamado OLEDB_LEER_CUBO, cuyo Connection Manager sea el creado en el punto 2 (SERVER-OLAP).  En la pestaña Component Properties, en la sección Sql Command pega la consulta creada en el punto 1, sí sin las variables tal cual, ya te explico porqué.....

Ve a la pestaña Column Mappings y dale refrescar, allí este componene debió haber leído las columnas que va a traer esta consulta y creó así mismo las columnas de salida.  Es por esto que debíamos pegar una consulta válida y que arroje resultados.  Ahora regresate a la pestaña Component Properties y en la sección Validate External Metadata asignale False, como esto va a ser leído de mi variable no quiero que cada vez que abra el paquete me refresque la metadata .  Dale OK y sal de este componente.

b.)  Regresa a la pestaña de Control Flow, dá clic derecho sobre el componente TAREA CONSULTAR CUBOS y elige properties, busca la sección Expressions, y dá clic en el botón con los puntos suspensivos...

Te aparecerá una ventana donde puedes configurar los componentes que están dentro de esta tarea.  En Property elige [OLEDB_LEER_CUBO].[SqlCommand] y en Expression elige @[User::V_SQL_MDX].  De esta forma a la conexion hacia nuestro cubo le estamos asignando como comando sql, el valor de la variable.  La imagen tiene otros nombres que fueron los que yo usé en mi paquete.

c.)  Vuelve a ingresar a TAREA CONSULTAR CUBOS, crea otro OLEDB Source que se conecte a tu base de datos relacional y extraiga los productos y sus correspondientes categorías, ya con estas dos fuentes OLEDB si puedes hacer las transformaciones que quieras.

d.)  Puedes unir los dos flujos de datos a través de un componente Merge join (antes debes ordenar los dos flujos de datos por medio de componentes Sort ordenando por el código del producto), luego conecta las flechas de ambos flujos hasta el Merge Join, algo similar a la imagen.

Ingresa al componente Merge Join y asegúrate que se están uniendo por el campo correcto.

.....Después de hacer todo esto, por fin pude seguir admirando la gota de lluvia sobre mi ventana....  ;)