MDX-Abfrage läuft immer in Timeout - MDX optimieren

Question

Hallo zusammen,

also vorneweg, ich bin jetzt nicht unbedingt der MDX-Crack, daher die Frage. Ich hab von eine Query die auf der kleineren "Modelebene" funktioniert.

Wenn ich das alles jetzt aber auf die Einheiten-Ebene runterbrechen will, dann läuft das alles immer in ein Timeout nach 2 Stunden.
Könnte man die Query evtl. noch irgendwo optimieren?

with member [Measures].[QtyTotal] as [Measures].[QtyInWarranty] + [Measures].[QtyInExtension]
Member [Measures].[QtyInWarrantyLastDayOfMonth] AS
( 
[Dim Calendar].[Calendar].CurrentMember.LASTCHILD
,[Measures].[QtyInWarranty]
) 
Member [Measures].[QtyInExtensionLastDayOfMonth] AS
( 
[Dim Calendar].[Calendar].CurrentMember.LASTCHILD
,[Measures].[QtyInExtension]
) 
SELECT
{ [Measures].[QtyInWarrantyLastDayOfMonth], 
[Measures].[QtyInExtensionLastDayOfMonth], 
[Measures].[QtyInWarranty], 
[Measures].[QtyInExtension], 
[Measures].[QtyStdIn],
[Measures].[QtyStdOut],
[Measures].[QtyExtIn],
[Measures].[QtyExtOut], [Measures].[QtyTotal]} on 0,
Filter(NONEMPTY(CROSSJOIN(
{[v Dim Unit Model 4IB].[Id Unit].[Id Unit].members},
{[Dim Country].[Id Country].[Id Country].members}, 
{[Dim Calendar].[Calendar].[Id Calendar Month].members}
), 
[Measures].[QtyTotal]
),
[Measures].[QtyTotal] > 0) on 1
FROM [InstalledBaseCS_Serial]
WHERE [Dim Calendar].[Year].&[2015]

Vielen Dank für die Unterstützung.

Armin

Answer 1

Hallo Armin,

ohne den Cube zu haben ist schwierig zu sagen, woran es liegt. Ich würde so vorgehen, das ich erst mal die MDX auf ein Minimum reduziere, die Abfrage teste und dann weitere Measures / Dimensions hinzufüge bis es zu dem Punkt kommt, das es sehr langsam wird. Die Filter(...) wurde ich als ersten Kandidaten entfernen, denn die Filter Funktion ist nicht so performant.

Kennst Du übrigens das MDX Studio von Mosha Pasumansky? Ist zum Glück noch als Download verfügbar unter https://www.sqlbi.com/tools/mdx-studio/ .Da gibt es eine Analyse Funktion, die einen auf mögliche Probleme hinweist, bei Deiner Abfrage gibt es 3 davon (ohne verbundenen Cube). Zudem bietet das Tool Abfrage Statistiken, sehr praktisch.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Olaf,

vielen Dank - das Tool kannte ich bis jetzt noch nicht. Sieht aber auf den ersten Blick schon sehr hilfreich aus.
Ich bin auch schon auf die Idee mit dem Verkleinern der Abfrage gekommen.
Prinzipiell funktioniert alles - nur so bald ich in der Query irgendwas mit 

{[v Dim Unit Model 4IB].[Id Unit].[Id Unit].MEMBERS}

einfügen, dann stehts.

Kann das sein, dass das Problem an der Tabelle liegt, da sind halt insgesamt rund 50 Mio. Einträge vorhanden.

Armin

 

Answer 3

Kann das sein, dass das Problem an der Tabelle liegt, da sind halt insgesamt rund 50 Mio. Einträge vorhanden.

Hallo Armin,

das ist mehr als gut möglich, zumal die Dimension auch noch in einem Crossjoin (MDX) mit 2 weiteren Dimension verwendet werden. Haben die beiden anderen Dimension nur je 10 Members, müssen durch das CrossJoin 5 Mrd. Tuples abgearbeitet werden.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Puh - das erklärt einiges.

