Start Consolidating data warehouse tables

Consolidating data warehouse tables

Data may then flow to an operational data store (ODS).

Warehouse Data Flow As the diagram shows, day-to-day operational databases and external sources provide the raw data.

Special logic monitors the data sources for changes and prepares periodic extracts of new data.

Sadly many warehouse initiatives lack staging data.

There is no good reason for omitting staging data in a substantial deployment. The only downside is the effort of creating and maintaining staging data, which can be mitigated with automation.

The ODS provides a single source of normalized operational data.

Thus if there are two sources of account information, there are two staging tables, and one account table in the ODS.

One approach is to write ETL code to load staging data. A2Bdata is the only such tool of which we are aware. It generates staging schema by reading the database catalog for source tables.

In addition it can load periodic extracts of source data into staging tables.

For example, an account dimension would combine one or more account data source tables.

An Example Consider a customer account application. The ‘STG’ prefix in the table name denotes a staging table.

Staging tables accumulate the history of input data from the periodic extracts. They merely accumulate it along with some system metadata.