Metabase Oracle

-->

A tutorial on deploying Metabase on AWS Elastic Beanstalk Corresponding blog post here: https://medium.com/@timabe/how-to-bootstrap-your-analytics-in-1-hour. Oracle database products offer customers cost-optimized and high-performance versions of Oracle Database, the world's leading converged, multi-model database management system, as well as in-memory, NoSQL and MySQL databases.

Microsoft provides you with a robust set of script file commands to execute and control SSMA activities. The console application uses certain standard script file commands as enumerated in this section.

Project Script File Commands

The Project commands handle creating projects, opening, saving, and exiting projects.

Command

create-new-project
: Creates a new SSMA project.

Metabase: Specifies whether it ithe source or target metabase. Destination: Specifies the path or the folder where the script has to be saved. Sql specifies the Oracle sql statements to be converted, one or more statements can be separated using a ';'. The Oracle providers are installed on the computer that is running SSMA. You have synchronized the converted objects with the SQL Server database. In Oracle Metadata Explorer, select the objects that contain the data that you want to migrate: To migrate data for all schemas, select the check box next to Schemas. Metabase Enterprise Edition lets you quickly and easily embed interactive reports in your app, with simple tools to let you match the look and feel to your brand. Learn more about Embedded Analytics See how it all fits together Reporting for your customers in under 10 minutes.

Script

  • project-folder indicates the folder of the project getting created.

  • project-name indicates the name of the project. {string}

  • overwrite-if-existsOptional attribute indicates if an existing project should be overwritten. {boolean}

  • project-type:Optional attribute. Indicates the project type i.e. 'sql-server-2005' project or 'sql-server-2008' project or 'sql-server-2012' project or 'sql-server-2014' project or 'sql-azure'. Default is 'sql-server-2014'.

Metabase

Example:

Attribute 'overwrite-if-exists' is false by default.

Attribute 'project-type' is sql-server-2008 by default.

Command

open-project: Opens an existing project.

Script

  • project-folder indicates the folder of the project getting created. The command fails if the specified folder does not exist. {string}

  • project-name indicates the name of the project. The command fails if the specified project does not exist. {string}

Syntax Example:

SSMA for Oracle Console Application supports backward compatibility. You will be able to open projects created by previous version of SSMA.

Metabase Oracle Tns

Command

save-project

Saves the migration project.

Script

Syntax Example:

Command

close-project

Closes the migration project.

Script

Syntax Example:

Database Connection Script File Commands

The Database Connection commands help connect to the database.

  • The Browse feature of the UI is not supported in console.

  • For more information on 'Creating Script Files', see Creating Script Files (OracleToSQL).

Command

connect-source-database

  • Performs connection to the source database and loads high level metadata of the source database but not all of the metadata.

  • If the connection to the source cannot be established, an error is generated and the console application stops further execution

Script

Server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file.

Syntax Example:

Oracle.metabase-driver.jar

Command

force-load-source/target-database

  • Loads the source metadata.

  • Useful for working on migration project offline.

  • If the connection to the source/target cannot be established, an error is generated and the console application stops further execution

Script

Requires one or several metabase nodes as command line parameter.

Syntax Example:

or

Metabase Oracle

Command

reconnect-source-database

Metabase Add Oracle Connector

  • Reconnects to the source database but does not load any metadata unlike the connect-source-database command.

  • If (re)connection with the source cannot be established, an error is generated and the console application stops further execution.

Script

Syntax Example:

Command

connect-target-database

  • Connects to the target SQL Server database and loads high level metadata of the target database but not the metadata entirely.

  • If the connection to the target cannot be established, an error is generated and the console application stops further execution.

Metabase Oracle

Script

Server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file

Syntax Example:

Command

reconnect-target-database

  • Reconnects to the target database but does not load any metadata, unlike the connect-target-database command.

  • If (re)connection to the target cannot be established, an error is generated and the console application stops further execution.

Script

Syntax Example:

Report Script File Commands

The Report commands generate reports on the performance of various SSMA Console activities.

Command

generate-assessment-report

  • Generates assessment reports on the source database.

  • If the source database connection is not performed before executing this command, an error is generated and the console application exits.

  • Failure to connect to the source database server during the command execution, also results in terminating the console application.

