Tärningens MDX Primer

Välkommen

Välkommen till en snabb översikt av MDX (Multi Dimensional eXpressions).

Tärningens MDX Primer visar dig metodiskt vad MDX är, grunderna bakom det fundamentala konceptet, hur man ställer MDX-frågor, hur man gör MDX-uttryck, hur MDX-operatorerna ser ut, och hur MDX-funktioner fungerar.

Vad är MDX?

MDX är ett frågespråk som tillåter dig att hämta data från multidimensionella databaser på samma sätt som SQL används till att hämta data från relationsdatabaser. Fast där SQL alltid returnerar resultatet längs två axlar - rader och kolumner - returnerar MDX resultatet längs multipla axlar.

MDX kan också användas som ett uttryck (MDX expression). Det används för att definiera och manipulera multidimensionella objekt och data i avsikt att kunna skapa beräknade värden.

MDX konstruerades ursprungligen 1997 av Microsoft, med Mosha Pasumansky som en av dess arkitekter. MDX introducerades kommersiellt 1998 tillsammans med OLAP Services 7.0, senare Analysis Services.

2001 kom Microsoft och Hyperion (numera Oracle) överens om att standardisera MDX i forumet XMLA (XML for Analysis). MDX är alltså ej proprietärt, utan standarden för MDX är en del av specifikationen 'OLEDB for OLAP' (som sponsras av Microsoft).

De flesta OLAP-leverantörer stödjer också MDX, exempelvis Essbase Server från Hyperion, Intelligence Server från MicroStrategy och Enterprise BI Server från SAS Institute, mfl.

Olika leverantörer har skapat olika utökningar av MDX i sina verktyg. Även Analysis Services 2005 innehåller utökningar av MDX jämfört med standarden för MDX. Denna genomgång är baserad på den version av MDX som finns i Analysis Services 2005.

Alla exempel är baserade på en kub med namnet AWDW, skapad från relationsdatabasen AdventureWorksDW, som följer med MS SQL Server.

Även om det finns vissa likheter mellan SQL och MDX är de i botten helt olika.

Grunderna

Kuben är fundamentet för den multidimensionella databasen, som innehåller dimensioner och mätvärden.

En dimension är något som beskriver ett verksamhetsobjekt eller en händelse. Dimensionens namn är normalt ett substantiv.

Mätvärden är kvantitativa enheter som används för analys. Varje mätvärde ingår i en enhet som kallas för mätgrupp, 'Measure group'. Mätgruppen innehåller relaterade mätvärden och varje mätvärde kan bara ingå i en mätgrupp. Objektet 'Measures' innehåller kubens mätvärden och hanteras tekniskt som en (speciell) dimension i kuben.

Normalt finns det en mätgrupp för varje faktatabell i ditt datalager. Mätgrupper används huvudsakligen för navigering i kuben av designverktyg och analysverktyg. De används aldrig i MDX-frågor när man frågar efter mätvärden. De kan dock användas i vissa MDX-funktioner.

En kub innehåller normalt mer än två dimensioner. (Kuben 'AWDW' i exempeldatabasen innehåller 21 dimensioner. I exemplet kommer 3 dimensioner av dessa att användas; Product, Customer och Date.)

Medlemmar

Raderna i en dimension kallas för dimensionens medlemmar. De fält som beskriver medlemmens egenskaper kallas för attribut.

Medlemmarna i en dimension inordnas i en eller flera hierarkier. Mätvärdena i kuben aggregeras på olika nivåer i en hierarki. Även dessa aggregerade nivåer kallas för medlemmar i MDX.

I MDX representeras varje medlem av ett unikt namn eller en unik nyckel. Det unika namnet eller nyckeln används alltså till att identifiera specifika medlemmar. (Det unika namnet för en medlem beror på dimensionsegenskaperna MemberUniqueNameStyle och HierarchyUniqueNameStyle.)

Följande exempel använder formatet Dimension.Hierarki.Nivå.Medlem:

	[Date].[Calendar].[Calendar Quarter].[Q1 2004] 

Hakparenteserna [ och ] används för att omsluta namn på dimensioner, hierarkier, nivåer och medlemmar. Det är inte nödvändigt att använda dem, såvida inte namnet innehåller ett blanksteg, ett tal eller om namnet inehåller ett reserverat ord i MDX. I exemplet ovan är till exempel ordet Date reserverat och måste omslutas av hakparenteser.

Följande tre format är också valida för medlemmen 'Q1 CY 2004':

	[Date].[Calendar].[Q1 2004] 
	[Date].[Calendar].[CY 2004].[H1 2004].[Q1 2004] 
	[Date].[Calendar].[Calendar Quarter].&[2004]&[1] 

I det första exemplet används formatet Dimension.Hierarki.Nivå.Medlem som kan användas så länge det inte finns fler medlemmar; exempelvis om första kvartalet varje år har namnet 'Q1' (istället för 'Q1 2004' etc) kan det formatet inte användas. Då måste man även ange hierarkins nivå.

I det andra exemplet kan man se navigationen tydligt eftersom alla tillgängliga medlemmar i dimensionen används.

I det tredje exemplet används ett annat sätt att specifiera en medlem; genom att använda tecknet & anger man medlemmens nyckel istället.

Celler

Varje kombination av olika dimensioner och mätvärden i kuben kallas för celler. Det innebär att varje cell innehåller data som kan aggregeras på olika nivåer i kuben.

I följande exempel hämtas 'Internet Sales Amount' från kuben 'AWDW' för första kvartalet 2004 i Australien för produktlinjen 'Mountain':

	SELECT Measures.[Internet Sales Amount] on COLUMNS 
	FROM [AWDW] 
	WHERE ( [Date].[Calendar].[Calendar Quarter].&[2004]&[1] , 
		[Product].[Product Line].[Mountain] , 
		[Customer].[Country].[Australia] ) 

