Learn how to connect to Stored Procedures within your own QlikView application

[harvinderjohal]

Relational Database Systems, such as Microsoft SQL Server, Oracle, IBM DB2, etc.. have the ability to contain multiple types of objects.
These objects can be Tables (that contain the raw data), Views or Queries (questions on the data contained within Tables), and they also have Stored Procedures. 

Stored Procedures are essentially little applications or programs that can run within the database to perform operations.  Some of those operations can be data bound – such as “prompt the user to enter a value of data to perform a selection on, go get some data from Tables, do some math, perform some logic, and then spit out a result set of data when you are done”.  This is the form of Stored Procedure you can connect to and get data to perform your analysis. 

The other form of operation a Stored Procedure can perform would be a database or task driven operation such as “perform an update on the database” or “go do something” – with Tasks, no real data is returned or created, it’s just a task that the database is performing and that task is something that is either successful or it’s not.  Tasks are not something you can analyze within any tool other than a database tool that can show metrics on task success/failure.

Older BI and standard query tools would rely on Stored Procedures to perform the heavy lifting of data for them – this was done for performance reasons so that any responsibility of performance could be pushed onto the database. 

A second reason why these tools used Stored Procedures was the same reason why these tools were effective during the dawn of BI – they only answered the first order level question.  If you had any follow-up questions from the data that was returned, you would have to create a new report, query, or Stored Procedure.  Unfortunately, those BI or standard query tools never evolved beyond this ability. 

QlikView allows you to leverage a technology that we have developed called the ‘Associative Model’ and it is a model that works ‘in memory’. 

In memory means we do not need to rely on the database to perform the heavy lifting for us, because we can handle large amounts of data, and the Associative Model enables the ability for you to ask follow-up questions of your data – ie. Not only can you ask the 1^st order level question, you can ask the 2^nd, 3^rd, etc.. order level questions all within your QlikView document (helping to reduce the need for creating more and more static standard reports).

As per this discussion, you can connect to a Stored Procedure and return data, just be aware of the restrictions that you are placing on your self with already having answered a 1^st order level question in the Stored Procedure – it means that are you forcing discovery down a path based on a question you already asked.  If you allow QlikView to pull data directly from Tables instead, the ability to perform Business Discovery and analyze data in any direction is greatly enhanced.

In order to connect to a Stored Procedure, you will need to do 3 things.

1. Establish connection to your database
2. Hard Code a SQL selection string into your QlikView script to your Stored Procedure
3. Enable Setting changes to perform read/write operations to your database.

Within the Script Editor, establish your connection to your database via ODBC or OLEDB.

Once a connection is established, you will need to enter a SQL statement that will fetch data from your Stored Procedure as well as send over any parameter values that the Stored Procedure is expecting.  In the example below, the Stored Procedure name is CustomersStoredProc.  It has the full database and object name listed:  QTSales.dbo.CustomersStoredProc.  The Input Parameter that the Stored Procedure is expecting is called @Country and the data/parameter value I want to pass to to the Stored Procedure to return for Country is 'Canada'.
Ie.

SQL
EXECUTE QTSales.dbo.CustomersStoredProc @Country = 'Canada'

A final thing to perform is to check the setting for ‘Open Databases in Read and Write mode’ option under the Settings tab.  This will allow for interactivity with your Stored Procedure and parameter passing to your database.

NOTE – checking this setting will prompt/warn you when executing the script.

