How to report data dynamically based on user's personal time zone setting -- Part 1

Hello again.

Ever since the introduction of global time zone concept, the information that business want to see has a whole different meanings. The transaction with certain amount took place at certain time means the world of difference if considering the time zone in where it occurred. Therefore, viewing data at user's preferred time has always been requirements. One of the trivia situation where time zone matters a lot is when it comes to stock markets. An user in California looks at his stock at 10:00 am in front of his office, he should know that whatever he sees happen at 1: 00 PM  NY time.

Therefore, the question comes when reporting in OBIEE that, at what specific time did this transaction occur? If an user is based out of Japan, he is likely to change his locale settings to Tokyo time and therefore, the transaction that he cares about show display in Tokyo time even if the transaction itself happened elsewhere and recorded at a different time zone. So how do we implement this kind of reporting capability in OBIEE?

Without thinking too much, we intuitively think we should add time zone information into the user table (If external authentication is used) and create session variables to capture the time zone and add the value to the conversation. At a high level, this may work although I haven't tried, but the issue is that the users can change their personal locale preference whenever they want and they should expect different results. Their changing locale doesn't trigger the same update at the DB level, therefore, it won't reflect the update. Therefore, this is not ideal.

The most simple and straight-forward way of changing the display of date & time in reports is by modifying the instanceconfig.xml file by adding the following entry:

- 0

Having done that, it will show you the difference of displaying date and time base on user's locale preference. We can see from below that we have a report with default locale setting and data being displayed is at GMT time (the time where OBIEE server machine is):

Now, change the time zone to Bangkok time, which is GMT + 7:

Now refresh the dashboard and the displaying date and time changes. The difference is perfectly 7 hours forward:

So now the job is done right? That's what we would think and I wish it was that easy so I didn't have to put "Part1" in the title of this article. You see, although the display changes perfectly correct, there is no actual date and time conversion happening at the query level. In other words, the query that is fired to the DB will still pick up date at default time zone, its just when it displays at the presentation, it changes accordingly.

So why is this a problem? Let me illustrate with the following example:

Let's say we have the following reports displayed with UTC time and users want to look at data by selecting the date and the hours where transaction is recorded. So let's add hour numbers into the report:

So far, the hours agree with the time stamp column, this assures that the correct hours are being filtered against. Now let's change the locale back to GMT + 7 Bangkok time and refresh the dashboard:

As you can see, now the hours are still showing what was in the previous time zone, but the displayed time stamp has been changed into Bangkok time. You see the problem?

Let's say the Bangkok user looks at this report and see the transactions occurring at hours between 2 PM to 3 PM his local time, and then he wants to look at all of the records between 2PM and 3PM, he will then change the filter value to 'between' 2 and 3. Then he is going to get the wrong record. Because without actual data level conversation, in the above example, if the user want to see the data at 2pm Bangkok time, he will have to enter 7 in the filter instead of 3. How would he know?

Therefore, something more needs to be done in order to create a truly internationalized dashboard.

I have figured out the way to do it, although it is not working 100% perfect, it is definitely very good.

I will continue on the solutions I use in the next post.

Stay tuned!

Read More

Time series measures -- ago, to_date function for trending comparison

Hello again

One of my audience brought up an interesting question a few days back. Below is the message:


Hi,
Please give me a solution for the below scenario.
i have pulled concept name, date, sale value(measures). my requirement is that the report should show sale value based onconcept name and current_year date and last year date of CY date' date like(12-sep-2012 & 12-sep-2011). It is like
concept name, CY_date, LY_date, Vales_Val_CY_date,Sal_val_LY_date. can i proceed with presenation variable or any other way. please give me solution ASAP.

Thank you.

For what I am understanding, which could be wrong by the way, the requirement is to compare the measurements taken over the time period over different years to see the trending statistics. This is pretty common in retail sales (I used to work as a part-time at shoe store at one point) that the business want to see the sales amount for certain products in March 2012 and then they want to see the sales amount for the same product in March 2011, 2010 etc to see a trend.  So ideally, the 2 fields should be side by side on the same report. It can be even trickier that when we are still in the middle of March, the total sales amount should be a month-to-date total because March isn't over yet. Then at the same time, they want to look at the equivalent month-to-date total last year. Since March 2011 is over, and what's stored in the database is likely the total sales of entire March 2011 since they do have the conclusion there. This comparison would be wrong. So we need to figure out a way go back in time and get the prorated equivalent period of the current year and get the result.

Now in OBIEE, we all know that we have a way to implement time series report by utilizing ago and to_date functions. In this case, we will continue to do so with a few expansions. So I am going to skip the details on how to set up the rpd for time series calculation by assuming the Date hierarchy is created with levels and keys properly defined. So let's get to the point.