Frågan visar att man frågar efter ett värde ur en kub för vissa specifika villkor. Villkoren som anges i WHERE-delen identifierar den önskade cellen unikt, fast allt som gjorts i frågan är att peka ut medlemmar i olika dimensioner, separerade med kommatecken.

Ett MDX-uttryck som pekar ut en cell på det viset kallas för en tupel.

Tuplar

Som förra stycket visade pekar en tupel ut en cell eller en del av en kub. En tupel representeras av en medlem från olika dimensioner, separerade av kommatecken och innesluten av (runda) parenteser, ( och ). Fast en tupel måste inte tvunget innehålla medlemmar från alla dimensioner.

Några exmpel på tuplar från AWDW:

1)	([Customer].[Country].[Australia]) 
2)	([Customer].[Country].[Australia], [Date].[Calendar].[Q1 CY 2004]) 
3)	([Customer].[Country].[Australia], [Date].[Calendar].[Q1 CY 2004], [Product].[Product Line].[Mountain]) 

De två första exemplena innehåller inte medlemmar från alla dimensioner. Därigenom representerar de sektioner av kuben. En sektion av kuben representerad av en tupel kallas för skiva ['slice'] eftersom man skivar kuben utifrån de angivna dimensionerna.

När man använder tupeln ([Customer].[Country].[Australia]) avser man egentligen de 16 underliggande cellerna för olika perioder och olika produkter som finns i kuben. Om man använder det som enda villkor i sin MDX-fråga kommer värdet i de underliggande cellerna att aggregeras. I detta exempel kommer 9'061'000,58 att returneras.

Det spelar inte någon roll i vilken ordning man anger medlemmarna i en tupel. Däremot kan en tupel inte innehålla flera medlemmar från samma dimension, eftersom en tupel identifierar en viss cell.

En tupel som representerar en enda medlem kallas för en enkel tupel ('simple tuple') och måste inte omslutas av parenteser. När en tupel representerar flera medlemmar måste tupeln omslutas av parenteser.

En samling av tuplar bildar ett nytt objekt som kallas för set.

Set

Ett set är en samling av tuplar som defineras av exakt samma dimensioner, både i typ och antal.

Ett set specifieras av krullparenteser, { och }. Se följande exempel:

Exempel 1 visar ett par från exakt samma dimension och är ett korrekt set:

	{([Customer].[Country].[Australia]), ([Customer].[Country].[Canada])} 

Exempel 2 visar ett felaktigt set, eftersom den andra dimensionen skiljer dem åt:

	{([Customer].[Country].[Australia], [Date].[Calendar].[Q1 CY 2004]), 
	 ([Customer].[Country].[Canada], [Product].[Product Line].[Mountain])} 

Exempel 3 visar ett korrekt set med tre dimensioner i varje tupel där urval av land och period är olika:

	{([Customer].[Country].[Australia], [Date].[Calendar].[Q1 CY 2004], [Product].[Product Line].[Mountain]), 
	 ([Customer].[Country].[Canada], [Date].[Calendar].[Q2 CY 2003], [Product].[Product Line].[Mountain]), 
	 ([Customer].[Country].[Sweden], [Date].[Calendar].[Q4 CY 2005], [Product].[Product Line].[Mountain]) 

Exempel 4 visar att ett korrekt set kan innehålla upprepade medlemmar:

	{([Customer].[Country].[Australia]), ([Customer].[Country].[Canada]), ([Customer].[Country].[Australia])} 

Ett set kan innehålla noll, eller flera, tuplar. Ett set med noll tuplar kallas för ett tomt set och representeras med två krullparenteser utan innehåll:

	{ } 

Om en tupel defineras av endast en medlem behöver man inte använda parenteser runt medlemmen. På samma sätt, om ett set definieras av endast en tupel behöver man inte använda krullparenteser runt tupeln. När MDX-frågan exekveras kommer ändå tupeln att tolkas som ett set.

MDX-frågor

Syntax för en MDX-fråga är:

	[ WITH <formula_expression> [ , <formula_expression> ... ] ] 
	SELECT [ <axis_expression> [ , <axis_expression> ... ] ] 
	  FROM <cube_expression> 
	[ WHERE [ <slicer_expression> ] ] 

Du kan kanske tycker att SELECT, FROM och WHERE verkar vara samma som i SQL, men i MDX är de betydligt mer komplexa.

Nyckelorden WITH, SELECT, FROM, WHERE bildar tillsammans med sina följande specifikationer en sats ('clause'). I det föregående exemplet är allt innanför hakparenteser valfritt, medan ... betyder att föregående del kan upprepas flera gånger.

Den enklaste tänkbara MDX-frågan är:

	SELECT FROM <cube_expression> 

Den skulle returnera summan av alla celler som finns i den angivna kuben. För att få ut ett användbart värde måste man specifiera axlar och villkor i SELECT och WHERE.

WITH används bland annat för att konstruera beräknade medlemmar och gås igenom efter att MDX-uttryck förklarats först.

SELECT (specifikation av axlar)

I MDX används SELECT för att hämta en delmängd multidimensionellt data från en OLAP-kub. I SQL används SELECT för att hämta data från en relationsdatabas som kolumner och rader, dvs med två dimensioner.

Om man tänker sig ett två-dimensionellt koordinatsystem brukar man använda X och Y som axlar. I SQL används X för kolumner och Y för rader. Multidimensionellt data behöver däremot returneras med hjälp av fler axlar.

Syntax för SELECT är:

	SELECT [ <axis_expression> [ , <axis_expression> ... ] ] 

Specifikationen av axlar efter SELECT skall motsvara det dimensionella data som efterfrågas. Dessa dimensioner kan kallas för axeldimensioner eftersom data från dessa dimensioner projiceras på motsvarande axlar.

Syntax för <axis_expression> är:

	<axis_expression> := <set> ON <axis | axis_number> 

MDX har förmåga att hantera upp till 128 axlar i SELECT-delen. De första fem axlarna har alias som kan användas: Det är COLUMNS, ROWS, PAGES, SECTIONS och CHAPTERS. Därefter används ett nummer för varje axel.

Se ett exempel:

	SELECT Measures.[Internet Sales Amount] on COLUMNS 
		[Customer].[Country].Members on ROWS 
		[Product].[Product Line].Members on PAGES 
	FROM [AWDW] 

I exemplet används tre axlar, varpå data från dimensionerna Measures, Customer och Product mappas.

I motsats till skivdimensionen använder axeldimensionen data för många medlemmar, inte enbart för enstaka medlemmar.

OBSERVERA! Inga genvägar! I MDX kan du inte hoppa över 'lägre' axlar. Om du vill använda en PAGES-axel måste du först ange både en COLUMNS-axel och ROWS-axel.

FROM (specifikation av kuben)

I MDX används FROM för att avgöra vilken kub som du vill hämta data från. Det liknar FROM i SQL där man anger vilken tabell som man vill hämta data från. Man måste ange FROM i en MDX-fråga.

Syntax för FROM är:

	  FROM <cube_expression> 

Specifikationen av en kub innehåller namnet på en kub, eller på del av en kub, som du vill hämta data från. Till skillnad från i SQL där man kan hämta data från flera tabeller samtidigt kan man bara ange en kub i varje MDX-fråga.

Kuben som anges kallas även för kubkontext ('cube context') för MDX-frågan. Det betyder att allt som specifieras i frågan måste existera i kuben.

Däremot kan man använda MDX-funktionen LookupCube för att hämta data från andra kuber. Om två eller flera kuber har gemensamma dimensionsmedlemmar kan LookupCube användas för att data från den aktuella kubkontexten med hjälp av de gemensamma dimensionsmedlemmarna.

WHERE (specifikation av skivor)

I MDX används WHERE för att begränsa antalet celler som omfattas av en fråga. Det påminner om användningen av WHERE i SQL där man exempelvis kan göra en jämförelse för att begränsa urvalet. Däremot gör man ingen jämförelse för att begränsa urvalet utan istället anger man någon medlem i någon dimension för att visa vilka celler som skall omfattas.

Syntax för WHERE är:

	WHERE { ( <slicer_expression> [ , <slicer_expression> ... ] ] ) } 

