OBIEE AGO and TODATE Series

Have a look a these two tables:

Let's create a MONTH_YEAR_AGO Column:

Have a look at the result:

....some thing is not right here....

Let's investigated the date dimension:

Looks alright? Have a look again without year and month:

We have a sorting issue here, so let's add a sorting column:

OK that's fixed, let's go back to our report:

Still a problem there, let's sort the chronological key:

That really fixed the problem!

Till Next Time

OBIEE Sort Order Column property

A customer had the following problem with his dates dimension:

The month year part of the date dimension wasn't sorted in the right way. He forgot to assign a sort column (format ('YYYYMM')):

The preferred result:

Till Next Time

OBIEE Let's Get Professional

Recently I was asked to have a look a some development work from some off-shore companies as part of a vendor selection. This triggered me to make a small random order check list of does and don'ts before your send your material to your customer. If you know any more, let me know and I will add them to the list.

• Don't do your development as an extension of the PAINT or SALES rpd. Start with a clean one.
• Replacing the Oracle logo with your own Company logo, sending in the SALES webcat as a Proof Of Concept, claiming 10k as costs, is not only very stupid, it's illegal / criminal!
• Do a consistency check before you send it to the customer.
• Don't do any customizations in S(K)_ORACLE10 directory, create a new one.
• Check which version of OBIEE your customer is using.
• Clean the RPD of test users.
• Remove any test material from the RPD.
• Start with a clean WEBCAT.
• Develop shared filters directly as shared filters, do not copy them.
• DO NOT USE other customers data in a Proof Of Concept for a new lead.
• If the customer gives you test data in separated XLS sheets, try merging them into one sheet, this will save a lot of time configuring ODBC connections.
• Don't go overboard with the aggregate persistence wizard. 400 aggregate tables on one fact table is plain stupid. If a customer really needs that, try selling them a product like ESSBASE.
• Install a version control system. (Don't forget to check in/out !)
• Develop your reports based on the customers screen size, most will still be on 17" (1024 x 768).
• Check your calculations against common sense: a €50,-- per year / per employee turnover should might be a good indicator that the calculation is wrong.
• Check your calculations against common sense: a .0004% annual growth 5 years in a row is very very strange.......
• Have one standard for graphs; 2D graphs are better "readable" then 3D.
• Keep your reports to one colour schema.
• Document your work!
• Try to avoid case 1=0 constructions in your prompts, use a proper LOV table.
• 27/11/08: Adrian Ward: When doing a presentation to the client, run through it first to cache the results. It will be far more impressive.
• 28/11/08: Bytus: Never try to recreate the Excel "requirements specifications", screenshots of old systems or Powerpoint mock-ups 1:1 at all costs.
  Work towards the best approximation which satisfies the actual clients needs (not his wishes) while bringing forward the strong points of OBI EE.
  (It can never be an Excel!)
• 03/05/08: Sunil: Above all be truthful and claim only what you know.
• 19/01/09: John Minkjan: Create different connection pools for different user groups
• 19/04/09: John Minkjan: Read Stephen Few: Information Dashboard Design: The Effective Visual Communication of Data
• 06/05/10: John Minkjan: Use display folders to organize your work.
• 28/06/10: John Minkjan: Don't use SYSTEM accounts in your connection pool
• If you are also the DB guy: http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html

Till Next Time

OBIEE Presentation server on IIS

Instead of using OC4J or the Oracle Application Server the OBIEE presentation server can also run on Microsoft's Internet Information Services (IIS). If your not running IIS and the OBIEE Presentation server on the same box, or didn't install IIS before the you installed OBIEE, here is a way to configure it:

Open the IIS Manager

Create a Web Site configuration on the IIS:

Click next

Enter a name for your web site configuration. Click next.

Leave to default values. Click next.

Enter the web site home directory, usually ..\Inetpub\wwwroot. Click next.

Leave to default. Click Next.

Click Finish.

Add the virtual directory's:

analytics => ..\OracleBI\web\app

Leave to default. Click next.

Click Finish

analytics => ..\OracleBIData\web\res

Leave to default. Click next.

Click Finish

Setting permissions "analytics"

Set execute permissions to "Scripts and Executables".

Check the Web Service Extension permissions for the saw.dll

Till Next Time

Edit: Jeff gives some info on using this on a 64 bit pltafrom:

http://it.toolbox.com/blogs/achieving-great-bi/obiee-on-vista-64bit-with-iis-70-29882

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 Federated Query's / Fragmented Query's

One of the power features of OBIEE is the usage of federated or fragmented query's. Being able to source different database at the same time can save you a lot of time because you don't have to reengineer your whole ETL every time a new system gets introduced.

Still it wise to put some effort in the way you map the tables in the repository, especially if you want to minimize the query response time.

Let's go back to out basic model:

As you can see this table only contains data after 2008. The data before 2008 is in the "old" database.

Making a federated / fragmented logical table

Step 1 import the "old" fact table into physical layer of the repository:

Step 2 make the correct joins in the Physical Layer

Step 3 Add the "old" table to the datasource of the logical table

Step 4 Fragment the data from the two sources:

Don't forget to set levels:

And even more important to enable the fragmentation:

It's good practice to also set the where clause(s):

Why?

During transaction from the "old" to a "new" system there is usually a grace period where both systems are kept up to date, if we don't "restrict" this "grace period" the numbers might not add up correctly.

Let's have a look at the result:

Optimizing the fragmentation

If we take a look at log of the previous query:

-------------------- Sending query to database named ORCL (id: <<8216>>):

select T46.LEVEL_01 as c1,
T46.LEVEL_05 as c2
from
DIM_LEVEL_05 T46
order by c2

+++Administrator:2d0000:2d0001

-------------------- Sending query to database named ORCL (id: <<8233>>):

select T26.D_YEAR as c1,
T26.D_YEAR_MONTH_DAY as c2
from
DIM_YEAR_MONTH_DAY T26
order by c2

+++Administrator:2d0000:2d0001

-------------------- Sending query to database named ORCL_OLD (id: <<8250>>):

select T1220.F_FACT_VAL as c1,
T1220.D_DATE as c2,
T1220.LEVEL_5 as c3
from
F_FACTS T1220
where ( T1220.D_DATE < '20080101' )
order by c2

+++Administrator:2d0000:2d0001

-------------------- Sending query to database named ORCL (id: <<8165>>):

select D0.c3 as c2,
D0.c2 as c3,
sum(D0.c1) as c6
from
(select T31.F_FACT_VAL as c1,
T26.D_YEAR as c2,
T46.LEVEL_01 as c3
from
DIM_LEVEL_05 T46,
DIM_YEAR_MONTH_DAY T26,
F_FACTS T31
where ( T26.D_YEAR_MONTH_DAY = T31.D_DATE and T31.LEVEL_5 = T46.LEVEL_05 and T31.D_DATE >= '20080101' )
) D0
group by D0.c2, D0.c3

You will see that the "old" data isn't picked up very efficiently. OBIEE fires three separated query's to the database and "stitches and summarizes" the data locally, while the "new" data is summarized at the database.

Use the database potential

OBIEE is NOT a database!

So all data manipulation / aggregation we can do at the database side is usually faster then bringing the data over to the BI-server. If the fact are in the "old" system then there is a big changes that it also contains the dimensions. If not ask your DBA to make a copy to that database either physical or as a view based on a database link.

We can import and map the "old" dimensions the same way as the "old" fact table.

Don't forget to set the levels:

Let's try a "border" line query:

From the log:

+++Administrator:2d0000:2d0003

-------------------- Sending query to database named ORCL_OLD (id: <<9994>>):

select T1220.F_FACT_VAL as c1,
T2134.LEVEL_01 as c2,
T1220.D_DATE as c3
from
DIM_LEVEL_05 T2134,
F_FACTS T1220
where ( T1220.LEVEL_5 = T2134.LEVEL_05 and T1220.D_DATE < '20080101' )
order by c3

+++Administrator:2d0000:2d0003

-------------------- Sending query to database named ORCL_OLD (id: <<9868>>):

select T2140.D_YEAR as c1,
T2134.LEVEL_01 as c3,
sum(T1220.F_FACT_VAL) as c5
from
DIM_LEVEL_05 T2134,
DIM_YEAR_MONTH_DAY T2140,
F_FACTS T1220
where ( T1220.D_DATE = T2140.D_YEAR_MONTH_DAY and T1220.LEVEL_5 = T2134.LEVEL_05 and T1220.D_DATE < '20080101' and (T2140.D_YEAR in ('2007', '2008')) )
group by T2134.LEVEL_01, T2140.D_YEAR

Nicely split into two non overlapping query's.

Till Next Time

This article is originally written for the Ciber knowledge blog.