How to build good Data Warehouse Structure - 03-15-2005 , 12:44 AM
I'm new to OLAP, and just tried build OLAP & Data Warehousing using
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Thx in advance
Re: How to build good Data Warehouse Structure - 03-15-2005 , 07:59 AM
There is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v (AT) yahoo (DOT) com> wrote