Changing SEQUEL Data Warehouse Once its Built

 

Data Warehouses are always evolving and growing to meet the needs of your organization. New tables will be added on a regular basis, and you will need to modify existing tables too. New columns may be required and the attributes of existing columns may need to be changed to match changes to data sources. Sequel Data Warehouse makes all of this very easy to do.

For example, lets consider a change to to Account Number. In the source database it has been increased from a 7-digit column to a 9-digit column. We know that Account Number is in multiple source tables and  Sequel Data Warehouse tables, but we're not sure which ones. Here are the steps in Sequel Data Warehouse:

1. Run the Change Data Element Attributes  process to change the column attributes in all Sequel Data Warehouse data warehouse tables. This single process will identify the tables that include the changed column and make the table change (in this example, increase it from 7 to 9 digits) in each table. All existing data will be retained. An audit report is produced, identifying the changed tables.
2. Run the Verify Sequel Data Warehouse Data Sources  process to identify the changed tables in your source applications. This will update Sequel Data Warehouse's metadata to reflect the changes and produce an Impact Analysis Report, highlighting the source tables with the changed account number column. The report will also list the ETL definitions (Extract Programs) that reference these tables, and even show how the changed column is used within the ETL. It may be referenced in one or more rules or transformations, as well as mapped to an output column.
3. Rebuild each affected Extract definition.

It doesn't matter whether the column is in one table or a hundred tables. These simple steps are all that is required. 

Of course, sometimes the change is more involved and may require modifications to the ETL process. The Impact Analysis report tells you which ones need to be changed. You can even run steps 1 and 2 in Report Only  mode, to produce the reports but not actually perform any changes. This allows you to understand the scope of the change and plan before the change is implemented.

If you are a database developer, you will know that some table changes cannot be performed via ALTER TABLE. For example re-ordering columns, adding a new column in the middle of the record layout or changing a data type from numeric to character are not supported by ALTER TABLE. You need to create a new table and manually copy the data from the old table to the new one. Sequel Data Warehouse knows this and does it automatically for you.