Summary

This article is prepared to help fetch the canceled Epics from Enterprise Insights and Atlassian Analytics.

Atlassian Analytics only the current_dw schema is available. Therefore it is not possible to match the canceled Epics with Portfolio ID since it is also removed.
There is an enhancement request to use the export_dw schema in Atlassian Analytics.  https://jira.atlassian.com/browse/JIRAALIGN-6838

Environment

Jira Align - Enterprise Insights 

Solution

The following SQL query fetches the list of all canceled Epics for the given Portfolio ID

DECLARE @PortfolioId INT = <Portfolio ID here>
SELECT DISTINCT 
    eh.[fk epic id],
    eh.[epic name],
    eh.[canceled flag],
    eh.[deleted flag],
    eh.[fk program id]
FROM current_dw.[epic history] eh
INNER JOIN (
    SELECT DISTINCT
        eh_inner.[fk epic id]
    FROM current_dw.[epic history] eh_inner
    INNER JOIN current_dw.[program] p ON eh_inner.[FK Program ID] = p.[Program ID]
    INNER JOIN current_dw.[portfolio] po ON p.[FK Portfolio ID] = po.[Portfolio ID]
    WHERE po.[Portfolio ID] = @PortfolioId
) AS EPICS ON eh.[fk epic id] = EPICS.[fk epic id]WHERE eh.[FK Program ID] = 0
    AND eh.[Epic Fact Valid To] = '9999-12-31 00:00:00.000'
    AND eh.[canceled flag] = 'Yes'
    AND eh.[deleted flag] = 'Yes'