Business Intelligence – Oracle

Archive for May, 2008

Oracle BI EE 10.1.3.3.3/2 – Customizing Skins, Styles and XML Messages – Dashboard footer Customization

Posted by Venkatakrishnan J on May 29, 2008

I have been receiving quite a few emails recently asking for a list of steps required to customize various parts of dashboards/answers. So, instead of blogging about each and every one of them seperately i thought it would be more relevant if i can write about the list of steps involved in identifying what to customize. So, typically any customization request can fall broadly into 3 categories. They are

1. Styles
2. Skins
3. XML Messages

In most of the cases, we can identify in which category our customization would fall into. But there are cases, wherein it would be difficult to identify the categories. So, lets take an example question. “How do i add a background image to the dashboard footer?”. Basically, we need to add a background image to the below dashboard footer.

     

As you see above, the user’s request was to find a way to add an image to this footer. So, in order to find out what to customize, lets open up a firefox browser with the Firebug extension installed. If you are not sure what the Firebug extension does, check my blog entry here. As a next step, start inspecting the HTML code of the footer. Firebug has this nice option called inspect that would allow anyone to findout the HTML code corresponding to any part of a HTML page.

     

As you see here, the entire footer is comprised of 2 td tags within a tr tag (2 columns in one row).

<tr>
<td class="PoweredByMsg">
</td>
<td valign="bottom" align="right">
</td>
</tr>

Our aim is to add an image spanning the entire row. As you see, this tr tag does not have any style or CSS class assigned to it. So, in order to achieve what we need we would have to modify a custom XML message(not a style). If it was a style we would have got the class attribute. The next step is to find out how we can identify the XML message. If you look at the above HTML source for the footer, you would see a class called PoweredByMsg. Lets search for all the files under {OracleBI}\Web\msgdb, which is where all the custom XML files exist, for the PoweredByMsg.

     

You would get one file viewscontrolmessages.xml which would be referring to this message. Now search for the message PoweredByMsg within this XML file. This would in turn give you the below XML message.

<WebMessage name="kmsgEVCPoweredBy"><HTML><table width="100%" height="24px" cellspacing="0">
<tr><sawm:if insert="1"><td class="PoweredByMsg"><sawm:param insert="1"/></td>
</sawm:if><td align="right" valign="bottom"><sawm:messageRef name="kmsgPoweredByAnchor"/>
</td></tr></table></HTML></WebMessage>

Now as you see, this the source XML message that is responsible for the tr and 2 td tags in the dashboard. Once you have identified the source XML message, copy the XML file to {OracleBIDate}\web\msgdb\customMessages (if you dont have this folder, create one). And then change the above message to the one shown below.

<WebMessage name="kmsgEVCPoweredBy">
<HTML>
<table width="100%" height="24px" cellspacing="0" style='background-image: url(res/s_oracle10/images/report_serviceRequest.jpg);'>
<tr>
<sawm:if insert="1">
<td class="PoweredByMsg">
<sawm:param insert="1"/>
</td>
</sawm:if>
<td>
<sawm:messageRef name="kmsgPoweredByAnchor"/>
</td>
</tr>
</table>
</HTML>
</WebMessage>

Now, restart the presentation services.

     

This should give you the list of steps for identifying what to modify while doing a dashboard/answers customization.

Posted in All Posts, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.3.3/2 – Reporting out of Multiple Levels in an Essbase Cube

Posted by Venkatakrishnan J on May 26, 2008

If you use Essbase or Oracle OLAP as a data source, the common reporting tool that the end users would most probably use is the corresponding Excel Add-ins. One of the major advantage of these excel add-in’s is that they have excellent connectivity to their correponding data sources. For example, if you take an Essbase cube (oversimplified for demonstration purposes) with a single dimension as shown below

    

the common reporting requirement is to have a report like the one shown below

    

This is one of the major advantages of Excel-add ins. One can treat any member from a dimension to be in the same level and can report on them. Now, lets try to import the above cube into BI EE and see what happens.

    

As you see, each level in an Essbase hierarchy is treated as a seperate column. Hence, in order to create reports like the one shown above, one way is to use a UNION ALL in the Answers and then combine all the different level columns into a single level. The other option to achieve the excel kind of reports as shown above is by leveraging the alternate hierarchy support option of Hyperion Essbase. That is, in the essbase outline, have all the members of your dimensional hierarchy in a flattened fashion (using the shared members method) in addition to the normal hierarchy as shown below.

    

As you se above, in addition to the normal hierarchy of the Geo dimension, we also have the flattened members of the dimension as shared members. Now go to BI EE and change the hierarchy type of the Geo dimension to “Unbalanced”.

    

Now, if you create a report in BI EE on the first level1 column, we should get all the members of the Geo dimension.

But the problem with this is that though the drill downs are enabled, the drills would not work since BI EE still treats them as level1 members and would try to apply a filter on level1. Lets look at an approach in a future blog entry wherein we can have the drills to work properly even if all the levels are flattened as shown above. So to summarize, if you have a requirement to report on all the levels (as a single level), the possible approaches are

1. Use UNION ALL and then combine data from multiple levels.
2. Use flattened alternate hierarchies from within Essbase.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 4 Comments »

Oracle BI EE 10.1.3.3.3/2 – Hyperion Essbase Alternate Hierarchies

Posted by Venkatakrishnan J on May 23, 2008

If you had read my blog entry here, i would have shown you how to go about implementing multiple hierarchies in BI EE by using the concept of Shared Levels. But the major problem with BI EE is that it can only handle shared levels and not shared members i.e it expects all the members within a level to be shared across hierarchies. Also, they cannot have a common parent. This basically intrigued me to find out how BI EE handles the alternate hierarchies from Essbase. Lets start with a classic example for alternate hierarchies i.e reporting in Fiscal and Financial years. So, lets go to the Administration console and start creating an outline as shown below.

    

We basically have 2 hierarchies. In the normal Fiscal year terms, January and Febraury months fall under Quarter 1. But for some companies like Oracle where Fiscal and Financial years are different, January and the Febraury months fall under Q3 of the Financial year. Once this done lets load the data using Essbase Integration Services or Hyperion DIM. In my case, i have used the DIM product to load the data. For more details on how to load data using DIM, check my blog entry here.

    

Lets first look at the data from the Essbase Excel Addin.

    

Now, lets import the cube in BI EE and see what happens if we drag and drop the cube into the BM layer.

    

As you see, BI EE treats all the hierarchies in the same way i.e it treats all the same levels from multiple hierarchies as same. We observed the same even for ragged hierarchies here. Lets create a report out of this in BI EE

    

There is also another approach for designing these. One can manually define the alternate hierarchies in BI EE. We shall see how to do that in a future blog entry.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.3.3/2 – Delivering Prompted Dashboard Pages using iBots – Saved Selections

Posted by Venkatakrishnan J on May 21, 2008

If you have looked at my blog entry here, i would have shown you how to go about delivering prompted dashboard pages. But the major drawback with that approach was one cannot send out PDF attachments as it involved the use of GO URL and iframes. Lets look at another approach (thanks to an excellent tip from Mike Durran, Oracle BI Delivers Product Manager) wherein one can deliver prompted dashboard pages even as PDF attachments. Lets start with the same example as before. Open up the Brand Analysis Dashboard page and apply some prompt values on the page.

    

Now, our aim is to send this page as is (with the applied prompt values) to end users. In order to so this, create a shared selection for this page

    

    

Make the saved selection as default for this page (if you want to deliver this as multiple users then you need to make the selection as default for all the users). Once this done, just deliver the dashboard page. This will automatically send the page after applying the selection

    

    

An elegant solution if you want to send a dashboard page with applied prompt values.

Posted in All Posts, OBI EE Plus | 5 Comments »

BI EE 10.1.3.3.3/2 – Hyperion Essbase Security – Zeroes for No Access

Posted by Venkatakrishnan J on May 19, 2008