[#2]

Hi Johal,

Thanks for the above details. This is really helpful.

I have a situation where in i need to pass the Input Parameter from the URL (Query String) to the stored procedure and bind the dashboard.

Ex: www.google.com/ID=4

I am trying to access the above ID field in a variable and then pass to the Stored Procedure as

sql exec sproc $(ID);

I am facing issues to access the query string value and also to bind a variable to the stored procedure dynamically instead of declaring as above(@Country='Canada').

Please let me know if you find any solutions.

Thanks

Sai Krishna

[kalyandg]

hi harvey johal,

how can i store this to qvd.

[harvinderjohal]

Hi Sai Krishna,

Data reload occurs based on how the script is created.  Upon reload of an app, QlikView will create an in memory list of fields and values from those fields in its own internal data structure (a highly optimized structure and is not a direct copy of the original datastore's structure - though not an exhaustive description of how QlikView manages data inmemory, refer to the following for a bit more info:  http://www.qlikviewaddict.com/2012/02/compression-in-qv.html).

Once the reload is complete and data returned, that data is now in memory within the application.  When you are viewing the application and making selections, it is based on data in memory and is after the reload has occured.

My suggestion is to modify the Stored Proc not to just look for a specific value and filter on that value, instead, modify the Stored Proc to accept a wildcard, such as an '*' that will return all the results into the Stored Proc's temp table.  This way, all data will be loaded into QlikView and based on user interaction of the application, the user can get the data that they need to see - it'll filter on the QlikView side.

If this is not feasible for you, the only other thing I can think of is the following:  I haven't tested this but there may be a way to modify the script prior to the reload by building a process or application that replaces the script with an updated script - ie. for the one value of the stored proc parameter.

Here's how to create a -prj folder with objects from a .QVW expanded into a folder.

1.  Assume you have a QlikView file called 'Sales.QVW' in a folder (C:\Temp)

2.  In C:\Temp, create a new folder called Sales-prj.  You should now have a Windows folder structure of C:\Temp\Sales-prj

3.  Open the Sales.QVW in the QlikView Designer.  Just save the file and close it without changes.

4.  In Windows Explorer, browse to the C:\Temp\Sales-prj folder.  Notice all the files created.  The file you're looking for is 'LoadScript.txt'.

5.  Before you do anything - back up your .QVW file and save a copy of the current 'LoadScript.txt'.

6.  Now if you make a change to the StoredProcParameter in the LoadScript.txt and save the file, open and reload the Sales.QVW in the QlikView Designer - it should load data from the StoredProc with the new value.

What I'm thinking is that you could create a custom app or batch or something that could replace the LoadScript.txt with a new script that would have a new parameter value entered.  Not 100% out of the box but it could help address your need.

Harvey

[harvinderjohal]

Hi Kaylan,

Refer to the online help in the QlikView Designer - perform an Index search for a function called 'Store'.  This will give you syntax on how to store out to a .QVD.

Harvey

[bob_zagars]

Hi Harvey Johal,

I understand your concept and am not sure if you have an inside track to writing to a database.  My first question would be what the primary reason behind Open Databases in Read and Write Mode.  Searching on QlikCommunity I have notice that every related question to Write Mode is stored procedures and I have noticed that when it comes to updating the database everyone refers to batch processes especially when it comes to something like the budget process (INPUTFIELD) which is saved for every user and not shared between users.

In the stored procedure examples a reference to the connection string needing Mode is write which would be an easy fix by adding this in the building of the connection string.  Just something I want to state as my opinion.

The batch process for updating something similar to a budget using INPUTFIELD, sound very logical but is actually very difficult because AJAX cannot run macros and now I am getting QlikOcx errors on the server opening a new file with IE plugin.  The only successful way of implementing this is with the desktop client which is not my preferred choice.

So my question is if I open a table in write mode and make one or more columns INPUTFIELD’s will it update my database?

Bob Zagars

[phcaptjim]

I am having trouble getting this stored procedure to return data (MS SQL Server):

RSAM_Data:

Load *;

SQL EXEC rsam.dbo.RS_SavedSearch

          @i_iSearchID = 3025,        // Search ID

        @i_sUserID = 'administrator',    // User the search should "run as"

        @iSearchCaller = 5,

        @i_iDisplayRsamID = 0;        // Uses RSAM_ID for column names

The load script executes without error but no data is returned......

4/7/2015 7:41:41 AM: 0899  OLEDB CONNECT*Provider*

4/7/2015 7:41:42 AM: 0904  RSAM_Data:

4/7/2015 7:41:42 AM: 0905  Load *

4/7/2015 7:41:42 AM: 0906  SQL

4/7/2015 7:41:42 AM: 0907  EXEC  rsam.dbo.RS_SavedSearch

4/7/2015 7:41:42 AM: 0908            @i_iSearchID = 3025,    

4/7/2015 7:41:42 AM: 0909          @i_sUserID = 'administrator',

4/7/2015 7:41:42 AM: 0910          @iSearchCaller = 5,

4/7/2015 7:41:42 AM: 0911          @i_iDisplayRsamID = 0

4/7/2015 7:41:43 AM:      Execution finished.

Any thoughts or ideas?  Thanks!