idea Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change - Status changed to: Delivered in Suggest an Idea

Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change

QTC_Manuel — Fri, 15 Jan 2021 19:00:48 GMT

Currently, when making any structure changes (E.g. add a column, drop a column) to a data mart fact or dimension, a subsequent validate of the changes results in dropping and recreating the entire data mart, even if only one object should have been modified. This causes a usability and data availability issue, particularly when you have a large data mart (300+) facts, because both the drop and create and reload operation of the entire mart could take many hours.

The idea is to potentially add more elaborate logic to the validation so that it can detect a minor change to one table and just operate on that table rather than the entire data mart.

@TimGarrod @Tzachi_Nissim

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change

paulstevens — Fri, 15 Jan 2021 20:19:49 GMT

We also experience this behaviour in the data mart and would like changes to be applied to the data mart without requiring 'drop and recreate'.

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change

jtompkins — Fri, 19 Mar 2021 15:00:58 GMT

YES! This is such a hurdle for us, especially because dropping/recreating dims breaks the referential integrity of our historical fact tables.

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change - Status changed to: Open - On Roadmap

Tzachi_Nissim — Thu, 08 Apr 2021 12:37:19 GMT

Hi Manuel,

Thanks for your input and thanks to others that commented. This is on the roadmap, but I would be interested in what you consider the most painful "minor change" that should be addressed first.

Thanks,

Tzachi

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change

QTC_Manuel — Thu, 15 Apr 2021 13:31:28 GMT

Hi Tzachi,

We could start with the column rename on a dimension or fact. Which should be an alter table statement.

Thanks,

Manuel

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change

dpurdy66 — Thu, 20 May 2021 12:18:13 GMT

We just bought Compose for DW and somehow we missed this problem in the POC we did before committing. This is a big issue. Dropping and recreating the entire data mart forces changes to be done only during weekend maintenance windows. Worse, it encourages modelers to include everything "just in case." That is the opposite of agile development.

I realize some types of changes are "breaking changes" and I would expect to have to drop and recreate the affected tables to implement them. However, for non breaking changes that could be done with alters and update statements, the burn everything down and rebuild approach is unreasonable.

IMO, adding an attribute to a dimension would be a great place to start and would add a lot of value to the product. Next, I would tackle adding columns to fact tables and then adding new dimensions. Of course renaming attributes and non breaking data type changes e.g. varchar(10) to varchar(20) should be low hanging fruit and be included in the first iteration since they would literally only require a simple alter table statement to implement.

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change - Status changed to: Delivered

TimGarrod — Wed, 11 May 2022 18:09:40 GMT

This feature is delivered in May 2022 Qlik Compose release.

Re: Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change - Status changed to: Delivered

TimGarrod — Wed, 11 May 2022 18:11:58 GMT

This feature is available in Qlik Compose May 2022 release.

Please see the help for -

Validating and Adjusting the Data Mart

https://help.qlik.com/en-US/compose/May2022/Content/ComposeDWDL/Main/DW/Data%20Marts/validate_the_data_mart.htm#Validating%20and%20adjusting%20the%20data%20mart

AND

Reload Data Mart

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