• Author / Uploaded
• Reiner Guerra Salas

Citation preview

Sistemas de Información Estratégica

MDX MDX (Expresiones multidimensionales) es un lenguaje para consultas de base de datos multidimensionales, de la misma forma que SQL es usada para consultas sobre base de datos relacionales. Fue originalmente definida como parte de las especificaciones OLE DB. MDX funciona para expresiones multidimensionales. Este es un lenguaje de consultas implementado por Mondrian. Las expresiones multidimensionales (MDX) permiten consultar objetos multidimensionales, como los cubos, y devolver conjuntos de celdas multidimensionales que contengan los datos del cubo. Una consulta básica de MDX luce como esto: SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS, {[Product].members} ON ROWS FROM [Sales] WHERE [Time].[1997].[Q2]

Consulta de MDX básica (MDX) La consulta de MDX (Expresiones multidimensionales) básica es la instrucción SELECT: la consulta utilizada con más frecuencia en MDX. Si se comprende cómo una instrucción MDX SELECT debe especificar un conjunto de resultados, en qué consiste la sintaxis de la instrucción SELECT y cómo crear una consulta simple mediante la instrucción SELECT, se tendrá un conocimiento sólido de cómo utilizar MDX para realizar consultas de datos multidimensionales. Sintaxis de la Instrucción SELECT En la sintaxis siguiente se muestra una instrucción SELECT básica que incluye el uso de las cláusulas SELECT, FROM y WHERE: [ WITH [ , ... ] ] SELECT [ * | ( [ , ... ] ) ] FROM [ ] [ ] Ejemplo de la Instrucción SELECT En el siguiente ejemplo se muestra una consulta de MDX básica que utiliza la instrucción SELECT . Esta consulta devuelve un conjunto de resultados que contiene las cifras de ventas e impuestos de 2002 y 2003 de la zona de ventas sudoeste. SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS, { [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS FROM [Adventure Works]

Docente: Ing. Reiner Guerra Salas

1

Sistemas de Información Estratégica WHERE ( [Sales Territory].[Southwest] )

Ejecución de Consultas Revisemos el cubo Quadrant Analysis del esquema SampleData.mondrian ubicado en C:\pentaho\bi-server\pentaho-solutions\samples\analysis, este esquema es proveído con la instalación de Pentaho.

Se probarán algunas de las consultas MDX para la verificación de este cubo.

Este cubo trabaja con la tabla quadrant _actuals de la base de datos PENTAHO_sampledata. Al momento de conectar el esquema con la plantilla de diseño de consultas MDX se debe verificar que la conexión haya sido exitosa. Se muestra el mensaje Mondrian connection Successful.

Docente: Ing. Reiner Guerra Salas

2

Sistemas de Información Estratégica

Descripción de la Tabla La tabla quadrant_actuals registra las cantidades asignadas a cada cargo dentro de un departamento en cada región. Las cantidades registradas son tres: Actual: Real, cantidad real asignada. Budget: Presupuesto, la cantidad proyectada o presupuestada para ese cargo. Variance: Varianza, la diferencia entre las dos anteriores (Budget-Actual).

Descripción del Cubo El cubo Quadrant Analysis trabaja con la tabla de hechos descrita en la parte superior. El cubo presenta tres medidas específicas para cada cantidad registrada en la tabla. Actual: Suma de todas estas cantidades. Budget: Suma de todos los presupuestos. Variance: Suma de todos las cantidades calculadas en el campo Variance. Las medidas realizan sumas y presentas totales y subtotales a manera que se van expandiendo los niveles. Se establece aquí el nivel de detalle en el concepto de granularidad. El nivel más general tiene que ver con la suma de cantidades de todas las regiones. A un nivel intermedio se encuentra la medida para todos los departamentos por región. Y en el nivel más específico se tiene la suma de todos los cargos por departamento. De la misma manera estas medidas permiten también mostrar las cantidades por cada cargo, por cada departamento, por cada región.

Consultas 1.

Consulta General que permite mostrar el total de la medida Actual por todo el conjunto de posiciones, en todos los departamentos de todas las regiones.

Docente: Ing. Reiner Guerra Salas

3

Sistemas de Información Estratégica select NON EMPTY {[Measures].[Actual]} ON COLUMNS, /*Select marca el inicio de la consulta para los campos no vacios en la medida Actual, estos datos constituyen los valores mostrados en la columna de mi tabla*/ NON EMPTY {([Region].[All Regions], [Department].[All Departments], [Positions].[All Positions])} ON ROWS /*Se debe señalar los valores que se mostraran en las filas de la tabla. En la tabla figurarán los nombres de todas las regiones, de todos los departamentos y de todas las posiciones o cargos.*/ from [Quadrant Analysis] /*la clausula from indica el cubo con el que estoy trabajando*/ Los nombres All Regions, All Deparments y All Positions fueron indicados en la creación del cubo en el atributo allMenberName del primer nivel de jerarquía de la dimensión Región, del primer nivel de jerarquía de la dimensión Deparments y en el primer nivel de jerarquía de la dimensión Positions. Copiamos la consulta en el diseñador de consultas MDX de Pentaho Schema Workbench, dar clic en Execute…

2.

Consulta que devuelve el valor de la medida Actual de todos las posiciones de todos los departamentos detallada por cada Región. Central, Eastern, Southern, Western. select NON EMPTY {[Measures].[Actual]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(Union({[Region].[All Regions]}, [Region].[All Regions].Children)), {([Department].[All Departments], [Positions].[All Positions])}) ON ROWS from [Quadrant Analysis]

Docente: Ing. Reiner Guerra Salas

4

Sistemas de Información Estratégica

Expresión

Descripción

Sintaxis

Argumentos Set_Expression1 Expresión MDX válida que devuelve un conjunto. Set_Expression2 Expresión MDX válida que devuelve un conjunto.

Devuelve el producto cruzado de uno o más conjuntos.

Crossjoin

Standard syntax Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] ) Alternate syntax Set_Expression1 * Set_Expression2 [* ...n]

