Day 3(11/27)

5 SQLScript queries

What is a scalar variable:


We define scalar variable as IN, OUT, or INOUT and be referenced anywhere in procedure.

It_expensive_books = SELECT title, price, crcy FROM :it_books
WHERE price >: minPrice AND crcy =:currency;

2) What is CE_CALC operator?

It evaluates an expression and then bound to a new column

i.e.:
with_tax = CE_PROJECTION (:product["CID", "CNAME", "0ID", "SALES", 
                                               CE_CALC('"SALES"* :vat_rate', decimal(10,2)]
AS "SALES_VAT"],
                                                 ""CNAME" = ':CNAME"');
IS EQUIVALENT TO

with_tax = SELECT cid, cname, oid, sales, sales * :vat_rate AS sales_vat
                FROM :product
                WHERE cname = ':cname';

All columns used in CE_CALC have to be included in the projection list.  Another frequent use of CE_CALC is computing row numbers.
CE_CALC('rownum()', integer) AS "RANK"

3) CE_JOIN operator?

It calculates an inner join of the given pair of tables on a list of join attributes.  Each pair of join attributes must have identical attribute names else one of them must be renamed prior to the join.

i.e. 
ot_pubs_books = CE_JOIN(:It_pubs, :it_books, ["PUBLISHER"]);
ot_pubs_books2 = CE_JOIN (:It_pubs, :it_books, [PUBLISHER"], ["TITLE", "NAME","PUBLISHER", "YEAR"]);

is equivalent to

ot_pubs_books3 = SELECT P.publisher as publisher, name, street, post_code, city, country, isbn, title, edition, year, price, crcy
      FROM :It_pubs AS P, :it_books as B
      WHERE P.publisher = B.publisher;
ot_pubs_books4 = SELECT title, name, P.publisher as publisher, year
     FROM :It_pubs AS P, :it_books AS B
     WHERE P.publisher = B.publisher;

4) What is CE_CALC_VIEW operator?

It returns results for an existing calculation view.  It takes the name of the calculation view and optionally a projection list of attribute names.

i.e.
out = CE_CALC_VIEW("CALC_view", ["CID", "CNAME"]);

is equivalent to

out = SELECT cid, cname FROM CALC_view;

5)  What is CE_OLAP_VIEW operator?

It returns results for an existing OLAP view (also known as Analytical view).  It takes the name of OLAP view and an optional list of key figures and dimensions as parameters.  The OLAP view is grouped by dimensions and key figures are aggregated using the default aggregation of the OLAP view.  

i.e. 

out = CE_OLAP_VIEW ("OLAP_view", ["Dim1", "kf"]);

Is equivalent to

out = SELECT dim1, SUM(kf) FROM OLAP_view GROUP BY dim1;











30 SAP HANA Questions

1)  What is a Restricted Measure?

restricted measures are used to filter the value for an output field based on the user-defined rules.  
can be used to restrict a measure based on certain conditions of the master data
i.e. you can restrict the revenue column only for Region = APJ,  Year = 2013

Example: Business wants to have additional column for Sales Amount based on calendar year. Let’s say one column for Current Year and another column for previous year sales.

1.

2.

3.

4.BU02 comes out restricted.

https://blogs.sap.com/2014/03/14/how-to-handling-multiple-exclude-restrictions-in-analytic-view/

2)  Can you add column views to Analytic view and Calculation view?

ColumnView – a metadata artifact in the HANA Calculation Engine which describes the entity relationship of the underlying tables. Such a ColumnView is automatically generated by BW for Infocubes, InfoObjects (most), and DSOs w/ SID-support.

We can add column views in a Calculation view but not in the Analytic view.


3)  Consider there is a table that contains product ID's with no product description and you have a text table for products that has language specific description for each products?  How can you get the language specific data? (http://saphanatutorial.com/sap-hana-text-join/)

Create a text join between these two tables.  The right table should be the text table and is mandatory to specify the "Language Column" in the 'Properties' view.

        A.  What are Text tables?

• Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). 
• Table A may therefore contain explanatory text in several languages for each key entry of B. 

Text Join:

• is used to fetch the description based on user's session language. Once we implement the text join in SAP HANA, it automatically find out user's language and give description in that language. 
• Create a calculation view which will give Product Name and Product Description. The Product Description should be only in user's session language. 

    1.  Right click on your schema and refresh to see the 2 tables created. 

    2.  Go to "Content" and right click on your package. Select Calculation view. 

   3.  

4) What is a decision Table? ( http://www.sapstudent.com/hana/sap-hana-decision-tables-with-updated-values/2 )

It creates related business rules in a tablular format for automating the decisions.  It helps in managing business rules, data validation, data quality rules without any language knowledge.  The active version of the deicision table can be used in applications.

You create decision table in a package just like any attribute view.  You can create from scratch or from an existing decision table.

1.2. 

3.  

• The Scenario pane consist of Data Foundation and Decision Table nodes. Selecting each node shows us the respective screen elements in the details and output panes.
• The Details pane of the Data Foundation node displays the tables or information models used for defining the decision table. The Details pane of the Decision Tablenode displays the modeled rules in tabular format.
• The Output pane displays the vocabulary, conditions, and actions, and allows us to perform edit operations. Expanding the vocabulary node, we can see the parameters, attributes, and calculated attributes sub-nodes. In the Output pane, we can also see the properties of the selected objects within the editor.

Step 4:

• The first thing we need to do while designing decision tables is adding source objects into the data foundation section.
• Here we are going to build decision table on ‘ORDERDETAILS’ table. So let’s add that table to the area either by drag and drop option or using ‘+’ option available when we hover mouse on to the data foundation as shown below.

• In this scenario, we will be building business rules based on column PRODUCT_GRP, QUANTITYORDERED and PRICEEACH. So let’s add those columns to the output as shown below.
  

• Calculated Attributes: This is the same as what we have in other modeling objects. These are used to create new columns based on the existing columns after applying specific business logic and can be used as either Condition or Action.
• Parameters: These are used to store result value after applying conditions or can be used to provide the values for conditions during runtime. Parameters can be used for both conditions and parameters.
• Conditions: Conditions are the columns on which we will write our business rules. In our scenario, we will build the business rules based on columns PRODUCT_GRP and QUANTITYORDERED. Let’s add those two columns to conditions as shown below.
• 
  

5)  Where to see the detailed report of the decision table?

In the 'Job Log' sections you can see the validation status and detailed report of the decision table.

6)  How to execute the decision table?

The decision table is executed by calling the procedure.

CALL "<schema name>"."<procedure name>";

CALL "<schema name>"."<procedure name>" (< IN parameter>, ..... , <IN parameter>, ?);
for Condition as parameters and Action as parameters.

On execution of the procedure, if no parameter are used then physical table is updated based on the data you enter in the form of condition values and action values.



7)  Are there any restrictions on Decision table to preview the data?

Data preview is supported only if:
Decision table is based on physical table and has at-least one parameter as action
Decision table is based on Information view and parameter(s) as action.



8)  Can you switch ownership of objects?

We can take the ownership of objects from other user's workspace only if it is inactive version of the object Authorization required is "Work in Foreign Workspace".  The active version is owned by the user who created and activated the object.

9)  What is the difference between Switch Ownership and Take Over?

Switch ownership: To take multiple inactive objects from other users.
Take Over: To take single inactive object from another workspace.

10)  How do you validate models?

Quick launch menu -> Validate
From the 'Available' list, select the required models that system must validate.
Choose Add
Click Validate

11)  How do you generate the documentation for the objects you created?

By using 'Auto Documentation' which captures the details of an information model or a package in a single document.  Process to create is:
Quick Launch -> Auto Documentation
In 'Select Content Type' choose 'Model Details' OR 'Model List'
Add the required objects to the Target list
Browse the location where you want to save the file
Click finish

12) How to identify whether an information model is referenced by any other information model?

