Synchronizing objects
We created tables, dimensions, and a cube; and new tables were automatically created for each dimension and cube. We then created mappings to map data from tables to tables, dimensions, and a cube. What happens if, let’s say for example, a table definition is updated after we’ve defined it and created a mapping or mappings that include it? What if a dimensional object is changed? In that case, what happens to the underlying table? This is what we are going to discuss in this section.
One set of changes that we’ll frequently find ourselves making is changes to the data we’ve defined for our data warehouse. We may get some new requirements that lead us to capture a new data element that we have not captured yet. We’ll need to update our staging table to store it and our staging mapping to load it. Our dimension mapping(s) will need to be updated to store the new data element along with the underlying table. We could make manual edits to all the affected objects in our project, but the Warehouse Builder provides us some features to make that easier.
Changes to tables
Let’s start the discussion by looking at table updates. If we have a new data element that needs to be captured, it will mean finding out where that data resides in our source system and updating the associated table definition in our module for that source system.
Updating object definitions
There are a couple of ways to update table definitions. Our choice will depend on how the table was defined in the Warehouse Builder in the first place. The two options are:
- It could be a table in a source database system, in which case the table was physically created in the source database and we just imported the table definition into the Warehouse Builder.
- It could be a table we defined in our project in the Warehouse Builder and then deployed to the target database to create it. Our staging table would be an example of this second option.
In the first case, we can re-import the source table using the procedures generally used for importing source metadata. When re-importing tables, the Warehouse Builder will do a reconciliation process to update the already imported table with any changes it detects in the source table. For the second case, we can manually edit the table definition in our project to reflect the new data element.
For the first case where the table is in a source database system, the action we choose also depends on whether that source table definition is in an Oracle database or a third-party database. If it is in a third-party database, we’re going to encounter an error. Hence, we’ll be forced to make manual edits to our metadata for that source until that bug is fixed. If the table is in an Oracle database, re-importing the table definition would not be a problem and it will do the reconciliation process, picking up any new data elements or changes to the existing ones.
For a hands-on example here, let’s turn to our new project that we created earlier while discussing snapshots. We copied our POS_TRANS_STAGE table over to this project, so let’s use that table as an example of a changing table, as we defined the table structure manually in the Warehouse Builder Design Center and then deployed it to the target database to actually create it. For this example, we won’t actually re-deploy it because we’ll be using that second project we created. It doesn’t have a valid location defined, but we can still edit the table definition and investigate how to reconcile that edit in the next section.
So, let’s edit the POS_TRANS_STAGE table in the ACME_PROJ_FOR_COPYING project in the Design Center by double-clicking on it to launch it in the Data Object Editor. We’ll just add a column called STORE_AREA_SIZE to the table for storing the size of the store in square feet or square meters. We’ll click on the Columns tab, scroll it all the way to the end, enter the name of the column, then select NUMBER for the data type, and leave the precision and scale to the default (that is 0) for this example.
We can validate and generate the object without having a valid location defined, so we’ll do that. The validation and generation should complete successfully; and if we look at the script, we’ll see the new column included.
We now need a mapping that uses that table, which we have back in our original project. Let’s use the copy and paste technique we used earlier to copy the STAGE_MAP mapping over to this new project. We’ll open the ACME_DW_PROJECT project, answering Save to the prompt to save or revert. Then on the STAGE_MAP mapping entry, we’ll select Copy from the pop-up menu. We’ll open the ACME_PROJ_FOR_COPYING project and then on the Mappings node, select Paste on the pop-up menu.
We ordinarily won’t copy an object and paste it into a whole new project just for making changes. We’re only doing it here so that we can make changes without worrying about interfering with a working project.