Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Road Trip with MDX Series (Intro to MDX Structures)

  • 30 December 2009
  • Author: MikeMollenhour
  • Number of views: 7474
  • 0 Comments

Rest Stop #1 MDX Journey Location Home City of Jacksonville Florida

Introduction to MDX

 

You know today’s cars can be somewhat intimidating with all their electronic devices this is also true of SQL MDX. Most of us are familiar with the aspect of driving the car while few of us (especially here in Florida) know how to steer on ice. Well I’ve been told that driving on ice gets better with practice and this is what this Series is about “Real World Practice” with MDX. To start we need a full understanding of dimensional modeling along with measures (fact tables).

Dimensions

It is very important we understand the relationship between these. In our trip we will talk about many dimensions like locations visited along the way. Our Rest Stop would be a location. This location would have many attributes like Country, State, City, and Address. The Previous attributes form what we know in data warehousing as a Natural Hierarchy. This means that Like in our driving adventure we must first be in the country to be in the State and then we must be in the State to be in the City and on and on until we get to our lowest grain of the dimension the actual rest stop address. Obviously in life we could even go further to men and women’s restrooms, picnic table numbers etc. This grain is decided on reporting needs, for example if we just want to keep track of visitors we could put a turn style at the entrance then we really don’t have the information of who visit the men’s room and who visited the women’s room. If we wanted to get more granular we could put turn styles at the entrance of each restroom or stall etc… While this is a much exaggerated example it does show how figuring out the grain of a dimension is very important. Another example of a dimension could be a vehicle dimension; this may hold different vehicles attributes such as type (car, bus, truck, etc) , color(green, red, etc), doors (2,4) and the list goes on and on. If we have a hierarchy defined as Type>color>doors this would be an unnatural hierarchy since I can have a red VW and also a Red Bus. Red is not specific to one model.

Now for Measures:

Measures (aka Facts)

So now that we have covered dimensions lets cover measures. Measures are typically numeric values that can be rolled up (aggregated) by several different slices of dimensions. To keep our analogy if we had a turn Style at each restroom in the rest area a measure could be the count of individuals going into each restroom. Another measure may be the amount of money collected in the vending machine. If we had someone doing a survey as you entered the rest stop we could also track the count of vehicles.

Grains

While it may seem here like I’m steering off and talking about Granola bars on the trip, I’m actually referring to grains within fact and dimension tables. The Grain refers to the lowest level of record we are storing. Take for example we go to the drive-in movie (only one left here in JAX) and we watch an old movie from the 60’s. As long as this movie was not retouched we could quickly see a difference in the quality. It may seem as the frame are jumping from shot to shot. If we examine this difference much of it could be attributed to the capture rate or shutter speed as it was in the past. Now we have digital video recorders that can record down to milliseconds. The downfall to recording at this granular level much as it is in a datawarehouse is the storage it will take and sometimes the performance you will get. So to relate this back to dimension and facts we can “record” at whatever grain we wish. Our Date dimension could be stored at the day or hour or second level if we really want to get crazy. Grain also applies to our fact table I can record visitors to the rest stop at different levels maybe I only check that turnstyle for counts and record them daily this measure would be at the day level or grain. In a Data Warehouse scenario quickly we can see, however, that this would probably not be out lowest grain in the measure group (fact table). In this scenario we would at least have some type of location probably and again this location could be at different granularity according to what level we are in the hierarchy (Country, State, City, and Address).

Tuples and Mathematics bringing it all together

In math we display a dimension as an axis x, y, z etc. This can be illustrated and charted as:

 

 Looking  at the Graph above we could find a particular point by specifiying the x and y axis as follows (1,3). The (1, 3) notation is a coordinate which points to a particular point in the x-y axis. To bring this back to MDX let’s assume the x-Axis is the Geography Dimension and the y-axis is the Date Dimension. This notation of (1,3) could just as well be Jacksonville FL and “10/23/2009” which in MDX is a Tuple represented by dimensional attributes like ([Geography].[City].&[Jacksonville].&[Florida], [Date].[Fiscal].[Date].&[20091023]) . The details of the syntax will be covered in subsequent rest stops but for now the important thing to note is that a tuple is coordinates that can point to a specific place in a cube. While we only showed a two dimensional representation here for visual purposes it is possible to have a z axis and really an unlimited number of axis. We could just as well have a tuple like([Geography].[City].&[Jacksonville].&[Florida], [Date].[Fiscal].[Date].&[20091023], [Vehicle].[Type].&[Bus]) which would notate busses in Jacksonville on this date. Of course since we are only recording from rest stops this would also be assumed.

Sets

So now we know how to represent a point in space from a formation of dimensions with a tuple, lets carry this one step further. What if I want to know about both busses and cars on this day. This would be very similar to a union in Transact SQL. So how do we get this behavior in MDX? Glad you asked. We need to notate this as a set which would be a combination of tuples here’s an example : {([Geography].[City].&[Jacksonville].&[Florida], [Date].[Fiscal].[Date].&[20091023], [Vehicle].[Type].&[Bus]), ([Geography].[City].&[Jacksonville].&[Florida], [Date].[Fiscal].[Date].&[20091023], [Vehicle].[Type].&[Car])} . Note that with sets we add the curly brackets “{}” around them to notate that it is a set of tuples, the tuple representation remains the same.

Conclusion Rest Stop 1

This concludes our trip to the Jacksonville rest stop where we covered Dimensions, grains, measures and tuples. Now it is on to Atlanta and I hear it can get a little crazy up there, so buckle you seat belt and we will start our journey with the knowledge we have so far and start writing some MDX.

Print
Categories: Query Languages
Tags:
Rate this article:
5.0

MikeMollenhourMikeMollenhour

Other posts by MikeMollenhour

Please login or register to post comments.