User:Tszurpicki/sandbox

MDX Functionality versus SQL in a nutshell

SQL (Structured Query Language) refers to only two dimensions, columns and rows, when processing queries. Because SQL was designed to handle only two-dimensional tabular data, the terms "column" and "row" have meaning in SQL syntax. (MSDN Library). MDX (Multidimensional Expressions) returns similar data but it comes from aggregated tables. So while they use similar commands they are executing across different parameters.

MDX is executed on an OLAP (online analytical processing) cube, which is an array of data that is understood in terms of its 0 or more dimensions. OLAP is a computer-based technique for analyzing business data in the search for business intelligence. They borrow aspects of navigational databases, hierarchical databases and relational databases. (2009, p. 177) Basic Statements

The Four basic SQL statements are: •SELECT •FROM  •WHERE and  •ORDER BY

Sample basic SQL query: SELECT column1, column2, …, column FROM table                                                                                                     Sample basic MDX query SELECT axis1 ON COLUMNS, axis2 ON ROWS FROM cube

SELECT and FROM serve exactly the same purpose they do in SQL

SELECT: tells us we want to execute a query and get data results back (Pasumansky, 1998) The basic Multidimensional Expressions (MDX) query is the SELECT statement—the most frequently used query in MDX. By understanding how an MDX SELECT statement must specify a result set, what the syntax of the SELECT statement is, and how to create a simple query using the SELECT statement, you will have a solid understanding of how to use MDX to query multidimensional data.

Sample Relationship Database Here's a simple example. John runs a lemonade and tea stand. One day, he kept track of how many lemonades and teas were bought by men and women. How many questions can we ask against this small set of data? Let's count them: How many lemonades were sold to men? How many lemonades were sold to women? How many teas were sold to men? How many teas were sold to women?

Sample Relationship Database Here's a simple example. John runs a lemonade and tea stand. One day, he kept track of how many lemonades and teas were bought by men and women. How many questions can we ask against this small set of data? Let's count them: How many lemonades were sold to men?

How many lemonades were sold to women?

How many teas were sold to men?

How many teas were sold to women?

That was for one day and a two dimensional table. Using an OLAP cube John can capture this data and his queries in a three dimensional cube will be astronomical.

John could use start with time ie. months then years. Then incorporate weather and profits. If you look at a rubiks cube from one side you have 9 squares. But if you realize it is a Cube with multiple layers and dimensions when you view it in 3d.

The SELECT command is used to specify a dataset, an MDX query must contain information about: The number of axes. You can specify up to 128 axes in an MDX query. The members from each dimension to include on each axis of the MDX query. The name of the cube that sets the context of the MDX query. The members from a slicer dimension on which data is sliced for members from the axis dimensions. (The Basic MDX Query)(http://msdn.microsoft.com/en-us/library/aa216770%28v=sql.80%29.aspx

MDX Commands


 * MSDN Library. (n.d.). Retrieved from Comparison of SQL and MDX: http://msdn.microsoft.com/en-us/library/aa216779(v=sql.80).aspx
 * O'Brien & Marakas. (2009). Online Analytical Processing . :
 * Pasumansky, M. (1998, April). MDX for Everyone. Retrieved from www.mosha.com: http://www.mosha.com/msolap/articles/MDXForEveryone.htm