Text Tables

Hey guys,

    Text joins ( http://saphanatutorial.com/sap-hana-text-join/  )

• are used to fetch the description based on user's session language.
• once implemented, it automatically find out user's language and give description in that language. 

1) Product(table) contains Product ID and Product Name 

2) PRODUCT_TEXT_TABLE(text table of product) contains the description of products in different languages. 

      a)i.e. "cotton shirts" in english and "BaumWoll Shirts" in German

Purpose:

    Create a calculation view gives 

               Product Name and Product Description(only in user's session language)

How to Implement?

1.       Open Hana Studio
2. Create 2 tables ( Product and PRODUCT_TEXT_TABLE)  --- directions on Bottom of page
3. Right click on your schema and referesh to see the 2 tables
4. Go to "content" and right click on the package, select calculation view
5. Give name and description of the calculation view.  Select data category as "Dimension"
6. Add a "join" block and add both the tables  "PRODUCT" and "PRODUCT_TEXT_TABLE"
7. Make a join on the column "PRODUCT_ID".  Specify the join types as "text join".  click on the "language" column.  Add "PRODUCT_ID", "PRODUCT_NAME" and "PRODUCT_DESCRIPTION" to output.

    8. Select the Type of columns as "attribute".  This may be selected by default.  Save and activate the view.

Directions How to create the tables:

-- REPLACE WITH YOUR ACTUAL SCHEMA NAME

-- PRODUCT table
CREATE COLUMN TABLE ""."PRODUCT"(
            "PRODUCT_ID" INTEGER ,
            "PRODUCT_NAME" VARCHAR(20) ,
            primary key ("PRODUCT_ID"));

-- PRODUCT text table
CREATE COLUMN TABLE ""."PRODUCT_TEXT_TABLE"(
            "PRODUCT_ID" INTEGER ,
            "LANGUAGE" VARCHAR(1),
            "PRODUCT_DESCRIPTION" VARCHAR(50) ,
            primary key ("PRODUCT_ID", "LANGUAGE"));

insert into ""."PRODUCT" values(1,'Shirts');
insert into ""."PRODUCT" values(2,'Jackets');
insert into ""."PRODUCT" values(3,'Trousers');
insert into ""."PRODUCT" values(4,'Coats');
insert into ""."PRODUCT" values(5,'Purse');

insert into ""."PRODUCT_TEXT_TABLE" values(1,'E', 'Cotton Shirts');
insert into ""."PRODUCT_TEXT_TABLE" values(1,'D', 'Baumwoll-Shirts');
insert into ""."PRODUCT_TEXT_TABLE" values(2,'E', 'Leather jacket');
insert into ""."PRODUCT_TEXT_TABLE" values(2,'D', 'Lederjacke');
insert into ""."PRODUCT_TEXT_TABLE" values(3,'E', 'Trousers and Pants');
insert into ""."PRODUCT_TEXT_TABLE" values(3,'D', 'Hosen und Hosen');
insert into ""."PRODUCT_TEXT_TABLE" values(4,'E', 'Coats and Blazers');
insert into ""."PRODUCT_TEXT_TABLE" values(4,'D', 'Muntel und Jacken');
insert into ""."PRODUCT_TEXT_TABLE" values(5,'E', 'Purse and Handbags');
insert into ""."PRODUCT_TEXT_TABLE" values(5,'D', 'Geldburse und Handtaschen');

    

    

Decision Tables

Hey guys,

   Summary of the Decision Table Blog:  https://archive.sap.com/discussions/thread/3488033

1. Can be used to restrict a measure based on certain conditions of the master data
2. 
   
   1. Columns: describe the definition and operation of the rules
   1. Condition Column – definition of the rule
   2. Action Column – operation to the performed if the conditions are satisfied.
   2. Rows: represents the value associated with these definitions and operations respectively.

Decision Tables:

1. formulate rules of business, company or corporation in a table structure
2. Business rules articulated in form of decision table are quick to understand, clean, crisp, readable and most importantly compressed.
3. a very powerful style to represent business rules which otherwise would have spanned over multiple rows, pages and screens

What are decision Tables made of:

1. Columns: describe the definition and operation of the rules
1. Condition Column – definition of the rule
2. Action Column – operation to the performed if the conditions are satisfied.
2. Rows: represents the value associated with these definitions and operations respectively.

1. Evaluated column-wise from left-to-right AND fashion.
2. Action value associated with a row – whose conditions are satisfied – are collected in the result set
1. the Customer.IDcolumn value is evaluated first
2. followed by Customer.CurrentBalance in AND fashion
3. If the conditions are met then Customer.Type is set to Gold or Silver.

This has created a benchmark by debuting business rules in database layer. Information modeler, data architects or application developers can now update or select the data from the database tables with basic understanding of SQL language – A language that manages database.

Business rules in this avatar offers a high degree of decision making capabilities with big data.;

writing business rules that aids better decision making with big data and performing analytics over the results –Business Rules powered by SAP HANA is the answer

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

Hana Questions (30 question)

1)What is a workspace?