Both Hyperion Essbase and BI EE have very good security models. Now that we have the connectivity to Essbase from BI EE, i thought i would test out the security integration between the two. One of the first things that i wanted to test was to find out how the Essbase Security propagates into the BI EE security model. Lets start the test with a simple cube/database having 2 dimensions LOB and YEAR and a measure AMOUNT. Now, lets define one more user in our Essbase server (user1) who would have read access to this cube.

    

    

As a next step lets define a filter in the Essbase cube which would restrict access to the LOB dimension and all its members. This filter can be achieved using the below maxL script.

create or replace filter 'POA'.'Poa'.'filter2'
no_access on '@DESCENDANTS ("LOB")'

Now, assign this filter to the user user1.

    

Once this is done, lets first see what happens when we try to view the cube’s data as user1 from with the excel add-in.

    

    

As you see, since this user does not have access to the other dimension members of the LOB dimension we get “No Access” message for all the members of the LOB dimension. But for the YEAR dimension alone, for which user1 has access, we get the data. Lets see what happens in BI EE. The first step is to import the cube as an Admin user (or an user with Application read/write privilege). Once this done, change the username in the connection pool as user1.

    

Now create BM and PL layers for this imported cube and try creating a simple report using this cube.

    

Strange that we are getting zeroes here instead of an error message (which i was expecting). But data is shown correctly for other dimensions for which this user1 has access to.

    

This is something you would have to be aware of while using the BI EE-Essbase connectivity. Not every user/management would be happy if you show them zeroes instead of a No Access message. But this can be circumvented by assigning one more layer of BI EE security on top of the Essbase dimensions.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 5 Comments »

Oracle BI EE 10.1.3.3.3/2 – Proxy User Functionality

Posted by Venkatakrishnan J on May 16, 2008

One of the very good features of BI EE is its ability to enable proxy functionality for all/some users. For example, as an Administrator user, one can proxy as another user and can find out what kind of dashboard other users have. One other good example is (given in the documentation) a manager user allowing one of his direct reportees to create reports for him. Though the setup for this functionality is straight forward, i thought i would blog about it since the functionality is commonly desired.(I believe Borkur had blogged about it before. Not sure where it is now. If anyone has the link, just leave a comment with the link and i would update the link here.) We shall be using 2 terms here. They are ProxyUser and TargetUser. You can understand what these 2 users mean from the below diagram

    

Basically ProxyUser is the user who would be proxying as other users. I.e User A can proxy as user B, C and D(targetUsers)

Enabling this functionality is a four step process.

1. Let BI Server and Presentation Server know about the list of target users.
2. Let BI Server and Presentation Server know about the list of proxy users.
3. Let BI Server and Presentation Server know about the relation between the target and proxy users (who will proxy in as whom)
4. Create an InitBlock and a system session variable called PROXY to allow the proxy user to authorize.

The first 3 steps are achieved by adding a set of tags to the instanceconfig.xml. Lets take a simple example. There are 2 users Administrator and paint. Our aim is to set up a proxy funtionality for the Administrator user so that he can proxy in as the paint user. In order to do that lets start with creating a simple table with the below structure.

CREATE TABLE PROXYTABLE
(
PROXYUSER VARCHAR2(100),
TARGETUSER VARCHAR2(100),
PROXYLEVEL VARCHAR2(20)
)

PROXYUSER – Stores the list of PROXYUSERS needed within BI EE. In our case, this column should have the Administrator user as one of the values.
TARGETUSER – Stores the list of TARGETUSERS needed within BI EE. In our case, this column should have the paint user as one of the values.
PROXYLEVEL – This can take 2 values — full or restricted. If full access is provided, then the proxyuser would have edit/read privileges of the target user (provided the proxyuser himself has those privileges). If restricted access is provided, then the proxyuser would only have read privileges (provided the proxy user himself has those privileges).

Now, lets insert the users as desired for our example into the table

INSERT INTO PROXYTABLE values('Administrator','paint','restricted');

Once this is done, create a seperate connection pool connecting to the database containing the above table.

    

