Business Intelligence – Oracle

Archive for June, 2008

Oracle BI EE – Public Reports and Dashboards – Bypassing authentication

Posted by Venkatakrishnan J on June 30, 2008

Another common question that keeps circulating is, “how do we bypass login to certain BI EE Dashboards?”. The most obvious answer to this question is to pass the username and password through the url. But certain security requirements/standards in some companies do not allow passing of passwords through the URL. So, lets look at a simple approach wherein some dashboards can be seen without passing the passwords through the url. The idea is pretty simple. Identify a user, for example PUBLIC, that would get direct access to certain dashboards. This user should not exist in the BI EE repository. All the other users would exist in the BI EE repository. So, the first step to achieve this is to create a simple init block with the below shown sql.

select ':USER' from dual where upper( ':USER' ) = upper( 'Public' )

Make the above init block to set the USER system session variable. Also, check the “Required for Authentication” check box. The init block can connect to any oracle database connection pool.


Now, if you use the below shown go url, the dashboards would be shown. As you see we are not passing the passwords through the go url.



The above approach uses the concept of external table authentication. Basically, the init block checks for the PUBLIC user and ensures that the user is authenticated without even checking for the password. All the reports/dashboards that can be viewed by the “Everyone” privilege can be accessed by this PUBLIC user. Very simple but can be used in certain situations.

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

Oracle BI EE – Row Level Security and Row-wise Intialized Session Variables

Posted by Venkatakrishnan J on June 30, 2008

As you would probably know, BI EE provides the capability to do row-level security from the Business Model layer. There was a question the other day wherein a user wanted to know how to do row-level security, wherein more than 1 filter value come from some other table. For example, lets consider the standard SH schema that comes with Oracle Database. The BM for this schema would look like the one shown below


Consider another table PROD_SECURITY containing the columns USERNAME and PROD_CATEGORY.


So, basically, the above table provides the list of users and their corresponding product category for which the users have access to. Now, in our BM, the sales and the product table are joined at the Product level. Each product category can have multiple products and hence we would have to use row-wise initialized session variables to achieve this security. So, start with creating an init block which would populate a session variable PROD_FILTER with all the prod ids belonging to the product category for which the user has access to. The init block would use the sql below



Once this done, use the below statement in the where clause of the content tab of the Sales data table.



So, basically the row wise initialized variable would be initialized to (100,200,….) etc. Lets check whether the security is getting properly applied from answers. Since, we have not secured on the dimension table, lets first include the products column in our report and see what happens to our report


As you see, it would list down all the product categories. Now lets include the AMOUNT_SOLD column from the fact table into our report.


As you see, the security has been applied and the user Administrator would be able to see only 2 categories for which he has access to.

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

Oracle BI EE – CPM Analytics and EPF – Handling Ragged Hierarchies in Relational Data Sources & Action Links

Posted by Venkatakrishnan J on June 23, 2008

The last couple of weeks have been really very hectic for me as i am currently leading a very interesting project for a client involving EPF, Essbase, BI EE, Informatica and HFR. Though the project details would never see the light of the day, there have been lots of key take aways both from a technology standpoint as well as from an architecture standpoint. As part of the project, i had an opportunity to interact with one of the PM’s for a product called as CPM Analytics. I am not sure how many of you have actually heard of this product. For those who have not heard about this product, it is very similar to the Analytic Applications (OBIA) like HR Analytics, SCM analytics etc. But the major difference between this and the other applications is that this is a pure reporting analytics i.e there is no warehouse or ETL part to this application. CPM analytics is nothing but a repository and a set of reports bundled by the Oracle Development team for direct reporting on Oracle Enterprise Performance Foundation. Now, why am i blogging about this? As i said earlier, i had a very fruitful discussion with the PM of this application and there were 3 key take aways from that meeting which i shall be sharing here. As you might probably know, EPF or Enterprise Performance Foundation provides an out of the box capability to consolidate financial as well as non-financial data from Oracle E-Business Suite. One of the major advantages of EPF is that it structures the data in such a fashion that it is very easy to report on. It structures the data in the form of dimensions and facts(though not a direct star since the grain can vary). Our discussion centered around 3 main topics. They were

1. Handling Ragged and value based hierarchies in EPF
2. Performance considerations
3. Drill through to Oracle GL using Action links

I will explain each of the above in detail.

Handling Ragged and Value based Hierarchies:

This was my first question to the PM since BI EE does not have the capability to handle value based hierarchies out of the box. Most of the hierarchies in EPF are value based (Parent-Child). So, in order to circumvent that drawback, the CPM analytics uses a two way flattening. Lets understand this in detail. For example, lets take a simple hierarchy as shown below


