will write some experience through this project
Several items that I’d like to touch are listed below:
1. SSIS tips
Development
We have 3 types of packages: daily, weekly and monthly.
Daily pkgs will pick up new records everyday and aggregate into the summary table.
For daily changed reference data, we do not pick up untill the monthly pkgs kick in.
That’s our version to Slowly Changing Dimension (ideally, SCD should be used)
weekly pkgs will truncate the fact table and bring 3 month new records
monthly pkgs will truncate dimension tables and bring all records
Deployment
So far, the deployment is using file system and import pkgs via SSMS. That’s not the best way though but DBA feels more comfortable in this way
Debugging
We have created a sysssislog table which contains all log information. If data error or pkg error occurs , dba or am will get an email immediately.
When we direct error output to another table in OLE DB destination, make sure it’s using table/view instead of fast load. If fast load is used, if the stream hits any bad data, ssis will direct all data after the bad row into the error table no matter if it’s bad or not. Be careful!
2. SSAS tips
Development
Deployment
Debugging
3. Version Control (SVN)
No comments:
Post a Comment