Questioning regarding Alias Table in Incorta for developers course

[mohamed_kamal]

in Incorta For Developers course, Lab: Schema Workshop, Check#2 and Check#3,
the instructor made an Alias table while the analysis or even the design can be done successfully without making it, as there was no more than one relation between the same two tables.

let's say there's table X and table Y and there's a join relation between both of them, the instructor wanted to introduce a new join relation between table X and table Z, and there was no previous join relation between X and Z. so, Instead of making the relation normally between the two original tables, the instructor made an alias table for X to introduce the new join relation.

so is there another reason that I didn't catch, is there a type of principle in the designing that the instructor follow or what is the point?

Thanks in advance.

[Shashidhar]

Hi kamal,

All joins in Incorta are Left outer joins by default. 

In case 1, the connection is X to Y and Y to Z, and if you want to get data from X and Z, you need to either include the Y object(column) in the query or explicitly reference Y as the driving Table(base). 

But here your secenario from case 2  is a bit different, Y is common parent for both X and Z. If you want to get data from X and Z, then here we need to handle many to many relationship. Here you can take the help of bridge table to solve this. Instead, In developer course, instructor created alias of X and join with Z which you can see in case 3. Sharing few URLs for your reference.

https://community.incorta.com/t5/data-schemas-knowledgebase/modeling-schemas-tables-and-joins/ta-p/7...

https://docs.incorta.com/cloud/concepts-base-table

https://www.youtube.com/watch?v=FJgFiDvToUY

Regards,

Shashidhar.S

[dylanwan]

Creating an alias is similar to using an alias in a SQL. You can create join between two tables using one foreign key. For example, a table has multiple date columns, order date, booking date, shipping date, scheduled delivery date.  If you want to create a join between this table and the DATE table, you Aliases will be used for creating joins between two tables. 

Which table should be created as a alias depending the reporting requirements.  It is typically related the common grouping attributes that will be used in the analysis.  For example, if you want to rollup the # orders booked and the # of order shipped.  The DATE table will be the common parent table and the ORDER TRANSCATION table will have two aliases, one for ORDER and the other for SHIPPING. 

Incorta generates the query plan based on the joins defined.  If more than one path is found, the query plan may not be desirable.  You can use alias to break that linkage.  Ideally, we should avoid multiple path between any two tables to reduce the ambiguity.   For example, the ADDRESS BOOK can be used to store customers as well as suppliers.  You may typically not want to use this to view the data side by side, two aliases, SUPPLIER and CUSTOMER may be created.

In term of physical storage, unlike creating aliases in the relational database like Oracle, aliases itself may not take space but joins will be created in Incorta Direct Data Mapping files.  Those joins will be refreshed during schema refresh so aliases and joins should only be created if they are really needed.