Specifikationen av en skiva är utpekandet av en eller flera medlemmar i en eller flera dimensioner.

Se följande exempel för att jämföra MDX med SQL:

	SELECT Measures.[Sales] on COLUMNS 
		[Product].[Product Line].Members on ROWS 
	FROM [AWDW] 
	WHERE ([Product].[Color].[Silver]) 

Det exemplet skulle i SQL motsvaras av frågan:

	SELECT Product.ProductLine, Product.Sales 
	FROM Product 
	WHERE Product.Color = 'Silver' 

I MDX anges en skiva av kuben som omfattar alla celler där en medlems färgattribut i produktdimensionen heter Silver, medan man i SQL gör en strängjämförelse om ett annat fält i raden har värdet Silver.

WHERE finns alltså i både MDX och SQL och påminner om varandra, men fungerar helt annorlunda.

En skiva fungerar som ett filter som utesluter icke-önskvärda medlemmar från frågan. Det som är mer intressant är att skivan inkluderar alla axlar som inte explicit inkluderas i de efterfrågade axlarna.

Defaultmedlemmar som inte inkluderas i frågeaxlarna kommer med i skivorna.

I motsats till axeldimensionen kommer skivan enbart att acceptera MDX-uttryck som utmynnar i en enkel tupel.

När det finns flera tuplar specifierade för en skiva, kommer dessa att utvärderas som ett set och resultatet aggregeras beroende på vilka mätvärden som efterfrågas och deras individuella aggregeringsmetod.

WITH (namn, beräkningar, mm)

Inom affärsvärlden behövs ofta beräkningar som konstrueras inom ramarna för en specifik fråga. I MDX ger WITH-delen möjlighet till den typen av beräkningar. Dessutom är det möjligt att hämta data från andra kuber genom att funktionen LookupCube i WITH-delen.

Typiska beräkningar man skapar med WITH är namngivna set ('named sets'), beräknade medlemmar och beräknade mätvärden.

Dessutom kan man definiera cellberäkningar, ladda en kub i cachen för att förbättra svarstiderna, ändra innehållet i celler genom att anropa externa bibliotek samt andra avancerade koncept som lösningsordning ('solve order') och genomgångsordning ('pass order'), med mera.

Syntax för WITH är:

	[ WITH <formula_expression> [ , <formula_expression> ... ] ] 

WITH-delen ger dig möjlighet till att specifiera flera olika beräkningar inom en sats. Specifikationen av beräkningsformler påverkas av beräkningstyp. Varje formel separeras med ett kommatecken.

Namngivna set

Som du tidigare lärt dig är ett set en samling av tuplar. Uttrycket för varje set kan lätt bli ganska långt och omfattande vilket kan få frågan att verka svårare än den egentligen är.

För att förbättra läsbarheten kan man istället skapa ett namngivet set, som är ett kort och läsbart namn för en mer eller mindre långt set-uttryck.

Tänk på det som ett alias för ett visst set. Man kan skapa fler namngivna set i samma fråga.

