QAD BI v3 - Tech Level 2 Certification - Part 3 > Customizations of BI v3 > Customization Exercise 1
  PPT
Customization Exercise 1
 
Use the steps from the prior slides to add this column to the data warehouse.
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
 
In this instance the column only appears as far as load_cm_mstr. It does not appear in the subsequent stage_customer_master table.
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
 
Customization Exercise
 
By confirming results, for instance, you shouldn’t expect to see more results in your results sets than the count of the records in the target table.
Customization Exercise
Customization Exercise
 
If it is difficult to place the new custom column(s) to the end of the column list, an alternate option is to go to the last column, copy the order number of that column, paste it in to the custom column order number and then add a number to it, thereby ensuring that the numbers of the custom column(s) is/are the highest.
Customization Exercise
 
This can easily be accomplished by dragging the column over from the final staging table the custom update procedure is going to utilize.
First find the target table in the Builder/Browser pane and click on it once so it’s columns show up in the Target pane. Then in the Source pane, find the final stage table to be referenced by the custom update procedure. If you don’t see that last stage table in the Source pane, try Refreshing the Connection from the Connection button. Once the table is found in the Source pane, double click on it to find the columns to be added and drag and drop them into the target pane. Make sure to place them at the end of the column list.
Customization Exercise
Customization Exercise
 
Create(Recreate) on fact/dim/perm tables in systems that are already populated with data will create all sorts of headaches as the data in the table gets truncated and the table rebuilt. Dimensions are the worst since they are referenced by multiple fact tables and may not populate the same way, so all the keys will be broken.
Customization Exercise
 
Customization Exercise
 
Note that when adding columns to some types of tables like dim_tables using the Validate against database option, the Alter table commands window will sometimes include problematic command logic like dropping all the columns except for the new custom column. This would be completely disastrous for the dim as we would then have a dim table with only one column. Remove all the other logic here so that all that remains is the ALTER TABLE ADD column command.
Customization Exercise
Customization Exercise
Customization Exercise
 
Things to be replaced in the procedure includes the comments at the top of the page, the procedure name, the table truncate portion, the insert script (replace with the update script), the insert_count variable which needs to be replaced with the update_count variable, and any comments so they properly refer to the dim_customer table (instead of work_om_change_list1).
Customization Exercise
 
Be sure as you go through the old code that was copied to change the procedure name, any reference to that procedure or table to point to the correct procedure name or table. Also remove any truncates if this is an update script and point the post update record count (after error check) to point at the update variable, not the insert variable. Clean up any other update notes to make sure everything is relevant (for instance, there is a warning message for updates on the update_work_om_change_list1 procedure that can be completely removed for our new update procedure).
Customization Exercise
Customization Exercise
 
For instance the custom update procedure for the new target table column should come after all other tasks relative to that table.
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise
Customization Exercise