Microsoft Office Tutorials and References
In Depth Information
On OLTP databases, you use the T-SQL language to perform the transactions, while for OLAP databases you
use MDX queries instead to parse the OLAP data structures (which, in this case, are cubes). And, finally, you use
OLAP/MDX for BI analysis purposes.
What is ETL doing in Figure 1-8 ? As we noted, ETL is the process used to migrate an OLTP database to an OLAP
database. Once the OLAP database is populated with the OLTP data, you use MDX queries and run them against the
OLAP cubes to get what is needed (the analysis).
Now that you understand the transformation, let’s take a look at MDX scripting and see how you can use it to
achieve your goals.
MDX stands for Multidimensional Expressions. It is an open standard used to query information from cubes. Here’s a
simple MDX query (running on the AdventureWorks database):
select [Measures].[Internet Total Product Cost] ON COLUMNS,
[Customer].[Country] ON ROWS
WHERE [Sales Territory].[North America]
MDX can be a simple select statement as shown, which consists of the select query and choosing columns and
rows, much like a traditional SQL select statement. In a nutshell, it’s like this:
Select x, y, z from cube where dimension equals a.
Let’s look at the MDX statement more closely. The query is retrieving information from the measure “Internet
Total Product Cost” against the dimension “Customer Country” from the cube “AdventureWorks.” Furthermore, the
where clause is on the “Sales Territory” dimension, because you are interested in finding the sales in North America.
■ names for columns and rows are not case sensitive. also, you can use 0 and 1 as ordinal positions for columns
and rows, respectively. if you extend the ordinal positions beyond 0 and 1, MDX will return the multidimensional cube.
MDX queries are not that different from SQL queries except that MDX is used to query an analysis cube. It has
all the rich features, similar syntax, functions, support for calculations, and more. The difference is that SQL retrieves
information from tables, resulting in a two-dimensional view. In contrast, MDX can query from a cube and deliver
Go back and take a look at Figure 1-6 , which shows sales (fact) against three dimensions: Product, Region, and
Time. This means you can find the sales for a given product in a given region at a given time. This is simple. Now
suppose you have regions splitting the US into Eastern, Mid and Western and the timeframe is further classified
as Yearly, Quarterly, Monthly, and Weekly. All of these elements serve as filters, allowing you to retrieve the finest
aggregated information about the product. Thus, a cube can range from a simple 3-dimensional one to a complex
hierarchy where each dimension can have its own members or attributes or children. You need a clear understanding
of these fundamentals to write efficient MDX queries.
In a multidimensional cube, you can either call the entire cube a cell or count each cube as one cell. A cell is built
with dimensions and members.
Using our example cube, if you need to retrieve the sales value for a product, you’d do it as
(Region.East, Time.[Quarter 4], Product.Prod1)
Search JabSto ::