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 1st order level question, you can ask the 2nd, 3rd, 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 1st 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.
- Establish connection to your database
- Hard Code a SQL selection string into your QlikView script to your Stored Procedure
- 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'.
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.