All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Monday, July 4, 2011

Convert a date type into year&month format

I am sure some other people have blogged this somewhere, but I'd like to add this to my list of topics because I have encountered this type of requirement quite often.

Basically, let's say we have a column called 'shipped date' that has data like 'YYYY-MM-DD'. If we want to change this data so it displays only year and month, like 'YYYY-MM' or 'YYYYMM', then how would we do it?

First of 'YYYY-MM-DD' is date datatype but once we change it to 'YYYYMM', it no longer is date anymore. Therefore a database function 'To_char' is needed for such a conversion.

In OBIEE, there is a function 'evaluate' that can help executing database functions within the Admin tool.

Therefore, the entire statement is something along this line:
EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "Purchase_Facts"."PO_DATE", 'YYYYMM')
if that's all we want to do.

Here, %1 and %2 are the number of parameters we are passing. We are dealing with only 2 parameters in this case: year and month, so we have %1 and %2.

So having followed the syntax, we define this statement in physical column mapping, which is in the LTS of the logical table in BMM layer, as you can see in the below testing sample:

This gives the physical definition of the column to be 'YYYYMM' instead of the original 'date' data.

Try it and test it for yourself..

Until next time.

Saturday, July 2, 2011

IBOT: Difference between the two Data Visibility setting

I know this is a very basic point about setting up IBOT, I still think it's worth noting on the blog for those who only knows the difference conceptually but not practically.

As you can see, there are 2 optional for 'Data Visibility' setting when creating any IBOT:

So what exactly does IBOT do when the setting is 'personalized' and when is not 'personalized'?

Personalized setting: The IBOT will execute the report and delivery its content for each individual recipient in distinct sessions. In other words, if you have 5 recipients for this IBOT, then when the IBOT is running, in the session monitor screen, you will see 5 sessions with the exactly same SQL being executed, each for one of the five recipient on the list. The feature of allowing subscribers to customize IBOT is enabled.

Not personalized setting: The IBOT will execute the report and delivery its content to all recipients at one time, the session will run as the userid that owns this IBOT. In other words, in the same session monitor screen, you will only see the report executed once and only, and the userid that the session belongs to will be the creator of that IBOT. On the other hand, the feature of allowing subscribers to customize IBOT is disabled.

So, knowing the difference, the rest is the business decision to make. Depending on the environment, hardware and software resources, and the demand of the BI system, if having too many query sessions running on the server box at the same time is not a good thing, then avoid personalized IBOT setting, especially when dealing with many IBOTs of similar requests. If you want to give certain users the ability to customize the IBOT because it is more important, then 'personalized' the setting. You just can't have both in one single IBOT.

Until next time.
Related Posts Plugin for WordPress, Blogger...