Application Component

1. Click the  Application  tab in the left pane and then click:
   • Application Load - Refer to Loading an Application to load the metadata, tables procedures, scripts etc. provided as part of the application.
   • Change Application Directory - Refer to Changing the Application Directory to change the directory of applications to be installed.
   • List applications in Repository - Refer to Listing Applications to list all applications that have been applied to the chosen metadata repository.

Loading an Application

1. In  WhereScape's Setup Administrator, click the  Application  tab and then click  Application Load  .

Loading an application inserts various objects into the chosen metadata repository. An application is best defined as a set of objects that are shipped to allow inclusion of those objects into a remote repository.

During the load of an application a number of cross environment mappings need to be resolved. These are:

Connections

Connections are normally unique for each metadata repository. They provide the path to the source data and this nearly always varies. Even though an application can ship a set of connections, these connections will nearly always need modification. Alternatively, a connection shipped with an application may map to an existing connection, and this can be done as part of the load process. If you are unsure whether or not a connection is required, it can be added and later modified.

Tablespaces (Filegroups in SQL Server and Databases in Teradata)

If an application ships table or index objects, then these objects will typically have a tablespace (filegroup in SQL Server and database in Teradata) associated with them. During the load, it is necessary to map these tablespaces (filegroups in SQL Server and databases in Teradata) to those available for the metadata repository.

Object Changes

The application may ship objects that already exist in the metadata. In such a case, the process during the load is to create a version of the existing object and then replace it with the new object. At the start of the load, a check is made to see if any objects will be replaced. If the application is a patch or an upgrade of an existing application version, then this will be normal. The load can be stopped at this point to allow for further investigation.

Table Changes

If the load replaces table objects, it may in turn need to alter the physical table in the database to match the new definition. Again, if this is a patch or an application upgrade, this may be desirable. If not, the proposed actions should be reviewed before proceeding.

Procedure Changes

New procedures, scripts, or modifications may be loaded as part of the application load. In the case of procedures, the procedures need to be recompiled. The load can perform this action, but any failures or issues needs to be resolved after the load has been completed.
The following section covers the actual application load process.

Backing Up the Metadata before Loading Applications

It is always a good idea to backup the WhereScape RED metadata before running an application load.
A metadata backup can be used to restore the metadata to the state before the application was loaded.
There are three options for backing up the metadata:

1. In RED, use the  Backup > Unload the metadata to disk  option to unload the metadata to a WhereScape Proprietary backup. Refer to  Unloading Metadata  in the  WhereScape   RED User Guide  for more information.
2. In RED, use the  Backup > Export the metadata  option to backup the metadata, using the data warehouse database's backup utility. Refer to  Backup using DB Routines  in the  WhereScape   RED User Guide  for more information.
3. In RED, use the  Tools > Build application tables  option to build an application of the objects being replaced. This is achieved by selecting the new application files you're about to load in the  Previous application  box of the Define and build an application distribution window. Refer to  Application Creation  in the  WhereScape RED User Guide  for more information.
   

   Note

   If the table changes in the application are being applied to the data warehouse database, then it is also a good idea to backup the data warehouse tables being changed.

Application Load Process

An application consists of multiple files that are loaded from a directory. The default directory is the WhereScape program directory. Another directory may be specified by clicking the  Change Application Directory  button. The last selected application directory is the default directory for the next application load.
Once the directory has been selected, the application load is started by clicking the  Application Load  button. This displays the available applications in the right pane.

An example application list follows:

• To load an application, either right-click the Application from the list and select  Install Application  from the pop-up menu or double-click the Application. Ensure the Application being installed has the same database type as the Metadata Repository, otherwise installation will fail.
• Once this menu option is chosen, enter the ODBC data source and logon to the meta repository and click  OK.
  

  Notes

  Applications created in 3D cannot be loaded into the latest version of RED if they were created in WhereScape 3D before version 2.8.0.0. To complete the application load process; export the XML file from the latest version of 3D OR complete the application load process in a version of RED earlier than 6.8.6.3.

  
                  1. Teradata Applications
  
• When loading an application into Teradata, the TDPID will be tested. If the TDPID test fails at this stage, a dialog will appear asking you to enter the correct TDPID. Click Test and Use after entering the TDPID and relevant credentials. If you skip the test and provide an invalid TDPID, native loads will not work in WhereScape RED. Teradata native loads use bteq which connects natively to the database using its own internal protocols without using the ODBC driver. Therefore, ODBC settings are not relevant in this process and a valid Teradata TDPID is required to load application procedure and scripts successfully using native load.
• After the TDPID check, an additional dialog is displayed for selecting the metadata repository database. Select the database and click OK.  
  
  
                  2. Oracle applications
  
  

When loading an application into Oracle, the TNS Name will be tested. If the TNS Name test fails at this stage, a dialog will appear asking you to enter the correct TNS Name.
Click Test and Use after entering the TNS Name. If you skip the test and provide an invalid TNS Name, native loads will not work in WhereScape RED.

A list of all the objects in the application is displayed in the right pane. Each object is identified as either new to the metadata, or as a replacement for an existing object. For example:

