OBIEE: Default Answers Template? After trying out the lazyweb method of search (aka Twitter) and not getting much help, I resorted to help at the OTN OBIEE Forum. It's not Twitter's fault, I think this problem was a bit too complex to describe in 140 characters.

Here's the post on OTN. I started to get nervous too, I posted on Friday and hadn't had a response...until today. 3 days? Man...that's way too long!

Here's the short of it.

Our reports were coming out funny. Dimension column headings had one style and the Fact table column headings had another.



Using Firebug, I could easily isolate the sections.

On the Dimension column, the definition looked like this:

<th  
  class="ColumnHdg" 
  style="background-color: rgb(231, 231, 247); font-size: 9px; color: rgb(0, 51, 102);" 
  scope="col" 
  dir="ltr">Product Desc
</th>

The Fact table column was defined as:

<th 
  class="ColumnHdg" 
  scope="col" 
  dir="ltr">Basis Amount
</th>

Note the style attribute...that overrides any class settings. Very annoying.

I thought it would be relatively simple to fix. I worked with custom messages before, this had to be similar. So I began to "grep" the messages directory

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"background-color" *.*

Nothing.

How about looking for the name of the class, ColumnHdg?

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"columnhdg" *.*
messages\criteriatemplates.xml
messages\formattemplates.xml
messages\mktgadminuitemplates.xml
messages\mktgcommontemplates.xml
messages\mktglistformattemplates.xml
messages\mktgsegmenttemplates.xml

So I start with criteriatemplates.xml and find the reference to columnHdg (just now realizing that the case doesn't match...oh well). That was part of the WebMessage kuiColumnFormulaEditor. So I searched for that...

You see where I'm going.

That lead me to the javascript files (of which there are tons). Nothing...not a single thing that could possibly be adding this style attribute.

That's when I mapped the dev server drive to my local computer and opened up WinMerge

I then began to compare every single file in both the msgdb and res (javascript) folders. I would then compare the files that were different to see if that could be the cause. Still...nothing.

I had looked in the webcat before, but couldn't find anything of global significance. I was headed back there though since I had lost hope with custom messages and/or javascript.

Then I got a message from the OTN Forum Administrator...could it be? Looked at the name of the person who answered it first, John Minkjan...sweet!

Looks like you forgot to reset the OOB settings when installing OBIEE:
have a look here to reset them:
http://obiee101.blogspot.com/2009/02/obiee-editing-system-wide-defaults.html

Click through, follow his instructions, bounce the server and voila!



As of this writing, I don't know what OOB stands for; I could make something up, but it probably wouldn't be appropriate for this family site.

Thanks John!

Labels: answers, obiee, presentation

  ¶ 3/07/2010 04:31:00 PM   4 Comments Links to this post

  OBIEE: XML File as Data Source I received a question in the form of a comment on using XML files here. Fittingly, we were discussing this exact topic yesterday and I decided I would try it out.

So here goes.

You'll need 2 files: a XML file and a XSL stylesheet (which defines the XML file). I found said files here. I'll also provide them here (cdcatalog.xml) and here (cdcatalog.xls).

In the Administration tool, go to File --> Import --> from Database



When you prompted to select a datasource, select XML and the window should look like this



For URL, browse to the location you saved cdcatalog.xml, do the same for XSLT



Hit OK and you'll be prompted with the Import dialog



Import the "table" and you're done.

Labels: howto, obiee, rpd

  ¶ 3/03/2010 03:48:00 PM   1 Comments Links to this post

  OBIEE: Dynamic Variables Apparently this is a 3 part series, Part I, OBIEE: Text File as Data Source and Part II, OBIEE: Create Repository Init Block.

Finally, to the ultimate goal, dynamic session variables. Ideally, these would be repository variables, but since I'm using the row-wise variety, that is not possible (as of 10.1.3.4). Once a day, or some other relatively long time frame would be sufficient.

Anyway, there's an occasion where dynamically creating variables might be needed or necessary.

To recap the problem

Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.

The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.

In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.

A few people did chime in, Pete Scott suggesting using the a command line utility detailed here. Christian chimed in that I should use it at my own risk and rnm1978 said this problem has not been truly solved yet.

The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seen

I need to look up that phrase as I'm certain it's peculiar to my brethren across the pond.

Finally, the meat of the post. Here's my table:

OBI@TESTING>@desc obi_variables
 Name                           Null?    Type
 ------------------------------ -------- -------------
 VARIABLE_NAME                  NOT NULL VARCHAR2(50)
 VARIABLE_VALUE                 NOT NULL VARCHAR2(250)
 VARIABLE_TYPE_CODE             NOT NULL VARCHAR2(30)

I'll throw a few records in there for fun (and to obviously demonstrate).

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'TNS_TESTING',
    'TESTING',
    'TNSNAME' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'PW_TESTING',
    'TESTING',
    'PASSWORD' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'URL_TESTING',
    'http://localhost:7777/pls/apex/f?p=101:1',
    'URL' );

