Architecture for direct query off snowflake / star schema model

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.

+1 to Jose B. Amazon QuickSight enhances dashboard performance with Dynamic Querying and other dataset optimizations

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

1 Like

Hi,

this videos may be help you :slight_smile:

@JoseB-aws @jhnjos Wouldn’t that just mean that your definition (view) was poorly defined if you have added extra things that weren’t needed?