Has anyone had any experience with direct querying against a star schema or snowflake model? We are planning to use Redshift for our source DB and are trying to determine if we should
create views in Redshift to accommodate the flattened structure needed for QS and the create a single dataset in QS
create individual datasets within QS for each dimension and fact and then create another dataset that joins these individual datasets
I have heard that there is a downside with creating views in Redshift because anytime the definition changes, we have to re-create the metadata for the view and that is “too much maintenance”. Are there any other pros and cons that anyone else has experienced with either of these approaches? I’m looking for a best practice so I can determine which is the right approach we should take.
Hello,
It is better to bring in the fact and dimension tables separately and join in a QuickSight dataset. This is better than creating a single unified view in Redshift and then using that view. The reason is that if the tables are in a QS-dataset, QS can intelligently drop dimension tables when they are not needed a visual today. This is configured in the join clause editor.
You can model your dataset within a QuickSight dataset using the visual join editor and QuickSight can then dynamically use the tables needed for the visuals where you use specific fields