Good news: you really need no mouse for Oracle BI EE Administrator
As a person who absolutely dislikes pushing the mouse on the table too much the Oracle BI-suite is not exactly what I prefer working with. Although having this personal attitude to work with shortcuts I – surprisingly enough - have to admit that there are some cool features in OBI EE’s administrator-tool that make the mouse the best next move for Data Modelling.
Let me say a word to myself, not long ago I started my professional life as a BI-consultant and in detail I am a divided-by-three person, one third a IBM, another one a Oracle-Data Warehouse- and the last third a Hyperion-guy. Perhaps not this usual, but it went this way for me. Hence in this Blog I’d like to write about whatever platform I am working with actually – trying to be neutral and not to favour one solution. I am a lucky One, since not so long my BI-heart is not divided by three, but only by two due to the fact Oracle bought Hyperion. Thanks to Oracle!
So my blogs should cover both, either Oracle BI EE & Hyperion (hopefully soon the applications also) or IBM Balanced Warehouse, Data Warehouse Edition, DB2 UDB etc. Let’s await what’ll come, I’m curious what’ll be anyhow.
Now, let’s come to my subject today: No Mouse in OBIEE’s administrator-tool.
As I’ve learned last year during a Proof of concept in America and meanwhile read several times in this superb blogs from Mark Rittman, Dylan Wan and different others there is a possibility to script the RPD-file in a very old-fashioned way. Pretty much like you do it if you create an HTML-page without this WYSIWYG-editors like Dreamweaver etc. In the end you can you can simply paste the text of your scripted model (see example-picture from Oracle's sales-model below) in your OBI EE Administrator-tool and this software interpretes the underlying syntax for you and visualizes it into this portion of client/server-tool.
(or check http://blog.trivadis.com/blogs/andreasnobbmann/Blog_UDML_PhysicalLayer_PaintDemo.rtf for Download it as an RTF-file)
For starting with scripting I’d like to recommend reading the following two blogs, they inform you generally about the usage of UDML – the Universal Database Markup Language in OBIEE. Mark Rittman does more in detail. Thanks to both of them for the great blogs they are continuously writing!
The first one comes from Dylan Wan (http://dylanwan.wordpress.com/2007/10/22/udml-in-oracle-bi-server/),
the second one from Mark Rittman (http://www.rittmanmead.com/2007/10/27/scripting-entries-in-the-oracle-bi-repository/).
When I first noticed that scripting is possible I was interested to find more information about the syntax of it – in order to have the pool of possibilities for configuring and defining my models.
Because UDML is a non supported and undocumented feature of Oracle (and not yet carved in stone – it seems) I have not found any documentation on this in the web, so I decided to do the work and discover the different syntax-options of UDML so far. Today it’s physical-layer-time and I’ll try to illustrate the syntax in a mindmap.
One hint to the database-features which can be overwritten in the database-properties (in the second tab). They are stored in an ini-file under BI_HOME/server/config/DBFeatures.INI. If you click restore to default on the features-tab he takes the values as given there.
But now – as mentioned I want to be neutral (also for this Pro-Mouse/Con-Mouse-point) - a very smooth and clever mouse-move I learned during the advanced OBIEE-training last week at Oracle in Dreieich near Frankfurt am Main / Germany.
Let’s say you have 2 fact-tables, one with fine-granular sales-details (say costs & sales as measures) , the other one already aggregated on a monthly basis. To let BI Server know of this big SQL-cost-saving advantage you have to define multiple logical table sources in your Business model. My way to achieve this was always to add the complete table, remove the unneeded and then map the rest of the columns accordingly, but as I stunningly saw last week you can simply drag the sales-column from the aggregate physical table and drop it over to the already existing sales-column in the business-model and OBI-administrator does everything else for you.
I simply never had this idea just because this anti-mouse-revolution in my head, I have to think about that.
By the way and because it’s related to UDML: there was a really good blog I read last time regarding automating the repository updates if you have multiple environments e.g. production, test and development. Thanks to Venkatakrishnan J for his words!
Here it is : http://oraclebizint.wordpress.com/2008/04/04/oracle-bi-ee-101332-udml-to-automate-repository-updates-migration-of-repositories-from-development-to-testproduction-environment/
And one little story for the end how Oracle BI makes life easier – at least in the UK. So, think about it when flying from Manchester.
http://www.computerweekly.com/Articles/2008/01/07/228771/manchester-airport-lifts-one-bag-rule-with-oracle.htm
Enjoy reading !
Cheers and until next time, then probably with UDML- part 2 for the Business Model-part.
Andreas