Foundational data architecture patterns - Making Sense of NoSQL - page 54

3.5.2 Getting familiar with OLAP concepts

Generally, OLAP systems have the same row-store pattern as OLTP systems, but the con-
cepts and constructs are different. Let's look at the core OLAP concepts to see how
they're combined to generate sub-second transactional reports using millions of trans-
actions:

 Fact table —A central table of events that contains foreign keys to other tables
and integer and decimal values called measures .
 Dimension table —A table used to categorize every fact. Examples of dimensions
include time, geography, product, or promotion.
 Star schema —An arrangement of tables with one fact table surrounded by
dimension tables. Each transaction is represented by a single row in the central
fact table.
 Categories —A way to divide all the facts into two or more classes. For example,
products may have a Seasonal category indicating they're only stocked part of
the year.
 Measures —A number used in a column of a fact table that you can sum or aver-
age. Measures are usually things like sales counts or prices.
 Aggregates —Precomputed sums used by OLAP systems to quickly display results
to users.
 MDX —A query language that's used to extract data from cubes. MDX looks
similar to SQL in some ways, but is customized to select data into pivot-table
displays.

For a comparison of MDX with SQL , see figure 3.11.
In this example, we're placing the total of each of the store sales in Minnesota
( WHERE STORE.USA.MN ) in each of the columns and placing each of the sales quar-
ters ( Q1 , Q2 , Q3 , and Q4 ) in the rows. The result would be a grid that has the stores on
one axis and dates on the other axis. Each grid has the total of sales for that store for
that quarter. The SELECT and WHERE statements are identical to SQL , but ON COL-
UMNS and ON ROWS are unique to MDX . The output of this query might be viewed in
a chart, like in figure 3.12.
Note that this chart would typically be displayed by an OLAP system in less than a
second. The software doesn't have to recompute sales totals to generate the chart.

Figure 3.11 A sample of an MDX
query—like SQL, MDX uses the same
keywords of SELECT, FROM, and
WHERE. MDX is distinct from SQL in
that it always returns a two-
dimensional grid of values for both
column and row categories. The ON
COLUMNS and ON ROWS keywords
show this difference.

SELECT

{ Measures.STORE_SALES_NET_PROFIT } ON COLUMNS,
{ Date.2013.Q1, Date.2013.Q4 } ON ROWS
FROM SALES
WHERE ( STORE.USA.MN )

Note the "ON ROWS" and "ON COLUMNS"