Database Reference
In-Depth Information
•
In our experience, the OLTP system is very often poorly documented. Our
first task is, therefore, that of creating good documentation for the system,
validating data, and checking it for any inconsistencies.
The OLTP database is not built to be easily queried for analytical workloads,
and is certainly not going to be designed with Analysis Services cubes in
mind. Nevertheless, a very common question is: "do we really need to build a
dimensionally modeled data mart as the source for an Analysis Services cube?",
and the answer is a definite "yes"!
As we'll see, the structure of a data mart is very different from the structure of an
OLTP database and Analysis Services is built to work on data marts, not on generic
OLTP databases. The changes that need to be made when moving data from the
OLTP database to the final data mart structure should be carried out by specialized
ETL software, such as SQL Server Integration Services, and cannot simply be
handled by Analysis Services in the
Data Source View
.
Moreover, the OLTP database needs to be efficient for OLTP queries. OLTP queries
tend to be very fast on small chunks of data, in order to manage everyday work. If
we run complex queries ranging over the whole OLTP database, as BI-style queries
often do, we will create severe performance problems for the OLTP database. There
are very rare situations in which data can flow directly from the OLTP through to
Analysis Services, but these are so specific that their description is outside the scope
of this topic.
Beware of the temptation to avoid building a data warehouse and data marts.
Building an Analysis Services cube is a complex job that starts with getting the
design of your data mart right. If we have a dimensional data mart, we have a
database that holds dimension and fact tables that can contain transformed, cleansed,
and calculated information. If, on the other hand, we rely on the OLTP database,
we might finish our first cube in less time, but our data will be dirty, inconsistent,
and unreliable, and cube processing will be slow. In addition, we will not be able to
create complex relational models to accommodate our users' analytical needs.
The data warehouse
We always have an OLTP system as the original source of our data, but when it
comes to the data warehouse, it is can be difficult to answer the apparently simple
question of whether we have a data warehouse. The problem is not the answer, as
every analyst will happily reply "Yes, we do have a data warehouse"; the problem is
in the meaning of the term "data warehouse".
Search WWH ::
Custom Search