Scripting Entries in the Oracle BI Repository

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"."PRODUCTPK" )
    SOURCES (
         "Seminar"."Product"."Productdw" ) NO INTERSECTION DIAGRAM POSITION (463, 66) ATTRIBUTES
    (
         "Seminar"."Product"."Id",
         "Seminar"."Product"."Prodid",
         "Seminar"."Product"."Name",
         "Seminar"."Product"."Description",
         "Seminar"."Product"."Category",
         "Seminar"."Product"."Listprice",
         "Seminar"."Product"."Producer" )
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Id" AS "Id" UPGRADE ID 26092
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Id"."AD3900:197681193399951" AS "AD3900: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"."AD3900:197691193399951" AS "AD3900: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"."AD3900:197701193399951" AS "AD3900: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"."AD3900:197711193399951" AS "AD3900: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"."AD3900:197721193399951" AS "AD3900:197721193399951" UPGRADE ID 27366 BETWEEN ATTRIBUTE  "Seminar"."Product"."Category" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."CATEGORY"}
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Listprice" AS "Listprice" UPGRADE ID 26097
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Listprice"."AD3900:197731193399951" AS "AD3900:197731193399951" UPGRADE ID 27368 BETWEEN ATTRIBUTE  "Seminar"."Product"."Listprice" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."LISTPRICE"}
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE "Seminar"."Product"."Producer" AS "Producer" UPGRADE ID 26098
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Product"."Producer"."AD3900:197741193399951" AS "AD3900:197741193399951" UPGRADE ID 27370 BETWEEN ATTRIBUTE  "Seminar"."Product"."Producer" AND EXPRESSION {"custdw".""."CUSTDW"."PRODUCT"."PRODUCER"}
    PRIVILEGES ( READ);
DECLARE LOGICAL KEY "Seminar"."Product"."PRODUCTPK" AS "PRODUCTPK" UPGRADE ID 26137 HAVING
    (
         "Seminar"."Product"."Prodid" ) PRIMARY KEY
    PRIVILEGES ( READ);
DECLARE LOGICAL TABLE SOURCE "Seminar"."Product"."Productdw" AS "Productdw" 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"."Listprice"}  AS {"custdw".""."CUSTDW"."PRODUCT"."LISTPRICE"} ,
         {"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"."ITEMSPK" )
    SOURCES (
         "Seminar"."Order Line Items Fact"."Itemsdw" ) 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"."Totalprice",
         "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"."AD3900:197371193399951" AS "AD3900: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"."AD3900:197381193399951" AS "AD3900: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"."AD3900:197391193399951" AS "AD3900: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"."AggRule2050:196761193399951" )
    PRIVILEGES ( READ);
DECLARE AGGREGATE RULE "Seminar"."Order Line Items Fact"."Quantity"."Measure"."AggRule2050:196761193399951" AS "AggRule2050:196761193399951" UPGRADE ID 27269 EXPRESSION { SUM("Seminar"."Order Line Items Fact"."Quantity")}
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Quantity"."AD3900:197401193399951" AS "AD3900: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"."AD3900:197411193399951" AS "AD3900: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"."Totalprice" AS "Totalprice" UPGRADE ID 26073
    PRIVILEGES ( READ);
DECLARE AGGREGATE MEASURE "Seminar"."Order Line Items Fact"."Totalprice"."Measure" AS "Measure" UPGRADE ID 26116
     RULES(
         "Seminar"."Order Line Items Fact"."Totalprice"."Measure"."AggRule2050:196771193399951" )
    PRIVILEGES ( READ);
DECLARE AGGREGATE RULE "Seminar"."Order Line Items Fact"."Totalprice"."Measure"."AggRule2050:196771193399951" AS "AggRule2050:196771193399951" UPGRADE ID 27271 EXPRESSION { AVG("Seminar"."Order Line Items Fact"."Totalprice")}
    PRIVILEGES ( READ);
