Once upon a time I implemented the SA system subject area ....... I have to admit for the first time I followed one post from Venkat. And then I can remember discussing this in Brighton with Benjamin and John. Next in my memory is a mail and then ....
So, because somebody wanted the SA system subject area I would like to post it here. But to make it more challenging I post the UDML code, nothing else makes sense. You know "Scripting with OBIEE is fun !", so I hope you'll have exactly this with this little geeky piece of code.
Perhaps it would be a good idea to make a little introduction: the SA system subject area is an Oracle standard to define some default settings for the Delivers part of OBIEE. For example the default mail-adress, the language, the delivery profile etc. can be set using it.
But now let's start. Here are some screenshots, only an excerpt of the different layers.
The physical layer:
(click here for Download as a UDML-file)
The Business model:
(click here for Download as UDML-file)
The presentation layer:
(click here for Download as UDML-file)
If you are facing problems with it post a comment and we'll sort it out together, OK ? Have fun !
BTW: replace YourDataSource, YourUser and YourPassword with your credential information in the UDML for the physical layer. And pleaSE plEASE PLEASE do a backup before trying anything, OK ? It is for your own safety. The "OBIEE-elders" Alex and Christian had their reasons when they said: "Don't let kids touch this one!" and "Don't try this at home".
So long,
Andreas
When I am at customer site the most interesting questions come up. Sometimes really challenging ones. Lucky this time I just had to ask around in my highly skilled Trivadis-network for the one after "How to implement ABC-Analysis in OBIEE". This time it was Hendrik Becker, who came up with the answer and provided me the solution. Thanks Hendrik !
Generally ABC-Analysis is mostly used to categorize inventory into classes A,B and C, where class A marks the items having the most influence on the inventory costs. Class A typically contains the items which make up to typically 80% (or 20% of total items) of the total value, class B will have around 15% of total value and so on. For more detailed general information you can refer here. In the end you can use this method for mostly all quantitative measures, e.g. sales or bookings can be displayed this way. Traditionally I would say that ABC analysis is used most frequently in combination with products or customers. For the first to get an impression on which products are your most important ones, for the second to find out more about your key accounts, perhaps also giving insight in how dependent you are from your Top customers in terms of sales, bookings or whatever measure is analyzed.
But of course ABC analysis (as all analysis) is not limited, so feel free to use ABC analysis whereever you like to. In my opinion this analysis really has a vey good informative value and by the way explains itself visually for every user.
Enough general explanations, let’s get our hands on it.
So, what we need for this are 2 calculated measures beside the normal figures. One shows be the cumulated value of the measure we want to display, the other holds the percentage of this value from the total.
Let's take the sales demo and use sales as our measure. What I do is I create a rolling sum over sales rsum("- Sales".Sales). Additionally I calculate the percentage of my detailed sales cell from the total sales like this ("- Sales".Sales/sum("- Sales".Sales)) * 100. And of course I want to know how many items are in my class, so I need to count them. And also I create the category (you remember A, B, or C) which I will need later, perhaps like this:
CASE WHEN
round(rsum("- Sales".Sales/sum("- Sales".Sales)*100),1) <= 80
THEN
'A'
WHEN
round(rsum("- Sales".Sales/sum("- Sales".Sales)*100),1) between 80 and 95
THEN
'B'
ELSE
'C'
END.
After all the report looks like this :
or graphically like this :
Fine, I like that so far. But there is something important missing. You might know what I mean, don't you ? Exactly, add a dashboard prompt to make the limits flexible and change your column formulas accordingly.
I dare to claim that this dashboard really is a benefit for your user. Or not ? What do you say ?
So long,
Andreas
One thing I am quite frequently asked by my customers is how to display the name of a measure and its corresponding base unit of measure (e.g. TCHF for Thousand CHF) in one column of my report. For those of you familiar with the external names there is one very comfortable way to handle this. If you are not yet deep into handling external contents from tables here some recommendable reads: Venkat did a good one, John also wrote about and last but not least there is my blog around that subject.
Coming to the heart of this blog post:
once upon a time when playing around with the external names table I tried to embed HTML tags into the table and found out that it is possible. Oh yeah ! First thing I tried is to implement that example mentioned, showing a measure alligned left with its approprate unit of measure alligned right . And after a little while playing around with the correct HTML-syntax. Attaboy, there we are.
<table border=''0'' width=''100%''><tr><td width=''250''>Sales</td><td align =''center''>[TCHF]</td></tr></table>
What I also like is that you can display all the different special characters you sometimes need. Here for the upped 2 for the square metre in the german language
<table border=''0'' width=''100%''><tr><td width=''250''>Area</td><td align=''center''>[m²]</td></tr></table>
So, in the end what you have to do is :
- mark your column(s) for which you want external names be shown in Answers instead of the default column names (right click, activate the bottom two buttons)
- from the menu use Tools / Externalize names to get all relevant columns out of your repository
- give your columns appropriate names in the before retrieved file
- create a table and insert your names
- for all measures use the above syntax
- define the connection pool and the init block to get the external names properly (see my blog under chapter 1 for this) in your repository
And with this you can really make your users, customers or simply just a friend of yours very very happy. It really looks stunning, doesn't it ?
BTW: Fläche = german for area.
One tip: copy one whole language in your excel file to a specific language (e.g. xx), so that you can develop easier. With the names of the columns displayed in HTML this is kind of creepy.
So long,
Andreas
Not a big deal for the HTML-guys among you, but for me something new. Here is a little feature to be included in a text section of your dashboard, which helps to make printing one click less. And sometimes can be the more appropriate way of printing. I just did it in combination with GoURL and used the HTML-View of the report (where the URL could for example look like this http://obiee_se:9704/analytics/saw.dll?Go&Path=/shared/Blogs/Get%20your%20stuff%20printed&Options=rmf&format=html) in order to avoid that the HTML-tags for my columns - lying in the external names table - are displayed on the report.
What the code described below does is it simply opens the print dialogue right after the report opened up. This is a fast way to get what you want on paper, isn't it ?
Have a look yourself if you like it or not.
<script language="javascript">
window.print();
</script>
Add this simple javascript to your report and every time you open this report you will be automatically asked to select the printer you want to print your report on. Alternatively you can add a link to the report and name it for example "Print this report".
The described solution is really good for everyone using intensively HTML formatting in their column-names (means in the external table). If you want to know more about Multilanguage support you can read something about here.
Have fun and so long,
Andreas
Great thing I found out this week. As so often a customer requested something, he wanted a text in a normal Pivot-table to have displayed vertical. While going through the different possibilities in OBIEE it did not come out to be makeable out-of-the-box. So, I looked around in the developers area and found something in the world of CSS. Just one hint before I show you how: this is only running for IE, because Firefox doesn't interpret filters.
Create a simple pivot-table and make sure you have the column you want to be displayed in it. When you are in the Pivot result view click on the value formatting of your column and add the following text to your CSS-window :
Writing-mode: tb-rl; filter: flipv fliph;
Your column content could look like that :
and your pivot similar to this one :
If you now add the above mentioned CSS in your format values screen
it looks like that in Microsoft Internet Explorer only.
It's not the perfect solution, simply because this is not running for Firefox, but it can help you in some cases, where the customer wants this kind of thing. Of course you can center the text by using the normal CSS- command text-align:center to the above stated CSS.
So long,
Andreas
BTW: as soon as I have a proper solution also for Firefox I'll let you know, am already in discussions with the Firefox-cracks @Trivadis.
I just had a customer requirement who wants for reports with a lot of rows that the headings stay fix while the real report can be scrolled user-friendly as you like it. As far as I know (and I double checked with @lex and Christian) there is no out of the box solution for this so I did my Wicky kind of thinking and thought about trying to do it with iFrames and Go URL functionality.
So, here we go. The first thing you need is a simple table view report with a lot of rows - let's say more than 200, then it really makes sense. If you have done so get the URL of your report and add a narrative view to it, where you apply the iframe and paste the URL of your report to. For getting the URL you of course need to save the report first.
So your first narrative view could look like that:
<iFrame src=http://obiee_se:9704/analytics/saw.dll?Go&Path=/shared/Blogs/Fix%20Heading%20variable%20report%20body&Action=Navigate&ViewName=tableView!1 scrolling="no" width="800" height="35"> </iFrame>
If you take a look then you will recognize that I selected only the table view and adjusted the height to only 35 points - even just enough to show the column headings. If this value is not good for you play around with the height until you have your perfect value. And I also have hidden the scrollbar for the column headings by adding the option scrolling="no" to the iFrame.
WOW: only the "native" names of views like in the XML described can be accessed via Go URL, like tableView!1, pivotView!1, compoundView!1 and so on.
After you are done with the first report add another narrative view to your request, in which you show also the table view, but in this case completely. This in the end means that you have the column headings two times. Surely it is not perfect, but at least a good start.
Now you can place the report two times in a dashboard, as first view take the compound View, where you have title and narrative 1 view in and as second view the narrative 2 view. Or you can put everything in one compound View - it's like you like it.
Be aware, that the size of the windows will not correlate to your browser window, it will stay the size you defined in your iFrame. Also, be aware, that you have to pass all parameters in saved filters to this Go URL with the syntax described by my fellow blogger here.
Have fun and so long,
Andreas
"Sorry, I'm late". After a real long time I finally have a little time to blog again. Missed it ! And want to restart with an happening which made my eyes look bright.
Oh what a event...
I can only follow my fellow-blogger Christian when I say I had the honor to attend the really great Rittman Mead BI forum which happened the 14th and 15th May in Brighton. So perfectly organized, with tons of informative and interesting presentations, lots of good talks and discussions around OBIEE, Essbase and ODI and with a - in my mind - very harmonizing group of people attending. All in all a very delightful happening for which I want to say thank you very much to Mark&Jon and their team Becky, Jennifer (ladies first !) and Borkur, Ragnar and Peter.
I was invited to hold a presentation about Scripting Oracle BIEE there, which was really fun, because the guys from Rittman Mead opened some bottles of ice cold beer before I started. Hence we had an even better
atmosphere in the audience than already before (see Mark's blog). Relaxed presenting....
The presentation went very good and while standing in front of the audience I even learned something new. That it is NOT possible to paste UDML of repository content from any of the menu points accessible from the manage menu and that the admin tool has an command line option with which you can do a lot of funny things around automation with your repository. Hardly I knew that I blackled (google for energy saver, wanna try ? Click here) around it and found that of course John already blogged on it. And we discussed the new Content Accelerator Framework, which uses XUDML - a combination of XML and UDML. See here some blogs around that, one from Venkat, the other one from @lex. Great, if I learn this much while presenting I will do that again and again.
Talking about presentations. It had very impressive presentations full of comprehensive content on the Rittman Mead BI Forum.
There has been the one from Edward Kroske (he’s THE MAN in Essbase), who wrote some books with very special titles like “Look smarter than you are”, blogs a lot (http://looksmarter.blogspot.com/) and explained in Brighton very detailed and with a lot of passion and the humor of a man from Texas what the differences between ASO and BSO cubes are (http://www.rittmanmead.com/files/edward_roske_essbase_internals.pdf). Funny and very interesting stuff though I did not know that ASO is existing, because I just worked with the older versions of Essbase yet. Need to catch up there some time very very soon.
Also a good one was the presentation of Mark himself, who was talking about optimizing the performance of the ETL routine for Oracle BI Apps. I followed this without even knowing how the BI apps look like – unfortunately I do not know of many installations in Switzerland / Germany / Austria.
John Minkjan (http://obiee101.blogspot.com/) completed the first day with a very complete presentation all around the Cache of OBIEE and there is a lot. Not only the well known Query-Cache, but also the presentation services cache and not to forget the browser cache. So, if you want to know something about read his presentation (http://www.rittmanmead.com/files/john_minkjan_cache_mgmt.pdf).
After John the legendary Venkat (he’s really the master of blogging around OBIEE and Essbase (http://oraclebizint.wordpress.com/)) held his presentation about Security integration between Essbase and OBIEE. This "geek" has developed a - as he said - “little” piece of Java code which makes the integration an easy catch. Follow his findings here http://www.rittmanmead.com/files/venkat_j_obiee_essbase_security.pdf. Unbelievable !
For those of you more interested in ODI / OWB melting together, there was an outlook how all this should happen from Craig Stewart (http://www.rittmanmead.com/files/craig_stewart_biapps_odi.pdf).
Also, there was a good one from Adam Bloom, who talked very informative around what to do, when a customer yells that performance of OBIEE suite is bad and what you can do to find out where it comes from.
Have a look, when working with performance issues in OBIEE this can help you find it faster (http://www.rittmanmead.com/files/adam_bloom_bi_server_diag.pdf ).
And on the second day finally I had the possibility to see next generation of OBIEE. 11g on the table, live in front of my eyes. It looks very good and just 5 seconds after the presentation started we had this humming “Woah” running through the audience. Mike Durran could have talked all day long about 11g and the new features, all of us would have followed his explanations with eyes wide open. But there were others to present.
And it was absolutely worth it. The approach from Emiel in his presentation was kind of different, but I liked it. Hopefully they can follow their strategy also when they have more data in the Warehouse. I think especially the Data Warehouse geeks among you will like his presentation. And if only to find out why it’s not the strategy you would follow / propose to a customer. http://www.rittmanmead.com/files/emiel_bockel_sun_stars.pdf .
Another very good presentation was all around Map integration in OBIEE. Maarten Jan Kampen, a guy from the Netherlands is actually in the middle of implementing that. A lot of customized Java / Java script and whatever else code in this solution. This is what I remember. Take a look: http://www.rittmanmead.com/files/maarten_jan_kampen_obiee_mapviewer.pdf .
Adrian was next, talking about global implementations of OBIEE, mainly focused on the translation features OBIEE provides for the repository and the presentation catalog, but also for the product messages themselves. Funny was that he also mentioned the BI Localizer, the tool I already used at a customer site to implement their language requirements. Check it out: http://www.rittmanmead.com/files/adrian_ward_globalization.pdf.
And then – in my opinion - the best presentation of the Forum was held by Tony Heljula. He talked about SOA integration in OBIEE and demonstrated live how to configure some examples. He demonstrated as if it is the most unimportant minor matter and in a very easygoing way. Great trainer this guy, I just met him last year at a partner event in Frankfurt, where he held the “Modeling the OBIEE repository” course, which I attended.
To be honest it was so interesting to see all the examples, where integration could make sense. Absolutely phenomenal – for me. But decide for yourself: http://www.rittmanmead.com/files/antony_heljula_obiee_soa.pdf
Wow, something totally new. Cube organized MVs, a new feature in Oracle OLAP 11g. Quite interesting, what Peter Brink showed us. But decide yoyurself. Here it is: http://www.rittmanmead.com/files/peter_brink_comv.pdf.
So, all in all this event showed me that it’s good to blog around something. I met a lot of people there and my network is bigger now and this is very valuable for me. But what makes the most is that it is a very good feeling to know that I blogged and that it is read by somebody and - at least sometimes - appreciated by someone out there – and not getting dusty somewhere in a library or so.
The event pretty much reminded me of the Trivadis Tech Event we have two times a year, even just for the OBIEE, Essbase and ODI world. Hopefully I can be at this Rittman Mead BI Forum again next year.
To all a good start in the week.
So long,
Andreas
Just a couple of weeks ago I ran into a situation at a customer-site. They have Hyperion Financial Management (HFM) already installed, but wanted to use both the softwares separately, but on the same server. Therefore I had to change some of the ports OBIEE normally uses.
For this I spent a little time with collecting all the ports OBIEE uses. Most of the ports I found out by doing the simple command netstat -a -b -v (you can simply pipe them into a file with netstat -a -b -v > usedports.txt in a dos-box) and by checking pretty much all of the configuration files. With the options -b -v netstat determines which is PID is the originator of the port usage.
I thought it could be of interest for you, so here they are:
| Port | used for | found |
| 5555 | Oracle IIOP-server port (Internet Inter-ORB Protocol) | internal-settings.xml |
| 5556 | Oracle IIOP-server SSL port | internal-settings.xml |
| 5557 | Oracle IIOP-server Client-Server SSL port | internal-settings.xml |
| 7000 | Discovery port for coordinator of Java cache | javacache.xm |
| 9127 | JMS Server port | jms.xml |
| 9700 | Monitor Controller port for Intra Cluster Communication with Cluster Controllers | NQClusterConfig.INI |
| 9701 | Monitor Server port for Intra Cluster Communication with clustered Analytics Server | NQClusterConfig.INI |
| 9703 | - Remote procedure call port of Oracle BI Server
- Outfacing port used for ODBC connections | NQSConfig.ini
NQClusterConfig.ini |
| 9704 | standard port for OC4J | instanceconfig.xml |
| 9704 | - used for Web Browser access, in Catalog manager etc. | default-web-site.xml |
| 9705 | standard port for BI Server scheduler | instanceconfig.xml |
| 9706 | Client controller part Clustering | NQClusterConfig.ini |
| 9710 | port for presentation services and ISAPI | isapiconfig.xml |
| 9810 | Java Host port | instanceconfig.xml |
| 23791 | Remote Method Invoker (RMI) port | rmi.xml |
| 23943 | Remote Method Invoker (RMI) SSL port | rmi.xml |
Hope, this helps some of you when wondering why that port is used and you cannot remember of which software. If I forgot something just let me know and I will add it.
So long,
Andreas
I am sitting in the train on my way back home from Munich. Just completed the survey for Mark Rittman's BI Forum when my thought came back to that one topic I wanted to blog about. So, here we go !
Have you also faced this screen in the left pane of Oracle BI Answers ?
A good description of what can be the reason, I do not know of any others. I would assume that in most of the cases the presentation catalog has simply been renamed. Sometimes there is a new version in the underlying business model of the presentation catalog, hence the guy modeling it decides wisely to rename also the presentation catalog.
The first option can of course also happen, but I want to dig into the second one.
I had this scenario at a customer and there it was not possible to rerename the presentation catalog back to the old name again. So, I thought about for a while and went the track right down the XML-path. And, yes, it was easier as expected.
For demonstrating that I changed the name of my catalog from "Sample Sales" to "Sample Sales 2".
Go to the advanced tab of the Answers report and take a look at the XML written there. You will find something like <saw:criteria subjectArea=""Sample Sales"">.(the " because of the spaces in the name). Replace the Samples Sales part with Sample Sales 2 and that's it, you see all your columns of the presetation catalog again.
Save the report and you are done.
So long,
Andreas
I am pleased to announce that I will represent Trivadis at the Rittman Mead BI Forum being one of the speakers in Brighton on may 14th and 15th this year.
Take a look at the agenda and decide yourself, I think it has quite interesting sessions: http://www.rittmanmead.com/rittman-mead-bi-forum-brighton-may-14th-15th-2009/.
There are only limited numbers, so it's like always: the early bird catches the worm.
So long and hope to see you there,
Andreas
Recently I went to the highly recommendable (sorry for this little ad !) Trivadis training "Data Warehousing with Oracle". As not other expected the coach had some really interesting informations for me.
Amongst other things one subject made me curious. When we went through the possibilities of documenting data models the teacher mentioned ADAPT as a defining language for modeling multidimensional data.
Hence I gave it a try and meanwhile I admit I tend to use it as often as possible. Also Trivadis has set up an enhanced usage of the inital approach, which is absolutely usable. In addition to that there is another extension for temporal aspects in data warehousing existing from Dr. Michael Hahne (See here.).
All of you might know that data modeling is tricky enough, but talking and discussing about that with your business users is even more tricky. Not to blame the business users for missing know how, but to be honest
business users and techies simply speak other languages.
So, here is some information around ADAPT, not pretending to be complete, but at least a beginning for everybody interested. The founder of this neat little piece of software is SymCorp (http://www.symcorp.com). On their homepage you can find a add-in for Microsoft visio, which provides you with the necessary ADAPT shapes. This add in can be downloaded from that page here. Under the same URL you can also find a whitepaper "Getting started with ADAPT". There might exist an PDF from Dr. Andreas Totok, who wrote about modeling controling information systems with ADAPT, unfortunately only in german, so I only mention it and do not provide a link.
Here the overview of available symbols for creating your own multidimensional models with Visio and ADAPT.
While going through this white paper and realizing the concept behind you will see that pretty much everything needed is available.Here is a look on how it can look like :
The main advantage of this modeling language is that the everybody seems to understand it intuitively, so it's easily possible to discuss multidimensional models within an audience with people from different departments, with varying skills, with the everybody- and the management level as well. It kind of unifies the people and brings team-decisions up to speed. An additional advantage is that every data modeler can implement the model based upon this documentation.
Have fun modeling around, I hope you like it....
So long,
Andreas
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
Mehr Beiträge
Nächste Seite »