Saturday, November 29, 2008

OBIEE AGO and TODATE Series

Have a look a these two tables:

image image

Let's create a MONTH_YEAR_AGO Column:

image

Have a look at the result:

image

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

Let's investigated the date dimension:

image

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

image

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

image

image

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

image

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

image

image

That really fixed the problem!

Till Next Time

Friday, November 28, 2008

OBIEE Sort Order Column property

A customer had the following problem with his dates dimension:

image

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

image

The preferred result:

image

Till Next Time

Thursday, November 27, 2008

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

Monday, November 24, 2008

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

image

Create a Web Site configuration on the IIS:

image

image

Click next

image

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

image

Leave to default values. Click next.

image

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

image

Leave to default. Click Next.

image

Click Finish.

Add the virtual directory's:

image

analytics => ..\OracleBI\web\app

image

image

Leave to default. Click next.

image

Click Finish

analytics => ..\OracleBIData\web\res

image

image

Leave to default. Click next.

image

Click Finish

Setting permissions "analytics"

image

Set execute permissions to "Scripts and Executables".

image

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

Saturday, November 22, 2008

OBIEE Excel count distinct error

I have a simple Excel sheet imported into OBIEE:

image

If I do a count it works fine:

image

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

image

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:

image

Now it works perfect:

image

Till next time

Friday, November 21, 2008

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:

image

image

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:

image

Step 2 make the correct joins in the Physical Layer

image

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

image

image

Step 4 Fragment the data from the two sources:

image

image

Don't forget to set levels:

image

And even more important to enable the fragmentation:

image

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

image

image

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:

image

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.

image

image

Don't forget to set the levels:

image

Let's try a "border" line query:

image

image

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.