Bookshelf Home | Contents | Index | PDF     

Oracle® Business Intelligence Server Administration Guide > Connectivity and Third-Party Tools in Oracle BI Server > Exchanging Metadata with Databases >

Generating the Import File

Both the Oracle Database Metadata Generator and the DB2 Cube Views Generator create the files that are needed to import metadata from Oracle BI into the Oracle Database Summary Advisor or an IBM DB2 Database. Before reading this section, read Finding Information on Metadata Exchange.

This section contains the following topics that are common to the two generators:

Running the Generator

The Oracle Database Metadata Generator and the DB2 Cube Views Generator are invoked from the command line or embedded in a batch file. The command-line executable is named SAMetaExport.exe, and has the following syntax:

SAMetaExport -r "PathAndRepositoryFileName" -u <UserName> -p <Password>
-f "InputFileNameAndPath" [-t "ORACLE" or "DB2"]

Table 30 contains descriptions of the parameters in the command-line executable file.

Table 30. Parameters for SAMetaExport.exe
Parameter
Definition
Additional Information

-r

Repository file name and full path

Quotation marks are required for the file name and path only if the file path is in long format or has spaces. Use the full path if the file is not in the current directory.

-u

User name

User name that will allow access to the repository.

-p

Password

Password for the user name. If the repository password is empty, then do not use the password parameter.

-f

Input file name and full path

Quotation marks are required for the file name and path only if the file path is in long format or has spaces. Use the full path if the file is not in the current directory. You specify input files so that you do not have to type all the required information at the command line, and so that you can type international characters. For more information about the input file, refer to About the Metadata Input File.

-t

ORACLE or DB2

Indication for either the Oracle or IBM DB2 Database. You can omit this parameter because the database type is automatically detected based on information in the repository.

About the Metadata Input File

The input file is a text file that contains the parameters that are described in Table 31.

Table 31. Cube Metadata Input File Parameters
Input File Name
Description

BUSINESS_MODEL

The name of the business model in the logical layer of the Oracle BI repository that contains the metadata that you want to export. If the business model is not found in the repository, then an error message is displayed.

PHYSICAL_DATABASE

The name of the database in the physical layer of the Oracle BI repository that contains the metadata that you want to export. When the business model derives from more than one database, then it eliminates metadata from all databases other than the one specified here. When the physical database is not found in the repository, an error message is displayed.

RUN_AS_USER

The user name of the user whose visibility needs to be duplicated for the metadata export. This cannot be empty. If the user is not found in the repository, then an error message is displayed.

OUTPUT_FOLDER

The full path and file name of the folder to which the SQL file will be written. If the folder does not exist when you run the Oracle Database Metadata Generator, it will be created. For more information about the output files, refer to About the Output Files.

The following text shows a sample metadata input file:

BUSINESS_MODEL= "Paint"

PHYSICAL_DATABASE ="SQL_Paint"

RUN_AS_USER = "Administrator"

OUTPUT_FOLDER = "C:\OracleBI"

About the Output Files

Each Generator creates different types of output files, as described in the following list:

  • Oracle Database Metadata Generator: Generates a SQL file that is encoded in UTF8 and stored in the specified output folder. The file name includes the name of the business model in the Cube Model, such as my_business_model.sql.
  • DB2 Cube Views Generator: Generates the following files in the specified output folder:
    • XML files (encoded in UTF8). One XML file is created for each specified business model. It contains all objects that were converted to cubes. Additionally, objects in the repository will be mapped to similar objects in the IBM Cube Views metadata. For a list of objects that will not be converted, refer to Metadata Conversion Rules and Error Messages.

      The name of the XML file will match the business model name (without spaces), followed by the XML extension. For example, SalesResults.xml. The following is the syntax of the XML file name:

    [BusinessModelNameWithNoSpaces].xml

    • A SQL file that contains the alias generation DLL. A SQL file is created for each specified business model only if aliases exist in the physical layer databases that are referenced in the specified business model. The alias file contains SQL commands that will create the aliases in the DB2 database. The name of the SQL file will match the business model name (without spaces), followed by the SQL extension. For example, SalesResults-alias.sql. The following is the syntax of the alias-SQL file name:

    [BusinessModelNameWithNoSpaces]-alias.sql

