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

A recent release of Qlikview added a new feature called "Direct Discovery",  that allows Qlikview reports to directly access a database datasource on-demand . Qlikview, by default, loads all required data up-front into its own in-memory column-store database, but this new Direct Discovery feature allows it to in addition retrieve data from an SQL data source, on-demand, to supplement this in-memory dataset.

This should allow larger datasets to be accessed from within Qlikview reports, and I therefore began to wonder if I could use this new functionality to access some of the very large data sets that we often see with OBIEE reports. In fact - could I use the Qlikview UI but with the OBIEE RPD as the datasource,  potentially replacing the Qlikview in-memory cache completey, instead using OBIEE's BI Repository, federated query capability and its own caching options? If so, not only would this give me access to all of the joined up heterogeneous datasources contained in the RPD, but also the comprehensive security model that OBIEE provides. So lets first see if I can use Qlikview to access data via an OBIEE RPD, and then in I'll cover the direct discovery feature in the next post.

To do this, I going to use a local copy of the SampleApp V305 Virtual Machine which contains an OBIEE environment that uses a number of different data sources available via the RPD. I created a simple OBIEE report containing Product, Date and Revenue from the I – Sample TimesTen folder, which I’m going to replicate using Qlikview with the OBIEE RPD as the data source:–

I started Qlikview and selected File > New, by default this starts a wizard that expects a file based data source, however using a database data source is a much more manual task, so Cancel this wizard and choose File > Edit Script to open the Script editor.

Click the Connect button and select the appropriate BIServer ODBC connection, supply the user name and password, click OK, and a connection statement will be added to the script.

Click the Select button and you will see the Folders and objects you normally see in the Presentation layer of the RPD shown as tables and columns. Using this wizard to create SQL select statements for product, time and Revenue, I created the following script:–

ODBCCONNECTTO QlikViewTest (XUserIdis LMUNCIJOPDZcWSFPDD, XPasswordis HECQSARMNLacWYIGTRNA); 
SQL SELECT * 
FROM "I - Sample TimesTen".Products; 

SQL SELECT * 
FROM "I - Sample TimesTen"."Time"; 

SQL SELECT "1- Revenue" 
FROM "I - Sample TimesTen"."Base Facts"; 

Running this script shows that data is retrieved, but when finished nothing is shown. This is because Qlikview does not have a default presentation, so I add some report objects and get the following:–

Going back to the Script editor I can see that the select that gets the Revenue does not have any key columns that Qlikview can use to link the values to the data. Qlikview automatically links datasets using common field names.

I reopen the Script Editor, highlight the Revenue select and click the Select button, scroll down to the Base Facts table and examine the available fields. Using this wizard I can only select the fields that have been put into the folder in the presentation layer and this does not provide the necessary product and time keys. This gives me two options:–

• Add suitable key values to the appropriate presentation layer folders
• Manually create a suitable piece of logical SQL

ODBCCONNECTTO QlikViewTest (XUserIdis LMUNCIJOPDZcWSFPDD, XPasswordis HECQSARMNLacWYIGTRNA); 
SQL SELECT *

FROM "I - Sample TimesTen".Products;
SQL SELECT *

FROM "I - Sample TimesTen"."Time";
SQL SELECT "I - Sample TimesTen"."Products"."P4 Brand" "P4 Brand",

"I - Sample TimesTen"."Time"."T05 Per Name Year" "T05 Per Name Year",

"I - Sample TimesTen"."Base Facts"."1- Revenue" "1- Revenue"

FROM "I - Sample TimesTen"; 

ODBC CONNECT TO QlikViewTest (XUserId is cJbQJYJOPDZcWSFPCC, XPassword is CHAeeARMNLacWYIGTBUA); 
ProductTable: 
LOAD Product, 
     Brand; 
SQL SELECT "P1 Product" as Product, 
"P2 Product Type", 
"P4 Brand" as Brand 
FROM "A - Sample Sales".Products; 
SQL SELECT "E - Sample Essbase"."Time"."T05 Per Name Year" as Year

FROM "E - Sample Essbase"; RevenueFact:
SQL SELECT "I - Sample TimesTen"."Products"."P1 Product" as Product,

"I - Sample TimesTen"."Time"."T05 Per Name Year" as Year,

"I - Sample TimesTen"."Base Facts"."1- Revenue" as Revenue

FROM "I - Sample TimesTen"; 

• an ODBC connection to the BIServer
• an appreciation of the Presentation layer, so that the correct objects can be accessed
• Manual creation or editing of generated logical SQL to enable Qlikview to successfully link datasets together.