Scripting Entries in the Oracle BI Repository

October 27, 2007 Oracle BI Suite EE

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:

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

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?

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

(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?

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:

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:

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:

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.

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.