Database Lesson #8 of 8 – Big Data, Data Warehouses, and Business Intelligence Systems

Database Lesson #8 of 8 – Big Data, Data Warehouses, and Business Intelligence Systems

Good day, everyone. This is Dr. Soper here. And today we will be exploring
the eighth and final topic in our series of
lectures on databases, with today’s topic focusing
on big data, data warehouses, and business
intelligence systems. Although the objectives
for today’s lecture are varied and manifold,
broadly speaking, our goals are to extend our
knowledge of database systems beyond relational
databases by exploring some of the additional ways in
which database technologies can be used to achieve
organizational objectives. Along the way, we will explore
the basic concepts of data warehouses and data marts. And we’ll learn about the
basic concepts and architecture associated with dimensional or
multi-dimensional databases. We will also learn about
business intelligence systems, online analytical processing,
and data mining, all of which are very useful for helping
managers to make decisions. And then toward the
end of today’s lecture, we will explore some
of the fastest growing topics in the database
world, including big data, the NoSQL
movement, structured storage, and the MapReduce process. So without further
ado, let’s begin. I thought a good
way of introducing some of the topics
in this lecture would be through an exploration
of human mathematical intuition, that is,
do we as human beings have a natural intuition for
finding truth in numbers. To examine this question,
consider the following example. Here we have two people, whom
I’ve named Bubba and Daisy. And both Bubba and Daisy
are interested in purchasing new vehicles. Currently, Bubba drives a
very inefficient vehicle. Let’s say that he
drives an old truck. And he gets 10 miles per gallon. Whereas Daisy drives a
reasonably efficient car. And in her case,
her current vehicle gets 30 miles per gallon. Now let’s say that
both Bubba and Daisy are interested in purchasing new
vehicles that will have better fuel economy than their
current vehicles with the goal of needing to purchase
less gasoline every year, thereby saving money. Bubba, however, does
not want to give up all of the benefits
of his big truck, so the vehicle he’s looking
at provides just 12 miles per gallon, as opposed
to his current vehicle which provides 10. Daisy, on the
other hand, is very interested in fuel economy. And so she is planning to
purchase a vehicle which delivers 50 miles per
gallon, as opposed to her current
vehicle, which has 30. Another way of
considering these numbers is by looking at the percentage
increase in miles per gallon. In Bubba’s case, he is
moving from a vehicle which has 10 miles a gallon to one
which has 12 miles per gallon, and that is an increase of
20% in the miles per gallon. Daisy, by contrast, is
moving from a vehicle with 30 miles per
gallon to one which provides 50 miles per gallon. And that is an
increase of 66.7%. Next, for the sake of offering
a fair comparison between Bubba and Daisy, let’s say
that both drivers drive 10,000 miles per year. Now at this point, I
want to appeal to you or mathematical intuition. Based upon the situation
that I’ve described, I would ask you a
simple question, which of these drivers,
Bubba or Daisy, is going to save more money
on gasoline every year? Well, if you are
like most people, you will look at these
numbers and you’ll say that clearly Daisy’s vehicle
has a much greater increase in miles per gallon,
therefore, she will be saving the most
money every year on gasoline. Unfortunately, this
is the wrong answer. In fact, Bubba will
be saving more money every year on gasoline. And let’s see why. Here we have a
table which shows us how much gasoline
both Bubba and Daisy are currently consuming
based upon their driving habits versus how much they
will consume if they purchase their new vehicles. In Bubba’s case, he drives
10,000 miles per year. And in his current vehicle,
he gets 10 miles per gallon. Therefore he will consume 1,000
gallons of gasoline per year. In his new vehicle,
Bubba will still drive 10,000 miles
per year, but now will get 12 miles per
gallon, and therefore will consume 833.33 gallons
of gasoline per year. By contrast, in Daisy’s
current vehicle, she gets 30 miles per gallon. And driving 10,000
miles per year, she consumes 333.33
gallons of gas. Whereas with her new vehicle,
which gets 50 miles per gallon, she will consume 200 gallons
of gasoline every year. Simple subtraction,
then, shows that Bubba will save 166.67
gallons of fuel per year if he purchases his new vehicle. Whereas Daisy will only
save 133.33 gallons of fuel per year. That is, Bubba will enjoy it
more fuel savings per year by buying a new truck, which
has 12 miles per gallon, versus his old truck, which
had 10 miles per gallon, than will Daisy by
buying a new car which has 50 miles per gallon,
versus her old car which provided 30 miles per gallon. So if you’re like most
people, in this problem you’re mathematical
intuition has failed. And it is for this
reason that I would argue that managers should
not rely on their intuition when making important
managerial decisions, but rather should rely on data. Many managers have
begun to recognize the deficiency is
in human intuition, and are hence
adopting technologies like business analytics,
business intelligence, data warehousing, and data mining,
so as to better support their decision-making
activities with the data that the organization
has at its disposal. By the way, if
you’re interested, this problem is known as the
miles per gallon illusion, and stems from the fact
that we here in the United States commonly measure fuel
economy in miles per gallon, as opposed to the much more
appropriate measure which would be gallons per mile. Now that we’ve
seen for ourselves how human intuition
can fail, in even very simple mathematical
problems, we can begin discussing some of the
systems and technologies that have been developed to help
managers make better decisions and avoid the failures
that are commonly associated with relying
upon intuition alone. First among these technologies
are business intelligence, or BI, systems. BI systems are information
systems that are intentionally designed to allow
managers to capitalize upon organizational data
for the purpose of improving their decision making. It’s important to note that
these business intelligence systems do not support
the real-time operational activities of the organization. Those activities, by contrast,
are supported by transaction processing systems. Instead, these BI
systems are designed to support managerial assessment
activities, analysis, planning, controlling, et cetera. Broadly speaking, we can
classify these BI systems into two categories. First, are simple reporting
systems, the purpose of which is to provide managers
with the ability to look at data in a
flexible, real-time way, that is, these reporting
systems support simple data organization capabilities,
such as sorting, filtering, and grouping data. And they often
provide the ability to make simple calculations
on data in real time. These simple calculations
might include operation such as a sum,
or an average, or a count. By contrast, second
category of BI systems are data mining applications. Data mining applications
are typically designed not to rely
on that real time data, but rather to rely on
archived historical data. And the reason for this is
that data mining applications typically allow for
sophisticated analyses on an organization’s data. Because these analyzes
involve complex statistical and mathematical processing,
they typically cannot be conducted in real time. The advantage, of course,
of such statistical and mathematical
techniques, is that they can deliver insights and create
predictive models that simply are not possible with the
simple types of calculations that are available
within Reporting systems. This figure depicts
the relationships that exist between operational
and BI applications. Whereas our operational
applications are used by functional users and
rely upon the operational DBMS. Business intelligence
applications are used by managerial
users, and can rely upon the operational
DBMS, or a specialized DBMS that is made just for
the business intelligence applications. And by extension,
these BI applications can hence rely directly upon
the operational database by way of the operational
DBMS, for example, in the case of
reporting applications. While they also might rely
upon archived historical data, or other data sources,
which are typically made available in the form of a
data warehouse or a data mart. As a quick review, just remember
that simple BI reporting applications typically
rely on an organization’s operational data. And they provide the
ability to look at data in a simple way in real time. By contrast, data mining
applications typically rely on archived historical
data, and as such, do not provide a real time
view of the organization. The trade-off or
this time lag is that data mining
applications can use sophisticated statistical
and mathematical techniques to create models which allow
managers to perform what if analyzes, do predictions
about the future, and generally speaking,
improve their decision making. As I noted earlier, business
intelligence applications that provide capabilities which
go beyond basic reporting, typically rely upon extracts
of the organization’s operational database,
along with data acquired from other sources, all of which
can be aggregated and stored in a data warehouse. Thus a data warehouse
commonly contains data from many
different sources. Not only does it contain
data from the organization’s operational
databases, but it can contain other internal
and external data as well. In the case of external
data, an organization may be able to obtain
such data from publicly available sources. Or they may purchase data. Examples of these
external data sets might include information about
what competitors are doing, what the market is
doing, or expectations about future global trends. Together, data from all
of these various sources are run through an
ETL system, where ETL stands for extract,
transform, and load, so as to clean and prepare
the data for inclusion in the data warehouse. After this process,
the data can actually be added to the data
warehouse itself. And then our more complex
business intelligence applications will
have a source of data upon which they can rely
when performing their tasks. Despite our best efforts at
designing relational databases that will ensure the quality
and integrity of the data that they contain,
it is, unfortunately, still possible for problematic
data to appear in the database. Further, because our business
intelligence applications may rely upon these data in
support of managerial decision making, it is
critically important that the data be of the
highest quality possible, such that managers will
have the greatest chance possible of
making good decisions. Here, we’re simply referring to
the classic and cliched concept of garbage in,
garbage out, that is, if we are providing our managers
with low quality or problematic data with which to support
their decision making, then we must expect
the resulting decisions to be similarly problematic. So let’s examine some
of the problems that can arise in operational databases. A very common problem is
what we call dirty data. And dirty data refers
to a data value which is obviously incorrect. As an example, we might have
the letter v stored as a gender code, instead of the more common
m or f, for male and female. Or we might have a value
of age stored as 213, which would be a remarkably old
human being if that number was correct. Other problems with
operational data include missing values
and inconsistent data, where inconsistent data refer
to data values that have changed and are not the same across
all of our data sources. So perhaps a
customer’s phone number was updated in the
operational database. And the previous value
in the data warehouse is, therefore, incorrect. Additional problems
with operational data include non-integrated
data, that is, when we have data from
two or more sources that we need to merge
together in some way so that they can be added
into the data warehouse. We may also have data
in an incorrect format, in which case, it will need to
be transformed into the format that is required by
the data warehouse. And of course, we may
simply have too much data. There is a general concept in
science known as parsimony. And this concept tells us that
simplicity is often preferred. For example, if I construct
a predictive model that is 95% correct, and it
relies upon three predictor variables in order to
achieve that 95% accuracy. But I might improve
the accuracy to 96% by adding 20
additional variables, in most cases, the additional
complexity involved in order to achieve such a marginal
gain in predictive power would not be worth it. So in that case, we would
prefer the three predictor model over the model which
contains 23 predictors. As I mentioned earlier,
because a data warehouse often contains data from
multiple data sources, the input data commonly need to
be run through an ETL process before they can be stored
in the data warehouse. Again, ETL here stands for
extract, transform, and load. Where the extract step
is simply pulling data from these various data sources. The transform step is
cleaning, or modifying, or processing the
data in such a way that they are made
appropriate for inclusion in the data warehouse. And then the load step
refers to the process of taking the transformed,
cleaned, processed data and actually storing it
in the data warehouse so that they can be used by
whichever business intelligence applications might rely
upon that data warehouse. Simple examples of
such transformations might be transforming a country
code into a country name. So we may have the country
code US, for example. And in the data
warehouse, we need to transform that into the
name of the country, which might be United States. Or we may have a
customer’s email address, such as [email protected] And we actually just want
to store the email domain in the data warehouse for
purposes of aggregation. In that case, we would want to
transform the customer’s email address simply into the
domain, which in this case would be, and store the
result in the data warehouse. Next, I would like to talk about
the concept of a data mart. And I think the best
way to understand a data mart is simply that it is
a subset of the organization’s data warehouse. Data marts are constructed
to support a specific need within the organization. So this might be a subset
of the data warehouse that is needed to support
a particular project, or a particular functional
area within the business, like advertising or
marketing, for example. Or perhaps, we need
to create a data mart to support a specific
group of employees within our organization,
like a sales team. Regardless of the
specific reason why we create a data mart,
the general principle underlying their
creation is simply that not all personnel,
or not all managers, within the organization
will need access to all of the organization’s
archive historical data. Personnel within
the organization who perform new product
development, for example, probably will not need
access to data associated with human resources, such as
employee salaries or employee benefits. Instead we might
create a data mart just for the new product
development team, which contains only
those data that directly support their needs. At some point in your adventures
in the database world, you may have heard the
term dimensional database, or multi-dimensional database. And I’d like to take a few
moments to talk about some of the concepts associated
with these types of databases. To begin, it’s important to note
that dimensional databases are designed and implemented
using exactly the same sort of database technology
that we use to create our operational databases. That is, dimensional
databases contain tables. They are related
to each other using primary key foreign key links. We have the same
concepts cardinalities, such as one to one
relationships, one to many relationships,
et cetera. So hopefully, operating
within the familiar framework of the relational
database world, understanding these
dimensional databases will be reasonably easy. Broadly speaking, the idea with
a multi-dimensional database is that we want to implement
a non-normalized database structure for the purpose of
vastly improving query speed. That is, in an
operational database, we typically implement
a database design that is largely
normalized, that is, it might be in
third normal form, or Boyce-Codd normal form,
with perhaps a few tables being denormalized for the purpose
of improving efficiency. And what a normalized
database allows us to do is to store large
amounts of data very quickly, while
still preserving the quality and the integrity
of the data in the database. The problem with this sort
of rigid, normalized design, however, is that if we want to
extract data from the database, we commonly need to perform
computationally expensive join operations in order to get
the information that we want. So a normalized
relational database is very good for quickly
storing information, but is very bad for quickly
extracting information that we want. By contrast, with
the sort of design that is implemented in a
multi-dimensional database, storing the data
in the database can be a very slow and
laborious process. However, extracting data from
the database is very fast. And the reasons for this
are that we implement a non-normalized
database structure, while simultaneously
storing data in pre-aggregated
levels of granularity within the dimensional database. An important point
to note here is that these dimensional
databases are used to track an
organization’s historical data, and therefore they almost
always contain a date or time dimension. And it is this date
or time dimension that provides us
with the ability to store the same
information aggregated at different levels
of granularity within the
multi-dimensional database. For example, imagine that we
have operational data which represents all of the sales
transactions for a store. So every time a
sale is made, we may generate a sale record,
which records information about that transaction. Now, this information
is useful, however, it may not be as
useful to a manager as it would be if it were
aggregated up to a coarser level of granularity. Consider that if I were to
take all of the sales data for one day and add
them all together, then I have a daily total. Similarly, if I take
the daily totals for seven consecutive days,
then I have a weekly total. I can, in such a way,
continue to create monthly totals, quarterly
totals, yearly totals, et cetera. It is the same
information that is available in the individual
sales transactions, except it has been
pre-aggregated and stored in the database in a
pre-aggregated form, such that we can vastly
improved query speed. That is, when a
query is run where we want to look at the data
in the form of weekly totals, or monthly totals, or quarterly
totals, the database at that time does not
need to aggregate all of the individual
sales transactions in order to produce the result. The result already exists
in the dimensional database because it has
been pre-processed prior to being added
into the database. So again, the purpose of
these dimensional databases, then, is to
intentionally implement redundant data in a
non-normalized design, such that we can vastly
improved query speed. We want to be able
to extract data very quickly from the database. The most common data model
for a dimensional database is known as a star schema. And the general characteristics
of a star schema are that we have several
dimension tables. In this case, we have
a time dimension table, a customer dimension table,
and a product dimension table. And at the intersection of
all of those dimension tables, we have something
called a fact table. Philosophically
speaking, the reason that the intersection table
at the center of the dimension tables is called
a fact table, is because a fact in
very real terms is the intersection
of information. For example, imagine that
I’m interested in knowing how many of a particular product
a specific customer purchased during the month of December. Well, the answer
to that question is the intersection
of the customer, the product in which
I’m interested, and the specific time frame
that I specify, in this case, the month of December. And the answer
might be 14 units. So a specific customer
purchased 14 units of a particular product
during the month of December. The intersection of those
three values is a fact. And it is for this
reason that we label the table at the center
of a star schema, a fact table. To help you better
conceptualize this concept, let’s consider the
intersection of two dimensions. And in this example,
we’re looking at the customer
dimension contrasted with the product dimension
in the form of a two dimensional matrix. The value contained in each
cell within this matrix, then, is a fact. And it expresses
to us the quantity of a particular product
that was purchased by a particular customer. And you will notice,
of course, if you recall from our last lecture,
that this structure is very similar to a bitmap index. Extending this concept out
into a third dimension, we can see here that
we’re representing a fact as the intersection of three
different dimensions in a three dimensional matrix. Along the horizontal axis
I, again, have customers. Along the vertical axis
I, again, have products. But now, along the z-axis,
I have a time dimension. Therefore, the value
contained in any of the cells in this
three dimensional matrix will tell me the quantity
of a given product that was purchased by a
particular customer during a particular
date or time frame. Now unfortunately, human
beings have a great deal of difficulty
envisioning higher order spaces beyond three dimensions. However, this concept
scales very easily up to higher dimensional spaces. So we might consider,
for example, a fact to be the intersection of four
dimensions, or five dimensions. And although it is not easy
to depict such a situation, conceptually, it is
just a natural extension of the two-dimensional and
three-dimensional examples we saw here. In either case, I
hope you can now understand why databases
designed in this way are called dimensional or
multi-dimensional databases. Next, I’d like to talk briefly
about OLAP and data mining technologies. If you recall from
earlier in the lecture, we said that,
generally, there are two broad categories of business
intelligence applications. And they were, reporting
applications, and data mining applications. Online analytical
processing, or OLAP, then, is a technique that supports
these reporting applications. That is, OLAP allows us to
dynamically examine database data in real time, and
apply simple transformations like sorting, filtering,
grouping, et cetera. And it allows us to perform
simple arithmetic functions, such as summing values together,
finding an average, account, the standard
deviation, et cetera. And again, this is intended
to be used in real time. By contrast, data
mining techniques support data mining category
of business intelligence applications. And data mining, broadly,
refers to a collection of mathematical and
statistical methods that can be used to gain deep
insights into an organization’s data. Again, remember that the
level of sophistication of these techniques generally
requires that they not be executed in real
time, so as to avoid interfering with the real time
operations of the organization. OLAP systems, then, when used in
support of simple BI reporting needs, produce
something called an OLAP report, which some people
will refer to as an OLAP cube. And the general idea
here is that our inputs are a set of dimensions,
while our outputs are a set of measures. So recalling the two dimensional
and three dimensional matrices that we saw just
a few moments ago, a manager might select
a series of dimensions. And the OLAP system
might allow him or her to perform simple
transformations or drill down operations
on the data which lie at the intersection
of those dimensions so as to gain real time
insights into the organization. And here we see that
these OLAP cubes can be constructed using our
standard SQL SELECT queries. In this case, we’re selecting a
number of different dimensions. We are then performing a
join on four separate tables, and are imposing some group
by and order by requirements. The result of this query
in OLAP terminology, then, would be a
result set which represents a
collection of measures that a manager could use
to gain some insights into his or her organization. And of course, rather than
constructing these SQL commands repeatedly, we
might take advantage of the capability of relational
databases to create views, so as to save the
SQL statements, which are used to produce common OLAP
cubes in the database itself. Data mining, then, can be
viewed as the convergence of many different disciplines. A skilled data miner needs
not only working knowledge of databases, but also needs
statistical and mathematical knowledge, perhaps knowledge
of artificial intelligence, or machine learning algorithms,
knowledge of data management technologies, and so forth. In the modern world, many
people become highly specialized in one particular area. But the people who are most
valuable to an organization often have expertise
in two or more areas. And this is certainly
the case with people who are experts at data mining. To conclude our
overview of data mining, I just wanted to
briefly describe some of the most
common techniques that are used to perform data mining
against an organization’s data. Among these techniques
are cluster analysis, in which case, the objective
is to group sets of entities together according to
their level of similarity along one or more dimensions. We also have decision
tree analysis, in which we can process a large
quantity of historical data and generate a
decision tree, which tells us what to do under
different circumstances, in order to achieve some
kind of the desired result. We also have regression
available to us as a very powerful
data mining tool. The goal of which is to
produce mathematical equations, or mathematical models, that not
only describe the relationships between variables,
but also provide us a basis for predicting
future events based upon past observations. Data mining
applications might also rely on sophisticated artificial
intelligence, or machine learning algorithms, such as
neural networks or support vector machines. And recently, we’ve seen a
rise in a technique known as market basket analysis,
or affinity analysis, which allows us to look for
patterns of co-occurrence, for example, determining
which products are commonly purchased together. And the results of
these affinity analyses can then be used as the
foundation of a recommendation engine, which can suggests to
you movies that you might like, books that you might
like, et cetera. Now I’d like to move into
the final topic in our course on databases, and that is the
rise of the big data paradigm. Scientists and
researchers have recently noted an exponential increase
the quantity of data being produced by the human species. If my memory serves
correctly, I believe the current rate of growth
is that the amount of data doubles every 40 months. At present, the world is
generating many exabytes of new data every single day. And if you’re unfamiliar
with the term exabyte, consider that one exabyte
is slightly more than one million terabytes. So you may have a
computing device at home that saves several
terabytes of data. But consider that several
million terabytes of new data are being generated by the
human species every single day. And this situation
creates a vast array of new and interesting
problems for organizations. The term big data, then, refers
to the rapidly expanding amount of data that is being stored
and used by organizations. These data sets can be very
large and very complex. And because of their
size and complexity, they can be extremely
difficult to process using traditional
database technologies. And an important
point to note is that much of what is
considered big data is being generated by web 2.0
applications, and the emerging collection of web
3.0 applications. Traditional examples
of web 2.0 applications might include social networking
sites, video sharing sites, blogs, discussion
forums, et cetera. So this rapidly accumulating
quantity of data presents many challenges
for organizations. Among these are simply
capturing all of the data and storing it, maintaining
the data once we have it. This is also commonly
referred to as curation, in the same way that
the curator of a museum must maintain all of the
ancient artifacts, so, too, must the curator
of a big data set be able to maintain the quality
and the integrity of the data in light of things
like failing hardware, and the desire of the data to
be used by many people from all over the world simultaneously. Additional challenges include
things such as search. How does one search
efficiently through such an enormous quantity of data? Data transfer,
consider for example, that if you have a 100
megabit network connection you can transfer
approximately one terabyte of uncompressed data per day. At this speed, it would take
you more than a million days to transfer one exabyte of data. Further challenges
include analyzing these massive data
sets, visualizing the massive quantities
of data, and so forth. In the past few
years, a term has arisen in the area
of big data that is used to describe the
movement toward using non-relational
databases in order to support these huge
and highly distributed, highly replicated
collections of data. And this term is called NoSQL. Although the name,
to many people, implies that SQL is not involved
in these databases, that is, they do not support
SQL-like queries, this assumption
is actually wrong. As it is used in contemporary
database circles, NoSQL means not only SQL, that
is, these very large databases, although they may not be
based on relational algebra, in the same way that a
relational database is, they nevertheless
support querying through a SQL-like
query language. Unlike the relational
database world where the relational model
is fixed and predominates all relational database
vendors, in the NoSQL world, there are many
different architectures for non-relational databases
that are currently being used. These include
architectures which rely upon a key value
store, a wide columnar store, a documents store. There are databases that
rely upon graph theory, and so forth. Collectively, all of
these different types of very large data
stores are commonly referred to as
structured storage. And they have a few
attributes in common. First, they arguably employ
simpler designs, as opposed to relational databases. And second, they almost always
have a looser consistency model than one will find in
a relational database. Another way of saying that
is these structured storage databases do not
provide ACID guarantees. If you remember,
ACID is an acronym which stands for Atomicity,
Consistency, Isolation, and Durability. And ACID guarantees
are the hallmark of a normalized
relational database. We cannot expect to have that
level of consistency in these massive, highly distributed,
highly replicated, structured storage databases. When discussing the
sort of data models that are actually used in these
structured storage databases, I like to use the
data model that is employed by the
Apache Cassandra database as an example. And the reason for
this is that it is one of the most
popular structured storage database management systems. And it is currently the most
popular wide columnar store that is available. Broadly speaking, the
Apache Cassandra database can be classified as a hybrid,
key value slash wide columnar database. So its architecture
contains elements of both a key value store
and a wide columnar store. The Apache Cassandra database
itself was originality created at Facebook by two
of their software architects, after which it was transferred
to the Apache Foundation, where it now resides as entirely
open source and free database. Apache Cassandra has
cross-platform support. The reason for this being
that it was a written in Java. So it can run on
Linux-based machines, Windows-based machines,
Unix, et cetera. And Cassandra supports a
massively distributed database environment. That is, it allows us to
subdivide our database among dozens, or
hundreds, or even thousands of separate
database servers, potentially spread out
all over the world. The database is a highly
scalable and decentralized. By scalable here, I
mean it’s extremely easy to add an extra node, that is,
an extra server to the cluster, thereby expanding the
size of the database. And by decentralized,
what I mean here is that all of the nodes,
that is all of the database servers, that are involved in
a Apache Cassandra database, have the same role. And this provides the very
desirable characteristic of there being no
single point of failure. Another very valuable
characteristic of the Apache Cassandra
model is that it provides for automatic data replication. That is, the database
itself can automatically make copies of data
and store those copies in different locations
throughout the cluster. This makes the database
highly fault tolerant, such that if an individual node,
that is an individual database server, were to fail, the
redundant data stores takeover instantaneously. There’s no down time
with the database at all. Further, Apache Cassandra
supports the MapReduce process, which is a computational model
for solving data processing problems in a highly
distributed environment. And I’ll talk more about
the MapReduce process here in a few minutes. And to illustrate the legitimacy
of the Apache Cassandra model, consider that it is currently
used by CERN, organization such as Constant Contact, Digg,
Instagram, Netflix, Reddit, Walmart, Twitter, et cetera. Now let’s talk about the
Cassandra data model itself. As you know, in a relational
database management system, related data for an
application are stored together in a container which is
referred to as a database, or sometimes as a schema. And within that
database or schema, we have one or more tables. The analogous
concept in Cassandra is something called a key space. That is, data for an
application are stored together in a container known
as a key space. And inside of that key
space, instead of tables, we have something known
as column families. So just as in the
relational database world, a single DBMS might contain
many databases, each of which contains many tables. In the world of
Apache Cassandra, the Cassandra
database might contain many key spaces, each of which
contains many column families. The column families,
then, contain columns. But this is not the
same as a column in a relational database. In Cassandra, a column
consists of a name, that is the name of the
column, a value, that is the data value
for that column, and the time stamp, where
the time stamp indicates the point in time at which
the data value was changed. Related columns, then, are all
stored within the same row. And each row is identified
using a unique row key. This notion of a
unique row key is directly analogous to
the idea of a primary key in the relational
database world. Rows in the Cassandra
model, however, are not required to contain the
same set or number of columns. That is, different rows
within the same column family might have a different
number of columns. And the number of columns
in a particular row is allowed to expand or
contract on an as-needed basis. A few additional
important differences to note between
the Cassandra data model and the
relational data model are that in the
Cassandra data model there are no formal foreign key
relationships between column families. That is, we cannot establish
formal relationships between column families within
the same key space. And what’s more, it is
not possible to join column families
together using a query. So whereas in the
relational database model we can write queries that
will join tables together, it is not possible in
the Cassandra model to join column
families together. Now, I know that this sort
of a verbal description of the Cassandra data model can
be a bit challenging to follow. So let’s look at a
picture which I hope will help to make some of
these concepts clearer. Here we see a graphical
representation of the Cassandra data model. The outermost
container represents all of the key spaces for
the Cassandra database. And in this case, we have just
two key spaces, one of which is labeled as the
blog key space, and the other which is labeled
as the store key space, where the details of the
store key space are not elaborated
in this diagram. Again, the idea here
is that a key space is roughly analogous
to a database within the relational
database world. This means that a key
space, then, is typically oriented toward
a particular need or a particular application. Within each key space, we
can have one or more column families. In this case, we have a
column family for users, and we have a column
family for blog entries. Next, let’s look at an
example of a column family. And to begin, I will refer
to the user column family. So here, we can see that we have
three rows within the column family. And each row represents a unique
user within the blog key space. A user, then, is
represented as a collection of one or more columns. And remember that, in
the Cassandra data model, the number of columns per
row can vary from row to row. So in this first row,
that is the Dan 42 row, we see that we
have three columns. The first column
is the name column. It’s value is Dan. And we have a timestamp. The second column
is the email column. It’s value is [email protected] Again, it has a timestamp. And the third column is
the phone columns, which has a value and a timestamp. For the next user, we
have only the name column. And for the third user, we
have only the name and email columns. So there is no requirement
that each row contain the same number, or even
the same type of columns. Next, let’s look at the
blog entry column family within this blog key space. Again, we see that each row
within the column family contains multiple columns. In this case, both rows
contain the same columns. But again, that is
not a requirement. Here, the columns are the text
column, the category column, and the user column. Note, particularly, that the
values stored in the user column can be used to determine
which blog entries were written by which users. However, remember that
formal relationships between column families
do not exist in Cassandra. That is, we do not
formally establish primary key, foreign
key relationships. So I hope that looking at this
diagram of the Apache Cassandra data model demystifies
things a little bit for you. I know that learning about
these structured storage data models for the first
time can be intimidating, but I hope that
through this diagram, you can see that it’s
really not that complicated. And I hope that is
encouraging for you. As I mentioned earlier, these
structured storage databases are often highly distributed
and highly replicated. That is, they may be spread
across many, many different nodes or database servers. Now this structure has
substantial advantages. Not only does it
provide fault tolerance, but it allows for data
requests to be handled by the nearest
available node that is able to service the request. So for example, if
you are in Tokyo, and it happens that a copy
of the data in which you are interested is stored on
one of my database nodes, which is located
near Tokyo, it’s much more efficient for that
node to handle your request than it would be to
route the request to a distant geographical
node, say, one which might be
located in Berlin. The problem with
this model, however, is that it can cause
problems with consistency. Consider what happens when
a data item is updated. So if I update a data
item on one node, it will take time
for that update to cascade to the other
nodes within the cluster that contain a copy of the data. So imagine that my distributed
structure storage database contains 1,000 nodes,
spread all over the world, and the data item I’m
interested in updating is replicated across
100 of those nodes. So I may then perform the
update on one of the nodes. And until that update
cascades throughout all of the other nodes
in the cluster, any requests for
that same data item that are made of
those other nodes will be returned values
that are out of date. And again, this
is due to the fact that the database is
widely distributed and widely replicated. And because we
typically do not enforce an ACID level of consistency. Thus, in these replicated
data environments, we commonly use a
consistency model that is referred to as
eventual consistency. And what eventual
consistency means is that if no new updates
are made to a specific data item for a period
of time, eventually all of the requests
for that data item will return the most
up to date value, regardless of which node
is servicing the request. And the time stamps that are
recorded during each item update are the key
which allows us to reconcile any inconsistencies
in replicated data values between nodes. Finally, I would just like to
take a few moments to discuss the MapReduce process. Broadly speaking, MapReduce
is a programming model that relies on
parallelization in order to perform data processing tasks
on these huge data sets that may be distributed across
many different servers, or many different nodes. So conceptually speaking,
then, the MapReduce process involves two different
types of nodes. There will be a master
node and a worker node. Put simply, the
master node is usually the node which receives
the data processing request from the user. While the worker
nodes are nodes which are assigned to complete
part of the processing task by the master node. So this MapReduce process,
then, unfolds in two steps. The first step is
called the map step. And in the map step,
the master node will take the data
processing problem and subdivide it into a
series of sub problems. And each of these sub
problems is then assigned to, and carried out
by, a worker node. The second step in
the MapReduce process, then, is the reduce step. So after the worker nodes have
completed their assigned tasks, they pass the results of their
work back to the master node. The master node will then
do the final processing, or final combining,
of those results in order to produce the overall
answer to the problem, which is then returned back to the user. Again, I know that
concepts such as this can be difficult to
understand in verbal terms, so let’s see if we can get a
better idea using an image. So toward the top of this
figure we have the master node. And toward the bottom, we
have various worker nodes, which here are labeled
one, two, three and n, up to however many worker nodes
we need to solve the problem. So the MapReduce process
unfolds as follows. As input, the data
processing problem is passed into the master node. The master node will then divide
that data processing problem into sub problems, which
are then assigned to and carried out by the
various worker nodes. After completing their
tasks, the worker nodes will return their results
back to the master node, which performs the final combining and
processing of the worker nodes’ results, in order to produce
the final answer, which is then the output of the
MapReduce process. Let’s consider an
example, imagine that we are a wireless
service provider, and it we use a highly
distributed, structured storage database, which has
1,000 different servers all over the world. Let’s further assume
that our 100 million customers are equally subdivided
among our 1,000 servers. So that means we would have data
for 100,000 customers per node within our database environment. Now let’s imagine that our
data processing problem is that we want to figure out
the average number of text messages sent during
the month of November. And we want those
results organized by age. So we would like to know what
is the average number of text messages sent by 18-year-olds,
and 19-year-olds, and 20-year-olds, and
21-year-olds, and so forth, all the way up
until our oldest customers. Now let’s see how
the MapReduce process can be used to solve
this data problem. First, the problem is
passed to the master node. And the master node might
subdivide the problem such that it instructs each
of the 1,000 worker nodes within our
database environment to count the total number
of text messages sent by each customer during
the month of November, and aggregate those
results by age. The results of the
worker nodes tasks, then, would be a
table of data, which might contain three columns. First would be all of the
distinct ages of the customers whose data resides on that
particular worker node. The second column
might be the number of customers who are that age. So we might have 1,000
18-year-olds, 714 19-year-olds, 235 20-year-olds, et cetera. And then, the total
number of text messages sent by customers of
each particular age. So perhaps, 18-year-old sent
10 million text messages. 19-year-olds sent 9,800,000
text messages, and so forth. So each worker node
performs this task for all of the customers whose
data are stored on that node. And those results,
then, are returned back to the master node. The master node will
then combine the results. So it will calculate, for
example, the total number of 18-year-olds and the total
number of text messages sent by 18-year-olds. After which it can divide
those two numbers in order to produce the
average number of text messages sent for 18-year-olds. That process is simply repeated
or customers of every page. And we then have the results,
which we can send back to the user who requested them. So I hope that you can
appreciate that this MapReduce process is a very clever way
of efficiently handling data processing problems on
distributed database environments by taking
advantage of parallelization in order to solve the problem. Well, my friends,
thus ends our overview of big data, data warehousing,
and business intelligence applications. And more broadly speaking, thus
ends our series of lectures on database technologies. It is my sincere
hope that you have found these lectures useful,
and most importantly, that you now have
the self-confidence to go out and start creating and
working with these databases. It has been a great pleasure
to lead you on this journey, and I hope that you
have a great day.

