Entries from Siebel Essentials tagged with 'Administration Tool'
Automating rpd metadata export with admintool.exe
This is an update to a previous post on data lineage in Oracle BI EE. As Erik Eckhardt describes in his blog, there is a not so widely known - and unsupported(!) - command line feature in the Oracle BI Administration Tool. It can be used to automate activities such as creating rpd files, importing physical layers, updating connection pools, exporting subsets from projects (which is used during installation of Oracle BI Applications btw) and more. As described in my post on Oracle BI data lineage, we have to export a csv file in order to get data we can use. Here is a way to use the admintool.exe /command syntax to automate this. 1. Create an input file with the following content. Save the file on your drive (example: D:\input.txt) Open samplesales.rpd Administrator Administrator Hide DescribeRepository D:\samplesales.csv UTF-8 MessageBox "Repository description exported successfully" Close Exit This file contains commands to Open the samplesales.rpd using Administrator as username and Administrator as password. The Hide command hides the Administration Tool window. The happy command is DescribeRepository which triggers an export of the rpd metadata to the D:\samplesales.csv file in UTF-8 codepage. This is similar to using the Administration Tool utility manually. The MessageBox command is just given as an example. Then we Close the file and Exit from Administration Tool. 2. Open a command prompt and type (or write the following into a shell script): D:\OracleBI\server\Bin\admintool /command d:\input.txt and execute the script. The admintool.exe will now open the input file and execute the commands, thus producing a metadata export file in an automated manner. In Erik's post you can find more commands. You can also run the Metadata Dictionary export using the GenerateMetadataDictionary command. The syntax for the input file is: GenerateMetadataDictionary Please note that using the /command switch is not documented and obviously not supported by Oracle Support, so please use it carefully and at your own discretion....
Data Lineage in Oracle BI EE
Earlier this year, I had the opportunity to work in a small Oracle BI EE project with a not so small source database. One of my tasks was to enable the project team members to easily identify objects with specific indications such as long-running queries or a high number of requests or a redundancy problem or the like. To put it short, what they wanted was: Data Lineage for Oracle BI metadata The design - and later implementation - included the following Oracle BI Usage Tracking (standard module)combined with presentation catalog metadatacombined with rpd metadataThere were some obstacles on the way, as usual. First, the Oracle BI rpd file is, well, a file and the data is not accessible like in a relational database. A fact which has been subject to criticism in recent times. The same holds true for the presentation catalog which has its information spread across thousands of small xml files. To overcome this, we used the Repository Documentation utility of the Oracle BI Administration Tool. This allows you to create a flat csv file with the dependencies of all objects from presentation layer, business model and mapping layer and physical layer. The screenshot shows the file after the insertion of a primary key column, which is a necessary task if you wish to connect to it as a data source in Oracle BI. In the project we used an import package to load the file into a MS SQL Server database. You can do the same with the presentation catalog using the Create Report option in the Tools menu of the Catalog Manager. Both tasks are purely manual and up to this moment I have not found an automation solution for it (if you did, please drop a comment), which is the main reason why data lineage for Oracle BI is a little bit cumbersome. Whenever the rpd or catalog content changes, an administrator must manually extract the csv files and (ideally) load them into tables in a relational database. As one commenter to this post has found out, there is an execution plan named Data Lineage in the current DAC repository (as created by the installer for Oracle BI Applications 7.9.5). A short investigation reveals that there are indeed table definitions which supposedly hold the data from the rpd and presentation catalog. At the moment this is only in the DAC repository and there is no accompanying ETL mapping in the Informatica repository. I would be so glad to be wrong here - if so, please add your comments below. Data Lineage subject area (inactive by default) in DAC We will see what future versions of Oracle BI Applications bring. Maybe a mapping which loads the csv files into the tables along with a ready-to-use rpd file (just dreamin'...) Now comes the knitting, which means that you have to create complex (physical) joins to glue the rpd metadata together with the presentation catalog data. For a first test you can use the presentation layer object names (table and column) to get first results in Answers. (This post does not deal with the creation of the business model and presentation layer in the rpd file to access the data). The S_NQ_ACCT table holds the valuable data from Oracle BI Usage Tracking, it has a column which contains the request path which allows us to join it to the presentation catalog data. Now we have a somewhat complete data lineage tool. Below is a screenshot from one of the first dashboard prototypes of the a/m project which allowed us to visualize the benefits of caching. There would be more to tell and maybe I find the time to post more on this, but let's dream with me a little more... What about bringing in the DAC repository, the Informatica repository, the Siebel CRM repository (right-click on a Siebel applet and see a report which describes all BC fields, columns and tables and their mapping to Oracle BI)...whoa...just woke up have a nice day...
Nested Presentation Tables - OBI EE Style...
...or "- " vs. "->" If you are a frequent user of Oracle BI Answers, you might find the following rather useful As you know, presentation tables can be nested or indented to ease navigation. I know you're smiling now and think "why is he bothering me with this kindergarden stuff?". Of course you know how this is achieved. Let's visit the Administration Tool: Notice two things here: First, the stop sign icon, which indicates a Presentation Catalog in the Oracle BI Applications rpd file which you should no longer be using. Second, the well-known use of the hyphen and space "- " combination in order to achieve the indentation of presentation tables. Let's label this "Analytics Style" because this is the way it has been done. Now let's have a look at the new presentation catalog: We notice that there are no hyphens and spaces, which by the way made some of the presentation tables inaccessible for external systems in the past. When you open the Properties dialog for an indented presentation table you see how it's done: Simply type "->" in the Description and the BI Presentation Server will interpret this as the command to render an indented presentation table. The description will render into a tool tip and yes, it does include the "->". Let's call this "OBI EE Style" and use it from now on. Happy indenting...
Search All Blogs
Google Search
Feed Subscription
If you use an RSS reader, you can subscribe to a feed of all future entries tagged 'Administration Tool'.