Script

  • conversion-report-folder: Specifies folder where the assessment report can to be stored.(optional attribute)

  • object-name: Specifies the object(s) considered for assessment report generation (It can have indivdual object names or a group object name).

  • object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • conversion-report-overwrite: Specifies whether to overwrite the assessment report folder if it already exists.

    Default value: false. (optional attribute)

  • write-summary-report-to: Specifies the path where the summary report will be generated.

    If only the folder path is mentioned, then file by name AssessmentReport<n>.XML is created. (optional attribute)

    Report creation has two further sub-categories:

    • report-errors (='true/false', with default as 'false' (optional attributes))

    • verbose (='true/false', with default as 'false' (optional attributes))

Syntax Example:

or

Migration Script File Commands

The Migration commands convert the target database schema to the source schema and migrates data to the target server.

The default console output setting for the migration commands is 'Full' output report with no detailed error reporting: Only summary at the source object tree root node.

Command

convert-schema

  • Performs schema conversion from source to the target schema.

  • If the source or target database connection is not performed before executing this command or the connection to the source or target database server fails during the command execution, an error is generated and the console application exits.

Script

  • conversion-report-folder: Specifies folder where the assessment report can to be stored.(optional attribute)

  • object-name: Specifies the source object(s) considered for converting schema (It can have indivdual object names or a group object name).

  • object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • conversion-report-overwrite: Specifies whether to overwrite the assessment report folder if it already exists.

    Default value: false. (optional attribute)

  • write-summary-report-to: Specifies the path where the summary report will be generated.

    If only the folder path is mentioned, then file by name SchemaConversionReport<n>.XML is created. (optional attribute)

    Report creation has two further sub-categories:

    • report-errors (='true/false', with default as 'false' (optional attributes))

    • verbose (='true/false', with default as 'false' (optional attributes))

Syntax Example:

or

Command

migrate-data

Migrates the source data to the target.

Script

  • conversion-report-folder: Specifies folder where the assessment report can to be stored.(optional attribute)

  • object-name: Specifies the source object(s) considered for migrating data (It can have indivdual object names or a group object name).

  • object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • conversion-report-overwrite: Specifies whether to overwrite the assessment report folder if it already exists.

    Default value: false. (optional attribute)

  • write-summary-report-to: Specifies the path where the summary report will be generated.

    If only the folder path is mentioned,then file by name DataMigrationReport<n>.XML is created. (optional attribute)

    Report creation has two further sub-categories:

    • report-errors (='true/false', with default as 'false' (optional attributes))

    • verbose (='true/false', with default as 'false' (optional attributes))

Syntax Example:

or

Migration Preparation Script File Commands

The Migration Preparation command initiates schema mapping between the source and target databases.

Command

map-schema

Schema mapping of source database to the target schema.

Migrates the source data to the target.

Script

  • source-schema specifies the source schema we intend to migrate.

  • sql-server-schema specifies the target schema where we want it to be migrated.

Syntax Example:

Manageability Script File Commands

The Manageability commands help synchronize the target database objects with the source database. The default console output setting for the migration commands is 'Full' output report with no detailed error reporting: Only summary at the source object tree root node.

Command

synchronize-target

  • Synchronizes the target objects with the target database.

  • If this command is executed against the source database, an error is encountered.

  • If the target database connection is not performed before executing this command or the connection to the target database server fails during the command execution, an error is generated and the console application exits.

Script

  • object-name: Specifies the target object(s) considered for synchronizing with target database (It can have indivdual object names or a group object name).

  • object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • on-error: Specifies whether to specify synchronization errors as warnings or error. Available options for on-error:

    • report-total-as-warning

    • report-each-as-warning

    • fail-script

  • report-errors-to: Specifies location of error report for the synchronization operation (optional attribute) if only folder path is given, then file by name TargetSynchronizationReport.XML is created.

Syntax Example:

or

or

Command

refresh-from-database

  • Refreshes the source objects from database.

  • If this command is executed against the target database, an error is generated.

Script

Requires one or several metabase nodes as command line parameter.

  • object-name: Specifies the source object(s) considered for refreshing from source database (It can have individual object names or a group object name).

  • object-type: Specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • on-error: Specifies whether to specify refresh errors as warnings or error. Available options for on-error:

    • report-total-as-warning

    • report-each-as-warning

    • fail-script

  • report-errors-to: Specifies location of error report for the refresh operation (optional attribute) if only folder path is given, then file by name SourceDBRefreshReport.XML is created.