Ich bin da wohl noch zu sehr in TSQL festgefahren und meinen Joins.

Gibt es denn eine Möglichkeit so riesige Datenmengen aufzusplitten?
Die Länder und das Jahr kann ich ja schlecht minimieren.

Könnte man da nicht vorher irgendwie (ähnlich ner CTE in TSQL) nur die Units für das Jahr 2015 raussuchen und dann das im Crossjoin verwenden?

Oder noch so als Idee - könnte man Modellgruppen und die Einheiten darunter irgendwie "vorgruppieren"?

Answer 5

So was ähnliches wie CTE gibt es in MDX, hat sogar das gleiche Keyword: WITH Member/WITH Set, so wie es in Deiner Abfrage schon verwendet wird.

Was es auch gibt, ist etwas wie eine "abgeleitete Tabelle" (derived table), nennt sich Subcubes:
CREATE SUBCUBE Statement (MDX)
Building Subcubes in MDX (MDX) : ..." By limiting the cube to a subcube, you can improve query performance."

Wenn Du MS Excel als Client für den Cube verwendest und dabei den SQL Profiler laufen lässt, wirst Du sehen, das Excel sehr exzessiv mit Subcubes arbeitet.

Um es mal vereinfach mit einer Inline Abfrage zu zeigen, das folgende Beispiel für den AdventureWorks Dem Cube, das zugegeben inhaltlich eher sinnfrei ist. Diese Abfrage liefert alle Datumswerte, wo das Default Measure nicht leer ist:

SELECT 
  {NonEmpty([Date].[Date].[Date].MEMBERS)} ON 0
FROM [Adventure Works]

Diese Abfrage wird nun als Subcube verwendet und eine weitere Abfrage drum-rum gebaut:

SELECT 
  {[Measures].[Internet Gross Profit]} ON 0
 ,{
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS
     ,[Measures].[Internet Gross Profit]
    )
  } ON 1
FROM 
(
  SELECT 
    {NonEmpty([Date].[Date].[Date].MEMBERS)} ON 0
  FROM [Adventure Works]
);

Auf die Art könntest Du nun auch versuchen zunächst Deine große Dimension so einzuschränken, das nur eine relevante Teilmenge zurück geliefert wird, mit der dann weiter gearbeitet wird.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

Hi Olaf,

super, das hilft mir echt schon mal weiter.

Eine Frage hätte ich noch:

Kann man die Datumswerte, die mit dem Subcube eingeschränkt wurden, auch noch mit in der Abfrage anzeigen?

Praktisch so in der Art (sinnfreie Abfrage ergänzt) 

SELECT 
  {[Measures].[Internet Gross Profit]} ON 0
 ,{
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS
     ,[Measures].[Internet Gross Profit]
     ,[Date].[Date].[Date].MEMBERS
    )
  } ON 1
FROM 
(
  SELECT 
    {NonEmpty([Date].[Date].[Date].MEMBERS)} ON 0
  FROM [Adventure Works]
  where [Date].[Date].[Year].&[2015]} 
);

Answer 7

Hallo Armin,

das geht so

SELECT 
  {[Measures].[Internet Gross Profit]} ON 0
 ,{
    NonEmpty
    (
      [Customer].[Customer].[Customer].MEMBERS * [Date].[Date].[Date].MEMBERS
     ,(
        [Measures].[Internet Gross Profit]
       ,[Date].[Date].[Date].MEMBERS
      )
    )
  } ON 1
FROM 
(
  SELECT 
    {
      NonEmpty([Date].[Date].&[20050101] : [Date].[Date].&[20051231])
    } ON 0
  FROM [Adventure Works]
);

1). Im Subcube hast Du ein WHERE auf die Date Dimension, die auch im SELECT verwendet wird, das ist nicht erlaubt. Deswegen ist der Wertefilter nun im SELECT selbst

2) Um die Datumswerte zu selektieren ist im ersten Argument der NonEmpty ein CrossJoin (kurz mit *) auf die Datumsdimension.

Ergebnis: Mit Datum

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 8

Also erst Mal schon vielen Dank für die Unterstützung bis jetzt. 

