How to Update the Data required for Datawarehousing / Data Analysis

[tapks]

I am new to OLAP / Datawarehousing & WORKING ON SQL Server 2000.

I know that for the above you need a lot of data of previous years. Once the above task is over, You have to constanly update the OLAP database with the new set of records generated by the users in OLTP environment.

My question is, how can I constantly update my OLAP database in a particular frequency (when I don't have any datetime field for the transactions in OLTP environment or even if I have the datetime field) ?

Or if anybody can suggest how to proceed in this case ? Is there any way out ?

Thankx in advance.

Tapks

 

[ShyamRewaskar]

you ll ve to schedule the extracting programs.

 

[tapks]

ShyamRewaskar!

But where? Is ther any option in the Cube / Dimension ? Or any where else?

 

[ganotedp]

You have a at least a couple of options to determine what's changed when you lack a datetime field for the transactions in OLTP environment.
1. Batch solution: Make periodic snapshots of the OLTP data, then after every snapshot do comparisons to see what's changed.
2. Low-latency solution: Use triggers on the OLTP tables that push inserted/updated records to a separate file.

 

[teccum]

You have to create a batch program or you can also schedule the extraction programs by putting it in a package and then schedule the package. You can also schedule the package using the windows NT scheduler services. The scheduling activity normally depends on the lowest granularities decided for the datawarehouse. For example if it is for time dimension and if you have decided the lowest granualarity as day, then you need to capture the data at the day level itself and it is same for all other dimension(which you normally will take care while writing the scripts).

Hope this helps

vijay

 

[necon]

One year ago, I was looking for an ETL tool that could help me to capture, extract, transform and load the inserted, updated and deleted, during the last day, records from an Oracle OLTP to an Oracle star schema.
I found nothing except some very expensive solutions (more than $200K). Finaly I created database triggers and wrote my own sql procedures in order to extract, transform and load the changed data.
Now I have a solution where every day, I am extracting about 80.000 inserted rows, 20.000 updated rows and 2.000 deleted rows from an Oracle OLTP in less than 10 min. Then it takes about 2 hours per day to update a star schema incrementaly, creating surrogates and doing data transformations, taking care for slow changing dimensions, etc.
It was not easy. I don't really know if I followed the best approach, actually I though, (and still believe) that this was the only feasible approach.
In my case It was very important to minimize the extraction time (10 min was a success)
Hope that this help

necon