I was tasked with creating some reports from Service Manager using the cubes.  My boss wanted a report that showed all active tickets, showing from high to low the analyst, then the category, then the user.  

I used the following Fields:

IncidentStatusValue in the Column Label

IncidentDimCount in the Values

AssignedToUserDim.DisplayName, IncidentClassificationValue, and AffectedUserDim.DisplayName in the Row Labels.

The report looks exactly as I want it to.  

The problem is that the data is wrong on several levels.

Issue 1:  It shows multiple entries for the AssignedToUserDim.DisplayName 



I changed the names, but if I click on the number 1 next to Joe User(Affected User) for either Analyst1 or 2 a tab open with IR48005.  Basically the two analyst both show an active ticket assigned to them, when only one of them should.

Issue 2: The Grand Total that is calculated by the PivotTable is incorrect.  I copy and paste the same numbers into the cells next to the PivotTable and the total is something completely different. 

The weird thing about this also is that when I did this 20 minutes ago before writing this post, the Autocalc showed 130.  

The first issue is more important than the second issue.  It is not just this report and I have tried using many different values only to get the same duplication.  Its almost like the DataWarehouse is not actually deleting the previous AssignedTo user.  The history in the actual ticket shows that it should be, but the reports show something different.  

This then throws off every report I try to make.  I made one for totals tickets per analyst and I noticed that I got credit for closed tickets that I had previously reassigned, as did the person who the ticket was reassigned to.  

Can anyone offer any suggestions?

Thanks.

Matt 

Have other forum members experienced this issue?  If so, is there a workaround or a fix for the above issue?

Have not heard from anyone.  No one is experiencing this issue or is no one using the cubes to create these types of reports?

Hi Matt,

We have the exact same issue :( I suppose the AssignedToUserDim takes the History into consideration and thus we see all user which ever were assigned to a particular work item and not just the current. Not sure if that's per design or a bug though - it could be both actually...

Will see if there's another way to get only the current assigned to user. Will let you know when I find something.

BR
Alex

Huzzah!  Someone else has the issue.  Thanks for the validation.  Let us know what you find.  I cannot seem to find anything online regarding this.  

Thanks.  

I am having exactly the same issue.  Have you found a solution?

Unfortunately no. But I think I found a pattern in regards to this: it seems the current assigned to user is always the one listed the last.

I guess this is because the stored procedure behind the AssignedToUserDim doesn't filter if the relationship is still active. This is quite easy to "reproduce" via PowerShell.

Lets say you have an IR with ID IR1234 which has been re-assigned multiple times.

If you run this small query now in PowerShell you'll see that all the AssignedToUser relationships still exists, but only one of them has the property "IsDeleted" set to False.

Import-Module SMlets

$IRO = Get-SCSMObject -Class (Get-SCSMClass System.WorkItem.Incident$) -Filter "Id -eq IR1234"
Get-SCSMRelationshipObject -BySource $IRO | where {$_.RelationshipID -eq "15e577a3-6bf9-6713-4eac-ba5a5b7c4722"}

So, in the DB those AssignedToUser relationships (actually it applies to all relationships) won't be "overwritten" or the old one deleted as soon as you re-assign an incident for example. Instead, new relationshipobjects will be generated and the old ones changes the "IsDeleted" attribute to True.

Perhaps it would be possible to create a new Dim for the Data Warehouse, re-use the stored procedure for the AssignedToUserDim and modify it slightly to filter only for the active AssignedToUser relationship. Then you could use your newly created dim to display the current Assigned To User. I haven't tested this though, probably it's more complex than I assume :)

Cheers

Alex

In reporting via DWDataMart you would filter by the IsDeleted column.  IsDeleted=False would return the newest row from the fact table.  Perhaps this can be used in the cube as well?