Multiple fact tables

Question & Answer
,
1

Hello,
I have 2 fact tables with different granularity (and some dimension tables) where I want to use in one dataset.
How can I do this without creating duplacate values?

Thanks,
Mia

2 Likes
2

H @miap - Welcome to AWS QuickSight community and thanks for posting the question. Before creating the data set at QuickSight you need to fix M:M ( Many to Many) relationship. Possibly you may need a bridge table which should fix the issue . This is a data modelling approach. Once you can fix your relations and validate the sample data in DB and understand joins, you can import the data sets and create a custom data set in QuickSight out of it. Also you create a data set with Custom SQL in QuickSight as well.

QuickSight will not solve your data granularity issue, you have to fix it at database and model it before import to QuickSight.

Hope the details will give some info.

Regards - Sanjeeb

1 Like
3

Thanks for your response but I’m not shure I understand.
For example, I have 3 tables:

Table 1 - ‘Budget’
DepartmentId - YearlyBudget
1 - 100M
Table 2- ‘Expenses’
DepartmentId - Date - Expensess
1 - 01.01.23 - 150
1 - 02.01.23 - 200
Table 3 - ‘Date’

How can I fix the relashionship to M:M and use these tables in the same dataset, if the only option in QS is join? (Instead of a star scheme)

Thanks in advance,
Mia.

4

Hi @miap - The M:M relationship can not be fixed at QuickSight level, you have to fix at ETL by introducing a bridge table.

Regards - Sanjeeb

1 Like
5

How does a bridge table fix the issue ? you are still joining into it and duplicating your values no ?