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

The OLAP process creates precomputed
structures called aggregates for the monthly
store sales as the new transactions are
loaded into the system. The only calcula-
tion needed is to add the monthly totals
associated with each quarter to generate
quarterly figures.

Store net profit
in thousands
5
4
3
2
1
0

Q1
Q2
Q3

Store
MN-1

3.5.3 Ad hoc reporting using aggregates

Why is it important for users to create ad
hoc reports using prebuilt summary data
created by OLAP systems? Ad hoc reporting
is important for organizations that rely on
analyzing patterns and trends in their data
to make business decisions. As you'll see
here, NoSQL systems can be combined
with other SQL and NoSQL systems to feed
data directly into OLAP reporting tools.
Many organizations find OLAP a cost-
effective way to perform detailed analyses
of a large number of past events. Their
strength comes in allowing nonprogrammers to quickly analyze large datasets or big
data. To generate reports, all you need is to understand how categories and measures
are combined. This empowerment of the nonprogramming staff in the purchasing
department of retail stores has been one of the key factors driving down retail costs
for consumers. Stores are filled with what people want, when they want it.
Although this data may represent millions or billions of transactions spread out
over the last 10 years, the results are usually returned to the screen in less than a sec-
ond. OLAP systems are able to do this by precomputing the sums of measures in the
fact tables using categories such as time, store number, or product category code.
Does it sound like you'll need a lot of disk to store all of this information? You might,
but remember disk is cheap these days and the more disk space you assign to your
OLAP systems, the more precomputed sums you can create. The more information
you have, the easier it might be to make the right business decision.
One of the nice things about using OLAP systems is that as a user you don't need to
know the process of how aggregates are created and what they contain. You only need
to understand your data and how it's most appropriately totaled, averaged, or studied.
In addition, system designers don't need to understand how the aggregates are cre-
ated; their focus is on defining cube categories and measures, and mapping the data
from the fact and dimension tables into the cube. The OLAP software does the rest.
When you go to your favorite retailer and find the shelves stocked with your favor-
ite items, you'll understand the benefits of OLAP . Tens of thousands of buyers and

Store
MN-2

Q4 Store

MN-3

Store
MN-4

Figure 3.12 Sample business intelligence
report that leverages summary information—
the result of a typical MDX query that places
measures (the vertical axis) within categories
(store axis) to create graphical reports. The
report doesn't have to create results by directly
using each individual sales transaction. The
results are created by accessing precomputed
summary information in aggregate structures.
Even new reports that derive data from millions
of transactions can be generated on an ad hoc
basis in less than a second.