Orphaned Relationship CiresonHardwareAsset RelatesToOwnedByUserFactvw

Ben_Holt

Hi

I have some custom views and reporting that I have created on my data warehouse database server. I happened to be checking some data today and noticed that I have some hardware assets with a relationship to the owned by user (aka Custodian) in the CiresonHardwareAssetRelatesToOwnedByUserFactvw view that hadn't been marked with a deletedate. We ended up changing the Custodian on an asset with the problem, and it marked the custodian before our change with a delete date for the relationship. It also created a new relationship entry for that device to the new custodian. Although, we still have an original custodian to device relationship that doesn't have a delete date set.

I have created some custom SQL views with joins that use that Cireson Hardware Asset to Custodian relationship to get the current custodian, and filter the relationship data so that only relationship information without a delete date are used. Having multiple relationship entries for an asset are creating multiple entries for a single asset in my custom view. Is there a recommended way to fix this issue, or could there be something wrong with a grooming process? Would there be any issue adding a delete date to the stale entry in the CiresonHardwareAssetRelatesToOwnedByUserFactvw view?

Thanks
Ben

Chris_Chekaluk1

I'm not sure this is exactly the same thing or not... but definitely seems similar & maybe related.  A long, long time ago (SCSM 2010 actually), we had an issue with deleteddate & grooming.  At that time, what happened was that ungroomed facts would have the expected DELETEDDATE value- active relationships had DELETEDDATE = null, where inactive relationship (such as those removed by user) had a date value in the DELETEDDATE field.

The problem we had back then was that grooming came along & guess what... marked Dimension records as being groomed (as expected) and when *that* happened...  the DELETEDDATE field in the FACT also was updated.  :-(

That meant that our reports lost the current relationship (AffectedUser, ResolvedByUser, etc.) for groomed records & then dropped out of our reports.  This issue was logged as a bug w/ MS at the time & my *assumption* is that they have fixed it somehow in the past 7 or 8 years.  I just haven't spent much time with DW reports since then and am not sure what the behavior is anymore.

At that time, our reports had to process each fact differently based on if the dimension record was groomed or not... it was pretty ugly.