The place where you work on project-related objects is called a repository workspace.

2) What is a package and its types? (structural/non-structural)

package is used to group together related content objects in SAP HANA studio.  By default it creates non-structural:

1) Structural: package only contains sub packages.  It cannot contain repository objects.
2)  non structural: package contains both repository objects and sub packages

3) What are the default packages delivered with the repository?

SAP, system-local, system-local generated, system-local.private

4) What can be the max. length of a package name?

190 characters including dots

5) What are package privileges?

REPO.READ
REPO.EDIT_NATIVE_OBJECTS
REPO.ACTIVATE_NATIVE_OBJECTS
REPO.MAINTAIN_NATIVE_PACKAGES


6)REPO.READ

Read access to the selected package and design time objects(both native and imported)

7)REPO.EDIT_NATIVE_OBJECTS

Authorization to modify design-time objects in packages originating in the system the user is working in.

8) REPO.ACTIVATE_NATIVE_OBJECTS(activate/reactivate)

Authorization to activate/reactivate design-time objects in package originating in the system the user is working in

9) REPO.MAINTAIN_NATIVE-PACKAGES(update/delete)

Authorization to update or delete native packages or create sub-packages of packages originating in the system in which the user is working.

10) How many objects is uniquely identified in the repository?

Each object is uniquely identified by the combination of package name, object name, and object type.

11)  What are the two developer roles in HANA SPS05?

Modeler and Application programmer

12) Modeler

concerned with the definition of model and schemas used in the SAP HANA, the specification and defintion of tables, views, primary keys, indexes, partitions and inter-relationships of the data, designing and defining authorization and access control through specification of privileges, roles, and users and generally uses the perspective "Administration Console" and "Modeler"

13) Application Programmer

Concerned with building SAP HANA applications which are designed based on MVC architecture and generally uses the perspective "SAP HANA development"

14)  What is SAP XS (extended-application-service)?

SAP HANA XS provides end-to-end support for web-based applications

15) What are development objects?

The building blocks of SAP HANA applications are called development objects.

16)  What is a repository?

The HANA repository is a storage system for development objects and is built into SAP HANA.  The repository supports Version Control, Transport, and sharing objects between multiple developers.  We can add objects to the repository, update the objects, publish the objects, and compile these objects into run-time objects.

17) What are the different perspectives available in HANA?

Modeler: used for creating various types of views and analytical privileges.

SAP HANA development: used for programming applications for creating development objects to access or update data models such as Server side Java script or HTML files.  

Administration: Used to monitor the system and change settings.

Debug:  used to debug such as SQLScript or server-side javascript (.xsjs files)

18) Before starting development work in SAP HANA studio, what are the roles a user should have on SAP HANA server?

Modeling, content_admin

19)  What is a Delivery Unit?

Delivery Unit( DU ) is a container used by the Life Cycle Manager (LCM) to transport repository objects between the SAP HANA systems.  

20)  What are Transformation Rules (Data trans.during the repl process)?

A rule specified in the advanced replication settings transaction for source tables such that data is transformed during the replication process.  i.e. you can specify rule to convert fields, fill empty fields, skip records.

21)  What happens when you set-up a new configuration? (GUID and MT)?

The database connection is automatically created along with GUID and Mass Transfer (MT_ID)

