Epic-Story Relationship in VersionOne Datamart

[Padmakumar]

Hi,

I am using VersionOne datamart for the integration of VersionOne with
one of our in-house applications in my company. I need to know how to
fetch the epics and the stories under the epics from the datamart
using an SQL Query.

Right now, I am selecting the relationship from
Fact.PrimaryWorkItemTable, but it seems to be very slow and is
affecting the performance of the application. Is there any way other
than this to get the relationship between Epics and the Stories ?

Please do help.

Thanks
Padmakumar.

[Joel]

Hi Padmakumar,
The Fact.PrimaryWorkitemTable as well as the Fact.Workitem table
have a foreign key relationship to Dim.Epic. Have you tried this
route? If you are still having troubles, please post your query and I
would be more than happy to look at it.

Best,
Joel

[Padmakumar]

Hi Joel,

This is the query I am working with. Since Fact.PrimaryWorkItem table
contains large amount of data, the query is becoming very slow.

SELECT DISTINCT E.EpicName, F.EpicKey, F.PrimaryWorkItemKey
FROM Fact.PrimaryWorkItem F, Dim.Epic E
WHERE F.EpicKey = E.EpicKey
AND F.AssetTypeKey = 0
AND F.PrimaryAssetStateKey <> 255
AND F.PrimaryAssetStateKey <> 192
AND F.PrimaryAssetStateKey <> -1

Thanks
Padmakumar

[Joel]

Hi Padmakumar,
Yep, Fact.PrimaryWorkitem table contains a large amount of data.
This is because it has a record for each story / defect in the V1
system for every day. What you will want to do is narrow down this
set to just be the most current snapshot in the system. You could do
this by using max(F.DateKey) I believe, or if you want a more usable
query that can be run daily without issue, join in Dim.Date (F.DateKey
= Dim.Date.DateKey) and add a where clause - where Dim.Date.Date =
GETDATE();

Hope this helps.

Best,
Joel