Scripting Entries in the Oracle BI Repository

Mark Rittman

Oct 27, 2007 • 11 min read

So I was reading through Dylan's blog the other day and noticed an article he'd written about Oracle BI Administrator scripting. The article looked particularly interesting as a couple of people had recently asked me whether it was possible to script the creation of objects in the Oracle BI Server repository, and this looked like it met the requirement. So how does it work?

Firstly, if you select any object, or combination of objects, in the Oracle BI Administration repository view, you can right-click on them and select Copy. If you then select Paste, you can copy an object from one part of the repository to another. If you open up Notepad though and paste the results in to there, you actually get a DDL-like script that seems to define the object(s) that you've selected.

To take an example, if you right-click on a dimension table in the logical layer, like this:

and paste the results into Notepad, you get this:

DECLARE LOGICAL TABLE "Seminar"."Product" AS "Product" UPGRADE ID 26062
	KEYS (
		 "Seminar"."Product"."PRODUCT_PK" )
	SOURCES (
		 "Seminar"."Product"."Product_dw" ) NO INTERSECTION DIAGRAM POSITION (463, 66) ATTRIBUTES
	(
		 "Seminar"."Product"."Id",
		 "Seminar"."Product"."Prodid",
		 "Seminar"."Product"."Name",
		 "Seminar"."Product"."Description",
		 "Seminar"."Product"."Category",
		 "Seminar"."Product"."List_price",
		 "Seminar"."Product"."Producer" )
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Id" AS "Id" UPGRADE ID 26092
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Id"."AD_3900:197681193399951" AS "AD_3900:197681193399951" UPGRADE ID 27358 BETWEEN ATTRIBUTE  "Seminar"."Product"."Id" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."ID"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Prodid" AS "Prodid" UPGRADE ID 26093
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Prodid"."AD_3900:197691193399951" AS "AD_3900:197691193399951" UPGRADE ID 27360 BETWEEN ATTRIBUTE  "Seminar"."Product"."Prodid" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."PRODID"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Name" AS "Name" UPGRADE ID 26094
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Name"."AD_3900:197701193399951" AS "AD_3900:197701193399951" UPGRADE ID 27362 BETWEEN ATTRIBUTE  "Seminar"."Product"."Name" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."NAME"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Description" AS "Description" UPGRADE ID 26095
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Description"."AD_3900:197711193399951" AS "AD_3900:197711193399951" UPGRADE ID 27364 BETWEEN ATTRIBUTE  "Seminar"."Product"."Description" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."DESCRIPTION"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Category" AS "Category" UPGRADE ID 26096
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Category"."AD_3900:197721193399951" AS "AD_3900:197721193399951" UPGRADE ID 27366 BETWEEN ATTRIBUTE  "Seminar"."Product"."Category" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."CATEGORY"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."List_price" AS "List_price" UPGRADE ID 26097
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."List_price"."AD_3900:197731193399951" AS "AD_3900:197731193399951" UPGRADE ID 27368 BETWEEN ATTRIBUTE  "Seminar"."Product"."List_price" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."LIST_PRICE"}
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Producer" AS "Producer" UPGRADE ID 26098
	PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Producer"."AD_3900:197741193399951" AS "AD_3900:197741193399951" UPGRADE ID 27370 BETWEEN ATTRIBUTE  "Seminar"."Product"."Producer" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."PRODUCER"}
	PRIVILEGES ( READ);
DECLARE LOGICAL KEY "Seminar"."Product"."PRODUCT_PK" AS "PRODUCT_PK" UPGRADE ID 26137 HAVING
	(
		 "Seminar"."Product"."Prodid" ) PRIMARY KEY
	PRIVILEGES ( READ);
