How do I delete profiling reports in the datamart

author
Talend Documentation Team
EnrichVersion
6.4
6.3
6.2
6.1
6.0
EnrichProdName
Talend Big Data Platform
task
Design and Development
Data Quality and Preparation
Data Quality and Preparation > Building reports
EnrichPlatform
Talend DQ Portal
Talend Studio

How do I delete profiling reports in the datamart

You may need to delete some of the profiling reports in the datamart of the data quality Portal to ease the management of the reports. This is possible by using SQL queries in the Data Explorer perspective in the Studio.
Environment

This procedure applies to version 6.0.1 and later of the Studio.

ProcedurePrerequisites:
  • You have installed the Portal and defined a datamart for the reports.
  • You have executed some reports in a Talend Studio connected to the datamart of the Portal where you have save report results.
Get the report identifier from the datamart
  1. Open the Data Explorer perspective in the Studio.
  2. Right-click a database connection in the Connections view and select New SQL Editor .
  3. Enter the following SQL query in the editor to get the identifier of one report:
    use <datamart database name>;
    /* get report UUID */ 
    select  REP_UUID, REP_LABEL  from tdq_analysis where REP_LABEL = '<report name>';

    Or use the below SQ query to get the identifiers of several reports. Make sure to use the exact datamart and report names in the query.

    use talend_dq61;
    /* get report UUID */ 
    select  REP_UUID, REP_LABEL  from tdq_analysis where REP_LABEL like 'c%'

    In this example we want to access the talend_dq61 datamart and get the identifiers of all the reports which start with the letter c .

  4. Execute the SQL query.

    The data explorer outputs the report names and their identifiers, three in this example.

    Now you can use one or more of the identifiers in another SQL query to delete the report(s).

Delete the report(s) from the datamart
  1. Open an editor in the Data Explorer perspective in the Studio.
  2. Enter the following SQL query in the editor. Make sure to execute the query in the order shown below.

    In this example, we want to delete, from the talend_dq61 datamart, the two reports which have the identifiers _EuRp8Ey8EeWI7MB95QH5HA and _cabpUUzUEeW6FuESwuuXqA .

    use talend_dq61;
    /*must execute the sql in this order*/
    /*1. delete data from tdq_indicator_value table*/
    delete from tdq_indicator_value where analysis_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA'));
    /*2. delete data from tdq_overview_indvalue table*/
    delete from tdq_overview_indvalue where an_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA'));
    /*3. delete from tdq_set_indvalue table*/
    delete from tdq_set_indvalue where an_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA'));
    /*4. delete from tdq_analyzed_set table*/
    delete from tdq_analyzed_set where match_ind_pk in(select m_pk from tdq_match_indvalue where analysis_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA')));
    /*5. delete from tdq_match_indvalue table*/
    delete from tdq_match_indvalue where analysis_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA'));
    /*6. delete from tdq_table_analyzed_set table*/
    delete from tdq_table_analyzed_set where an_pk in (select an_pk from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA'));
    /*7. delete from tdq_analysis table*/
    delete from tdq_analysis where rep_uuid in ('_EuRp8Ey8EeWI7MB95QH5HA', '_cabpUUzUEeW6FuESwuuXqA');
  3. Execute the SQL query to delete the reports.
  4. In an SQL editor, re-run the below SQL query:
    use talend_talend_dq61;
    /* get report UUID */ 
    select  REP_UUID, REP_LABEL  from tdq_analysis where REP_LABEL like 'c%'

    The two reports which have the identifiers _EuRp8Ey8EeWI7MB95QH5HA and _cabpUUzUEeW6FuESwuuXqA have been deleted from the damart.

    The reports have been deleted from the TDQ_ANALYSIS table, and all facts (analysis results) related to these reports have been deleted from the fact tables.

Related Jira Issues

DOCT-5634