Unterauswahlen in Abfragen

Untergeordnete Ausdrücke sind geschachtelte SELECT-Ausdrücke, die verwendet werden, um den Bereich des Cubes einzuschränken, der zur Auswertung des äußeren externen SELECT-Ausdrucks dient. Mithilfe von untergeordneten Ausdrücken kann ein neuer Bereich definiert werden, mit dem alle Berechnungen ausgewertet werden.

Untergeordnete Ausdrücke nach Beispiel

Beginnen wir mit einem Beispiel, wie Unterauswahlen helfen können, die Ergebnisse zu erzeugen, die wir anzeigen möchten. Angenommen, Sie werden gebeten, eine Tabelle zu erzeugen, in der das Verkaufsverhalten für ein Jahr für die besten 10 Produkte angezeigt wird.

Das Ergebnis sollte wie die folgende Tabelle aussehen:

	Summe von Jahren	Jahr 1	...
Summe der ersten 10 Produkte
Product A
...

Das o. a. Ergebnis könnte mit dem folgenden MDX-Ausdruck erreicht werden:

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

Dies gibt die folgenden Ergebnisse zurück:

	All Periods	KJ 2005	KJ 20062	KJ 2007	KJ 2008
All Products	$80,450,596.98	$8,065,435.31	$24,144,429.65	$32,202,669.43	$16,038,062.60
Mountain-200 Black, 38	$1,634,647.94	(null)	(null)	$894,207.97	$740,439.97
Mountain-200 Black, 42	$1,285,524.65	(null)	(null)	$722,137.65	$563,387.00
Mountain-200 Silver, 38	$1,181,945.82	(null)	(null)	$634,600.78	$547,345.03
Mountain-200 Black, 46	$995,927.43	(null)	(null)	$514,995.76	$480,931.68
Mountain-200 Silver, 42	$1,005,111.77	(null)	(null)	$529,543.29	$475,568.49
Mountain-200 Silver, 46	$975,932.56	(null)	(null)	$526,759.30	$449,173.26
Road-150 Red, 56	$792,228.98	$382,159.24	$410,069.74	(null)	(null)
Mountain-200 Black, 38	$1,471,078.72	(null)	$789,958.49	$681,120.23	(null)
Straße 350 W Gelb, 48	$1,380,253.88	(null)	(null)	$744,988.37	$635,265.50

Dies entspricht fast genau den gewünschten Ergebnissen, außer dass die Abfrage nur 9 von 10 Produkten zurückgab und die Gesamtsumme All Products der Summe aller Produkte entspricht, nicht nur der Summe der in diesem Fall zurückgegebenen Top 9. Ein anderer Ansatz zur Lösung des Problems wird in der folgenden MDX-Abfrage dargestellt:

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

Dies gibt die folgenden Ergebnisse zurück:

	Alle Zeiträume	KJ 2005	KJ 2006	KJ 2007	KJ 2008
Mountain-200 Black, 38	$1,634,647.94	(null)	(null)	$894,207.97	$740,439.97
Mountain-200 Black, 42	$1,285,524.65	(null)	(null)	$722,137.65	$563,387.00
Mountain-200 Silver, 38	$1,181,945.82	(null)	(null)	$634,600.78	$547,345.03
Mountain-200 Black, 46	$995,927.43	(null)	(null)	$514,995.76	$480,931.68
Mountain-200 Silver, 42	$1,005,111.77	(null)	(null)	$529,543.29	$475,568.49
Mountain-200 Silver, 46	$975,932.56	(null)	(null)	$526,759.30	$449,173.26
Road-150 Red, 56	$792,228.98	$382,159.24	$410,069.74	(null)	(null)
Mountain-200 Black, 38	$1,471,078.72	(null)	$789,958.49	$681,120.23	(null)
Straße 350 W Gelb, 48	$1,380,253.88	(null)	(null)	$744,988.37	$635,265.50
Road-150 Red, 62	$566,797.97	$234,018.86	$332,779.11	(null)	(null)

Dies entsprach fast genau den gewünschten Ergebnissen, nur die Summe der Produkte war nicht richtig. An diesem Punkt kann man anfangen, den oben erwähnten MDX-Ausdruck zu ändern, um die fehlende Zeile hinzuzufügen. Jedoch könnte sich diese Aufgabe als aufwändig erweisen.

Ein anderer Ansatz zur Lösung des Problems besteht in der Neudefinierung des Cuberaums, über den der MDX-Ausdruck aufgelöst wird. Was, wenn der 'neue' Cube nur Daten von den ersten 10-Produkten enthält? Für diesen Cube wird das All-Element an die Top 10-Produkte angepasst, und eine einfache Abfrage kann die gewünschten Ergebnisse zurückgeben.