DECLARE LOGICAL TABLE SOURCE "Seminar"."Product"."Product_dw" AS "Product_dw" UPGRADE ID 26198
	PROJECT DISTINCT (
		 {"Seminar"."Product"."Id"}  AS {"custdw".""."CUSTDW"."PRODUCT"."ID"} ,
		 {"Seminar"."Product"."Prodid"}  AS {"custdw".""."CUSTDW"."PRODUCT"."PRODID"} ,
		 {"Seminar"."Product"."Name"}  AS {"custdw".""."CUSTDW"."PRODUCT"."NAME"} ,
		 {"Seminar"."Product"."Description"}  AS {"custdw".""."CUSTDW"."PRODUCT"."DESCRIPTION"} ,
		 {"Seminar"."Product"."Category"}  AS {"custdw".""."CUSTDW"."PRODUCT"."CATEGORY"} ,
		 {"Seminar"."Product"."List_price"}  AS {"custdw".""."CUSTDW"."PRODUCT"."LIST_PRICE"} ,
		 {"Seminar"."Product"."Producer"}  AS {"custdw".""."CUSTDW"."PRODUCT"."PRODUCER"}  )
	FROM
	(
		( "custdw".."CUSTDW"."PRODUCT"))
	PRIVILEGES ( READ);
DECLARE LOGICAL SOURCE FOLDER "Seminar"."Product"."Sources" AS "Sources" UPGRADE ID 26066
	PRIVILEGES ( READ);

That's interesting. If you do the same for a fact table, you get this:

