Manual de MDX

Manual de MDX
Meritxell Thu, 09/25/2014 - 11:58

Cubo OLAP y MDX

Este es un tutorial online sobre el lenguaje de consulta multidimensional MDX.

MDX (Multi-Dimensional eXpressions) es un lenguaje de consulta usado para sacar datos de una base de datos multidimensional. Más concretamente, MDX se usa para consultar datos de una base de datos OLAP con Analysis Services y otras herramientas que soporten este lenguaje.

MDX ayuda a crear Reports con más facilidad que con SQL, ya que con SQL tenemos que ir especificando todas las relaciones de las tablas para poder sacar un dato en concreto, en cambio, en MDX ya esta todo relacionado a priori.

En las diferentes secciones y páginas que componen este manual de MDX aprenderás qué es este lenguaje de consulta tan utilizado en entornos OLAP, en qué se diferencia con el lenguaje SQL para consultas de bases de datos, y cómo se utiliza MDX.

Se enseña cómo son las Queries de MDX, y cuáles son sus principales funciones, expresiones y operadores, cómo trabajar con miembros de las jerarquías de los cubos, con miembros calculados, o cómo gestionar cubos con el lenguaje MDX modificando miembres, creando nuevos miembros de cubos, trasladando miembros de una dimensión a otra, o eliminando miembros.

 

1. Introducción al lenguaje de consulta MDX

1. Introducción al lenguaje de consulta MDX Meritxell Mon, 09/29/2014 - 11:40

MDX (Multi-Dimensional expressions) es un lenguaje de consulta usado para sacar datos de una base de datos multidimensional. Más concretamente, MDX se usa para consultar datos de una base de datos OLAP con Analysis Services y otras herramientas que soporten este lenguaje.

MDX ayuda a crear Reports con más facilidad que con SQL, ya que con SQL tenemos que ir especificando todas las relaciones de las tablas para poder sacar un dato en concreto. En cambio, en MDX ya esta todo relacionado a priori.

Comparación entre SQL y MDX:

  • SQL Relacional / MDX Multidimensional

  • SQL Debes especificar las relaciones (Join) / MDX No se especifican las relaciones
    En SQL para poder sacar informacion relacionada entre dos o más tablas tenemos que especificar los campos que las relacionan, en cambio en MDX no es necesario, porque cuando se genera el cubo define cuáles serán los metadatos y por tanto las jerarquías y sus relaciones.

  • SQL No posicional / MDX Posicional
    SQL no es posicional. Esto significa que en SQL no existe el concepto de record anterior y record posterior, sino que tengo un conjunto de datos y basta. Por tanto, no existe ningún dato agregado ni calculado sino que usamos las funciones de agregación Group By, y SUM en el momento que queremos sacar qualquier dato agregado de una manera dinámica.
    En cambio MDX es posicional, ya que las agregaciones ya estan calculadas y solo se tiene que especificar en que posición se encuentra el dato que se quiere sacar.
    Ejemplo:
    Si hacemos una consulta del facturado del mes de enero 2006, con MDX yo puedo sacar la facturación del mes anterior y posterior con el prevmember o el nextmember o los hijos del año 2005 y dará el facturado de todos los meses del 2005. De esta manera podemos ver la tendencia que tiene el facturado desde el 2005 hasta enero del 2006.

  • SQL no Referenciado / MDX Referenciado
    SQL no es referenciado, ya que como hemos dicho antes cada vez que queremos sacar un dato tenemos que hacer todas las JOIN y todos los cálculos necesarios dinámicamente, ya que no hay nada precalculado.
    En cambio MDX tiene referenciados los datos y no le hace falta usar las funciones de agregado ya que cada cálculo esta guardado en una celda y solo se tiene que especificar la referencia a ella.
    Un ejemplo que explicaría este concepto es: si queremos sacar el facturado del año 2005 no tengo que sumar todos los meses sino que pongo la referencia a la celda donde está ya guardado ese dato.

 

Conceptos Básicos

Conceptos Básicos Meritxell Mon, 09/29/2014 - 10:57

El cubo es el fundador de la base de datos multidimensional. Cada cubo contiene más de dos dimensiones.

Dimensiones, niveles y medidas

Las dimensiones son atributos estructurales del cubo y más especificamente, son jerarquías organizadas en niveles que describen los datos en la tabla de hechos.

Un nivel es un elemento de la jerarquía de una dimensión, que describe la jerarquía de los datos. Estos niveles dentro de una jerarquía van del nivel superior al nivel inferior (detalle). Los niveles solo existen al interno de las dimensiones y nos ayudan a especificar el contenido y la estructura de la jerarquía de las dimensiones.

La medida es un conjunto de valores basados en una columna de la tabla de hechos del cubo. Normalmente es de tipo numérico. Por tanto, son los valores del cubo de los que haremos el analisis.

Las medidas al interno del MDX son tratadas como una dimensión no con jerarquías sino plana.

En la figura siguiente se muestra como se representaría un cubo. Cada celda del cubo tiene unas coordenadas multidimensionales.
 

