Generating the Import File

The Oracle Database Metadata Generator and the DB2 Cube Views Generator create the files needed to import metadata from the Oracle BI Server into the SQL Access Advisor or an IBM DB2 database.

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.

The sametaexport utility is available on both Windows and UNIX systems. However, you can only use sametaexport with binary repositories in RPD format.

The location of the sametaexport utility is:

ORACLE_HOME/bi/bifoundation/server/bin

sametaexport -r "PathAndRepositoryFileName" [-p repository_password]
-f "InputFileNameAndPath" [options]

The table contains descriptions of the parameters in the command-line executable file.

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.
-p	Repository password	The password for the given repository. The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release. For scripting purposes, you can pass the password through standard input.
-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. See About the Metadata Input File.

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.

-p

Repository password

The password for the given repository.

The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release. For scripting purposes, you can pass the password through standard input.

-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. See About the Metadata Input File.

You can include some additional parameters in the input file or at the command line to change various defaults for the Oracle Database Metadata Generator and the DB2 Cube Views Generator. Parameters specified in the input file take precedence over parameters specified at the command line. You must include these parameters only if you want to change the default values.

The tables describe these optional parameters.

Parameter Definition	Additional Information	Input File Usage Example	Command Line Usage Example
Use schema name from RPD	When set to `YES`, the table schema names are used as they are used in the repository. The default value is `YES`.	USE_SCHEMA_NAME_FROM_RPD = NO	-schemafrom rpd NO
Default schema name	The default schema name is used as the table schema name if the value of `-schemafromrpd` is set to `NO`, or if the repository schema name cannot be determined. The default value is `SIEBEL`.	DEFAULT_SCHEMA_NAME = ORACLE	-defaultschema ORACLE
Oracle schema name	The metadata from Oracle Database Metadata Generator is created under this schema. The default value is `SIEBEL`.	ORA_DIM_SCHEMA_NAME = ORACLE	-orclschema ORACLE
Logging enabled	Indicates whether to keep a log of the metadata export process. Valid values are `ON`, `OFF`, and `DEBUG`. The default value is `ON`.	LOGGING = DEBUG	-logging DEBUG
Log file name	The path to the log file. If you provide an invalid path, an error occurs. If you do not provide this parameter, the default log file path is used. The default path is: ORACLE_INSTANCE\diagnostics\logs\ OracleBIServerComponent\ coreapplication_obisn\ OraDimExp.log	`LOG_FILE_NAME = C:\bea_default\instances\instance1\diagnostics\ logs\generator\logfile.log`	`-logfile C:\bea_default\instances\instance1\diagnostics\logs\generator\logfile.log`

Parameter Definition	Additional Information	Input File Usage Example	Command Line Usage Example
Distinct count supported	When set to `YES`, allows measure containing the `DISTINCT_COUNT` aggregation to be exported. The recommended setting and default value is `NO`.	DISTINCT_COUNT_SUPPORTED = YES	-distinct YES
Statistical functions supported	When set to `YES`, allows measures containing the aggregation `STDDEV` to be exported. The recommended setting and default value is `NO`.	STATISTICAL_FUNCTIONS_SUPPORTED = YES	-stat YES
Use schema name	When set to `YES`, the Cube Views metadata attributes have columns from tables under a schema name, which are then specified in the parameters. When set to `NO`, the schema names for these tables are empty. The default value is `YES`.	USE_SCHEMA_NAME = NO	-useschema NO
Use schema name from RPD	When set to `YES`, the table schema names are used as they are used in the repository. The default value is `YES`.	USE_SCHEMA_NAME_FROM_RPD = NO	-schemafromrpd NO
Default schema name	The default schema name is used as the table schema name if the value of `-schemafromrpd` is set to `NO`, or if the repository schema name cannot be determined. The default value is `SIEBEL`.	DEFAULT_SCHEMA_NAME = ORACLE	-defaultschema ORACLE
Cube views schema name	The name of the schema under which the Cube Views metadata is created. The default value is `SIEBEL`.	CUBE_VIEWS_SCHEMA_NAME = ORACLE	-cubeschema ORACLE
Log file name	The path to the log file. If you provide an invalid path, an error occurs. If you do not provide this parameter, the default log file path is used. The default path is: ORACLE_INSTANCE\diagnostics\logs\ OracleBIServerComponent\ coreapplication_obisn\ CubeViews.log	LOG_FILE_NAME = C:\bea_default\instances\instance1\diagnostics\logs\generator\logfile.log	-logfile C:\bea_default\instances\instance1\diagnostics\logs\generator\logfile.log
Log failures	When set to `YES`, the log file lists the metadata that was invalidated under a certain rule. The default value is `YES`.	LOG_FAILURES = NO	-logfail NO
Log success	When set to `YES`, the log file lists the metadata that has been checked under each rule and has passed the check. The default value is `NO`.	LOG_SUCCESS = YES	-logsuccess YES

