datawarehouse with DTS + stored procs only ?
-
what about using dts as etl tool and stored procs for delta's / transformations / lookups / etc. for a datawarehouse ?
Is there a catch or is this just a very cheap and usefull alternative to etl software on the market ?
thanks for thoughts on this matter
Edited by - hbkdba on 08/01/2003 05:12:54 AM
-
We don't have any datawarehouses but have datamarts (corner shop variety). I use procs, dts, scheduler for whole process from data extraction to cube build. It works for me
Far away is close at hand in the images of elsewhere.
Anon. -
That's what we use just DTS and sp's for the transformations. Works well for us. Their is a learning curve with DTS, and DTS does have it's quirks, but it's a great alternative to the very expensive ETL products on the market.
Diane
-
@davidburrows thanks for sharing. What in fact is a datamart ?
@dm thanks, can you give an idea what the quirks are ?
Currently we have a very expensive ETL tool, the second by the way, neither of them was worth a penny (both brought in by external "specialists").
-
I think it is one of those buzz words that got around. I understand it as a datawarehouse but on a small scale or part of a datawarehouse.
Datawarehousing is not really my forte. All we do is generate landing tables from original data, create lookups and then the cube. We currently equate one cube (aka datamart) = one database whereas I suppose a datawarehouse would consist of many cubes looking at different parts or the warehouse.
Far away is close at hand in the images of elsewhere.
Anon. -
DavidBurrows, I've just "googled" the web on this matter and it seems that a datamart is designed for 1 purpose where a datawarehouse has a larger (probably company-wide) scope. This reflects in a smaller database and a different database-design for the datamart.
-
We use sp's and DTS to do our DataWarehouse and I couldn't be happier. There is a learning curve but that is due to data warehouse itself (facts and dimensions etc). Our warehouse is currently 26+ Gigs and we load it with delta's daily in under 2 hours (along with the recon reports for balancing).
I personally have looked at one other tool for ETL and quite honestly, it was too much like work. It was just another layer to learn and then find a way to trick it to do what you need.
If you want flexability with low cost, stick with DTS.
-
quote:
Our warehouse is currently 26+ Gigs and we load it with delta's daily in under 2 hours (along with the recon reports for balancing).Our datawarehouse is currently only ODS (ADS to be started soon) and 160+Gigs. Our daily loads (including reindexing and reporting) must stay under 12 hours. Can you give an idea on the size of your delta's ? (fi: 20% delta's a day)
Thanks
-
We are currently phasing out our load as we were bought by another company.
We use to take 8-10 hours to load and by modifiying the Kimball approach we were able to pair it down to under 4 hours at our peak (12000 changed/added loans per day). The biggest time savers were to offload some processing that didn't have to run at the actual load time (reseting of some current record indicators) also we stopped using work keys with transaction data such as GL data. It by definition is historical so there is no reason to keep duplicating it just to have work keys when natural keys would do.
-
quote:
the Kimball approachCould you explain this ?
quote:
peak (12000 changed/added loans per day)How many rows / day is this ?
- for update
- for insert
- probably for delete
Thanks
-
Kimball -- Ralph Kimball is one of the original designers of Data Warehousing. His approach is using Fact and dimension tables in a star schema (snowflake if it gets more complicated). You load the dimensions first and then the fact table(s). I would suggest you find one of his books (or any book that deals with the "Star Schema" for data warehousing) or even visit his website(www.ralphkimball.com).
As for the delta volume, we didn't track changes vs new data because in both cases you add a new record and you never delete the old records. Here is an old report for the whole of March 2001.
object_counted curr_cnt prev_cnt growth growth
------------------------------ ----------- ----------- --------- --------
DIM_CALENDAR 109938 109938 0 0.0
DIM_CONS_DIRECT_LOB 34 34 0 0.0
DIM_CUSTOMER 40666 40505 161 0.39
DIM_INTERNAL_PARTY 28456 23308 5148 22.08
DIM_INVESTOR_COMMITMENT 4143 3331 812 24.37
DIM_LOAN_ARM 7206 6936 270 3.89
DIM_LOAN_FEE 0 0 0 0.0
DIM_LOAN_ORIGINATION 28626 22884 5742 25.09
DIM_LOAN_PRODUCT 779 754 25 3.31
DIM_LOAN_PROPERTY 537 537 0 0.0
DIM_LOAN_SERVICING 0 0 0 0.0
DIM_NON_CON_CURRENT_STATE 0 0 0 0.0
DIM_PURCHASE_COMMITMENT 14332 11828 2504 21.17
DIM_ROLE 6 6 0 0.0
DIM_SALES_ORGANIZATION 1529898 1424981 104917 7.36
FCT_CHAR_TO_SUB_CHAR 573638 573024 614 0.10
FCT_INDICATOR 421220 420884 336 0.07
FCT_INTERNAL_ORGANIZATION 4908053 3878194 1029859 26.55
FCT_INVESTOR_COMMITMENT_NOTE 0 0 0 0.0
FCT_LOAN_BORROWER_CHAR 3878612 3545001 333611 9.41
FCT_LOAN_CANCELLATION_REASON 788134 777786 10348 1.33
FCT_LOAN_PRICE_ADJUSTMENT 293247 260808 32439 12.43
FCT_LOAN_PRODUCTION 2722204 2498816 223388 8.93
FCT_LOAN_SPECIAL_FEATURE 811939 738500 73439 9.94
FCT_LOAN_STATUS 13938563 13105883 832680 6.35
FCT_LOAN_TERM 2722213 2498823 223390 8.93
FCT_PEOPLESOFT 815495 1682487 133008 7.90
TOTAL DATABASE SIZE IN Megs 22510 15177 7333 48.31
I appoligize for the format of this. I suggest you cut and paste it into excel to format it into a readable document.
Edited by - songmeister on 08/04/2003 12:04:11 PM
Edited by - songmeister on 08/04/2003 12:09:39 PM
-
songmeister, thanks for all your usefull info,
greetings,
Steven.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply