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.
This article is done on a Oracle Database.
You can use this feature with Oracle Database 9i and higher.
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.
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