| Oracle® Life Sciences Data Hub Adapter Toolkit Guide Release 2.2 Part Number E18734-03 |
|
|
View PDF |
| Oracle® Life Sciences Data Hub Adapter Toolkit Guide Release 2.2 Part Number E18734-03 |
|
|
View PDF |
This section contains the following topics:
To prepare for designing an adapter, you may find it helpful to:
Read Chapter 1, "About Adapters" and Chapter 3, "Developing an Adapter".
Familiarize yourself with the Oracle LSH user interface for creating the relevant object type—Load Set, Data Mart, Program, or Business Area—and read the corresponding chapter of the Oracle Life Sciences Data Hub Application Developer's Guide.
You may want to create a Load Set, Data Mart, Program, or Business Area of each existing type in the user interface and compare each one to the Adapter Areas and Tech Types column values for the corresponding adapter type. You can see the actual settings for shipped Oracle LSH adapters through two views, CDR_ADAPTER_AREAS_V and CDR_TECH_TYPES_V.
Read the chapter on execution in the Oracle Life Sciences Data Hub Application Developer's Guide and the chapter on services in the Oracle Life Sciences Data Hub System Administrator's Guide. The Oracle Life Sciences Data Hub System Administrator's Guide also has chapters on setting up security for some shipped adapters that show different ways of synchronizing security between Oracle LSH and external systems.
Read the Reference Information section about APIs in the Oracle Life Sciences Data Hub Application Programming Interface Guide.
Understand the external system for which you are creating an adapter.
This section contains the following topics:
The functionality required for an adapter depends in large part on whether the adapter's purpose is to load data into Oracle LSH (Load Set adapters), export data from Oracle LSH (Data Mart adapters), transform data (Program adapters) or to allow an external tool to view data contained in Oracle LSH (Business Area adapters). However, the requirements also vary depending on the external system.
Load Set adapters ensure that Load Sets of their type have target Table Descriptors with the same metadata structure as in the source system and must actually transfer data to Oracle LSH. Or, if the external system is Oracle-based, you may also be able to provide the option to create pass-through views so that users can view data live in the source system from Oracle LSH.
During Load Set definition, a Load Set adapter may need to do the following:
Receive information from the user on the location from which to upload or copy metadata structures; see "Planning Parameters and Parameter Sets".
Connect to the source data system.
Upload columns or otherwise copy the metadata structure of the data to be loaded to one or more Oracle LSH Table Descriptors and their source Table definitions.
For some adapters, such as the shipped Text Load Set adapter, this is not possible and the user must manually define the metadata structure (Table Descriptor).
Most Load Set adapters are not required during Load Set installation. However, the tech types table stores installation function names and you can write them if you need them; see "Object Installation Functions".
At runtime, a Load Set adapter may need to do the following:
Verify that the transport mechanism is available.
Verify that the structure of the incoming data is the structure expected by Oracle LSH, and return an error if it is not.
Verify the source data currency.
Write data to targets in Oracle LSH.
Return results and status.
Data Mart adapters enable users to create data files for their technology type, containing data from one or more Oracle LSH Table instances—either all data, with audit information, or all data at a particular timestamp (including current data). Depending on the technology, it may be appropriate to create one file per Table instance or to combine all data in a single file. It may be appropriate to zip the file.
A Data Mart adapter does not require custom functions to set up source data structures during Data Mart definition because the source data structures are within Oracle LSH.
Most Data Marts require:
One Planned Output defined object in the Data Mart definition to serve as a placeholder for each Data Mart output file, including the log file; see "Planning Planned Outputs".
Parameters to collect information from the Definer about what data to include or how to generate the Data Mart; see the chapter on Data Marts in the Oracle LSH Application Developer's Guide for examples.
A function to recalculate their Installable status after each Definer modification and checkin; see "Status_Recalc_Function".
Most Data Mart adapters are not required during Data Mart installation. However, the tech types table stores installation function names and you can write them if you need them; see "Object Installation Functions".
Program adapters must do two things that normally require two different technology types:
Enable users to launch an integrated development environment (IDE) from the Oracle LSH Program UI to develop a program that reads data in Oracle LSH tables and either generates a report or manipulates data and writes to target tables.
Run those user-defined Programs in the appropriate processing engine.
The IDE part of the adapter may not require an Adapter Domain or Adapter Area or any other defined objects, but only a technology type and custom functions; see "Planning Adapter Areas" and "Planning Integrated Development Environment Adapters".
When a Definer clicks the Launch button in an installed Program of the technology type you create, the adapter must open the appropriate IDE and make the data in the Table instances mapped to the Program's Table Descriptors available in the IDE.
Since Program IDE adapters allow users to view Oracle LSH data through an external tool, those adapters must coordinate user privileges between Oracle LSH and the external system.
A Program adapter does not require PL/SQL programs to set up source data structures during Program definition because the source data structures are within Oracle LSH.
Programs of the new technology type may require special attributes that you must set up as define-time parameters. Your define_time_function must then collect the user-specified values of these attributes and handle them appropriately.
You may want to call a public API trom the define_time_function to create a Planned Output as a placeholder for the log file; see "Planning Planned Outputs".
Write a function to recalculate their Installable status after each Definer modification and checkin; see "Status_Recalc_Function".
You can use installation functions to synchronize Oracle LSH object metadata with your adapter's external system; for example, download the Program's source code or the values of the Program's define-time Parameters; see "Object Installation Functions".
When a user runs a Program, a Program adapter may need to do the following:
Verify that the required technology is available.
Read Oracle LSH data from the Table instances mapped to the Program's source Table Descriptors and execute the user-defined source code in the appropriate processing engine, either to write data to the Table instances mapped to the Program's target Table Descriptors or to generate a report.
Check the source data currency.
Generate a log file.
Store the output(s) in Oracle LSH.
Note:
A Generic Visualization adapter is available to integrate any visualization tool with Oracle LSH with much less work than creating your own adapter; see Chapter 5, "Using the Generic Visualization Adapter".However, you may want to create your own adapter in order to use the following Business Area functionality, which is not available when you use the Generic Visualization adapter:
Launch IDE from the Business Area
Define joins and hierarchies in the Business Area
Business Area, or visualization, adapters enable users to create a Business Area containing Table Descriptors, Joins, and Hierarchies, instantiate them in the Oracle LSH database, and send the metadata to the visualization system in a form comprehensible to the visualization system. A user can then launch the visualization system from Oracle LSH or log in directly to the external system and view data in the Table instances mapped to the Business Area's Table descriptors. The data remains in Oracle LSH.
If further definition is required in the external system, the adapter must facilitate that. You may need to save these externally made changes back to Oracle LSH under version control. The OBIEE adapter uploads a source code file for this purpose.
Since Business Area adapters allow users to view Oracle LSH data through an external tool, those adapters must coordinate user privileges between Oracle LSH and the external system.
A Business Area adapter does not require custom functions to set up source data structures during Business Area definition because the source data structures are within Oracle LSH. However, you may need to use the definition functions for define-time parameter values or source code.
Note:
Joins and hierarchies are automatically available for definition in any Business Area. The adapter does not have to handle this.At installation, a Business Area adapter may need to push Oracle LSH metadata to the external system and create a corresponding metadata representation appropriate to the visualization system.
For example, the OBIEE adapter creates an OBIEE Subject Area corresponding to each OBIEE Business Area. The first time an OBIEE Business Area is installed, the OBIEE adapter creates a default repository (RPD) file and deploys it on the OBIEE Presentation Server.
You need to provide the URL required to launch the visualization tool and write a function (see "Build_IDE_Cfg_Function") that is tracked in the Tech Types table.
For some external visualization systems (for example, OBIEE), you may need to access the external system in one mode from the Business Area UI and in another mode from the Visualizations subtab of the Reports tab. Oracle LSH calls the build_ide_cfg_function in both cases, but detects whether the user is in the Business Area or in the Reports tab and calls the appropriate environment.
Most external systems require only a single Adapter Area. However, you may want to create multiple Adapter Areas for the same system if, for example, you want to load many different types of data and/or metadata from the same system. This is true of the Oracle Clinical adapter, which has a single Adapter Domain containing multiple Adapter Areas, one for each type of data or metadata loaded.
You need multiple Adapter Areas if the Load Sets, Programs, Data Marts, or Business Areas created for your external system require:
Different define-time or runtime Parameters; see "Planning Parameters and Parameter Sets".
Different predefined fixed data structures matching those in the external system; see "Planning Data Structures".
Your adapter may need multiple technology types but only one adapter type; for example, if your external system is required both as an integrated development environment (IDE) and for execution. The IDE part of the adapter may not require an Adapter Domain or Adapter Area or any other defined objects, but only a technology type and custom functions (primarily the Build_IDE_Cfg_Function). This is true if the IDE requires the user to log in (as SAS does). If the adapter does not require the user to log in , you need to create an Adapter Domain, Adapter Area and other objects.
You must create at least one new technology type for each Adapter Area. Oracle LSH requires that you follow a naming convention to distinguish between technology types:
If the adapter needs to open an integrated development environment (IDE) you must create a technology type with a name that ends with the string DEV. The system calls the Build_IDE_Config_function only for technology types whose name ends with DEV.
This technology type can handle opening two different IDEs if required, using the value that the system supplies to an input parameter of the Build_IDE_Cfg_Function to determine whether the user launched the IDE from the Reports tab or from a Program or Business Area.
If the adapter does not open an IDE, or if it needs to do something in addition to opening an IDE, create a technology type (or an additional technology type) that does not have a name that ends with DEV.
You may need more than one technology type of the non-IDE type if your adapter needs to call two different functions at the same point in processing—that is, if a fork is required.
For example, the Oracle LSH OBIEE adapter has three technology types, CDR$OBIEEDEV for the IDE and two others, CDR$OBIEE and CDR$OBIEETMP. CDR$OBIEE is the base technology type for the OBIEE adapter; the one whose name is in the tech_types column of the adapter area table. Each of the two has both an Execution_Function and a Post_Execution_Function so they cannot coexist in the same technology type. Instead, the Install_Function of the base OBIEE technology type, which the system calls during Business Area installation, calls both execution functions. The execution function of the second technology type is coded to wait until the post-execution function of the first has completed so that the follow occur in order:
Generate the RPD file.
Upload the RPD file to the Business Area Source Code definition.
Deploy the RPD file to the OBIEE Presentation Server.
Restart the OBIEE Presentation Server.
In addition, if your external system uses multiple types of processing—for example, Java and C++—you must create a technology type for each processing type.
Each technology type definition includes:
A unique ID. Contact Oracle Support to get an ID for your technology type that is unique across all adapters developed for use with Oracle LSH.
The object type to be created using the adapter.
A service type; see "Planning Services".
The names of functions and procedures to be used during object installation and execution.
Oracle LSH uses Oracle Warehouse Builder (OWB) for job tracking. Specify one of two possible OWB operators for adapters that run jobs:
CdrPLSQLImmediate_1 for external systems that use PL/SQL to execute their Load Sets, Programs, or Data Marts.
CdrSERVICE_1 for external systems that are located on an operating system; these adapters use the Oracle LSH Distributed Processing (DP) Server to execute their defined objects.
No OWB operator is required to launch an IDE, so an IDE technology type may not need an OWB operator. However, if the adapter needs to take an action in the IDE during or after the launch, its technology type needs the CdrSERVICE_1 OWB operator. For example, the OBIEE 10g adapter needs to copy the RPD file to OBIEE and restart the Presentation Server the first time an OBIEE Business Area is installed. It uses this OWB operator for that purpose.
Note:
There is one additional OWB operator, CdrDiscovererBA, which was hardcoded for the former Oracle Discoverer adapter. Do not use this operator.Services are required to handle the interaction of Oracle LSH with the external system. You must create at least one service type for your adapter. A different service type is required for each technology type. To create a new service type, extend the CDR_SERVICE_TYPEs lookup; see "Adding Lookup Values".
Each Oracle LSH installation that uses your adapter must have a service, service instances, and a service location for each service type required for your adapter defined in the Oracle LSH user interface. Your custom functions can then make use of the local information provided in the service, service location, and service instance definitions, which are stored in the following internal tables:
CDR_SERVICE_LOCATIONS
CDR_SERVICES
CDR_SERVICE_INSTANCES
The system assigns a service instance to each job execution and IDE launch. For information on the function of service instances during job execution, see "Execution Process".
If you need to collect additional information about the local installation of Oracle LSH or your external system, you can use the Details field of the service definition to collect it. The Details field is not required. The shipped adapters use it in different ways that are described in the Oracle Life Sciences Data Hub System Administrator's Guide chapter on services. The user-entered value of the Details field is stored in the DETAILS column of the CDR_SERVICES table (varchar2(2000) BYTE).
This section contains the following topics:
For each action a user performs in the user interface (UI) to define or install a Load Set, Data Mart, Program, or Business Area, or to run a Load Set, Data Mart, or Program or launch an IDE, you must write a PL/SQL function or procedure to do the work.
Oracle LSH designers have tried to anticipate every possible type of PL/SQL function or procedure that might be required by any adapter, and have added columns to the Adapter Areas table and the Tech Types table to store their names and call them at particular times for particular purposes. Each function and procedure has a required signature that is described in the following sections.
Note:
You are not required to supply every function and procedure. Write only those that are required for your adapter.When you run the APIs to create an Adapter Area or Technology Type you enter the name of each function or procedure as the value for the the appropriate attribute.
Oracle LSH calls each function at the appropriate time (object definition, installation, or execution) if its name is in the Adapter Areas or Tech Types table, and automatically passes the required input parameter values to the function.
You include your functions and procedures in one or more PL/SQL packages and upload each package to a Source Code definition that you create inside the Program definition in the Adapter Area. If multiple people are coding your adapter at the same time, you may want to develop these functions and procedures in separate packages; see "Planning Programs and Packages".
If you need user input for any of these functions, you need to define Parameter objects to collect the information in the user interface and refer to these Parameters in your source code. See "Planning Parameters and Parameter Sets" for further information.
This section contains the following topics:
All object definition function and procedure names are stored in the adapter_areas table.
Note:
Write define-time functions and procedure so that they use UI error handling to display any errors they may return in the user interface.(Applies only to Load Set and Program adapters.) Use this function to enable Definers to upload files (such as a SAS data set or XML file) to create Table Descriptors or Table Descriptor columns with the same metadata structure as the file.
You can also use this function to read table metadata in a database and create a Table Descriptor from it.
If you set the Adapter Area flag ALLOW_COLUMN_UPLOAD to File (for a file system) or Yes (for a database), the Upload Columns button is enabled in the user interface and the system invokes the column upload function when required; see "Creating an Adapter Area".
The system creates a BLOB from the file and passes a name/value pair in the parameter pi_cNameValuePair with name = TMP_BLOB_ID and a value equal to the BLOB_ID of the uploaded file. You write the column upload function to read the BLOB using the ID passed and process it to create either a Table Descriptor, including its columns, or just columns for an existing Table Descriptor.
Oracle LSH has an Upload button for new Table Descriptors on the Table Descriptor subtab on the Load Set and Program definition and instance pages. It also has an Upload Columns button on the Table Descriptor page. The system invokes the Column Upload function when the user clicks the Upload button in either location. You can write the function so that it calls the public API CDR_PUB_DF_TABLE.UPLOADOPERATORCOLUMNS to upload columns or an entirely new Table Descriptor:
If you call the API to create an entirely new Table Descriptor, leave the Object ID and version parameters null since they do not exist. The API creates a new Table Descriptor and assigns an Object ID and version number to it.
If you call the API from the context of an existing Table Descriptor to update its columns, pass the Table Descriptor's Object ID and version number to the API.
In the case of uploading columns to an existing Table Descriptor, the API updates the existing column definitions in Oracle LSH if there are differences.
If the adapter uploads database table structures, use the following two definition functions (Auto_Add_Tab_Desc_Function and Auto_Add_Tab_Desc_LOV.
Create Parameters in a Parameter Set with the name PARAMETERSET_LOADSETLEVEL_DEF to collect the name and location of the file—or the remote location and connection—from the Definer; see "Planning Parameters and Parameter Sets" and reference these parameters in your column upload function.
The Column_Upload_Function must have the following signature:
PROCEDURE UploadOperatorColumns (pi_nCompanyID IN CDR_DF_NAMING_V.COMPANY_ID%TYPE ,pi_nLSRObjID IN CDR_DF_NAMING_V.OBJ_ID%TYPE ,pi_nLSRObjVer IN CDR_DF_NAMING_V.OBJ_VER%TYPE ,pi_nOperObjID IN CDR_DF_NAMING_V.OBJ_ID%TYPE ,pi_nOperObjVer IN CDR_DF_NAMING_V.OBJ_VER%TYPE ,pi_cNameValuePair IN CDR_NAME_VALUE_PAIR_COLL DEFAULT NULL) IS
The procedure's parameters take values as follows:
pi_nCompanyID see "Getting Your Company ID"
pi_nLSRObjID takes the Object ID of the Load Set or Program definition.
pi_nLSRObjVer takes the object version of the Load Set or Program definition.
pi_nOperObjID takes the Object ID of the Table Descriptor.
pi_nOperObjVer takes the object version of the Table Descriptor.
pi_cNameValuePair The system creates a BLOB from the uploaded file and passes a name value pair with name = TMP_BLOB_ID and value = BLOB_ID_of_the_uploaded_file.
(Applies only to Load Set adapters.) You can write a procedure to retrieve a list of all the tables or other data structures in the external system in a user-specified location and to insert them into a list values so that the user can select some or all of them to be uploaded. The system then calls the auto_add_tab_desc_lov function and passes the user's selection to it.
Note:
The above description is correct. The intended functions for this column in the Adapter Areas table and the Auto_Add_Tab_Desc_LOV column are reversed.If you write a procedure for this purpose, set the Allow_Auto_Add_Tab_Desc flag to YES. If not, set Allow_Auto_Add_Tab_Desc to NO. YES or NO must be in uppercase. If YES, you must also write a procedure to create Table Descriptors from the selected source data structures (see following procedure).
The following Oracle Clinical Load Set shipped adapters use an Auto_Add_Table_Descriptor_Function: Data Extract Oracle Views, Data Extract SAS Views, Study Data and Study Design. The shipped Oracle Tables and Views Load Set adapter also uses this function.
The Auto_Add_Tab_Desc_Function must have the following signature:
PROCEDURE getDataOperList(pi_nCompanyID IN CDR_DF_NAMING_V.COMPANY_ID%TYPE ,pi_nLSRObjID IN CDR_DF_NAMING_V.OBJ_ID%TYPE ,pi_nLSRObjVer IN CDR_DF_NAMING_V.OBJ_VER%TYPE ,po_vOperList OUT NOCOPY CDR_VAR_LIST_COLL) IS
The function's parameters take the following values:
pi_nCompanyID see "Getting Your Company ID"
pi_nLSRObjID takes the Object ID of the Load Set definition
pi_nLSRObjVer takes the object version of the Load Set definition
po_vOperList outputs a list of Tables from which the user can choose one or more to upload. The return value should be a collection and the ORACLE_NAME attribute of each CDR_VAR_OBJ_TYPE that is part of the returned collection should be populated with the name of a table.
(Applies only to Load Set adapters.) if you set the Allow_Auto_Add_Tab_Desc flag to YES you must also write a procedure to create Table Descriptors in Oracle LSH based on each of the data structures the Definer selects from the list of values (LOV).
Note:
The above description is correct. The intended functions of this PL/SQL function and the Auto_Add_Tab_Desc_Function PL/SQL function are reversed.The Auto_Add_Tab_Desc_LOV must have the following signature:
PROCEDURE CreateMultipleOperators (pi_nCompanyID IN CDR_DF_NAMING_V.COMPANY_ID%TYPE ,pi_nLSRObjID IN CDR_DF_NAMING_V.OBJ_ID%TYPE ,pi_nLSRObjVer IN CDR_DF_NAMING_V.OBJ_VER%TYPE ,pi_vOperList IN VARCHAR2) IS
The function's parameters take the following values:
pi_nCompanyID see "Getting Your Company ID"
pi_nLSRObjID takes the Object ID of the Load Set definition
pi_nLSRObjVer takes the object version of the Load Set definition
pi_vOperList takes the list of Tables the user selected to upload. The table names are separated by the pipe character (|) with an additional pipe at the end. For example, if the user selects tables named demog, ae, and conmed, the input value is demog|ae|conmed|.
The system launches the Define-Time function from the Create page of a Load Set, Program, Data Mart, or Business Area when the user clicks Apply.
If all objects created using your adapter need the same Planned Output, or one or more Table Descriptors with a fixed structure, you can use the Define-Time function to create them automatically for every object of your adapter's type.
For example:
Add a Planned Output as a placeholder for the log file or Data Mart file; call the public API CDR_PUB_DF_PLANNED_OUTPUT.CREATEPLANNEDOUTPUT.
Add Parameters if you need to connect to a remote connection during definition
If you are creating a Load Set adapter and your source data system has a fixed data structure, you may want to add Table definitions to the Adapter Area that match the source system's data structures and write code to use them to create target Table Descriptors at definition time.
Most shipped adapters use a Define-Time function.
The Define_Time_Function must have the following signature:
FUNCTION CreateLogFilePlannedOutput (pi_nCompanyID IN NUMBER ,pi_nLSID IN NUMBER ,pi_nLSVer IN NUMBER) return BOOLEAN IS
The function's parameters take the following values:
pi_nCompanyID see "Getting Your Company ID"
pi_nLSID takes the Object ID of the Load Set definition
pi_nLSVer takes the object version of the Load Set definition
The system calls this function when a user modifies a Program, Business Area, or Data Mart. Although it is called the Status Recalculation function, the system automatically recalculates the installable status each time an object is modified, so you do not need to write a function for that purpose.
However, you can use this function to do anything your adapter requires when objects of your adapter type are modified.
Load Sets do not use the Status_Recalc_Function. The signature is different for Programs and Business Areas than it is for Data Marts. For Programs and Business Areas, the system invokes the function when the user checks the object in or out. For Data Marts the system invokes the function when it computes the status—when the user checks in the Data Mart.
Shipped Data Mart and Program adapters use a Status_Recalc_Function, including SAS Export, Oracle Export, and Text Export Data Mart adapters and Informatica and BIP Program adapters.
The Status_Recalc_Function for Program and Business Area adapters must have the following signature:
PROCEDURE RecalcInfaPgmStatus ( pi_sourceCdrNaming IN cdr_naming_version_obj_type, pi_vEvent IN VARCHAR2, pv_bothRefAndDef IN cdr_df_naming_v.checked_out_flag_rc%type );
The function's parameters take the following values:
pi_sourceCdrNaming is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE that contains object attributes. See the Oracle Life Sciences Data Hub Application Programming Interface Guide Reference Information section for details about the required attributes.
pi_vEvent takes either CHECKIN or CHECKOUT, depending on the UI trigger event
pv_bothRefAndDef: When the event is CHECKOUT, this parameter value is passed as $YESNO$YES if both the definition and instance are being checked out and $YESNO$NO if only the definition is being checked out.
When event is CHECKIN this value is passed as NA.
The Status_Recalc_Function for Data Mart adapters must have the following signature:
PROCEDURE synchronizeDatamart ( pi_nDMCompanyId cdr_df_naming_v.company_id%TYPE, pi_nDMObjId cdr_df_naming_v.obj_id%TYPE, pi_nDMObjVer cdr_df_naming_v.obj_ver%TYPE, pi_vChildType cdr_df_naming_v.object_type_rc%type);
The function's parameters take the following values:
pi_nCompanyID see "Getting Your Company ID"
pi_nDMObjId takes the Object ID of the Data Mart definition
pi_nDMObjVer takes the object version of the Data Mart definition
pi_vChildType takes the type of object that was modified, triggering the status recalc function. For example, if a user modifies a Table Descriptor in the Data Mart, then the child type is $OBJTYPES$TABLEDESCRIPTOR. See the Reference Information section in the Oracle Life Sciences Data Hub Application Programming Interface Guide for information on how to look up the correct string for the object type from the lookup.
This section contains the following topics:
Depending on the external system, you may need to write functions for use when a user installs a Load Set, Data Mart, Program, or Business Area. All object installation function names are stored in the tech_types table.
Each function should return TRUE in case of success and FALSE in case of a failure. All three of these functions must have the following signature:
function name_of_function (company_id in cdr_program_refs.company_id%TYPE, prref_id in cdr_program_refs.prref_id%TYPE, prref_ver in cdr_program_refs.prref_ver%TYPE, ) return boolean
The functions' parameters take values as follows:
pi_nCompanyID see "Getting Your Company ID"
prref_id see "Getting an Object's Prref_Id and Prref_Ver"
prref_ver see "Getting an Object's Prref_Id and Prref_Ver"
Your external system may require a function to be executed before a user installs a Load Set, Data Mart, Program, or Business Area.
Your external system may require a function to be executed when a user installs a Load Set, Data Mart, Program, or Business Area. The system calls this code immediately after the Pre-Install Function. There is no need to have both.
For example, the Informatica adapter uses the install function to create a folder in Informatica for every Informatica program being installed.
Your external system may require a function to be executed immediately after a user installs a Load Set, Data Mart, Program, or Business Area.
The following execution-related function names are stored in the tech_types table:
The following execution-related function names are stored in the adapter_areas table:
Oracle LSH uses its Distributed Processing (DP) Server for executing jobs outside the Oracle LSH database server. For more information, see "Planning for Object Execution", "Planning Services" and the chapter on services in the Oracle LSH System Administrator's Guide.
(For Visualization and Program adapters only.) This function is called when a user launches an integrated development environment (IDE) either from inside Oracle LSH or directly through the URL. Use it to do whatever is required for your system at that point; for example, create the URL for the IDE to be launched, download a file, or give the user the security access he or she needs in the external system.
You can use the input parameter pi_launchPref of the Build_IDE_Cfg_Function to launch one tool or mode of an IDE from the Oracle LSH Applications UI tab (the Business Area or Program properties page) and a different one from the Reports tab (for visualizations).
See "Planning Integrated Development Environment Adapters" for related information.
The build_ide_cfg_function must have the following signature:
Procedure proc_name(pi_nCompanyId IN NUMBER,
pi_nObjectId IN NUMBER,
pi_nObjectVer IN NUMBER,
pi_nWorkAreaId IN NUMBER,
pi_nConfigId IN NUMBER,
pi_nConfigVer IN NUMBER,
pi_nPrrefId IN NUMBER,
pi_nPrrefVer IN NUMBER,
pi_launchPref IN varchar2,
po_allocateServiceInstance OUT VARCHAR2,
po_launchData OUT CDR_IDE_LAUNCH_DATA,
po_isDBAccountAvailable OUT VARCHAR2);
pi_vCdrUser IN VARCHAR2,
pi_vScemaName IN VARCHAR2,
pi_vPath IN VARCHAR2,
The function's parameters take the following values:
pi_nCompanyID see "Getting Your Company ID"
pi_nObjectId takes the Object ID of the Business Area or Program instance.
pi_nObjectVer takes the object version of the Business Area or Program instance.
pi_nWorkAreaId takes the Object ID of the Work Area that contains the Business Area or Program instance.
pi_nConfigId takes the Object ID of the Business Area or Program definition.
pi_nConfigVer takes the object version of the Business Area or Program definition.
pi_nPrrefId takes the PrrefID of the Business Area or Program instance; see "Getting an Object's Prref_Id and Prref_Ver". This parameter is not required but you can use the value to gain access to the Table Descriptors more quickly and improve performance.
pi_nPrrefVer takes the PrrefID of the Business Area or Program instance; see "Getting an Object's Prref_Id and Prref_Ver". This parameter is not required but you can use the value to gain access to the Table Descriptors more quickly and improve performance.
pi_launchPref Use this parameter if you need to open two different applications, each for a different mode—for example, if you need to launch a development environment and a visualization environment for the same adapter. For example, the OBIEE adapter launches the OBIEE Administrator's tool, which is on the Definer's PC, from the Business Area UI and OBIEE Presentation Services, which is on the OBIEE Server, from the Visualizations subtab of the Reports tab.
The system passes one of the following values:
LAUNCHFILE The system passes this value (in uppercase) to the function if the user launches the IDE from the Applications tab.
LAUNCHURL The system passes this value (in uppercase) to the function if the user launches the IDE from the Reports tab.
Your code can either handle these values differently or not.
po_allocateServiceInstance must output T if a service instance is required for the adapter or F if it is not; see "Planning Services".
po_launchData is a collection that outputs attribute values as follows:
launch_mode can have a value of either URL or FILE, depending on whether the adapter needs to launch a URL or open a file. The value of this attribute determines which of the other attributes are required. You must write the code to make the function return the value that is appropriate for your adapter.
If launch_mode is set to FILE, the system launches the IDE on the Definer's PC by pushing a file with extension .cdz.
Note:
Oracle LSH supports only IDEs that are installed on the Definer's PC.If launch_mode is set to URL, the system forwards the IDE launch request to the URL, which is another attribute of the CDR_IDE_LAUNCH_DATA collection.
url is required if the launch_mode value is URL. It must provide the actual URL the adapter is to launch.
Note:
If you are creating this adapter for use in other companies or locations, you can use the Execution Command field of the service type definition for the purpose of collecting the URL. The field is available because IDE technology types do not require an execution command. This value is stored in the EXECUTION_COMMAND column of the CDR_SERVICES table (varchar2(200) BYTE).blob_elements is a collection of BLOBs. It is required if the launch_mode value is FILE and the file is binary. It outputs the actual file(s) that must be downloaded to the client.
blob_filenames is a collection that is required if the launch_mode value is FILE and the file is binary. It outputs the name of the file(s) that must be downloaded to the client. There must be a blob_filename value for each blob_element.
clob_elements is a collection of CLOBs. It is required if the launch_mode value is FILE and the file is character-based. It outputs the actual file(s) that must be downloaded to the client.
clob_filenames is a collection that is required if the launch_mode value is FILE and the file is character-based. It outputs the name of the file(s) that must be downloaded to the client. There must be a clob_filename value for each clob_element.
col_comp_list is a collection that is required if you need to download a file to the IDE or pass any other values to the IDE when a user launches the IDE. Specify one or more argument/value pairs; for example, "_comp002_", "<filename>" where the filename is the file to be downloaded. The argument name and value must match the those in the cdrconfig.xml file; see "Planning Integrated Development Environment Adapters".
col_sub_dirs is a collection that is required for users to view outputs generated by the Program they are defining in the IDE. This attribute must name the directories into which the system should put these outputs; see "Creating Subdirectories on IDE Computers".
po_isDBAccountAvailable outputs T if a database account is required by the adapter or F if it is not. For example, the SAS adapter requires a database account to reconnect to the Oracle LSH database during IDE launch.
pi_vCdrUser takes the Oracle LSH user ID of the person launching the visualization tool.
pi_vSchemaName takes the Oracle LSH database account user ID of the person launching the visualization tool.
pi_vPath takes the full path of the object:
user_db_account/domain_name/app_areaname/wa_name/object_name/vversion_number
for example:
John_Smith_DB/SmokeTestDomain/SmokeTestAA/ SmokeTestWA/Study55698_BA/v3
This function is not called by the system. Use it only if you need an additional function that another function can call.
For example, in the SAS adapter, the pre-execution function calls a procedure buildSasExeContent to generate dynamic components of a SAS configuration for execution. The binary components are returned in a list of BLOBs and the ASCII components are returned in a list of CLOBs.
Your adapter may require code to be run immediately before executing a Load Set, Data Mart, or Program. For example, a pre-execution procedure might check connectivity with the external system, prepare scripts for downloading to the Distributed Processing Server, and, for Load Sets, verify that source data structures and Oracle LSH target Table instances are compatible. The Pre_Execution_Function requires the following signature:
procedure <procname>(pi_nJobId in cdr_jobs.job_id%type)
pi_nJobId takes the jobID of the current object execution.
Some shipped adapters use a Pre_Execution_Function, including: SAS, Text, and Oracle Clinical Data Extract SAS View Load Sets; Oracle and SAS Data Marts.
This function is responsible for coordinating the execution of a Load Set, Data Mart, or Program. It must call several public APIs. These are documented in the Oracle Life Sciences Data Hub Application Programming Interface Guide. See also "Planning for Object Execution".
The execution function must do the following:
Fetch the current job ID. Use the public API CDR_PUB_EXE_RUNTIME.GETCURRENTLYEXECUTINGJOBID.
If required, build a collection of BLOBs or CLOBs and the corresponding BLOB or CLOB file names for the files that the Oracle LSH Distributed Processing (DP) Server must download when it runs the job, and call public API CDR_PUB_EXE_EXTERNAL.CREATETEMPLOBS to upload the BLOBs or CLOBs to a temporary table. This constitutes the job payload.
Build an XML file to send to the DP Server so that the DP Server can start executing the job. To create the XML file, call public API CDR_PUB_EXE_EXTERNAL.GENERATEXMLPAYLOAD. This constitutes the XML payload; see "XML Payload".
Start the job execution by calling public API CDR_PUB_EXE_EXTERNAL.SENDJOB.
The DP Server then receives the XML payload, downloads the job payload, and starts the job.
The function must wait for the job to complete. Call public API CDR_PUB_EXE_EXTERNAL.WAITFORFINALSTATUS.
The Wait For Final Status API returns 1 if the job completes without warnings, 2 if the job completes with warnings, and 3 if the job fails.
If the job succeeds, return 0; else return 1.
If the job produces one or more outputs, the DP Server loads the BLOB or CLOB file(s) into a temporary table. Your postexecution function should retrieve these files; see "Post_Execution_Function".
The Execution_Function requires the following signature:
function <funcname>return number;
XML Payload For technologies that run outside the database and therefore use the OWB operator CdrService_1, you can call API CDR_PUB_EXE_EXTERNAL.GENERATEXMLPAYLOAD to produce a default XML payload file. If your technology requires additional functionality—for example, passing additional values to the job—your execution function can produce an XML file with the information your adapter requires. However, your execution function must use the same structure.
The required XML payload file structure is:
<?xml version="1.0" ?> <EXEJOB EXEJOB_VERSION="1.0"> <JOB ID="id_of_currently_executed_job" TYPE="exe"> <SURROGATEJOBID>id_of_master_job_or_same</SURROGATEJOBID> <PRREFID>prref_id_of_the_executing_program</PRREFID> <CONFIGID>0</CONFIGID> <WORKDIR>/user/oracle/work_directory</WORKDIR> <PROGRAM>/user/oracle/execution_command_location/execution_command_script</PROGRAM> <RUNSCRIPT>entry_point_run_script</RUNSCRIPT> <OUTPUTPATH>Output_path</OUTPUTPATH> <PRIORITY>$JOBPRIORITIES$NORMAL</PRIORITY> <SCHEMA>ZZ_account_from_service_instance</SCHEMA> <USERID>(optional)</USERID> <SUBDIRS><DIR NAME="first_subdirectory" /><DIR NAME="another_subdirectory" /><DIR NAME="yet_another_subdirectory" /> </SUBDIRS> </JOB> </EXEJOB>
Example 2-1 XML Payload Required File Structure
<?xml version="1.0" ?> <EXEJOB EXEJOB_VERSION="1.0"> <JOB ID="797120006" TYPE="exe"> <SURROGATEJOBID>797120006</SURROGATEJOBID> <PRREFID>42580001</PRREFID> <CONFIGID>0</CONFIGID> <WORKDIR>/user/oracle/sas92/SasWork/ip1dv102</WORKDIR> <PROGRAM>/user/oracle/sas92/sasNormal</PROGRAM> <RUNSCRIPT>runSasJob</RUNSCRIPT> <OUTPUTPATH>Output</OUTPUTPATH> <PRIORITY>$JOBPRIORITIES$NORMAL</PRIORITY> <SCHEMA>ZZ_CDR_SI_610001</SCHEMA> <USERID>797120006</USERID> <SUBDIRS><DIR NAME="SOURCE" /><DIR NAME="Output" /><DIR NAME="LSH_RS" /> </SUBDIRS> </JOB> </EXEJOB>
Additional information:
Job ID and Surrogate Job ID. If the job is has only a single process, these two IDs are the same. If there is a master job and a subjob, the Job ID is for the master job's ID and the Surrogate Job ID is for the current subjob.
Prrefid. The Prrefid of the object being executed. You can use API CDR_PUB_EXE_RUNTIME.GETJOBINFO to get this ID from the CDR_SUBMISSIONS table.
Configid. The object ID of the object being executed.
Workdir. This is the location set up under the DP Server directory for jobs of this type and named as the Root Directory in the service definition. It is stored in the ROOT_DIRECTORY column of the CDR_SERVICES table. The DP Server creates a subdirectory here for each job with the job ID as a name, containing the files required for the job.
Program. (Optional) This value is stored in the PROGRAM column of the CDR_SERVICES table.
Run Script. Your execution function must generate a script for each job that includes the service instance assigned to the job and provides the actual starting point for the job execution.
Output Path. Enter the path for the DP Server Home/log directory.
Priority. The priority requested by the user for the service instance. Possible values are: $JOBPRIORITIES$NORMAL, $JOBPRIORITIES$HIGH, or $JOBPRIORITIES$LOW.
Schema. The ZZ% account of the service instance. You can use the SERVICE_INSTANCE_ID to get the ZZ account from the table CDR_SERVICE_INSTANCES.
User ID. This value is not required. It is used only by one shipped adapter.
Subdirectories.
See the Oracle Life Sciences Data Hub Application Programming Interface Guide for information on execution-related APIs.
Your adapter may require a procedure to run after the execution of a Load Set, Data Mart, or Program. For example:
If the execution produces outputs, the DP Server puts these files in a temporary table. You can use the view CDR_TEMP_BLOBS_V to get a list of the outputs uploaded by the DP Server and call the public API CDR_PUB_EXE_EXTERNAL.UPLOADBLOBOUTPUT to upload the outputs.
You can create a procedure to search the log file for specific information after execution.
The Post_Execution_ Function requires the following signature:
procedure <procname>(pi_nJobId in cdr_jobs.job_id%type)
pi_nJobId takes the jobID of the current object execution.
Note:
If any of these programs returns an error the job returns an error. If any of these programs returns a warning then the job returns a warning unless another part of the job generated an error.(Optional; applies to Load Sets only. Oracle LSH does this automatically for Programs and Data Marts.) Use this function to determine the currency of the data in the external system. If you define a currency funtion, the system automatically invokes it when the job is executed and uses it to determine whether to run the job.
The currency function must return the source data currency. Oracle LSH job processing logic then determines whether the currency of the previous successful job was the same as the current job. If so, it marks the current job as duplicate and stops the job (unless the Force Execution flag is set to Yes by the Force Execution system Parameter). If the currency values are different, then the system proceeds to execute the job; see "Planning for Object Execution".
One of the system Parameters available for submitting a job is Currency_Type, whose value indicates whether the job will take the most current data or use a data snapshot. The default is to use the most current data. If there is no currency function available and the parameter value is for current data, the system processes the most current data.
The following shipped Oracle Clinical Load Set adapters use a currency function: Data Extract Oracle Views, Data Extract SAS Views, Labs, and Global Metadata.
This procedure has the following required signature:
PROCEDURE getCurrency(pi_nCompanyID IN NUMBER ,pi_nPrrefId IN NUMBER ,pi_nPrrefVer IN NUMBER ,pi_cRunParamNVPair IN CDR_NAME_VALUE_PAIR_COLL ,pi_cSysParamNVPair IN CDR_NAME_VALUE_PAIR_COLL ,po_cCurrencyListColl OUT NOCOPY CDR_CURRENCY_LIST_COLL);
The procedure's parameters take values as follows:
company_id see "Getting Your Company ID"
prref_id see "Getting an Object's Prref_Id and Prref_Ver"
prref_ver see "Getting an Object's Prref_Id and Prref_Ver"
pi_cRunParamNVPair is a collection with a name,value pair for each runtime Parameter for the job that is passed to the currency function. For example, Oracle Clinical Labs Load Sets pass the remote location and lab name to the adapter's currency function.
pi_cSysParamNVPair is a collection with a name,value pair for each predefined system Parameter for the job that is passed to the currency function. System Parameters include currency type, job priority, force execution, and more. For a complete list and descriptions, see the Oracle Life Sciences Data Hub User's Guide.
po_cCurrencyListColl this output parameter is a collection of type CDR_CURRENCY_LIST_COLL, which is a table of CDR_CURRENCY_OBJ_TYPE that has the following elements:
VCCURRVALUE VARCHAR2(4000)
DTCURRVALUE DATE
NUMCURRVALUE NUMBER
The adapter can return how current the data in the remote system is by either returning a character, date or number currency or any combination of the three. For example, the Oracle Clinical Labs currency function fetches the max date value from the tables labs, lab_range_subsets and ranges.
You may need to write a procedure to synchronize security with the external system; see "Synchronizing Security with Integrated Environments". If so, set the Security_Recalc_Flag_RC for the Adapter Area to $YESNO$YES and write a procedure with the following signature:
Procedure RecalcDiscovererSecurity (pi_nCompanyId IN NUMBER, pi_nObjId IN NUMBER, pi_nObjVer IN NUMBER);
The function's parameters take the following values:
pi_nCompanyId see "Getting Your Company ID"
pi_nObjId takes the Object ID of the object instance the user is trying to use.
i_nObjVer takes the object version number of the object instance the user is trying to use.
This section contains the following topics:
If you need information from the user during Load Set, Data Mart, Program, or Business Area definition, installation, or execution, you must do the following:
Define a Parameter Set in the Adapter Area with one of three required names and usage settings, depending on whether the Parameter value is required in order to define or install the object in general, to define Table Descriptors for the object, or to collect information at runtime.
When you run the Create Adapter Area API, set a flag to indicate that this type of Parameter Set exists (although you must create the Adapter Area before you can create the Parameter Set).
Define a Parameter for each piece of information you need to collect, and create an instance of it in the appropriate Parameter Set.
The system then displays the Parameters you define in the proper place in the user interface and uses their values at the correct time.
Note:
If you are developing an adapter for use in other locations and need to collect information about the Oracle LSH installation or the external system installation, you can use the Details field of the service defintion; see "Planning Services".If you need information from the user during Load Set, Data Mart, Program, or Business Area definition, create a Parameter Set named PARAMETERSET_LOADSETLEVEL_DEF and set its Usage attribute to DEFINITION. Oracle LSH displays Parameters in this Parameter Set as attributes in the Load Set, Data Mart, Program, or Business Area Properties page in the user interface.
For example, the following shipped adapters have these attributes:
SAS and Text Load Sets: Save Input File (Yes or No)
All Data Marts: File Name (for the Data Mart's output file)
Text Data Marts: Mode (Delimited or Fixed) and Filename Extension (.csv or .txt)
If you need Parameters whose values are used during Load Set, Data Mart, Program, or Business Area installation, you can define them in the parameterset_loadsetlevel_def Parameter Set.
(Load Set adapters only) Load Sets' Table Descriptors are based on data structures in the external system. If user input is required to define these Table Descriptors, create Parameters to collect this information and create an instance of each Parameter in a Parameter Set with the required name: parameterset_operatorlevel and set its Usage attribute to OPERATOR.
Oracle LSH creates attributes in the object definition user interface that correspond to the Parameters you define in the parameterset_operatorlevel Parameter Set. In the user interface, these attributes appear in the same location as the parameterset_loadsetlevel_def attributes, but the system uses their values at different times.
For example, the following shipped adapters have the following Table Descriptor attributes:
Oracle Tables and Views Load Sets: Remote Location and Database Schema
Some Oracle Clinical Load Sets: Remote Location and Study Name
Note:
If your adapter requires a Remote Location attribute, after you have defined the adapter you must also define at least one remote location in the Oracle LSH user interface; seeOracle LSH System Administrator's Guide for further information.If you need information from the user when he or she runs the Load Set, Data Mart, or Program, or launches a visualization tool, create a Parameter Set named PARAMETERSET_LOADSETLEVEL_RUN and set its Usage attribute to EXECUTION.
Oracle LSH displays Parameters in this Parameter Set as Parameters in the Parameters tab in the Load Set, Data Mart, Program, or Business Area Properties page and in the Load Set, Data Mart, or Program's Execution Setup.
For example, the following shipped adapters have the following runtime attributes:
SAS Load Sets: Dataset Filename and BLOB ID (Temporary)
Text Load Sets: Data File Name, Data Format, Delimiter Character, Enclosing Character, Initial Records to Skip, Maximum Allowed Errors, Temp LOB ID, Date Format
Oracle Tables and Views Load Sets: Remote Location
SAS Data Marts: Mode and Zip Result
Oracle Export Data Marts: Compress and Statistics
Text Data Marts: Zip Results, FirstRow Desc, Operating System, Separating Character, Use Enclosing Character, Enclosing Character
Note:
If you are creating an adapter that must upload files, define a Parameter with its Parameter Type set to either BINARY_FILE or TEXT_FILE, as appropriate, and create an instance of it in the runtime Parameter Set. Create a second Parameter called TMP_BLOB_ID, for example, and create an instance of it in the same Parameter Set. Write code to upload the file to a temporary location and store the ID for the file as the value of TMP_BLOB_ID. Use this value in your pre-execution or execution function.Extend the following lookups:
CDR_TECH_TYPES. At least one new technology type is required for your adapter. Enter the same value that you enter in the pio_techTypeRow.TECH_NAME_RC parameter of the Create Technology Type API. This value is used internally.
CDR_SERVICE_TYPES. One service type per technology type is required. This value appears in the user interface for defining services.
CDR_FILE_TYPES. Your adapter may need need a new file type; for example, for source code in Program or Business Area adapters. Add the file type to this lookup.
For instructions on extending lookups in your environment, see the chapter on lookups in the Oracle Life Sciences Data Hub System Administrator's Guide.
Provide instructions to each company using the adapter for adding the lookup values required for your adapter in their own environment.
Data Mart adapters may require predefined Planned Outputs to support the actual data file produced by running the Data Mart. Program adapters may required predefined Planned Outputs to support report outputs.
Data Mart, Program, and Load Set adapters may all need predefined Planned Outputs to serve as placeholders for other outputs created when they are executed; for example, log and error files.
To automatically create a fixed Planned Output definition for every object of your adapter's object type (Data Mart, Program, or Load Set), call public API CDR_PUB_DF_PLANNED_OUTPUT.CREATEPLANNEDOUTPUT in your define_time_function.
If you are creating a Load Set adapter and your source data system has fixed data structures, you can define Tables in your Adapter Area with the same structure; see "Call the Create Table API". You then write your adapter code to create Load Set Table Descriptors based on those Tables rather than requiring connection to the remote database or requiring manual definition by users.
The Oracle Clinical Load Set adapters that import data from fixed Oracle Clinical tables do this, including Design and Definition, Labs, Randomization, and Study Data.
This section includes the following topics:
Programs, Load Sets, and Data Marts are all executable objects and an adapter of these type must handle the object execution. Business Area and Program IDE adapters may also require execution functionality. Some execution functionality is built in to Oracle LSH, but each adapter requiring execution must have a custom execution function, a custom service type, and a custom execution command.
Note:
Processing engines that run on the database do not use the DP Server.The execution process includes:
The user submits a Load Set, Program, or Data Mart for execution.
The system calls the OWB operator specified for the technology type and creates an OWB audit task, which OWB tracks in order to report the job's status. OWB returns control to Oracle LSH Runtime (called "the system" here).
The system generates a job ID and assigns a service instance to the job.
The system calls the pre-execution function specified in the technology type, if any, and then calls the execution function.
You must write an execution function to do the following; see "Execution_Function" for details.
Build a collection of files, if required
Output an XML message with information about the job (the XML payload)
Call the Send Job public API
The Oracle LSH listener detects the XML message produced by the Execution_Function, dequeues the message, and sends the XML message to the DP Server to run the job in the external processing engine using the assigned service instance.
The service instance is the account that the job uses to connect back to the Oracle LSH database to read from sources and write to targets, if any.
The DP Server creates a subdirectory with he job ID as a name in the work directory defined under the DP Server directory for jobs of this type and puts all the files required for the job in this subdirectory.
The DP Server makes two UNIX shell variables available to the processing engine:
$CDRJOBSCHEMA holds the value of the service instance account, which is extracted from the XML message; for example, <SCHEMA>ZZ_CDR_SI_250001</SCHEMA>
$CDREXEPASS holds a random password assigned to the account for that job. The password can never be reused.
The service instance runs the execution command. There is a single execution command, which you must write, for all jobs of this type; see "Execution Command".
The DP Server job processor uses the information in the XML message to connect back to the Oracle LSH database. Once connected, it downloads all the necessary data, including the execution function(s), into a target directory identified in the XML message.
The job processor then executes the job-specific script by spawning a new operating system-level process.
This spawned process runs the processing engine, which in turn carries out the actual job execution using the downloaded job input data.
After the spawned external process completes, the job processor connects back to Oracle LSH and uploads any output results into Oracle LSH. The service instance is released.
Meanwhile, the execution function waits for the job to complete, using the Wait for Final Status public API as soon as it calls the Send Job API.
If the job produces one or more outputs, the DP Server loads the BLOB or CLOB file(s) into a temporary table. Your postexecution function should retrieve these files; see "Post_Execution_Function".
The system calls the postexecution function.
OWB reports the final job status. See the Oracle Life Sciences Data Hub User's Guide for a description of job and execution statuses.
For processing engine technology types, you must write an execution command file. The execution command invokes the external processing system. It must be contained in a file of a type appropriate for the operating system; a shell script for UNIX or a command file for Windows.
In most cases, the execution command must provide a mechanism to pass the service instance to the external processing engine which can then use the service instance's connection credentials to connect back to the Oracle LSH database.
To help ensure the proper storage and display of non-English character data in Oracle LSH, the processing engine should use UTF8 character encoding. If you can enforce this in the execution command, do so.
As with the shipped adapters, at each Oracle LSH installation the user must move the execution command file into the DP Server directory created by the user, or a subdirectory of it, and enter the actual path of the file in the service location definition in the Oracle LSH user interface (see the Oracle Life Sciences Data Hub System Administrator's Guide). The system picks up the location from the service location definition when a user submits a job.
If you are creating this adapter for use in other companies or locations, you must include instructions for system administrators similar to those in the Oracle Life Sciences Data Hub Installation Guide to move the file and edit it to add whatever information your adapter may require about the location; for example:
the Oracle SID
the location of the technology server
the location of Oracle setup script coraenv
a variable holding the path to any external command invoked inside the execution command; for example, if the execution command invokes a Java executable, the path to the Java executable must be set correctly in the path variable.
The security required for your adapter depends on the interaction with the external system that it requires:
Data Mart adapters and processing engine adapters for Programs require no security coordination with the external system. The user must have proper Oracle LSH security privileges in order to define and run the Data Mart or run the Program. The DP Server automatically provides an account and unique password for each job through the service instance to allow the processing engine to connect to the Oracle LSH database to process data; see "Planning for Object Execution".
Load Set adapters may require security coordination with the external system; for example, it may be necessary for users to have a user account with the same name in both systems. File-based Load Sets require access to the files to be loaded. Database-based Load Sets may require additional security.
Program and Business Area IDE adapters generally require the most complex security coordination between the two systems; see "Synchronizing Security with Integrated Environments".
This section contains the following topics:
IDE Launch Process from Within Oracle Life Sciences Data Hub
Planning Navigation in the External System to Business Areas
Business Area and Program adapters may require that Oracle LSH Definers be able to do the following in a development environment that is integrated with Oracle LSH:
Launch the integrated development environment (IDE) from within Oracle LSH either from the Program or Business Area Properties page (for development) or from the Reports tab, Visualizations subtab (for viewing data visualizations), and then see appropriate Oracle LSH data in the IDE.
Download and upload files between Oracle LSH and the IDE—or the adapter may need to do this automatically.
Log in directly to the external system and gain access to Oracle LSH objects and data. Oracle LSH allows this access only for adapters that view but do not modify Oracle LSH data. In this case, you can use the generic visualization adapter; see Chapter 5, "Using the Generic Visualization Adapter".
When a user launches an IDE from within Oracle LSH, the system:
Calls the Build_IDE_Cfg_Function to launch the IDE, download files, if necessary, and whatever else you have coded it to do.
Oracle LSH makes a distinction—by passing a different value to the Build_IDE_Cfg_Function—between IDEs launched from the Reports tab, through which Consumers view data visualizations, and IDEs launched from Program and Business Area pages, through which Definers develop Programs and Business Areas. Consumers can see blinded data in an IDE if they have the required privileges in Oracle LSH, but Definers can never see blinded data when they launch the IDE from a Program or Business Area.
Calls the Security_Recalc_Function and passes values to it for the company ID, object ID, and object version of the object—Program or Business Area—for which the IDE is being launched. You can use these to get the prrefid of the Program or Business Area to set the context for data security; see "Getting an Object's Prref_Id and Prref_Ver" and "Establishing Context".
Assigns a service instance to the IDE session. The service instance is the account that the job uses to connect back to the Oracle LSH database. It remains allocated to the Program or Business Area until it is explicitly released by a Checkin or Undo Checkout user action.
The service instance assigned to the Program or Business Area during IDE launch is associated with the user's database account, so if the user logs in using their database account, the system automatically sets up the sys context so that they can read the data in Table instances mapped to the Program or Business Area's Table Descriptors.
Your adapter must synchronize security with the external system:
Object Security: Users should be able to access only the appropriate Oracle LSH objects (Program or Business Area) from the external system and have only the appropriate privileges on those objects.
Data Security: Users should be able to see data only in Table instances that are mapped to the object's Table Descriptors, and should be able to view only data in appropriate currency and blinding states.
Both types of security must be enforced both when the user launches an IDE from within Oracle LSH and when the user logs in directly to the external system.
The Oracle LSH security system enforces object security within Oracle LSH through user accounts. Viewing data requires an Oracle LSH database account mapped to a user account. Your adapter can use one or more Oracle LSH user accounts to take advantage of this functionality.
Within Oracle LSH, the system enforces that users can see data only in Table instances mapped to the current object. You can use the Tracking API and Tracking Table to enforce the same restriction in the external system.
See the Oracle Life Sciences Data Hub Implementation Guide and the Oracle Life Sciences Data Hub System Administrator's Guide for information on Oracle LSH security.
You may need to create entities in the external system that correspond to Oracle LSH objects; for example, the Informatica adapter creates a different Informatica Folder for each Oracle LSH Informatica Program, and the OBIEE adapter creates a different OBIEE Subject Area for each OBIEE Business Area. You can then use the external system's security to enforce access to the appropriate external system entities.
In the case of Informatica, the Build_IDE_Cfg_Function interacts with Informatica through the DP Server to assign privileges to the user for the Folder specific to the Program. Alternatively, you can use the Security_Recalc_Function to synchronize security in your adapter.
You can use the Tracking API and Tracking Table to maintain a record of the object the same user accessed during his or her previous session in the IDE and then use the security APIs of the external system to remove access to that object in the current session; see "Tracking and Removing Object Access".
An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace. Inside the context are the name-value pairs (an associative array). An application can use the application context to access session information about a user, such as the user ID or other user-specific information, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application.
For Oracle LSH, you need a name-value pair for each of the following:
User ID
User password
PrrefID of the Program or Business Area
You can set the context as follows:
If no login is required when the user launches the external system, set the sys_context by writing a function.
Establish the context with the database logon trigger. The adapter doesn't need to do anything specifically for allowing the user access to the correct data. The Oracle LSH logon trigger uses the user's database account to determine the user's privileges and enables the sources and targets so that the user can view LSH data from the IDE.
Note:
For information on application context functionality, logon triggers, and more, see the Oracle® Database Security Guide 11g Release 2 (11.2) athttp://download.oracle.com/docs/cd/E11882_01/network.112/e16543.pdf; see "Finding Oracle Documentation".If you are using the Generic Visualization adapter, see "Initializing the Business Area Instance".
Program and Business Area adapters allow users to launch an integrated development environment (IDE) from Oracle LSH to modify an Oracle LSH Program or Business Area's source code. You must ensure that Definers can modify only Programs and Business Areas on which they have Modify privileges in Oracle LSH by using the external system's APIs or another security mechanism.
One approach is to ensure that when users launch the IDE, which they can do only through a Program or Business Area, that they have access only to that particular Program or Business Area in that session, even if they have access to other Programs or Business Areas of the same IDE type and have worked on them in the past.
For example, if the external system has entities that correspond to Oracle LSH Programs or Business Areas (such as Informatica Folders or OBIEE Subject Areas) and public APIs that grant and revoke access to these entities, you can design your adapter so that when the user launches the IDE, the adapter calls the external system's API to:
grant access to the entity corresponding to the Program or Business Area
revoke access to previously granted entities, if any
In order to revoke access to previously granted entities, you must maintain a record of each user's IDE launches and the Programs, Business Areas, and the corresponding external entities to which each user was granted access. Oracle LSH has a public API, CDR_PUB_EXE_EXTERNAL.TRACKLAUNCHIDE, and a table, CDR_IDE_LAUNCH, for this purpose; see "Tracking API" and "Tracking Table".
In your Build_IDE_Cfg_Function you can call CDR_PUB_EXE_EXTERNAL.TRACKLAUNCHIDE and the external system's APIs to grant and revoke access to the appropriate entities in the external system.
In addition, in the case of Programs, your function may need to determine whether the Program being launched uses source code shared from another Program and if so, grant read access to the corresponding external entity for that Program too. You then also need to be sure to revoke access from all entities included in the previous launch. They have the same IDE_LAUNCH_ID.
Note:
No Business Area types currently included with Oracle LSH use shared source code, but it is theoretically possible.Use public API CDR_PUB_EXE_EXTERNAL.TRACKLAUNCHIDE to pass information to the tracking table about the current user and IDE launch. You can set a parameter to indicate whether to delete the record of the user's previous IDE launch from the table or not.
The API's signature is:
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,pi_IdeLaunchColl IN CDR_IDE_LAUNCH_OBJ_COLL
, pi_DelPrevEntry IN VARCHAR2
);
The nonstandard parameters for this API are:
pi_IdeLaunchColl cdr_ide_launch_obj_coll is a collection that is a table of cdr_ide_launch_obj_type, with the following attributes:
COMPANY_ID. NUMBER(6)
IDE_LAUNCH_ID. NUMBER(22)
PRREF_ID. NUMBER(22)
PRREF_VER. NUMBER(7)
SHARED_FLAG_RC . VARCHAR2(30)
EXT_SYS_ENTITY. VARCHAR2(4000)
LAUNCH_USER_ID. NUMBER(15)
pi_DelPrevEntry Set to Y to delete the record of the current user's previous IDE launch. Set to N to maintain the complete IDE launch history for the user.
If your adapter uses the TRACKLAUNCHIDE API, the internal table CDR_IDE_LAUNCH contains a record for each Program or Business Area required for the current IDE launch. If the Program being launched uses shared source code included in another Program, the API creates a record for both Programs and gives both records the same IDE Launch ID.
The table has the following columns:
COMPANY_ID NUMBER (6). To get the company ID, use CDR_PUB_DEF_FACTORY_UTILS.GETCOMPANYID.
IDE_LAUNCH_ID NUMBER (22). This value is generated from a sequence every time the user launches the IDE and the TRACKLAUNCHIDE API is called.
PRREF_ID NUMBER (22). Prref ID of the Program or Business Area from which the IDE is being launched or the one containing shared source code. See "Getting an Object's Prref_Id and Prref_Ver".
PRREF_VER NUMBER (7). Prref version of the Program or Business Area from which the IDE is being launched or the one containing the shared source code. See "Getting an Object's Prref_Id and Prref_Ver".
SHARED_FLAG_RC VARCHAR2 (30). If N, the Program is the Program being launched. If Y, the Program contains shared source code required for the Program being launched.
Note:
Normally Business Area adapters do not use shared source code and always have this flag set to N.EXT_SYS_ENTITY VARCHAR2 (4000). The entity in the external system which maps to the Oracle LSH Program or Business Area. For example, each Oracle LSH Informatica Program corresponds to a Folder in Informatica. If this relationship is required, your adapter must track it.
LAUNCH_USER_ID NUMBER (15). ID of the user who is launching the IDE.
If you are creating a Program or Business Area adapter, you may need to create one or more new Source Code types and a new file type for each new Source Code type.
Before you can create a source code type you must:
Create a technology type by calling the Create Technology Type API; see "Creating a Technology Type". Enter the Tech Type ID in the TECH_TYPE_ID column for the source code type.
Add the file type you need for your adapter source code by extending the CDR_FILE_TYPES lookup; see "Adding Lookup Values". Enter the file extension in the DEFAULT_EXTN column for the source code type. Do not include the dot (.) before the letters in the extension.
You then create a a new Source Code type to link your technology type with your file type. To create a new Source Code type, call the procedure cdr_srccode_types_m.insertRow:
PROCEDURE InsertRow( pRecord in out nocopy CDR_SRCCODE_TYPES%rowtype );
The columns in the Source Code Types table include the following. See the section on Source Code in the "Defining Programs" chapter of the Oracle Life Sciences Data Hub Application Developer's Guide for information about these Source Code attributes.
Tech Type ID. Enter the ID of your technology type. This is generated by Oracle LSH when you create the technology type; see "Creating a Technology Type".
Source Code Type RC. Enter the new file type you added to the CDR_FILE_TYPES lookup in the format $FILETYPES$NEW_FILE_TYPE.
Binary Flag RC
Use Libname RC
Position
Allowed as Primary Flag RC
Instantiated from Different Program
Default Extension. Enter the extension of the new file type, including the dot (.) preceding it; for example, .xml.
You can use the view CDR_PUB_GENERIC_BA_V to make the Oracle LSH object hierarchy—from Domains to Application Areas to Work Areas to Business Areas—to which the current user has access, available in the external system. You can then use it to create a display in the external system's user interface that helps users find what they need; see "Display User's Business Area Instances".
The user must log in using the database account.
Note:
This works only if you give the objects in the hierarchy meaningful names.When the user launches the IDE from Oracle LSH, the Oracle LSH client, cdrclient, generates a run script for the IDE launch based on what is defined in cdrconfig.xml.
Download the cdrconfig.xml file shipped with Oracle LSH and add information for your system's technology type to cdrconfig.xml as follows. See the Oracle Life Sciences Data Hub Installation Guide chapter on installing Oracle LSH, section on installing the client plug-in.
<TECHTYPE TYPE="$TECHTYPES$<tech_type_name>" NAME="<name>" VERSION="<version>">
<PARAM NAME="EXEHOME" VALUE="<complete_path_to_exe_file>"/>
<PARAM NAME="PREPROC" VALUE=""/>
<PARAM NAME="POSTPROC" VALUE=""/>
<CMDLINEARGS>
<ARG POSITION="_POS1_" VALUE="_COMP999_"/>
</CMDLINEARGS>
</TECHTYPE>
For PREPROC, enter the full path to a batch file or shell script you have written, if any, that must be run immediately before running the .exe file for the IDE. For example, the OBIEE adapter needs to unzip the RPD file at this point and has a preprocessing .bat file for this purpose.
Use POSTPROC similarly, for a batch file or shell script that must be run immediately following the IDE launch.
CMDLINEARGS are for command line arguments to pass to the IDE executable.
You can specify additional argument values by adding argument/value pairs for positions 1 to 999 at the line:
<ARG POSITION="_POS1_" VALUE="_COMP999_"/>
For example, if you need to download a file to the IDE, add another argument/value pair such as "_comp002_", "<filename>" where <filename> is the file to be downloaded. The OBIEE adapter creates a default RPD file and downloads it to the IDE, where the user can edit it in the BI administrator's tool.
You must match argument values in the line above to the names you use in the Build_IDE_Cfg_Function, and output the argument/value pairs in the col_comp_list attribute of the po_Launch_Data parameter of the Build_IDE_Config_Function; see "col_comp_list".
For example:
<TECHTYPE TYPE="$TECHTYPES$OBIEEDEV" NAME="AdminTool" VERSION="10.1.3.4">
<PARAM NAME="EXEHOME" VALUE="D:\OracleBI\server\Bin\AdminTool.exe"/>
<PARAM NAME="PREPROC" VALUE="unzipRPD.bat"/>
<PARAM NAME="POSTPROC" VALUE=""/>
<CMDLINEARGS>
<ARG POSITION="_POS1_" VALUE="_COMP999_"/>
</CMDLINEARGS>
</TECHTYPE>
For adapters that produce outputs you must create subdirectories on the Definer's PC to hold IDE job outputs (for example, any outputs that are defined as Planned Outputs of Programs) or any other files, such as error or log files.
Oracle LSH creates a directory structure on the Definer's PC that uses the organizational structure defined for the Program or Business Area in Oracle LSH. By default the location is: %USERPROFILE%\Application Data\CDR\cdrwork\user_name\Domain(s)\Application_Area\Work_Area\launched_object_name\version. Oracle LSH creates the subdirectory you specify inside the version directory.
In the Build_IDE_Cfg_Function specify one or more subdirectory names in the col_sub_dirs attribute of the parameter po_LaunchData.
|
 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|