Und dein Beispiel ist super - so sollte es bei mir mit den Units und Datum auch funktionieren.

Aber da ich und MDX wohl keine Freunde mehr werden klappts mal wieder nicht. Jetzt kommt die Fehlermledung:"System.OutOfMemoryException" 

Hab das jetzt mal so umgebaut mit Datum

SELECT
{
	[Measures].[QtyInWarranty], [Measures].[QtyInExtension], [Measures].[QtyStdIn],
	[Measures].[QtyStdOut], [Measures].[QtyExtIn], [Measures].[QtyExtOut]
	} on COLUMNS ,
	{
		NONEMPTY
		(
			[v Dim Unit Model 4IB].[Hierarchy].[Id Unit].MEMBERS*[Dim Calendar].[Calendar].[Id Calendar Month].MEMBERS,
			(	
				[Dim Country].[Id Country].[Id Country].members,
				[Dim Calendar].[Calendar].[Id Calendar Month].MEMBERS
			)
		)
	}			
	ON ROWS
FROM
(
  SELECT
   {
		nonempty([Dim Calendar].[Calendar].[Id Calendar Month].&[11624]:[Dim Calendar].[Calendar].[Id Calendar Month].&[11635])
	} ON 0
	FROM [InstalledBaseCS_Serial]
)

Kann ich bei der Subcube-Abfrage nicht auch irgendwie alle Units aus dem Jahr 2015 einschränken und diese dann einfach ausgeben?

Answer 9

nonempty([Dim Calendar].[Calendar].[Id Calendar Month].&[11624]:[Dim Calendar].[Calendar].[Id Calendar Month].&[11635])

Hallo Armin,

OutOfMemory Exception hört sich so an, als sei die Ergebnismenge zu groß.

Hattest Du geprüft, ob der Subcube (s.o9 wirklich nur die gewünschte Teilmenge liefert? Ich hatte mein Beispiel star vereinfacht. Normalerweise gibt man bei Nonempty noch ein Measure/Kalkulation an, das auf Nonempty geprüft wird; lässt man es weg wie auch bei Deiner Abfrage, wird auf das "Default Measure" geprüft, wie ich schon schrieb; welches auch immer das in Deinem Cube ist. Siehe NonEmpty (MDX) für Beispiele.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 10

Hallo zusammen,

bin zwar immer noch nicht am Ende meiner Lösung angekommen. Aber es wird langsam :-)

Momentan komme ich z.B. auf so ein Ergebnis für ein Model mit den dazugehörigen Units:

Mir würde es aber reichen, wenn ich davon auch nur die gelb markierten Lines kriege. Also fehlt mir wohl nur noch irgendwo der Filter.

Meine Abfrage dazu sieht so aus:

WITH 
  MEMBER [Measures].[QtyTotal] AS 
    [Measures].[QtyInWarranty] + [Measures].[QtyInExtension] 
SELECT 
  NON EMPTY 
    {
      [Measures].[QtyStdOut]
     ,[Measures].[QtyInExtension]
     ,[Measures].[QtyStdIn]
     ,[Measures].[QtyInWarranty]
     ,[Measures].[QtyTotal]
    } ON COLUMNS
 ,NON EMPTY 
    {
      NonEmpty
      (
          [DimModel].[ModelNoShort].[ModelNoShort].ALLMEMBERS
        * 
          [DimModel].[ModelUnitMapping].[Id Unit].ALLMEMBERS
       ,[Measures].[QtyTotal]
      )
    } ON ROWS
FROM 
(
  SELECT 
    {
      [v Dim Unit Model 4IB Test].[ModelUnitMapping].[Model No Short].&[ABAB]
    } ON COLUMNS
  FROM 
  (
    SELECT 
      {[Dim Calendar].[Calendar].[Month Name4report].&[2015/01]} ON COLUMNS
    FROM [InstalledBaseCS_Serial]
  )
)
WHERE 
  [Dim Calendar].[Calendar].[Month Name4report].&[2015/01];

Könnte mir hierbei noch jemand helfen?

Danke

Armin