Three fact tables with one dimension table

Hello all, I am trying to build a QuickSight matrix table that counts the voice of customers (VOC) by customer segment. I want to do the following as illustrated by the attached image:

• Use three fact tables that contains unique VOC data. There are three different sources of VOC: one VOC table per source.
• Use a single table that contains customer data.
• Join three VOC tables and customer table through customer ID.
• In the dashboard, use calculated field to add the count of VOCs of three different sources to demonstrate an overall count.

However, I am having a hard time figuring out how to do this. Will there be any way that I can do this? Your advice would be highly appreciated.

Hi,

It is simple to manage it, just select the fact table first and dimension tables later with â€śLEFT TABLE JOINâ€ť.

Regards,
Naveed Ali

1 Like

Thank you very much for the answer @Naveed.

I am sorry for the incomplete explanation in my previous post, but what I am actually trying to do is to join tables as illustrated in the screenshot.

• Tables AAA to EEE are dimension tables in relation to Customer table.
• Customer table is also a dimension table in relation to VOC Source tables 1-3.
• On the other hand, VOC Source table 1-3 are fact tables, containing VOC from 3 different sources.

Do you happen to know if itâ€™s possible to join tables as illustrated in the screenshot?

Thank you very much in advance.

1 Like

Hi,

GREATE!
One tip for you :), yourâ€™s bigger should on left side and use the LEFT JOIN.

Regards,
Naveed Ali

1 Like

Naveed is right. Since you want metrics by Customer segment and have three different fact tables you need to run this through the Customer since you may or may not have data for a customer in all three fact tables for the same details (dates etc).

So if you have Customer on the Left side of the join and have the fact tables to the rightside of the join then I think it should work for you.

3 Likes

Did this solution work for you? I am marking this reply as, â€śSolution,â€ť but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like

@Naveed @Giridhar.Prabhu Thank you for the answers. I apologize for the late reply. I tried the following as advised:

• Customer on the left side
• VOCs on the right side
• left join (join through â€ścustomer IDâ€ť).

However, it did not work well. The count of VOCs per customer/customer-segment becomes larger than the actual count. This is because some â€ścustomer IDâ€ť appears multiple times on the right side (i.e. some customers have raised multiple VOCs). If you could let me know if I am missing something, that would be highly appreciated.

Hi,

You can try the distinct count function this will return you the actual result.
One more thin you need to group the customer id and use value for the rest of the column.
I hope this will work for you.

regards
Naveed Ali

1 Like

@Naveed I will try that. If itâ€™s not working, I will post here. Thanks a lot you for your advice!

2 Likes