Oracle BI Suite EE

OBIEE as a Data Source for Qlikview, and the New "Direct Discovery" Feature: Part 2

Richard Yeardley

Sep 17, 2013 • 7 min read

In my last post I explored how to use Qlikview, an in-memory query tool similar in concept to Endeca Information Discovery, to connect to OBIEE's BI Repository and use it as a data source. The conclusion was that a Qlikview report could be easily and successfully produced as long as some knowledge of the presentation layer and OBIEE logical SQL was available. In this follow-up article I'm going to take a look at the new Qlikview feature I mentioned yesterday, "Direct Discovery", to see whether it's possible to have Qlikview bypass its own in-memory cache and use the BI Repository instead, something that's particularly interesting in the context of Exalytics where that repository might in-fact point to a TimesTen database, giving us the ability to use Qlikview's UI whilst accessing data through the OBIEE enterprise, and engineered systems framework. Before I do that though, a brief diversion into the world of OBIEE's "logical SQL" syntax, on which this integration idea rests.

Standard logical SQL, either generated by OBIEE or manually written, will access the Presentation layer of the BI Repository (RPD) and will use appropriate joins and security as defined by the business model and mapping layer, and the physical layer, in that repository. Now, a "best practice" RPD hides all the physical datasources, necessary joins, both physical and logical, and includes enough security so that users only see data they are authorised too; for most reports this is enough, but some reports may need to access to data in a slightly different manner and with more access to these underlying implementation details, and this may be achieved by writing logical SQL that accesses these other layers in the repository.

There are several options that can be used with OBIEE logical SQL to target the query at these different layers within the RPD, and these advanced techniques may be used to fulfil more complex reporting requirements. The following sections will demonstrate these techniques.

The first example is a simple logical SQL to get a dataset of total revenue by product by year:–

SELECT 
"I - Sample TimesTen"."Products"."P1 Product" as Product, 
"I - Sample TimesTen"."Alternate Calendars"."T35 Cal Year" as Year, 
"I - Sample TimesTen"."Base Facts"."1- Revenue" as Revenue 
FROM "I - Sample TimesTen";

This basic form of logical SQL does not need to have any joins and aggregations specified as they are all provided by the BI Server for you, and only the subject area name needs to be given as the table name. This SQL is translated by the BI Server to the syntax of the underlying technology and the appropriate joins, aggregates and groupings added.

The same dataset can be produced with logical SQL from the Business Model and Mapping layer:–

SELECT_BUSINESS_MODEL 
"P1 Product" as Product, 
"T35 Cal Year" as Year, 
"1- Revenue" as Revenue 
FROM "05 - TimesTen Sample"."D1 Products (Level Based Hier)", 
     "05 - TimesTen Sample"."D0 Time", 
     "05 - TimesTen Sample"."F0 Revenue Base Measures";

When selecting data from the Business Model and Mapping layer, tables must be added to the FROM section for each object selected, but joins need not be added. The SQL is still translated by the BI Server to the syntax of the underlying technology and the appropriate joins, aggregates and groupings added.

The same dataset can produced with logical SQL from the Physical layer:–

SELECT_PHYSICAL 
b.Prod_Key as Product, 
A.CAL_YEAR as Year, 
sum(B.Revenue) as Revenue 
from "05 - Sample App TimesTen".."BISAMPLE_TT"."SAMP_TIME_QTR_D" A, 
     "05 - Sample App TimesTen".."BISAMPLE_TT"."SAMP_REVENUE_F" B 
where A.QTR_KEY = B.Bill_Qtr_Key 
group by A.CAL_YEAR, B.Prod_key;

When selecting data from the Physical layer, the SQL statement is passed directly to the underlying datasource, so must have the appropriate and correct syntax and included all joins, aggregations and groupings.

All of these types of logical SQL can be used within a Qlikview report. However, they will need to be manually added to the load script, as the select wizard will only generate standard logical sql.

An example of using and combining these more advanced OBIEE logical sql statements can be seen in the following Qlikview load script:–

ODBC CONNECT TO QlikViewTest (XUserId is HfOdKYJOPDZcWSFPRC, XPassword is dRMWbARMNLacWYIGTJKB); 

ProductTable: 
SQL SELECT "P1 Product" as Prod_Desc, 
"P2 Product Type" as Type, 
"P4 Brand" as Brand, 
"P0 Product Number" as Product 
FROM "A - Sample Sales".Products; 

Dates: 
SQL SELECT_BUSINESS_MODEL "T35 Cal Year" as Year 
FROM "05 - TimesTen Sample"."D0 Time"; 

RevenueFact: 
SQL SELECT_PHYSICAL 
b.Prod_Key as Product, 
A.CAL_YEAR as Year, 
sum(B.Revenue) as Revenue 
from "05 - Sample App TimesTen".."BISAMPLE_TT"."SAMP_TIME_QTR_D" A, 
"05 - Sample App TimesTen".."BISAMPLE_TT"."SAMP_REVENUE_F" B 
where A.QTR_KEY = B.Bill_Qtr_Key group by A.CAL_YEAR, B.Prod_key;

This load script loads the product details using standard logical SQL from the Presentations layer, time details from the Business layer and the revenue dataset from the Physical layer. These three different SQL statements are sent to the BIServer and three datasets are returned. Qlikview stores all this data in its in-memory database and joins the datasets together using the common field names.

The following report can then be produced:–

Qlikview report from OBIEE

