Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE

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

ShireyJW
Contributor III
Contributor III

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

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.

 

Tags (1)
6 Comments
John_Teichman
Former Employee
Former Employee

Hello @ShireyJW,

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

ShireyJW
Contributor III
Contributor III

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
Employee
Employee

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

Status changed to: Open - Collecting Feedback
ShireyJW
Contributor III
Contributor III
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
Employee
Employee

Understood. Thank you for the clarification!

 

Regards,

Tzachi

TimGarrod
Employee
Employee

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

 

Status changed to: Delivered