Transcend Part 1: Mappings
If you read the blog regularly, then you know that Rittman Mead offers a product called Transcend that is designed to interact with the Oracle Database to make some of the heavy-lifting in ETL mappings very simple. Because we recently released version 2.5 of Transcend, I thought I'd talk about some of the features I haven't blogged about before. In the next few posts, I'll talk about "Mapping" entities, "Dimension" entities, and how these can be integrated with standard ETL tools like OWB and ODI. In the current post I'll mainly be talking about Mapping entities
When you think of a Mapping in Transcend, try not to think about the common source-to-target functionality that you would create in an ETL tool or a custom mapping. Instead, try to consider EVERYTHING ELSE that you might need to couple with the mapping that ETL tools don't provide: index maitenance, constraint maintenance, segment-switching, etc. If you look at the above posts, you will see a lot of features that can be implemented with a series of calls. That's not difficult, but it's still code, right? But what if you could "bundle" all of the necessary calls that need to be made BEFORE and AFTER a mapping into one easy name, so that you could pass that name to just two calls--one before the mapping and one after--to get all the necessary features needed to roll that mapping? Well... then you'd have a Transcend Mapping!
In addition to all the heavy-lifting, we wanted to make sure Transcend did the simple things right, so we first created an instrumentation framework that could be used consistently throughout the product. This framework is affectionately called Evolve and includes auditing, logging (with different levels), process registration with the Oracle Database, and a DEBUG mode. So the very basic skeleton of a Mapping in Transcend is the ability to log messages in a logging table, set the MODULE and ACTION contexts with the database, and capture exception errors all the way back to their source. To put together a standard mapping, I would make the following call:
SQL> BEGIN 2 trans_adm.create_mapping( p_mapping => 'map_sales_fact' ); 3 END; 4 / PL/SQL procedure successfully completed. SQL>
Now, whenever I execute the MAP_SALES_FACT mapping in either my ETL tool or my custom ETL processing, I need to make a single call before the mapping runs, and a single call after:
SQL> exec trans_etl.start_mapping( p_mapping => 'map_sales_fact' ); Pre-mapping processes beginning Pre-mapping processes completed PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 SQL> -- here you would execute the mapping SQL> -- notice that the session has been instrumented; SQL> select SYS_CONTEXT( 'USERENV', 'MODULE' ) module, 2 SYS_CONTEXT( 'USERENV', 'ACTION' ) action 3 from dual; MODULE | ACTION ------------------------------ | -------------------------------- mapping map_sales_fact | execute mapping 1 row selected. Elapsed: 00:00:00.00 SQL> exec trans_etl.end_mapping( p_mapping => 'map_sales_fact' ); Post-mapping processes beginning Post-mapping processes completed PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL>
So at the very least, Transcend provides the ability to instrument the mapping so the session can be identified easily in V$SESSION, as well as setting a consistent environment for tracing sessions with DBMS_MONITOR, which allows for tracing sessions based on combinations of MODULE and ACTION. But there is a lot more we can do with Transcend. We can easily incorporate index maintenance by changing our configuration slightly. All the features previously described around index maintenance can be turned on and off with a simple configuration change (NOTE: the 'both' parameter specifies that we want to mark indexes both UNUSABLE before the mapping and USABLE again after the mapping. We could have also passed "unusable","usable" or "ignore" ):
SQL> BEGIN 2 trans_adm.modify_mapping( p_mapping => 'map_sales_fact', 3 p_table => 'sales_fact', 4 p_owner => 'stewart', 5 p_indexes => 'both', 6 p_index_type => 'bitmap', 7 p_idx_concurrency => 'yes' 8 ); 9 END; 10 / PL/SQL procedure successfully completed. SQL>
Now I simply execute the exact same calls I made before, but now the new functionality is implemented:
SQL> exec trans_etl.start_mapping( p_mapping => 'map_sales_fact' ); Pre-mapping processes beginning 5 indexes and 0 local index partitions affected on table STEWART.SALES_FACT Pre-mapping processes completed PL/SQL procedure successfully completed. SQL> -- here you would execute the mapping SQL> exec trans_etl.end_mapping( p_mapping => 'map_sales_fact' ); Post-mapping processes beginning Rebuild processes for unusable indexes on 28 partitions of table STEWART.SALES_FACT submitted to the Oracle scheduler No matching unusable global indexes found Post-mapping processes completed PL/SQL procedure successfully completed. SQL>
Additionally, all the constraint maintenance features described here can be implemented with another call to MODIFY_MAPPING:
SQL> BEGIN 2 trans_adm.modify_mapping( p_mapping => 'map_sales_fact', 3 p_constraints => 'both', 4 p_constraint_type => 'P', 5 p_con_concurrency => 'no' 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> exec trans_etl.start_mapping( p_mapping => 'map_sales_fact' ); Pre-mapping processes beginning 5 indexes and 0 local index partitions affected on table STEWART.SALES_FACT 1 constraint disablement process for STEWART.SALES_FACT executed Pre-mapping processes completed PL/SQL procedure successfully completed. SQL> -- here you would execute the mapping SQL> exec trans_etl.end_mapping( p_mapping => 'map_sales_fact' ); Post-mapping processes beginning Rebuild processes for unusable indexes on 28 partitions of table STEWART.SALES_FACT submitted to the Oracle scheduler No matching unusable global indexes found 1 constraint enablement process for STEWART.SALES_FACT executed Post-mapping processes completed PL/SQL procedure successfully completed. SQL>
And finally, we can implement segment-switching using partition exchanges, table renames... and now a new option in 2.5: MERGE statements. As described in the above post, Transcend Mappings can provide the functionality to facilitate getting the rows from one segment into another. So you could create a mapping that loads all the new rows for a fact table, and propagate those changes into another table using a trio of possibilities. Formerly, only the options to do this with partition exchanges and table renames made sense, because those were the most difficult to reproduce in and ETL tool, and thus required custom-coding. As I've worked with numerous clients since writing the initial version of Transcend, I've discovered that some of the non-Oracle ETL tools have a tough time replicated the functionality of a MERGE statement. So now we can MERGE all the rows from one segment into another at the end of an ETL mapping by simply configuring this feature with Transcend:
SQL> BEGIN 2 trans_adm.modify_mapping( p_mapping => 'map_sales_fact', 3 p_replace_method => 'merge', 4 p_staging_owner => 'stewart', 5 p_staging_table => 'sales_stg' 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> exec trans_etl.start_mapping( p_mapping => 'map_sales_fact' ); Pre-mapping processes beginning 0 indexes and 80 local index partitions affected on table STEWART.SALES_FACT 1 constraint disablement process for STEWART.SALES_FACT executed Pre-mapping processes completed PL/SQL procedure successfully completed. SQL> -- here you would execute the mapping SQL> exec trans_etl.end_mapping( p_mapping => 'map_sales_fact' ); Post-mapping processes beginning Number of records merged into STEWART.SALES_FACT: 918843 Rebuild processes for unusable indexes on 16 partitions of table STEWART.SALES_FACT submitted to the Oracle scheduler No matching unusable global indexes found 1 constraint enablement process for STEWART.SALES_FACT executed Post-mapping processes completed PL/SQL procedure successfully completed. SQL>
And we can still use the segment-switching options from the previous version, including the popular "exchange" option:
SQL> BEGIN 2 trans_adm.modify_mapping( p_mapping => 'map_sales_fact', 3 p_replace_method => 'exchange' 4 ); 5 END; 6 / PL/SQL procedure successfully completed. SQL> exec trans_etl.start_mapping( p_mapping => 'map_sales_fact' ); Pre-mapping processes beginning Pre-mapping processes completed PL/SQL procedure successfully completed. SQL> -- here you would execute the mapping SQL> exec trans_etl.end_mapping( p_mapping => 'map_sales_fact' ); Post-mapping processes beginning 6 index creation processes submitted to the Oracle scheduler for STEWART.SALES_STG Creation of constraint SALES_STG_PK executed 1 constraint built for STEWART.SALES_STG STEWART.SALES_STG exchanged for partition SALES_Q4_2003 of table STEWART.SALES_FACT 1 constraint dropped on STEWART.SALES_STG 6 indexes dropped on STEWART.SALES_STG Post-mapping processes completed PL/SQL procedure successfully completed. SQL>