Search code examples
sql-serverdata-warehouseoltp

Transforming OLTP Relational Database to Data Warehousing Model

What are the common design approaches taken in loading data from a typical Entity-Relationship OLTP database model into a Kimball star schema Data Warehouse/Marts model?

  • Do you use a staging area to perform the transformation and then load into the warehouse?
  • How do you link data between the warehouse and the OLTP database?
  • Where/How do you manage the transformation process - in the database as sprocs, dts/ssis packages, or SQL from application code?
Solution

  • Personally, I tend to work as follows:

    1. Design the data warehouse first. In particular, design the tables that are needed as part of the DW, ignoring any staging tables.
    2. Design the ETL, using SSIS, but sometimes with SSIS calling stored procedures in the involved databases.
    3. If any staging tables are required as part of the ETL, fine, but at the same time make sure they get cleaned up. A staging table used only as part of a single series of ETL steps should be truncated after those steps are completed, with or without success.
    4. I have the SSIS packages refer to the OLTP database at least to pull data into the staging tables. Depending on the situation, they may process the OLTP tables directly into the data warehouse. All such queries are performed WITH(NOLOCK).
    5. Document, Document, Document. Make it clear what inputs are used by each package, and where the output goes. Make sure to document the criteria by which the input are selected (last 24 hours? since last success? new identity values? all rows?)

    This has worked well for me, though I admit I haven't done many of these projects, nor any really large ones.