Cubo dimnsional

 

La nomenclatura para llamar a una celda es:

➢ Poner un miembro de cada dimensión
➢ Las dimensiones se separan por comas (,)
➢ El nombre de la celda va entre ()
➢ El nombre de cada dimensión [] (no obligatorios)
 

Ejemplo:

([Mesures].[OrderCount], [Date]. [CalendarYear].&[2004])

El símbolo & se pone para referirnos a un miembro clave (Key Member) si no lo ponemos nos referimos al nombre del miembro (MemberName).

NOTA: Si no se especifica ninguna dimensión usará el miembro corriente y si no existe ningun miembro seleccionado usará el miembro por defecto.

Miembros

Miembros Meritxell Thu, 10/02/2014 - 12:14

Cada jerarquía de una dimensión contiene uno o más items que son denominados Miembros. Cada miembro corresponde a un valor dentro de la tabla dimensión.

En esta imagen podemos ver unas cuantas dimensiones con sus correspondientes miembros organizados por jerarquías.

 

Dimensiones y jerarquías en MDX

 

Usando la nomenclatura adecuada se puedes acceder a cualquier miembro de cualquier dimensión, especificando:

➢ El nombre de la dimensión
➢ El nombre de la jerarquía
➢ El nombre del nivel

 

Por ejemplo, si se quiere acceder al miembro Q1 CY 2004 en de la jerarquía Calendar se representa de la siguiente manera:

[Date].[Calendar].[Calendar Quarter].[Q1 CY 2004]

Como podemos ver los corchetes [] se usan para delimitar los nombres de las dimensiones, las jerarquía, los niveles y los miembros. Los corchetes no son obligatorios, pero si el nombre de la dimensión, jerarquía nivel o miembro tienen algún espacio si que serán obligatorios.

Las siguientes tres represantaciones para acceder al miembro Q1 CY 2004, son válidas:

[Date].[Calendar].[Q1 CY 2004] (1)
[Date].[Calendar].[CY 2004].[H1 CY 2004].[Q1 CY 2004] (2)
[Date].[Calendar].[Calendar Quarter].&[2004]&[1] (3)

La primera representación del miembro está representada en el formato:

Dimension.Jerarquía.MemberName

En la segunda representación hace un recorrido por todos los miembros hasta llegar al que se quiere.
La última representación hace un recorrido usando las llaves de los miembros.

Como hemos dicho antes el símbolo & nos indica que nos estamos refiriendo a la llave del miembro.
 

Ejemplo:

Si nosotros queremos el miembro con nombre Ramon y con clave &1 me dará exactamente ese miembro, si por el contrario solo ponemos el nombre del miembro, Ramon, me sacará todos los miembros que se llaman Ramon, ya que Ramon no es una clave primaria.

Ejemplo con cubo Analysis DW: queremos coger de la dimension cliente, el cliente Ramon Sanchez Perez.

[Dim Cliente].[Nombre Cliente].[Ramon Sanchez Perez]
[Dim Cliente].[Zonas Clientes].[Barcelones].[Ramon Sanchez Perez]
[Dim Cliente].[Zonas Clientes].[Nombre Cliente].&[1]

 

En resumen, para poder acceder a un miembro debes usar el siguiente formato:

[DimensionName].[HierarchyName].[LevelName].[MemberName]

 

2. Queries en MDX.

2. Queries en MDX. Meritxell Mon, 09/29/2014 - 11:22

En MDX una query tiene tres partes:

➢ Columnas y Filas (Dicers o Axis)
➢ Dato (Data Grid)
➢ Filtro (Slicers)

La sintaxis es la siguiente:

[WITH <formula_expression> [, <formula_expression> ...]]
SELECT [<axis_expression>, [<axis_expression>...]]
FROM [<cube_expression>]
[WHERE [slicer_expression]]

Puedes ver que las clausulas WITH y WHERE son opcionales, porque estan indicadas con [].

Por tanto, la query mas simple que se puede hacer es:

SELECT
FROM [Adventure Works]

Para indicar los axis o ejes, se debe especificar el conjunto de datos que quieres sacar y si lo quieres sacar en las columnas o en las filas.

Por tanto:

Select <Set> on columns, <Set> on rows
From <Cubo>
Where <Filtro>

La palabra Columns es equivalente a 0 y la palabra rows equivalente a 1. Por tanto, se puede escribir la query de la siguiente manera:

Select <Set> on 0, <Set> on 1
From <Cubo>
Where <Filtro>

Por tanto en la Query tenemos las siguientes cláusulas:

➢ La clausula SELECT es donde se especifican los datos que se necesitan visualizar en cada eje (Columnas, Filas).

➢ La clausula FROM usada para especificar el nombre del cubo donde esta guardada la información que se quiere sacar.

➢ La clausula WHERE usada para filtrar una pequeña sección de datos de los cuales quieres sacar los resultados.

➢ La cláusula WITH sirve para crear calculos o conjuntos que se usaran en las queries sucesivas a esta cláusula. Esta cláusula se usa normalmente para definir y crear miembros calculados y conjuntos con nombre. Más adelante se explicará con más detalle.

 

