Manual de MDX
Manual de MDX Meritxell 25 Septiembre, 2014 - 11:58Este 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 29 Septiembre, 2014 - 11:40MDX (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 29 Septiembre, 2014 - 10:57El 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.
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 2 Octubre, 2014 - 12:14Cada 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.
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 29 Septiembre, 2014 - 11:22En 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:
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:
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:
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:
3. Fórmulas y funciones en MDX
3. Fórmulas y funciones en MDX Meritxell 29 Septiembre, 2014 - 11:28Conjunto con nombre (Named Sets)
Conjunto con nombre (Named Sets) Meritxell 29 Septiembre, 2014 - 11:35Un 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:
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:
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:
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:
Miembros Calculados
Miembros Calculados Dataprix 29 Septiembre, 2014 - 15:27Los 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:
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:
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:
Resultado:
Expresiones MDX
Expresiones MDX Meritxell 29 Septiembre, 2014 - 15:33Las 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:
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
Navegación
Navegación Meritxell 29 Septiembre, 2014 - 15:53CurrentMember
CurrentMember Meritxell 2 Octubre, 2014 - 12:18La 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:
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 2 Octubre, 2014 - 12:18La 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:
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 2 Octubre, 2014 - 12:19La 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 2 Octubre, 2014 - 12:21La 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 2 Octubre, 2014 - 12:23La 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:
Descendants
Descendants Meritxell 2 Octubre, 2014 - 12:24La 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:
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:
Operadores
Operadores Meritxell 2 Octubre, 2014 - 10:54Operadores Aritméticos y de conjunto
Operadores Aritméticos y de conjunto Meritxell 2 Octubre, 2014 - 11:18Operadores 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 2 Octubre, 2014 - 11:16Los 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 2 Octubre, 2014 - 11:19Los 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 2 Octubre, 2014 - 11:22Los 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 2 Octubre, 2014 - 11:23IIF
IIF Meritxell 2 Octubre, 2014 - 11:34Sintaxis 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 2 Octubre, 2014 - 11:37Sintaxis 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 2 Octubre, 2014 - 11:38Sintaxis 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 2 Octubre, 2014 - 11:42Sintaxis 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 2 Octubre, 2014 - 11:48Sintaxis 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 2 Octubre, 2014 - 11:53Sintaxis 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 2 Octubre, 2014 - 11:56Esta 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 2 Octubre, 2014 - 11:57Parallel Period
Parallel Period Meritxell 2 Octubre, 2014 - 11:59Sintaxis 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 2 Octubre, 2014 - 12:02Sintaxis 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 2 Octubre, 2014 - 12:05Sintaxis 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 2 Octubre, 2014 - 12:07Las 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 2 Octubre, 2014 - 12:07Sintaxis 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 2 Octubre, 2014 - 12:09Sintaxis 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 2 Octubre, 2014 - 12:10Sintaxis 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 2 Octubre, 2014 - 12:11Sintaxis MDX para eliminar un miembro en un cubo:
ALTER CUBE cube
DROP DIMENSION MEMBER MemberUniqueName
[WITH DESCENDANTS]