Our Blog

Prompts and Parameters – Recommendations for use within Framework Manager

By Craig Harding, Consultant for PerformanceG2

Better performance will be realized by using parameters and prompts within Model Query Subjects, filter objects or stand alone calculations. This will allow for better SQL generation than inserting them within the SQL of a Datasource Query Subject.

These recommendations lead to better SQL generation, which in turn, leads to better performance.

Nearly any prompting can be done in a Model Query Subject:

[Product dimension].[Product line].[PRODUCT_LINE_CODE] =
 #prompt('Product Line Code', 'integer', '1')#

Same with Calculations:

Case [Product dimension].[Product line].[PRODUCT_LINE_CODE]
 When #prompt('Target Product Line Code’, 'integer', '1')# then 1
 Else 0

The added benefit of placing prompts in a stand alone calculation is that if the report author does not use the calculation, then the prompt is no longer needed. There is no need to add this case statement to the Base SQL.

When using calculations and filter objects in Model Query Subjects or as stand alone objects, the Datasource Query Subjects can be left untouched. This means the SQL remains minimized and eliminates the need to fetch metadata.

Contact the Blogger

This entry was posted in Technical Concepts and Ideas, Tips and tricks and tagged , , , , , , , , , , , , , , . Bookmark the permalink.

Comments are closed.

PerformanceG2 Menu