El orden de las tuplas en el conjunto resultante depende del orden de los conjuntos que deben unirse y del orden de sus miembros. Por ejemplo, cuando el primer conjunto consta de {x1, x2,...,xn} y el segundo conjunto consta de {y1, y2, ..., yn}, el producto cruzado de estos conjuntos es: {(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),..., (x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}

Set_Expression

Hierarchize

Ordena los miembros de un conjunto en una jerarquía. La función siempre conserva los duplicados.

Expresión MDX válida que devuelve un conjunto.

Hierarchize(Set_Expression [ , POST ] )

Docente: Ing. Reiner Guerra Salas

Si no se especifica POST, la función ordena los miembros en un nivel en su orden natural. Si se especifica POST, la función Hierarchize ordena los miembros en un nivel siguiendo un orden postnatural.

5

Sistemas de Información Estratégica Set Expression 1 Expresión MDX válida que devuelve un conjunto.

Union

Children

Devuelve un conjunto generado por la unión de dos conjuntos que, opcionalmente, conserva miembros duplicados.

Devuelve el conjunto de elementos secundarios de un miembro especificado.

Standard syntax Union(Set_Expression1, Set_Expression2 [,...n][, ALL]) Alternate syntax 1 Set_Expression1 + Set_Expression2 [+...n] Alternate syntax 2 {Set_Expression1 , Set_Expression2 [,...n]}

Set Expression 2 Expresión MDX válida que devuelve un conjunto. Con la sintaxis 1 alternativa, los duplicados se eliminan de forma predeterminada.Con la sintaxis estándar, el indicador ALL conserva los duplicados del conjunto combinado. Se eliminan los duplicados de la cola del conjunto. Con la sintaxis alternativa 2, siempre se conservan los duplicados.

Member_Expression

Member_Expression.Children

Expresión MDX válida que devuelve un miembro.

Copiamos la consulta en el diseñador de consultas MDX de Pentaho Schema Workbench, dar clic en Execute…

Docente: Ing. Reiner Guerra Salas

6

Sistemas de Información Estratégica 3.

Consulta que muestra los valores de la suma de actual de todas las posiciones en todas las regiones, detallada por cada departamento.

select NON EMPTY {[Measures].[Actual]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(Union(Union(Crossjoin({[Region].[All Regions]}, {[Department].[All Departments]}), Crossjoin({[Region].[All Regions]}, [Department].[All Departments].Children)), Crossjoin([Region].[All Regions].Children, {[Department].[All Departments]}))), {[Positions].[All Positions]}) ON ROWS from [Quadrant Analysis] 4.

Consulta que muestra los valores de la suma de actual de todas las posiciones en todas las regiones, detallando sólo los departamentos de la región central. select NON EMPTY {[Measures].[Actual]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize(Union(Union({([Region].[All Regions], [Department].[All Departments])}, Crossjoin([Region].[All Regions].Children, {[Department].[All Departments]})), Crossjoin({[Region].[All Regions].[Central]}, [Department].[All Departments].Children))), {[Positions].[All Positions]}) ON ROWS from [Quadrant Analysis]

Docente: Ing. Reiner Guerra Salas

7

Sistemas de Información Estratégica

5.

Consulta que muestra el valor de la medida actual de cada región.

select NON EMPTY {[Measures].[Actual]} ON COLUMNS NON EMPTY {[Region].[All Regions].Children} ON ROWS from [Quadrant Analysis]

6.

Consulta que muestra el valor de la medida Actual de todas las posiciones de todos los departamentos detalladas por región. select NON EMPTY {[Measures].[Actual]} ON COLUMNS, NON EMPTY Crossjoin(Hierarchize([Region].[All Regions].Children), {([Department].[All Departments], [Positions].[All Positions])}) ON ROWS from [Quadrant Analysis]

7.

Consulta que muestra el valor de la medida Actual de todos los departamentos detalladas por región. select NON EMPTY {[Measures].[Actual]} ON COLUMNS, NON EMPTY Crossjoin({[Region].[All Regions].Children}, {[Department].[All Departments]}) ON ROWS from [Quadrant Analysis]

Docente: Ing. Reiner Guerra Salas

8

Sistemas de Información Estratégica 8.

Consulta que muestra el valor de las medidas Actual, Budget y Variance detalladas por región. select NON EMPTY {[Measures].[Actual], [Measures].[Budget], [Measures].[Variance]} ON COLUMNS, NON EMPTY {[Region].[All Regions].Children} ON ROWS from [Quadrant Analysis]

Ejercicios Resolver las siguientes consultas de tal modo que muestren los resultados de los campos que se consignan en cada tabla. 1.

Consulta General que permite mostrar el total de la medida Budget en todas las regiones.

Medidas Budget All Regions ? Region

2.

Consulta General que permite mostrar el total de la medida Variance en todas las regiones.

Medidas Variance All Regions ? Region

3.

Consulta que muestra los valores de Actual para cada departamento pero sólo de la región Sur.

Region Departments Southern Executive Management Finance Human Resource Marketing & Comunication Product Development Professional Services Sales Docente: Ing. Reiner Guerra Salas

Medidas Actual ?

9

Sistemas de Información Estratégica 4.

Consulta que muestra los valores de Actual para cada departamento pero sólo de la región Este.

Region Eastern

5.

Departments Executive Management Finance Human Resource Marketing & Comunication Product Development Professional Services Sales

Medidas Actual ?

Consulta que muestra los valores de Actual para cada departamento pero sólo de la región Oeste.

Region Departments Western Executive Management Finance Human Resource Marketing & Comunication Product Development Professional Services Sales

Docente: Ing. Reiner Guerra Salas

Medidas Actual ?

10