Solved

How to delete base model related stuff

  • 12 April 2024
  • 8 replies
  • 40 views
H
Rank
Userlevel 2
Badge +5

Hi, 

In the past, we included the base model for the Exchange connector into our work project. But we stopped with that and now we use the ms_graph functionality.

But there are several ‘exh_**’ domains and tables auto generated by the base model, but these can't be deleted from the SF user interface.
Even not after unlinking the base model from the project and generating the definitions.

Is there an option to get them safely deleted from the model?

Thanks in advance!

icon

Best answer by Mark Jongeling 12 April 2024, 13:41

View original

8 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi HJ,

From 2023.2 the deletion of generated objects from base models has changed, the deletion of these objects happens only if they are not in use by other objects.

So the domains will be cleaned up if there are no columns using these. The deletion will happen bottom up so once the lowest level is unused, the rest will be cleaned up when running a Generate definition. You should be able to see the Usage of the domains in the Domains screen in the detail tab Usage:

SF > Data model > Domains > Usage

For the Tables, they could be in use by Process actions for example, or in a Menu. Navigate through the details availabke in Subjects. The important screens here are the Links and the Applied to.

Hope this helps out cleaning it up!

On holiday May 6 - May 31
H
Rank
Userlevel 2
Badge +5

Hi, 

I already checked the menu's and process flows and functionality.
But these tables are not used in this branch any more.

But still the tables can't be deleted or are getting deleted by Generate definitions:
 

And due to this, 41 exh_ domains are not deleted as they are used in the tables above.

 

I only see that the translations are still there…


We are using the 2023.2.12 version
 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Can you check the Generated by control procedure value for these tables? In 2023.2, they should be Copied from base model. (See Form > Trace info)

If the value is Copied from base project. then that is most likely the cause of this problem. Fixing that is a little challenging as all related object should be updated as well. If this is the problem, you can try the following and the regenerate the definitions:

-- Replace with Model and Branch of your choice
declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{}'

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from dom t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_component_property t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_component t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from screen_type t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from col t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

update t1
set t1.generated_by_control_proc_id = 'Copied from base model'
from tab t1
where t1.generated_by_control_proc_id = 'Copied from base project'
  and t1.model_id = @model_id
  and t1.branch_id = @branch_id

If for the given model and branch it gives the desired result, you could opt to run it for all models and branches inside your Software Factory.

In 2023.2, Copied from base project should have been replaced entirely by Copied from base model. This is done during the upgrade to 2023.1.

On holiday May 6 - May 31
H
Rank
Userlevel 2
Badge +5

I've checked, but these are already properly updated with the upgrade.
There are no results in the SF database for the generated_by_control_proc_id = 'Copied from base project'

 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hmm, then we need to be looking even deeper, we need to find where the table is used. The code looks through the following tables to see the table (tab_id) is in use:-

  • linked_model (not present in any other base model) - This could be the most likely cause

Query to check:

declare @model_id model_id = '{MODEL}', @branch_id branch_id = '{BRANCH}'

select t1.tab_id, l.base_model_id, l.base_branch_id
from tab t1
join linked_model l
  on l.work_model_id = t1.model_id
 and l.work_branch_id = t1.branch_id
where t1.model_id  = @model_id
  and t1.branch_id = @branch_id
  and t1.generated_by_control_proc_id = 'Copied from base model'
  and exists (select 1
                from tab b
               where b.model_id = l.base_model_id
                 and b.branch_id = l.base_branch_id
                 and b.tab_id = t1.tab_id)

If this returns a Base model and branch, it means that table is still present in a Base model that is linked to your work model. That's why the table is not removed.

or else:

  • automl_model
  • automl_model_fitted
  • automl_model_predictor
  • automl_model_target
  • col (either as tab_id or view_tab_id)
  • col_data_sensitivity
  • conditional_layout
  • cube
  • data_set_tab
  • drag_drop
  • indx
  • list_bar_item map
  • map_base_layer
  • module_item
  • process_action
  • process_action_col_input_parmtr
  • process_action_output_parmtr
  • process_action_start_tab
  • process_action_start_tab_variant
  • process_action_tab_prefilter_input_parmtr
  • ref (either source_tab_id or target_tab_id)
  • role_tab scheduler
  • scheduler_view
  • tab_check_constraint
  • tab_prefilter
  • tab_prefilter_grp
  • tab_prefilter_tag
  • tab_report
  • tab_report_grp
  • tab_tab_drag_drop (either drag_tab_id or drop_tab_id)
  • tab_tab_variant_drag_drop (either drag_tab_id or drop_tab_id) tab_tag
  • tab_task tab_task_grp
  • tab_variant
  • tab_variant_map_overlay
  • tab_variant_tab_drag_drop (either drag_tab_id or drop_tab_id)
  • tab_variant_tab_variant_drag_drop (either drag_tab_id or drop_tab_id)
  • test_case
  • tile

 

Handy query to check thanks to ChatGPT:

DECLARE @tab_id VARCHAR(50) = 'ext'; -- Replace 'your_tab_id_here' with the tab_id you want to check
declare @model_id model_id = '{model}', @branch_id branch_id = '{branch}'

