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..

Thanks
Until next time.

Read More

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.

Thanks
Until next time.

Read More