Troubleshooting Errors from the Generator

Error messages indicate that the Generator was unable to complete some or all of its tasks. After starting the Generator, you might observe the following error messages:

  • Unable to write to Log file: <log_file_name>.

    The log file specified in the NQSConfig.INI file might contain the wrong path, the user might not have write permissions to that folder, or the disk could be out-of-space.

  • Run_as_user, <user_name>, is invalid.

    The user name is incorrect.

  • Repository, <repository_name>, is invalid or corrupt.

    The repository name might be incorrect, it might not exist in the given path, or the user might not have permission to read it.

  • Physical Database, <database_name>, is invalid.

    The physical database name is incorrect.

  • Business Model, <model_name>, is invalid.

    The business model name is correct.

  • Authentication information provided is invalid.

    The specified username or password is incorrect.

  • Path: "<path_name>" is invalid.

    The path or file name is incorrect.

Metadata Conversion Rules and Error Messages

When the Generator creates the output files, it also maps the metadata objects in the Oracle BI repository to similar objects in the metadata of the Oracle Database or the IBM DB2 Database.

This section explains the rules used to identify Oracle BI metadata that cannot be translated (converted) into either SQL or XML format. These rules are necessary because the Oracle Database and IBM Cube Views do not support some of the metadata constructs that are allowed by Oracle BI.

Dimensional metadata in the SQL or XML file will be generated at the logical fact table source level. If a logical fact table source has an invalid logical dimension table source, then the logical dimension table source will be invalidated. If the logical fact table source is invalid, then all the logical dimension table sources that are linked to it will also be invalidated. Invalid Oracle BI repository metadata elements will not be converted to cubes in the SQL or XML file.

When a rule is violated, the Generator writes the error messages and the metadata that violated the rule to a log file. You specify the name of this log file in the LOG_FILE_NAME parameter in the NQSConfig.INI file. For information about parameters in the Cube Views section of the NQSConfig.INI file, refer to Oracle Business Intelligence Infrastructure Installation and Configuration Guide.

Conversion Rules for Oracle Database

The following list provides the rules for converting Oracle BI metadata into objects in the Oracle Database:

  • Attributes that contain expressions in the logical table cannot be exported.
  • Tables joined using complex joins are not considered.
  • Tables that are opaque views are not considered.
  • Columns used as part of a key in one level cannot be used as part of another level key. Consider the example that is shown in the following figure of a repository that contains a dimension called Source A and a dimension called Source B. The Source A dimension shares the T1 table with Source B. The T24Key is used in both the join to the T2 table (T24Key and T2Key) and in the join to table T4 (T24Key and T4Key).

    The Oracle Database prohibits the use of columns as keys in multiple levels. This prohibition requires the Oracle Database Metadata Generator to eliminate one of the two joins, usually the join that is encountered first. Therefore, because the T1-T4 join was encountered first, the joins to T2 and T3 are lost, which prevents the Attr2 and Attr3 attributes in tables T2 and T3 from being exported.
Conversion Rules for IBM DB2 Database

Table 32 lists the rules used to validate Oracle BI repository metadata elements, error messages that are written to the log file if the rule is violated, and an explanation of what caused the rule violation. The error messages help you determine why a particular Oracle BI metadata object was not exported to the XML file.

Table 32. Validation Rules for Metadata Elements
Rule
Message
Explanation

ComplexJoinFactsRule

[Fact Logical Table Source]Complex Physical Joins not supported

%qn has a complex Join %qn between Physical Tables %qn and %qn

If the physical fact tables are connected through complex joins, the join is not supported. A complex join is defined as any join between two tables that do not have a foreign key relationship.