Now we can have a look at the Direct Discovery feature of Qlikview and create a load script and report that will collect the dimensional data at the load time, but get the measure data on request.

The Direct Discovery syntax is reasonably straightforward and mainly consists of two parts. Firstly, the SQL part of the load syntax is replaced with the keyword DIRECT, and at this point you can also add a staleness timeout for the dataset. The second and more important area is to specify the measures that need to be collected when the query is run.

For example:–

DIRECT ( stale after 10 seconds ) SELECT 
"P1 Product" as Product, 
"T05 Per Name Year" as Year 
FROM "I - Sample TimesTen";

The first line of this example tells Qlikview that the following query is a Direct Discovery query and that any data selected will be stale after 10 seconds, so that any further user interaction after this period will need to re visit the database. The two fields selected in this query will be loaded into the Qlikview memory structures to allow the reporting objects to be created. At this point, Qlikview will also go to the database and obtain all of the other columns associated with the table in order to add these columns to its meta data catalog, This is to allow the report writer to add fields to the Qlikview report and expressions. Such columns are known as implicit columns.

Now, as we are connecting to an OBIEE RPD and using OBIEE logical SQL we do not need to specify tables in the FROM reference of the SQL statement but just the subject area name. However, this causes an issue with Direct Discovery, as Qlikview will attempt to select all of the fields associated with the table to add those column names to its meta data; which in this case is all of the objects in the “I – Sample TimesTen” subject area. Not only can this can take a long time, but errors can be produced from the RPD if everything is selected as once. For example, in this case Qlikview will send the following sql to the RPD:–

SELECT * FROM "I - Sample TimesTen" WHERE 1 = 0

Although this will not select any data, it will try to select every column in the subject area and results in a “No fact table exists at the requested level of detail” error, as it does not make sense to select all of the columns from a single data mart at once.

To avoid this situation we must directly specify as part of the query which columns we wish to include in the report. For example:–

DIRECT ( stale after 10 seconds ) SELECT 
"P1 Product" as Product, 
"T05 Per Name Year" as Year 
IMPLICIT "1- Revenue" as Revenue, 
         "10- Variable Costs" as Variable_Costs, 
         "11- Fixed Costs" as Fixed_costs 
FROM "I - Sample TimesTen";

By adding this IMPLICIT list of measures, Qlikview will not interrogate the database for a list of implicit fields, but just add the three specified measure objects to its metadata.

Use of the IMPLICIT syntax is vital to using Direct Discovery with the OBIEE RPD, as only the basic logical SQL can be used, trying to use the more advanced SELECT_BUSINESS_MODEL or SELECT_PHYISCAL options does not work.

It is also worth knowing that this initial version of direct discovery only supports one direct discovery table per QlikView report and the source of the direct discovery can only be a single database table or view.

Updating the previous load script to include a Direct Discovery section now looks like this:–

ODBC CONNECT TO QlikViewTest (XUserId is HfOdKYJOPDZcWSFPRC, XPassword is dRMWbARMNLacWYIGTJKB); 
ProductTable: 
SQL SELECT "P1 Product" as Product, 
"P2 Product Type" as "Product Type", 
"P3 LOB" as "LIne of Business", 
"P4 Brand" as Brand 
FROM "I - Sample TimesTen".Products; 

Dates: 
SQL SELECT "T05 Per Name Year" as Year 
FROM "I - Sample TimesTen"."Time"; 

RevenueFact: 
DIRECT ( stale after 10 seconds ) SELECT 
"P1 Product" as Product, 
"T05 Per Name Year" as Year 
IMPLICIT "1- Revenue" as Revenue, 
         "10- Variable Costs" as Variable_Costs, 
         "11- Fixed Costs" as Fixed_costs 
FROM "I - Sample TimesTen";

This load script loads the product details using standard logical SQL, time details from the Business layer and the revenue dataset is loaded on demand. The following report can then be produced:–

Qlikview report using Direct Discovery

In this successful example, all of the RPD dimensional data was initially loaded into Qlikview’s own in-memory store and then the additional measure totals where loaded upon request when drilling down on the Product Brand. This invoked the Direct Discovery feature and ran additional SQL for each level accessed.

Now that we can use Direct Discovery against the RPD, we have the option to include several different levels and flavours of data aggregation to the reports. Such aggregation could be done using Essbase or TimesTen or even the OBIEE cache, but this would require the appropriate configuration within the RPD.

In summary the following are required to successfully use Qlikview with the OBIEE RPD:–

An ODBC connection to the BIServer from the Qlikview environment
Good appreciation of the RPD
Manual creation of logical SQL to create datasets
Working knowledge of Qlikview load scripts
Use of IMPLICIT fields when using direct discovery

From these brief articles it can be seen that we can successfully use the Qlikview UI with the OBIEE RPD to create reports and also make use of the new dynamic Qlikview direct discovery feature, such that any of the aggregation, security, multi data sources, etc.. features available from the RPD are all available to the Qlikview developer and user. However - it doesn't look like it's possible to replace the Qlikview in-memory data cache entirely, as in this version at least you can only have a single direct discovery table within each Qlikview report, and it also looks like it's necessary to have loaded at least some data into the Qlikview cache so that the direct discovery table can then join back to it.

There's also other restrictions such as direct discovery tables not being available for regular Qlikview drop-down menu controls, so it's probably fair to say that this is a "work in progress" - however if the feature does mature and potentially allow all data to be sourced on-demand like this, it could be an interesting complement to OBIEE's regular reporting tools.

Sign up for more like this.