IF EXISTS (
    SELECT 1 FROM (
        SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'col', tab_id FROM col WHERE (tab_id = @tab_id OR view_tab_id = @tab_id) and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_col_input_parmtr', tab_id FROM process_action_col_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_start_tab_variant', tab_id FROM process_action_start_tab_variant WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_tab_prefilter_input_parmtr', tab_id FROM process_action_tab_prefilter_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'ref', source_tab_id AS tab_id FROM ref WHERE source_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'ref', target_tab_id AS tab_id FROM ref WHERE target_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_tab_drag_drop', drag_tab_id FROM tab_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_drag_drop', drop_tab_id FROM tab_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_variant_drag_drop', drag_tab_id FROM tab_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_variant_drag_drop', drop_tab_id FROM tab_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_tab_drag_drop', drag_tab_id FROM tab_variant_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_variant_tab_drag_drop', drop_tab_id FROM tab_variant_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drag_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drop_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id
    ) AS all_tabs
    WHERE tab_id = @tab_id
)
BEGIN
    SELECT 'The tab_id is in use in table: ' + table_name
    FROM (
        SELECT 'automl_model' AS table_name, tab_id FROM automl_model WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_fitted', tab_id FROM automl_model_fitted WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_predictor', tab_id FROM automl_model_predictor WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'automl_model_target', tab_id FROM automl_model_target WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'col', tab_id FROM col WHERE (tab_id = @tab_id OR view_tab_id = @tab_id) and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'col_data_sensitivity', tab_id FROM col_data_sensitivity WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'conditional_layout', tab_id FROM conditional_layout WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'cube', cube_id FROM cube WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'data_set_tab', tab_id FROM data_set_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'drag_drop', drag_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'drag_drop', drop_tab_id FROM drag_drop WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'indx', tab_id FROM indx WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'list_bar_item', tab_id FROM list_bar_item WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'map_base_layer', tab_id FROM map_base_layer WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'module_item', tab_id FROM module_item WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action', tab_id FROM process_action WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_col_input_parmtr', tab_id FROM process_action_col_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_output_parmtr', tab_id FROM process_action_output_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_start_tab', tab_id FROM process_action_start_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_start_tab_variant', tab_id FROM process_action_start_tab_variant WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'process_action_tab_prefilter_input_parmtr', tab_id FROM process_action_tab_prefilter_input_parmtr WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'ref', source_tab_id AS tab_id FROM ref WHERE source_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'ref', target_tab_id AS tab_id FROM ref WHERE target_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'role_tab', tab_id FROM role_tab WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'scheduler', tab_id FROM scheduler WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'scheduler_view', tab_id FROM scheduler_view WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_check_constraint', tab_id FROM tab_check_constraint WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter', tab_id FROM tab_prefilter WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter_grp', tab_id FROM tab_prefilter_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_prefilter_tag', tab_id FROM tab_prefilter_tag WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_report', tab_id FROM tab_report WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_report_grp', tab_id FROM tab_report_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_tab_drag_drop', drag_tab_id FROM tab_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_drag_drop', drop_tab_id FROM tab_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_variant_drag_drop', drag_tab_id FROM tab_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_tab_variant_drag_drop', drop_tab_id FROM tab_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_tag', tab_id FROM tab_tag WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_task', tab_id FROM tab_task WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_task_grp', tab_id FROM tab_task_grp WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant', tab_id FROM tab_variant WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_map_overlay', tab_id FROM tab_variant_map_overlay WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_tab_drag_drop', drag_tab_id FROM tab_variant_tab_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_variant_tab_drag_drop', drop_tab_id FROM tab_variant_tab_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drag_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drag_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
		UNION ALL SELECT 'tab_variant_tab_variant_drag_drop', drop_tab_id FROM tab_variant_tab_variant_drag_drop WHERE drop_tab_id = @tab_id and model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'test_case', tab_id FROM test_case WHERE model_id = @model_id and branch_id = @branch_id
        UNION ALL SELECT 'tile', tab_id FROM tile WHERE model_id = @model_id and branch_id = @branch_id
    ) AS used_table
    WHERE tab_id = @tab_id
END
ELSE
BEGIN
    PRINT 'The tab_id is not in use.';
END

 

 

On holiday May 6 - May 31
H
Rank
Userlevel 2
Badge +5

The first query gives no results in the branch where I deleted the base model from 
(DEV), but for TEST it still gives results.

The other query to check for results initially resulted in some roles still having the rights for some of the tables and some data sensitivity setup.
These I could remove.

Now only result is returned that it is in use for the table ‘col’ , but that is logical as it still has columns, (but these cannot be deleted via SF).
 

 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi HJ, as a final blow you can execute the task_delete_tab via SSMS. Fill in three parameters: Model, Branch, and Table

Then the task will clean up all the tables and underlaying data. That should definitely fix it. 

exec task_delete_tab 'Model', 'Branch', 'exh_log'
exec task_delete_tab 'Model', 'Branch', 'exh_A'
exec task_delete_tab 'Model', 'Branch', 'exh_B'
-- ....

 

On holiday May 6 - May 31
H
Rank
Userlevel 2
Badge +5

The tables with the domains are deleted now from the branch.

Thanks!

Reply


Terms & ConditionsCookie settings