How do I get only the most current values for fields that change frequently?

Steven_Willis

I am looking at the Hardware Asset Cube in Excel and I am trying to list the most current information. For example I am looking at assets that are currently listed as 'In Stock' and were previously 'Deployed'. When I list the Asset and the 'CiresonLocationDim', I am seeing the current value and the previous value in the listing.

Chris_Chekaluk

I have a theory..  Even though you removed one Location & added a second, the SCSM database still retains the fact that you used to have the original relationship.  It just marks a removed relationship differently than an active one.  I'd suggest that you try adding the HardwareAssetRelatesToLocation IsDeleted to your filter and use IsDeleted=0 to display only the active related Location.

Brett_Moffett

Could it be that one of the values is for the LocationDim and the Other is for the Hardware Asset Dim?
Would love to see a screen shot.

Steven_Willis

Here are a few screenshots to show you what I am seeing. One shows what I have in Excel, the second is the first item in the excel list shown in the console and the third shows the history tab for when the values were changed.

Brett_Moffett

Hi @Steven_Willis, Sorry for the long delay in replying to this post.
The report you are seeing looks very odd to me and why it would report two locations for a single item is a mystery.
This could be a bug, or it could be a coincidence of when the Cubes ran.
I would expect to see the location relationship add the new relationship and remove the old relationship in the same history (ECL) record. However, it is doing it in two different logs indicating that this is happening on two separate transactions. This is not an issue, per se, as the result is the same but it might be introducing issues with the ETL jobs and therefore the cubes when they are calculated.

If you force another run of the Cubes does this clean up these double entries?

Steven_Willis

I still see the multiple entries.

Chris_Chekaluk

I have a theory..  Even though you removed one Location & added a second, the SCSM database still retains the fact that you used to have the original relationship.  It just marks a removed relationship differently than an active one.  I'd suggest that you try adding the HardwareAssetRelatesToLocation IsDeleted to your filter and use IsDeleted=0 to display only the active related Location.