¿Como buscar un miembro de dimensión para ser luego utilizado en un cálculo de MDX?

Question

Buenas.

Tengo el siguiente escenario:

Se trata de hacer un cubo para seguimiento de obras. Cada obra puede durar varios meses, (incluso varios años). Cada obra tiene un presupuesto de costes inicial, pero este prespuesto puede ir variando a lo largo del tiempo en función de dificultades encontradas, nuevos requerimientos del cliente final, etc. Cada nueva versión del presupuesto de costes puede ser aceptado o no. Además de los presupuestos, el cubo tiene tablas de hechos para recoger los costes reales, facturación, certificación, etc. pero estos últimos hechos no me plantean problemas. Mi problema son las versiones de los presupuestos de costes.

Intento explicarme mejor:

Las tablas tablas que tengo son (las tablas reales son mas complejas, pero creo que con esta simplificación se puede ver bien el problema):

 

- Dimension_Obras (SKObra, DescripciónObra)

- Dimension_Capitulos (SKCapitulo, DescripciónCapitulo)

- Dimension_VersionesPresupuestoCostes (SKVersion, FKObra, IdVersion, DescripcionVersion, FechaAprobacion)

- Fact_LineasPresupuestoCostes (IdLinea, FKObra, FKVersion, FKCapitulo, ImporteCostePresupuestado)

- Fact_LineasCostesReales (IdLinea, FKTiempo, FKObra, FKCapitulo, ImporteCosteReal)

La FechaAprobacion de la Dimension_VersionesPresupuestoCostes es un campo de tipo numérico que contiene una fecha en formato AAAAMMDD. Si la fecha de aprobación está en blanco significa que no está aprobado ese presupuesto por parte del cliente.

En uno de los informes de tabla dinámica se me pide algo parecido a esto:

Obviamente, en esta tabla dinámica pueden cambiar el filtro de tiempo según les convenga. 

Mi problema es:

 

- ¿Como hago para saber cual es la versión adecuada en función de la obra, del día ClosingPeriod del filtro de tiempo y de la FechaAprobación de las versiones?

Lo del ClosingPeriod lo tengo claro, pero no se como localizar el miembro adecuado de la dimensión versiones en función de FKObra = Dimension_Obras.Currentmember y de FechaAprobacion la mas cercana anterior o igual al día ClosingPeriod(Dim_Tiempo.currentmember)

Si pudiera localizar el SK de la versión adecuada, entonces podría crear un cálculo para que me saque los importes de presupuesto de costes en la columna del informe de tabla dinámica.

Answer 1

He conseguido una aproximación a la resolución total del problema. Os cuento.

He lanzado la siguiente query:

with member measures.ultimodia as

 (closingperiod([Dim_Tiempo].[Año - Semestre - Trimestre - Mes].[Id Fecha],[Dim_Tiempo].[Año - Semestre - Trimestre - Mes].currentmember).member_key

 )

select {[Measures].[ImporteCostePresupuestado]} on columns,

non empty ([DimensionObras].[Obra].children, 

           Filter([Dimension_VersionesPresupuestoCostes].[Version Presupuesto].[Version Presupuesto].AllMembers, 

                  ([Dimension_VersionesPresupuestoCostes].[Version Presupuesto].CurrentMember.Properties("FechaAprobacion")> "17530101") AND

                  ([Dimension_VersionesPresupuestoCostes].[Version Presupuesto].CurrentMember.Properties("FechaAprobacion")< ultimodia))

                 ) on rows

from [CUBO_OBRAS]

WHERE [Dim_Tiempo].[Año - Mes - Fecha].[Mes].&[200709]

 

La consulta de arriba es muy rápida (menos de un segundo con los datos reales que se llevarían a producción). Con esto consigo que me aparezcan esto (lo saco en Excel y lo he retocado para que se entienda mejor):

Como se ve, de la obra 001B6 solo me aparecen esas dos versiones, y eso es correcto, porque son dos versiones aprobadas cuya fecha de aprobación es anterior a la fecha final del filtro de tiempo que actualmente se está estudiando (en este caso Septiembre de 2007).

La cuestión es que debo afinar el FILTER porque lo que yo necesitaría realmente es que solo me apareciera la versión OV00003, porque es justo la última versión aprobada antes del 30 de Septiembre. 

He probado a utilizar la función MAX dentro del filter, con idea de que me devuelva la versión que tenga fecha de aprobación maxima, y creo que por ahí deben ir los tiros, pero no doy en el clavo final, porque o me devuelve una consulta nula, o bien aparece un mensaje de error diciendo que se ha quedado sin memoria.  

Mi problema está ahora mismo en que no soy capaz de combinar la función FILTER y la función MAX para que me devuelva el resultado adecuado.

¿Alguna orientación?

Answer 2

Al final lo he solucionado siguiendo una sugerencia del maestro Miguel Egea (gracias, Miguel).

Básicamente lo que he hecho es solucionar parte del problema en el ETL, y parte del problema en MDX. Intento explicarme:

- El en Data Warehouse hemos creado una tabla con tres campos: FKObra, FKTiempo, FKVersion. Esta tabla se rellena durante los procesos de ETL, y la idea es que para cada obra y cada día (el día es el nivel hoja de la dimensión Tiempo), se guardará en el campo (FKVersion)  el código de clave subrogada de la versión de presupuesto aprobada que corresponda. Esto se hace con Transact SQL con una sentencia INSERT con el crossjoin de FKObra y FKTiempo, y después el update del campo FKVersion con algunas clausulas WHERE, etc. Esto realmente no es excesivamente complejo. Esta tabla pasará a ser una tabla de hechos del cubo (con los campos FKObra y FKTiempo que son los campos que relacionan con las dimensiones de Obras y de Tiempo). El otro campo (FKVersion) en esta tabla lo trato como una medida.

 

- Por otro lado, en MDX creamos un cálculo (llamémosle  [GetVersionAprobada] que me devuelva la tupla (Obra.currentmember, Closingperiod(nivel-de-dia, dim-tiempo.currentember), FKVersion). Esto me devuelve un número que se corresponde con la SKVersion correcta para cada proyecto que haya en cellset en función  de la fecha última que haya puesta en el filtro de dimensión tiempo.

- Una vez que tenemos calculado el campo [GetVersionAprobada], el último paso es obtener el campo calculado [ImporteCostePresupuestado Aprobado] mediante la tupla ( [ImporteCostePresupuestado], strtomember([GetVersionAprobada]).

Espero que se entienda.

Seguro que todo esto se puede optimizar, pero eso será en otra fase de aquí a un par de meses, de momento me funciona y así se quedará.

Saludos.