Cognos 8 & Oracle BI EE 10.1.3.4.1 - Reporting using Cognos on BI EE Metadata - BI Server ODBC

In the last blog entry i had given a high level overview of the various components comprising the Cognos 8 BI Suite. One of the most common questions that we tend to get in conferences is how do we go about using Cognos to report out of the model designed in BI EE. BI EE is probably one of the best tools out there in the market with a very good data modeling (for reporting) framework. So, if we can leverage the capabilities of BI EE from other reporting tools, it can really turn out to be a much better value add.

As you would probably know, BI Presentation Services connects to BI Server using ODBC on the default 9703 port. Since BI Presentation Services itself uses ODBC, any other tool using the BI Server ODBC connection should theoretically be able to leverage all the features of the BI Server without compromising much on the performance. Unfortunately, it is not as straightforward to leverage the ODBC layer of BI EE. We will have to apply certain workarounds depending on the tool that we are connecting from. Lets look at how Cognos 8 can leverage the BI Server framework.

We will be using the Cognos Framework Manager to first create a connection to the BI Server. The pre-requisite for this is, one would have to install the BI Server ODBC client on the machine containing the Cognos Content Server. I shall be demonstrating this using the default Sample Sales schema of BI EE.

We first start with creating a Project in the Cognos Framework Manager and then defining a connection using the ODBC interface to BI EE.

Then start the import process. During the import, it will just show the default catalog assigned in the BIEE ODBC source. Just choose that, as the import process will not import anything at all. The main reason for this is the ODBC implementation of BI Server is a little different from the other common ODBC sources. Hence the default import process will not import anything.

In the namespace AnalyticsWeb create a new Query Subject using the data source option. The reason for doing this is, it provides a capability for us to handcraft the Logical SQL in a format that Cognos recognizes.

While entering the SQL, ensure that you are including all the columns belonging to a single Subject area in BI EE. Remember there are no external SUM etc kind of GROUP BY operations that are needed in logical SQL unless you want to join data across subject areas. BI EE will accept the logical SQL and will convert it to necessary Physical SQL or MDX using it. For illustration, i have used the logical SQL given below

SELECT
"D0 Time"."T02 Per Name Month" as Month,
"D0 Time"."T03 Per Name Qtr" as Qtr,
"D0 Time"."T04 Per Name Half" as HalfYear,
"D2 Market"."M01  Market" as Market,
"D2 Market"."M02  Area" as Area,
"D4 Product"."P01  Product" as Product,
"F2 Units"."2-01  Billed Qty  (Sum All)" as BilledQty,
"F1 Revenue"."1-01  Revenue  (Sum All)" as Revenue
FROM
"Sample Sales"
ORDER BY
Month, Qtr, HalfYear, Market, Area, Product

Ensure that you alias the columns with proper names. Once this is done go to the Query Information tab. You will first get an error as shown below.

The main reason behind this is, like BI EE logical SQL, Cognos also has its own internal SQL language called Cognos SQL. This somehow seems to add some un-necessary constructs to our Logical SQL. Hence you will not be able to test it using this method. To bypass this error, go to the options link in the Query Information tab and choose the Pass-Through option. What this will do is, it will always push the above Logical SQL natively to BI Server without adding any custom Cognos specific SQL constructs. Though the Native SQL option will work as well, i will recommend using Pass Through as BI Server ODBC does not support derived tables in the SQL.

You should be able to test the SQL successfully now. You can also see the Cognos SQL and the native SQL in the Query Information tab.

Choose the AnalyticsWeb datasource and in the Query Processing property choose the Limited Local option. This will enable some processing to be done on the Cognos server. Also choose the Rollup processing to be Local as well as we do not want SUM and GROUP BYs to be pushed in to the Logical SQL

Once this is done, create a package and just include that the Query Subject that we created above and publish the package to Cognos Connection Server.

We can leverage the published Query subject from the Report Studio and Query Studio. Everything will work from Pivot tables, Filters etc. Lets now create a filter in the Query Studio and then look at the actual Logical SQL that is getting fired in the back end.

You will notice that filters are not passed back to the Logical SQL. This is where performance can be a real issue. If we do not have a means of pushing the filters to the Logical SQL then we cannot actually put this into any practical use since irrespective of the filters applied, the Logical SQL will remain the same and hence degrading performance. In effect, Cognos does the filtering in its memory rather than pushing it natively to the SQL. The main reason is since we are using Pass-Through SQL, Cognos cannot determine how to push the where clause into the SQL and hence will result in filtering in its memory. The only way Cognos can push the where clause to the actual Logical Query is if we had chosen the Cognos SQL option (which unfortunately does not work for BI EE). I believe we can muck around the Cognos CWM export (like UDML for BI EE) XML file and then make the Cognos SQL to somehow work. But i will leave that for now. Also custom Cognos specific calculations will be done at the Cognos layer. Just make sure that if you want to leverage BI Server calculations, you need to put the calculation functions in the Logical SQL itself. Lets look at a workaround to get the filters to work without any performance issues.

For the workaround, do not use the filtering option in the query or report studio. Instead open up the Framework manager and go to the SQL in the Query Subject. Add custom Filters there with a macro prompt enabled. All the attributes on which you would like to apply filter on, enable either the single select or the multi select macro prompts as shown below

SELECT
"D0 Time"."T02 Per Name Month" as Month,
"D0 Time"."T03 Per Name Qtr" as Qtr,
"D0 Time"."T04 Per Name Half" as HalfYear,
"D2 Market"."M01  Market" as Market,
"D2 Market"."M02  Area" as Area,
"D4 Product"."P01  Product" as Product,
"F2 Units"."2-01  Billed Qty  (Sum All)" as BilledQty,
"F1 Revenue"."1-01  Revenue  (Sum All)" as Revenue
FROM
"Sample Sales"
WHERE
"D2 Market"."M02  Area" = # prompt('Area1')#
AND "D4 Product"."P01  Product" = # prompt('Product')#
ORDER BY
Month, Qtr, HalfYear, Market, Area, Product

Republish the package again and when you try to create a report, you will see 2 prompts appearing as soon as you include some columns from this Model. These are the filter values which will be passed directly to the Logical SQL

The logical SQL will now contain the filters.

This is probably the main thing to consider while reporting out of BI Server ODBC connection. In fact, even BI EE itself cannot report out of its own ODBC connection(kind of strange). Though the import will work in the case of BI EE, we cannot build a model out of an existing repository through ODBC and then report out of it. It actually gets even more interesting since there are quite a few database features that we need to eliminate to somehow make it work(for BI EE). But in the case of Cognos, the only thing to be aware of is the filtering and the calculation functions. Apart from that everything should work. One can even build dimension models out of this to enable drills from the Cognos layer.