DS 715 – Assignment#5 Performing OLAP with SSAS and MDX Objective: Demonstrate use of SSAS interactive features and MDX queries to perform OLAP operations; Estimated time to complete: 40 min reading +45-50 min. solve queries Points: 25/100. Tasks: In this assignment we are going to use perform OLAP operations on a cube called DS715 located in a multidimensional database called SSAS_DS715 that has been defined based on the tables in the database given by the description below: Tb_Supplier(Supp_ID, Name, City, State) Tb_Consumer(Con_ID, Name, City, State) Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging) Tb_Offers(Supp_ID, Prod_ID, Quantity, Price) Tb_Requests(Con_ID, Prod_ID, Quantity, Price) Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price) The dimensions of the cube are: Tb_Supplier, Tb_Consumer and Tb_Product. Measure groups are: Tb_Offers, Tb_Requests and Tb_Transactions. The following dimension hierarchies have been defined within the dimensions: Tb_Supplier: State > City > Name Tb_Consumer: State > City > Name Tb_Product: Product_Packaging > Name Product_Category > Product_Line > Name DS 715 – Assignment#5 Page 2 Performing OLAP Can be done in two ways: - Using the interactive Browse interface to build OLAP queries; - Using the Multidimensional Expressions (MDX) syntax. Interactive OLAP Microsoft SQL Server Analysis Services (SSAS) provides a very intuitive interactive browse feature that is based on the drag-and-drop metaphor. Basic understanding of cubes with dimensions, measures, and hierarchies is sufficient for creating OLAP queries. The queries created can also be scripted which means the equivalent MDX query is generated, which can then be visualized, copied and tweaked for further execution. That makes the feature a useful tool to also learn the actual MDX syntax. After you logged-in into Microsoft SQL Server Analysis Services do the following: 1. Expand the SSAS_DS715 multidimensional database. 2. Expand the Cubes folder. 3. Right-click on the DS715 cube and choose: Browse. 4. Expand the Measures tree and the Tb_Consumer, Tb_Product, Tb_Supplier dimension trees. The result will look like below: DS 715 – Assignment#5 Page 3 5. Drag and drop measure and dimension attributes on the blank center area to build a query. Click right on the blank center area and chose”Clear Grid” to abandon and delete the current query and its result. When finished execute the query by clicking on the “Execute Query” icon; i.e. the third from the right with a red exclamation mark. Note query result will show automatically as soon as a measure attribute is drag on the surface. The result may be like: DS 715 – Assignment#5 Page 4 6. Click on the leftmost top icon, tool tip “Design Mode”, to create equivalent MDX code. The code looks like: DS 715 – Assignment#5 Page 5 7. Carefully study the generated code, remove all optional syntax elements and arrange in easy to read format. 8. Move the code in a new query window and execute. 9. Make changes and create new queries. 10. Repeat the process until you become and MDX expert. Multidimensional Expressions (MDX) Multidimensional Expressions (MDX) is a query language specialized for OLAP that is designed to manipulate and retrieve multidimensional data in Microsoft SQL Server Analysis Services (SSAS). MDX is similar in many ways to SQL, but it allows expressing queries on cube data in terms of dimensions, hierarchies and measures. Those are the higher level abstractions business analysts are comfortable to work with during their OLAP sessions. However, MDX is not an extension of the SQL language and is different from SQL in many ways. In order to create MDX queries that return and format multidimensional data, we need to understand basic concepts in MDX and dimensional modeling, MDX syntax elements, MDX operators, MDX statements, and MDX functions. While it not our objective to present here the details of the MDX syntax, we can get a good sense of how MDX works and how to start building our own MDX queries by looking at some representative samples. For a presentation of MDX check for example the book from APress, Pro SQL Server 2012 BI Solutions, by Randal Root and Caryn Mason (APress, 2012). Check the MDX solutions to the sample queries from Assignment#4. Test and run each query below against the DS715 located in the SSAS_DS715 database and compare the results with the sample queries from Assignment#4. DS 715 – Assignment#5 Page 6 --apex - grand total SELECT { Measures.[Quantity - Tb Transactions], [Measures].[Tb Transactions Count], Measures.[Price - Tb Transactions] } ON COLUMNS FROM DS715 --base SELECT NON EMPTY { [Measures].[Price - Tb Transactions], [Measures].[Quantity - Tb Transactions] } ON COLUMNS, NON EMPTY { ([Tb Supplier].[Name].[Name].ALLMEMBERS * [Tb Consumer].[Name].[Name].ALLMEMBERS * [Tb Product].[Name].[Name].ALLMEMBERS ) } ON ROWS FROM DS715 --aggregates by product SELECT NON EMPTY { [Measures].[Quantity - Tb Transactions], [Measures].[Tb Transactions Count], [Measures].[Price - Tb Transactions] } ON COLUMNS, NON EMPTY { [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS FROM DS715 --aggregates by products sold to consumers in Wisconsin? SELECT NON EMPTY { [Measures].[Quantity - Tb Transactions], [Measures].[Tb Transactions Count], [Measures].[Price - Tb Transactions] } ON COLUMNS, NON EMPTY { [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS FROM [DS715] WHERE [Tb Consumer].[State].&[Wisconsin] --Quantity of milk sold by each supplier from Wisconsin? SELECT NON EMPTY { DS 715 – Assignment#5 Page 7 [Measures].[Quantity - Tb Transactions]} ON COLUMNS, NON EMPTY { [Tb Supplier].[Name].[Name].ALLMEMBERS } ON ROWS FROM [DS715] WHERE ([Tb Product].[Name].[Name].&Milk, [Tb Supplier].[State].&[Wisconsin]) --Quantity of milk sold by each supplier from Wisconsin in the state of --Illinois? SELECT NON EMPTY { [Measures].[Quantity - Tb Transactions]} ON COLUMNS, NON EMPTY { [Tb Supplier].[Name].[Name].ALLMEMBERS } ON ROWS FROM [DS715] WHERE ([Tb Product].[Name].[Name].&Milk, [Tb Supplier].[State].&[Wisconsin], [Tb Consumer].[State].&Illinois) More sample queries: --aggregates by consumers, nulls included and grand total SELECT {Measures.[Quantity - Tb Transactions], Measures.[Price - Tb Transactions] } ON COLUMNS, [Tb Consumer].[Name].ALLMEMBERS ON ROWS FROM DS715 --aggregates by consumers, nulls included, no grand total SELECT {Measures.[Quantity - Tb Transactions] , Measures.[Price - Tb Transactions] } ON COLUMNS, [Tb Consumer].[Name].[Name] ON ROWS FROM DS715 --aggregates by consumers, no nulls, no grand total SELECT NON EMPTY { Measures.[Quantity - Tb Transactions], Measures.[Price - Tb Transactions] } ON COLUMNS, NON EMPTY [Tb Consumer].[Name].[Name] ON ROWS FROM DS715 DS 715 – Assignment#5 Page 8 --aggregates for select consumers SELECT NON EMPTY { Measures.[Quantity - Tb Transactions], Measures.[Price - Tb Transactions] } ON COLUMNS, NON EMPTY {[Tb Consumer].[Name].[Fisher], [Tb Consumer].[Name].[Gray], [Tb Consumer].[Name].[Hammer] } ON ROWS FROM DS715 DS 715 – Assignment#5 Page 9 Given the DS715 cube located in the SSAS_DS715 database solve the following queries (all aggregates will be from the Tb_Transactions measure group): 1) Aggregates by combinations of supplier name and product name? 2) Aggregates by supplier states? 3) Number of transactions between supplier-city-consumer-city pairs? 4) Name of each product sold in Wisconsin and quantity of sales for the product? 5) Quantity of sales aggregated by product and supplier state? 6) Quantity of computer sales aggregated by suppliers in Wisconsin? 7) Quantity of auto sales by each supplier from Wisconsin to consumers in Illinois? 8) Quantity of auto sales by each supplier in Madison to consumers in Illinois? 9) Quantity of each product sold by supplier Bernstein to consumers in Chicago? 10) Quantity of milk sold by supplier Bernstein to consumers in Chicago? 11) (Extra Credit) For each product list quantity sold by suppliers in Madison to consumers in Chicago versus quantity sold by suppliers in Chicago to consumers in Madison (result columns will be: product name, quantity Madison_Chicago, quantity Chicago_Madison? Return: Please post your solutions to the queries above in properly formatted MDX syntax (each clause on its own line and with capitalized keyword and operators) in the D2L dropbox created for this assignment. Robert Dollinger March 28,