Understanding the implementation of value based hierarchy (parent-child) in OBIEE 11G

Hello

As we all know, from 11g onwards, the feature of implementing value based hierarchy is available. There are several great articles out there that explains the steps of such implementation in 11G, therefore I am not going to repeat these things. What I want to talk about is more of a concept of value based hierarchy implementation, which will hopefully answer the questions that a lot of beginners reading about this topic may have as such "How do I know the right table structures for this type of hierarchy?"

As we know, employee dimension is a very typical example of value based hierarchy, but I don't want you to stop right here. There could be so many other tables that can be designed to support value based hierarchy based on reasons we will touch upon.

Conceptually, we all know what value based hierarchy is from looking at sample diagrams of hierarchical trees where branches are labelled with employee names. Now, if you look at the physical table where this type of hierarchy is supported, it will look like this:

Employee                 Manager                   Level (or other names)                other attributes
Me :)                                                                8                                            phone number, address etc
Superlek                     Me                              7                                             ......
Attachai                     Superlek                        6                                            .......
Sombat                      Attachai                         5                                            .......
Dekkers                     Sombat                         4                                            .........
Orono                        Dekkers                        3                                            ...........
Yodsanan                   Orono                          2                                             ...........
Mick                          Yodsanan                     1                                             ...........
Gina                           Mick                            0                                             ..........
Decharwin                  Me                               7                                             ............


From the above example, it clearly stores Employees in relation to the immediate boss in a value based hierarchical fashion. The level (or whatever name it can be) will keep an integer that indicates the person's level in this hierarchy. This table can have other attributes that handles changes and updates of employee depending on how the business wants to keep track of historical record. But the idea is pretty clear here.

Now if you want to redesign this table to store structure based hierarchy similar to time dimension, then this table structure will look like this:

Employee            Manager           Sr Manager        Director         ......       CEO
Gina                      Mick                Yodsanan            Orono                       Me :)

The reason structure based hierarchy isn't the best for employee dimension is that it's very rigid. All of the columns are fixed and levels are pretty much hard coded. In the future, if someone gets promoted, demoted, retired, or given a new rank that didn't exist before, it will become very difficult to maintain this kind of records in structure based hierarchy. The reason why time dimension is great for structure based hierarchy is that, the calender system that we use in our daily life doesn't change. In other words, 'February' will always be in 'Month' column, it will never get 'promoted' and become 'quarter' nor will be demoted to be 'Week'.

Based on the simple concept, you should be able to think of other dimensions that can be good candidates for either structure based hierarchy or value based hierarchy. It can also depend on the business process of each individual company or industry, it can also base on determining factors, such as pricing based hierarchy or promotion based hierarchy and so on.

Therefore, when it comes to determining what type of hierarchy should be implemented in OBIEE, the decision should be made way prior to it gets to OBIEE. It should be a decision made during the designing of project roadmap.

Thanks

Until next time

                    

Read More

Navigating between reports and dashboards when there is no common fields to pass parameters

Hello All

Today I want to talk about navigating between reports and dashboards. As we all know, this feature can be done in 11G through action frameworks, this allows us to click on any particular value of the actionlink defined column and navigate us to another dashboard page (BI Content if we choose it in the actionlink) and the value we clicked previously will be filtered.

This is a pretty standard feature that has been part of OBIEE product since it was still called Siebel Analytics. However, in order for this feature to work as straightforward as it is, the condition is that there must be a common field used in both reports. In other words, if you want to navigate from report 1 to report 2 by clicking on the values of a column, then that column needs to exist in both reports for this to work.

This becomes an issue sometimes when that column is technically not the same column yet it is the same column from the business perspective. In other words, we have all used alias in our data modeling and it gives us a lot more flexibility in creating the kind of reports we want. Alias of the same physical table can be used in different star and snow-flake schemas without causing conflicts. However, these alias columns, although being referred to the same physical table, aren't being treated the same column from OBIEE.

Here is the case:

We have a dashboard here that I am going to click on any values of 'Vlan Interface', then the page should become another dashboard with only that 'Vlan interface' filtered

However, it is not working. The destiny report is successfully being fetched, however the filter box is empty. This means the value that we clicked was being passed from the first page, but it is not received in the destiny page.

The reason here is that, although both the Vlan Interface column and Interface prompt has the same data and they are from the same interface column in the physical table, they are different columns according to OBIEE. They are from different folders and they have different names, they simply exist in different places.

 I wish OBIEE can have the intelligence, or an added feature that allow users to define actionlinks by including or excluding the navigation of the actual physical column. Well, until that happens in the future, we just have to work around it now.

To make the destiny dashboard receive the values passed from the first dashboard, there must be a common field between the 2. Since we want to click on 'Vlan Interface' and go to the second dashboard, then we will create a dashboard prompt using 'Vlan Interface' columns and then place it on the destiny dashboard:

In this prompt, we will set the default section as SQL statement:

Select "Node Interface", "Node Logical Interface" from table where rcount("Node Interface", "Node Logical Interface") = 2.

This is nothing but setting a default value so that if you do come to this dashboard by direct access, this prompt will have some default value to run against all of the reports there. If you access this page by clicking on 'Vlan Interface' value from the other dashboard, then the value you click will be shown in this prompt after the navigation. This is basically the idea. I choose rcount = '2' simply because I know the first value in my table is 'N/A'.

Set the presentation variables here, we will pass this variable to the existing 'interface' prompt on this page later.

Now that we have created this prompt, we will put it on the destiny dashboard

Now that this prompt is on the dashboard, but I want it be work behind the scene only. I don't want users to see this prompt and the 'interface' prompt at the same time. So it's just the matter of hiding this section all the time.

Simply create a report that always returns some data and use it as the condition:

Define a condition in the section Condition:

True if row count = 0 determines that this section will display only when the row count of this report = 0, which is always going to be false. This implies that this section will always be hidden.

Now, we need to modify the existing 'interface' prompt to make it affected by the 'Vlan interface' prompt that we just created.

Here, the idea is to change the default section of this prompt so that it's default value is whatever 'Vlan Interface prompt' passes :

SELECT "Node Interface"."Node Logical Interface" from Table where "Node Interface"."Node Logical Interface" = '@{V}'

Here, the presentation variable 'V' could be the value of rcount = 2 in the physical table or whatever Vlan Interface value the user click from the first page:

Now, let's give it a test. First, we go to this dashboard page with direct access. We see that the 'Vlan Interface' prompt is hidden, and the interface prompt is showing a default interface value, this is good.

Now, let's go back to the first dashboard page to test the navigation part:

Click on Vlan interface 'ETH Vlan 1410 U1/M1/IF0':

We arrive at the destiny dashboard with 'ETH Vlan 1410 U1/M1/IF0' being passed successfully:

Everything looks as if nothing extra has been done, this is perfect.

Thanks

Until next time

Read More

Calling Javascript function from OBIEE dashboard

Hello again

This time I am going to show how to call javascript function from OBIEE dashboard. The script can be written and saved in the common.js file located in:

OBIEE_Folder/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/b_mozilla/common.js

I have written a simple script as the follow and I saved it in the bottom of the common.js file:

// MY Stuff
function obi_popup()
{
alert("Hello World")
}

So as you can see, the function name is called 'obi_popup' and the function itself is supposed to return a text 'Hello World' when called.

Once this is done, restart the OBIEE opmn service.

Go to the dashboard and add a text section. In there add the following text:



check "contain HTML markup" box

This will appear a button labelled 'hi there!', and by clicking on it, the javascript 'obi_popup' should be called

Click and the result:

Thanks,

until next time

Read More