One of the most important factor that determines the usability of an application is response time. Decision table when encapsulated in calculation view is even more efficient as the execution happens in calculation engine. In this blog, I will explain you how to use decision table procedure (generated after activation of the decision table) in scripted calculation view.     

   

Here are some facts –

  • Decision table that ONLY has Parameter as Action can be used in scripted calculation view. This is because the calculation view generates a read-only procedure but a decision table with table columns in action has ability to update the database table – which is a conflicting behavior.  Hence, it is not possible to use decision table – with table column as Action – in scripted calculation view

Let us take the example of getting DISCOUNT on a product based on QUANTITY and MODEL. Assuming that we already have a decision table created on tuples ORDER and PRODUCT. [ Note: You may follow my blog to model such decision table, if not already done ] Your decision table would appear as follows – where you notice that DISCOUNT (used as Action) is a parameter and not a table column

/wp-content/uploads/2013/10/7_1_295686.png

     Figure 7.1 – Data foundation of the decision table with DISCOUNT parameter as Action

/wp-content/uploads/2013/10/7_2_295687.png

     Figure 7.2 – Decision table with values

Assuming that this decision table is already activated (if not then Activate the decision table). Once activated the generated procedure could be found at –
HANA System/Catalog/ _SYS_BIC/ Procedures/<your-package-name>/<your-decisiontable-name>

Now, we model the calculation view using this decision table procedure. Let us get started –

    1. Create a new calculation view of type ‘SQL Script’.  
      /wp-content/uploads/2013/10/7_3_295751.png

      Figure 7.3 – Calculation View of ‘SQL Script’ type shown to be created

    2. In the Calculation View editor, Select Script_View in the Scenario panel, on the left
      /wp-content/uploads/2013/10/7_4_295752.png
      Figure 7.4 – Script view editor of the calculation view. This would be used later to add the content from generated decision table
    3. Open the procedure that was generated on activation of the decision table. Copy the contents – between the tags BEGIN returned_tt = and END; (make sure it is the last END tag) – and paste it after var_out = in the Details section of  scripted calculation view as shown below. Since, the output of a scripted calculation view is always through variable var_out – make sure that the contents of the procedure are selected into ‘var_out
      /wp-content/uploads/2013/10/7_5_295753.png
      Figure 7.5 – Content from decision table procedure pasted against ‘var_out’ output variable
    4. Next, you have to define the Output of the scripted view. The Ouput of the scripted calculation view must match the output of the decision table procedure content that you have just copied from the decision table. To do so, Right click on the ‘Columns’ in the Output pane of the calculation view editor and select Create Target. A popup shown below opens. Select the ADD button in the table to add target columns to the Output.
      /wp-content/uploads/2013/10/7_7_295754.png
      Figure 7.6 – Output of the scripted calcutation view. Output is same at the columns of the Table Type generated after activating decision table

      /wp-content/uploads/2013/10/warn_295784.jpg
      Make sure that the Name, Data type, Length and Scale of the target column matches the name, data type, length and scale of the generated table type on which the decision table procedure is written.

      /wp-content/uploads/2013/10/info_295785.jpgThe generate table type is available under
      HANA System/ Catalog /_SYS_BIC /Procedures /Table Types /<your- package name>/<your decision table name>/TT.
      /wp-content/uploads/2013/10/7_8_295756.png
      Figure 7.7 – Table type that is generated after activation of decision table

    5. Select the Semantics node in the Scenario panel, and add required target columns as Measure or Attribute
      /wp-content/uploads/2013/10/7_9_295760.png
      Figure 7.8 – Type (Attribute or Measure) is set for the output of the scripted view
    6. Save, Validate and Activate the calculation view. Your calculation view is now ready to be used for analytical purpose. Here I, would show you a glimpse of data preview for this calculation view. Try and change the Value axis for discount or price and see the trends.
      /wp-content/uploads/2013/10/7_10_295761.png
      Figure 7.9 – Data preview of the scripted calculation view

With this you can use decision table runtime object into calculation view. There is however a restriction, that the Actions must always be of Parameters type. However the benefits outweigh the restriction especially because calculation view uses the most advanced and optimized engine for execution. Try it out yourself with different modeling scenarios like decision tables only on parameters, with more than one parameters in actions etc. and share your experience andfeedback so that we can improve better.

Other Related Blogs

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Sangamesh Hanumantha Sangamad

    Hello Archana,

    This is really a nice blog. I have a question :

    The image where you paste the code above shows that you are using a claculated view inside a calculated view. So I am trying something similar. I want to know whether “SYS_BIC”.”rulesonhana/FOR_SCRIPTED_CALCVIEW/CV” is a table type or a procedure you are calling?

    I believe its a table type. Could you please clarify on that?

    Thanks

    (0) 
    1. Archana Shukla Post author

      Hello Sangamesh,

      It is the generated procedure. I have already mentioned above, after Figure 7.2, where you can find the generated procedure. Alternatively you can just use Result View of decision table than copying/pasting content of procedure as :

      var_out = SELECT MANUFACTURER, MODEL from “SYS_BIC”.”<package-name>/<decisiontable-name>/RV”

      Note
      CV : Column View and RV : Result View

      Hope that answers !

      Regards,

      Archana

      (0) 
  2. John Appleby

    Just wanted to put a quick note against “calculation view uses the most advanced and optimized engine for execution”.

    As of HANA SP06, my experience has been that the graphical views are faster. In fact, they can be many times faster than scripted calc views or CE Functions. You should use scripted views if you have a need for the functionality.

    Hope this helps.

    (0) 
    1. Marcel Scherbinek

      John Appleby wrote:

      You should use scripted views if you have a need for the functionality.

      Hi John,

      which funcionality is meant when using only CE scripted calculation views? Additional control strcutures? Variables? As far as the (SQLScript) CE functions provide exactly the sames functionality as the graphical functions I can’t get any advantages by using scripted views.

      As far as I know SAP advised to use SQLScript only in scripted calculation views. So I’m already missing some functionalities by native SQL as non-equal joins or windowing.

      Regards,

      Marcel

      (0) 
      1. Sangamesh Hanumantha Sangamad

        Hello John, Hello Marcel,

        Great you guys brought out this topic.

        I have several qestions and I always wanted to ask :

        I am implementing a business logic and I have all my data on HANA database. I have master data, transactional data and so on. I read in several blogs that instead of using stored procedures use different view types for performance.

        Q1. Why is it performance beneficial if I use view types in place of stored procedures?

        Then, referring to those blogs and having no reason I started using view types. I begin with attribute views and analytical views. But my logic implementation required very complex joins, cases and calculations. This I found it really hard to achieve through modeling technique. So, I switch to Calculation views and I write all my complex queries(using native SQL). I also tried using CE functions but later realized that CE functions do not give all the benefits of SQL like using diggerent functions for my implementation. I am also aware that I cannot use CE and SQL in a single procedure. So it was better for me to use SQL at the end of the day.

        Q2. So, what is your opinion between writing several complex queries in a single Calculation view and writing the query in multiple calculation views(divising the query into chunks and combine CV’s  later)

        Q3. If using several calculation views is better, then what is the reason?

        Q4. What is the exact difference between attribute view, analytical view and calculation view? More importantly, analytical view and calcualtion view since we could use all functionalities in both nad for me both are same. Which performance is better and why?

        Q5. In what cases you suggest to use different view types?

        Q6. What is your opinion on using if else statements and using nested procedures?

        This seemy like a long list, but certainly this will help me to understand a lot of stuff and also help me for my further implementation.

        Thanks

        Sangamesh

        (0) 
        1. John Appleby

          Q1: A lot of effort has been put into optimizing Analytic and Calculation Views in SP06/SP07. They are faster than SQL or CE Functions, if your functionality is supported. If it’s not then you need to use SQLScript. It is acceptable to mix SQL and CE Functions provided you do not transfer a lot of data between those engines. Be careful.

          Q2: I try to build the base views as Analytic views and then reuse base Analytic views in Calc Views with various filters. This creates reusable logic and reduces complexity. As of SP07 you will find that most scenarios run similarly in the optimizer – there isn’t a massive penalty of different designs.

          Q3: As per Q2, it usually doesn’t make much difference. Using multiple legs of Calc Views to accelerate performance of Analytic Views can be helpful if you have problems gaining parallelization. This is a final resort, as it increases application complexity.

          Q4: Attribute Views are Master Data objects, Analytic Views are Virtual Cubes, and Calculation Views are like Multiproviders (combining multiple cubes). Generally… Attribute Views run in the SQL/Join Engine, Analytic Views run in the OLAP Engine and Calculation Views run in either/or/both, depending on how you configure them. But the engine can be overridden in SP07.

          Q5: I always say, avoid using Calc Views unless you expressly need them. They are much more prone to design that causes slow execution.

          Q6: Don’t understand the context of this question.

          (0) 
          1. Sangamesh Hanumantha Sangamad

            Hello John,

            Many thanks for your detailed answer.

            Q6.Initially I began with using cursors in procedures and then I realized looping, using if else statements will affect performance during execution which I experienced myself. But what is your opinion on using these. I would also like to know, is it efficient using nested procedures(calling inside a procedure) or does it also affect performance because i read about this once in a blog.

            Well your statement in the following comment there isn’t a set of best practice advicewhich is very true in my opinion. The reason is that I have seen lots of blogs, tutorials and not many explain why we have to exactly use these view types in HANA and when. Atleast, I did not find any convincing.

            try various options, and see what works best for your scenario. This is the best one. I am currently following this and myself having not great expertise on SQL, it takes more time for me to end up with a satisfied result testing all scenarios.

            If you think of any blogs where it is well explained about these please do share the links.

            (0) 
            1. John Appleby

              So yes, nested loops can perform badly. However so long as you aren’t looping too many times, and you run a good quantity of work inside the loop, they can be acceptable.

              If possible you should replace a loop with a set function which HANA can parallelize.

              (0) 
              1. Sangamesh Hanumantha Sangamad

                This is a good hint. I used lot of subqueries since I had to impement mathematical calculations on various conditions. Mabe I will try to work around on SET functions. Thanks for your great and timely response.

                Regards

                Sangamesh

                (0) 
      2. John Appleby

        So this is an often confused area and the SAP advice is (in my opinion) wrong. I have heard it a few times.

        When you mix SQL and CE Functions in SQLScript, you use different DB engines and transfer data between them. This can cause performance problems. On the other hand it can allow you to use SQL functionality that is not available in CE Functions. Therefore mixing them should be done with care, and with small data volumes in data transferred between engines.

        It is perfectly acceptable to use SQL in SQLScript, and this can perform much better than CE Functions in some cases. For instance, COUNT DISTINCT is only possible in SQL (and it pushes the function down into the column store). If you do it in SQLScript you have to do two COUNTs, which is much slower.

        As of HANA SP07, Window functions run in the column store so they should be nice and fast.

        Unfortunately because of the complexity of the various engines, there isn’t a set of best practice advice – try various options, and see what works best for your scenario. Hope this clarifies.

        (0) 
  3. Rainer Winkler

    Hello Archana,

    maybe I did not understand your blog correctly. You copy the generated coding from the procedure of the decision table into the coding of the calculation view.

    Whenever the decision table is changed, this has to be repeated manually. Is this really an advantage?

    Nonetheless your blog gives some interesting insights into decission tables and calculation views.

    With kind regards

    Rainer

    (0) 
    1. Archana Shukla Post author

      Hello Rainer,

      Yes, this was the disadvantage of pasting procedure code into scripted calculation view. There is yet another way in case your decision tables are changing.

      Once can use result view of the decision table either in the Graphical Calculation view (by dragging and dropping the same onto your projection) or Scripted Calculation view as :
      var_out = SELECT MANUFACTURER, MODEL from “SYS_BIC”.”<package-name>/<decisiontable-name>/RV”

      In this case you need not alter your calculation view.

      regards,

      Archana

      (0) 

Leave a Reply