The DWD tool makes the addition of additional columns or tables to a data warehouse a fairly straightforward exercise, and it is one of the primary selling points of its creator, Wherescape. One of the great challenges in data warehousing is that when a data warehouse is built, it is difficult to make changes to it. Wherescape’s approach eliminates much of the difficulty with getting these changes right, but the original intent of the tool is for a single customer to be able to make changes to their data warehouse. As QAD is building out its BI solution to package warehouses into different modules that are implemented for many different customers, it is a much more challenging environment to release bug patches and upgrade code. Each customer may have a different set of customizations and installing patches or upgrades to tables and procedures that have customizations can potentially wipe out the customization work and require it to be done again.
As an example, look at a customized dim_item table that then has a patch without the customizations applied to it.
Here is the layout for the dim_item table from the 3.4.1 release. The last several columns are circled for reference purposes.
Here is the layout for the dim_item table for a customer that has a 3.3 customized version of the modules that has the easily identifiable custom columns that begin with an x_ prefix. QAD’s preferred prefix for custom columns now is xx_. This layout also has some other columns that do not match the 3.4.1 dim_item table. These had been added at time of installation with the belief that these same columns would be added to the code base. That ended up not yet happening, so an upgrade to 3.4.1 is likely to require even more work and investigation. Unless columns are already in the code base, treat any additions as custom.
In SQL Server, you can see that the table matches the meta-data observed using the DWD.
And if we open the affiliated procedure, in the DWD, we can see what columns are being inserted into and that they match.
A patch is then applied using the Administrator tool to fix some issue with the table or to upgrade the table to the 3.4.1 version of the code. You can see that it initially says that existing objects are replaced.
However, the default setting for each object during application installation ensures that the changes alter the dim, perm, and fact tables and the updates proceed successfully.
A refresh of the DWD Browser pane to review the changes to dim_item then reveals that the data in the tables looks exactly like the 3.4.1 version of dim_item. Any other columns that had existed for this table in the DWD’s meta-data are no longer shown.
When the patch was applied, we had selected to not change existing tables, only alter them. Why are the other columns gone? Going to SQL Server reveals that they still exist in the database and that any new columns that were found when the patch was applied were placed at the end of the table column list. In this case, net_weight and net_weight_um were new columns in the 3.4.1 table.
Reviewing the affiliated procedure in the DWD shows that all the logic referencing the custom columns has been removed and the new logic only reflects the columns that exist in the 3.4.1 version that was applied.
Note: Because the new columns were added to the table and the table was not dropped, the custom columns would still contain their data, but would no longer be updated by the new procedure.
As you can see, implementing patches and upgrades to tables that have any custom work done to them is not a straightforward process. It likely requires a fair amount of followup work to get the customizations back in place in the DWD meta-data. The underlying SQL table can remain unchanged. Ideally, it is for this reason that custom work is not directly done on standard release tables in the data warehouse, or at the least, the footprint of the customized work mostly falls outside of the baseline code.