Ejemplos de queries básicas con el cubo Analysis DW

select [Measures].members on columns
from [Analysis DW]

Resultado:

Queries básicas con el cubo Analysis DW:

 

select [Measures].[Cantidad Pedida] on columns
from [Analysis DW]
where ([Dim Periodo].[Año].&[2006],
       [Dim Producto].[Categoria - Nombre].[Software],
       [Zona Pedido].[Nombre Zona].&[Barcelones])

Resultado:

Queries básicas con el cubo Analysis DW:

 

select [Measures].[Importe Pedido] on columns,
       [Dim Cliente].[Zonas Clientes].[Todos Clientes].&[1] on rows
from [Analysis DW]
where [Zona Pedido].[Nombre Zona].&[Barcelones]

Resultado:

Queries básicas con el cubo Analysis DW

 

select [Measures].[Importe Pedido] on columns,
       [Dim Cliente].[Zonas Clientes].members on rows
from [Analysis DW]
where [Zona Pedido].[Nombre Zona].&[Barcelones]

Resultado:

select [Measures].[Importe Pedido] on columns,
       non empty([Dim Cliente].[Zonas Clientes].members) on rows
from [Analysis DW]
where [Zona Pedido].[Nombre Zona].&[Barcelones]

Resultado:

Queries básicas con el cubo Analysis DW
 

3. Fórmulas y funciones en MDX

3. Fórmulas y funciones en MDX Meritxell Mon, 09/29/2014 - 11:28

Conjunto con nombre (Named Sets)

Conjunto con nombre (Named Sets) Meritxell Mon, 09/29/2014 - 11:35

Un conjunto o Set es un conjunto de celdas de la misma dimensionalidad, es decir, que en un mismo conjunto podemos tener más de una celda, pero todas tienen que pertenecer a la misma dimensión.

Un conjunto se puede definir a priori asignándole un nombre y así cada vez que se quiera hacer referencia a ese conjunto no se tendra que especificar cada celda sino el nombre de ese conjunto previamente definido. A esto se le llama Conjunto con Nombre o Named Sets.

La expresión para crear un conjunto con nombre es:

SET <set_alias_name> AS [']<set>[']

Cada conjunto se deberá delimitar entre {}. Si hay uno solo no es necesario, pero si por el contrario hay más de uno se deberán poner.

Ejemplo de Conjunto con nombre con el cubo Analysis DW

Query usando un conjunto sin nombre

Select [Measures].[Importe Pedido] on columns,
{[Dim Cliente].[Zonas Clientes].[Zona].&[1], [Dim Cliente].[Zonas Clientes].[Zona].&[2]} on rows
from [Analysis DW]

 

Resultado:

Conjunto sin nombre

 

La misma query usando un conjunto con nombre y por tanto usando la clausula with y así una vez definido solo poniendo el nombre del conjunto ya lo reconoce

with set [Provincia Barcelona] as '{[Dim Cliente].[Zonas Clientes].[Zona].&[1], [Dim Cliente].[Zonas Clientes].[Zona].&[2]}'

Select [Measures].[Importe Pedido] on columns,
[Provincia Barcelona] on rows
from [Analysis DW]

 

Resultado:

Conjunto sin nombre

Creación de un conjunto con nombre con SSAS

La configuración queda de la siguiente manera para el ejemplo de hacer un conjunto con los 5 mejores clientes con respecto al importe de pedido:

 Conjunto con nombre con SSAS

El problema es que no se puede visualizar dentro del browser del SSAS, sino que se tiene que visualizar haciendo una Query como esta:

select [Top Five] on rows, [Measures].[Importe Pedido] on columns
from [Analysis DW]

 

Resultado:

Visualización con una Query

 

Miembros Calculados

Miembros Calculados Dataprix Mon, 09/29/2014 - 15:27

Los miembros Calculados son cálculos hechos con expresiones MDX. Un ejemplo típico de miembro calculado es el calculo Year-To-Date (YTD) de las ventas de los productos. Esto puede ser especificado por una expresión MDX usando la cláusula WITH. Por tanto, se podrán devolver las ventas no sólo de cada mes sino también las ventas de los meses desde inicio de año hasta el mes corriente.

La fórmula de la clausula WITH para crear un miembro calculado es:

Formula_expression := MEMBER <MemberName> AS [‘]<MDX_Expression>[‘],
[ , SOLVE_ORDER = <integer>]
[ , <CellProperty> = <PropertyExpression>]

 

El Solve_Order es un parámetro opcional el valor del cual debe ser un entero positivo. Este parámetro determina el orden en que deben ser evaluados los miembros siempre y cuando se hayan definido más de un miembro dentro de la clausula WITH.

El CellProperty es también un parámetro opcional que se usa para especificar las propiedades de la celda para el miembro calculado, como por ejemplo el formato del texto de la celda, incluído el color de fondo.

 