Der folgende MDX-Ausdruck verwendet eine Unterauswahlanweisung, um den Cuberaum für die ersten 10 Produkte neu zu definieren, um die gewünschten Ergebnisse zu erzeugen:

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]

Der vorangehende Ausdruck gibt die folgenden Ergebnisse zurück:

	Alle Zeiträume	KJ 2005	KJ 2006	KJ 2007	KJ 2008
All Products	$19,997,183.30	$1,696,815.63	$2,816,611.28	$7,930,797.72	$7,552,958.66
Mountain-200 Silver, 38	$2,160,981.60	(null)	(null)	$1,024,359.10	$1,136,622.49
Mountain-200 Silver, 42	$1,914,547.85	(null)	(null)	$903,061.68	$1,011,486.18
Mountain-200 Silver, 46	$1,906,248.55	(null)	(null)	$877,077.79	$1,029,170.76
Mountain-200 Black, 38	$1,811,229.02	(null)	$896,511.60	$914,717.43	(null)
Mountain-200 Black, 38	$2,589,363.78	(null)	(null)	$1,261,406.37	$1,327,957.41
Mountain-200 Black, 42	$2,265,485.38	(null)	(null)	$1,126,055.89	$1,139,429.49
Mountain-200 Black, 46	$1,957,528.24	(null)	(null)	$946,453.88	$1,011,074.37
Road-150 Red, 62	$1,769,096.69	$828,011.68	$941,085.01	(null)	(null)
Road-150 Red, 56	$1,847,818.63	$868,803.96	$979,014.67	(null)	(null)
Straße 350 W Gelb, 48	$1,774,883.56	(null)	(null)	$877,665.59	$897,217.96

Die o.a. Ergebnisse sind das, nach dem wir gesucht haben.

Überprüfen wir, was genau der untergeordnete Ausdruck ausgeführt hat. Der untergeordnete Ausdruck gab einen neuen Cube zurück, der alle Dimensionen des Produkts korrekt enthielt, nur in der Produktdimension filterte er alle Elemente für die gewünschten Top 10-Produkte. Das ist, als ob alle Daten, die nicht den Top 10 entsprechen, entfernt werden, und der Cube neu erstellt wird. Bei diesem Beispiel muss beachtet werden, dass die Top 10-Produkte über das All-Element aller anderen Dimensionen im Cube berechnet wurden, denn in dem untergeordneten Ausdruck wurden keine anderen Filtereinschränkungen festgelegt.

Untergeordnete Ausdrücke können so komplex sein, wie gewünscht. Im folgenden Beispiel wird veranschaulicht, wie eine ähnliche Tabelle wie die oben erwähnte erzeugt wird, die jedoch in der Sales Territory-Dimension nach France und in der Sales Channel-Dimension nach Internet gefiltert wird.

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]

Ergibt die folgenden Ergebnisse:

	Alle Zeiträume	KJ 2005	KJ 2006	KJ 2007	KJ 2008
All Products	$748,682.49	$32,204.43	$73,125.18	$269,506.56	$373,846.32
Mountain-200 Silver, 38	$90,479.61	(null)	(null)	$41,759.82	$48,719.79
Mountain-200 Silver, 42	$97,439.58	(null)	(null)	$39,439.83	$57,999.75
Mountain-200 Silver, 46	$102,079.56	(null)	(null)	$27,839.88	$74,239.68
Mountain-200 Black, 38	$26,638.28	(null)	$12,294.59	$14,343.69	(null)
Mountain-200 Black, 38	$96,389.58	(null)	(null)	$41,309.82	$55,079.76
Mountain-200 Black, 42	$80,324.65	(null)	(null)	$43,604.81	$36,719.84
Mountain-200 Black, 46	$107,864.53	(null)	(null)	$45,899.80	$61,964.73
Road-150 Red, 62	$46,517.51	$14,313.08	$32,204.43	(null)	(null)
Road-150 Red, 56	$46,517.51	$17,891.35	$28,626.16	(null)	(null)
Straße 350 W Gelb, 48	$54,431.68	(null)	(null)	$15,308.91	$39,122.77

Die oben genannten Ergebnisse sind die Top 10-Produkte, die in Frankreich über den Internetchannel verkauft wurden.

SELECT-Anweisung

Das BNF für die Unterauswahl ist:

[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>]

Der untergeordnete Ausdruck ist eine weitere SELECT-Anweisung, wo die Achsenspezifikationen und die Slicerspezifikation den Bereich des Cubes filtern, über den der äußere Select-Ausdruck ausgewertet wird.

