How should I join 2 transaction tables in IDT?

[Former Member]

Dear Experts,

I have a little bit knowledge with IDT now.

I'm facing problem to join 2 transaction tables in IDT as show below:

Please help me.

Best regards,

Chenna Yon

[former_member4998 · Accepted solution]

Hi..

Sales Transaction. Primary Kay (as per your tables: Sales ID) = Order Transaction. Foreign Key(as per your tables:

Sales ID),

if still not able to get the correct results as per your requirements, create derived tables and by putting all the logic.

As per best practices should not join Fact table to fact table

[Former Member · Accepted solution]

Dear Sreenivasulu Dasari,

I joined 2 transaction tables.

I can not get correct results as my requirements.

How can I create derived tables for this problem?

Could you show me the step of create derived tables for 2 transaction tables joining?

Best regards,

Chenna Yon

[former_member4998 · Accepted solution]

Hi..

You can enhance the data foundation using the derived tables , but derived table is a virtual table in the data foundation that combines other tables using calculations and functions. You can create objects in the business layer on a derived table in the same way that you do for a standard table.

1. Open the data foundation in the editor by double-clicking the data foundation name in the Local Projects View.
2. Right-click the table header of the table to be the basis for the derived table, and select Insert > Derived Table.
3. Enter a name for the derived table that is unique in the data foundation,

Write the SQL statement which is to be used for the derived table in the "Enter SQL Expression"

(Sales Transaction. Primary Kay (as per your tables: Sales ID) = Order Transaction. Foreign Key(as per your tables:

Sales ID) and calculations and restrictions)

and check any error’s by Click "Check Syntax". If the SQL gives error, check the syntax and fix it. If the SQL parses correctly, click OK.

[Former Member · Accepted solution]

Hi Chenna,

Looking into the two transaction tables, I can see the relationship as Sales_id but than its not a datamodeling practise to join them.

If you join these two tables, you will end up creating many to many relationship, which will never give you correct results.

For any of your requirement where you want to pull data from these two tables in a single query, let than data get merged in report and not in semantic layer.

If you are thinking of creating a derive table, it is same as creating a view at semantic layer and not in database but in your case you need to use subqueries to join these tables.

I would suggest to de-normalise the table and connect only dimensions with facts and use contexts to separate join paths of facts.

Your query will synchronise at report level, which will fetch you correct results than joining in universe.

Thanks

Gaurav

[Former Member · Accepted solution]

Dear Gaurav,

Could you guide me about merge strategy in report?

Best regards,

Chenna Yon

[Former Member]

Hang on, why are you joining them? You shouldn't. They are both fact tables.

Do you have common dimension tables?

Both order and sales tables should join to the associated dimension tables.

Just looking at what you've listed, you'd also need two aliases of your country table, one for sales, one for orders.

I'd strongly recommend a universe design course - this sort of basic design concept is covered in the courses.

Say you do join the two tables, what cardinality would you use? You have two Sale ID 5s in one table and two Sales ID 7s in the other - at best you're on a many to many, which gives you an instant chasm trap and over inflation of numbers.

Take a step back and think about your data model first.

[Former Member]

Chenna Yon,

Join Sales and Order table based on SALES ID.

Sales Transaction.Sales Id = Order Transaction.Sales Id

If there is Customer table too then join both Order and Customer tables based on Customer ID

Concept - Create joins: Information design tool 4.x - YouTube

[TammyPowlas]

I also moved this discussion to the Semantic Layer space

[TammyPowlas]

Please check tutorial at

Complete list of IDT tutorials is at