About the Metadata Input File

The table describes the parameters in the metadata input file.

The input file is a text file that contains the parameters that are described in the following table.

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 to export. If the business model is not found in the repository, then an error message is displayed. You can only specify one business model name in the input file. To generate metadata for multiple business models, create another input file and run the Oracle Database Metadata Generator or DB2 Cube Views Generator again.
PHYSICAL_DATABASE	The name of the database in the physical layer of the Oracle BI repository that contains the metadata to export. When the business model derives from multiple databases, 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 database user whose visibility must be duplicated for the metadata export. This parameter cannot be empty. This user must exist as a user reference in the repository.
OUTPUT_FOLDER	Specifies the full path and file name of the folder where the SQL file placed. If the folder does not exist when you run the Oracle Database Metadata Generator, then the folder is created in the process. See About the Output Files.

The following text shows a sample metadata input file:

BUSINESS_MODEL = "1 - Sample App"
PHYSICAL_DATABASE = "1 - Sample App Data"
RUN_AS_USER = "Administrator"
OUTPUT_FOLDER = "C:\OracleBI"

About the Output Files

Each Generator creates different types of output files.

The following list describes the output files:

The Oracle Database Metadata Generator creates a SQL file that is encoded in UTF-8 and stored in the specified output folder. The file name is based on the name of the business model you specified in the input file, such as my_business_model.sql.
The DB2 Cube Views Generator creates the following files in the specified output folder:
- XML file (encoded in UTF8). One XML file is created for the specified business model. The file contains the objects that were converted to cubes. Objects in the repository are mapped to similar objects in the IBM Cube Views metadata. See Conversion Rules for IBM DB2 Databases for a list of objects that are not converted.
  
  The name of the XML file matches the business model name, without spaces, followed by the XML extension, for example, SalesResults.xml.
- A SQL file that contains the alias generation DLL. The SQL file is created for the specified business model only if aliases exist in the physical layer databases that are referenced in the business model. The alias file contains SQL commands that create the aliases in the DB2 database. The name of the SQL file matches the business model name, without spaces, followed by the SQL extension, for example, SalesResults‐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 input file or at the command line 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.rpd, 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 does not match a valid physical database object in the repository.
Business Model, model_name, is invalid.

The business model name does not match a valid business model object in the repository.
Authentication information provided is invalid.

The repository password provided at the command line is incorrect.
Path: "path_name" is invalid.

The path or file name is incorrect, or the current user does not have read access.

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 Business Intelligence metadata that cannot be translated (converted) into either SQL or XML format. These rules are necessary because Oracle Database and IBM Cube Views do not support some of the metadata constructs that are allowed by Oracle Business Intelligence.

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 mapped to it will also be invalidated. Invalid Oracle Business Intelligence 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 the log file.

Conversion Rules for Oracle Databases

Learn the rules for converting Oracle BI metadata into objects.

The following list provides the rules for converting Oracle Business Intelligence 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.

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, the other joins are lost, which prevents them from being exported.

Conversion Rules for IBM DB2 Databases

Learn the rules used to validate the Oracle BI repository metadata elements, and other information.

The following table 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 Business Intelligence metadata object was not exported to the XML file.

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, then 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, then 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 mapping, then that logical fact table source is invalidated and all logical dimension table sources mapped 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 mapping, then 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, then 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 mapped to two dimension tables from the same dimension source (if the fact table is not exclusively mapped to the most detailed table in the table source), then 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, then 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 typically `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`, `STDDEV`, `COUNTDISTINCT`, and `COUNT`.
CountDistMeasureRule	[Column] COUNT-DISTINCT Aggregation Rule not supported. %qn uses an aggregation rule of %s which is not supported.	`COUNTDISTINCT` aggregation is not supported for this particular column.
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.	The level key for this level has one or more columns that are invalid.
VariableBasedColumnRule	[Logical Table Source -> Column] Column uses a Variable in the Expression Column %qn uses a variable in its mapping.	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 that is not supported. %qn has expression %s that 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.