SQL Models

Question 1

SQL Skills

Answer 1

Acquire skills in SQL to be effective in modelling.( Not an alternative).

Question 2

Where we can achive SQL

Answer 2

1. Filter
2. Input parameter
3. Functions
4. Procedures.
5. Analytical priveleges.

Question 3

DML

Answer 3

Insert , Update, delete , read

Question 4

SQL- Declarative language

Answer 4

You tell what to do , not how to do

Question 5

Global Temporary vs local temp

Answer 5

Global Temp - table available globally but data is visible to current session.
Local temp - Table and data available to local session only.

Question 6

Temporal properties

Answer 6

Not supported in row based tables.

Question 7

System versioned table and application versioned table.

Answer 7

Time travel.

Question 8

System versioned table and application versioned table.

Answer 8

Time travel.

Question 9

Consumption of Hierarchy in SQL

Answer 9

1. DIM view +STAR join combination.
2. Special parameter “Enable hierarchy”that allow the hierarchy.
3. Semantics-> SQL access , enable the hierarchy.
4. LEVEL and parent child , both are allowed.

Question 10

Input mapping for Table function

Answer 10

1. Data Source Column
2. Input parameter.
3. Constant

Question 11

Procedures

Answer 11

R ,
SQL
L

Question 12

Procedures

Answer 12

One or more Inputs

One or more output

Question 13

Function

Answer 13

Only SQL

Question 14

Derive value for input parameter

Answer 14

Procedure and Scalar function.

Question 15

Full outer

Answer 15

Restriction : In star join only 1 DIM can be supported.

Question 16

Temporal Join

Answer 16

1. Only Supported in Star node of Cube with Star Join.
2. Join Type - Inner.
3. Allowed
   a) Timestamp
   b) Date
   c) Integer.

Question 17

DIM - Shared columns

Answer 17

Can not rename the Shared col however we can create alias.

Question 18

Multi Join

Answer 18

Multi Store Order

1. Outside in.
2. Inside out.

Question 19

Non Equi Join

Answer 19

1. Left Outer
2. Right outer
3. Full Outer
4. Inner
   Can be used on above CV with Operator condition.
   Operator can be different on different join fields.

Question 20

Aggregation Functions

Answer 20

1. Sum
2. Min
3. Max
4. Ave
5. STD DEV
6. Variance

It should not be used in Stacked scenerio.

Question 21

Aggregation

Answer 21

Calculated Col is always calculated after aggregation