Los miembros calculados pueden ser utilizados como medidas o como miembros. Por tanto, si creamos un miembro tenemos que especificar dónde se quiere guardar su definición, si en la dimensión Measures y por tanto será una medida, o por el contrario en cualquier otra dimensión, que entonces será un miembro.

Es importante saber que de un miembro calculado sólo se guarda su definición, y es en el momento de su utilización cuando se calcula. Por tanto, esto hace que no se incremente la dimensión del cubo. También se debe saber que los miembros calculados se deben basar en datos existentes en el cubo.

Para crear estos miembros MDX dispone de una gama de funciones que ofrecen una extrema flexibilidad a la hora de manipular los datos multidimensionales.

Ejemplo de Miembros calculados con el cubo AnalysisDW

With Member [Measures].[PorcentajePedidos] as
          '([Measures].[ImportePedido]/[Measures].[Cantidad Pedida])'

Select [Measures].[PorcentajePedidos] on columns,
       [Dim Cliente].[Zonas Clientes].members on rows
from [Analysis DW]

 

Resultado:

Miembros calculados con el culbo AnalysisDW

Combinación de Miembros calculados y conjuntos con nombres dentro de una misma clausula WITH

with set [Provincia Barcelona] as
       '{[Dim Cliente].[Zonas Clientes].[Zona].&[1],    
         [Dim Cliente].[Zonas Clientes].[Zona].&[2]}'
     Member [Measures].[PorcentajePedidos] as
       '([Measures].[Importe Pedido]/[Measures].[Cantidad Pedida])'

Select [Measures].[PorcentajePedidos] on columns,
[Provincia Barcelona] on rows
from [Analysis DW]

Resultado:

Combinación de Miembros calculados y conjuntos con nombres dentro de una misma clausula WITH

 

Ejemplo de creación de miembros calculados MDX con SSAS

Vamos a añadir un miembro dentro de una dimensión y no como una medida. El miembro lo añadiremos en el año de la dimensión dim_periodo y calculará el sumatorio de los años 2005 y 2006.

La configuracion para crear un miembro calculado con una expresión SSAS es:

Añadir un miembro dentro de una dimension

 

Resultado:

Añadir un miembro dentro de una dimension resultado

 

Expresiones MDX

Expresiones MDX Meritxell Mon, 09/29/2014 - 15:33

Las expresiones MDX sirven para evaluar un valor. Son usadas en cálculos o para definir valores de objetos como default member o default measures o para definir expresiones de seguridad que permitiran o denegaran el acceso a los datos.

Las expresiones MDX normalmente toman un miembro, una tupla o un conjunto como parámetro y devuelven un valor. Si el resultado de evaluar la expresión MDX no tiene valor devuelve NULL.

A continuación se muestran unos cuantos ejemplos:

Este ejemplo devuelve el valor por defecto de la jerarquía Zonas Clientes de la Dimension:

[Dim Cliente].[Zonas Clientes].DEFAULTMEMBER

Para ver el resultado lo sacaremos en forma de query:

select [Dim Cliente].[Zonas Clientes].DEFAULTMEMBER on columns
from [Analysis DW]

 

Resultado:

valor por defecto de la jerarquía Zonas Clientes de la Dimension

Este ejemplo devuelve para cada Zona de cliente la diferencia de importe que hay respecto a la zona de Barcelona:

([Dim Cliente].[Zonas Clientes].CURRENTMEMBER, [Measures].[Importe Pedido]) -
([Dim Cliente].[Zonas Clientes].[Zona].&[1], [Measures].[Importe Pedido])

Para ver el resultado lo escribimos en forma de miembro calculado dentro de la clausula WITH:

With Member [Measures].[Diferencia] as
  '([Dim Cliente].[Zonas Clientes].CURRENTMEMBER,[Measures].[Importe Pedido]) -
   ([Dim Cliente].[Zonas Clientes].[Zona].&[1], [Measures].[Importe Pedido])'

select [Measures].[Diferencia] on columns,
       [Dim Cliente].[Zonas Clientes].[Zona] on rows
from [Analysis DW]

 

Resultado de la expresión MDX

valor por defecto de la jerarquía Zonas Clientes de la Dimension, resultado

 

Navegación

Navegación Meritxell Mon, 09/29/2014 - 15:53

CurrentMember

CurrentMember Meritxell Thu, 10/02/2014 - 12:18

La función MDX currentmember devuelve el miembro corriente. Esto significa que devolverá el miembro que en ese momento esta seleccionado. Si no existe ningún miembro seleccionado se cogerá el miembro de default, que normalente es ALL. Por tanto, si queremos sacar una medida y no especificamos ninguna dimension siempre nos sacará el miembro de default.

Esta función incluye otra llamada name que nos informa del nombre del miembro corriente.

Un ejemplo de esta funcion usando una Query seria:

select [Dim Cliente].[Zonas Clientes].currentmember on columns
from [Analysis DW]

 

Resultado:

Llamada name

Como podemos ver, el miembro corriente de la dimension cliente es el miembro ALL llamado en este caso Todos los Clientes., que es el miembro por defecto.
 

DefaultMember

DefaultMember Meritxell Thu, 10/02/2014 - 12:18