Danny Hutson

75 thoughts on “Database Lesson #8 of 8 – Big Data, Data Warehouses, and Business Intelligence Systems

  1. Thank you Dr Soper , they were very helpful , you explained in a simple but comprehensive way and covered almost all common topics in database with clear explanations and examples

  2. At 23:00 you spoke of rigid Normalization compromising the speed of queries, thanks for that eye opener; somehow I always thought better normalization would have sped up querying. In SQL server however, would the use of a View assist with the speed of data retrieval?

  3. props man, I listened to all 8 of these lectures. took 3 days, but these are very helpful. Thank you for making these. 

  4. I also want to add my thanks and gratitude for these 8 excellent tutorials where you've made some rather abstract topics very easy to understand. I passed my MTA yesterday and so now onto tackling the MCSA. Thanks again Dr DS!

  5. i have seen all your videos and i really thank you for this and i hope that you can make more videos for other aspects because your way anyone can understand from

  6. topics are well explained, thanks! (may be more emotions in speech tone next time, just a tiny bit 🙂 thanks again!

  7. Thank you for very useful presentation. Presented in clear, concise and structured way. In particular, your style is unpretentious and your approach simple and that facilitates quick learning. Thank you!

  8. Thanks for ur generosity in sharing your knowledge………………
    People like you make this world a beautiful place to live………….

  9. Thank you for you sharing your knowledge with us.
    Microsoft Virtual Academy trainers should take NOTES on how to make something good that everyone can understand and learn from it.
    I wish you all the best and once again thank you for sharing. I've learned in 10 hours what I cud not in 3 books.

  10. I have learned a LOT from your videos, and loved all of them…thank you so much for helping me as I start my career as a DBA. Thanks again

  11. Thank you for your clear explanation on various database concepts and technologies!
    I feel more confident on listening to your lecture series #1 to #8.

  12. ~Become an expert in Data Warehousing and Business Intelligence techniques covering concepts like DW Architecture, Data Modeling, ERwin, ETL fundamentals, Business Reporting and Data Visualisation

  13. ^Become an expert in Data Warehousing and Business Intelligence techniques covering concepts like DW Architecture, Data Modeling, ERwin, ETL fundamentals, Business Reporting and Data Visualisation

  14. Thank you Dr. Soper! That was the best course I've seen on YouTube for many years. It is the best both in terms of material and the way how it was presented. The slow speed of the lectures helps to understand better. If someone is not satisfied with the speed just click 'gear' icon and increase speed.

  15. Thank you Dr. Daniel, your videos have been a God sent. I don't know how I would have gotten through this last semester without them.

  16. Thank you so much for your lectures. Your lectures are really interesting and useful. I did learn a lot
    Once again, thank you!

  17. Thank you very much Dr.Soper, these are best lectures which are very useful . Thanks for your sharing. Thumb up!

  18. Thnx Dr.Soper its really an excellent tutorials, is there any other advanced tutorials of database?

  19. Thank You Dr. Daniel Soper. This video has been tremendously informative and useful. The manner in which you present the information is what makes it interesting to listen to. I am going back through all the videos again to absorb the material. Thanks

  20. Thank you Dr. Soper for making these videos. I particularly enjoyed your simple examples to illustrate the concepts.

  21. i suggest watching this on 1.5 speed. his lectures are very informational but if i watch them in normal speed i could fall asleep lol

Leave a Reply

Your email address will not be published. Required fields are marked *