You want to get the real insight to your repository ? Some columns have been changed and therefore not translated anymore ? But which of the hundreds ? Then parsing your UDML could probably help you...
Not the first time that networking brought really good ideas to me, but first time the digital one does. Just received an e-mail over a network (no no, I do not mention which) with the hint to a UDML-parser this guy
from Oracle wrote.
Hence my customer-situtation was aching for such a tool (renamed a presentation table and fought a battle with some reports) I thought let's give it a try just to check whether I catched all the important things (aliases, changes for CD_ and CN_values for external strings etc...) or not.
So I downloaded the file from http://code.google.com/p/udmlparser/. Great news: it's Open Source. Daniel's sentence brings it to the point: "Use it, get involved .... and love it".
Seems very easy to handle and indeed it is. The only thing you need is the UDML of your repository and I will not add the complete "How to use it" here, but in short it's:
- download the JAR file, from Downloads tab.
- move it to a target directory
- run: java -jar udmlparser052.jar
- check the required parameters
- use nqUDMLgen (under OBIHome/Server/bin) with flags -n -q -8 to extract your repository like that
C:\OracleBI\server\Bin>nQUDMLGen.exe -U Schnuffel -P Paule
-R C:\OracleBI\server\Repository\paint.rpd
-O C:\OracleBI\server\Repository\paintUDML.txt -N -Q -8
- and let it run by firing thejava -jar udmlparser0_4.jar -udml=paintUDML.txt -rpdxml=paintUDML.xml
C:\Software\UDML_Parser>java -jar udmlparser052.jar
-udml=C:\OracleBI\server\Repository\paintUDML.txt
-rpdxml=paintUDML.xml
And for further documentation please visit here :http://code.google.com/p/udmlparser/wiki/Usage. (1 place to update is better than many places !). More informations and a detailed step by step-description you can find on this page http://it.toolbox.com/wiki/index.php/Oracle_BI_EE_-_Encoding_Repository_metadata_in_XML.
If followed all the steps above Daniel's parser works through the UDML and you will get the following (or similar) screen:
And the result is a properly formatted XML-file which structures your repository for you into the different layers, tables, colums, connection pools etc. It looks like that :
Pretty much what you see also in the UDML, but you really have a better overview and can dig into your repository better. And in combination with a good XML editor (in the sample pictures below I used XML Spy) it can help you save a lot of time.
Thanks a lot to Daniel for the great work he did, I just saw he published several releases during the last months. I really appreciate that !
So, have a good time with it.
So long,
Andreas
A couple of weeks ago a OBIEE colleague of mine just had a case with a customer installation where whatever PDF you create from the BI publisher was half empty or half full, depending on how optimistic you are.
We decided together to blog what we found out because it is something you normally do not find due to the fact that it is too easy to find.
The solution was that the standard setting in the server configuration of BI publisher is set to a report viewer height of 500, which is around half a page . We searched a while for the place where this has to be changed and in the end it was the "Ah yeah" effect Thomas had when he finally discovered and I when he explained it to me.
Start your BI publisher web login, click the admin tab and go to the server configuration. Here it is. The only thing you have to change here is yep the Report Viewer Height. Try 750, makes the page of a 19'' screen perfectly contentful.
And by the way: every user can have his own setting. You can do it in the upper part of the page under Preferences after login to BI publisher over the web. It is the last field on the right hand.
So long,
Andreas
Something I searched for a longer time within OBIEE is kind of a Dashboard-menu for the case you have so many dashboards, that it confuses the user more than it helps.
You know some people say there is a golden number for the normal brain: the 8. So 8 dashboards, 8 measures, 8 dimensions etc. Think about..... how many things can you memorize at one glance.... ?
But if you want more Dashboards here is a parameter to limit the number of dashboards shown on the page. I chose 2 Dashboards (which have to be in the same shared folder) :
<DashboardMaxBeforeMenu>2</DashboardMaxBeforeMenu>
Add this simple row to the instanceconfig.xml (located in <OracleBIData>\web\config\ or with / under Linux/Unix) and afterwards restart your Presentation Services. So, in the end it could look like this :
Something you can find in the manual in the Presentation Services Admin Guide on page 81 or you can ask somebody (Thanks Tony for your help !).
Have a good one and so long,
Andreas
Just a couple of weeks ago I implemented support for multiple languages at customer-site and to be honest it is not the big easy to do so with OBIEE. Hence I decided to blog around some circumstances which occurred during the last weeks and not to go forward with the UDML-part of OBIEE. Of course Venkat did a lot of blogging on this subject already, so there was not a lot left for me, but I found some things.
1. But let's start at the beginning:
- first you have to switch on the external strings for your presentation-model in your Oracle BI Admin (which I will call OBIA in short from now on). Right click on it and choose either Column Names (prefixed CN_) or Column Descriptions (prefixed CD_) or both.
- afterwards select from the menu Tools / Utility and Externalize Strings
- choose your business model from presentation layer and hit the save-button, answer the questions for a file-name as you prefer. OBIA creates a CSV-file with 3 columns in it.
- the 1st one with the actual name of the presentation column
- the 2nd one contains the session-variable which will be created within Answers or Dashboard after you have implemented it sucessfully
- the 3d one holds the translation of the column
- for the purpose of having more flexibility let's add another one with the language-abbreviation
- create a table in whatever database (some call MS Access one, I do not !), in which you have the four of them columns, which you can name as you prefer.
For example ACTUAL, SESSION_VARIABLE, TRANSLATION, LANG_ID.
- add a connection-pool, the best with a speaking name, to your repository and test the connection with a short Data Preview (or simply do Update Row Count)
- afterwards the only thing left is to add an initialization-block which determines for you the language of the user logging in and another one which provides the functionality for OBIEE to select the correct columns from the created table.
Here the first one (you do not necessarily need this one, because in the second one you are using the same session-variable ! I did for making it more clear.). This one you can also use if you want for example a language driven filter or prompt.
select ‘VALUEOF(NQ_SESSION.WEBLANGUAGE)' from dual
and the second one:
select SESSION_VARIABLE, TRANSLATION from sales.bi_msg
where LANG_ID = 'VALUEOF(NQ_SESSION.WEBLANGUAGE)'
Now restart the presentation services and everything should be fine for every language, at least for the ones you already translated and inserted the column-names and -descriptions in your table.
But there is all this other stuff hanging around in the reports from which I initially thought is also implemented with the external string-solution. All this names of dashboard-pages, titles and subtitles, legends, report-based calculated columns, narrative views, chart-scale-markers, pivot-table-ingredients, gauge-range settings etc.pp.
Never the less, it came out some of them do not run with session-variables (Subtitles, Legends, views etc., see more details in the Answers, Delivers, and Interactive Dashboards-doc on page 45 or - it is the exception-join ) :
"Where can you reference variables?
You can reference variables in the following areas:
■ Title Views
■ Narrative Views
■ Column Filters
■ Column Formulas
■ Conditional Formatting conditions (presentation variables only)
■ Direct Database Requests
■ Dashboard prompts
■ Chart scale markers.
■ Gauge range settings.
■ Static text.
■ iBot Headlines and text"
2. Although it makes sense to have different places for translations in some cases, it is not exactly what Oracle is propagating when stating that they have one single and central place for holding metadata. Of course, this is the case for the complete model, but the reporting-(better frontend) side is not included.
So to have this also translated you have to shut down your presentation services, start the Catalog Manager, open the desired catalog-files offline and export the captions (see pictures below). In this case you do not need to state an exact name (only the directory) for your file, your catalog manager exports every presentation catalog in one xml-file for you.
If you do this the second time you can force the software to export only new or changed strings into your captions. Also you see the option to exclude the column-descriptions.
After this step you should find a bunch of xml-files in the directory you defined in the export captions-dialog.
Wow, a lot of effort to handle translation, and not the comfortable way. Unfortunately it is a lot of manual work and you have in addition to do every keystroke very carefully, because all changes can only be done directly in a XML-file, where the format is really the crux of matter. And do not even think of versioning, handling external translation tables from different translators, changed calculated columns or titles. In the end you have to manually translate the whole report-catalog into all needed languages for all the reports, dashboards, graphs, legends, subtitles a.s.o and also for all metadata defined in the repository.
So, for the first time the very wide and broad (tend to use the word sophisticated, so I do) functionalities of OBIEE are a burden. To be honest I was not happy with this conclusion. In the end a very lucky coincidence happened: a software partner developed a tool for this special needs. Yeah! Its name is BI Localizer (more info on http://www.bi-localizer.com/). It is not the fancy-looking Client-Tool from the layout-perspective, but it handles all multilanguage-related tasks in one and therefore is Admin's darling.
You can
- parse & fix the repository
- import external translation tables
- translate the different fields in the XML-data
- do the translation of CN_ (column-names) & CD_-(column-descriptions) values for the repository-columns
Let's go in detail. After you have installed the demo-version you will be emailed after providing your information on the BI Localizer-page you start the setup and follow the instructions in the "Users manual BI Localizer.pdf". The setup will install some .NET-packages and finally also the BI Localizer-stuff. Please be aware that because the installer makes a automatic download from Microsoft you have to be connected with the internet. It could happen that you have to restart your computer (because of .NET).
There are some prerequisites (see the "User Manual" under post-install activities on page 7 ) to take care for so that you are ready for starting:
- copy all BI Localizer-related files in one working-directory (e.g. BILocalizer), pay especially attention for the locale_default_template.udml (in it the initialization-block is defined) and the corresponding MS Access-file BI_Localizer.mdb.
- firstly you need the earlier defined externalization strings from your rpd-file (see point 1.Also described on page 8 and 9 in the "User Manual" )
- secondly of course the XML-definitions of your reports, dashboards, etc. are required. For obtaining them shut down your presentation services (see point 2.Also described on page 10 in the "User Manual" ))
- as a third step create a table with the provided within the SQL-script CreateLangTable.sql
- also you need a ODBC-connection to the new created Oracle-table for the interchange of data between MS Access and Oracle
- define an OCI-connection to your Oracle-table in your tnsnames.ora in order to give the prd-file access to it
- the external translation table has to be in a special format, for more information please refer to "User Manual" on page 13 under External Translation catalog)
Ready to go ? Start the BI-Localizer-application from your programs-folder from the BI Localizer-menu. After clicking Start on page 1 you have to define all parameters for either the repository- and the Oracle-DB-side. The values you have to define are on the left side:
- the Output directory
- the Username (for the repository) and also the password
- the file-name of the repository (by default NF_OBIEE_LANGUAGE_TABLE, you can of course change) and
- the MS Access-File, where the all data is stored
on the right side:
- username and password for the Oracle-DB-connection (actually only Oracle is supported as database)
- the SID of your database (you can see in your TNS-Names-file under Oracle\network\)
- the table-name (by default NF_OBIEE_LANGUAGE_TABLE, you can also change that)
Click on Step 2 for the next page, where the repository-informations are given. First the Captions you created in step 2, then the external strings you created from within your repository. At the bottom of this screen you can set the language you want work on right now (in Demo-Version only 4 languages are available).
Click on Step 3 for page 3, where the general options are defined. For the initial time of usage please check the close match, every time later you check exact match to be sure that exact pattern-matches should be handled for translation by the tool. You want your repository parsed and fixed and the initialization block included ? Check the boxes on the right hand of the screen for. Also, if you already have an external translation table
Depending on the parameters you defined you see the Go-Buttons executable on the next page
1. Repository-clean-up (creates also the initialization-block for the Oracle-database needed for Localization)
2. Translate new / changed web-catalog metadata (the xml-files) - only shown if there is new untranslated metadata
3. Translate new / changed repository metadata (the rpd-file) - only shown if there is new untranslated metadata
4. Upload localization database to Oracle (NF_OBIEE_LANGUAGE_TABLE,if you have not changed the name)
5. Import external translation catalog
So, you can now do as and what you prefer. If you choose one of the two translation points the number of values to be translated displays and afterwards the translation is started.
Unfortunately in this version of BI Localizer you have to do this initially. If finished you can work with the underlying MS Access table and copy and paste the values for each language, integrating the translations from your external translation provider or translating it yourself, if your familiar with the languages needed.
Do not forget to change the settings on page 3 to exact match after your initial load, otherwise he will prompt you again for the translation of all unmatched data.
So, you see there are some things to do in this tool, but for the beginning it is quite useful.
Have fun and so long,
Andreas
Despite I assume most of you know that there is a little special thing you have to take care for in order to achieve proper inter-dashboard-navigation I wanted to post this shortly.
One of the most powerful features of OBIEE is the navigation between the different dashboards and reports. This is running very smooth and easy, generously said "only" by setting the filters for whatever column you want
to use to be prompted. But only for columns from the same business model (better presentation catalog).
If you want to connect two dashboard-pages from different presentation catalogs (let's say Sales and for example Costs), then you have to pay attention for the following requirements of OBIEE :
- the logical column and logical table must have the exact same name in the two catalogs
- if not you can define an Alias for the table and it is running again.
In pictures it looks like that :
You can see that all logical colums and tables have the same name. If not you can define an alias on the table as well as on the column. In presentation layer double-click on the presentation table or column, select Alias and create an Alias. By the way: if you rename a logical table, OBIA (Oracle BI Administration Tool) creates the Aliases automatically.
I would not recommend to create Aliasing for columns, because it gets confusing and is pretty fast not administrable anymore. Of course you can see the defined alias' in your UDML, but it is not obvious for somebody reading your repository the first time.
So long,
Andreas
Oracle BI - suites provide a lot of flexibility to extend your BI-applications with variable content in order to face all challenges to customize a solution to your needs. Hence, today I will write something about adding your personal / company-pictures to the Dashboards, Answer-reports etc.
First you have to think about how to customize your style, which is not easy due to so many possibilities. Finally Skins, Styles, CSS, Banner-images and so on are pretty clearly described in chapter 10 of the OBIEE Presentation Service Admin Guide. Today I like to restrict my writing on the style- and skin-side. It is more a build your appetite instead of claiming to be the complete answer-catalogue to your questions.
All default skin-related files are found in the directory OracleBI\web\app\res\sk_oracle10, the correlating styles in the same directory, under s_oracle10. This folders you can copy to OracleBIData\web\app\res\sk_YourFolder (or s_YourFolder for style) and adapt all files within this directory to your wishes. After you have restarted the presentation services you have this style available in your dashboard-properties 
.
You can define your standard-skin in the well-known instanceconfig.xml by adding the entry <DefaultStyle>NameofYourStyle</DefaultStyle>. As mentioned above the name of your skin has to begin
with sk_, of your style with s_, but in the instanceconfig.xml you do not need the leading sk as I have
tested. But pay attention: the rest of the name must not contain underscores.
Here you see multiple subfolders where you can arrange your images or other files.
Most of them have speaking names, Charts for the different chart-types, Gauges for the gauges, etc. Images is generally the folder for the pictures, in Meters you find the standard-structure you get right after you click on image in your edit column-dialog-window.
Cheers,
Andreas
I guess some of you might have used the neat function "Percent of" in the pivot-tables of OBIEE. Cool stuff ! And this easy to implement. Just click and say "Show Data as" "Percent of" and choose column, row, section, page, column parent, row parent or layer.
And you are done, that's it !
Comes along very useful !
Anyhow, just had a request from a customer wanting the percentage more accurate, with 2 decimals after the point. OK, I thought, should not be this difficult to find. But far out....
After a while I had checked all the formatting-options in Answers and Dashboards in depth, but still no clue where to in the end change that setting. Hence my last desperate consultant-try was to examine the pure XML-code for further hints. Et voila I got it.
If you want also 2 places after the decimal-separator go to the advanced tab of your OBIEE-frontend, copy the complete XML-request in a editor (wordpad or so, unfortunately you cannot search in the HTML-window in OBIEE directly), do a !!! BACKUP of the XML !!! first and then search for "saw:percent". Right after this there are two parameters for this type of display-schema <minDigits> and <maxDigits>.
<saw:dataFormat xsi:type="saw:percent" minDigits="1" maxDigits="1" scale="2"/></saw:displayFormat></saw:showAs>
Here the complete procedure :
1. set both the minDigits- and maxDigits-value to 2 (Attention: please change nothing else, neither in formatting, nor in adding a row, a tab or only a carriage return or something else.),
2. copy the complete text from within your wordpad,
3. clear the complete XML-window,
4. paste the changed XML-code in the most upper window (make sure you did not leave out any row/character of the changed XML),
5. click Set XML
6. do not save the report immediately, go back to the result-tab first and check the outcome. Now, if all went well save your report.
So, you have OBIEE showing the value properly the way you want.
I thought I found the standard-setting for the "percent-of"-function within the file pivotvieweditor.js sitting in the directory \oracleBI\web\app\res\b_mozilla\views\, but despite there are values hard-coded for <minDigits> and <maxDigits> in the function "PTCreatePercentOf" unfortunately it doesn't effect new PercentOf-columns in Pivots. Anyhow I do not give up and will find and post it as soon as I got something.
So, that's for today. Have a good time and so long,
Andreas
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