La función DefaultMember devuelve el miembro por defecto.

Para seguir con el ejemplo anterior, si queremos saber el miembro por defecto de la dimension Cliente haremos lo siguiente:

select [Dim Cliente].[Zonas Clientes].DEFAULTMEMBER on columns
from [Analysis DW]

 

Resultado:

Llamada name

Como podemos ver, es el mismo resultado ya que en este caso el miembro corriente y el miembro de default es el mismo. 

Children

Children Meritxell Thu, 10/02/2014 - 12:19

La función Children de MDX saca a partir de un miembro de una jerarquía todos sus hijos.

Ejemplo:

Se quiere sacar el importe de los pedidos de los hijos de la zona de Barcelona.

select [Dim Cliente].[Zonas Clientes].[Zona].&[1].CHILDREN on rows,
       [Measures].[Importe Pedido] on columns
from [Analysis DW]

 

Resultado:

 

PrevMember

PrevMember Meritxell Thu, 10/02/2014 - 12:21

La función PrevMember de MDX nos devuelve el miembro anterior al seleccionado o CurrentMember.

Ejemplo:

Queremos saber el importe de los pedidos del año 2006 y de su miembro anterior, es decir, del 2005.

Select {([Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].[Año].&[2006],
         [Measures].[Importe Pedido] ),
        ([Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].[Año].&[2006].prevmember,          [Measures].[Importe Pedido])} on columns
from [Analysis DW]

 

Resultado:

 

 

Ascendants

Ascendants Meritxell Thu, 10/02/2014 - 12:23

La función Ascendants de MDX devuelve el conjunto de antecesores de un miembro.

Ejemplo:

Query que devuelve el importe de pedidos por cada uno de los antecesores del mes de marzo del 2006

select ASCENDANTS([Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].
                                [Año].&[2006].&[1.].&[3.]) on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:

Función Ascendans

 

Descendants

Descendants Meritxell Thu, 10/02/2014 - 12:24

La función Descendants de MDX devuelve el conjunto de descendientes de un miembro.

Sintaxis:

DESCENDANTS (Miembro, nivel)

Ejemplo:

Importe de los descendientes del año 2006 a nivel de mes, sabiendo que la jeraquia es año-trimestre-mes-fecha

select descendants ([Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].
                                  [Año].&[2006],2) on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:

Función descendants

 

Si lo hacemos a nivel de trimestre será:

select descendants ([Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].
                    [Año].&[2006],1) on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:

Función Decendants

 

Operadores

Operadores Meritxell Thu, 10/02/2014 - 10:54

Operadores Aritméticos y de conjunto

Operadores Aritméticos y de conjunto Meritxell Thu, 10/02/2014 - 11:18

Operadores Aritméticos y de Conjunto

Los operadores aritméticos son los típicos: +, - , * y /

Tanto el operador + como el – y el *, también son operadores de Conjunto.

La + y la - hacen la unión y la diferencia entre dos conjuntos respectivamente, pero el operador * hace el producto entre dos, pero lo hace para todas las posibles combinaciones entre los conjuntos, es decir,

Si por ejemplo, tienes un conjunto {Barcelona, Girona} y otro {2003, 2004, 2005} y quieres hacer el producto se representará de la siguiente manera:

{Barcelona, Girona} * {2003, 2004, 2005}

Y el comportamiento de este operador será:

{(Barcelona, 2003), (Barcelona, 2004), (Barcelona, 2005), (Girona, 2003), (Girona, 2004), (Girona, 2005)}

Ejemplos de operadores aritméticos:

Operador +

Se quiere unir la ciudad Barcelona con la ciudad Girona usando el operador + la expresión MDX sería:

[Dim Cliente].[Ciudad].&[Barcelona] + {[Dim Cliente].[Ciudad].&[Girona]

El resultado de esta expresión es:

{[Dim Cliente].[Ciudad].&[Barcelona],[Dim Cliente].[Ciudad].&[Girona]}

Para poder ver como se usaría este operador en una Query usamos un conjunto con nombre dentro de la clausula WITH:

with set [Barcelona-Girona] as '{[Dim Cliente].[Ciudad].&[Barcelona]} +
                                {[Dim Cliente].[Ciudad].&[Girona]}'

select [Measures].[Importe Pedido] on columns,
       [Barcelona-Girona] on rows
from [Analysis DW]

 

   

 

Operador *

Se quiere hacer una combinación entre dos conjuntos, por ejemplo, las zonas Barcelona y Girona con las categorías de producto hardware y software:

{[Zona Pedido].[Nombre Zona].&[Barcelones], [Zona Pedido].[Nombre Zona].&[Girones]} *
{[Dim Producto].[Categoria Producto].[Categoria].&[1],[Dim Producto].[Categoria Producto].[Categoria].&[2]}

El resultado de esta expresión sería:

{  ([Zona Pedido].[Nombre Zona].&[Barcelones], [Dim Producto].[Categoria Producto].[Categoria].&[1]),
   ([Zona Pedido].[Nombre Zona].&[Barcelones], [Dim Producto].[Categoria Producto].[Categoria].&[2]),
   ([Zona Pedido].[Nombre Zona].&[Girones], [Dim Producto].[Categoria Producto].[Categoria].&[1]),
   ([Zona Pedido].[Nombre Zona].&[Girones], [Dim Producto].[Categoria Producto].[Categoria].&[2]) }

 

Para poder ver cómo se usaría este operador en una Query usamos un conjunto con nombre dentro de la clausula WITH:

with set [zonas-Productos] as
'{[Zona Pedido].[Nombre Zona].&[Barcelones],
  [Zona Pedido].[Nombre Zona].&[Girones]} *
 {[Dim Producto].[Categoria Producto].[Categoria].&[1],
  [Dim Producto].[Categoria Producto].[Categoria].&[2]}'