Now, lets start with the first 3 steps mentioned above i.e to let the BI Server and Presentation Server know about the list of target, proxy users and their corresponding relationships. In order to do this, we need to first add a set of tags to the instanceconfig.xml that would let the presentation server know about an XML template(which would contain the queries to get the list of target, proxy users). So, add the tags given below to your instanceconfig.xml (between the ServerInstance tags)

 <LogonParam>
  <TemplateMessageName>LogonParamSQLTemplate</TemplateMessageName>
  <MaxValues>100</MaxValues>
 </LogonParam>

    

In the above XML, TemplateMessageName tag value(LogonParamSQLTemplate) would be the Template name which we would be creating.

Go to {OracleBIDate}\web\msgdb\customMessages (if you do not have this folder, create one) and create the below XML file in this folder (you can give any name to the XML file)

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
 <WebMessageTable system="SecurityTemplates" table="Messages">
 <WebMessage name="LogonParamSQLTemplate">
 <XML>
  <logonParam name="RUNAS">
  <getValues>EXECUTE PHYSICAL CONNECTION POOL Proxy.Proxy
  select TARGETUSER from PROXYTABLE where PROXYUSER='@{USERID}'</getValues>
  <verifyValue> EXECUTE PHYSICAL CONNECTION POOL Proxy.Proxy
  select TARGETUSER from PROXYTABLE where PROXYUSER='@{USERID}' and TARGETUSER='@{VALUE}'</verifyValue>
  <getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL Proxy.Proxy
  select PROXYUSER, PROXYLEVEL from PROXYTABLE where TARGETUSER='@{USERID}'
  </getDelegateUsers>
  </logonParam>
  </XML>
  </WebMessage>
  </WebMessageTable>
</WebMessageTables>

Once this is done, restart the presentation server. Now, the next step is to initialize a system session variable PROXY from an init block. This will help in authenticating the proxy user as the target user.

    

    

In the InitBlock use this SQL.

SELECT TARGETUSER FROM PROXYTABLE WHERE  'VALUEOF(NQ_SESSION.RUNAS)' = TARGETUSER AND ':USER' = PROXYUSER

Do not worry about RUNAS session variable. This RUNAS session variable would be populated when you click on the target user. And based on what you have clicked, the PROXY user value would be populated. Now, if you log on as Administrator and click on Settings -> Act as, you would get the paint user.

    

    

Now, the Administrator user should be able to proxy as the Paint user

    

If needed, you can populate another session variable PROXYLEVEL to vary the access levels of the proxying user. But this should get you started.

Posted in All Posts, OBI EE Plus | 6 Comments »

Oracle BI EE 10.1.3.3.3/2 – Delivering Prompted Dashboard Pages using iBots – Bookmark links and HTML

Posted by Venkatakrishnan J on May 13, 2008

One very good feature that BI EE does not have currently is the ability to send prompted dashboards to users via iBots. Currently, iBots can only send filtered reports but not dashboards. Lets look at an approach today to send prompted dashboards to end users. Use this approach only if you have no other option. Using the approach one can send the dashboards only in HTML format (PDF would not work). Let us start with the Brand Analysis Dashboard Page that we have in the Paint Dashboard. This page contains 4 prompts and a number of reports. Lets choose some values in all these prompts and run the reports like the one shown below

    

Our aim is to send this page(with the prompt values that we have chosen) via delivers. In order to achieve this lets start with create a Bookmark link to the page. If you are not sure what Bookmark link does, check my previous blog entry here. This bookmark link would give us a dashboard page with the same prompt values that we applied above. Now save this link. Then go to answers and create a simple report. Include a dummy column with the below formula

CASE WHEN 1=0 THEN Markets.Region ELSE 'Dummy' END

    

This is to ensure that the report does not throw out any error. Now, go to the narrative view of this report and enter the below iframe html code.

    

In the above iframe, replace the src= attribute with the bookmark link that you saved in the previous step. Also, ensure that you are adding nquser and nqpassword parameters as shown above. Now, in the compound layout of this report just have this narrative view. Save this report. Now go to delivers and schedule an ibot to send this report to end users. Ensure that you are sending the content only as HTML.

    

Now, your end users would be receiving the prompted dashboards via delivers.

    

Not the best solution, but can be used in certain cases where the requirement is an absolute must. One can add more customizations to this by choosing a single bookmark page from multiple pages.

Posted in All Posts, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.3.3/2 – Hyperion Essbase Connectivity – Handling Measures and Account Dimension

Posted by Venkatakrishnan J on May 12, 2008

I was working with the BI EE and Essbase connectivity today and came across another important feature that would let an user to analyze any dimension members as Type 1 or Type 2. If you had gone through my previous blog entry here, i would have briefly explained what Type 1 and Type 2 actually mean. By default, when you import an Essbase cube, all the measures within an Account dimension would be imported as flat-list (with no hierarchy). This kind of an import is called as Type 1. For example, consider that we have a cube/database containing 2 dimensions LOB and YEAR. And we have a third dimension ACCOUNT containing the AMOUNT measure, then the TYPE 1 Essbase cube would be treated as shown below

DIMENSION LOB                     DIMENSION YEAR                     AMOUNT

    

As you see, in Type 1 we are losing out on the measure hierarchy if any. One of the very good advantages of Essbase is that it treats all the dimensions(including an ACCOUNT dimension) as the same i.e one can use any dimension as a scenario dimension from a reporting standpoint. Now, the question is how do we leverage that from within BI EE. i.e Basically we would like to use the months in the YEAR dimension as a scenario dimension and keep the account dimension intact. If you are not sure what i mean, below screenshot is what i need. The idea is to have the months in the reporting as a column header for all the analysis that i would be doing. In such a case, it is as though we are having as many AMOUNT measures as the number of MONTHS.

    

In order to achieve the above, there are a couple of changes that we would have to do in the physical layer of the Admin tool. Double click on the cube and navigate to hierarchies. Double click on the Accounts Dimension and change the dimension type to Other (instead of Measure Dimension).

    

Similarly, change the Year Dimension type from Other to Measure Dimension.

    

This will flatten out the Year Dimension. Now, remove the AMOUNT measure column from the cube and manually create 3 columns (each matching the attribute value names of the dimension members) as shown below

    

Now, recreate the BM and physical layer. Now you would be able to create reports as shown above in the 2nd screenshot

    

The above should give you an idea of how to go about converting your hierarchical members into flattened measure columns.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.3.3 – Released and Out for Download

Posted by Venkatakrishnan J on May 10, 2008

Well, it looks like the wait is over and 10.1.3.3.3 version of BI EE has been released. You can download it from here and the documentation is here. Unfortunately, there is not even one new major feature that has been added to BI EE. But BI Publisher has some good new features. Below is the quote from the New Features guide.

Oracle BI Enterprise Edition Version 10.1.3.3.3 contains no new features.

However, Oracle BI Publisher Version 10.1.3.3.3 contains new features. For more information about the new Oracle BI Publisher features, refer to the Oracle Business Intelligence New Features Guide Version 10.1.3.3.3.

Tim has already blogged about the new features of BI Publisher here. On a high level, below are the new features that have been added to BI Publisher.

1. JDBC DataDirect drivers for DB2 (v8.1,v9.1), MS SQL Server (2000,2005), Oracle (9i, 10g) are now bundled in BI Publisher 10.1.3.3.3

2. New option to enable/disable the “Link to this Report” link in every report.

3. Support for Pagination in RTF Output and more

4. Support for multiple cascading parameters.

5. Support for vertical alignment of external graphics

6. Support for multiple currency symbol display in reports

I have a hunch that 10.1.3.3.3 might have some critical bug fixes especially surrounding BI EE – Essbase connectivity. Let me play around more with this release to find out what more has been added/fixed.

Posted in All Posts, OBI EE Plus | 1 Comment »

Nothing like waiting for a new release – BI EE 10.1.3.3.3 to be released tommorrow!!!

Posted by Venkatakrishnan J on May 9, 2008

Well, this is what the otn BI EE download page gave me today.

    

10.1.3.3.3 version is about to be released in another 8 to 10 hours(hopefully). I believe this is more of a patch release with some bug fixes. Lets wait and see what new features/bug fixes are being bundled into this release.

Posted in All Posts, General | 1 Comment »