Syntax Example:

or

or

Metabase Oracle

Script Generation Script File Commands

The Script Generation commands perform dual tasks: They help save the console output in a script file; and record the T-SQL output to the console or a file based on the parameter you specify.

Command

save-as-script

Used to save the scripts of the objects to a file mentioned when metabase=target ,this is an alternative to synchronization command where in we get the scripts and execute the same on the target database.

Script

Requires one or several metabase nodes as command line parameter.

  • object-name: Specifies the object(s) whose scripts are to be saved . (It can have individual object names or a group object name)

  • object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type will be 'category').

  • metabase: Specifies whether it ithe source or target metabase.

  • destination: Specifies the path or the folder where the script has to be saved, if the file name is not given then a file name in the format (object_name attribute value).out

  • overwrite: if true then it overwrites if same filename exist. It can have the values (true/false).

Syntax Example:

or

Command

convert-sql-statement

  • context specifies the schema name.

  • destination specifies whether the output should be stored in a file.

    If this attribute is not specified, then the converted T-SQL statement is displayed on the console. (optional attribute)

  • conversion-report-folder specifies folder where the assessment report can to be stored.(optional attribute)

  • conversion-report-overwrite specifies whether to overwrite the assessment report folder if it already exists.

    Default value: false. (optional attribute)

  • write-converted-sql-to specifies the file (or) folder path where the converted T-SQL is to be stored. When a folder path is specified along with the sql-files attribute, each source file will have a corresponding target T-SQL file created under the specified folder. When a folder path is specified along with the sql attribute, the converted T-SQL is written to a file named Result.out under the specified folder.

  • sql specifies the Oracle sql statements to be converted, one or more statements can be separated using a ';'

  • sql-files specifies the path of the sql files which has to be converted to T-SQL code.

  • write-summary-report-to specifies the path where the report will be generated. If only the folder path is mentioned, then file by name ConvertSQLReport.XML is created. (optional attribute)

    Report creation has 2 further sub-categories, viz.:

    • report-errors (='true/false', with default as 'false' (optional attributes)).

    • verbose (='true/false', with default as 'false' (optional attributes)).

Script

Requires one or several metabase nodes as command line parameter.

Metabase

Syntax Example:

or

or

Next Step

For information on command line options, see Command Line Options in SSMA Console (OracleToSQL) .

For information on Sample console script files, see Working with the Sample Console Script Files (OracleToSQL)

The next step depends on your project requirements:

  • For specifying a password or export/ import passwords, see Managing Passwords (OracleToSQL).

  • For generating reports, see Generating Reports (OracleToSQL).

  • For troubleshooting issues in console, see Troubleshooting (OracleToSQL).

Home
E-mail Us
Oracle Articles
New Oracle Articles


Oracle Training
Oracle Tips

Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support


SQL Tuning
Security

Oracle UNIX
Oracle Linux
Monitoring
Remote s
upport
Remote plans
Remote
services
Application Server

Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support

Remote S
upport
Development

Implementation


Consulting Staff
Consulting Prices
Help Wanted!


Oracle Posters
Oracle Books

Oracle Scripts
Ion
Excel-DB



MetaBase scripting for the
Oracle data warehouse DBA


Donald K. Burleson
06 Jul 2005
One of the most challenging areas for any Oracle DBA is the management of complex job streams. Fortunately, Oracle has come to the rescue with the dbms_scheduler package and the Oracle Warehouse Builder MetaBase Plus (OMB) scripting language.

For Oracle data warehouse administration, the Oracle Warehouse Builder product offers OMB as a way to integrate all Extract, Transform and Load (ETL) activities by providing built-in tools that provide error-checking, validation and data mappings. It's sort of like a TP monitor (CICS) for the OWB environment.

While Oracle has made great headway with the GUI interfaces in Oracle 10g Enterprise Manager, senior Oracle professionals still feel the need to use more-robust scripting environments. A GUI can't do it all, and advanced operations require more flexibility and sophisticated job control logic:

  • Scheduled execution -- Fire-off job streams at pre-determined times, with pre-defined prerequisite conditions. Ensure that a 'missed' job is re-scheduled.
  • Conditional execution -- execute this task based on the status of completed tasks.
  • Error alerts and job stream validation -- The Oracle DBA needs to define the scope of everything that might go-wrong and create user-exits to pause execution until important issues are resolved.