Schema GUID ensures that configuration with the same schema name can be created.
mt_id is used in the naming of SLT jobs and the system can uniquely identify a schema

22)  what factors (4) influence the change/increase the number of jobs? (configurations, load/repl, initial, latency)?

1) number of configurations managed by the SLT replication server
2)  Number of tables to be loaded/replicated for each configuration
3) expected speed of initial load
4) Expected replication latency time.  As a rule of thumb, one BDG job should be used for each 10 tables in replication to achieve acceptable latency times.

23)  When to change the number of data Transfer jobs? (Latency, resources, complete)

1)  If the speed of the initial load/replication latency time is not satisfactory
2)  If SLT replication server has more resources than initially available, we can increase the number of data transfer and/or initial load jobs
3)  After the completion of the initial load, we may want to reduce the number of initial load jobs.


24)  What the jobs involved in replication process? (5) MM DAM

1) Master job (IUUC_Monitor_<MT_ID>)
2) Master Controller Job (IUUC_REPLIC_CNTR><MT_ID>)
3) Data Load Job(DTL_MT_DATA_LOAD_<MT_ID><2digits>)
4) Migration Object Definition Job (IUUC_DEF_MIG_OBJ<2digits>)
5) Access Plan calculation Job(ACC_PLAN_CALC_<MT_ID>_<2digits>)

25) What is the relation between the number of data transfer jobs in the configuration settings and the available BGD work processes?

Each job occupies 1 BGD work processes in SLT replication server.  For each configuration, the parameter Data Transfer Jobs restricts the max number of data load job for each mass transfer ID (MT_ID)

A mass transfer ID requires at least 4 background jobs to be available.  One master job, one master controller job, at least one data load job, one additional job either for migration/access plan calculation/to change configuration settings in A'Configuration and Monitoring a Dashboard'

26)  If you set the parameter "data transfer jobs" to 04 in a configuration "SCHEMA1", a mass transfer ID 001 is assigned.  Then what jobs should be in the system?

1 Master Job ( IUUC_MONITOR_SCHEMA1)
1 MASTER CONTROLLER JOB (IUUC_REPL_CNTR_001_0001)
At most 4 parallel jobs for MT_ID 001
(DTL_MT_DATA_LOAD_001_/1/-01/-03/-05)

Performance.  It lots of tables are selected for load/replication at the same time, it may happen that there are not enough background jobs available to start the load procedure for all tables immediately.  In this case you can increase the number of initial load jobs, otherwise tables will be handled sequentially.  

For tables with large volumes of data, you can use the transaction 'Advanced Replication Settings (IUUC_REPL_CONT)" to further optimize the load and replication procedure for dedicated tables.

27)  What happens after the SLT replication is over?

The SLT replication server creates 1 user, 4 roles, 2 stored procedures and 8 tables.
1 User
1 privilege
4 Roles
<Replication Schema>_DATA_PROV
<REPLICATION_SCHEMA>_POWER_USER
<REPLICATION_SCHEMA>_USER_ADMIN
<REPLICATION_SCHEMA>_SELECT
2 STORED PROCEDURES
RS_GRANT_ACCESS, RS_REEVOKE_ACCESS
8 TABLES

28)  What are the different replication scenarios?

Load, Replicate, Stop, Suspend, and Resume

a) load

Starts an initial load of replication data from the source system.  The procedure is a one-time event after it is completed, further changes to the source system database will not be replicated.

For the initial load procedure, neither database triggers nor logging tables are created in teh source system.  Default settings use reading type 3 with up to 3 background jobs in parallel to load tables in parallel or subsequently into the HANA system.

b)replicate

combines an intial load procedure and the subsequent replication procedures(real time or scheduled)

Before the initial load procedure will start, database trigger and related logging tables are created for each table in the source system as well as in SLT replication server.

c) Stop Replication (triggers)

Stop any current load or replication process of a table; removes the database trigge and related logging table completely.  Only use the function if you do want to continue a selected table otherwise you must initially load the table again to ensure data consistency.


d) Suspend

Pause a table from a running replication.  The database trigger will not be deleted from the source system.  

e) Resume

Restarts the application for a suspended table.  The previous suspended replication will be resumed(no new initial load required)