select [zonas-Productos] on columns
from [Analysis DW]

 

Resultado:

 

En este resultado podemos observar cómo se han hecho todas las posibles combinaciones. Si le ponemos la medida importe veremos cómo por cada combinación, en este caso zona-pedido, se calcula el importe correspondiente.

Para ello, la Query sería:

with set [zonas-Productos] as
  '{[Zona Pedido].[Nombre Zona].&[Barcelones],
    [Zona Pedido].[Nombre Zona].&[Girones]} *
   {[Dim Producto].[Categoria Producto].[Categoria].&[1],
    [Dim Producto].[Categoria Producto].[Categoria].&[2]}'

select [zonas-Productos] on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:

 

Operadores de Comparación

Operadores de Comparación Meritxell Thu, 10/02/2014 - 11:16

Los operadores de comparación de MDX son los habituales: <, <=, >, >=, =, y <>.

Estos operadores comparan dos expresiones MDX y devuelven TRUE o FALSE.

Un ejemplo sería:

Count ([Dim Cliente].[Zonas Clientes].members) > 3

Esto nos diría si el numero de miembros de esta jerarquía es mayor que 3 o no.

 

Operadores lógicos

Operadores lógicos Meritxell Thu, 10/02/2014 - 11:19

Los operadores lógicos en MDX son: AND, OR, XOR, NOT, and IS.

Estos operadores usan dos expresiones MDX como argumentos y devuelven TRUE o FALSE.

 

Operadores Especiales MDX

Operadores Especiales MDX Meritxell Thu, 10/02/2014 - 11:22

Los operadores especiales de MDX son: '{}', comas y ':'

El operador {} permite encapsular una celda o un conjunto de celdas. Si es una única celda el operador es opcional, porque MDX lo pone por defecto.

El operador ‘,’ se usa para separar los diferentes miembros dentro de un conjunto.

Por ejemplo:

{(Barcelona, 2003), (Barcelona, 2004), (Gerona, 2003), (Gerona, 2004)}

El operador ':' se usa para definir rangos. Por tanto, cuando pongamos dos miembros no consecutivos de un conjunto separados por este operador significa que cogera todos los miembros desde el miembro antes del operador hasta el miembro de después del operador.

Ejemplo:

{[Dim Cliente].[Ciudad].&[Barcelona]:[Dim Cliente].[Ciudad].&[Vilafranca]}

Esto nos daria como resultado todas las ciudades que hay entre Barcelona y Vilafranca (ambas incluídas) dentro del cubo.

Para comprobarlo hacemos una Query:

with set [Ciudades] as
  '{[Dim Cliente].[Ciudad].&[Barcelona]:[Dim Cliente].[Ciudad].&[Vilafranca]}'

select ciudades on columns
from [Analysis DW]

 

Resultado:


 

 

Funciones más comunes de MDX

Funciones más comunes de MDX Meritxell Thu, 10/02/2014 - 11:23

IIF

IIF Meritxell Thu, 10/02/2014 - 11:34

Sintaxis de la función MDX IIF:

IIF (<<Logical Expression>>, <<String Expression1>>, <<String Expression2>>)

Esta función equivale al clásico condicional 'IF' y te permite dependiendo del valor que devuelve la expresión lógica hacer una cosa u otra.

Por tanto IIF sería como

Si Expresión Lógica = TRUE Entonces StringExpression1
Sino StringExpression2

 

Ejemplo de funcíón IIF en MDX:

Este ejemplo pondrá el color del texto en rojo si la cantidad pedida es iguala 1 y en verde si es diferente de 1.

iif([Measures].[Cantidad Pedida]=1, 255 /*Red*/, 8454016 /*R=128, G=255, B=128*/)

Otro ejemplo sería que si el miembro corriente de la dimensión Measures está vacío saldrá ‘YES’, si por el contrario está informado saldrá ‘NO’

IIF( IsEmpty([Measures].CurrentMember), ‘YES’, ‘NO’)

 

SUM

SUM Meritxell Thu, 10/02/2014 - 11:37

Sintaxis de la función SUM en MDX:

SUM (<<Set>> [, <<NumericExpression>>])

Devuelve la suma de una expresión numérica evaluada a partir de un conjunto

 

Ejemplo:

Devuelve la suma de los importes de pedidos del 2006.

SUM([Dim Periodo].[Año].&[2006] , [Measures].[Importe Pedido] )

 

En SSAS quedaría:

 

Resultado:

 

Count

Count Meritxell Thu, 10/02/2014 - 11:38

Sintaxis de Count en MDX:

COUNT( «Set» [, EXCLUDEEMPTY | INCLUDEEMPTY] )

Esta función devuelve el número de celdas de un Conjunto.

En esta función podemos especificar si se quiere excluir o incluir las celdas vacías en el cálculo.
 

CROSSJOIN

CROSSJOIN Meritxell Thu, 10/02/2014 - 11:42

Sintaxis de CROSSJOIN en MDX:

CROSSJOIN( «Set1», «Set2» )

Esta función permite hacer todas las posibles combinaciones entre dos conjuntos. Es equivalente al operador '*'.

 

Ejemplo de CROSSJOIN en MDX:

CROSSJOIN ( {[Zona Pedido].[Nombre Zona].&[Barcelones], [Zona Pedido].[Nombre Zona].&[Girones]},
                      {[Dim Producto].[Categoria Producto].[Categoria].&[1],
                       [Dim Producto].[Categoria Producto].[Categoria].&[2]} )

Esta expresión combina las zonas 'Barcelones' y 'Girones' con las categorías de producto 'hardware' y 'software'.
Para comprobarlo hacemos la siguiente query:

with set [zonas-Productos] as
  'CROSSJOIN({[Zona Pedido].[Nombre Zona].&[Barcelones],
              [Zona Pedido].[Nombre Zona].&[Girones]},
             {[Dim Producto].[Categoria Producto].[Categoria].&[1],
              [Dim Producto].[Categoria Producto].[Categoria].&[2]})'

select [zonas-Productos] on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:


 

Filter

Filter Meritxell Thu, 10/02/2014 - 11:48

Sintaxis de Filter en MDX:

FILTER( «Set», «Search Condition» )

Esta función toma un conjunto y una expresión booleana como argumentos y devuelve un subconjunto si la expresión booleana es TRUE.

 

Ejemplo de la función Filter en MDX:

FILTER( [Dim Producto].[Categoria Producto].[Producto], [Measures].[Importe Pedido] > 1000 )

Este ejemplo muestra del conjunto de los productos sólo los que tienen un importe de pedido superior a 1000€.

Para ver el resultado creamos la siguiente query:

with Set [Catprod] as
  'FILTER( [Dim Producto].[Categoria Producto].[Producto],
           [Measures].[Importe Pedido] > 1000 )'

select Catprod on columns, [Measures].[Importe Pedido] on rows
from [Analysis DW]

 

Resultado:

 

Si queremos comprobar que realmente se ha hecho bien el subconjunto ejecutamos la siguiente query que nos muestra todos los productos con el importe de pedido correspondiente:

select [Dim Producto].[Categoria Producto].[Producto] on columns,
       [Measures].[Importe Pedido] on rows
from [Analysis DW]

Resultado:


 

Como se puede observar sólo hay dos productos que superan los 1000€ de importe de pedido, y se corresponden con los productos resultantes de usar la funcion Filter anterior.

 

Order

Order Meritxell Thu, 10/02/2014 - 11:53

Sintaxis de Order en MDX:

ORDER( «Set», {«String Expression» | «Numeric Expression»} [, ASC | DESC | BASC | BDESC] )

Esta función ordena un conjunto de miembros dependiendo de la expresión, es decir, que puedes por ejemplo ordenar los productos que tienes en tu cubo ascendente o descendentemente por el importe de Pedido. También permite hacer la ordenación basándose en la jerarquia de ese grupo especificando ASC o DESC. En cambio, si quieres ignorar la jerarquía se debe especificar BASC o BDESC.

 

Ejemplo de la función Order en MDX

ORDER ( [Dim Producto].[Categoria Producto].[Producto], [Measures].[Importe Pedido], BDESC )

Este ejemplo ordena de manera descendente el conjunto de productos por el importe de pedidos, ignorando las jerarquias

Para comprobar el resultado hacemos la siguiente query:

with Set [Ordena_Catprod] as
  'ORDER( [Dim Producto].[Categoria Producto].[Producto],
          [Measures].[Importe Pedido], BDESC )'

select Ordena_Catprod on rows, [Measures].[Importe Pedido] on columns
from [Analysis DW]

 

Resultado:

 

En cambio, si no queremos ignorar las jerarquias:

ORDER( [Dim Producto].[Categoria Producto].[Producto], [Measures].[Importe Pedido], DESC )

La query es:

with Set [Ordena_Catprod] as
  'ORDER( [Dim Producto].[Categoria Producto].[Producto],
          [Measures].[Importe Pedido], DESC )'

select Ordena_Catprod on rows, [Measures].[Importe Pedido] on columns
from [Analysis DW]

y el resultado:

AVG

AVG Meritxell Thu, 10/02/2014 - 11:56

