Hi
We are currently building a Dimensional Reporting Model from the Jira database to do extensive Power BI Analysis\Trending and Tracking on the Jira data and are building directly from the jira database schema.
There is a need to view tasks per Kanban board and the challenge is to find a proper link from the ‘Kanban Board’ to its relevant project and jira issues in the database.
The table in question is the AO_60DB71_RAPIDVIEW table.
The below query returns all the sprint boards to the jiraissue table and works perfectly but the same logic does not apply for Kanban boards even though the Kanban board descriptions does appear in the A0…..RAPIDVIEW table.
Could you please be of assistance to help us identify the proper joins in the database to join the Kanban board to it’s project and issues.
The below code links jira issues to the relevant sprints.
Select
[spr].[NAME] As [SprintName],
Case When spr.CLOSED = 1 Then 'Yes' Else 'No' End As [SprintClosed],
[customf].ISSUE As [IssueID],
[spr].ID As [SprintID],
[rv].[Name] As [BoardName],
Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left([START_DATE],10))) , Cast('1970-01-01 00:00:00' As DateTime))) As [SprintStartDate],
Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left(END_DATE,10))) , Cast('1970-01-01 00:00:00' As DateTime))) As [SprintEndDate]
From [jira].[customfieldvalue] AS [customf]
Inner Join [jira].[AO_60DB71_SPRINT] As [spr]
On
Convert(varchar,[spr].[ID]) = [customf].STRINGVALUE
Left Outer Join [jira].[AO_60DB71_RAPIDVIEW] As [rv]
on
[rv].[ID] = [spr].[RAPID_VIEW_ID]
Where [customfield] = (Select [ID] From [jira].[customfield] Where cfname = 'Sprint')