ComplexJoinDimsRule

[Dimension Logical Table Source]Complex Physical Joins not supported

%qn has a complex Join %qn between Physical Tables %qn and %qn

If the dimension physical tables are connected through a complex join, then that join is not supported.

ComplexJoinFactDimRule

[Fact Logical Table Source -> Dimension Logical Table Source] Complex Physical Joins not supported.

%qn has a complex Join %qn between Physical Tables %qn and %qn.

If a dimension physical table and a fact physical table are connected through a complex join, that join is not supported and the dimension table source is invalidated.

OpaqueViewFactRule

[Fact Logical table Source] Physical SQL Select Statements not supported.

%qn uses the SQL Select Statement %qn.

When the physical fact table is generated by a SQL select statement, the logical fact table source that contains the table is invalidated. All logical dimension table sources connected to this logical fact table source are also invalidated. This construct allows subquery processing.

OpaqueViewDimRule

[Dimension Logical table Source] Physical SQL Select Statements not supported.

%qn uses the SQL Select Statement %qn.

When a physical dimension table is generated by a SQL select statement, the logical dimension table source containing that table is invalidated.

OuterJoinFactRule

[Fact Logical Table Source] Physical Outer Joins not supported.

%qn has an outer join %qn between physical tables %qn and %qn.

If the logical fact table source has an outer join linkage, then that logical fact table source is invalidated and all logical dimension table sources linked to this source will also be invalidated.

OuterJoinDimRule

[Dimension Logical Table Source] Physical Outer Joins not supported.

%qn has an outer join %qn between physical tables %qn and %qn.

If the logical dimension table source has an outer join linkage, that logical dimension table source is invalidated.

WhereClauseFactRule

[Fact Logical Table Source] WHERE clauses are not supported.

%qn has a where condition %s.

If the fact table source uses a WHERE clause to filter the data that is loaded, then this table source is invalidated.

WhereClauseDimRule

[Dimension Logical Table Source] WHERE clauses are not supported.

%qn has a where condition %s.

If the dimension table source uses a WHERE clause to filter the data that is loaded, this table source is invalidated.

TwoJoinFactDimRule

[Fact Logical Table Source -> Dimension Logical Table Source] Multiple Joins between sources not supported.

%qn and %qn have at least the following joins : %qn, %qn.

If a physical fact table is linked to two dimension tables from the same dimension source (if the fact table is not exclusively linked to the most detailed table in the table source), the dimension table source is invalidated.

HiddenManyManyRule

[Fact Logical Table Source -> Dimension Logical Table Source] Join between (physical or logical?) fact and dimension is not on the most detailed table.

%qn between %qn and %qn is not on the most detailed table %qn {Join name, facttable, dimtable).

This is related to the TwoJoinFactDimRule. If the fact table is joined to a dimension table that is not the most detailed table in the table source, the dimension table source is invalidated.

ComplexMeasureRule

[Column] Complex Aggregation Rules not supported.

%qn uses an aggregation rule of %s which is not supported.

The supported aggregations are SUM, COUNT, AVG, MIN, MAX, STDDEV, COUNT-DISTINCT, and COUNT.

CountDistMeasureRule

[Column] COUNT-DISTINCT Aggregation Rule not supported.

%qn uses an aggregation rule of %s which is not supported.

COUNT-DISTINCT aggregation is not supported.

InvalidColumnLevelRule

[Level] Some columns that are part of the Primary Level Key are invalid.

%qn has %qn as part of its primary key, when %qn has already been marked invalid.

COUNT-DISTINCT aggregation is not supported.

VariableBasedColumnRule

[Logical Table Source -> Column] Column uses a Variable in the Expression

Column %qn uses a variable in its mapping.

COUNT-DISTINCT aggregation is not supported. The logical column uses repository and session variables in the expression.

OneFactToManyDimRule

