Suggest an Idea

[ShireyJW]

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.

[John_Teichman]

Hello @ShireyJW,

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

[ShireyJW]

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

[Tzachi_Nissim]

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

[ShireyJW]

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.

[Tzachi_Nissim]

Understood. Thank you for the clarification!

Regards,

Tzachi

[TimGarrod]

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_...