Scripting Entries in the Oracle BI Repository

October 27th, 2007 by

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.

Comments

  1. Venkat Says:

    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

  2. Oracle BI EE 10.1.3.3/2 - Changing Passwords from Presentation Services « Business Intelligence - Oracle Says:

    […] 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 […]

  3. Adrian Ward Says:

    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.

  4. Mark Rittman Says:

    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

  5. ivanko Says:

    Yet, this method is not officially supported and any damage you insulting by using it is at your responsability.

  6. CIBER Knowledge » OBIEE Development street / Ontwikkel straat Says:

    […] Credit where credit is due, the original idea is from my collegue Raymond de Vries based on this entry from Mark Rittman. […]

  7. Marija Bonello Says:

    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

  8. Oracle BI EE 10.1.3.3/2 - UDML to automate repository Updates - Migration of Repositories from Development to Test/Production Environment « Business Intelligence - Oracle Says:

    […] 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 […]

  9. Good news: you really need no mouse for Oracle BI EE Administrator - Andreas Nobbmann Says:

    […] the second one from Mark Rittman (http://www.rittmanmead.com/2007/10/27/scripting-entries-in-the-oracle-bi-repository/). […]

  10. Rittman Mead Consulting » Blog Archive » Migration OBIEE Projects Between DEV and PROD Environments Says:

    […] 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 […]

Website Design & Build: tymedia.co.uk