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, December 31, 2012

Redesigning Dashboards for slicker visuals and better performance

So you may or may not have encountered a dashboard with way too many charts and reports. This is fairly common in a lot of projects. If people aren't sure about what they want, they might as well put them all on the page. The look and feel might attract certain audience because there are different charts, different shapes and colors on one page. However, there can be problems as well. One of the thing to remember is that if 1 dashboard page has too many reports, them it will generate a lot of SQL queries every time an user visits this page. Multiplies that by 20 and another 20 and another 20 depending on how many dashboards are like that and how many users are going there for how oftenly, this can become a problem. Also if the user ever increase the time period of the dashboard and start plotting more dots on the charts, it can overload the graphing engine and create problems if all of the charts are running at the same time. The dashboard below can be a typical example (I only have a screen shot of the pdf version):

So now, how are we going to reinvent this dashboard so it will look better and it will perform better? This is the question that requires a little bit of high level visualization. You as a developer who is getting so used to reading the requirement from someone else, for once in your life need to step out of that box, and start thinking on our own. There is no such thing as right or wrong, it's more about redesigning your work for the better. Now you are an artistic person, your right brain is going to help a lot (or your left brain.. My right or your left?)

As for me, i thought the views that Iphone provides for weather display is a very slick view. You don't start by showing everything, but instead you get a 7-day overview. Once you touch-screen the day, it displays the detail weather forecast each hour of that day. Similar to the view below (Am I right fellow Iphone users?):

So what can I do to design a dashboard in OBIEE 11G that looks similar to that?

Upon some research, trials and errors, I can't really make it look like that as of yet. But I have been able to re-arrange the dashboard layouts without having to make huge changes to the existing setups. I am pretty happy with the result as it is getting better and performance efficient compared to before. There are more potentials for improvement, which I will get into later. But for now, I will show you what I have done.

1. Before doing anything, first visualize how you want to break down the existing dashboard components. Knowing that there are 12 reports in my dashboard, I have found that these reports can be put into 4 categories based on the content. So in my heart, I have come up with the names of these 4 groups.

2. In order to have the dashboard display to work similar like that of Iphone, you pretty much have not a lot of choices here. The weather on the screen is almost working similarly to that of the dashboard prompt, which passes the values to its content and displays accordingly. So I have to create a new dashboard prompt that holds 4 options which represents 4 groups names in the drop down list.

To do so, I am setting the display value to be SQL and I entered a code that unions 4 separate select statements with different group name in there.

Also, setting a presentation variable will be important. In my case, it is named "select"

3. Create 4 different reports (or analysis), each one is hard coded with the value of the group name I create in step 1:

This is the first report of the 4, and I am entering 'Node CPU & Memory' there. So this report will always have 1 record, which is 'Node CPU & Memory'. The filter is applied with the presentation variable 'select'.

So by default, this report will be empty because I set the default value of the variable to be 'none', which won't match with the data 'Node CPU & Memory' that I entered.  In other words, this report will only return 1 raw when the value 'Node CPU & Memory' is passed from the dashboard, or it will always be empty.
 4.Fellow the logic of step 3 and create 3 more reports. Each one is hard coded with a different group name value: "Top Node Interface", "Top Node LSP", "Top Node Pseudowire":

5. Put everything together on the dashboard along with all the other reports and prompts that already exist there:

Notice on the dashboard layout edit page I have put all of the reports into different sections, yet all of the sections are within 1 column. Each section can be defined using conditions to determine whether it displays or not. This can have different visual affects depending on how you arrange it.

So in my case, I have taken the first section and added a condition using 1 of the 4 new reports just created. In this case, the node report selection guide1, which is using value  'Node CPU & Memory' is being picked as a condition for this section. Because the 2 reports in this section are relevant to the topic and I want them to be displayed only when user selects 'Node CPU & Memory' from the dashboard prompt.

I then apply the same logic to all the other sections using different reports I created.

6. Test the new look:

In this dashboard you can see that initially there is nothing being processed. I do have a few prompt values as default. And if you see the dropdown box of my new prompt, it gives user 4 items to select.

User select 'Top Node Interface' and click apply, then the dashboard displays only the reports grouped under this name. This is done without popping a new windows nor taking you to another page. It happens right in front of you:

You notice that not only the correct report is showing, but also the values from the other prompts default are also being filtered at the same time. Cool..

Now try a different item just to be sure. This time I pick 'Top Node LSP' as shown:

Click apply and I get a new report:

Things are working and now my dashboard has a new life although not quite looks like the weather view on Iphone, but it's still pretty slick. Maybe the spirit of Apple awakens the artsy side of me just for the moment.


Until next time.

Wednesday, December 26, 2012

OBIEE 10g to 11g upgrade challenge ---dealing with unexpected floor function

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!

Great, one of the many problems from upgrade has been resolved, now on to the next one.

Stay tuned and happy holiday!

Related Posts Plugin for WordPress, Blogger...