DECLARE LOGICAL TABLE "Seminar"."Order Line Items Fact" AS "Order Line Items Fact" UPGRADE ID 26060 KEYS ( "Seminar"."Order Line Items Fact"."ITEMS_PK" ) SOURCES ( "Seminar"."Order Line Items Fact"."Items_dw" ) NO INTERSECTION DIAGRAM POSITION (311, 166) ATTRIBUTES ( "Seminar"."Order Line Items Fact"."Itemid", "Seminar"."Order Line Items Fact"."Prodid", "Seminar"."Order Line Items Fact"."Price", "Seminar"."Order Line Items Fact"."Quantity", "Seminar"."Order Line Items Fact"."Ordid", "Seminar"."Order Line Items Fact"."Total_price", "Seminar"."Order Line Items Fact"."Comments", "Seminar"."Order Line Items Fact"."Custid", "Seminar"."Order Line Items Fact"."Status", "Seminar"."Order Line Items Fact"."Orderdate" ) PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Itemid" AS "Itemid" UPGRADE ID 26068 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Itemid"."AD_3900:197371193399951" AS "AD_3900:197371193399951" UPGRADE ID 27296 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Itemid" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."ITEMID"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Prodid" AS "Prodid" UPGRADE ID 26069 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Prodid"."AD_3900:197381193399951" AS "AD_3900:197381193399951" UPGRADE ID 27298 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Prodid" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."PRODID"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Price" AS "Price" UPGRADE ID 26070 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Price"."AD_3900:197391193399951" AS "AD_3900:197391193399951" UPGRADE ID 27300 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Price" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."PRICE"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Quantity" AS "Quantity" UPGRADE ID 26071 PRIVILEGES ( READ); DECLARE AGGREGATE MEASURE "Seminar"."Order Line Items Fact"."Quantity"."Measure" AS "Measure" UPGRADE ID 26115 RULES( "Seminar"."Order Line Items Fact"."Quantity"."Measure"."AggRule_2050:196761193399951" ) PRIVILEGES ( READ); DECLARE AGGREGATE RULE "Seminar"."Order Line Items Fact"."Quantity"."Measure"."AggRule_2050:196761193399951" AS "AggRule_2050:196761193399951" UPGRADE ID 27269 EXPRESSION { SUM("Seminar"."Order Line Items Fact"."Quantity")} PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Quantity"."AD_3900:197401193399951" AS "AD_3900:197401193399951" UPGRADE ID 27302 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Quantity" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."QUANTITY"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Ordid" AS "Ordid" UPGRADE ID 26072 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Ordid"."AD_3900:197411193399951" AS "AD_3900:197411193399951" UPGRADE ID 27304 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Ordid" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."ORDID"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Total_price" AS "Total_price" UPGRADE ID 26073 PRIVILEGES ( READ); DECLARE AGGREGATE MEASURE "Seminar"."Order Line Items Fact"."Total_price"."Measure" AS "Measure" UPGRADE ID 26116 RULES( "Seminar"."Order Line Items Fact"."Total_price"."Measure"."AggRule_2050:196771193399951" ) PRIVILEGES ( READ); DECLARE AGGREGATE RULE "Seminar"."Order Line Items Fact"."Total_price"."Measure"."AggRule_2050:196771193399951" AS "AggRule_2050:196771193399951" UPGRADE ID 27271 EXPRESSION { AVG("Seminar"."Order Line Items Fact"."Total_price")} PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Total_price"."AD_3900:197421193399951" AS "AD_3900:197421193399951" UPGRADE ID 27306 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Total_price" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."TOTAL_PRICE"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Comments" AS "Comments" UPGRADE ID 26074 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Comments"."AD_3900:197431193399951" AS "AD_3900:197431193399951" UPGRADE ID 27308 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Comments" AND EXPRESSION {"custdw".""."CUSTDW"."ORDERS"."COMMENTS"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Custid" AS "Custid" UPGRADE ID 26075 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Custid"."AD_3900:197441193399951" AS "AD_3900:197441193399951" UPGRADE ID 27310 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Custid" AND EXPRESSION {"custdw".""."CUSTDW"."ORDERS"."CUSTID"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Status" AS "Status" UPGRADE ID 26076 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Status"."AD_3900:197451193399951" AS "AD_3900:197451193399951" UPGRADE ID 27312 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Status" AND EXPRESSION {"custdw".""."CUSTDW"."ORDERS"."STATUS"} PRIVILEGES ( READ); DECLARE ATTRIBUTE "Seminar"."Order Line Items Fact"."Orderdate" AS "Orderdate" UPGRADE ID 26110 PRIVILEGES ( READ); DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Orderdate"."AD_3900:197461193399951" AS "AD_3900:197461193399951" UPGRADE ID 27314 BETWEEN ATTRIBUTE "Seminar"."Order Line Items Fact"."Orderdate" AND EXPRESSION {"custdw".""."CUSTDW"."ORDERS"."ORDERDATE"} PRIVILEGES ( READ); DECLARE LOGICAL KEY "Seminar"."Order Line Items Fact"."ITEMS_PK" AS "ITEMS_PK" UPGRADE ID 26135 HAVING ( "Seminar"."Order Line Items Fact"."Ordid", "Seminar"."Order Line Items Fact"."Itemid" ) PRIMARY KEY PRIVILEGES ( READ); DECLARE LOGICAL TABLE SOURCE "Seminar"."Order Line Items Fact"."Items_dw" AS "Items_dw" UPGRADE ID 26196 PROJECT ( {"Seminar"."Order Line Items Fact"."Itemid"} AS {"custdw".""."CUSTDW"."ITEMS"."ITEMID"} , {"Seminar"."Order Line Items Fact"."Prodid"} AS {"custdw".""."CUSTDW"."ITEMS"."PRODID"} , {"Seminar"."Order Line Items Fact"."Price"} AS {"custdw".""."CUSTDW"."ITEMS"."PRICE"} , {"Seminar"."Order Line Items Fact"."Quantity"} AS {"custdw".""."CUSTDW"."ITEMS"."QUANTITY"} , {"Seminar"."Order Line Items Fact"."Ordid"} AS {"custdw".""."CUSTDW"."ITEMS"."ORDID"} , {"Seminar"."Order Line Items Fact"."Total_price"} AS {"custdw".""."CUSTDW"."ITEMS"."TOTAL_PRICE"} , {"Seminar"."Order Line Items Fact"."Comments"} AS {"custdw".""."CUSTDW"."ORDERS"."COMMENTS"} , {"Seminar"."Order Line Items Fact"."Custid"} AS {"custdw".""."CUSTDW"."ORDERS"."CUSTID"} , {"Seminar"."Order Line Items Fact"."Status"} AS {"custdw".""."CUSTDW"."ORDERS"."STATUS"} , {"Seminar"."Order Line Items Fact"."Orderdate"} AS {"custdw".""."CUSTDW"."ORDERS"."ORDERDATE"} ) FROM ( ( "custdw".."CUSTDW"."ITEMS" ( "custdw".."CUSTDW"."ITEMS", "custdw".."CUSTDW"."ORDERS", "custdw".."CUSTDW"."ITEMS"."ITEMS_FKey"

		   MANY TO ONE)),
	( "custdw".."CUSTDW"."ORDERS"
		( "custdw".."CUSTDW"."ORDERS",  "custdw".."CUSTDW"."ITEMS",  "custdw".."CUSTDW"."ITEMS"."ITEMS_FKey"

		   ONE TO MANY)))
FRAGMENT CONTENT {"Seminar"."Order Line Items Fact"."Orderdate" >= '01-jan-2007' AND "Seminar"."Order Line Items Fact"."Orderdate" < '01-jun-2007'}
PRIVILEGES ( READ);

DECLARE LOGICAL SOURCE FOLDER "Seminar"."Order Line Items Fact"."Sources" AS "Sources" UPGRADE ID 26064
PRIVILEGES ( READ);

If you're familiar with the OMB language that's used in Oracle Warehouse Builder, or OLAP DML, or even Oracle DML, this will look familiar - it's a declarative language used for defining objects, in this instance logical tables in the Oracle BI EE repository.

What if we do the same for a dimension?

DECLARE DIMENSION "Seminar"."Productdim" AS "Productdim" UPGRADE ID 26059 ON
	(
		  "Seminar"."Productdim"."All Products" ) DEFAULT ROOT  "Seminar"."Productdim"."All Products"
	PRIVILEGES ( READ);
DECLARE LEVEL "Seminar"."Productdim"."All Products" AS "All Products" UPGRADE ID 26129 GRAND TOTAL ALIAS FULL DRILL UP COVERAGE CONSTANT 'All Products'
	PRIVILEGES ( READ);
DECLARE LEVEL "Seminar"."Productdim"."Category" AS "Category" UPGRADE ID 26130
	 HAVING (
		   "Seminar"."Product"."Category" ) FULL DRILL UP COVERAGE
	PARENTS (
		 "Seminar"."Productdim"."All Products" DEFAULT ) CONSTANT 'Category'
	PRIVILEGES ( READ);
DECLARE LOGICAL KEY "Seminar"."Productdim"."Category"."CATEGORY" AS "CATEGORY" UPGRADE ID 26147 HAVING
	(
		 "Seminar"."Product"."Category" ) PRIMARY KEY
	PRIVILEGES ( READ);
DECLARE LEVEL "Seminar"."Productdim"."Product" AS "Product" UPGRADE ID 26131
	 HAVING (
		   "Seminar"."Product"."Name" ) FULL DRILL UP COVERAGE
	PARENTS (
		 "Seminar"."Productdim"."Category" DEFAULT ) CONSTANT 'Product'
	PRIVILEGES ( READ);
DECLARE LOGICAL KEY "Seminar"."Productdim"."Product"."NAME" AS "NAME" UPGRADE ID 26148 HAVING
	(
		 "Seminar"."Product"."Name" ) PRIMARY KEY
	PRIVILEGES ( READ);

OK, so what if we do the same in the physical layer? Can we script the creation of a physical database entry?



DECLARE DATABASE "soademo" AS "soademo" UPGRADE ID 11788 TYPE Oracle10gR2
	 FEATURES (
		'LEFT_OUTER_JOIN_SUPPORTED' = 'Yes',
		'RIGHT_OUTER_JOIN_SUPPORTED' = 'Yes',
		'FULL_OUTER_JOIN_SUPPORTED' = 'Yes',
		'NESTED_OUTER_JOIN_SUPPORTED' = 'Yes',
		'UNION_SUPPORTED' = 'Yes',
		'UNION_ALL_SUPPORTED' = 'Yes',
		'COUNT_SUPPORTED' = 'Yes',
		'COUNT_DISTINCT_SUPPORTED' = 'Yes',
		'COUNT_STAR_SUPPORTED' = 'Yes',
		'SUM_SUPPORTED' = 'Yes',
		'AVG_SUPPORTED' = 'Yes',
		'MIN_SUPPORTED' = 'Yes',
                   ......
		'TIME_FORMAT' = '',
		'DATETIME_FORMAT' = '',
		'SORT_ORDER_LOCALE' = 'english-usa',
		'COMMENT_START' = '/*',
		'COMMENT_END' = '*/' )
	CONNECTION POOLS (
		   "soademo"."Connection Pool" )
	PRIVILEGES ( READ);
DECLARE CONNECTION POOL "soademo"."Connection Pool" AS "Connection Pool" UPGRADE ID 11789
	DATA SOURCE  {soademo}
	TIME OUT 300
	MAX CONNECTIONS 10
	TYPE 'Default'
	USER 'soademo'
	PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54F4FC53545E4298A16D2BF43FEA4A25A'
	REQUIRE QUALIFIED TABLE NAME
	SHARED LOGIN
	CONNECTIONS TO SAME URI 10
	OUTPUT TYPE XML
	HEADER PATH {C:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
	TRAILER PATH {C:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
	BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
	TEMP TABLE PREFIX {TT}  OWNER {}
	PRIVILEGES ( READ);
DECLARE SCHEMA "soademo".."SOADEMO" AS "SOADEMO" UPGRADE ID 11790
	PRIVILEGES ( READ);
DECLARE TABLE "soademo".."SOADEMO"."ADDRESS" AS "ADDRESS" UPGRADE ID 11796 HAVING
	(
		"soademo".."SOADEMO"."ADDRESS"."STREET" AS "STREET" UPGRADE ID 11842 TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."STATE" AS "STATE" UPGRADE ID 11843 TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."ZIP" AS "ZIP" UPGRADE ID 11844 TYPE "VARCHAR" PRECISION 10 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."COUNTRY" AS "COUNTRY" UPGRADE ID 11845 TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."ADDRESSTYPE" AS "ADDRESSTYPE" UPGRADE ID 11846 TYPE "VARCHAR" PRECISION 10 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."ADDRID" AS "ADDRID" UPGRADE ID 11847 TYPE "VARCHAR" PRECISION 4000 SCALE 0  NOT NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."ADDRESS"."CITY" AS "CITY" UPGRADE ID 11849 TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE
	PRIVILEGES ( READ)
	) DIAGRAM POSITION (170, 50) ROW COUNT 3.000000 LAST UPDATED '2007-06-27 11:36:06'
	PRIVILEGES ( READ);
DECLARE TABLE KEY "soademo".."SOADEMO"."ADDRESS"."ADDRESS_PK" AS "ADDRESS_PK" UPGRADE ID 11877 HAVING
	(
		 "soademo".."SOADEMO"."ADDRESS"."ADDRID" )
	PRIVILEGES ( READ);
.....
DECLARE TABLE "soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES" AS "CUSTOMER_ATTRIBUTES" UPGRADE ID 12338 HAVING
	(
		"soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."CUSTID" AS "CUSTID" UPGRADE ID 12339 TYPE "VARCHAR" PRECISION 40 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."FRAUD_ALERT" AS "FRAUD_ALERT" UPGRADE ID 12340 TYPE "VARCHAR" PRECISION 1 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."DEMOGRAPHIC_GROUP" AS "DEMOGRAPHIC_GROUP" UPGRADE ID 12341 TYPE "VARCHAR" PRECISION 2 SCALE 0  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."LARGEST_ORDER_SIZE" AS "LARGEST_ORDER_SIZE" UPGRADE ID 12342 TYPE "DOUBLE" PRECISION 9 SCALE 2  NULLABLE
	PRIVILEGES ( READ),
		"soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."PROFIT_SCORE" AS "PROFIT_SCORE" UPGRADE ID 12343 TYPE "TINYINT" PRECISION 2 SCALE 0  NULLABLE
	PRIVILEGES ( READ)
	) DIAGRAM POSITION (171, 50)
	PRIVILEGES ( READ);
DECLARE TABLE KEY "soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."CUSTID" AS "CUSTID" UPGRADE ID 12344 HAVING
	(
		 "soademo".."SOADEMO"."CUSTOMER_ATTRIBUTES"."CUSTID" )
	PRIVILEGES ( READ);

(I've cut out a lot of the above script, to save space...)

So, looks like you can. What about the presentation layer, can you script it's creation?

DECLARE ENTITY FOLDER "Seminar".."Product" AS "Product" UPGRADE ID 27264 ENTITY  "Seminar"."Product"
	FOLDER ATTRIBUTES  (
		  "Seminar".."Product"."Id",
		  "Seminar".."Product"."Prodid",
		  "Seminar".."Product"."Name",
		  "Seminar".."Product"."Description",
		  "Seminar".."Product"."Category",
		  "Seminar".."Product"."List_price",
		  "Seminar".."Product"."Producer" )
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Id" AS "Id" UPGRADE ID 27221 LOGICAL ATTRIBUTE  "Seminar"."Product"."Id"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Prodid" AS "Prodid" UPGRADE ID 27222 LOGICAL ATTRIBUTE  "Seminar"."Product"."Prodid"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Name" AS "Name" UPGRADE ID 27223 LOGICAL ATTRIBUTE  "Seminar"."Product"."Name"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Description" AS "Description" UPGRADE ID 27224 LOGICAL ATTRIBUTE  "Seminar"."Product"."Description"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Category" AS "Category" UPGRADE ID 27225 LOGICAL ATTRIBUTE  "Seminar"."Product"."Category"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."List_price" AS "List_price" UPGRADE ID 27226 LOGICAL ATTRIBUTE  "Seminar"."Product"."List_price"
	PRIVILEGES ( READ);
DECLARE FOLDER ATTRIBUTE "Seminar".."Product"."Producer" AS "Producer" UPGRADE ID 27227 LOGICAL ATTRIBUTE  "Seminar"."Product"."Producer"
	PRIVILEGES ( READ);

Looks like you can. About the only thing you can't script, it appears, is repository objects created using the Manage menu in the BI Administrator menu (Security Manager, Variable Manager etc) - these don't have Copy and Paste operators available when you right-click on them - doesn't mean you can't script them, I suppose, it's just that there's no easy way to extract the script for them.

So now that you've got these scripts, what can you do with them. Well, in the /OracleBI/server/bin directory, there are two executables: nqUDMLexec.exe and nqUDMLgen.exe, that you can use to process, and generate, scripts respectively. If I extract a script for an entire subject area, and then delete it in the repository, I can re-create it again using the following command:

nqUDMLexec.gen -u  -p  -i  -B  -O

If you supply the same repository name for both -B and -O repositories, it will merge the changes into the same repository. If the objects that you are importing from the file are already present in the repository, it replaces them, if they're not present, it creates them. Note that if you select a subject area, extract the script and then delete it, the import process will only re-create the subject area, not the presentation layer that would have disappeared when you deleted the subject area. To recreate both of these items, you would need to hold down the CTRL button and select both of them in order for both of their definitions to be included in the script.

If you run the script through the nqUDMLexec.gen utility, here's the output you get:

E:\>e:\product\oraclebi\server\bin\nqudmlexec.exe -u Administrator -p Administrator -i c:\logical_area_create.udml -B e:\product\oraclebi\server\repository\paint.rpd -O e:\product\oraclebi\server\repository\paint.rpd

---------------c:\logical_area_create.udml---------------

Complete success!!!

Note that if your repository is currently online, you'll need to shut down the BI Server before running the script, otherwise the utility will only open the respository read-only and the update won't take place.

If you then restart the BI Administration tool and check the results, you'll find the subject area has been restored.

If you run the other utility, nqUDMLgen.exe, you get the following help text:

E:\>e:\product\oraclebi\server\bin\nqudmlgen.exe
nQUDMLGen  -U userid [-P [password]] -R repository_pathname -O output_script_pat
hname [-8] [-N] [-Q] [-S]
  -h                 Display this usage information and exit.
                     -8 is for UTF-8
                     -N is for not generating upgrade id
                     -Q is for generating script without security objects
                     -S is for generating script for only security objects
                     Q and S override each other if both are present

Running it, passing it the path to a repository file, outputs a definition of the entire repository to a UDML file, including the security settings (with passwords obfuscated), though not the definition of any variables, intialization blocks, filters and so on.

E:\>e:\product\oraclebi\server\bin\nqudmlgen.exe -u Administrator -p Administrator -r e:\product\oraclebi\server\repository\paint.rpd -o c:\seminar.udml

Looks pretty interesting. From reading Dylan's blog article, all of this is undocumented and unsupported, so I guess it's an internal feature that BI EE uses, and tools such as OWB will use in the future to interface with the BI EE repository layer. It shouldn't be too difficult to reverse-engineer the syntax though, although as it's unsupported there's no guarantee the syntax will stay the same, or it'll even be present in future releases. It does look interesting though, if at any time you wish to script the generation of a repository, or sections of a repository, and it should be possible to automatically derive a set of repository objects from an underlying metadata model, or collection of database tables already in a star schema.

If anyone else has used this, add a comment to let me know how you got on.

Sign up for more like this.