Could someone please explain the pros and cons of building an OLAP cube
in the following 2 ways:
1.) right from the data in a transactional database using data views

2.) from a star schema dimensional model which is populated by pulling
data from the source system

Thanks!

1. Pros: can be real-time or near rel time. good if your source has a good
data quality and you don't need to transform your data
Cons: impact your source system (you have to read directly this can cause
performance issues for your DB users); business rules can be difficult to
apply (if any); a change made in the source database (like a schema change)
can break your cubes; difficult (or not possible) to create slow changing
dimensions

2. Pros: Isolate your B.I. needs from your operationnal needs (your source
database can change but not the needs); better data quality & integrity; can
be used for reporting too (not only OLAP usage); minimize impact on source
system; can integrate data from multiple sources easely (like budgets,
finances, production...); designed to keep historical data and support for
slowchanging dimensions
Cons: Real-time more complex to setup; longer overall setup (create a
database & create ETLS vs create views)


<jimdefruscio (AT) yahoo (DOT) com> wrote