Database Reference
In-Depth Information
Figure 2-1.
A BI solution overview
The data warehouse you create is designed on the principles of an online analytical processing (OLAP) style
of database utilizing dimensional and fact tables. This is a different style of design than the online transactional
processing (OLTP) databases that most developers are familiar with. The design differences are based upon their
purpose. Databases that focus on gathering new data are designed around the OLTP format. The OLAP format
focuses on providing information from existing data. As you will see later, you work with both OLTP and OLAP
databases in a BI solution. OLAP databases come in two common forms: relational databases and cube databases.
Relational databases use tables to contain the reporting data, while cube databases use cubes instead. This makes
sense when you remember that the terms
tables
and
relations
are synonymous in database terminology.
In a BI solution, data warehouses are created using relational databases in an OLAP format. Nevertheless,
you may also create an OLAP cube in addition to the data warehouse. Note that in Figure
2-1
, we have displayed
this connection between these two objects with a dotted line, indicating that the cube database represents an
optional component.
Not all BI solutions need a cube database. In fact, many companies choose to create reports using the
data warehouse alone. In Figure
2-1
, the thin lines from the data warehouse to the reporting options represent
this standard scenario. In addition, it is still possible to pull report data from the original online transaction-
processing (OLTP) databases when needed, indicated in Figure
2-1
.
The data warehouses and cubes provide additional options that make these structures desirable. For
instance, because SSAS cubes host data mining capabilities, you can pull data mining results to your reports
through your cubes. Another advantage of having a cube is that a variety of reporting applications are available
designed to work with cubes alone.
Interviewing and Isolating Data
In any BI solution, the first course of action is interviewing the client or company owner that needs the solution.
Because we do not have real life clients to interview, we describe the scenario here. Let's consider the following
as our letter of engagement:
