SQL Models Flashcards
SQL Skills
Acquire skills in SQL to be effective in modelling.( Not an alternative).
Where we can achive SQL
- Filter
- Input parameter
- Functions
- Procedures.
- Analytical priveleges.
DML
Insert , Update, delete , read
SQL- Declarative language
You tell what to do , not how to do
Global Temporary vs local temp
Global Temp - table available globally but data is visible to current session.
Local temp - Table and data available to local session only.
Temporal properties
Not supported in row based tables.
System versioned table and application versioned table.
Time travel.
System versioned table and application versioned table.
Time travel.
Consumption of Hierarchy in SQL
- DIM view +STAR join combination.
- Special parameter “Enable hierarchy”that allow the hierarchy.
- Semantics-> SQL access , enable the hierarchy.
- LEVEL and parent child , both are allowed.
Input mapping for Table function
- Data Source Column
- Input parameter.
- Constant
Procedures
R ,
SQL
L
Procedures
One or more Inputs
One or more output
Function
Only SQL
Derive value for input parameter
Procedure and Scalar function.
Full outer
Restriction : In star join only 1 DIM can be supported.
Temporal Join
- Only Supported in Star node of Cube with Star Join.
- Join Type - Inner.
- Allowed
a) Timestamp
b) Date
c) Integer.
DIM - Shared columns
Can not rename the Shared col however we can create alias.
Multi Join
Multi Store Order
- Outside in.
- Inside out.
Non Equi Join
- Left Outer
- Right outer
- Full Outer
- Inner
Can be used on above CV with Operator condition.
Operator can be different on different join fields.
Aggregation Functions
- Sum
- Min
- Max
- Ave
- STD DEV
- Variance
It should not be used in Stacked scenerio.
Aggregation
Calculated Col is always calculated after aggregation
