Part 2: MDX Code Development for Beginners

Welcome back to part 2 of MDX Code Development for Beginners. In part 1, we looked directly at the basic SELECT FROM clause in T-SQL and converted it to the SELECT ON COLUMNS ON ROWS FROM which is the equivalent in MDX. In part 2 we will be looking into the different BASIC filters that can be introduce into the SELECT statement in comparison to T-SQL “WHERE” clauses. To start let’s look at the adventure works database and write a query to bring back the SUM of our Internet Sales and the sub categories for the sales without any filters.

Here are our results: As you see in order to tie the tables together I needed to join 3 tables to

get my desired result set of Sub Categories and Internet Sales. Now we can look at the same result set from the AdventureWorks data warehouse using the following SELECT Statement: 

Here is our desired result set: Same result set using less Joins. Now we get to have some fun

adding the WHERE clause in T-SQL and then translating it to MDX. The next code snippet depicts internet sales for the year 2006. Adding the WHERE Clause makes the results from the

AdventureWorks DW filter down to exact matches of ROAD BIKES and MOUNTAIN BIKES because based on the data they are the only subcategories sold in 2006: How do we do this in MDX?  Let Start with the basic MDX Query to bring back Internet Sales Amount and the subcategories. From this we can

see all internet sales which is my COLUMNS and I define my Subcategories as my ROWS and I define that I want to isolate my query to just those members. So obviously we brought back

internet Sales for all years captured into the cube.  So now we need to understand the WHERE clause and how to use it properly in MDX. The “WHERE” clause in MDX is used to define another slicer and limit the attributes from the defined slicer. The slicer used in the “WHERE” clause cannot be the same slicer used when defining your rows hence why I use the date dimension.  The MDX query below depicts the WHERE clause and syntax: The results are

below: Dan_King_MDX_Code_Part_2_-_Image_5.png Take a look at the SELECT statement used in MDX.  Notice a lot of joins isn’t needed because cubes are built for these types of aggregations and being able to look at trends and review different analytics.  One thing to notice is all the NULL values in the result set which we will discuss in the future. Thanks for reading part 2 of my MDX series.  To recap part 2 of the series we looked at the OLTP relational model, the Data Warehouse, and browse the cube using MDX.  We went from T-SQL with a few joins to using measures and attributes to slice data by.  In part three we will take a look at eliminating NULL values and the different functions out there to also filter your result set.