Antag att du vill hämta data för kunder i tre europeiska länder, vilka inte finns grupperade i någon hierarki:

	SELECT Measures.[Sales] on COLUMNS 
	  {[Customer].[Country].[Italy], [Customer].[Country].[France], [Customer].[Country].[Germany]} on ROWS 
	FROM [AWDW] 

Frågan är inte överdrivet lång, men om frågan omfattar ytterligare medlemmar och funktioner skall appliceras på samma set flera gånger kommer den att bli svårläst. Istället för att återanvända samma set flera gånger i samma fråga kan man alltså skapa ett namngivet set.

Se följande exempel med ett namngivet set:

	WITH SET [EUROPE] 
	  AS '{[Customer].[Country].[Italy], [Customer].[Country].[France], [Customer].[Country].[Germany]}' 
	SELECT Measures.[Sales] on COLUMNS 
	  [EUROPE] on ROWS 
	FROM [AWDW] 

Syntax för namngivna set är:

	<formula_expression> := SET <set_alias_name> AS [']<set>[']

Nyckelorden för att skapa ett namngivet set är SET och AS.

Aliaset kan vara vilket namn som helst. Oftast innesluts namnet i [ och ], men det är oftast inte nödvändigt. Samma regler som för medlemmar gäller.

Oftast innesluts ett namngivet set inom '-tecken, men det är inte nödvändigt utan snarare en kvarleva från Analysis Services 2000 som krävde det.

MDX-uttryck

MDX-uttryck är partiella MDX-satser som utvärderas som ett värde. De används normalt i beräkningar, för att definiera värden för objekt såsom defaultmedlem eller defaultmätvärde, eller för att definiera uttryck för behörighet som att tillåta eller förbjuda åtkomst.

Ett typiskt MDX-uttryck tar en medlem, en tupel eller ett set som parameter och returnerar ett värde utifrån parametern. Om MDX-uttrycket inte returnerar något, kommer istället ett Null-värde att returneras.

Se följande exempel på MDX-uttryck:

Exempel ett returnerar defaultmedlemmen i hierarkin 'Customer Geography' i dimensionen för kunder:

	Customer.[Customer Geography].DefaultMember 

Exempel två används för att jämföra försäljningen i Australien med olika länder:

	( Customer.[Customer Geography].CurrentMember , Measures.[Sales] ) - 
	( Customer.[Customer Geography].[Australia]   , Measures.[Sales] ) 

Exempel tre visar ett mer komplext MDX-uttryck som kan användas för att tillåta eller förbjuda åtkomst i en kub:

	Count( Intersect( Descendants( IIF( Hierarchize( Exists( [Employee].[Employee].Members , StrToMember
("[Employee].[login].[login].&["+USERNAME+"]") ), POST).Item(0).Item(0).Parent.DefaultMember is
 
					    Hierarchize( Exists( [Employee].[Employee].Members , StrToMember
("[Employee].[login].[login].&["+USERNAME+"]") ), POST).Item(0).Item(0), 
					    Hierarchize( Exists( [Employee].[Employee].Members , StrToMember
("[Employee].[login].[login].&["+USERNAME+"]") ), POST).Item(0).Item(0).Parent, 
					    Hierarchize( Exists( [Employee].[Employee].Members , StrToMember
("[Employee].[login].[login].&["+USERNAME+"]") ), POST).Item(0).Item(0) ) ).Item(0) , 
		[Employee].[Employee].CurrentMember ) ) > 0 

Det sista exemplet är komplext och skall mest visa att man kan skapa tämligen komplexa MDX-uttryck. Exemplet returnerar True eller False med hjälp av en mängd MDX-funktioner och användarens login.

Beräknade medlemmar

Beräknade medlemmar specifieras av MDX-uttryck i WITH-delen av en fråga. Dessa MDX-uttryck exekveras som en utvärdering istället för en ren hämtning av data.

Exempelvis kan årets ackumulerade försäljning skapas som en beräknad medlem utifrån varje månad.

Syntax för beräknade medlemmar är:

	<formula_expression> := MEMBER <member_name> as [']<MDX_expression>['] 
		[ , SOLVE_ORDER = <integer> ] 
		[ , <CellProperty> = <Property_expression> ] 

Nyckelorden för att skapa beräknade medlemmar är MEMBER och AS.

Namnet på den beräknade medlemmen bör vara ett komplett medlemsnamn som inkluderar dimension, hierarki och nivå, på vilken medlemmen kommer att skapas.

MDX-uttrycket bör innehålla ett värde som överensstämmer med medlemmen.

Om SOLVE_ORDER anges skall det vara med ett positivt heltal. Om beräkningen baseras på flera medlemmar kan man med SOLVE_ORDER ange i vilken ordning medlemmarna måste utvärderas.

Även <CellProperty> är en valfri parameter. <CellProperty> kan användas för att ange egenskaper på beräknade medlemmar såsom formattering, bakgrundsfärg, med mera.

Beräknade mätvärden

Alla mätvärden i en kub kan kommas åt via en speciell dimension som kallas för Measures. Beräknade medlemmar kan också skapas i dimensionen med mätvärden.

Faktum är att det är vanligt att beräknade medlemmar skapas i den dimensionen. Beräknade medlemmar i Measures brukar kallas för beräknade mätvärden.

Se följande fyra exempel som åskådliggör hur man skapr beräknade mätvärden i en fråga:

Exempel ett visar ett enkelt beräknat mätvärde, som skillnaden mellan två faktiska medlemmar:

	WITH MEMBER [Measures].[Profit] AS '([Measures].[Sales] - [Measures].[Cost])' 
	SELECT [Measures].[Profit] on COLUMNS, 
		[Customer].[Country].Members on ROWS 
	FROM [AWDW] 

