In Quicksight, I have a fact table named “sales” and two dimension tables named “locations” and “products.” I need to create two visualizations: one that shows sales by location and another that shows sales by product. Additionally, I need filters based on location and product.
The requirement is to display all locations and products in the filters, even if they do not have corresponding data in the fact table. However, when joining the fact table with the dimension tables in Quick Sight, only those locations and products with data in the fact table are shown.
How can I display a list of locations and products that exist in the dimension tables but do not have any data in the fact table?
Hi @achourey
Your requirement is a star schema design. In QuickSight dataset you have to join the required tables that get all relevant details as one dataset.
In your case you can achieve the results through an left/right join on the sales table from both the Product and Location tables. This way though there are no sales the data from the dimension tables is part of your dataset and you can show the same in your visualizations and parameters.
Sample dataset with outer joins from Dimension tables
dataset output
Visualization
There is a pending request to allow creating datasets by specifying One-to-Many relationship with Fact table. I don’t know when that will be available.
1 Like
Hi @achourey, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)
