In this lesson, you're going to understand the
concepts of OLAP, MDX and business
intelligence. But before we move on to OLAP, let's look at
some of the problems that OLAP was invented
to solve. Dr E.F. Codd, an IBM scientist, invented the
relational database in the early '70s. In 1979 Oracle released their first commercial
relational database product. By the mid to late 1990s, relational databases
had come of age. Almost all big corporations used a relational
database, to store their data. But with the adoption of relational databases
came a problem. Companies found that relational databases were
great at storing data, but made it difficult to generate management
reports, from transactional data. If, for example, you needed to report sales for a
specific month, the database server would potentially have to
loop through millions of transactions, in order to
generate a total. On the hardware of the day, this would often
slow down the entire system to the point where it became unusable for critical
business operations. Of course, the reports would still be generated
eventually, but the whole process took far too
long. So Dr Codd looked at the problem once again,
and came up with a solution: OLAP. The idea behind OLAP was to pre-compute all of
the totals and subtotals needed for reporting. At night or at the weekend, when the database
server was normally idle. The totals are stored in a special database,
called an OLAP Cube. An OLAP Cube doesn't have to loop through any
transactions, because totals are all pre-
calculated, providing instant access. An OLAP Cube is a snapshot of data at a
specific point in time, perhaps at the end of a
specific day, week, month or year. The standard version of Excel 2013 now provides
a complete OLAP solution, on your desktop. In Lesson 6-2, you actually created an OLAP
Cube without realizing it. When you create an OLAP Pivot Table, from a
data model, an OLAP Cube is automatically
created in the computer's memory. And is used to power an OLAP Pivot Table. At any time, you can refresh the Cube, using
the current values in the source tables. With very large data sets, it could take an
appreciable amount of time for Excel to
reconstruct the Cube. But with the data sets we've been using (just a
couple of thousand rows), the process appears
to be instantaneous. Now, let's move on to Business Intelligence (or
BI). When IT professionals talk about Business
Intelligence, they usually mean the analysis and
presentation of data, stored in an OLAP Cube. In the recent past, it was very expensive to
implement a Business Intelligence solution. BI projects would often take many months to
complete, and involve large numbers of highly-
trained IT professionals, to design and extract data into the OLAP
Cubes. The vision for the Excel data model is that
ordinary Excel users can create a ready-to-go
OLAP Cube almost instantly. Microsoft sometimes called this concept Self
Service BI. And unlike traditional Business Intelligence
solutions, it can be implemented by ordinary
Excel users, and provides instant results. Because the OLAP Cube is generated
automatically. Now that you understand the concept behind
OLAP, let's look at how an OLAP Cube works in
more detail. You know that you can create an OLAP Cube
from an Excel data model. Conceptually, you can think of an OLAP Cube
as consisting of many boxes, each with a total
inside it. Now, let's cover some OLAP terminology. It's important you understand this terminology,
because you'll find it used in some of the OLAP
functions that will be used later in this session. And you'll also find it used in Microsoft's Help
pages. So first, let's talk about Dimensions. I'm going to add a Dimension, to this OLAP
Cube. And that is the Category dimension. You can see that this data would originate in the
Category table. And you can see that the front slice of the Cube
is coming from rolled up totals, in the
Confections category. The center slice is coming from rolled up totals
in Condiments category. And the rear slice is coming from rolled up totals
in the Beverages category. Now, let's look at another Dimension. This time,
the Dimension of Time. We're going to have January rolled up totals in
the top slice of the Cube, February rolled up totals in the next slice. Then, March and then, April rolled up totals, in
the bottom slice of the Cube. And now, let's cover another piece of OLAP
technology, the Measure. A Measure is simply the thing that's being
totaled. So let's add some Measures along the bottom
of the Cube: In Stock, On Order, Sales Amount,
Sales Cost, and Sales Quantity. Let's now put a value into one of those little
boxes, and see how we'd retrieve it. When you retrieve a value from an OLAP Cube,
the value is said to be at a Cube intersection. That is an intersection of a Measure and
Dimensions. I can see that the Measure, this time, is the
Sales Amount. So the 2,233 is the total amount of sales at
some point. Then, I can see that this is in the January slice. So I know that 2,233 involves sales rolled up in
January. And then, I can see it's in the Confections
dimension. So I know this is sales for Confections in
January, and it's a sales amount. So 2,233 is the total sales amount in January, in
the Confections category. Now, let me talk about another piece of OLAP
terminology, the concept of Hierarchies. A good example of a Hierarchy is months,
quarters and years. Items in a Hierarchy have a parent/child
relationship. And Hierarchies come into play when you drill
down into data. To make a bit more sense of what I've just said,
let's add an example, by adding a rolled up
total, for 2013. You might begin by looking at sales for 2013,
but then, need to drill down, into sales for March
2013. In this respect, 2013 is a parent in the
Hierarchy, and the months are children within
the Hierarchy. You can, of course, always compute the total
parent sales, by adding the sales of all the
children together. Let's now add some more values, to the front
slice of this OLAP Cube. And you can see that I can now see (in the
Confections category) my rolled up totals for
January, February, March and April, in the five Measures (In Stock, On Order, Sales
Amount, Sales Cost and Sales Quantity). And in order to retrieve any of those values, I'd
have to provide two Dimensions and one
Measure. Now that you understand how OLAP Cubes
work, let's look at how Excel retrieves values
from an OLAP Cube, into an OLAP Pivot Table. Here, we have an OLAP Cube that's been
created from a relational data source. When the OLAP Pivot Table wants to get
information from the OLAP Cube, it uses a language called MDX (or
Multidimensional Expressions). The OLAP Pivot Table creates an MDX query. And the Cube sends back the data requested by
the MDX query. Now, you'll be pleased to know that you don't
need to learn the MDX query language, in order
to use an OLAP Pivot Table. The queries are created behind the scenes
automatically, for you. Although it is possible to also roll your own, if
you do know the MDX query language. You're going to see some MDX expressions
later, in Lesson 6-6. MDX is an open standard that's also used by
many non-Microsoft products, such as Cognos
PowerPlay. An OLAP Pivot Table can, in theory, be used to
report from any MDX-compliant OLAP source. Well, now that you understand the concepts of
OLAP, MDX and Business Intelligence, you're
ready to move on with the rest of this session. And you've now completed Lesson 6-3:
Understand OLAP, MDX and Business
Intelligence.
