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:–

The first task is to use the ODBC driver for BIServer, installed as part of the standard OBIEE installation, to created a system DSN to allow access to the RPD for Qlikview to use.

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:–

I can see some data, but what I cannot do, is the Qlikview thing of clicking on a data item and the rest of the data changing to reflect this choice. Its useful to look at the table joins, select File > Table Viewer to see what Qlikview has done with the datasets :–

Its is easy to see that none of the datasets are linked and all of the datasets need to be linked to allow Qlikview to highlight associated data.

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

I want to avoid putting key values into measure folders in the presentation layer as that goes against best practice and usability practices. A much easier option is to manually create some logical SQL, if fact OBIEE provides this on the Advanced tab of the Analysis editor.

I don’t need all of this generated logical SQL, but it is a good starting point. I copy this into my Qlikview script editor, and edit it just retrieve the data and my script is now:–

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";

Notice that I have removed some of the generated sql and changed the aliases to be the actual column names to help Qlikview link the data. If I execute the script I now get a much more Qlikview type report, where i select a Brand and Qlikview shows me the related years and the total revenue:–

A quick look at the table view shows one of the most important parts of a Qlikview report, the dataset links:–

Now that I’ve got Qlikview using the RPD and I can reproduce the data I saw in OBIEE, but I want to extend the logical sql and make a more interesting report. So i manually edit the sql to get this:–

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";

With this script, I’ve only selected the data objects I need to link and display and I’ve used the power of the RPD to select the product data from an Oracle database, the time details from an Essbase cube and revenue data from a TimesTen database. These separate datasets are then combined into a single Qlikview report:–

From this brief article it can be seen that we can successfully use the Qlikview UI with the OBIEE RPD to create reports, but three things are necessary:–

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.

In the next part, I will look more closely at the different logical sql options and have a look at the Direct Discovery feature to retrieve the data on-demand from the RPD, thereby avoiding duplication of data and the potential use of TimesTen or Essbase as the underlying in-memory database.