Real Time Saver

We all have these projects were a bunch of excel sheets are made available to you by the business. “All the business logic is in there Have Fun” . Next you will spend days trying to figure out how stuff works. You can save a lot of time by using an excel plug in “trace” made by Christopher Teh Boon Sung: http://www.christopherteh.com/trace/.

It make great graphical overview on how all the cells are connected to each other. This can save you several days!

Till Next Time

OBIEE Excel count distinct error

I have a simple Excel sheet imported into OBIEE:

If I do a count it works fine:

But if I do a count distinct I get an error:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: 37000 code: -3100 message: [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'count(distinct T2207.[F_LETTER])'.. [nQSError: 16014] SQL statement preparation failed. (HY000)

This is causes by the fact that Microsoft Excel doesn't know the "COUNT DISTINCT" command.

You can fix this by deselecting the count distinct feature in the database properties:

Now it works perfect:

Till next time

OBIEE Changing XLS (Excel) data on the fly

• One the big strengths of OBIEE is the possibility to easily integrate Excel sheets into the repository. If you leave everything to the default settings you will find that you can’t edit the sheet “on the fly” without having to restart the OBIEE services.
  Here is a workaround.
  
  Step 1: Make the XLS workbook “shareable”
  

In Excel goto Tools > “Share Workbook”


In the “Editing” tab check the “Allow changes……..” box.

When prompted save the workbook!

Step 2: Set the connection pool settings:

Uncheck:
Require fully qualified table names
Shared logon
Enable connection pooling
Use multithreaded connections
Execute queries asynchronously

Check:
Parameters supported

Set:
Parameters supported to Repeatable read

Step 3:

Deselect the cacheable checkbox

Now when you have made a change to your Excel sheet you only have to press the refresh button in your OBIEE report:

Till Next Time

OBIEE Excel Import, prepping the data

One of our customers has an application which generates an Excell with combined data.

They wanted this to be imported into OBIEE, but couldn’t get OBIEE to see the data. They got the connection oke, but didn’t "see" the tables. This problem was caused by the fact that the generating application didn’t prep the data. It didn’t set the named ranges for the three tables.


Here is how you do it by hand:
Select a range:

Enter a range name and press enter!

Do the same for the two other tables:


Here is how you do it by VBA code:

Columns("A:B").Select ActiveWorkbook.Names.Add Name:="MY_TABLE_RANGE",
RefersToR1C1:= _ "=Sheet1!C1:C2"

When you now try to import the data into OBIEE you will see the three tables:

Till Next Time

Note: don't forget to disable the count distinct feature:

http://obiee101.blogspot.com/2008/11/obiee-excell-count-distinct-error.html

OBIEE Excel and slashes

Just a tip from my (time costly) experience when using the Excel plug-in. Try to avoid the '/' - forward slash (fi: costs / hr) in column names . Excel has sometimes trouble handling those columns if the column name is used in further (VBA) scripting. Basically it not a good idea to use any mathematical signs (+-/*^%#&) in a column name.

OBIEE for MicroSoft Office

When you try to log on to OBIEE for the first time from Excel, you are asked to define a connection:


The setup documentation and the default settings from OBIEE for MS-Office can put you in the wrong direction.

The port number is the port where your bioffice is running, default this is the same port as your BI-server (default) 9704.

Your connection could look like this: