Please note that the following model has been implemented in SPS06 (revision 60). I am not sure about the feasibility of this approach on the earlier revisions.
Secondly some of the observations on the below model are based on my personal experience and opinion. The SAP experts on this forum can provide better justification of the model behavior in terms of the performance of the query.
As mentioned in the earlier blog Implementation of WTD, MTD, YTD in HANA using Input Parameters only, let’s look into an option to populate the Input parameters for the Graphical Calc view defined in the blog, with the values derived using SQL script.
Rules of the game remain same i.e. same business requirements, assumptions, Sample data and Projections with Constant columns for Pruning, as mentioned in the earlier blog Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection.
The values for the input parameters can be derived in the SQL script by simple calculations. In some cases of complicated derivation rules, even complex SQL, with additional lookups on other tables, can be done. This option provides very good flexibility in terms of value derivation for the Input parameters and Projection filters.
Approach with SQL Script based View for deriving the Input Parameter Values:
In this approach, the Key Input parameters for the DATE_FROM and DATE_TO are defined in the Script based Calc view. Using the value of INPUT_DATE_TO Input Parameter, WEEK_DATE_FROM, MONTH_DATE_FROM and YEAR_DATE_FROM values can be derived with the same logic as mentioned in the first blog. The derived values are then passed to the Graphical Calc view Input parameters.
Please note that the syntax for the Calculated column Expression and SQL script expressions can be different for the same functionality.
The model for Script based Calc view can be represented as follows:
The filter defined in the Script based Calc view can be defined as;
The script code executing the query on the Graphical Calc view can be defined as follows:
The query can be executed against the Script based Calc view with the 2 input parameters for the IP_DATE_FROM and IP_DATE_TO.
The execution plan for the above query is as follows:
The execution plan for the SQL code shows that, despite of using the earlier model of Graphical Calc view and the SIMILAR SQL statement, there is no evident filter push down observed, as it was clearly seen in the earlier Graphical model query execution.
Here the reason could be, that the Script based Calculation view executed the defined query in the script code against the graphical view with ALL columns used in the SELECT statement as defined in the script. Hence all the column joins are performed and there is no Query stripping for the columns. In case of query execution DIRECTLY on the Graphical Calc view (as mentioned in the earlier scenario of Direct Input parameters), ONLY those columns are joined to generate the result set which are required as per the SELECT statement.
Pros of the model:
- This approach provides very flexible way of deriving the values to be passed to the projection filters of the underlying Calc view.
- It provides the possibilities of the lookups on other table columns, which are not directly available in the current view.
- No dependency on the Front end reporting tool to derive the Date values in case of Complex Lookups like Custom Calendar.
Cons of the model:
- The Query stripping for the columns is not possible with Script based Calc views. Hence even if only 1 or 2 columns are selected from the SELECT statement defined in the script, the query is executed for all the columns as defined in the SELECT statement. This has negative impact on the performance.
- The implementation and maintenance complexity is increased. Any change to the underlying Calc view has impact on the SELECT statement and the var_out structure. Since the var_out structure has to be maintained manually, the manual effort and maintenance complexity increases.
This model provides very high flexibility in terms of the value derivation for the projection filter. In case of custom Calendar or Custom Time periods for a specific organization, the Week / Month / Year Start Date may not be same as Calendar Week / Month / Year start date. In such cases, the lookups can be done on the custom calendar table to derive the Week Start date, Month Start Date and Year Start Date.
If the number of columns in the SELECT statement of the script is less, then the impact of the performance will not be very negative. But with the more number of columns in the SELECT statement, more column joins are performed impacting the performance.
Hence depending upon the complexity of the value derivation and the Reporting requirements, this model can be used with agreed performance expectations with the business users.
In the next blog, we will discuss another option of the Graphical implementation to avoid the scripted view and to benefit the Filter push down and Query stripping for columns concept for the same requirement.
Please feel free to comment / suggest on the blog. :smile:
=================================================================================
Date: 16 Oct 2013
Adding some more screenshots from the Planviz in response to the comment from jody.hesch
The filter operation is seen in the Aggregation Pop operation before the final result is generated.
Expanding to the lowest level (as you can see from the number of color bands on the left) in the PlanViz till we reach the Analytic search, it can be seen that it still provides the entire table content of 24 records. The further expansion of the Analytic search shows the column executions for each of the table columns, but NO BwPopSearch operation.
Regarding the exact behavior of HANA in this regard, can be explained by the development team. Unfortunately I am not privileged to have that information. Here I just posted my experience with scripted Calc view. :smile:
- SAP Managed Tags:
13 Comments