In EPF, this hierarchy would exist in the Parent-Child form as shown below


Now, in order to leverage this hierarchy in BI EE, the first step is to flatten the above hierarchy as shown below


Now, if you notice, though we have flattened the hierarchy, we still have raggedness in the hierarchy. Hence while making the join with the fact table, we would be losing out on all the values that are null in the lowermost level. In order to circumvent this, we would have to do additional flattening which will replicate the lowest child to all the levels (where it is null) as shown below


So, if you look at the CPM analytics repository, you would notice 20 levels for all the dimensions. The reason for having that is to handle the value based hierarchy and also to ensure that we are handling the raggedness in the hierarchy.


This is a very nice way of handling ragged hierarchies. But the major drawback with this approach is that you would have a lot of unnecessary duplicates when you drill down and also we need to ensure that the hierarchy depth does not cross the limit specified. Still a very handy approach.

Performance Considerations:

The second part of our discussion was towards the performance of CPM analytics. EPF contains huge amounts of data even after consolidation from Oracle GL. So, one would have to ensure optimal performance for all the users irrespective of the kind of queries that they fire. In order to achieve that, CPM analytics uses 2 approaches

1. Enable caching for all queries – The usual caching approach. But the major drawback with this is that one cannot determine what kind of queries an end user will fire and hence it would not be possible to cache everything. Also, there is a physical limitation to caching.
2. Intelligent use of MV’s – This is an approach very unique to CPM analytics. In order to ensure optimal performance, they have created Materialized views for each dimension at its granularity. For example, Natural Accounts is one of the important dimensions in EPF. So, you would find a MV called as FEM_NAT_ACCTS_MV which will store the aggregated data at the natural account level. Also, in order to ensure that this MV is always used by the optimizer you would find the below statement being fired for all the sql’s.



The above ensures that the optimizer would pick up the data from the MV even if the statistics are stale.

Drill through to Oracle GL using Action Links:

This is again one of the very unique features of CPM analytics. It provides out of the box capability to drill through to Oracle GL. Lets first try to understand what Action links are. Action Links, but for its name, are nothing but columns exposed as URL’s. There is nothing extra that one would have to do on the BI EE side to enable this. Action links enable end users to drill from a BI EE report to an Oracle GL form. Typically these are called as Action Links since the links are dynamic and they are obtained directly from Ebusiness Suite. In CPM analytics (the same concept was used in Fusion Intelligence before), the action links have been implemented using a simple sql shown below (just an example)

  B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705)
    WHEN B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705) THEN
--    '&CreationRowSequence='||B.CREATION_ROW_SEQUENCE||

As you see above in the sample sql, the part highlighted in bold would provide us the URL. So, typically when we are in an Answers report the idea is to pick up that column and make it as a URL. Hence, whenever someone clicks on that link it will automatically take them to the GL page by passing the appropriate parameters. The above was just to give an idea. There can be lots of different pages in EBS that we can redirect to and hence it is pretty tedious and complex to implement. But on the whole the idea is the same. But the major drawback with this approach is that this has a specific granularity. So, whenever someone creates a report with this link, it will open up a report at the URL grain rather than the report grain. This is something one would have to be aware of while implementing action links.

On the whole, the 3 approaches above can be re-used in relevant implementations.

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

Oracle BI EE – Displaying UDA’s of Essbase Dimension Members – A comparison with Hyperion Financial Reporting

Posted by Venkatakrishnan J on June 15, 2008

Another common question that anyone might get while working on the BI EE – Essbase connectivity is to know a way for displaying UDA’s of Essbase dimension members. Essbase supports this concept called UDA’s wherein one can assign the same attribute to multiple dimension members so that one can do cross dimensional attribute analysis. For example, if you take the default Sample-> Basic database you would notice that the Market Dimension members would have some UDA’s assigned to it like Major Market, Small Market etc.


But if you import the database/cube to BI EE, you would notice that there is no provision for explicitly displaying UDA’s. So, in order to display all the members to a specific UDA, we would have to use the below EVALUATE function from within Answers.

CAST(EVALUATE('UDA(%1.Dimension,"Major Market")',Market.Region) as character(30))



Lets try drilling down on say East and see what happens.