We can check the model references by using 'Where Used'.  Process is:
Go to the package
Select the required object
From the context menu, choose 'Where Used'.

13)  What is the difference among Raw Data, Distinct values and Analysis while doing the Data Preview?

Raw Data: It displays all attributes along with data in tabular format.
Distinct Values:  It displays all attributes along with data in graphical format.
Analysis: It displays all attributes and measures in graphical format.

14)  What are the different types of functions can be used in expressions?

Conversion, String, Mathematical, Date and Misc functions.

if("SCORE' > 7, "SELECTED', IF("SCORE" > 4, "ONHOLD" , "REJECTED")];
returns REJECTED if the SCORE IS <= 4.

case("CODE", 1, 'NEW', 2, 'VENDOR REBUILT', 3, 'SHOP REBUILT', 'INVALID');
if the value of CODE is other than 1/2/3 then a default value of 'INVALID' will be selected.

15)  How to search Tables, Models and Column views?

In the Modeler search field, enter the object you want
Select the system in dropdown
Click search.   

The matching objects are listed in results pane with 3 tab pages: Tables, Models, and Column views.

16)  How to search Tables, Models, and Column views?

In the Modeler search field, enter the boject you want
Select the system in dropdown 
Click search

Thte matching objects are listed in results pane with 3 tab pages: Tables, Models, and Column Views.

17)  Is it possible to import SAP Netweaver BW objects?
yes it is possible to import SAP BW objects.

18)  How to Import BW models?

The process to Import BW models:
File menu ->  Import
Expand SAP HANA Content node, choose 'Import SAP NetWeaver BW Models'
In 'Source System' enter BW credentials
Select the target system
Select BW InfoProviders
If you want to import selected models along with display attributes for IMO Cube and IMO DSO, select 'Include Display Attributes'
We can select analysis authorizations associated with InfoProviders/Role based.
Click finish

19)  What is Label mapping?

We can choose associate an attribute with another attribute description.  Label mapping is also called as Description mapping.  For example if A1 has a label column B1, then you can rename it B1 to A1.description.  The related columns appear side by side during data preview.  

20)  What happens when one of the table in Attribute view has modified column with data type?

It reflects the previous state of the columns, even if you remove and add it again.  It is referring to the cache.  To resolve this issue close the editor and reopen it.  

21)  What happens when you open an attribute view with a missing columns in the required object?

An error will be shown "column is not found in table schemaname.tablename" and the editor does not open.  To make it consistent

Open the required object add the adding column/attribute/measure temporarily
Now open the object which was previously giving error
Find all references to this column, Save the object
Now go ahead and delete the column from the required object.


22)  What does the aggregation type 'Calculate Before Aggregation' mean?

If you select 'Calculate Before Aggregation', the calculation happens as per the expression specified and then the results are aggregated as SUM, MAX, MIN, or COUNT.  If it is NOT selected, the calculation happens as per the expression specified but the data is not aggregated but shown as FORMULA.

23) How to activate the other objects( required or impacted objects) along with current object?

By using 'Save and Activate All' option in the toolbar.

24)  Can you add column views to Analytic view and Calculation view?

We can add column views in a Calculation view but not in the analytic view.

25) How to create Counters in Graphical Calculation view?

For example to get the number of distinct values of an attribute:
Go to output pane, right click counters
from the output pane, right click Counters
From the context menu, choose New
Choose Attribute
click ok

26)  What is a Constant column and how to create it?

In a Union view, a Constant column is created for the ouput attributes for which there is no mapping to the source attributes.  To create Constant column:

Right click the attribute in the target list
Choose Manage Mappings
To map source to the target column


27)  Consider that there are two tables(Actual sales and Planned sales)  with similar structures.  I want to see the combined data in a single view but at the same time how can I differentiate the data between these two tables?

Create a union view between the two tables and have a 'Constant column' indicating constant values like 'A' for Actual sales and 'P' for planned sales.  The default value for the constant column is NULL.







100 pages