Presentation Transcript

1. MultiDimensionalExpressions by Orçun YILDIZ
2. Multi-dimensionalExpressions Relationaldatabases SQL as a querylanguage OLAPdatabases  MDX as a querylanguage
3. Shorthistory Itwasfirstintroduced as part of the OLE DB for OLAP(ODBO)* specification in 1997fromMicrosoft. Withtheinvention of XML forAnalysis**, became de facto standart for OLAP. *ODBOisthestandard API forexchanging data between OLAP server and a client. **is an industrystandardfor data access in analtyicalsystems.
4. Who is using? Vendors on clientside Microsoft Excel Panorama Software CrystalReports Cognos Vendors on server side NCR Oracle SAS SAP Terradata
5. Somegoodfeatures: A robust set of functions: makesaccessingmulti-dimensional data easier &moreintuitive Users can retrievemulti-dimensional data structureswithnearlyanynumber of dimensions. Flexibility & Powertoprocessmulti-dimensional data  becomechoiceforpractical,results-basedbusinessintelligence
6. Analogywith SQL Some say that MDX is to multi-dimensional databases, what SQL is to relational databases. This is a good analogy EXCEPT: MDX does not havethe data definitioncapabilities(DDL) that SQL has. However, it doessupport data manipulationcapabilities. (DML) Whereasthereturnedresultwith SQL is a table, thereturnedresultwith MDX is a data cube.
7. CubeConcepts Cubesarekeyelements in OLAP whicharesubsets of data from data warehouse, organizedandsummarizedintomultidimensionalstructures.
8. MDX Data Types Scalar Dimension/Hierarchy Level Member Tuple Set
9. Scalar It is either a numberor a string. e.g. 7,”CMPE”… It can be returnedby a MDX function e.g. Aggregate(number),UniqueName(string)
10. Dimension/Hierarchy It is theattributeinformation in a cube. e.g. Time,Product,Gender Dimensionsassumedto be mutuallyindependent. Hierarchy is a dimensionhierarchy of a cube. e.g. Time.Fiscal,Time.Calendar
11. Level It is a level in a dimensionhierarchy. e.g.Time.Fiscal.Month,Time.Calendar.Quarter Eachlevel in a dimension is of a finergrainthanitsparent.
12. Member It is a member in a dimensionhierarchy. e.g. Time.Fiscal.Year.2006 Allmembersarespecificto a hierarchy.
13. Tuple It is an orderedcollection of oneormoremembersfromdifferentdimensions. e.g. (Time.Fiscal.Month.August,Customer.ByGeopraphy.Turkeit)
14. Set It is an orderedcollection of tupleswiththesamedimensionality. e.g.{(Measures.Sales,Time.Fiscal.2006),(Measures.Sales,Time.Fiscal.2009)}
15. Final RemarkAbout Data Types Measuresarespecialcase of dimensionsandtheyholdnumericalvalues. e.g. Amount of licences,cost,price…
16. Examples Dimension [Geography] Hierarchy [Geography].[Geo] and [Geography].[Economy] Levels [Geography].[Geo].[Continent], [Geography].[Geo].[Country], [Geography].[Geo].[City], [Geography].[Economy].[Partnership], [Geography].[Economy].[Country]
17. Examples Each level of a given hierarchy will 'produce' a member with a parent-child relationship. [Geography].[Geo].[Europe]: is a continent (withallEuropeancountries as children) [Geography].[Geo].[Europe].[Switzerland]: is a countrywhoseparentmember is Europe) [Geography].[Geo].[Europe].[Switzerland].[Geneva]: is a city (whoseparentmember is Switzerland)
18. SelectStatement SELECT [Measures].Members ON COLUMNS, [Geography].[Geo].Members ON ROWS FROM [Sales] *The ON keyword is used to organize the selected data. *MDX utilizes the concept of axes. The data selected from a dimension can be put on a axis. *In the query above, we selected the members of the [Measure] dimension and put them on axis 0, or columns. And we selected the members of the [Geography] dimension and put them on axis 1, or rows.
19. SelectStatement However, a SELECT is not limited to two axes. We could have columns, rows, pages, chapters, and sections. A query that uses axis 1 must also use axis 0. A query that uses axis 2 must also use axis 1 and axis 0. You cannot skip an axis in a query. In the FROM clause, we specify the cube which will be used for querying the data specified in the SELECT clause.
20. Concept of DefaultMembers  SELECT FROM [Sales] Is thisvalid MDX query?
21. Concept of DefaultMembers SELECT FROM [Sales] Is thisvalid MDX query? YES
22. Concept of DefaultMembers MDX uses the concept of a default member. Each hierarchy has a default member. This is usually the top of the hierarchy. If a member from a dimension is not specified, then the default member is automatically taken. Abovequery, will return the default [Measures] value indexed by the default members from all the other hierarchies
23. WHERE(a.k.a Slicer) Clause SELECT [Measures].Members ON 0, [Geography].[Geo].Members ON 1 FROM [Sales] WHERE [Time].[Calendar].[Year].[2010]
24. WHERE Clause It is used in ordertoreducethescope of a query.When it is not specified in a query, entirecubespace is takenintoaccount. In the query above, only that section of the cube where the year is 2010 is taken into account as the scope of the query. The rest of the cube is ignored. This is why the WHERE clause is called the 'slicer' - because it takes a 'slice' from the entire cube and performs calculations only on that slice of data.
25. FROM as a Slicer Besides the WHERE clause, MDX provides a second method of slicing the cube. This is the FROM clause. And the FROM clause is not limited only to a cube. A SELECT (a.k.a. sub-select, sub-query) can be used instead: SELECT [Measures].Members ON 0, [Geography].[Geo].Members ON 1 FROM (SELECT [Time].[Calendar].[Year].[2010] ON 0 FROM [Sales]) This is equivalent to the previous query
26. HierarchyNavigation MDX is locationaware. Consider a cell in the cube which contains the sales of MagicDrawlicenses in the year 2010. This cell is represented by the tuple: ( [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].[2010] ) { [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].PrevMember } Itnavigatestothepreviousyearwhich is 2009 since MDX automaticallyreferencedthemember 2009, whichcomesbefore 2010 in yearattribute.
27. CommonCalculations Average Contributionto Total PeriodtoDate
28. Average What are the average sales of Products, per quarter, for any given year? In MDX, any given year will be represented by [Time].[Year].CurrentMember . Since we know that quarters come under years in a hierarchy of time, we can find the quarters which come under a particular year by using the descendants function: Descendants( [Time].[Year].CurrentMember, [Time].[Quarter] )
29. Continued Averaging the sales across these quarters is also easy: Avg( Descendants( [Time].[Year].CurrentMember, [Time].[Quarter] ), [Measures].[Amount] )
30. Contributionto Total What percentage of sales is allocated to one particular product as compared to all products? First, get the sales of the particular product: ( [Measures].[Amount], [Product].[Prod].CurrentMember) And now, get the sales of the parent. This can be done by: 2. ( [Measures].[Amount], [Product].[Prod].Parent ) Calculating the contribution ratio is simply a matter of dividing the first figure by the second.
31. PeriodtoDate What are the sale figures of a particular product from the beginning of the quarter up to a specific date?
32. PeriodtoDate The current date can be represented by [Time].[Calendar].CurrentMember. How do you get the date for the first day of the current quarter? By looking at the time hierarchy. We know that days roll into months and months roll into quarters. So, [Time].[Calendar].Parent.Parent, would take us to the Quarter node of the current date. Now, to get to the first day of this quarter, we simply have to use the descendants function and first child function as shown below: Descendants( [Time].[Calendar].Parent.Parent, [Time].[Calendar].[Day] ).FirstChild
33. PeriodtoDate To get the sum of sales from the first day of a quarter up to a specific date, do as follows: Sum( { Descendants( [Time].[Calendar].Parent.Parent, [Time].[Calendar].[Day] ).FirstChild : [Time].[Calendar].CurrentMember }, [Measures].[Amount] )
34. CalculatedMembers Calculated members allow you to define an MDX expression/formula that is attached to a new dimension's member. Eithernew [Measures] beingcalculatedor a calculatedmember(newdimensionmember)
35. CalculatedMembers WITH MEMBER [Measures].[Market Share] AS ([Measures].[Amount]) / ( [Product].CurrentMember.parent,[Measures].[Amount] ), FORMAT_STRING = "Percent" SELECT [Product].[icCube].Children ON 0, [Measures].[Market Share] ON 1 FROM [Sales]
36. CalculatedMembers Member Identifier: This specifies the name of the calculated member, the dimension to which the calculated member belongs, and its hierarchical positioning. Remember that [Measures] is also a dimension in MDX. The main part of the definition is the formula which tells us how the results are derived. Optional properties can provide additional information such as the display format of the values.
37. Examples What is sales ofproduct 1 and product2 in Europe and Asia over years 2009 and 2010? SELECT   { Continent.[Europe], Continent.[Asia] }      ON AXIS(0),   { Product.[product1], Product.[Product2] }   ON AXIS(1),   { Years.[2009], Years.[2010] }      ON AXIS(2) FROM Sales
38. Examples Forcalculatingprofit: WITH   MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost' SELECT   Products.MEMBERS ONCOLUMNS, Year.MEMBERS ON ROWS FROM Sales WHERE ( Measures.Profit)
39. Examples SELECT { Years.[2009], Years.[2010] } ON COLUMNS, Regions.Continent.MEMBERS       ON ROWS FROM Sales WHERE ( Products.[Product Group].[product1],Customers.[customer1] ) This query shows the sales of product1’s to customer1 in years 2009 and 2010 by all continents.
40. References www.iccube.com/support/documentation/mdx_tutorial http://www.microsoft.com/msj/0899/mdx/mdx.aspx http://en.wikipedia.org/wiki/MultiDimensional_eXpressions