mdx详解。 mondrian.. mdx
Installation o Installation(the embedded version) Download Mondrian-em bedded version and Tomcat o Follow the instructions in install. html o Start tomcat(run the startup. bat) obrOwsehttp://localhost:8080/mondrian-embedded/ erWang(UNSW) 9318 MDX Motivation o Every input/ output in SQL must be relation SELECT Store state, SUM(sales) FROM F. Store. time WHERE F. storekey=Store. storekey AND F.timekey=Time. timekey GROUP BY Store. state CA111,111 IL|222,222 SELECT Store state, Time. quarter, SUM(sales) FROM F, Store. time WHERE F. storekey=Store. storekey AND F. timekey=Time. timekey GROUP BY Store state, Time.quarter CA|Q1|44,444 CA丨Q2|11,111 IL丨Q4|88,888 o Typical reporting/ analytical applications requires cross-tab Q1|Q2|Q3|Q4 IL I Wei Wang (UNSW) 9318 MDX Pivot and Unpivot o SQL can produce cross tab via the Case construct seLect Store, state AS state SUM (CASE WHEN Time. quarter='Q1 THEN sales END) As Q1 SUM (CASE WHEN Time. quarter='Q4' THEN sales END) As Q4 FROM F. Store. Time WHERE F. storekey=Store. storekey AND F.timekey=Time. timekey GROUP bY Store. state o PIvOT has been proposed to help o MDX can support these types of queries more natually and more efficientl SELECT [Time] [Year] [1997]. CHILDREN] ON COLUMNS I[Store. [Store State]. MEMBERS oN RO FROM [Sales] WHERE ([Measures] [Store Sales]) o Who speaks mdX? MS SQL Server 200? Analysis Service(SSAS), Essbase (now owned by Oracle), Mondrian,etc Wei Wang (UNSW) 9318 MDX Basic Concepts o Dimensions and Members a dimension may have several levels Each level has a number of members °Axes o Refers to the dimensions" of a query's result cube o An axis could contain several cube dimensions in com bination easures Attributes(of numerical values) to be aggregated and analyzed o They collectively forms the measures dimension o Default member: the top-level member(usu. ALL) Default measure: the first measure specified in the cube o One can specify the default members/measures or disallow the ALL level in the schema Tuple to define a slice of data from a cube .([Product] [Product Family] [Drink], [Store] [USA] [CA]) Set an ordered collection of zero, one or more tuples usually used to define axis and slicer dimensions °{[Time].[2007].[Q1],[Time].[2007].[Q2]} Wei Wang (UNSW) 9318 MDX Example All Products All Products Product family Drink Food Product name Portsmouth Skinner Washington Johnson Toby the product dimension Members of the product dimension Return a sets of members o [Product] [Product Family] [Skinner] o [Product] [Product Name]. MEMBERS= Portsmouth, Skinner, Washington, Johnson, Toby j o [Product] [Drink]. CHIlDREN= Portsmouth, Skinner, Washington J o [Product] [Drink] [Skinner]: [Washington]= Skinner, Washington I o DESCENDANTS([Product] [Food], [Product Name])= Johnson, Toby Wei Wang (UNSW) 9318 MDX FoodMart °The[ Sales]cube Measures: [Unit Sales], [Store Cost], [Store Sales], [Sales Count] [Customer Count], [Promotion Sales] SELECT [[Measures]. Members ON COLUMNS FROM [Sales Calculated measures: [Profit], [Profit last Period] [Gewinn-Wachstum] SELECT [AddCalculatedMembers( [Measures. Members)] ON COLUMNS FROM [Sales o Dimensions [ Store]:[ Store Country]→[ Store state]→[ Store City]→[ Store Name] [Time]:[Year]→[ Quarter]→[ Month] Example Use Mondrian's workbench to open./demo/FoodMart. xml erWang(UNSW) 9318 MDX MDX is not SQL Basic syntax One of the three ways to write comments select set o on COLUMNS, / block comment * Hset 1 on RoWs / line comment iset n on AXIS (n) WHERE (tuple)// called "slicer dimension" Note o No axis or the Where statement can share any of the same dimensions o JPivot cannot display results with>2 dimensions Key differences between MDX and SQL °“ Cube in, Cube out" for MD o set notation needs to be used after seleCt o From clause can name only one cube o The Where clause describes the slicer axis (i.e, all the axes that is not a query axis)and is filtered by its default members Wei Wang (UNSW) 9318 MDX Example A typical query SELECT [Time] [Year][1997], [Time] [Year][1998] ON COLUMNS [STore] [Store Name]. MEMBERS] ON ROWS FROM [Sales WHERE [Measures] [Store Sales]) Using the level. MEMBERS and member. CHILDREN functions select [[Time] [Year][1997]. CHILDREN ON COLUMNS i[Store] [Store City]. MEMBERS] ON ROWS FROM [Sales] WHERE [Measures] [Store Sales]) utput 19971998 Q1 Q2 Q3 Q4 HQ HQ Store 6 ? ? Store 6 ??? Store 7 Store 7 ? Wei Wang (UNSW) 9318 MDX