Scripting Entries in the Oracle BI Repository
October 27th, 2007 by Mark Rittman
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.
October 27th, 2007 at 7:25 pm
Hi Mark,
It is always recommended to take a backup of the repository before even attempting to use both nqUDMLgen.exe and nqudmlexec.exe. I have seen strange things happening while using the nqudmlexec.exe on an online repository where multiple users are checking in and checking out. As you said, it is not supported. I have used this primarily for downgrading repositories. For example, a repository that has been created in 10.1.3.3 cannot be used from a 10.1.3.2 Administrator console. In that case, if one is not using any advanced features specific to 10.1.3.3, then i create the UDML, change the version and create the repository again.
Thanks,
Venkat
October 28th, 2007 at 9:54 am
[...] be seen if you paste it in say notepad/wordpad. I came to know this from Mark’s blog entry here. Similarly, so far i was under the impression that passwords of users can be changed only from the [...]
October 29th, 2007 at 1:42 pm
Hi Mark
This can be a very useful tool, particularly from a documentation point of view. The standard documenting tool does not include all the objects so I have created a parsing code to read the UDML into a database of metadata. I would like to get my hands on an IDE for UDML. I have also found that running through the UDML gen process can remove some glitches that appear in corrupted rpd files.
October 29th, 2007 at 2:29 pm
Hi Adrian,
I was thinking of you actually when I ran through the examples – I think you’ve mentioned working with UDML files in the past on your blog, up until now I wasn’t really aware of what they were. As you say, taking it one step further and parsing the file into a metadata database sounds very interesting, as would having an IDE for UDML development. One for Oracle when they eventually get around to a single BI IDE with single BI repository, with maybe UDML being the declarative language for BI metadata…
regards, Mark
November 3rd, 2007 at 12:13 pm
Yet, this method is not officially supported and any damage you insulting by using it is at your responsability.
January 30th, 2008 at 9:33 pm
[...] Credit where credit is due, the original idea is from my collegue Raymond de Vries based on this entry from Mark Rittman. [...]
February 26th, 2008 at 2:07 pm
Hi, Has anyone used UDML to read the permissions assigned to particular fields in the subject area and filters assigned to group? I had opened an SR about this and Oracle advised that it can be done using UDML however I found no documentation about it. Has anyone ever used it please?
Thanks
Marija
April 4th, 2008 at 10:03 am
[...] Before proceeding further i would recommend everyone to go through Mark’s blog entry here first which has details on what these utilities can do. One of the major advantages of these [...]
April 11th, 2008 at 3:18 pm
[...] the second one from Mark Rittman (http://www.rittmanmead.com/2007/10/27/scripting-entries-in-the-oracle-bi-repository/). [...]
April 28th, 2008 at 6:51 pm
[...] the amended text file into a new, blank PROD repository using the nQUDMLExec.exe utility. See this posting on this blog on UDML and repository migration and merging, and this posting by Venkat on automating changes to [...]