Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
danielagger988
Starting Member
2 Posts |
 Posted - 2010-11-19 : 08:29:38
|
| I am Integrating 3 different database sources into an ODS. The ODS is going to have the exact tables and columns as the source system. I just tried to load one sample table (3 columns) with 2 million records, just to check the systems. It took around 30 minutes, I don’t want to go into asking you guys how to optimize it. My plan is to create a schema, and the table structure for all the sources in the ODS in the first week and once the structure is ready I will start loading data from one database source at a timeQ1) Is there a golden rule or method to follow while creating the table structure or should I just start doing the create table scripts for all the tables in the ODS? Q2) Since the tables are relational in the source system, and my guess is that ODS need not have any relation among tables. So I don’t have to create any constraints (like PK, FK) while writing the create scripts. Correct me if I am wrong. Q3) I am going to use SSIS to load data, so how do I create the packages, should I have each package for each source or should I break up the tables, say 5 tables in one package. Does this affect the performance? It would be really great if you could answer these questions and also give me some tips/advice. I owe you guys. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-19 : 10:54:02
|
| In our case we're using one SSIS package per ODS table. It will involve entire logic to get data for table which might include some lookups to other related tables too.ODS tables will store the history in our case. The tables there make use of Surrogate keys which are generated for business key combinations.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 11:14:11
|
| I would create the structure in execl - a worksheet for the each table and columns.Specify in that where th dta is sourced.From this generate the database structure.From the same data generate the objects to populate the data (whether it's ssis packages or SPs or whatever).Also generate the statements to run them.Then all you have to do is change the spreadsheet and your system will be automaticaly generated. You get the documentation automatically in step with the implementation.Follow the same process for generating datamarts and you reach a situation where you don't have to do much in the way to implement updates. For bigger systems I just run this overnight so any changes made during the day are incorporated (or over the w/end if it takes more time).If you have hundreds of tables you don't want to be maintaining individual packages - just changing logging would be a huge overhead.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Building ODS