These are very high level overview of how to move data from a traditional database or transactional database to a data warehouse database. It provides vital clues to get you started on a productive road to success!
Steps to move data from a transactional database to a data warehouse type of database.
The general steps are move data from transactional database to the staging area of the data warehouse database. Then move data into a staging table, then finally move the data to the final table of the data warehouse.
Steps Details:
- Move data from transactional database to staging area. Normally move the data into a table prefixed with “scr” so it can be easily identified where the data came from. This step basically tries to capture the data as is from the source database. Most times data fields are changed to text type so that no data transformation takes place.
- The second step is to move the data from the scr table into a table called staging. In general this table is prefixed with the word “scr” so the content of the table is easily distinguished. Just like the source table. It is important to keep data organized to keep sanity in the house.
- Inspect the data that exist in the staging table and note down facts and dimensions. And create facts and dimension tables in the data warehouse.
- Move the data into the data ware house. Data will be copied into their various facts and dimensions tables. Example of a dimension is date and cities. Example of fact is name of a person. The idea is that dimension can be re-used.
- Finally create views that will link together tables based on the business requirement.
Leave a Reply