It looks like BI Server is not drill happy whenever you use UDA’s. Also, one cannot have a report like the one shown below in BI EE(atleast in


The major reason for the above errors that you see is due to the fact that BI EE is more of a relational reporting tool. It still is not Multi-Dimensional aware per se and it still tries to treat a multi dimensional data source in the form of rows and columns. But one good thing is that it is getting there and hopefully we should have full blown multi dimensional query capability in the coming releases. Now, lets try to achieve the same in Hyperion Financial Reporting.

As a first step lets try to create the above shown excel report. In order to do that we would need to have the query as shown below


Once this is done, choose the members for the Region and Accounts.



Now, insert a column just after the Region column. Make sure that it is a column of type formula.


And in the custom Text area enter the below formula (increment the row numbers as needed).

<<MemberProperty(current, 1, Market, UDA)>>



Now, if you look at the report one can get UDA’s as well as the member names. Now, what if we want all the member names for a specific UDA. In order to do that, just have sales and Market columns in your report. Then instead of adding the members add a function as shown below



The above will list all the members to a specific UDA (Major Market) similar to what BI EE did above. What if we want drill downs? Just enable auto expansion and we would get the drills automatically as shown below


There are quite a few member specific formatting that one can do with HFR. BI EE and BI Publisher should hopefully uptake these features in the coming releases.

Posted in All Posts, Hyperion Essbase, Hyperion Financial Reporting, OBI EE Plus | 2 Comments »

Oracle BI EE – Image Maps, HTML and GO URL

Posted by Venkatakrishnan J on June 11, 2008

I wanted to blog about something very simple today which came to me as a question from two different users. If you had noticed my blog entry here, i would have shown how to go about creating image prompts. Lets look at another approach today that will give more control to reporting users. I will be using the same image as used in the previous blog entry (shown below)


So, our idea is to filter a report based on the year values displayed above i.e for example when a user clicks on 2000, then our aim is to open up a report with a year=2000 filter. So, lets start with a simple report as shown below.


Ensure that you have the prompted clause filter on Year. Then go to the narrative view of this report and enter the below HTML.

<img src="<a href=""></a>" usemap="#YearPrompt" border="0">
<map id ="YearPrompt"
<area shape ="rect" coords ="120,7,173,28" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2000" target=_new/>
<area shape ="rect" coords ="119,65,172,89"  href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2001" target=_new/>
<area shape ="rect" coords ="118,124,174,145" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2002" target=_new />


As you see, what this above html does is it produces an image map and assigns the GO URL of the different reports to different map coordinates. So, when you click on Year 2000, the 1st url would pop up. Same would be the case for other years. Or the other option is to put the above HTML map directly in the dashboard Text Object as shown below.


Now, this Image Map would work without even using the Image Prompts. One good thing about this approach is that you can control the positioning of your target report.



Simple, but a better option than image prompts since you have more control.

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

Oracle BI EE – MDX and Essbase – Direct Database Requests

Posted by Venkatakrishnan J on June 8, 2008

As i have mentioned before BI EE uses the C-API of Essbase to fire queries against Essbase. The queries fired are standard MDX queries. Today, lets look at the MDX queries fired by BI EE and see what these queries actually return in Essbase Administration Services. Also, lets see what kind of MDX queries does BI EE accept. In order to do that, we would have to first enable the logging level of the user to 2 or 3.


Now, lets create a simple report out of Sample->Basic cube.


Now, lets check the MDX query which is being fired back by BI EE to Essbase.

  set [Market3]  as '[Market].Generations(3).members'
  set [Population2]  as '[Population].Generations(2).members'
  set [Q] as 'crossjoin ({[Market3]},{[Population2]})'
  {} on columns,
  {[Q]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sample.Basic]

Lets copy this query and fire this in Essbase Administration Services


Now, lets enable Direct Database Request and lets fire the above query from there.


So far so good. It looks like the query is correct and it gets the columns out properly. But lets see what happens when we view the results.


Ouch. Looks like direct database requests do not work with Essbase. Strange. Since the reports work i was under the impression this would also work. I am pretty sure that i am doing something wrong here. One other strange thing is that this is not a BI EE specific error but rather a C-API specific error. I am not sure what to conclude here since i cannot continue my testing here untill i get this to work. My idea was to test all the possible MDX queries that BI EE would accept. I cannot do that since it looks like direct database requests do not work on Essbase.

Posted in All Posts | 1 Comment »

Hyperion Financial Reporting – Getting Started

Posted by Venkatakrishnan J on June 7, 2008

Though BI EE has connectivity to Essbase, there are still quite a few gaps that would have to be addressed before it can be used for creating financial reports out of Essbase. I hope 11g can bridge that gap. So, lets look at another tool today called Hyperion Financial Reporting. Hyperion Financial Reporting (HFR) is a component similar to BI Publisher. The major difference between these 2 products is that, BI Publisher can create financial reports only out of relational databases. But HFR can report out of Multi-dimensional data sources like Essbase, MSAS., SAP-BW and also on Hyperion Financial Management (HFM). Also, HFR provides drill down, drill backs etc out of the box which BI Publisher currently does not. I hope BI Publisher roadmap covers integrating features of HFR in the long run. In order to install and configure HFR there are 4 components that one would have to download. They are

1. Shared Services
2. BI Plus Services
3. BI Plus UI Services
4. BI Plus Client

HFR reports are generally created out of a client component called as Reporting Studio. All the reports created out of this studio are then published into the Hyperion BI Plus Workspace so that users can access the reports through a URL. Once the installation is done, we would have to create an user in the shared services and provision it to access BI Plus services.



The reporting studio connects to the Financial Reporting Server through the user created above.


Now, lets start with creating a simple report. As you see below, the reporting studio interface looks very simple and uncomplicated. But the features of this product are so good that you cannot think of using any other tool for doing Multi Dimensional financial reporting.


Lets start with creating a simple report on the Basic cube of the Sample application. We start with creating a grid in the report which would in turn ask us to provide the connection details.



Once this is done, lets choose Year in the Rows and Market in the Columns and see what happens.


Now, lets choose some dimension members for the Year and market dimension.


As you see we can choose any specific level for reporting (which is a major drawback currently in BI EE).


In order to enable drills, choose the member for which you want to drill and enable “Allow Expansion”.


Now, save this report and view the report from workspace (Since in my case there is no data in the cube you would see only #Missing in the report. )


As you see you can also drill down/drill back on all the levels.


In a future blog entry we shall be looking at some complex reports and how those can be created in HFR.

Posted in All Posts, Hyperion Financial Reporting | 36 Comments »

Hyperion Essbase 9.3.1 – Loading Valued Based Hierarchies (Parent-Child) using Admin Services and Rules Files

Posted by Venkatakrishnan J on June 2, 2008

For the past couple of months i have been primarily working with Hyperion Essbase and the BI EE connector to Essbase. There are a lots of things that i want to write about which i would be doing in the coming week or so. As i had blogged here, there are many ways of loading data into Hyperion Essbase. They are

1.   Essbase Administration Services
2.   Essbase Integration Services
3.   Hyperion Data Integration Management
4.   Oracle Data Integrator

The most commonly/widely used ones are the first 2. I had already shown you here on how to go about loading Value based hierarchies using EIS(Integration Services). Lets see how to go about doing the same using Administration Services(EAS). EAS uses the concept of Rules Files to load data into an Essbase database. In our example, lets load the simple EMP – MGR parent child hierarchy from the EMP table of the SCOTT schema into a sample Essbase database. So as a first step, log into the Essbase database using EAS. And then open the outline.


Now open the Data Prep Editor from File -> Editors menu. You should be getting something like the screenshot below


Basically data prep editor is the editor that we would be using to create a rules file. Just like EIS, this editor also allows data load directly from a relational database. In our case, go to File -> Open SQL. Ensure that you are assigning the database to the SQL. Once that is done, enter the SQL given below.




In the above sql, we are doing an NVL in order to make Essbase understand that we are creating child members for EMP dimension. So, basically all our members would roll up to EMP (which would be same as the EMP dimension). If you closely look at the second screenshot the root is actually in the middle of the result set. We shall see what impact this has on the final dimension later. Once this is imported, go to View and set this rules file as a file with Dimension build fields.


Then assign an outline to the rules file.


Then go to the Dimension Build Settings and set the 2 properties as shown below. The Allow Property changes will allow us to set the aggregation (‘+’ in our sql above) of the members and the Parent Child references will automatically create the hierarchy based on the parent child relationship in the sql.


Then go to the field properties and assign the dimension, Parent, child, alias and property properties for the each of the fields.



Now validate the rules file. Then save it. Lets load the dimension using this rules file.



Now if you see, the data is not loaded properly. 7902 and 7698 should actually come under KING (7839). But that has not happened here. So, what this means is that Essbase rules file expects all the parents to be loaded first and then the children.  But in the case above, since we did not have a proper ordering of the data, the 2 children 7902 and 7698 got loaded before their parent KING (7839). Now, in order to rectify this go the SQL of the rules file and change it to the one shown below

NVL(to_char(MGR), 'EMP' ) MGR,


Now the above will give us the data in proper format (parents will get loaded first since the connect by clause internally starts with the root).


Posted in All Posts, Hyperion Essbase | 9 Comments »