Jim Lu

I am trying to create custom adapter using IWarehouseAdapter, it requires custom fact table that reference to exiting "Team Project" and "Work Item" Dimension table as DimUse object. When inserting new fact entry, system has to define key field value for each DimUse field. For "Team Project" the key value is projectUri, what is the key value that should be inserted for the "Work Item" Dimension The warehouseschema.xml gives some information but it is not very helpful. Is there any other document I can reference to



Re: Team Foundation Server - Reporting & Warehouse What is the key field value format for existing Dimension Table

Othmane Rahmouni-MSFT

Hi Jim,

Check out the following link which has some sample code on how to achieve what you need to do: http://msdn2.microsoft.com/en-us/library/bb130310(VS.80).aspx

The answer to your question is to use:

FactEntry fe = m_IDataStore.CreateFactEntry("WorkItem");

fe["Work Item ID"] = wi.ID;

thanks,

Othmane





Re: Team Foundation Server - Reporting & Warehouse What is the key field value format for existing Dimension Table

Nick Ericson - MSFT

What does your custom adapter do What is your custom Fact

From the Config.xml (or using the WarehouseConfig) you can look at the Dimensions to find their "KeyFieldName". In the case of Team Project it is Project Uri and for Work Item it is "Work Item".

Look in the work item table to figure out its value. In this case [Work Item].[Work Item] has the value: <Work Item System Id>-<Work Item Revision>.





Re: Team Foundation Server - Reporting & Warehouse What is the key field value format for existing Dimension Table

Jim Lu

Hi Nick

We have a need to generate report base on groups and division from an external data source. The relationship between the external division and work item is equivalent to outer join. When you generate report, the external division has to be shown and related work item could be empty. I am trying to create a fact table that merges the both data so administrator won't have to deal with empty cell filtering when building such report.

The Custom Fact would be something like

<Fact>
<FriendlyName>Division owned Work Item</FriendlyName>
<Name>Division owned Work Item</Name>
<Fields>
</Fields>
<DimensionUses>
<DimensionUse>
<FriendlyUseName>Work Item</FriendlyUseName>
<UseName>Work Item</UseName>
<DimensionName>Work Item</DimensionName>
</DimensionUse>
<DimensionUse>
<FriendlyUseName>Division</FriendlyUseName>
<UseName>Division</UseName>
<DimensionName>Division</DimensionName>
</DimensionUse>
</DimensionUses>
</Fact>

You are correct about [Work Item] value, I have double check the [Work Item] table under TFSDataWarehouse, they are using a combination of [Work Item].[System Id] and [Work Item].[Revision]. I guess using [Work Item] dimension table will not help me in this case, since I only want to the latest work item. There is no reason to maintain records for all revision of work item ever created.

Another alternative may be to define [Division] DimensionUse for [Current Work Item] Fact table. But how can you set the corresponding values for [Current Work Item] fact. Can the custom DimUse value be set as any [Activate Date] or [Assigned User] dim value through Team explorer work item editor Any ideas