This feature is currently in preview and will be available to all users in coming months.
An error occurred while saving the commentKin Tang commented
MPP Databases don't work like this. Other MPP database such as Netezza will not work if you have multiple columns HASH but only JOIN on one of the column. Do to the fact the data is distributed/stored using the HASH of all of columns for FACT while Dimension is distributed/stored using a different HASH of one column, the data ends up being not co-located.
In order get the benefit of the distribution, both table in the JOIN need to be co-located thus using the same HASH key for distribution. If Fact and Dimension table are distributed using different HASH columns. It will end up calling DMS every execution because the tables are not co-located.
Small Dimension table should be replicated to minimize data movement.
Reference; Designed systems using Netezza, AWS RedShift and Azure SQL Data Warehouse.
113 votesunder review · 6 comments · Azure Synapse Analytics » SQL/T-SQL · Flag idea as inappropriate… · Admin →
Thank you for voting for this feature! We are aware of this scenario and are looking into ways of supporting this. In the meantime, stay tuned for an update and please continue voting for this feature.
Thank you for your feedback. Please help us understand your scenario by filling out the following short survey if you haven’t already:
Thank you for all the feedback folks. Unfortunately this is taking longer than we’d like. We will reach back out when it is on the roadmap and can share when we have an update.
This feature is in private preview and will be available to all users in coming months.