TYPE       VARIABLE_NAM VARIABLE_VALUE
---------- ------------ ------------------------------------------
PASSWORD   PW_TESTING   TESTING
URL        URL_TESTING  http://localhost:7777/pls/apex/f?p=101:1
TNSNAME    TNS_TESTING  TESTING

For demonstration purposes, I am going to create a new database in the Physical layer and add a connection pool called obi variables pool. From the previous post, I'll be using the values that I brought in through the text file. The only difference is that I added username...just in case. To sum that up, I'm bringing in the TNSNAME entry, the USERNAME and the PASSWORD.

Your connection pool should look like this:



The password column is the same format as the others, VALUEOF( INIT_PASSWORD )

You'll have to re-enter that when you save.

Now I'll be creating an init block using this connection pool. I won't drag you through that entire process again (your welcome). This is a Session Init Block as opposed to the Repository Init Block in the previous example.

The only difference is that I will be using a row-wise variable. I'll get to that in a second.

After selecting the obi variable pool connection pool, entering the following SQL statement into the area provided:

SELECT variable_name, variable_value
FROM obi_variables

.In the Edit Data Target area, select the row-wise initialization variable. Select OK.

Test it and voila! You now have dynamic variables in OBIEE.

Labels: howto, obiee

  ¶ 3/01/2010 10:30:00 PM   0 Comments Links to this post

  OBIEE: Single/Detail Record View Recently, I've been searching for a Single Row/Detail type report in Answers. Sadly, there isn't one so named.

Here's what I could find in Answers:



Nothing jumps out at me, so, Twitter to the rescue again



Within minutes...I had an answer from Mr. Christian Berg. What was that answer? The Narrative View.

Here's the screen for the Narrative View:



According to Mr. Berg, you reference the columns using the @1 (@2, @3, etc) syntax, the number representing the column number. So I tried that



Run it...



That's not what I want...

OK, so let's see if I can add some HTML to it. First I check the Contains HTML Markup box and then I enter in the following in the Narrative Text Area:

@1<br>

@2<br>

@3<br>

@4<br>

@5<br>

@6<br>

@7<br>

@8<br>

@9<br>

Here's what it looks like:



Better, but not exactly what I want. Now that I know I can use HTML though, it should be very easy.



Voila!

Labels: howto, obiee, reports

  ¶ 2/28/2010 11:55:00 PM   0 Comments Links to this post

  Random Things: Volume#16 EBS Challenge
John Piwowar wrote the first part of the EBS Installation Guide back in December, since then, it's become a runaway success. I hadn't realized how so until recently when I was looking at Google Analytics and it came it at number 19, for all time. Now it's up to 13 and with the addition of it to the front page, it should only go up faster.



I've never had a post that was this popular so I thought it fitting that John should get the recognition that he deserves. If you decide to take part and then write it up, let me know and I'll link you up to The EBS Challengepage. I think there are 4 people that have completed it so far. I'll even give you space here if you don't have your own blog.

Travel
Got the best color car ever this week.



OBI EE
For some reason my original, Learning Oracle Business Intelligence (OBIEE) post, has been near the top of the charts for multiple search phrases.So I did what any sane person would do, I updated it with some new information. Not really sure if it's new, but it's got more. If people are finding it, I might as well update it as often as possible.

Labels: ebs, jpiwowar, obiee, random

  ¶ 2/28/2010 10:06:00 PM   0 Comments Links to this post

  OBIEE: Create Repository Init Block My post earlier today, OBIEE: Text File as Data Source , was the first part of my overall goal.

Here's what I'm trying to do. Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.

The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.

In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.

We've pulled the text file into the RPD already. Now, I need to create 2 repository variables to hold these values. I open up the Administration tool, go to Manage then variables. You should see this:



Then follow this picture to create a new Initialization Block



Name your Initialization block init_block_test



Leave the scheduler stuff alone for now, click on Edit Data Source



First, browse for the connection pool



