About
sametaexport is an utility to exchange metadata with DB2 or Oracle Database (Dimension with its Hierarchy) to pre-aggregate the relational data and improve query performance.
For DB2, the utility:
- is called the DB2 Cube Views Generator.
- create materialized query tables (MQTs) through the IBM DB2 Cube Views tool.
For Oracle Database, the utility:
- is called the Oracle Database Metadata Generator.
- convert the OBI Hierarchy and Dimension into a SQL file that contains PL/SQL commands to generate dimensions in the Oracle Database that are used by the SQL Access Advisor to creates materialized views and index recommendations on optimizing performance.
This article is done on a Oracle Database.
You can use this feature with Oracle Database 9i and higher.
Articles Related
Steps
Generating and executing the Import File
The sametaexport is invoked from the command line.
You must run it in a bi-init windows.
sametaexport -r "PathAndRepositoryFileName" [-p repository_password] -f "InputFileNameAndPath" [options]
sample metadata input file with the repository of the sample application 11g:
BUSINESS_MODEL = "1 - Sample App"
PHYSICAL_DATABASE = "01 - Sample App Data (ORCL)"
RUN_AS_USER = "weblogic"
OUTPUT_FOLDER = "C:\Temp"
sametaexport -r "SampleApp.rpd" -p Admin123 -f "C:\Temp\InputFile.txt"
Warning : Database Type not specified! Will determine from the Repository.
Database Type determined as "ORACLE".
Successfully created Metadata Bridge.
In the output folder, you can find a SQL file that is based on the name of the business model you specified in the input file (1_-_Sample_App.sql). It contains all the “create dimension” statement.
When a rule is violated, the Generator writes the error messages and the metadata that violated the rule to the log file.
CREATE DIMENSION BISAMPLE.D_SAMP_TIME_DAY_D
LEVEL L_Calendar_Date80CAB6D1 IS (SAMP_TIME_DAY_D.Calendar_Date)
LEVEL L_Per_Name_Week80CAB6D0 IS (SAMP_TIME_DAY_D.Per_Name_Week)
LEVEL L_Per_Name_Month80CAB6CF IS (SAMP_TIME_DAY_D.Per_Name_Month)
LEVEL L_Per_Name_Qtr80CAB6CE IS (SAMP_TIME_DAY_D.Per_Name_Qtr)
LEVEL L_Per_Name_Half80CAB6CD IS (SAMP_TIME_DAY_D.Per_Name_Half)
LEVEL L_Per_Name_Year80CAB6CC IS (SAMP_TIME_DAY_D.Per_Name_Year)
LEVEL L_T41_Fscl_Week80CAB6D6 IS (SAMP_TIME_DAY_D.Fscl_Week)
HIERARCHY H_Hier1 (
L_Calendar_Date80CAB6D1 CHILD OF
L_Per_Name_Week80CAB6D0 CHILD OF
L_Per_Name_Month80CAB6CF CHILD OF
L_Per_Name_Qtr80CAB6CE CHILD OF
L_Per_Name_Half80CAB6CD CHILD OF
L_Per_Name_Year80CAB6CC)
HIERARCHY H_Hier2 (
L_Calendar_Date80CAB6D1 CHILD OF
L_T41_Fscl_Week80CAB6D6 CHILD OF
L_Per_Name_Year80CAB6CC)
ATTRIBUTE L_Calendar_Date80CAB6D1 DETERMINES .......
bisample@db11gr2>@1_-_Sample_App.sql
Dimension created.
Dimension created.
Dimension created.
Dimension created.
Dimension created.
Dimension created.
Dimension created.
When the script executes successfully, you can see the dimensions that were created by using the database web console or the Oracle Enterprise Manager Database Control. In the Oracle Enterprise Manager Database Control, expand the following nodes: Network, Databases, database-name, Warehouse, Summary Management, Dimensions, System.
Next Steps
The following next steps can be found in the documentation. Deploying Metadata for Oracle Database
- Defining Constraints for the Existence of Joins
- Creating the Query Workload
- Creating Materialized Views