Wenn ein Element in einer der Achsen- oder Slicerklauseln angegeben wird, dann werden mit diesem Element seine Vorgänger und Nachfolger in den Bereich des Teilcubes für den untergeordneten Ausdruck einbezogen. Alle in der Achsen- oder Slicerklausel nicht genannten gleichgeordneten Elemente und ihre Nachfolger werden über den Teilbereich gefiltert. So ist der Bereich des äußeren untergeordneten Ausdrucks, wie bereits beschrieben, auf die vorhandenen Elemente (sowie ihre Vorgänger und Nachfolger) der Achsen- oder Slicerklausel begrenzt.

Da Alles Elemente zu allen nichterwähnten Dimensionen in Achse oder Slicerklauseln der auserlesenen Leerzeichen gehören; daher sind alle Nachfolger der Alles Elemente von diesen Dimensionen also ein Teil der Unterauswahlleerzeichen.

Das All-Element im Cubeteilbereich wird in allen Dimensionen neu bewertet, um den Begrenzungen des neuen Bereichs zu entsprechen.

Das folgende Beispiel bestätigt die oben genannten Aussagen. Der erste MDX-Ausdruck hilft, die ungefilterten Werte im Cube anzuzeigen, der zweite MDX-Ausdruck veranschaulicht den Effekt der Filterung in der Subselect-Klausel:

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]

Gibt folgende Werte zurück:

	Internet Sales Amount	Reseller Sales Amount
All Customers	$29,358,677.22	$80,450,596.98
USA	$9,389,789.51	$80,450,596.98
Oregon	$1,170,991.54	$80,450,596.98
Portland	$110,649.54	$80,450,596.98
Washington	$2,467,248.34	$80,450,596.98
Seattle	$75,164.86	$80,450,596.98

Im oben erwähnten Beispiel ist Seattle ein untergeordnetes Element von Washington, Portland ist ein untergeordnetes Element von Oregon, Oregon und Washington sind untergeordnete Elemente von USA, und USA ist ein untergeordnetes Element von [Kundengeografie] [Aller Kunden]. Alle angezeigten Elemente in diesem Beispiel verfügen über andere gleichgeordnete Elemente, die zum übergeordneten Aggregatwert beitragen; d. h. Spokane, Tacoma und Everett sind gleichgeordnete Orte von Seattle und tragen zu Washington Internet Sales Amount bei. Der Wert Reseller Sales Amount ist vom Attribut Customer Geography unabhängig, daher wird der All-Wert in den Ergebnissen angezeigt. Der nächste MDX-Ausdruck veranschaulicht den Filtereffekt der Unterauswahlklausel:

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]
        )

Gibt folgende Werte zurück:

	Internet Sales Amount	Reseller Sales Amount
Kunden	$2,467,248.34	$80,450,596.98
USA	$2,467,248.34	$80,450,596.98
Washington	$2,467,248.34	$80,450,596.98
Seattle	$75,164.86	$80,450,596.98

Die oben genannten Ergebnisse zeigen, dass nur Vorgänger und Nachfolger von Washington State zum Teilbereich gehören, über den die äußere Select-Anweisung ausgewertet wurde.

Das All-Element wurde der Filterung für Washington entsprechend angepasst, und zwar in der Dimension [Customer Geography] und allen anderen Dimensionen, die sich mit [Customer Geography] überschneiden. Alle Dimensionen, die sich nicht mit [Customer Geography] überschneiden, bleiben im Teilcube unverändert.

Die folgenden zwei MDX-Anweisungen veranschaulichen wie das All-Element für andere Dimensionen angepasst wird, um den Filterkriterien des untergeordneten Ausdrucks zu entsprechen. Die erste Abfrage zeigt die unveränderten Ergebnisse an, und die Zweite zeigt folgende Auswirkungen der Filterung:

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]

	All Products	Zubehör	Komponenten	Mountain	Straße	Touring
Kunden	$29,358,677.22	$604,053.30	(null)	$10,251,183.52	$14,624,108.58	$3,879,331.82
USA	$9,389,789.51	$217,168.79	(null)	$3,547,956.78	$4,322,438.41	$1,302,225.54
Oregon	$1,170,991.54	$30,513.17	(null)	$443,607.98	$565,372.10	$131,498.29
Portland	$110,649.54	$2,834.17	(null)	$47,099.91	$53,917.17	$6,798.29
Washington	$2,467,248.34	$62,662.92	(null)	$945,219.38	$1,155,880.07	$303,485.97
Seattle	$75,164.86	$2,695.74	(null)	$19,914.53	$44,820.06	$7,734.54

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]

	All Products	Zubehör	Komponenten	Mountain	Straße	Touring