Select the get_local_password (yes, the name mysteriously changed from the default Connection Pool to "get_local_password," it makes sense doesn't it?).



In the Default Initialization String text box, enter:

SELECT username, password 
FROM test.txt

You can test it if you want, but I'm saving it for the final step.

Your Initialization Block Data Source should look like this



Now select Edit Data Target



Which looks like this



Select New and enter INIT_USERNAME in the Name text box and make the Default Initializer 'USERNAME' (in single quotes).



Do the same thing for the password column, INIT_PASSWORD and 'PASSWORD'

Now you are back at the main screen and the Test button (lower left hand corner) should be enabled. Select it.



One final test...let's see if we can access these in the presentation layer.



So it's a success right?

After I was done, I began to think..."You can access the repository variable from the presentation server"

Ummm...that's not good. Double checked the properties of both the Initialization Block and the Variables to see if there's a way to lock it down...and there's not. Because of this "small" little security issue, I'm not completely sold. It is a proof of concept, perhaps it should stay there.

Labels: howto, obiee

  ¶ 2/24/2010 10:17:00 PM   6 Comments Links to this post

  OBIEE: Text File as Data Source I have a requirement to pull data from a text file. OBI has the ability to pull from (just about) any database, but can it pull from a text file? Of course.

First I'll create a simple file, call it test.txt. In the file I put

cjustice testing

that "space" between cjustice and testing is actually a tab character (usually represented as -> if you have view formatting turned on). Knowing that it is a tab will be important shortly. Save the file to your root folder, i.e. c:\test.txt

Next up, go to your ODBC Datasources:

Control Panel --> Administrative Tools --> Data Sources (ODBC)

When that opens up, navigate to the System DSN tab and click on Add.

You'll be prompted to choose a driver, select the Microsoft Text Driver (*.txt, *.csv)



Click on Finish.

Next up, you should see the ODBC Text Setup screen, click on the Options tab to expand the window.



It should look like this:



Uncheck the Use Current Directory checkbox (default is usually c:\windows\system32):



Then select the Select Directory button and navigate to your root folder, or the place where you saved the test.txt file. You should see your file, greyed out, in the left hand pane.



Now, at the bottom of the window, uncheck Default (*.*) which should enable the Define Format button



Select the Define Format. For the Format drop down, choose Tab Delimited. Rows to Scan, in my case, will only be 1. You can leave it at the default if you want though.

After you have done that, the Guess button should be enabled for you, go ahead and click it.



See F1 and F2? Those are your columns. Let's name them something meaningful so we can easily reference them later.

In the Name box, it says F1, enter in USERNAME and click on Modify (the button to the right):



Do the same thing for the F2 column, name it PASSWORD.

It should end up looking like this:



Select OK and then select OK again in the ODBC Text Setup screen. You should now see your new data source in the System DSN window.



OBI EE Admin Tool
Now, we need to pull this new data source into the RPD. Open up your RPD, go to File --> Import --> from Database

You'll see this window, go ahead and select your new data source:



You'll be prompted with the Import window, expand the c:\ drive and then find your file



Select Import (at the bottom), wait for it to complete and then close the window. In your physical layer you should see a new "database" with the name "text_test" (or whatever you named your data source). Go ahead and expand that to see what you've got:



To verify, right click on the table "test.txt" and select View Data.



All done.

Labels: howto, obiee

  ¶ 2/24/2010 05:30:00 PM   5 Comments Links to this post

  OBIEE: Call SAPurgeCacheByDatabase So the reason I am investigating the ODBC Function extensions is that we are trying to tie this into our existing ETL process. After load, and within a designated time frame, we'd like to clear the cache for a given set of tables. Fortunately, all those tables exist in a single database. Unfortunately, I'm wasting your time by putting these into separate posts. :)

First, nothing up my sleeve.



So you understand the mapping, I made a pretty picture for that (it's not readily available in the cache manager)



I go off and run the reports (no need for screenshots of those).

Refresh the cache manager



So now I have cache entries for both "databases" (in quotes because they are actually the same physical database, just 2 separate connections).

I then create the SQL file and put the following:

CALL SAPurgeCacheByDatabase( 'local-sh' );

Then I just follow the same procedure as SAPurgeCacheAll()

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s sapurgecachebydatabase.sql

The output is almost identical as well

-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeCacheByDatabase( 'local-sh' )
CALL SAPurgeCacheByDatabase( 'local-sh' )
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
RESULT_CODE  RESULT_MESSAGE

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
1            [59118] Operation SAPurgeCacheByDatabase succeeded!

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
Row count: 1
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------

Processed: 1 queries

And voila! The Sales cache has been cleared.

Labels: howto, obiee, tools

  ¶ 2/22/2010 10:08:00 PM   0 Comments Links to this post