Top-N per Dimension in Oracle BIEE

How to show Top 5 number of sales per city in Oracle Business Intelligence (EE)?

OOT: Karena blog ini didedikasikan untuk bangsa Indonesia [backsound lagu ‘Maju Tak Gentar’, dengan semangat ‘ 45 yang berapi-api] maka penjelasan mengenai tip dan trik Oracle BIEE mulai dari posting ini dan seterusnya ditulis dalam bahasa Indonesia [halah, ngaku aja bahasa Inggrisnya payah!!!]

Kali ini saya akan membahas cara menampilkan TopN per dimensi di Oracle Business Intelligence. Jadi setelah kebingungan demi kebingungan dalam mencari TopN per dimensi 1 per dimensi 2 [nah loh], munculah ilham [dengan sedikit wejangan mbah Google] yang akan dijelaskan berikut ini.

Sebelumnya, mungkin point 1 sangat dasar sekali tapi saya rasa perlu untuk dijelaskan. (Report berikut menggunakan Subject Area paint)

1. Cara menampilkan TopN
1. Di tab Criteria tambahkan filter pada measurement

2. Ada 2 cara

i. Pilih Operator is in top dan isikan value yang diinginkan misal 5 (untuk menampilkan 5 teratas)

ii. atau tambah measurement Units lagi kemudian tambahkan Function aggregasi TopN

3. Hasil dengan menggunakan pivot table… Jreng jreng….

note: jike menggunakan cara b.ii, simpan kolom measurement yang menggunakan function tersebut di Excluded area

2. Cara menampilkan Top 5 per Region
1. Dengan menggunakan cara b.ii tadi, di function aggregasi TOPN tambahan by nama_kolom_dimensi

2. Dan yupe, semudah itu untuk dapat hasil

Note: kolom region ditempatkan di Section area untuk mendapatkan hasil seperti di atas

3. Top N per dimensi per dimensi lainnya
1. Bagaimana jika ingin menampilkan Top 5 UPC per Region per Market? Sejauh ini saya masih menggunakan cara workaround [wallahu’alam itu cara yang terbaik atau bukan, still researching] yaitu menggabungkan (concat) dimensi pertama dan dimensi kedua

2. Sehingga menghasilkan report seperti berikut

4. Dan seterusnya..

Meskipun blog ini ga (mungkin belum) ada yang baca, tapi semoga tulisan ini nantinya bisa bermanfaat. Cheers!!!

How to Solve Out-of-Memory Error in Oracle BIEE

Dengan berbaik hati setelah segenap daya dikerahkan untuk mengetahui cara menyelesaikan error yang satu ini, here is one of the solutions you may try to solve the error. Setelah surfing [walaupun dengan keterbatasan jaringan ke Internet] seribu malam [halah ga deng seharian], akhirnya kita [tetap] memilih insting dan ide gila untuk ngotak ngatik sendiri demi menemukan solusinya.

Jadi begini keadaanya:
- Table yang mau diretrieve dari Oracle BIEE nya itu juta-jutaan records [thanks to database yang bisa ngehandle data segitu gede]
- Database yang dipake Greenplum
- Jadi koneksi Oracle BIEE ke Greenplum adalah PostgreSQL ODBC 8.03.01

Dan errornya:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: S1000 code: 1 message: Out of memory while reading tuples.; No query has been executed with that handle. [nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)

Dan salah satu solusinya adalah (jreng jreng jreng):
- Ganti ODBC driver ke versi yang lebih pas dengan Oracle BIEE (kalo kita sih ganti ke 8.01.02)

I’m trying to figure out other possible solutions, tapi untuk sementara cara ini berhasil. Meskipun belum tau pasti root causenya apa, ya pilihan terbaik sejauh ini ganti ODBC driver ke versi yg lebih cocok dengan Oracle BIEE nya.

Dari segi performance ga jauh beda antara versi yang sebelumnya kita pake dengan versi yang skrg kita pake meskipun downgrade, dan setelah di cek di Oracle BIEE nya pun aman aman aja, dan yg penting error out-of-memory-nya udah ga ada.

Kalo mau share tentang masalah ini silahkan kasih komentar.

Oracle BI Catalog Merging

Ok, now let's talk about migrate catalog of Oracle BI. There was an issue when I wanted to 'merge' or migrate catalog from one server to another. Here some ways to merge the catalogs


• Upgrade catalog
• Archive Unarchive
Details:
a. Find active catalog:
- In folder OracleBIData\web\config, find instanceconfig.xml file.
- Active catalog is referred to in tag .

b. Copy the catalog to desired location (for merging). In our case we should have two offline catalogs; development and production. It is better to put both catalogs on the same machine, related to performance when merging.

c. Choose one of the following ways to merge

d. Upgrade catalog
- Open Catalog Manager
- Open offline one of the catalogs


- Select Tools then Upgrade Catalog


- Choose Original Presentation Catalog and Modified Presentation Catalog. If one of the catalogs is original (has no changes), choose the catalog for Original Presentation Catalog, if both of them have any changes, choose any of them to be the Original Presentation Catalog.

- Notes: Upgrading the catalog only add what is previously not exist in original, any changes on modified catalaog of existing items will not be applied


e. Archive
- Open 2 Catalog Managers
- Open offline both catalogs
- On one Catalog Manager, select folder to archive, then File | Archive.



- On the other one select the folder where we want to unarchive, then File | Unarchive
- Notes: Archiving cannot be performed from root folder, add what is previously not exist in original and any changes on modified catalog of existing items will be applied

OK that's for now.

Why are there many database connections opened by Oracle BI?

Okay, let's start blogging!

Currently I'm in a project that requires me to be able to use Business Intelligence tool: Oracle Business Intelligence Suite Enterprise Edition. Actually it was Siebel's but then -as some big companies love to do- Oracle 'take' it. Oracle, yeah it's expensive! So from that time me and my friend have been trying to learn it and here I want to share.

Let's go to the first issue I want to share.

Why are there many database connections opened by Oracle BI?

If you notice in your database activities that there so many connections opened by Oracle BI but they do nothing (idle). This becomes a problem when DBA(s) limit the connection of user for BIEE application, and they shout at you that BIEE users open connection without doing anything (you should be grateful if your DBA is patience).

Connection idle (opened by Oracle BI)

Actually Oracle BI enables us to use one connection for more than one request. The way it does is by remaining open after one request completed, so if you run another request it won’t open another connection instead it uses the remaining one.

You can set the time for Oracle BI to remain open, by setting Timeout property in database properties options. To change the properties you can follow these steps:

1. Open rpd you want to set in Administration tool
2. On the Physical Layer, right click the connection pool

Connection Pool

3. You can find Timeout property

Connection Pool Properties Dialog Box

4. Set the connection timeout for the connection

Note from help

Setting the timeout depends on your need. Ask your DBA also to have discussion how long it should be specified.