Sales Database

1. Sales Database

2. Location Location Atlanta Atlanta Product Product Product Grapes Grapes Grapes Denver Denver Detroit Detroit Cherries Cherries Cherries Melons Melons Melons Sales Apples Apples Apples Pears Pears Pears Q1 Q2 Q3 Q4 Time Sales Cube Tuple can represent a cell or slice of a cube. A set of tuples {} represents an axis of a cube

3. MDX Query SELECT {<axis_specification> } on Axis(0) /On Columns, {<axis_specification>} on Axis(1) /On Rows FROM [<cube_specification>] [WHERE [<slicer_specification>]] Warehouse Data Models

4. MDX Query SELECT {<axis_specification> } on Axis(0) /On Columns, {<axis_specification>} on Axis(1) /On Rows FROM [<cube_specification>] [WHERE [<slicer_specification>]] Warehouse Data Models

5. MDX Query • The way to think when solving a problem is to clearly specify: • First thing is to search for keywords; such as: display or list. Then check if these keywords represent measures; such as: profit, cost, sales, count, average… • If not measures, then it comes from other dimension • The output should be stated as follows: • Select {…} on columns, • {…} on rows • Where … represent one or more dimensions or dimension members. • Measures: are preferably set on where clause • Member that limit results: • Any constraints given in the statement of query on required output are set in the where clause, beside measures; for example: gender= “female”, quarter=Q1 … Warehouse Data Models

6. MDX Query Display all Internet Sales - sales amount, Internet Sales – Order Quantity that are done in the Calendar Year 2003 and 2004 for Europe Sales Territory SELECT {[Measures].[Sales Amount],[Measures].[Order Quantity]} on COLUMNS, {[Order Date].[Calendar Year].&[2003], [Order Date].[Calendar Year].&[2004]} on ROWS FROM [Sales] WHERE [Dim Sales Territory].[Sales Territory Group].&[Europe] Warehouse Data Models

7. MDX Query Required all Reseller Sales- sales amount that are done by all sales employees and show specifically if they were Married or Single “Marital Status”. List them by people promoted by femalereseller category. SELECT {[Dim Employee].[Department Name].&[Sales]} on COLUMNS, {[Dim Employee].[Marital Status].&[M],[Dim Employee].[Marital Status].&[S]} on ROWS FROM [Sales] WHERE ([Measures].[Sales Amount - Fact Reseller Sales],[Dim Employee].[Gender].&[F],[Dim Promotion].[English Promotion Category].&[Reseller])

8. MDX Query Required all Internet_Sales - sales amount and order quantity measures for all unmarried Customers SELECT {[Dim Customer].[Marital Status].&[S]} on COLUMNS, {[Measures].[Sales Amount],[Measures].[Order Quantity]} on ROWS FROM [Sales]

9. Members Function Dim Customer [Customer].[Customer Geography].members Will results: All, Australia, New South Wales, Alexandria, 2450, Coffs Harbour, 2010,…, Canada, …, France, …, Germany,… United Kigdom,…, United States,…

10. Members Function • Remarks: • [ Dim Customer].members • If Dimension_Expression is specified, the Members function returns the set of all members within the specified dimension. • [Dim Customer].[Customer Geography].members • If Hierarchy_Expression is specified, the Members function returns the set of all members within the specified hierarchy. • [Dim Customer].[Customer Geography].[Country].members • If Level_Expression is specified, the Members function returns the set of all members within the specified level. • [Dim Employee].[Gender].[Female]. members • Error: Cannot do members function for a member value • [Dim Product].members • Error, the Dim Product Dimension has more than one hierarchy Warehouse Data Models

11. Members Function Examples This function gets all members of a dimension, hierarchy or level. Its syntax: {Dimension_Expression|Hierarchy_Expression|Level_Expression}.Members Display Reseller_Sales - amount for all employees’ gender and all months (Hint: use members function). select {[dim Employee].[Gender].members} on columns, {[Order Date].[Month Number Of Year].members} on rows from [sales] Where ([Measures].[Sales Amount - Fact Reseller Sales]) Warehouse Data Models

12. Members Function Examples Display Reseller Sales - order quantity for all employees’ gender and all currencies (Hint: use members function). Select {[dim Employee].[Gender].members} on columns, {[Dim Currency].[Currency Name].[Currency Name].members} on rows from [sales] Where ([Measures].[Order Quantity - Fact Reseller Sales]) Warehouse Data Models

13. Members Function Examples Display Internet Sales - order quantity for different product lines requested to be delivered in 2003 SELECT {Measures.[Order Quantity]} ON COLUMNS, {[dim Product].[Product Line].Members} ON ROWS FROM [sales] WHERE ([due Date].[Calendar Year].[Calendar Year].&[2003]) Warehouse Data Models

14. Children Function • Remarks: • This function returns the children of a specified member or level. • Its syntax: • {Member_Expression|Level_Expression}.Children Warehouse Data Models

15. Children Function Examples Display Internet Sales - order quantity for customers living in different Australian states at 2003 (Hint: use children function). SELECT {Measures.[Order Quantity]} ON COLUMNS, {[dim Customer].[Customers Geography].[english Country region name].&[Australia].children} ON ROWS FROM [sales] WHERE ([due Date].[Calendar Year].&[2003]) Warehouse Data Models

16. Descendants Function • Remarks: • It is a very powerful function. It returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels. • Its syntax : • Descendants( { Member_Expression | Set_Expression } [ , ( Level_Expression | Numeric_Expression ) [ ,Desc_Flag ] ] ) Warehouse Data Models

