Obtain Historical Data for Quality Purpose

Question

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!

Chrissie Hoots · Answer

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

Sam Tsubota · Answer

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]