28)  What happens if the replication is suspended for along period of time or system outage of SLT or HANA system?

The size of the logging table increases.

29)  What is an analytic view?

Analytic views are used to model data that includes measures.  In case of multiple tables, measures must originate from only one of these tables (central table).  you can model columns, calculated columns, restricted columns, variables, and input parameters.

30)  How do you fine tune attributes of analytic views?  (6 types: restrictions, drill, hidden, aggregation, currency)

1)  Can apply fitler to restrict values
2)  Can be defined as hidden so that they can be processed, but not visible to end users
3) Can be defined as key attributes and used when joining multiple tables
4) can be further drilled down by "drill down enable" property( you can disable this behavior for selected attributes)
5)  You can model aggregation type of measures
6) you can model currency and unit of measure.

31) Can we include attribute views in Analytic view definition?

Yes

32)

Modeling Day 1: Certification Series

Modeling Questions:  30 Questions

1)  What are the options available in 'Run With' while creating calculation view? (Invoker, defined)

 Definer's Right:  System uses the rights of the definer while executing the view or procedure for any user

Invokers Right:  System uses the rights of the current user while executing the view or procedure


2)  While creating a graphical calculation view, what are the options available in Tools, palette?

Union, Join, Projection, and Aggregation

3)  How to create counters in graphical calculation view?

i.e. to get the number of distinct values of an attribute: go to the output pane, right click counters from the context menu, choose new attribute, click ok

4)  Is it mandatory to include measures for calculation view?

No, calculation view containing no measures work like an attribute view and is not available for reporting purposes

5)  How do you debug the calculation view with lot of complexity at each level?

By previewing the data of an intermediate node.

6)  In calculation view, what is the option 'auto map by name' used for?

It automatically creates the input parameters corresponding to the source and performs a 1:1 mapping

When you preview the data of an intermediate now, SAP HANA studio activates the intermediate calculation model with the current user instead of the user _SYS_REPO.

Table 1: Working With View Nodes

Requirement	Task to Perform
If you want to query data from two data sources and combine records from both the data sources based on a join condition or to obtain language specific data.	Create Joins
If you want to query data from database tables that contains spatial data.	Create Spatial Joins
If you want to validate joins and identify whether you have maintained the referential integrity.	Validate Joins
If you want to combine the results of two more data sources.	Create Unions
If you want to partition the data for a set of partition columns, and perform an order by SQL operation on the partitioned data.	Create Rank Nodes
If you want to filter the output of projection or aggregation view nodes.	Filter Output of Aggregation or Projection View Nodes.

7)  While creating hierarchy, what does the option 'Aggregate all Nodes' mean?

For example there is a member A with value 100, A1 with value 10, A2 with value 20 where A1 and A2 are children of A.

Default: 'Aggregate all nodes' is set to false and you will see a vlaue of 30 for A

When option is true: you will count the posted value 100 for A as well and a result of 130.


8) How can you generate a Sales report for a region in a particular currency where you have the sales data in a database table in a different currency?

Create an Analytic view by selecting the table column containing the sales data and currency and perform currency conversion.  Once the view is activated, we can use it to generate reports.

9)  What are the factors that affect currency conversion?

Currency conversion is performed based on source currency, target currency, exchange rate, and date of conversion.  You can select currency from the attribute data used in the view.  Currency conversion is enabled for analytic view and calculation views.

10)  What is the prerequisite for doing the currency conversion?

You need to import tables TCURC, TCURF, TCURN, TCURR, TCURT, TCUV, TCURW, TCURX

11)  What is the prerequisite for Unit of Measure?

You need to import the tables T006 and T006A

12)  What happens when you activate an object?

The object is exposed to repository and for analysis.

13)  What is the difference between activate and redeploy?

Activate:  It deploys the inactive object

Redeploy:  It deploys the active objects.  You do this when run-time object is corrupted or deleted and you want to create it again.  OR when the object goes through client-level activation and server-level activation but fails at MDX, and the object status is still active.

14)  What are the supported activation modes?

Activate and ignore the inconsistencies in impacted objects 
Stop activation in case of inconsistencies in impacted objects

Irrespective of the activation mode, if even one of the selected objects fails (either during validation or during activation), the complete activation job fails and none of the selected objects will be activated.