DECLARE ATTRIBUTE MAP "Seminar"."Order Line Items Fact"."Totalprice"."AD3900:197421193399951" AS "AD3900:197421193399951" UPGRADE ID 27306 BETWEEN ATTRIBUTE  "Seminar"."Order Line Items Fact"."Totalprice" AND EXPRESSION {"custdw".""."CUSTDW"."ITEMS"."TOTALPRICE"}
    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"."AD3900:197431193399951" AS "AD3900: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"."AD3900:197441193399951" AS "AD3900: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"."AD3900:197451193399951" AS "AD3900: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"."AD3900:197461193399951" AS "AD3900: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"."ITEMSPK" AS "ITEMSPK" 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"."Itemsdw" AS "Itemsdw" 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"."Totalprice"}  AS {"custdw".""."CUSTDW"."ITEMS"."TOTALPRICE"} ,
         {"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 ( 'LEFTOUTERJOINSUPPORTED' = 'Yes', 'RIGHTOUTERJOINSUPPORTED' = 'Yes', 'FULLOUTERJOINSUPPORTED' = 'Yes', 'NESTEDOUTERJOINSUPPORTED' = 'Yes', 'UNIONSUPPORTED' = 'Yes', 'UNIONALLSUPPORTED' = 'Yes', 'COUNTSUPPORTED' = 'Yes', 'COUNTDISTINCTSUPPORTED' = 'Yes', 'COUNTSTARSUPPORTED' = 'Yes', 'SUMSUPPORTED' = 'Yes', 'AVGSUPPORTED' = 'Yes', 'MINSUPPORTED' = 'Yes', ...... 'TIMEFORMAT' = '', 'DATETIMEFORMAT' = '', 'SORTORDERLOCALE' = 'english-usa', 'COMMENTSTART' = '/', '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"."ADDRESSPK" AS "ADDRESSPK" UPGRADE ID 11877 HAVING ( "soademo".."SOADEMO"."ADDRESS"."ADDRID" ) PRIVILEGES ( READ); ..... DECLARE TABLE "soademo".."SOADEMO"."CUSTOMERATTRIBUTES" AS "CUSTOMERATTRIBUTES" UPGRADE ID 12338 HAVING ( "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."CUSTID" AS "CUSTID" UPGRADE ID 12339 TYPE "VARCHAR" PRECISION 40 SCALE 0 NULLABLE PRIVILEGES ( READ), "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."FRAUDALERT" AS "FRAUDALERT" UPGRADE ID 12340 TYPE "VARCHAR" PRECISION 1 SCALE 0 NULLABLE PRIVILEGES ( READ), "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."DEMOGRAPHICGROUP" AS "DEMOGRAPHICGROUP" UPGRADE ID 12341 TYPE "VARCHAR" PRECISION 2 SCALE 0 NULLABLE PRIVILEGES ( READ), "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."LARGESTORDERSIZE" AS "LARGESTORDERSIZE" UPGRADE ID 12342 TYPE "DOUBLE" PRECISION 9 SCALE 2 NULLABLE PRIVILEGES ( READ), "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."PROFITSCORE" AS "PROFITSCORE" UPGRADE ID 12343 TYPE "TINYINT" PRECISION 2 SCALE 0 NULLABLE PRIVILEGES ( READ) ) DIAGRAM POSITION (171, 50) PRIVILEGES ( READ); DECLARE TABLE KEY "soademo".."SOADEMO"."CUSTOMERATTRIBUTES"."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"."Listprice",
          "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"."Listprice" AS "Listprice" UPGRADE ID 27226 LOGICAL ATTRIBUTE  "Seminar"."Product"."Listprice"
    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:\logicalareacreate.udml -B e:\product\oraclebi\server\repository\paint.rpd -O e:\product\oraclebi\server\repository\paint.rpd

---------------c:\logicalareacreate.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 repositorypathname -O outputscript_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.

Subscribe to Rittman Mead

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!

Subscribe to Our
Monthly Newsletter!

* indicates required