Subselecciones en Consultas

se aplica a: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Las expresiones de subselección son expresiones SELECT anidadas que se usan para restringir el espacio del cubo desde donde se evalúa el SELECT externo externo. Las subselecciones permiten definir un nuevo espacio sobre el que se evalúan todos los cálculos.

Subselecciones por ejemplo

Comencemos con un ejemplo de cómo las subselecciones pueden ayudar a generar los resultados que queremos mostrar. Supongamos que se le pide que genere una tabla que muestre el comportamiento de ventas, a lo largo de años, para los 10 productos principales.

El resultado debe ser similar a la tabla siguiente:

Para hacer algo parecido al anterior, podría escribir la siguiente expresión MDX:

SELECT [Date].[Calendar Year].MEMBERS on 0  
     , TOPCOUNT( [Product].[Product].MEMBERS  
               , 10  
               , [Measures].[Sales Amount]  
               ) ON 1  
  FROM [Adventure Works]  

Esto devuelve los siguientes resultados:

Que está muy cerca de lo que estamos buscando; excepto que la consulta devolvió 9 productos y no 10, y el total de todos los productos refleja la suma de todos los productos, no la suma de los 9 principales devueltos (en este caso). Otro intento de resolver el problema se presenta en la siguiente consulta MDX:

SELECT [Date].[Calendar Year].MEMBERS on 0  
     , TOPCOUNT( [Product].[Product].CHILDREN, 10, [Measures].[Sales Amount]) ON 1  
  FROM [Adventure Works]  

Esto devuelve los siguientes resultados:

Estuvo muy cerca del resultado deseado porque solo omitió la suma de los productos. En este punto, puede empezar a ajustar la expresión MDX anterior para agregar la línea que falta; sin embargo, esa tarea podría resultar complicada.

Otro enfoque para resolver el problema sería empezar a pensar en términos de redefinir el espacio del cubo sobre el que se resuelve la expresión MDX. ¿Qué ocurre si el cubo "nuevo" contiene solo datos de los 10 productos principales? A continuación, ese cubo tendrá el miembro 'All' ajustado solo a los 10 productos principales, y ahora una consulta sencilla satisfaría nuestras necesidades.

La siguiente expresión MDX usa una instrucción de subselección para redefinir el espacio del cubo a los 10 productos principales y generar los resultados deseados:

SELECT [Date].[Calendar Year].MEMBERS on 0  
     , [Product].[Product].MEMBERS on 1  
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN  
                       , 10  
                       , [Measures].[Sales Amount]  
                       ) ON 0  
          FROM [Adventure Works]  
        )  
 WHERE [Measures].[Sales Amount]  

La expresión anterior devuelve los siguientes resultados:

Los resultados anteriores son exactamente lo que estábamos buscando.

Vamos a revisar qué hizo exactamente la subselección por nosotros. La subselección nos devolvió un nuevo cubo que contenía todas las demás dimensiones del producto tal como están; pero, en la dimensión del producto, filtró todos los miembros para que coincidieran con los 10 mejores productos que nos interesaban. Era como si hubiéramos quitado todos los datos que no cumplían con los diez principales criterios y hubiéramos reconstruido el cubo. El otro concepto importante que se debe comprender en este ejemplo es el hecho de que los 10 productos principales se calcularon respecto al miembro All de todas las otras dimensiones del cubo; esto es cierto porque no se han impuesto otras restricciones de filtrado en la subselección.

Las subselecciones pueden ser tan complejas como se desee; en el ejemplo siguiente se muestra cómo generar una tabla similar a la mencionada anteriormente, pero filtrada en Francia en la dimensión Territorio de Ventas y en Internet para la dimensión Canal de Ventas.

SELECT [Date].[Calendar Year].MEMBERS on 0  
     , [Product].[Product].MEMBERS on 1  
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN  
                       , 10  
                       , [Measures].[Sales Amount]  
                       ) ON 0  
             , [Sales Territory].[Sales Territory].[Region].[France] on 1  
             ,  [Sales Channel].[Sales Channel].[Internet] on 2  
          FROM [Adventure Works]  
        )  
 WHERE [Measures].[Sales Amount]  

Se generaron los siguientes resultados:

Los resultados anteriores son los 10 mejores productos vendidos en Francia a través del canal de Internet.

Instrucción Subselect

El BNF para la subselección es:

[WITH [<calc-clause> ...]]  
SELECT [<axis-spec> [, <axis-spec> ...]]  
FROM [<identifier> | (< sub-select-statement >)]  
[WHERE <slicer>]  
[[CELL] PROPERTIES <cellprop> [, <cellprop> ...]]  
  
< sub-select-statement > :=  
   SELECT [<axis-spec> [, <axis-spec> ...]]  
   FROM [<identifier> | (< sub-select-statement >)]  
   [WHERE <slicer>]  
  

La subselección es otra instrucción SELECT en la que las especificaciones del eje y la especificación del segmento filtran el espacio del cubo sobre el que se evalúa la selección externa.

Cuando se especifica un miembro en una de las cláusulas de eje o slicer, ese miembro junto con sus ancestros y descendientes se incluyen en el espacio del subcubo para la subselección; todos los hermanos no mencionados en la cláusula de eje o slicer, junto con sus descendientes, se filtran del subespacio. De este modo, el espacio de la selección externa se ha limitado a los miembros existentes en la cláusula de eje o la cláusula de corte, con sus ascendentes y descendientes como se mencionó antes.

Dado que el miembro All de todas las dimensiones no mencionadas en las cláusulas axis o slicer pertenece al espacio de la selección; a continuación, todos los descendientes del miembro All de esas dimensiones también forman parte del espacio de subselección.

