Tuesday, November 22, 2016

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
RequirementTask 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';


No comments:

Post a Comment