Exempel två använder både ett namngivet set och en beräknad medlem i samma fråga. Frågan sorterar produkterna efter försäljningen i ett namngivet set och sedan beräknas rankningen som en beräknad medlem. Frågan använder funktionerna Order och Rank:

	WITH SET [ProductOrder] AS 'Order( [Product].[Product Line].Members , [Measures].[Sales] , BDESC)' 
	  MEMBER [Measures].[ProductRank] AS 'Rank( [Product].[Product Line].CurrentMember , [ProductOrder] )' 
	SELECT { [Measures].[ProductRank] , [Measures].[Sales] } on COLUMNS, 
		[ProductOrder] on ROWS 
	FROM [AWDW] 

Resultatet blir:

			ProductRank	Sales 
	All Products	1		109'809'274,20 
	Road      	2		 48'262'055.15 
	Mountain  	3		 42'456'731,56 
	Touring   	4		 16'010'837,10 
	Accessory 	5		  2'539'401,59 
	Components	6		    540'248,80 

Exempel tre visar en beräkning av årets ackumulerad försäljning med hjälp av funktionerna Sum och YTD i ett namngivet set. (Om funktionen YTD används utan parametrar används medlemmens defaultvärde på den nivån för att se om medlemmens värde skall inkluderas eller ej.) Funktionen Sum används för att summera ihop alla medlemmar som skall inkluderas:

	WITH MEMBER [Measures].[Cumulative Sales] AS 'SUM( YTD() , [Measures].[Sales] )' 
	SELECT [Measures].[Sales] , [Measures].[Cumulative Sales] on COLUMNS, 
		[Date].[Calendar].[Calendar Semester].Members on ROWS 
	FROM [AWDW] 

Resultatet blir:

			Sales		Cumulative Sales 
	H2 2001   	3'266'373,66	3'266'373,66 
	H1 2002   	3'805'710,59	3'805'710,59 
	H2 2002   	2'724'632,94	6'530'343,53 
	H1 2003   	3'037'501,36	3'037'501,36 
	H2 2003   	6'753'558,94	9'791'060,30 
	H1 2004   	9'720'059,11	9'720'059,11 
	H2 2004   	   50'840,63	9'770'899,74 

Exempel fyra visar en beräkning av den kvartalvisa förändringen av försäljningen första halvåret 2002:

	WITH MEMBER [Date].[Calendar].[%Change] AS 
		100 * ( ( [Date].[Calendar].[Calendar Quarter].[Q2 2002] 
			- [Date].[Calendar].[Calendar Quarter].[Q1 2002] ) 
			/ [Date].[Calendar].[Calendar Quarter].[Q2 2002] ) 
	SELECT { [Date].[Calendar].[Calendar Quarter].[Q1 2002] 
		, [Date].[Calendar].[Calendar Quarter].[Q2 2002] 
		, [Date].[Calendar].[%Change] 
		} on COLUMNS, 
		Measures.[Sales] on ROWS 
	FROM [AWDW] 

Resultatet blir:

			Q1 2002   	Q2 2002   	%Change 
	Sales Amount	1'791'698,45	2'014'012,13	11,038 

MDX kommentarer

MDX har utvecklats mycket sedan dess födelse och det är snabbt gjort att hamna i komplexa uttryck som ovan. Speciellt komplexa uttryck bör kommenteras och MDX erbjuder tre möjligheter:

	// (två framåtvända snedstreck) kommentar till radslut 
	-- (två bindestreck) kommentar till radslut 
	/* (par av snedstreck och stjärna) kommentar mellan */ 

Rekommendationen är att kommentera mycket så att det senare blir enklare att gå tillbaka och minnas resonemanget bakom logiken.

MDX operatorer

I MDX används flera olika operatorer, precis som i SQL och andra språk. En operator är en slags funktion som genomför specifika operationer och som tar parametrar.

Det finns aritmetiska, jämförande, logiska och speciella typer av operatorer.

De aritmetiska operatorerna i MDX är +, -, * och /.

De logiska operatorerna i MDX är <, <=, >, >=, = och <>.

De jämförande operatorerna i MDX är AND, OR, XOR, NOT och IS.

Speciella operatorer i MDX är (, ), {, }, kommatecken och :.

Aritmetiska operatorer

Vanliga aritmetiska operatorer som +, -, * och / finns också i MDX. Precis som i andra språk kan dessa tillämpas på två siffror. Operatorerna + och - kan också användas som unära operatorer, dvs med endast en siffra som parameter, tex +100 eller -100.

Operatorerna +, -, och * kan även tillämpas på set i MDX. Operatoren + används för att skapa en union av två set. Operatoren - används för att skapa differensen emllan två set. Operatoren * används för att skapa en korsprodukt av två set. En korsprodukt innehåller alla möjliga kombinationer av de två seten.

Se följande exempel:

Exempel ett visar en union av två set:

	{[Customer].[Country].[Italy]} + {[Customer].[Country].[France]} 
	= {[Customer].[Country].[Italy], [Customer].[Country].[France]} 

Exempel två visar en korspodukt av två set:

	{Male,Female} * {2002,2003} = {(Male,2002), (Male,2003), (Female,2002), (Female,2003)} 

Jämförande operatorer

MDX stödjer även operatorerna <, <=, >, >=, = och <> för jämförelse. Dessa operatorer använder två MDX-uttryck som parametrar och returnerar True eller False vid jämförelsen.

Exemplet visar en jämförelse:

	Count( [Customer].[Country].Members ) > 3 

I exemplet kommer jämförelsen att returnera True om det finns fler än tre länder i hierarkin Country.

Logiska operatorer

