Which is better Direct read to DB or Stored procs?

[Anonymous]

Hi Guys

Just wanted to find out what are the benefits of reading Stored Procs and benefits of reading directly the Database

The issue i have is there is way to much data (within the financial sector) and many calculations being done. so the Sql developers have advised they can create Stored Procs

or

I can learn the Database and do everything in Qlikview.

From experience anyone know of any ideas?

If there are any white papers on this i would appreciate this a lot!

Thanks

Any help appreciated

[sinanozdemir]

Hi,

Stored Procedures can be used for many reasons. In your case, all the complex joins are put in a stored procedure so that you don't see underlying tables, views, and joins. Also stored procedures allow you to put in your own parameters, such as dates, keywords, and numbers for thresholds.

The way we use is to have end-users/analysts to type the date range for a very complicated query:

Exec sp_profitLoss '2015-9-01', '2015-12-31' -> so this stored procedure takes two parameters; one is period start date and the other one is period end date.

I found this user intuitive because users/analysts don't have to worry about learning the complex data models and other things.

Hope this helps. 

[petter · Accepted solution]

If the stored procedures are well suited for delivering the optimal tables to be connected in a good QlikView data model and you get high quality and responsive support on this development I would say that is perfect. Especially if the source systems tables are complex and many. A good QlikView data model is one that lets you most easily create the right visualisations by having the most suitable dimensions and the correct measures so it is quick and easy to develop and easy to maintain. More often than not this turns out to be a star schema - a dimensional model.

On the other hand being self-served by going directly to the source tables could have benefits too. So it all comes down to what kind of competence you and the SQL developers have and how optimal the interaction between you and them will be.

I have no knowledge of any whitepapers  that covers this topic specifically. However at least a couple of the QlikView books that has been published in recent years covers some of these considerations if not all but. Have a look at Stephen Redmond's "Mastering QlikView" and Oleg Troyansky's "QlikView your Business".

[eya]

The correct approach is using QVDs as a staging layer to speed up reads, and let Qlik "associate" the tables naturally.

SQL joins (within a stores proc) will may not handle full-outer-joins (data left behind), and will fall in the fan-trap, chasm-trap (data being double counted).