A window appears after the list has been displayed, advising of what is to occur. You may cancel the application load at this point and review the objects to be replaced. The load can then be continued by using the right-click menu option when positioned over any object.
Click OK to proceed with the application load. A new window appears providing options for the application load:

If you click  OK, the objects are inserted into the metadata.

Metadata Options

Application Files

General Options

Connections

Jobs

Object Source Mapping Sets

This option is only available when applications are being deployed from 3D to RED.

Data Warehouse Options

Data Warehouse Table/View Options

For each table and view object loaded by the application load, it is possible to specify whether the metadata changes should be applied to the data warehouse table or view; and to the indexes on these objects. The change action can be specified by object type. New objects can have a different action to existing objects of the same type. Possible actions are:

• Created  - available for new tables and indexes of any object type
• Recreated  - available for existing tables and indexes of any object type
• Altered  - available for existing tables and indexes of most object types (not available for load tables or views)
• Nothing  - available for all new and existing object types and indexes.

The following additional settings can be configured when importing Load table objects from 3D to RED:

• New Table Default Load Type, if not set in Source Connection
• New Table Default Load Script Connection, if not set in Source Connection
• New Table Default Load Script Template, if not set in Source Connection
  

  Note

  The above default settings are only applied for new Load table objects, if these attributes are not defined in the Source Connection. They only apply for new Load table objects and Connections from 3D.

This window also enables you to set whether or not new or existing objects are to be versioned. Set to  True  to use versioning, else set to  False.

Other options:

• Alter allows columns to be dropped  - This option enables you to set whether or not columns are allowed to be dropped by object type, for tables being altered. Set to  True  to allow columns that are not in the metadata, to be dropped; else set to  False.
• Allow alter table processing with non-compliant DDL  - This option is used for custom database targets and sets whether or not to allow DDL statements that do not use a substitutable format for the table name to be used during validation, and subsequent alter table processing. Default setting is  True. The DDL statements are set in the template that is defined in the custom database Connection properties (Connection > Target Settings > Default Table Create DDL Template) in RED. Refer to Connection Target Settings for Custom Database in the RED User Guide for details.
  

  Warning

  Setting this option to TRUE can lead to the DDL being run against the actual data warehouse table.

The above options applies to Stage, Permanent Stage, Dimension/Model, ODS, Normal, Fact and Aggregate tables.
Object action settings may be saved and reloaded later for another application using the  Save Settings  and  Load Settings  buttons. Supplied default settings may also be loaded by clicking the  Default Settings  button.
 
The Connection Build Window
The  Connection Build  window appears when the connection used by an imported table does not exist in the destination Data Warehouse.

The  Connection Build  window is displayed if the following criteria are met:

• If the connection the table uses is included in the exported files, but the  Add Connections   as required  setting is disabled, the window is displayed with the  Add Connection  button. Clicking the  Add Connection  button adds the connection included in the exported files to the destination Data Warehouse.
• If the connection the table uses is not included in the exported files, the window is displayed without the  Add Connection  button. The user is required to select an existing connection in the destination Data Warehouse.

Loading Applications with Targets

When importing tables that contain Targets which do not exist in the destination Data Warehouse, users can select existing Targets from the list or add new ones, using the  Add Connection/Target  in the  Resolve Target  window, see the topic below for more information.

Adding Connections and Targets

On the  Add Connections and Targets  screen, click the  Add  button to add your desired connection.

Setup Administrator recognizes you are trying to import a table that references a specific connection type and that option will be selected by default in the  Connection Type  drop-down list. In the example below, Setup Administrator recognizes a tabular table imported from a Microsoft Analysis Services 2005+ connection and selects it.
Type in the connection details and then click  OK  to create the connection.

The Filegroup /Tablespace Selection window
Filegroups/Tablespaces may need to be manually remapped to the corresponding Filegroup/Tablespace available to the metadata repository.
A window is displayed for each Filegroup/Tablespace encountered that is not automatically mapped (refer to Filegroup/Tablespace options above).

The  New Filegroup  drop-down lists all filegroups visible to the repository.
A window appears when the application load has been completed. The full results should be reviewed by scrolling back, or you can right-click and click  Excel It  to send the results to Microsoft Excel.

Creating and Loading Applications from the Command Line

It is possible to create and load applications from the command line by running a batch file. The WhereScape RED Application directory contains an example batch file  WSL_Application_Create_Restore_Point_and_Load.bat for creating and loading.
If you right-click this file and select  Edit  you will see the steps outlined, as well as the details on the options available.
The first step  creates  a restore point application (R) based on objects about to be loaded. This process calls the command line functionality of RED and creates the application file.
The second step  loads  an application (A) into a test WhereScape RED repository. It uses an XML Options File to specify various options, calls the command line automation functionality of Setup Administrator and loads the application (A). Ensure that you have already created and properly defined the XML Options File. Refer to XML Options File for Application Loads for details.
The WhereScape RED Application directory contains example XML files:

• WSL_Application_Load_SQL.xml for SQL Server
• WSL_Application_Load_ORA.xml for Oracle
• WSL_Application_Load_TER.xml for Teradata

