Obtain Historical Data for Quality Purpose

[Chrissie Hoots]

If I want to know something like 'How many Features did not get assigned to a Capability as of March 31st 2023', is there a way to pull data with this type of logic? What are the tables/columns in Enterprise Insights? 

We have both current_dw and export_dw available. Thank you!

[Sam Tsubota]

Hi @Chrissie Hoots ,

You will need to use the current_dw.Feature History table and filter records that where the FK Capability ID = 0 (No Capability Assigned) and you will need to filter the Feature Fact Valid From and Feature Fact Valid To dates based on March 31, 2023.

The filtering of dates can be a little tricky and you may end up with some duplicate records. I first filtered records where the Feature Fact Valid From is less than or equal to March 31, 2023 and the Feature Fact Valid To is equal to 12-31-9999. These are the current feature records. You may also want to return features that were at one time not assigned to a capability. For those records I filtered records where Feature Fact Valid To equals March 31, 2023. 

Here is a sample SQL query. I used GROUP BY to try and remove duplicate records.

SELECT

F.[FK Feature ID]

,F.[Feature Name]

,F.[Feature Fact Valid From]

,F.[Feature Fact Valid To]

,F.[FK Capability ID]

FROM

(

SELECT

FH.[FK Feature ID]

,FH.[Feature Name]

,CAST(FH.[Feature Fact Valid From] AS DATE) AS [Feature Fact Valid From]

,CAST(FH.[Feature Fact Valid To] AS DATE) AS [Feature Fact Valid To]

,FH.[FK Capability ID]

FROM [current_dw].[Feature History] AS FH

WHERE FH.[FK Capability ID] = 0

AND (

(CAST(FH.[Feature Fact Valid From] AS DATE) <= '2023-3-31' AND FH.[Feature Fact Valid To] = '9999-12-31 00:00:00.000') --Current history record that does not have capability assigned base on filter date

OR

CAST(FH.[Feature Fact Valid To] AS DATE) = '2023-03-31' --History record filtered by date where capability was not assigned

)

) AS F

GROUP BY --Grouping to remove duplicate feature records

F.[FK Feature ID]

,F.[Feature Name]

,F.[FK Capability ID]

,F.[Feature Fact Valid From]

,F.[Feature Fact Valid To]

[Chrissie Hoots]

Thank you Sam for your prompt response. This should be a good start. I may come back with more questions later.