15) What are the options available in source input parameter?

-Create new map 1:1
-Map by Name
-Remove Mapping

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

Create a union view(graphical) 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.

17)  What is a constant column and how to create it?

In a union view, a constant column is created for the output attributes for which there is no mapping to the source attributes.  

18)  In which configuration table, the mapping between authoring and physical schema is stored?

SYS_BI.M_SCHEMA_MAPPING

19)  What's the purpose of generating time data?

If you model a time attribute view without generating time data, an empty view will be shown when you use data preview.  


20)  In which configuration table the generated time data information will be stored:

for Gregorian calendar type(SYS_BI)
M_TIME_DIMENSION_YEAR
M_TIME_DIMENSION_MONTH
M_TIME_DIMENSION_WEEK
M_TIME_DIMENSION
for Fiscal
M_FISCAL_CALENDAR
All these tables are under schema _SYS_BI

21)  What is an attribute?

Attribute represents the descriptive data used in modeling i.e. city, country, etc.

22)  What is a simple attribute?

Simple attributes are individual analytical elements that are derived from the data foundation.  For example Product_ID, Product_Name are attributes of a Product subject area

23)  What is a calculated Attribute?

Calculated attributes are derived from one or more existing attributes or constants.  For example deriving the full name of a customer ( first name and last name), assigning a constant value to an attribute that can be used for arithmetic calculations.

24)  What is a private attribute?

Private attributes used in an analytical view allow you to customize the behavior of an attribute for only the view.

i.e. if you create an analytical view and you want a particular attribute to behave differently than it does in the attribute view to which it belongs, you can define it as a private attribute.

25)  What is a measure?

Measures are simple measurable analytical elements and are derived from Analytic and Calculation views.

26)  What is a simple measure?

Simple measure is a measurable analytical element that is derived from the data foundation

27)  What is a calculated measure?

Calculated Measures are defined based on a combination of data from OLAP cubes, arithmetic operators, constants, and functions.

OLAP cube: multidimensional database that is optimized for data warehouse and online analytical processing (OLAP) applications.  An OLAP cube is a method of storing data in a multidimensional form, generally for reporting purposes.

28)What are Counters?

Counters add a new measure to the Calculation view definition to count the recurrence of an attribute.  

For example to count how many times product appear.

29) What is an attribute view?

Attribute views are used to model entity based on the relationship between attribute data contained in multiple source tables.  You can model Columns, Calculated Columns and Hierarchies.

30)  How can you fine-Tune attributes of an attribute view?

1)  Apply filter to restrict values
2)  can be defined as hidden so that they can be processed but not visible to the end user
3) can be defined as key attributes and used when joining multiple tables
4) can be further drilled down by 'Drill down enable property'









SQLScript (3):

1)  What is CE_LEFT_OUTER_JOIN operator?

It calculates the left outer join.  Besides the function name the syntax is same as CE_JOIN.

CE_LEFT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

2)What is CE_RIGHT_OUTER_JOIN operator?

It calculates the right outer join.  Besides the function name the syntax is same as CE_JOIN.

CE_RIGHT_OUTER_JOIN("COLTAB1", "COLTAB2", [KEY1, KEY2], [A, B, Y, D]

3)  What is CE_PROJECTION operator?

It restricts the columns in the table variable and optionally renames columns, computes expression, or applies a filter.

example:
ot_books1 = CE_PROJECTION (:it_books, ["TITLE", "PRICE", "CRCY" AS "CURRENCY"], "PRICE" > 50');

EQUIVALENT TO

ot_books2 = SELECT title, price, crcy AS currency
                      FROM :it_books WHERE price > 50;

4)  What is CE_CALC operator?

It evaluates an expression and then bound to a new column

example:
with_tax = CE_PROJECTION(:product, ["CID", "0ID", "SALES", 
                                                              CE_CALC("SALES" * :vat_rate', decimal(10,2))
AS "SALES_VAT"],
                                                                 ""CNAME" = ':cname'"");


with_tax2 = SELECT cid, cname, oid, sales, sales * :vat_rate AS sales_vat
                     FROM :product
                     Where CNAME = ':cname';