快速打造Web-based高效能决策支援系统进阶开发技巧深度探讨

1、張智凱張智凱恆逸資訊教育訓練處恆逸資訊教育訓練處TimeProductLocationDimensionQ4Q1Q2Q3GrapesApplesMelonsCherriesPearsAtlantaDenverDetroitMemberCellTimeStartQuarter 1July 1EndSeptember 30Quarter 2October 1December 31Quarter 3January 1March 31Quarter 4April 1June 30PropertiesLevelsAxes 以逗點區以逗點區隔隔SetSELECT Gender.MEMBERS ON COL

2、UMNS, Measures.MEMBERS ON ROWS FROM SalesSELECT Customers.CHILDREN ON COLUMNS, Gender.MEMBERS ON ROWS FROM SalesSELECT Education Level.MEMBERS ON COLUMNS, Marital Status.CHILDREN ON ROWS FROM Sales WHERE Measures.Store SalesSELECT Education Level.MEMBERS ON COLUMNS, Marital Status.CHILDREN ON ROWS F

3、ROM Sales WHERE (Gender.M, Measures.Store Sales)SELECT Time.1997.Q1.CHILDREN,Time.1997.Q2.5 ON ROWS, Price Destroyers, Price Slashers, All Promotions ON COLUMNS FROM Sales WHERE (Gender.M, Customers.USA.WA, Measures.Store Sales)Select Descendants (1997,Month,BEFORE) ON COLUMNS, Store Sales, Store Co

4、st ON ROWS FROM SALESSelect Profit on Columns, TopCount(Product Category.Members , 5, Profit) on rows from salesSELECT Product.CHILDREN ON COLUMNS, TopCount ( Descendants ( Customers, Name ), 10, MEASURES.Store Sales Net) ON ROWS FROM SalesWHERE (Time.1997.Q4.12, MEASURES.Store Sales Net)Select Cust

5、omers.All Customers.USA.CHILDREN ON COLUMNS,(GENDER.F, Marital Status.M), (GENDER.F, Marital Status.S), (GENDER.M, Marital Status.M), (GENDER.M, Marital Status.S) ON ROWS FROM Sales WHERE (Store Sales)Select Customers.All Customers.USA.CHILDREN ON COLUMNS, CROSSJOIN(GENDER.CHILDREN, Marital Status.C

6、HILDREN) ON ROWS FROM Sales WHERE (Store Sales)Select CrossJoin (1997.Children, Food,Drink) ON COLUMNS, CrossJoin (Store Type.Children, Store Sales,Store Cost) ON ROWS FROM SALESSelect Profit on Columns, UNION(TopCount(Store Name.Members, 5, (Profit,1997.Q1), TopCount(Store Name.Members,5,(Profit,19

7、97.Q2) on Rows from SalesSelect Profit on Columns, INTERSECT(TopCount(Product Name.Members, 10, (Profit,1998.Q1), TopCount(Product Name.Members,10 ,(Profit,1998.Q2) on Rows from SalesSELECT ORDER( Education Level.MEMBERS, Promotions.All Promotions, ASC) ON COLUMNS, NON EMPTY ORDER ( Promotions.MEMBE

8、RS, Education Level.All Education Level, DESC) ON ROWS FROM Sales WHERE (Store Sales)WITH MEMBER Time.97 to 98 AS Time.1998 - Time.1997 MEMBER Measures.Profit AS Measures.Store Sales - Measures.Store CostSELECTMeasures.Store Sales, Measures.Store Cost, Measures.Profit ON COLUMNS,Time.1997, Time.1998

9、, Time.97 to 98 ON ROWS FROM SalesWITH MEMBER Time.97 to 98 AS Time.1998 / Time.1997, SOLVEORDER = 1 MEMBER Measures.Profit AS Measures.Store Sales - Measures.Store CostSELECTMeasures.Store Sales, Measures.Store Cost, Measures.Profit ON COLUMNS,Time.1997, Time.1998, Time.97 to 98 ON ROWS FROM SalesW

10、ITH MEMBER MEASURES.PercentGrowth AS (Store Sales) / (Store Sales, Time.PrevMember), FORMAT_STRING = #.00%SELECT DESCENDANTS ( Time, Month ) ON COLUMNS, Store Sales, PercentGrowth ON ROWSFrom SalesWITH MEMBER MEASURES.PercentGrowth AS IIF ( IsEmpty (Time.PrevMember),1, (Store Sales) / (Store Sales,

11、Time.PrevMember), FORMAT_STRING = #.00%SELECT DESCENDANTS ( Time, Month ) ON COLUMNS, Store Sales, PercentGrowth ON ROWSFrom SalesMetadataCubeDefsDimensionsHierarchiesLevelsMembersCellsetAxesPositionsMembersCellsConnectionDataCubeDefsDimensionsHierarchiesLevelsMembersCellsetAxesPositionsMembersCells

12、ConnectionSales Of Computers Nelson White USA USA USA_North USA_North Seattle Boston USA_South Japan Seattle Boston USA_South Japan Jan 00 10 20 30 40 50 60 70 Feb 01 11 21 31 41 51 61 71 1998, Q1 Mar 02 12 22 32 42 52 62 72 1998, Q2 03 13 23 33 43 53 63 73 1998, Q3 04 14 24 34 44 54 64 74 Oct 05 15

13、 25 35 45 55 65 75 Nov 06 16 26 36 46 56 66 76 1998, Q4 Dec 07 17 27 37 47 57 67 77 CellsAxisObjectPosition MemberServerDimensionsLevelsMeasuresMDStoresMDStoresDatabases.DimensionsLevelsMeasuresMDStoresContain Cubes.DimensionsLevelsMeasuresMDStoresThat contain Partitions.DimensionsLevelsMeasurescont

14、ain Aggregations.DSOcube.Process fullProcessServer1. Begin trans2. Create shadow3. Load data4. Commit transRDBMSprocess threadsquery threadsqueriesreplaceDSOServerfactsRDBMScube.Process processSuspend“loader”cube.Process processResumequeriesDSOServerqueriesAsia Executives:Allow everything related to

15、 Japan and SingaporeAncestor(Sales.CurrentMember, Country).Name “India”Ancestor(Sales.CurrentMember, Country).Name = “Japan” OR Ancestor(Sales.CurrentMember, Country).Name = “Singapore”Japan Managers:Allow actual and planned sales in JapanAllow only actual sales in Singapore(Measures.CurrentMember.Name = “Actual Sales” AND Ancestor(Sales.CurrentMember, Country).Name = “Singapore”) OR (Ancestor(Sales.CurrentMember, Country).Name = “Japan”)Japan Manager