De logiska operatorerna i MDX är AND, OR, XOR, NOT och IS, som utför logisk likhet, olikhet, uteslutande olikhet, negation respektive jämförelse. Dessa operatorer använder två MDX-uttryck som parametrar och returnerar True eller False.

Ofta används dessa operatorer i MDX-uttryck avsedda för cell och dimensionsåtkomst.

Speciella MDX-operatorer - Parenteser, komma och kolon

Vanliga runda parenteser, dvs ( och ), används för att innesluta en eller flera medlemmar för att bilda en tupel. Om en tupel enbart innehåller en medlem, kan man strunta i parenteserna.

Krullparenteser, dvs { och }, används för att innesluta en tupel eller ett samling av tuplar för att bilda ett set. Om ett set enbart innehåller en tupel, kan man strunta i krullparenteserna.

Kommatecken används för att skapa en tupel som innehåller mer än en medlem. Därmed skapas en skiva av kuben. Dessutom används kommatecken för att skapa ett set som innehåller mer än en tupel.

Kolon används för att definiera en kedja av medlemmar inuti ett set. Medlemmarna rangordnas med hjälp av nyckeln eller namnet, beroende på vilken som används vid definitionen av kedjan. Kolon placeras mellan två medlemmar som inte kommer efter varandra för att indikera att alla medlemmar mellan dem även skall inkluderas i samma set.

Exempelvis om länderna Australia, Canada, France, Germany, Italy, UK och USA finns i ett set i den ordningen kommer följande MDX-uttryck att även returnera länder som finns mellan de angivna länderna:

	{Customer.Country.Canada : Customer.Country.Germany} 
	= {Customer.Country.Canada,Customer.Country.France,Customer.Country.Germany} 

MDX-funktioner

MDX-funktioner används för att programmatiskt använda multidimensionella databaser; från att traversera dimensioner till att beräkna numeriska värden baserade på det underliggande datat.

Man kan även sortera tuplar inom ett set, räkna antalet medlemmar i en dimension och konvertera strängar till MDX-objekt, med mera.

Det finns många olika MDX-funktioner som löser många olika typer av problem. Nedan delas de upp i olika kategorier och några grundläggande exempel visas.

Egna funktioner

Man kan också skriva egna funktioner, i tex dotnet eller som lagrade procedurer, vilka kan anropas från MDX.

Exempelvis följande MDX-fråga anropar den egna funktionen MyStoredProc som tar två argument och returnerar ett MDX-objekt:

	SELECT MyStoredProc (arg1, arg2) ON COLUMNS
	FROM MyCube

Anropssätt

MDX-funktioner kan anropas på flera olika sätt:

.Function

Exempel: Dimension.Name returnerar namnet på objektet som refereras, vilket skulle kunna vara en hierarki eller ett medlemsuttryck. Det påminner lite grann om punkt-operatorn i .NET, vilken fungerar på ungefär samma sätt.

	WITH MEMBER Measures.LocationName AS [Customer].[country].CurrentMember.Name 
	SELECT Measures.LocationName ON COLUMNS 
		Customer.Country.members ON ROWS 
	FROM [AWDW] 

Function

Exempel: Funktionen Username används för att få tag i användarens inloggningsnamn. Den returnerar en sträng i formatet "domain name\user name". Funktionen används oftast i MDX-uttryck som hanterar åtkomst av dimensioner eller celler.

	WITH MEMBER Measures.User AS Username 
	SELECT Measures.User ON 0 
	FROM [AWDW] 

Function()

Exempel: Funktionen CalculationCurrentPass() kräver parenteser, men tar ändå inga argument.

Function(arguments)

Exempel: OpeningPeriod( [Level_Expression [, Member_Expression] ] ) är en MDX-funktion som kan ta antingen ett eller två argument. Den används normalt med en tidsdimension, men fungerar givetvis med alla dimensionstyper. Funktionen returnerar den första medlemmen i nivån för Member_Expression. Följande exempel ger "Day 1":

	SELECT OpeningPeriod (Day, [April]) ON 0 
	FROM [AWDW] 

Set-funktioner

Set-funktioner arbetar med set. De tar set som argument och oftast är även resultatet ett set. Några av de mest använda set-funktionerna är Crossjoin och Filter.

Funktionen Crossjoin returnerar alla tänkbara kombinationer av set utifrån de angivna argumenten. Om det finns N set, kommer alla tänkbara medlemmar i alla set att placeras på en axel.

Funktionens syntax:

	Crossjoin ( <Set_Expression> [ , <Set_Expression> ... ] ) 

Se följande exempel:

	SELECT Measures.[Internet Sales Amount] ON COLUMNS 
		Crossjoin( { Product.[Product Line].[Product Line].Members } , { Customer.Country.Members } ) ON ROWS 
	FROM [AWDW] 

Frågan i exemplet kommer att returnera korsprodukten av varje medlem i produktdimensionen med varje medlem i kunddimensionen för Internet-försäljningen.

Följande är några av de första raderna i svaret:

	Product Line	Country		Internet Sales Amount 
	Accessory 	All Customers	604'053,30 
	Accessory 	Australia  	127'128,61 
	Accessory 	Canada     	 82'736,07 
	Accessory 	France     	 55'001,21 
	Accessory 	Germany    	 54'382,29 
	Accessory 	United Kingdom	 67'636,33 
	Accessory 	United States	217'168,79 
	Components	All Customers	(NULL) 
	...		...		... 

Ibland kommer korsprodukten vara null. Antag att en produkt enbart sålts i Australien; då kommer korsprodukten vara null för denna produkt i alla andra länder. Normalt är du inte intresserad av tomma nullvärden. Istället för att hämta allt och sedan kolla om något är null, finns det ett annat sätt att utesluta dem från hämtningen; funktionerna NonEmpty eller NonEmptyCrossjoin.