[Fact Logical Table Source -> Dimension Logical Table Source] There must be a unique join path between the most detailed tables in the (logical or physical?) fact and the dimension.

No join paths found between %qn and %qn (both physical table names).

Found at least the following join paths: (%qn->%qn....), (%qn->%qn....)

Same as in TwoJoinFactDimRule or HiddenManyManyRule.

ManyMDTinFactRule

[Fact Logical Table Source] Fact Logical Table Source must have a unique most detailed table.

%qn has at least the following most detailed tables : %qn,%qn.

A fact that has more than one table that is the most detailed table.

NoMeasureFactRule

[Fact Logical Table Source] Fact Logical Table Source does not have any Measures.

%qn does not have any deployable measures.

A fact table does not have any measures because all the measures have been invalidated.

NoInActiveFactRule

[Fact Logical Table Source] Fact Logical Table Source is not marked Active.

A fact source is not active.

NoInActiveDimRule

[Dimension Logical Table Source] Dimension Logical Table Source is not marked Active.

A dimension source is not active.

NoAttributeInFactRule

[Fact Logical Table Source -> Column] Attribute found in Fact.

%qn in a fact source %qn does not have an aggregation rule.

No attributes in the fact source.

NoMeasureInDimRule

[Dimension Logical Table Source -> Column] Measure found in Dimension.

%qn in a dimension source %qn has an aggregation rule.

No measures in the dimension source.

VisibleColumnsAttrRule

[Column] -> The run_as_user does not have visibility to this Logical Column.

%qn is not accessible to the run_as_user %qn due to visibility rules.

A column does not have visibility for this user.

VisibleColumnsMeasRule

[Column] -> The run_as_user does not have visibility to this Logical Column.

%qn is not accessible to the run_as_user %qn due to visibility rules.

A column does not have visibility for this user.

MultiplePrimaryKeysDimRule

[Dimension Logical Table Source] A Join uses an alternate key in the Dimension Logical Table Source.

%qn between %qn and %qn in %qn uses the alternate key %qn.

A dimension physical table can contain only one primary key. It is joined to another dimension physical table using a different unique key and that join is invalid.

IBM Cube Views does not accept any unique keys to be used for foreign joins and always requires the primary key.

MultiplePrimaryKeysFactRule

[Dimension Logical Table Source] A Join uses an alternate key in the Dimension Logical Table Source.

%qn between %qn and %qn in %qn uses the alternate key %qn.

A fact physical table can contain only one primary key. It is joined to another fact physical table using a different unique key and that join is invalid.

IBM Cube Views does not accept any unique keys to be used for foreign joins and always requires the primary key.

MultiplePrimaryKeysFactDimRule

[Fact Logical Table Source -> Dim Logical Table Source] A Join uses an alternate key between the Logical Table sources.

%qn between %qn and %qn for sources %qn and %qn uses the alternate key %qn.

A fact physical table can contain only one primary key. It is joined to a dimension physical table using a different unique key and is invalid.

IBM Cube Views does not accept any unique keys to be used for foreign joins and always requires the primary key.

NotDB2ExpressionAttrRule

[Dimension Logical Table Source -> Column] The Column contains an Expression not supported.

%qn has expression %s which is not supported.

The attribute contains an expression not supported by IBM Cube Views.

This includes metadata expressions that use DateTime functions (for example, CURRENT_DATE).

NotDB2ExpressionMeasRule

[Fact Logical Table Source -> Column] The Column contains an Expression not supported.

%qn has expression %s which is not supported.

A measure contains an expression not supported by IBM Cube Views.

This includes metadata expressions that use DateTime functions (for example,. CURRENT_DATE).

NoAttributeDimRule

[Dimension Logical Table Source] Dimension Logical Table Source does not have any attributes visible to the run_as_user.

%qn can not be queried by user %qn since none of its attributes are visible.

A dimension does not have any attributes.
  
Oracle® Business Intelligence Server Administration Guide Copyright © 2007, Oracle. All rights reserved.