TFS Data Warehouse Problem extracting Test Run history - extra rows in FactTestResult table (PointID, ChangeNumber)

Question

I am building a Power BI report to display Test Executions for Azure DevOps (On-Prem). This data is not exposed via OData so I have to use a live connection to the TFS Analysis instance. Due to the live connection in Power BI, I am not able to add to the Data Model as I would like. I have written some queries to retrieve the data from the Data Warehouse I need but I have noticed that there are a number of extra rows in the FactTestResult table that contain values for the PointId and ChangeNumber - not really sure what these records are used for as I can not see them in the UI - Azure DevOps Server 2020. Adding the filter ResultCount = 1 will remove these other rows resulting in a single row. There is however only a single row remaining where AreaSK and IterationSK are both null - unlike the columns I have just filtered out. Ideally I would be able to join on the Area and Iteration and I can work around this problem, I would just like to understand what is the purpose of these extra rows - PointID and ChangeNumber and is there a better way to write the Test execution history query below.

SELECT *
  FROM [Tfs_Warehouse].[dbo].[vFactTestResultOverlay] FTRO
  join vDimTestRunOverlay DTRO on 
  DTRO.TestRunSK = FTRO.TestRunSK 
  where DTRO.TestRunId = "testRunID" and 
  TeamProjectCollectionSK = "projectCollectionID"

Answer 1

TFS Data Warehouse Problem extracting Test Run history - extra rows in FactTestResult table (PointID, ChangeNumber)

For the PointID, we could to know it is the ID of test point. Indeed, we cannot see it directly on the UI, but we could get it by the REST API:

Points - List

GET https://dev.azure.com/{organization}/{project}/_apis/test/Plans/{planId}/Suites/{suiteId}/points?api-version=6.0

And we could see Points on the UI:

For the ChangeNumber, I also could not get much info about it, just the document about it:

Test Results and Test Runs

Answer 2

using code similar to the following I managed to order and find the most recent record without the null entries for iteration and area. Didn't explicitly refer to changeNumber in the end.

select 
*, 
Row_Number() over (partition by FTR.TeamProjectSK,FTR.TestSuiteSK,FTR.TestCaseId,FTR.ConfigurationSK order by FTR.ResultDate desc) as TestResultsPerTestCaseOrderedDesc
from [Tfs_Warehouse].[dbo].[FactTestResult] FTR 
where PointrecordCount = 1