Deras syntax:

	NonEmptyCrossjoin ( <Set_Expression> [ , <Set_Expression> ... ] [ , <Crossjoin_Set_Count> ] ) 

	NonEmpty ( <Set_Expression> [ , <FilterSet_Expression> ] ) 

Så för att exkludera alla tomma set kan man använda någon av ovanstående funktioner. Fast om man använder NonEmptyCrossjoin måste man sätta filtret på rätt fält eftersom annars defaultfältet används, som är ett annat.

Om man istället använder NonEmpty måste man anropa Crossjoin inuti funktionen. Funktionen NonEmpty är ny i Analysis Services 2005.

Se följande exempel:

	SELECT Measures.[Internet Sales Amount] ON COLUMNS 
		NonemptyCrossjoin( { Product.[Product Line].[Product Line].Members } , { Customer.Country.Members } , 
			Measures.[Internet Sales Amount] , 2 ) ON ROWS 
	FROM [AWDW] 

	SELECT Measures.[Internet Sales Amount] ON COLUMNS 
		NonEmpty( Crossjoin( { Product.[Product Line].[Product Line].Members } , { Customer.Country.Members } ) , 
			Measures.[Internet Sales Amount] ) ON ROWS 
	FROM [AWDW] 

De flesta användare och klientverktyg använder funktionen NonEmptyCrossjoin väldigt mycket.

En annan mycket användbar MDX-funktion är funktionen Filter. Den exkluderar svaret baserat på ett eller flera villkor.

Funktionen tar två argument; ett set-uttryck och ett logiskt uttryck. Det logiska uttrycket tillämpas på varje set och sedan returneras de set som tillgodoser det logiska uttrycket.

Funktionens syntax:

	Filter ( <Set_Expression> , { <Logical_Expression> | [ CAPTION | KEY | NAME ] = <String_Expression> } ) 

Resultatet av ovanstående exempel på Crossjoin är 43 celler. Om man bara är intresserad av celler vars värde är större än ett visst belopp kan man tillämpa funktionen Filter:

	SELECT Measures.[Internet Sales Amount] ON COLUMNS 
		Filter( Crossjoin( { Product.[Product Line].[Product Line].Members } , { Customer.Country.Members } ) , 
			Measures.[Internet Sales Amount] > 2000000 ) ON ROWS 
	FROM [AWDW] 

Frågan kommer att filtrera bort alla rader vars försäljningsbelopp är mindre 2'000'000. Följande rader returneras:

	Product Line	Country   	Internet Sales Amount
	Mountain  	All Customers	10'251'183,52
	Mountain  	Australia 	 2'906'994,45
	Mountain  	United States	 3'547'956,78
	Road      	All Customers	14'624'108,58
	Road      	Australia 	 5'029'120,41
	Road      	United States	 4'322'438,41

Medlemsfunktioner

Medlemsfunktioner arbetar med medlemmar. De kan tex returnera den aktuella medlemmen, föräldern, förfadern [ancestor], barn, sysslingar, nästa medlem, mm. Alla medlemsfunktioner returnerar en medlem.

En av de mest använda funktionerna är ParallelPeriod. Funktionen returnerar en medlem i tidsdimensionen utifrån en annan medlem och efter vissa villkor.

Funktionens syntax:

	ParallelPeriod ( [ <Level_Expression> [ , <Numeric_Expression> [ , <Member_Expression> ] ] ] ) 

Figuren illustrerar hur ParallelPeriod fungerar. Till exempel ParallelPeriod([Quarter],1,[April]) returnerar [January], ParallelPeriod([Quarter],-2,[April]) returnerar [October]:

|-------------------------------------------------------------------------------------------------------------------------------|
|																|
|	Calendar	Calendar	Calendar	Calendar								|
|	Years		Semesters	Quarters	Months									|
|	-----		---------	--------	------									|
|																|
|						    |--	January	   <----							|
|				    |--	Quarter 1 --|--	February	|							|
|				    |		    |--	March		| ParallelPeriod([Quarter],1,[April]) = [January]	|
|		  |----	Semester 1 -|					|							|
|		  |		    |		    |--	April	   =====							|
|		  |		    |--	Quarter 2 --|--	May		|							|
|		  |				    |--	June		|							|
|	Year 1 ---|							|							|
|		  |				    |--	July		| ParallelPeriod([Quarter],-2,[April]) = [October]	|
|		  |		    |--	Quarter 3 --|--	August		|							|
|		  |		    |		    |--	September	|							|
|		  |----	Semester 2 -|					|							|
|				    |		    |--	October	   <----							|
|				    |--	Quarter 4 --|--	November								|
|						    |--	December								|
|																|
|-------------------------------------------------------------------------------------------------------------------------------|

Funktionen använder följande logik för att hitta önskad medlem:

1) Funktionen kontrollerar att det är en tidsdimension. I illustrationen finns hierarkin Calendar som innehåller nivåerna Year, Semester, Quarter och Month.

2) Funktionen letar först upp föräldern till medlemmen som angivits i det sista argumentet, [April], i den angivna nivån, [Quarter], som är det första argumentet. Den konstaterar att medlemmen [April]'s förälder på den nivån är [Quarter 2].

3) Sysslingen till [Quarter 2] hittas sedan med hjälp av det numeriska uttrycket. Ett positivt tal betyder att den eftersökta sysslingen ligger före i tiden jämfört med den aktuella medlemmen. Ett negativt tal betyder att den eftersökta sysslingen ligger senare i tiden. I detta exempel är den eftersökta medlemmen [Quarter 1] eftersom det numeriska uttrycket är 1.