Kunden	$2,467,248.34	$62,662.92	(null)	$945,219.38	$1,155,880.07	$303,485.97
USA	$2,467,248.34	$62,662.92	(null)	$945,219.38	$1,155,880.07	$303,485.97
Washington	$2,467,248.34	$62,662.92	(null)	$945,219.38	$1,155,880.07	$303,485.97
Seattle	$75,164.86	$2,695.74	(null)	$19,914.53	$44,820.06	$7,734.54

Die Ergebnisse zeigen, dass sich die Werte für All Products, wie erwartet, nur auf Werte von Washington State beziehen.

Untergeordnete Ausdrücke können je nach verfügbarem Speicher unbegrenzt geschachtelt werden. Der innerste untergeordnete Ausdruck definiert den Startteilbereich, über den gefiltert und an den nächsten äußeren Select-Ausdruck weitergegeben wird. Es muss beachtet werden, dass Schachtelung ein abhängiger Vorgang ist. Deshalb kann die Reihenfolge, in der die Schachtelung stattfindet,unterschiedliche Ergebnisse liefern. In den folgenden Beispielen sollte der Unterschied beim Auswählen einer Schachtelungsreihenfolge veranschaulicht werden.

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]

Folgende Ergebnisse wurden ausgegeben:

	Alle Vertriebsgebiete	Australien	Canada	Zentral	Nordwest	Südwest
All Products	$7,591,495.49	$1,281,059.99	$1,547,298.12	$600,205.79	$1,924,763.50	$2,238,168.08
Mountain-200 Silver, 38	$1,449,576.15	$248,702.93	$275,052.45	$141,103.65	$349,487.01	$435,230.12
Mountain-200 Black, 38	$1,722,896.50	$218,024.05	$418,726.43	$123,929.46	$486,694.63	$475,521.93
Mountain-200 Black, 42	$1,573,655.14	$239,137.96	$319,921.61	$130,102.75	$420,445.84	$464,046.98
Mountain-200 Black, 46	$1,420,500.58	$192,320.16	$230,875.99	$117,044.49	$424,813.66	$455,446.27
Road-150 Red, 56	$1,424,867.11	$382,874.89	$302,721.64	$88,025.44	$243,322.36	$407,922.78

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]

Folgende Ergebnisse wurden ausgegeben:

	Alle Vertriebsgebiete	Australien	Canada	Nordwest	Südwest	Großbritannien
All Products	$7,938,218.56	$1,096,312.24	$1,474,255.49	$2,042,674.72	$2,238,099.55	$1,086,876.56
Mountain-200 Silver, 38	$1,520,958.53	$248,702.93	$275,052.45	$349,487.01	$435,230.12	$212,486.03
Mountain-200 Silver, 42	$1,392,237.14	$198,127.15	$229,679.01	$361,233.58	$407,854.24	$195,343.16
Mountain-200 Black, 38	$1,861,703.23	$218,024.05	$418,726.43	$486,694.63	$475,521.93	$262,736.19
Mountain-200 Black, 42	$1,702,427.25	$239,137.96	$319,921.61	$420,445.84	$464,046.98	$258,874.87
Mountain-200 Black, 46	$1,460,892.41	$192,320.16	$230,875.99	$424,813.66	$455,446.27	$157,436.31

Wie Sie sehen können, gibt es zwischen beiden Sätzen Unterschiede in den Ergebnissen . Die erste Abfrage hat die Frage von den besten verkaufenden Produkte der obersten fünf verkaufenden Bereiche beantwortet. die zweite Abfrage hat die Frage der größten Verkäufe der obersten fünf verkaufenden Produkte beantwortet.

Hinweise

Unterauswahlen haben folgende Einschränkungen und Beschränkungen:

• Die WHERE-Klausel filtert den Teilbereich nicht.

• Die WHERE-Klausel ändert das Standardelement nur im untergeordneten Cube.

• Die NON EMPTY-Klausel ist in einer Achsenklausel nicht zugelassen, verwenden Sie stattdessen einen NonEmpty (MDX) Funktionsausdruck.

• Die HAVING-Klausel wird nicht in einer Achsenklausel zugelassen; verwenden Sie stattdessen ein Filter (MDX) Funktionsausdruck.

• Berechnete Elemente sind standardmäßig nicht in untergeordneten Ausdrücken zulässig. Diese Einschränkung kann jedoch sitzungsabhängig aufgehoben werden. Dazu wird der SubQueries-Verbindungszeichenfolgeneigenschaft in ConnectionString-Eigenschaft oder der DBPROP_MSMD_SUBQUERIES-Eigenschaft in Unterstützte XMLA-Eigenschaften (XMLA) ein Wert zugewiesen. Eine ausführliche Erklärung des Verhaltens berechneter Elemente finden Sie unter Berechnete Elemente in untergeordneten SELECT-Ausdrücken und Teilcubes abhängig von den Werten von SubQueries oder DBPROP_MSMD_SUBQUERIES.