Hello guys, I hope you are doing well.
Could you help me with my data model? How to model two fact tables in one single dataset? My model is available below.
Unfortunately, I cannot build a temp table and need to show forecasts and Sales in one visual.
QuickSight doesn’t allow me to use two different measures (different datasets) in one visual.
I am using CSV files and I cannot use sql script to do that.
Thank you
Hi @woliveiras - Your Sales fact is prod grain and forecast is category grain. Also why you have 3 date dimensions. This looks like a galaxy model. What is your common dimension between 2 facts… If you can share some sample data as well so that we can give a try.
Hi @David_Wong - Any advise on this.
Regards -Sanjeeb
Leaving aside the data modeling questions around the difference in granularity between Fact_Sales and Fact_Forecast, what you can do to join two CSV in a single data source is:
Hi, @eperts. Thank you for your message. The critical point here is to solve the snowflake model in QuickSight, considering different levels of granularity. This is something that, PowerBI solves quickly, QuickView in the same way as SAP BusinessObjects or Microstrategy. I am trying to understand the best way to use QuickSight when we cannot change our model. If I have an ETL tool, Data Glue, or even this information in a database, I think we could solve it quickly.
Thanks again.
Hi @Sanjeeb2022, thank you for your message. We have a snowflake model here, and our “Date” dimension is composed of a Year table, Month and Date. We could use just a date to get everything we need. However, my intention here is to show that my situation is different granularities and two or more fact tables (all of them in different levels)
Hi all!
@woliveiras the question is indeed a very valid question. In QuickSight a Dataset is single table model. With SPICE and our analytical engine it becomes a very large, fast and highly scalable analytical table, but still one big table.
As @eperts correctly mentions our data prep allows one to JOIN multiple csv files while specifying join type and columns to join on. In most cases you could join both fact tables on the desired columns (common reporting dims/common grain) and resulting Dataset used for visualizations requiring metrics from both facts. Dataset will have duplication and it will be important to use LAC-A and LAC-W to ensure aggregations occur at the desired grain for the analysis.
In your case it seems that the common columns do not exist in the two fact tables but depend on joins with multiple other tables concurrently (part of the snowflake model). This can be handled by leveraging a query engine (athena) or ETL flow (Glue) to transform facts to include common dims. (there are probably a few ways one could model this togehter)
We are working on enhancements to our data prep and query engine and would love to gather your feedback on your use case, current workarounds and inform our product design process.
Please Direct Message me and happy to continue the discussion there.
thanks!
Ramon Lopez
Hi @Ramon_Lopez, First, thank you for your clear message. We are working on a fantastic product, and QuickSight is one of our options to deliver the best value for our clients. I understood everything you explained, and it is precisely what we are seeking (working together to provide the best value in terms of data exploration). Understanding what QuickSight has now (and in the future) is essential. We can find the best solution together.
I’m going to send a message to you, and we can discuss it there.
Thank you again.
