Enterprise Insights Capacity Table Question

[Joe]

So I am trying to find out who is and is not entering data into the capacity page.  I am querying data via Enterprise Insights.  I am trying to join that table to my teams > programs > PI so that I can get a view of data for a PI for each team.

The issue is that I don't understand why there are so many rows with the same data for a team, with different capacity ID's?

The data below is from just joining the [export_dw].[team] on team ID? 

Kinda stuck...

[Allan Maxwell · Accepted solution]

@Joe 

At a glance, it looks like you are getting the multiple plans by querying only Capacity; you probably also need to group by or otherwise filter on Capacity Plan.  In the example below, I join several other tables to make the results more readable.

SELECT cPlan.[Capacity Plan ID], pi.[PI Name], ao.[Customer Object Name], prog.[Program Name], team.[Team Name], cap.[Available Capacity Value], cap.*

FROM [current_dw].[Capacity Plan] AS cPlan

LEFT JOIN [current_dw].[Program Increment] AS pi ON pi.[Program Increment ID] = cPlan.[FK Program Increment ID]

LEFT JOIN [current_dw].[Agile Object] AS ao ON ao.[Agile Object ID] = cPlan.[FK Agile Object ID]

LEFT JOIN [current_dw].[Capacity] AS cap ON cap.[FK Capacity Plan ID] = cPlan.[Capacity Plan ID]

LEFT JOIN [current_dw].[Program] AS prog ON prog.[Program ID] = cap.[FK Program ID]

LEFT JOIN [current_dw].[Team] AS team ON team.[Team ID] = cap.[FK Team ID]

WHERE cPlan.[Capacity Plan ID] = 3

ORDER BY cPlan.[Capacity Plan ID], prog.[Program Name], team.[Team Name];

 

[Joe]

AHH, I didn't see the [Capacity Plan] table.  Why are you bringing in the [Agile Object] Table?

I appreciate the reply!

-joe

[Allan Maxwell]

The Agile Objects table stores the values from the Platform Terminology page.  I pulled in just incase the customer rename Epics to something like "Initiatives".

[Joe]

Ok, thanks for that.  Think I'm good.

-joe