Esta función devuelve la media de los miembros de una jerarquía con respecto a su padre. Hemos de especificar el nivel para que no de resultados alterados.

 

Ejemplo de la función AVG de MDX:

Media de los miembros de la zona clientes

AVG([Dim Cliente].[Zonas Clientes].[Zona].members )

 

Confuguracion del miembro caculado en SSAS:

 

Resultado:


 

Funciones de Tiempo

Funciones de Tiempo Meritxell Thu, 10/02/2014 - 11:57

Parallel Period

Parallel Period Meritxell Thu, 10/02/2014 - 11:59

Sintaxis de la función ParallelPeriod de MDX:

PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )

Esta funcion devuelve el periodo paralelo al corriente. Por tanto, dado un nivel de jerarquía, un número y la medida que queremos analizar nos da el valor de la misma medida en el periodo paralelo. Por tanto, si estás sobre el mes irá al mes anterior, si estamos sobre el año te dará el año anterior,...

Ejemplo de la función ParallelPeriod de MDX

Queremos saber el el importe de pedidos para el periodo año, trimestre, mes, fecha del año pasado de los productos.

Esto seria:

(PARALLELPERIOD ([Dim Periodo].[Año - Semana - Fecha Iso].[Año],1,
                                [Dim Periodo].[Año - Semana - Fecha Iso]), [Measures].[Importe Pedido] )

Puedes ir para atrás al mismo periodo de un año o de más de uno.

Configuración de esta función añadiéndola como un miembro calculado dentro del entorno de SSAS

 

Resultado:

 

Year To Date (YTD)

Year To Date (YTD) Meritxell Thu, 10/02/2014 - 12:02

Sintaxis de la función YTD en MDX:

YTD( [«Member»] )

Esta función devuelve un miembro desde el inicio del año hasta ahora.

Si hacemos la YTD a fecha de hoy de los importes, devolverá la suma de los importes desde inicio de año hasta hoy.

Ejemplo de YearToDate en MDX

sum(YTD( [Dim Periodo].[Año - Semana - Fecha Iso] ), [Measures].[Importe Pedido])

Se ha configurado también a través del entorno SSAS que si la cantidad pedida es igual a 1 se pone en rojo el importe y si es mayor se pone en verde. La pantalla de SSAS queda de la siguiente manera:

 

Resultado:

Otras funciones de MDX muy parecidas a YTD son: QTD (Quarter To Date), MTD (Month To Date), WTD (Week To Date)

 

Periods To Date

Periods To Date Meritxell Thu, 10/02/2014 - 12:05

Sintaxis de PeriodsToDate en MDX:

PERIODSTODATE( [«Level»[, «Member»] ] )

Esta función devuelve el conjunto de miembros del nivel especificado que van del primer miembro hasta el especificado.

Ejemplo de PeriodsToDate en MDX

Si queremos sacar el conjunto de miembros del nivel año hasta el mes de Abril de 2006

Sum (PERIODSTODATE( [Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].[Año],
                                      [Dim Periodo].[Año - Trimestre - Mes - Fecha Iso].[Año].&[2006].&[2.].&[4.] ),
                                      [Measures].[Importe Pedido])

 

En SSAS sería:

 

Resultado:

 

 

4. Gestión de cubos MDX

4. Gestión de cubos MDX Meritxell Thu, 10/02/2014 - 12:07

Las funciones que se pueden hacer para gestionar los cubos son:

➢ Modificar el miembro de Default de una dimensión.
➢ Refrescar/Actualizar las celdas de datos y a los miembros de dimensiones.
➢ Crear un nuevo miembro.
➢ Borrar un miembro.
➢ Mover un miembro con su jerarquía.
 

Modificar el miembro de Default de una dimensión

Modificar el miembro de Default de una dimensión Meritxell Thu, 10/02/2014 - 12:07

Sintaxis para establecer el miembro Default de una dimensión:

ALTER CUBE cube
UPDATE DIMENSION dimension, DEFAULT_MEMBER = ‘MDX Rules’
 

Crear un nuevo miembro

Crear un nuevo miembro Meritxell Thu, 10/02/2014 - 12:09

Sintaxis MDX para crear un nuevo miembro en un cubo:

ALTER CUBE cube
             CREATE DIMENSION MEMBER [ParentUniqueName]. MemberNam,
                           KEY=’KeyValue’ [PropertyName=’PropertyValue’ [, PropertyName=’PropertyValue’….. ]]

 

Mover Miembros De una Dimensión a otra

Mover Miembros De una Dimensión a otra Meritxell Thu, 10/02/2014 - 12:10

Sintaxis MDX para mover miembros de una dimensión a otra en un cubo:

ALTER CUBE cube
             MOVE DIMENSION MEMBER MemberUniqueName [WITH DESCENDANTS]
             UNDER ParentUniqueName

 

Eliminar un Miembro

Eliminar un Miembro Meritxell Thu, 10/02/2014 - 12:11

Sintaxis MDX para eliminar un miembro en un cubo:

ALTER CUBE cube
             DROP DIMENSION MEMBER MemberUniqueName
             [WITH DESCENDANTS]