The tags in the XML Options File need to be edited because the login/connection details, etc. need to be set. Refer to XML Options File for Application Loads for details.
The Batch Application Create options and the Batch Application Load options (described below) are listed at the end of the batch file. The values for these variables also needs to be customized before running this file.

The process typically involves the following steps:

1. Create an application (A) in RED containing your data warehouse changes.
2. RUN WSL_Application_Create_Restore_Point_and_Load.bat from the command line; which creates a restore point application (R) and applies application (A) to a test WhereScape RED repository.
3. If the changes are incorrect, they can be undone by loading the restore point application (R).

Batch Application CREATE Options
The following options are available on RED to create applications.

If using a trusted connection, the user name and password are not necessary.

Batch Application LOAD Options
The following options are available on Setup Administrator to load applications.

Setup Administrator Process Exit Codes

When installing an application using the command line, the Setup Administrator process returns one value from a set of exit codes from its process when exiting.
After an application install, when the Setup Administrator process exits, users can check the exit codes to determine the cause for a possible problem with the application install.
The set of Setup Administrator exit codes are described below:

Users can test for these values, using a batch file and take action accordingly.
Below is an example batch file that can be used to check Setup Administrator's exit code.

Example batch file - SQL: 

@echo off
 
Adm.exe /AL /AF "app_con_SQLT_base_1.wst" /LF "ImportLog.txt" /PF 
"WSL_Application_Load_SQL.xml"
 
if errorlevel 8 goto MetaDataError 
if errorlevel 5 goto FileIoError 
if not errorlevel 0 goto ImportError
 
echo Successfully imported 
goto end


:MetaDataError 
sendmail -recipient admin@company.com -subject "Import failed" -body "Corrupt meta data found during application import. Re-install the Data Warehouse" 
goto end

:FileIoError sendmail -recipient admin@company.com -subject "Import failed" -body "ExportedDiagram.xml is missing, re-generate it then re-run this process" 
goto end

:ImportError sendmail -recipient admin@company.com -subject "Import failed" -body "Unable to import the application file ExportedDiagram.xml" 
goto end
 
:end

Example batch file - 3D: 

@echo off
Start /wait /D "C:\Program Files (x86)\WhereScape\Adm.exe" /AL /AF "ExportedDiagram.xml" /LF "C:\temp\test\ImportLog.txt"
if %errorlevel% equ 8 goto MetaDataError
if %errorlevel% equ 5 goto FileIoError
if not %errorlevel% equ 0 goto ImportError
echo Successfully imported
goto end

:MetaDataErrorsendmail -recipient admin@company.com -subject "Import failed" -body
"Corrupt meta data found during application import. Re-install the Data Warehouse"
goto end


:FileIoErrorsendmail -recipient admin@company.com -subject "Import failed" -body
"ExportedDiagram.xml is missing, re-generate it then re-run this process"
goto end


:ImportErrorsendmail -recipient admin@company.com -subject "Import failed" -body
"Unable to import the application file ExportedDiagram.xml"
goto end
 
:end

Changing the Application Directory

1. In WhereScape's Setup Administrator, click the  Application  tab and then click  Change Application Directory.

2. Browse and select a new application directory.

Listing Applications

1. In  WhereScape's Setup Administrator, click the  Application  tab in the left pane and then click  List applications in Repository.

2. The  List applications in Repository  menu option prompts for a logon to the required metadata repository. Applications are applied to a specific metadata repository.Once logged on a list of the applications that have been installed into that repository is shown. If no applications have been loaded, then the list is empty. An example output is as follows:

This example shows that the tutorial_2 application has been loaded into the metadata.

Testing Applications

A testing application set consists of a Procedure and an XML script and provides the ability to define a series of tests against data warehouse objects; either comparing them to an expected value or to the results of a query.
The XML script contains the test definitions. Each test is a new XML node in the XML script and contains a name, a test query, an expected value, or a comparison query.
The procedure simply runs the tests and determines whether the tests are passed or not. This is most likely to be run as a scheduled job within WhereScape RED.

XML Options File for Application Loads

If applications are being loaded using the command line, the saved application load options in the local registry can be outputted to an XML Options File for use in the application load. Refer to Creating and Loading Applications from the Command Line for details

1. To create the options file, click  Create XML Options File  in the left pane.
   

   Tip

   You can also access the Create XML Options File from the Applications menu.

   
   
2. If the load options have not yet been saved into the local registry, a message appears prompting you to select the application to be loaded and to save the load settings for that application.
   • • Type in the location where you want to save the XML Options File in the  XML Options File Name  field.
     • Type in the details of the  XML Elements  that are added to the destination details of the XML Options File.

       Metadata Repository Type	The RDBMS type of the metadata repository.
       DSN	The value to be written in the <DSN/> element of the XML Options File.
       DSN architecture	The value to be written in the <DSNArchitecture> element of the XML Options File.
       User ID	The value to be written in the <UserID/> element of the XML Options File.
       Password	The value to be written in the <Password/> element of the XML Options File.

        
3. Click  Create File, the results confirm that the XML Options File has been created.