4) Därefter tar funktionen den medlem som har samma position i [Quarter 1] som [April] har i [Quarter 2], dvs [January].

Funktionen ParallelPeriod används till att jämföra mätvärden för olika perioder. Ett typiskt exempel är en användare som vill jämföra försäljningen mellan olika kvartal eller mellan olika år, speciellt om användaren vill göra relativa jämförelser.

Numeriska funktioner

Numeriska funktioner är mycket användbara när man skall definiera parametrar för MDX-frågor eller skapa beräknade mätvärden.

Notera att det finns en mängd statistikfunktioner i denna grupp, bland annat funktioner för standardavvikelse, varians och korrelation. De mest använda är dock funktionerna Count och DistinctCount. Count används för att räkna antalet medlemmar i ett objekt som en dimension, tupel, set eller nivå. DistinctCount används för att räkna antalet medlemmar som är unika i ett set-uttryck.

Funktionernas syntax:

	Count ( <Dimension_Expression> | <Tupel_Expression> | <Set_Expression> | <Level_Expression> ) 

	DistinctCount ( <Set_Expression> ) 

Se följande exempel:

	WITH MEMBER Measures.CustomerCount AS DistinctCount( 
		Exists( Customer.Country.MEMBERS , Product.[Product Line].Mountain , "Internet Sales" ) ) 
	SELECT Measures.CustomerCount ON COLUMNS 
	FROM [AWDW] 

Funktionen DistinctCount används för att räkna antalet unika kunder som köpt något ur produktlinjen [Mountain]. Om en kund har handlat flera gånger räknas ändå kunden bara som en kund.

MDX-funktionen Exists används för att bara räkna de kunder som har handlat via Internet. Exists är ny i Analysis Services 2005.

Resultatet från Exists är ett set med kunder som har köpt Mountain-produkter över Internet. Det totala antalet unika sådana kunder är 9590.

Navigeringsfunktioner

Funktionerna i denna grupp hanterar dimensioner, nivåer och/eller hierarkier och används typiskt för navigering och manipulation.

Till exempel funktionen Level är en nivåfunktion:

	SELECT [Date].[Calendar].[Calendar Quarter].[Q1 2004].Level ON COLUMNS 
	FROM [AWDW] 

Frågan visar en lista med alla kvartal för 2004. Anledningen är att funktionen Level i ovanstående exempel returnerar [Date].[Calendar Year].[Calendar Semester].[Calendar Quarter] som nivå.

Strängfunktioner

För att få tag i namnet på ett set, tupel eller dimension som en sträng, kan man använda strängfunktioner som MemberToStr ( <Member_Expression> ). För att göra tvärtom, dvs omvandla en sträng till ett set, tupel eller medlem, kan man använda funktioner som StrToMember ( <String> ).

Tänk dig ett scenario där ett klientverktyg visar namnet på alla länder. När en användare väljer ett visst land skall verktyget visa försäljningen för det landet. Men eftersom namnet på landet är en sträng i klientverktyget måste det konverteras till motsvarande medlem i dimensionen.

Strängfunktionerna används mest till att ta emot parametrar från användare och konvertera dem till motsvarande MDX-objekt i kuben. Dock är dessa funktioner relativt långsamma och därför bör man undvika dem så långt som möjligt.

	SELECT StrToMember ( '[Customer].[Country].[Australia]' ) ON COLUMNS 
	FROM [AWDW] 

Övriga funktioner

Det finns ytterligare fyra kategorier av funktioner. Subcube och Array har varsin funktion. De sista två kategorierna är logiska funktioner, med vilka man kan göra booleanska (logiska) utvärderingar på MDX-objekt, och tupelfunktioner som arbetar med tuplar.

Dessutom har flera nya MDX-funktioner introducerats i Analysis Services 2005. Du har redan sett NonEmpty och Exists, men det finns ännu fler.

Sammanfattning

Grattis, du har gått igenom grunderna i MDX. Du vet vad celler, medlemmar, tuplar och set är. Du vet att MDX har två olika former, både MDX-uttryck och MDX-frågor.

Du har sett att MDX-frågor till viss del påminner om SQL, men att skillnaderna är stora när du går på djupet. Du vet att MDX-uttryck är enkla men kraftfulla uttryck som tillåter dig att definiera och manipulera MDX-objekt och multidimensionella data.

Du kan WITH, SELECT, FROM, WHERE samt MDX-operatorerna för addition, subtraktion, multiplikation och division. Dessa detaljer är nödvändiga för att kunna börja använda språket.

Du har sett de fyra anropsformerna för funktioner, tittat på elva funktionskategorier, samt sett några detaljerade exempel på några vanliga funktioner som Crossjoin, Filter, ParallelPeriod, MemberToStr och StrToMember.

Nu är det bara att börja använda MDX!

Om MDX Primer

Tärningens MDX Primer skrevs för att det inte verkar finnas några bra (korta) sammanställningar om MDX på svenska. Eller på engelska.

Den primära inspirationskällan är boken "Professional SQL Server Analysis Services 2005 with MDX" av Sivakumar Harinath och Stephen R Quinn, men även flera andra böcker, kompendier och häften. Dessutom ligger det egna erfarenheter inbakade i texten.

Saknas det något? Är något fel? Vill du ha mer hjälp? Mejla mej!

Publicerad 7 oktober 2007 av Christer Tärning

Översättning

engelska	svenska
cube	kub
dimension	dimension
measure	mätvärde
axis	axel
slice	skiva
cell	cell
member	medlem
tuple	tupel
set	set
expression	uttryck
query	fråga
clause	sats
level	nivå
bracket	hakparentes
curly brace	krullparentes
context	kontext
scope	omfång