The Oracle data warehouse professional has two choices for managing the ETL for their data warehouse:

  • Do It Yourself (DIY) -- If the Oracle DBA already understands how to create batch streams, do error-checking and enforce conditional execution, writing your own ETL is an option. The Oracle10g dbms_scheduler package can be used to schedule job-streams, or the traditional method of OS shell scripts is sometimes used. The DIY approach has more flexibility and you can choose best-of-breed approaches and techniques, rather than relying on the OWB offerings. Dr. Tim Hall has an excellent book on the jobstream subject titled 'Oracle Job Scheduling', a comprehensive reference for performing complex job executions in an Oracle environment.
  • Use Oracle MetaBase -- OMB is a extension of the Tcl language (pronounced 'tickle') and it has all of the robust features of any programming language, including complex Boolean operators, variable support, and loops (FOR, WHILE, REPEAT-UNTIL). The Oracle warehouse Tcl extensions are customized into OMB, so you can perform complex data mappings and program job streams. Like Java, OMB is platform independent and it requires no changes to run MetaBase on a Mainframe or a Macintosh.

Of course, MetaBase requires you to use the extra-cost Oracle Warehouse Builder to manage your ETL, but the OWB OMB scripting language is an attractive alternative to the do-it-yourself approach, depending upon your level of ETL experience.

Inside OMB syntax

Let's take a closer look at the OMB environment. OMB is very much like SQL*Plus and it has a list of built-in commands that can be invoked to manage OWB data mappings. These data mappings can be nested (a hierarchical structure) and OMB offers commands to allow you to invoke these mappings from a Tcl program. There are two types of OMB commands:

  • Metadata Definition Language (MDL) -- Like DDL, it allows you to create and drop OMB objects. MDL object types include projects, modules, tables, mappings, and workflow processes.
    • OMBALTER -- This is used to alter the metadata for a Warehouse Builder component. In the example below, note the UNIX-like backslash commend continuation characters:
    • OMBCREATE -- use this command to create a component in OWB, which might be a project, module, table, mapping, or workflow processes.
    • OMBDROP -- Used to drop named objects from the repository.
  • Metadata Manipulation Language (MML) -- Analogous to Oracle DML, the MML commands allow us to alter (add, update, delete) named objects. Some of the MML commands include:
    • OMBCC -- This is the 'change context' command and it is used like the UNIX and DOS 'cd' command to change OWB directories.
    • OMBCOMMIT -- Commits a transaction
    • OMBCOPY -- This is a handy command for cloning a section of use this command to copy one or more objects of the same type.
    • OMBDEPLOY -- This deploys an object to a specified database via the OWB runtime tables.
    • OMBLIST -- the OMBLIST command is like a directory listing command (ls or dir) and it lists all OWB objects under a specific hierarchical tree.
    • OMBRECONCILE -- This is used to reconcile the target OWB metadata definition with the target database metadata definition.
According to the ODTUG paper 'Using Oracle Metabase Plus Language To Build And Deploy Mappings And Workflows' using Oracle's MetaBase is an attractive alternative to traditional job stream applications and it shows working examples of Tcl with the MetaBase extensions: Similarly, the example below shows the deployment of OWB process flows to the Oracle Workflow engine. First, it connects to the repository and it changes the context to STUDENT_YEAR_4 module. It then retrieves all the workflow packages starting with a particular naming pattern. The inner loop also controls as to what group of workflow should be deployed.

Conclusion

Will OMB take-off as the ETL scripting language of choice for the Oracle warehouse DBA? Since OWB usually comes with a data warehouse license, it's essentially a free tool, and any data warehouse DBA will want the robust features of being able to manage OWB from a non-GUI environment. The only downside, of course, is the requirement to learn the Tcl language.

References

  • 'Using Oracle Metabase Plus Language To Build And Deploy Mappings And Workflows'
    Presented at ODTUG 2005 by Allison Lobato and Nghia Phu.

About the Author

Donald K. Burleson has been a DBA for more than 20 years and specialized in Oracle performance tuning. The author of more than 30 Oracle books, Burleson provides Oracle consulting at www.dba-oracle.com and remote Oracle support at www.remote-dba.net.


��