17. Descendants Function Examples • Get the number of orders [Internet Sales - Order Quantity] done by every German state (Hint: use descendants function). • Get all Germany’s states and the quantity of orders done [Internet Sales - Order Quantity] (Hint: use descendants function). • SELECT Descendants([Dim Customer].[Customers Geography].[English Country Region Name].&[Germany], [Dim Customer].[Customers Geography].[State Province Name],self) ON COLUMNS, • [Measures].[Fact Finance Count] ON ROWS • FROM [sales] Warehouse Data Models

18. Descendants Function Examples • Get all countries and the quantity of orders done [Internet Sales - Order Quantity] (Hint: use descendants function). • SELECT Descendants( • [Dim Customer].[Customers Geography], • [Dim Customer].[Customers Geography].[State Province Name],before) ON COLUMNS, • [Measures].[Order Quantity] ON ROWS • FROM [sales] Warehouse Data Models

19. Additional Set Functions: Union, Except, CROSSJOIN Warehouse Data Models

20. Union Function Generates the set of all members of two sets, optionally retaining duplicates. Its syntax Union ({Set1}, {Set2} [, ALL]) The ALL element is an optional element retaining duplicates Warehouse Data Models

21. Union Example Get all the states for both United Kingdom and Australia for Fact Interent Sales Tax Amt, Sales Amount, Order Quantity SELECT Union( Descendants([Dim Customer].[Customers Geography].[English Country Region Name].&[Australia], [Dim Customer].[Customers Geography].[State Province Name]), Descendants([Dim Customer].[Customers Geography].[English Country Region Name].&[United Kingdom], [Dim Customer].[Customers Geography].[State Province Name]), ALL)ON COLUMNS, {[Measures].[Order Quantity],[Measures].[Tax Amt],[Measures].[Sales Amount]} ON ROWS FROM [sales] Warehouse Data Models

22. It worth mentioning that {USA.Children, CANADA.Children} {USA.Children} + {CANADA.Children} Union(USA.Children, CANADA.Children, ALL) are generating the same result, and duplicated members are always retained when an alternate syntax is used. But Union(USA.Children, CANADA.Children) eliminates duplicates Warehouse Data Models

23. Except Function Generates the members not in common between two sets. Its syntax is: Except ({Set1}, {Set2} [, All]) The ALL element is an optional element retaining duplicates Warehouse Data Models

24. Except Function Show the Internet Sales - quantity of orders for all products, with the exception of unknown categories SELECT {[Measures].[Order Quantity]} ON Columns, {Except ([Dim Product].[Product Categories].[All].Children , [Dim Product].[Product Categories].[All].UNKNOWNMEMBER)} ON ROWS FROM [Sales] VERSUS SELECT {[Measures].[Order Quantity]} ON Columns, {[Dim Product].[Product Categories].[All].Children} ON ROWS FROM [Sales] Warehouse Data Models

25. Cross Join Function Can be used to nest multiple dimensions on an Axis, where a tuple is a collection of members from different dimensions. Warehouse Data Models

26. Cross Join Example Get the different combination of gender and marital status of customer for all Australian states for Fact Internet Sales – Sales Amount SELECT Descendants([Dim Customer].[Customers Geography].[English Country Region Name].&[Australia],[Dim Customer].[Customers Geography].[State Province Name]) ON COLUMNS, CrossJoin([Dim Customer].[Gender].children,[Dim Customer].[Marital Status].children) ON ROWS FROM [Sales] where [Measures].[Sales Amount] WHAT IF: we don’t want to show the UNKNOWN ??? Warehouse Data Models

27. Calculated Members and calculated measures Warehouse Data Models

28. Calculated Members • Calculated Members are customized measures or dimension members by combining cube data or by using arithmetic operators, numbers, and/or functions. • Calculated Members Types • Definition Stored in the Cube, but their values computed on the fly. • Definition in the MDX Query, where both definition and data are compiled and computed on the fly. • Example: you can create a calculated member called Pounds that converts Dollars to Egyptian Pounds by multiplying an existing Dollar measure by a conversion rate. Warehouse Data Models

29. Calculated Members Using MDX Syntax With Member <New Member Name> as ‘<Formula>’ Select From Where Warehouse Data Models

30. Example on Calculated Measures Using MDX Calculate the revenue of the Internet Sales, according to this equation (Revenue = OrderQuantity*(UnitPrice-StandardProductCost)) and display it for all Sales Territory Regions categorized by customer gender. With member Measures.Revenue as '[Measures].[Order Quantity]*([Measures].[Unit Price]-[Measures].[Product Standard Cost])' select {[Dim Customer].[Gender].members} on columns, [Dim Sales Territory].[Sales Territory Region].Children on rows From Sales Where Measures.[Revenue] Warehouse Data Models

31. Calculated Dimension Member Using MDX • Get theInternetSales_OrderQuantitycategorized by customer gender and display sales for all customer countries and group countries in Europe and NorthAmerica, in two new members. • where NorthAmerica contains United States and Canada • Europe Contains Germany, France and United Kingdom With member [Dim Customer].[English Country Region Name].[NorthAmerica] as '[Dim Customer].[Customers Geography].[English Country Region Name].&[United States]+[Dim Customer].[Customers Geography].[English Country Region Name].&[Canada]' member [Dim Customer].[English Country Region Name].[Europe] as '[Dim Customer].[Customers Geography].[English Country Region Name].&[France]+ [Dim Customer].[Customers Geography].[English Country Region Name].&[Germany]+ [Dim Customer].[Customers Geography].[English Country Region Name].&[United Kingdom]' Select {[Dim Customer].[English Country Region Name].members, [Dim Customer].[English Country Region Name].[Europe],[Dim Customer].[English Country Region Name].[NorthAmerica]} on columns, {[Dim Customer].[Gender].members} on rows From Sales Where Measures.[Order Quantity] Warehouse Data Models