2

I would like to understand how OLAP-cube operations (i.e. drilling up/down, slicing/dicing and pivoting) and MDX are related. My current guess is that OLAP-cube operations to MDX are like relational algebra to SQL. However, I do not see how some basic features of MDX correspond to OLAP-cube operations. For example, consider the following query on the demo "Sales" cube that comes with icCube:

SELECT {([Ottawa],[2009]), ([United States],[Feb 2010])} on Rows,
       [Measures].members on Columns
FROM [Sales]

How does the use of tuples (e.g. ([Ottawa],[2009])) correspond to an OLAP-cube operation?

AlwaysLearning
  • 7,257
  • 4
  • 33
  • 68
  • MDX returns 'something' for you to look at in an OLAP client. When you drill down in the OLAP client, the client works out some new MDX based on where you are and what you drilled on, and what the hierarchy definitions are in the cube, submits that MDX and displays the result. – Nick.Mc Oct 15 '17 at 23:05
  • @Nick.McDermaid Are you saying that OLAP-cube operators are what visualization tools are expected to implement? In particular, if I send an MDX query directly from my application (e.g. written in C#) and get an object as a result, then OLAP-cube operators are about various ways of accessing that object? – AlwaysLearning Oct 16 '17 at 09:41
  • Can you clarify what you mean by "OLAP-cube operators". Do you mean an OLAP client program? Or do you mean an actual operator (+, -) in the MDX language? – Nick.Mc Oct 16 '17 at 23:08
  • @Nick.McDermaid I meant the *operations* drilling up/down, slicing/dicing and pivoting. See https://en.wikipedia.org/wiki/OLAP_cube#Operations. – AlwaysLearning Oct 17 '17 at 06:44

3 Answers3

2

Yes, "OLAP-cube operations are what visualization tools are expected to implement". MDX is the query language that is executed against a cube that produces a result. OLAP clients generally run MDX against a cube. "OLAP cube operations" as described in that wikipedia are usually as a result of a person performing adhoc analysis against a cube in an client application.

Cube provide a structure and an access language that usually makes these types of operations easier (or at least faster)

How does MDX relate to a "drill down" operation? for example?

Firstly some MDX has already been run and yielded some kind of view of the cube (generally some rows, some columns, and a measure in the intersection although the MDX language syntax doesn't limit to two axes only).

So a person sees this information and decides to drill down on a single item in the row (this item was previously returned by some MDX). So the OLAP client generates some MDX that provides the drilled down view on the item

It might just add a children MDX function to the item in question. Or it might do it some other way. It depends on the client.

Heres some introductory info on how you can eavesdrop on the interactions between an OLAP client (which one? doesn't matter) and a SSAS cube

https://learn.microsoft.com/en-us/sql/analysis-services/instances/introduction-to-monitoring-analysis-services-with-sql-server-profiler

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0

You can think of the MDX query specifying areas or regions of space within a cube - the tuple is a primary way of giving the processor co-ordinates which correspond to the part of the cube you are interested in.

It is the intersection of the co-ordinates and slices you specify that give you a result.

MDX is strongly related to set theory as the main types withing the cube are dimensions, sets, tuples, members etc.

whytheq
  • 34,466
  • 65
  • 172
  • 267
0

An MDX query defines a table and for each of the table cell we've a tuple. In your scenario assuming we've two measures ( Meas1, Meas2 ) :

([Ottawa],[2009],[Meas1])                 ([Ottawa],[2009],[Meas2])
([United States],[Feb 2010],[Meas1])      ([United States],[Feb 2010],[Meas2])

On this cell tuples you might add the WHERE clause, the SubQuery and the defaults that might be different than ALL (not advised). Remember all is a 'special' member that is ignored.

A tuple defines a single measure, Meas1 or Meas2, this will select the 'fact table' with a measure column, usually a numerical value. The other members are used to select rows in the table performing on them the aggregation defined by the measure ( sum , min, max.... ) on all rows defined by the tuple members, Ottawa and 2009 for example. As whytheq explains, you've a lot of transformations to 'play' with members as you would with sets.

This is the simple vision, as you can use calculated members that define a transformation instead of a simple row aggregation (e.g. Difference with previous year) and some aggregations are a bit more tricky ( open, close...).

But if you understand this well you got a perfect ground to understand MDX.

ic3
  • 7,917
  • 14
  • 67
  • 115