idea Add Support for Distribution Method for data mart in Azure Synapse Analytics in Suggest an Idea

Add Support for Distribution Method for data mart in Azure Synapse Analytics

ShireyJW — Wed, 15 Jul 2020 06:34:54 GMT

Description:

In Azure Synapse Analytics, fact tables distribution method reverting back to round robin once the ETL workflow runs, It looks like the process drops and re-creates the fact tables each time (or whenever this happens, the distribution method is reset).

In addition, the distribution method can be set in the data warehouse, but is not available as a setting in the data mart. Therefore, the distribution method is set to "Round Robin" by default, instead of Replicate or Hash (which is much more desirable for dimension and fact tables respectively).

Value Proposition:

I constantly have to manually maintain the distribution method settings on data mart tables because the default behavior does not perform very well in a Microsoft Azure environment. Fixing this will make the product usable.

Target Audience:

Developers

How does this help the user:

Our developer staff will not have to manually maintain these tables every time the code is "generated"... which is often in a development process.

Re: Dictate Distribution Method for data mart tables in Azure Synapse Analytics

John_Teichman — Tue, 14 Jul 2020 04:41:14 GMT

Hello @ShireyJW,

Please update your post per the submission guidelines. I do not see an actual Idea submission right now.

Re: Add Support for Distribution Method for data mart in Azure Synapse Analytics

ShireyJW — Tue, 14 Jul 2020 11:58:03 GMT

I have edited the idea... but not really sure what you mean by "I do not see an actual Idea submission right now".

Re: Add Support for Distribution Method for data mart in Azure Synapse Analytics - Status changed to: Open - Collecting Feedback

Tzachi_Nissim — Mon, 17 Aug 2020 11:24:01 GMT

Hi ShirleyJW,

That completely makes sense and is needed. I assume this is an aggregated or state oriented fact if it is recreated every time - right?

Regards,

Tzachi

Re: Add Support for Distribution Method for data mart in Azure Synapse Analytics - Status changed to

ShireyJW — Mon, 17 Aug 2020 14:43:44 GMT

That is correct... we have standardized on "State Oriented" facts because the behavior of "Transaction" facts won't work for us. There is no update, that we could see from our testing, to Transaction facts even though our data warehouse could have more recent changes. The Transaction fact seems to only ever show the original version of any of its facts. Because of this, we are forced to use the "State Oriented" facts and use a view to show the latest version of each fact to simulate the "Transaction" fact. We also can show all the fact versions for those that desire that as well.
In any case, we need support to control the distribution method in the data mart, the data warehouse, as well as the staging areas to get performance on Synapse.

Re: Add Support for Distribution Method for data mart in Azure Synapse Analytics

Tzachi_Nissim — Tue, 18 Aug 2020 10:35:37 GMT

Understood. Thank you for the clarification!

Regards,

Tzachi

Re: Add Support for Distribution Method for data mart in Azure Synapse Analytics - Status changed to: Delivered

TimGarrod — Mon, 10 Jan 2022 22:59:06 GMT

2 features were added to Qlik Compose due to this ideation.

1. Table creation modifiers were added to Qlik Compose in the May 2021 release.

2. Behavior for any "re-load" required by Qlik Compose was adjusted to TRUNCATE instead of Drop and Recreate the fact tables.

Item 1 included the ability to define any physical properties for table creation

Distribution type & hash keys
Table type (clustered index table v clustered columnstore)
etc.

https://help.qlik.com/en-US/compose/May2021/Content/ComposeDWDL/Main/DW/Data%20Marts/table_creation_modifiers_schemas.htm