Taking the above-mentioned scenario as an example, so I want to create a measure that gives me the current month-to-date sales total. Always, we have to consider the scenario when users want to run the report at any point in time during the month.  So in the below image, beneath the retail sales amount is MTD Retail Sales Amount, which is a pretty standard.




Now the tricky part is the second measure that I am about to create. This measure should give me the same time period equivalent of last year. A simple Ago function will work ONLY if we are looking at previous year's month totals. It WON'T work if user is querying during a given day before current month end and want to see the total amount over the same number of dates last year. Therefore, we have to do it differently here.

First of all, we do need to use the ago function, but it won't be on 'retail sales amount' column for the reasons stated above. Therefore, it has to be taking the result of 'MTD Retail Sales Amount' that I created earlier and get the equivalent 12 months ago. So it will be Ago(MTD Retail Sales Amount, Month, 12).

However, this is not going to be enough because the to-date function is going to add all of the days from the past up until now. So if we are talking March last year, then we don't want to add April, May, June of last year into the equation. Because changes are, the date dimension stores dates way ahead into the future, if we are in March 2012, that means April 2012 has no sales yet and therefore the users may not want to see the equivalent of April in 2011. Remember, April, May, June of 2011 will have to be based on the summary of sales made over the number of days equivalent to how many days are into April 2012, not the entire month.

So, this means a filter will have to be apply into the expression. We have to make this formula return data that are only up to current month. Hence, a dynamic variable will have to be created to capture the current month. Ideally, it show return data in the format of yyyymm and it should be numeric data type.

The statement is like so: Select To_number(to_char(sysdate,'yyyymm')) from dual

Now we have that, let's incorporate this filter logic into the expression of 'Last Year MTD retail sales amount'. This gives me the final expression:

FILTER( AGO("inSight Smart Mobile Backhaul"."LSP Facts"."MTD Retail Sales Amount", "inSight Smart Mobile Backhaul"."Date Dimension"."Month", 12) USING "inSight Smart Mobile Backhaul"."Date"."Year Month" <=  VALUEOF("Current_Month"))

A side note: I don't like to do ''<=" comparison on varchar datatype.

In some projects, month column can be stored as a varchar with the format like '2012 - 03'. If that's the case, just convert it to number by removing '-', or create a new column for it and use that in this formula. To convert into numeric datatype, just use replace function: cast(replace(column name,'-','') as integer)

Anyways, that's beside the point.

So after putting everything together, let's see what kind of report it looks like:

Woops, I have to confess that I messed up a little bit. Not because the implementation is not working, but because I purged the data before writing up this blog post. Therefore as you can see from the report, all we have in my testing database is the data of 2012 Sept. Therefore, same time last year sales amount is blank. I should've been careful next time. However, I can assure you that the data came out right from the same report before I purged everything.

Not convinced?

Go ahead and get it a try, experiment it!

Until next time

Thanks

Read More

case study --- How to pass values to different dashboard prompts interactively part 2

In the previous post i have discussed the requirement and passing values from 1 dashboard to another dashboard as we navigate, and also the issues and challenges I was facing in this particular. In this post, we will continue exploring the solutions.

So like I said before, I need to find a way to fool OBIEE into thinking these prompts are the same first of all. The first difference I see is that the date prompt in one dashboard is using a between operator while the other prompt is split into 2 date prompts having equal operator for each. So I decide to split the first prompt into 2 and define the same expression in the prompt to make it the same as the other prompt.

Of course, I have to make sure that the functionality of the original prompt is kept the same. I am doing it by assigning presentation variables to both new prompts.

Now, moving onto the prompts on dashboard 1 and see what we can do about that. Since there is only 1 date prompt on dashboard 1, I will not make an effort to further configure it. That will, only 1 date prompt value from dashboard 2 will be passed to dashboard 1.

The main effort here is to configure the hour prompts on dashboard 1 so that it will be treated the same as dashboard 2.

Being aware of the fact that the hour prompts in dashboard 2 needs presentation variable (time scale granularity) to determine its content, we will have to make it the same in dashboard 1. But we have to do it in such way that these prompts still present the same values even if there is no presentation variables being chosen because it doesn't need it at all.

For the start hour prompt, I have entered the following expression:

Starthour: case when '@{varDateSelect}' = 'Hour' then Date."Hour Number" when '@{varDateSelect}' = 'Day' then null else Date."Hour Number" end

So in this case, when vardateselect is unspecified, the content will still be Date."Hour Number defined in the 'else' clause.

For end hour prompt, I have entered the following expression:

end hour: case when '@{varDateSelect}' = 'Hour' then Date."Hour Number" when '@{varDateSelect}' = 'Day' then null when 1=1 then Date."Hour Number"  end

This is the same logic as start hour. The only difference is that I have 'when 1 =1' as a condition. This is to fool OBIEE into thinking that these 2 prompts are different so that they each will allow different prompt values at the same time without being synchronized by OBIEE. Yet, both expression will yield the same result.

The below 2 expressions will also be defined in the hour prompts on dashboard 2. That way, OBIEE will look at them as the same, mean while the functionality of the prompts on dashboard 2 will remain the same because the prompt values will always be according to the vardateselect variable, which means the 'else' condition will never be used.

Now, let's create some reports to deal with passing the LSP values:

This report will return list of LSPs and the column header and value will be holding the navigation path to dashboard 1. More importantly, the default filter value for this report is 'Not Found', which means initially the report will always return nothing until a new prompt value being passed.

Now lets put it all together on dashboard 2:

Here as you can see, I have defined the guided navigation on the section that holds this LSP report. That way initially, this report is invisible from the dashboard like illustrated from below:

Now let's test the feature by selecting the date and hour range, LSP names on dashboard 2 and click 'go'. You should see that the LSP report being rendered and the results will show shortly:

The below shows the reports based on the selected values from the prompts on dashboard 2. You can see the LSP report having both header and data highlighted. So the idea is that by clicking on the header, all of the LSPs coming from the multi-select prompt will be passed along to dashboard 1. If clicking on a specific LSP, then only that LSP will go through along with the dates and hours.

So let's check it out by clicking and we get to dashboard 1:

As you can see, all of the dashboard prompts on dashboard 1 are filled with values coming from dashboard 2, because OBIEE thinks they are all the same so accepted the interaction.

Of course, another way of defining the navigation is by embedding a navigation link on the dashboard like so:

and the dashboard display will be like this:

This works just as well as the first one.

Thanks

Until next time

Read More

case study --- How to pass values to different dashboard prompts interactively part 1

Hello again

There is a scenario where I have to navigate from one dashboard to another while keeping the values selected as the criteria in the next dashboard. OBIEE has a pretty standard functionality that allows you to navigate. In 10G version, you can define the navigation path in the column property while in 11G, you add action links to it.

All these are great and simple, but there is a problem when the prompts on these dashboards are different. In OBIEE, if the prompts have different expressions either because they are made from different columns or they are using different logical expressions, they are being treated differently, so while the navigation will still happen, but the value from 1 prompt will not be passed as a filtered value to another prompt. What if I have 2 date columns that are aliases of the same date table, and I want to be able to navigate from one to another using prompts from both columns? This sometimes become a practical challenge. In the eyes of users, they won't understand the difference between the 2 prompts so they only expect to be able to navigate from 1 to another. So this requires some workaround.

Now lets look at this scenario to understand better the issues. I have 2 dashboards that each contains date and hour prompts at least:

Now if you select a date and hour period from dashboard 1 and navigate to dashboard 2, the date and hour values should be expected to come along as well. Well, this should be typically the expectation if the date and hour prompts are exactly the same column with the same expression across both dashboards. However, in my case it is not. Therefore while the navigation still works, but when you get to dashboard 2, the prompted date and hour values are still blank, the report will run with either default prompt values or run without any filtered values.

So let's look at how these prompts are different in my scenario:

The prompt in dashboard 1 is done as the following:

Here, the date prompt comes from date column as it is, the content of the hourly prompts are using the following expression:
Starthour prompt: case when 1 = 1 then hour number end

Endhour prompt: Case when hour number = 0 then 24 else hour number end.

The reason for this type of implementation can be found here, it was meant to solve a different problem.

Now the set up for prompts on dashboard 2 are found below:

Now the reason was this implementation was discussed here if you are interested to know.

Basically, these 2 dashboards content very different functionality, therefore it required different programming behind the scenes. The date prompt on 1 dashboard  is using between operator while is equal operator on another dashboard. The hour prompts on dashboard 2 have to be evaluated based on presentation variables from another prompt (Time scale granularity ), which is not required for the same prompts on dashboard 1. Long story short, I can't pass values from dashboard 1 to dashboard 2 because these are being considered different fields by OBIEE even if users think they are the same.

Therefore, in order to make it work, you can't just convince the users that this can't be done nor will you hope they will  understand the technicality of OBIEE. This will only create bad impression about the product. Hence, we have to look into ways to get around with this requirement. The idea of how to fool OBIEE into treating these prompts the same without compromising the individual functionality of each dashboard.

We will get into the detail on the next entry.

Stay tuned

Until next time

Read More