El miembro 'All', en todas las dimensiones, en el espacio de subcubos, se reevalúa para reflejar el impacto de las restricciones del nuevo espacio.

En el ejemplo siguiente se mostrará lo que se ha dicho anteriormente; la primera expresión MDX ayuda a mostrar los valores sin filtrar en el cubo, el segundo MDX ilustra el efecto del filtrado en la cláusula Subselect:

SELECT { [Customer].[Customer Geography].[All Customers]  
       , [Customer].[Customer Geography].[Country].&[United States]  
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]  
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]  
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]  
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]  
       } ON 1  
     ,  {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} ON 0  
  FROM [Adventure Works]  

Devuelve los siguientes valores:

En el ejemplo anterior, Seattle es una subcategoría de Washington, Portland es una subcategoría de Oregon, Oregon y Washington son subcategorías de Estados Unidos y Estados Unidos es una subcategoría de [Customer Geography].[Todos los clientes]. Todos los miembros mostrados en este ejemplo tienen otros elementos del mismo nivel que contribuyen al valor agregado primario; Es decir, Spokane, Tacoma y Everett son ciudades del mismo nivel de Seattle y todas contribuyen a Washington Internet Sales Amount. El valor Reseller Sales Amount es independiente del atributo Customer Geography; por lo tanto, el valor All se muestra en los resultados. La siguiente expresión MDX muestra el efecto de filtrado de la cláusula de subselección:

SELECT { [Customer].[Customer Geography].[All Customers]  
       , [Customer].[Customer Geography].[Country].&[United States]  
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]  
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]  
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]  
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]  
       } ON 1  
     ,  {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} ON 0  
  FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0  
           FROM [Adventure Works]  
        )  

Devuelve los siguientes valores:

Los resultados anteriores muestran que solo los antepasados y descendientes del Estado de Washington son parte del subespacio donde se evaluó la instrucción select externa; Oregón y Portland se han eliminado del subcubo porque Oregón y todos los demás estados hermanos no se mencionaron en la subselección cuando se mencionó Washington.

El miembro All se ajustó para reflejar el filtrado en Washington; no solo se ajustó en la dimensión [Customer Geography], sino en todas las demás dimensiones que cruzaron con [Customer Geography]. Todas las dimensiones que no se cruzan con [Customer Geography] permanecen en el subcubo sin modificar.

Las dos instrucciones MDX siguientes muestran cómo se ajusta el miembro All de otras dimensiones para satisfacer el efecto de filtrado de la subselección. La primera consulta muestra los resultados no modificados y el segundo muestra el impacto del filtrado:

SELECT { [Customer].[Customer Geography].[All Customers]  
       , [Customer].[Customer Geography].[Country].&[United States]  
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]  
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]  
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]  
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]  
       } ON 1  
     ,   [Product].[Product Line].MEMBERS ON 0  
  FROM [Adventure Works]  
 WHERE [Measures].[Internet Sales Amount]  

SELECT { [Customer].[Customer Geography].[All Customers]  
       , [Customer].[Customer Geography].[Country].&[United States]  
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]  
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]  
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]  
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]  
       } ON 1  
     ,   [Product].[Product Line].MEMBERS ON 0  
  FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0  
           FROM [Adventure Works]  
        )  
 WHERE [Measures].[Internet Sales Amount]  

Los resultados anteriores muestran que todos los valores de Productos se han ajustado solo a valores del Estado de Washington, según lo previsto.

Las subselecciones se pueden anidar sin límite en cuanto a la profundidad, excepto por la memoria disponible. La subselección interna define el subespacio inicial sobre el que se aplica el filtrado y se pasa a la siguiente selección externa. Es importante tener en cuenta que el anidamiento no es una operación conmutativa; por lo tanto, el orden en el que se establece el anidamiento puede producir resultados diferentes. Los ejemplos siguientes deben mostrar la diferencia al elegir un orden de anidamiento.

SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0  
     , [Product].[Product].MEMBERS on 1  
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) ON 0  
          FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) on 0  
                  FROM [Adventure Works]  
               )  
        )  
 WHERE [Measures].[Sales Amount]  

Devolvió los siguientes resultados.

SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0  
     , [Product].[Product].MEMBERS on 1  
  FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) ON 0  
          FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) on 0  
                  FROM [Adventure Works]  
               )  
        )  
 WHERE [Measures].[Sales Amount]  

Devolvió los siguientes resultados.

Como puede ver, existen diferencias en los resultados entre ambos conjuntos. La primera consulta respondió a la pregunta de cuáles son los productos más vendidos en las 5 regiones más vendidas, la segunda consulta respondió a la pregunta de dónde son las ventas más grandes de los 5 productos más vendidos.

Observaciones

Las subselecciones tienen las siguientes restricciones y limitaciones:

• La cláusula WHERE no filtra el subespacio.

• La cláusula WHERE solo cambia el miembro predeterminado en el subcubo.

• No se permite la cláusula NON EMPTY en una cláusula de eje; use en su lugar una expresión de función NonEmpty (MDX ).

• No se permite la cláusula HAVING en una cláusula de eje; use una expresión de función Filter (MDX) en su lugar.

• De forma predeterminada, los miembros calculados no se permiten en subselecciones; sin embargo, esta restricción se puede cambiar, en cada sesión, asignando un valor a la propiedad de la cadena de conexión SubQueries o a la propiedad DBPROP_MSMD_SUBQUERIES en Propiedades XMLA Admitidas (XMLA). Vea Miembros calculados en subselecciones y subcubos para obtener una explicación detallada del comportamiento de los miembros calculados en función de los valores de SubConsultas o DBPROP_MSMD_SUBQUERIES.