Building ODS and Data Warehouse

[OilersDBA]

Hi guys,

I am currently finishing off my high level structure for the new
business intelligence database system for my company and without going
into too much detail I would like to get your comments on it. Maybe
there is something you have tried that did/didn't work. Any comments
at all would be appreciated.


I have a pretty standard system plan in place. We have about 10 OLTP
systems/databases currently and all vary in the amount of traffic from
low (5 transactions a minute) to heavy (5-30 rows a second). We have
thousands of financial terminals all over the world and these
terminals feed our OLTP servers in near real time. Our customer
service team must have near real time access to this data so this is
where I want to make use of an ODS. My thought is to develop a
messaging system from all of the OLTP systems and stage this data to
this ODS. This will be where the tech support/ops/customer care teams
can have instant access to the data and be able to support the
customers with a real time view to the session events. Much of our
daily reporting and information gathering is done by the
afforementioned teams. Customer payments, machine health, peripheral
activity and software events are all gathered and sent back. I am
designnig the ODS so our employees can make quick decisions based on
the incoming events. Separate from the ODS, I am also going to
implement a data warehouse. This area will contain more long term
trending of transactions and other data used for executive decisions.
I have already designed and successfully built data warehouses before
so I am very confident in this area. The area that I do have a few
questions is the bridge between the ODS and the data warehouse. I have
read many articles dealing with real time data warehousing and ODS ETL
and many people have different opinions on what they think is the way
to go. My initial thought is to stage OLTP data to the ODS in near
real time. From there I will stage the data into the data warehouse
every night as we have the hardware to do it this frequently. So my
question is:


Should I run two systems (ODS/DW) side by side for long periods of
time and use the systems as separate entities for different groups of
people or should I use the ODS for say just the current day and the
run any earlier reporting off the data warehouse?


I know having redundant data costs money and greater effort to manage
but we have the resources to back this if need be. Our OPS team needs
data in real time, our financial team needs it daily and the executive
team needs it monthly so the ODS would serve the OPS team pretty much
exclusively.


Does anyone have any general comments to describe their experiences in
designing and building similiar systems? Our data flow is not overly
large right now but it is growing exponentially every month and I want
to be ready for it years down the road. Thanks for reading my long
winded article and I would love to hear all of your thoughts. Thanks in advance.


Corey

 

[johnherman]

It sounds like you have given considerable thought to the design of the system. However, the driving force for the design as well as the availability of information is business requirements. Your information (reporting, analysis, exports to other systems) requirements will drive the amount of data on each ODS and DW and the degree of redundancy. In addition, I will make the assumption that the ODS is in near normal form while the DW is in star schema. These designs may also have impact to the amount of data stored in ODS vs DW because of the way your end-user tools will access those structures.

I suggest you make use of a "batch" or "cycle" solution when moving data from the ODS to DW. This makes it easier to track data lineage, to recreate or republish data, and to purge data from the ODS.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright