Posts
Hello all
It's been a little while since I posted. I was busy re-platforming and upgrading all of our BI system to 11g. They was a lot of work but it was fun and challenging. Moreover, there was a lot of lessons learned from that, which I will post in detail later on. Today, I just want to share something real quick.
One of the thing I immediately noticed after upgrading to 11g was that all of my reports stopped working on the dashboard when passing through dates and hours from the prompt. Let me illustrate what I mean here.
The below report is showing records for every hour of the day and the hour prompts are not selected to begin with.
Now I put 1 and 23 in my prompt and the report returns nothing. Later on, I tried different number ranges, from 1 to 3 or from 10 to 11, the report never came back with data, it is impossible as I do have data for those hours.
For those who are curious about this set up, you can find it here and here too as I implemented this along with time zone conversation back in 10g:
In my other system, this report is still working fine as it is in 10G environment.
So what's going on? How do you begin solving this mystery?
This is when analyzing the physical SQL queries generated in the 2 applications becomes crucial. I generated both SQLs, and I ran both against the DB that 11G is connected to. I found out that both SQLs work perfectly and correctly in the DB. However, when I changed the prompt values in 11g to match the criteria of the working report in 10g and re-evaluated the SQL, i saw something interesting.
In the 11g SQL, in part where the filter is applied, I noticed 'floor' function being used here. At the same time in the SQL generated from 10g for the same logic, the SQL doesn't have 'floor' function. It was just doing a simple division:
Upon some research, I came across that in 11g, the BI server automatically issues floor function when 2 integers are dividing each other.
This makes sense now as all of my filter logic are based on hour divided by 24. The reason was complex, if you are interested, you can read about it here (It can be valuable for those who are struggling with performance). Anyways, it is what it is and knowing that floor function happens when 2 integers divide, so we have to think of a work-around.
Floor function can be unsupported if you wish to uncheck the DB feature of that in the RPD, but I wouldn't just go for that, because you never know in the future you may need it.
Knowing the problem and the future of my set up, I have decided to change the formulas and coding of my filters to avoid integers dividing each other:
As you can see from below, these old codes in 10g are diving integers. So I am going to replace 24 (highlighted) to 24.00 to all that applies. This should automatically fool OBIEE into not using floor function.
Now, let's test again.
Working perfect now!
It's been a little while since I posted. I was busy re-platforming and upgrading all of our BI system to 11g. They was a lot of work but it was fun and challenging. Moreover, there was a lot of lessons learned from that, which I will post in detail later on. Today, I just want to share something real quick.
One of the thing I immediately noticed after upgrading to 11g was that all of my reports stopped working on the dashboard when passing through dates and hours from the prompt. Let me illustrate what I mean here.
The below report is showing records for every hour of the day and the hour prompts are not selected to begin with.
Now I put 1 and 23 in my prompt and the report returns nothing. Later on, I tried different number ranges, from 1 to 3 or from 10 to 11, the report never came back with data, it is impossible as I do have data for those hours.
For those who are curious about this set up, you can find it here and here too as I implemented this along with time zone conversation back in 10g:
In my other system, this report is still working fine as it is in 10G environment.
So what's going on? How do you begin solving this mystery?
This is when analyzing the physical SQL queries generated in the 2 applications becomes crucial. I generated both SQLs, and I ran both against the DB that 11G is connected to. I found out that both SQLs work perfectly and correctly in the DB. However, when I changed the prompt values in 11g to match the criteria of the working report in 10g and re-evaluated the SQL, i saw something interesting.
In the 11g SQL, in part where the filter is applied, I noticed 'floor' function being used here. At the same time in the SQL generated from 10g for the same logic, the SQL doesn't have 'floor' function. It was just doing a simple division:
Upon some research, I came across that in 11g, the BI server automatically issues floor function when 2 integers are dividing each other.
This makes sense now as all of my filter logic are based on hour divided by 24. The reason was complex, if you are interested, you can read about it here (It can be valuable for those who are struggling with performance). Anyways, it is what it is and knowing that floor function happens when 2 integers divide, so we have to think of a work-around.
Floor function can be unsupported if you wish to uncheck the DB feature of that in the RPD, but I wouldn't just go for that, because you never know in the future you may need it.
Knowing the problem and the future of my set up, I have decided to change the formulas and coding of my filters to avoid integers dividing each other:
As you can see from below, these old codes in 10g are diving integers. So I am going to replace 24 (highlighted) to 24.00 to all that applies. This should automatically fool OBIEE into not using floor function.
Now, let's test again.
Working perfect now!
Great, one of the many problems from upgrade has been resolved, now on to the next one.
Stay tuned and happy holiday!
0 comments:
Post a Comment