I recently saw people asking how to use Custom SQL in Lookup_Ext. I searched for it on line but could not find much written on this subject. So this article will explain in short how we can use this feature in Lookup_Ext function which is less known and uncommon among DS Developers (at least I have not seen many developers using this feature) but could be very effective and will improve performance in some scenario.
Purpose:
The purpose of the Custom SQL in the Lookup_Ext function is to improve the performance by limiting data while retrieving into memory, in case doing pre-load cache.
Example:
If you have a DIM_DATE table with 100 years of data but you had transactions only covering the previous week requiring a look up. You may add the Custom SQL to only select the rows from DIM_DATE covering the past week and reducing the size of the lookup table in memory from millions rows to a limited no. of rows.
You can use aggregate functions as well and perform calculations. If anything looks complex to implement in DS but easier at database level then this feature can be useful.
But, you have to ensure that the SQL here selects all the columns you need in the Lookup as well as whatever you have put in the WHERE clause.
Implementation:
Example to use an aggregate function using Custom SQL to calculate a sum of salary for particular Employee and Department.
SQL Code inside Custom SQL:
How to identify if Custom SQL being used in your Lookup_Ext?
You can see a big exclamation mark in your Lookup_ext screen. If you are not using Custom SQL this exclamation mark does not appear.
Limitations:
The Custom SQL is inflexible as there is no way to pass any runtime variables into the Custom SQL. The SQL seems to be fixed at the time of development.
Hope this helps.
0 comments:
Post a Comment