No natural key

[DingoCC]

Hi All,
Type 1 SCD contains what I'm calling an holistic help desk ticket, which is an amalgam of a user's problem as it's passed from one helpdesk application to another and so (max of four helpdesk application's in use here).

My problem is that the loading of the dim and fact each day is too complex as I have no natural key. I want to use the concat of the ID's from each system but that changes as the user's problem is passed along. This solution is complex and prone to issues yet it's all I can propose:

1. Load new dim record (this would be when a ticket is passed from system A to system B)
2. Delete old dim record (when the ticket was only in A)(also as it could be late arriving)
3. Update existing fact records due to FK changing from step 1&2
4. Insert new fact records

The fact table is a weekly snapshot. Also the four ticket systems are in a matrix so that for example system D can pass to system C and visa-versa. This prevents me from building logic that applies precedence that I could have used to better handle steps 1 & 2 above. I have no need for the ticket dimension to be a type 2.

Is summary: How best to ETL with no natural key?

Colin.

[ngalemmo]

You mentioned each system assigns an ID to the ticket, so you do have a natural key. It's just that its not always the same one. What you do have is four sources for similar information. The issue becomes relating the dimension rows, not so much a modeling issue.

What are the facts you are trying to build? It is the intent to have ticket as one of the dimensions?

[DingoCC]

Yes, relating the dimensional rows is the issue and yes Ticket is a dimension. There is a User dimension along with three role playing date dimensions. Facts are three measure of ticket age and a bit for Open/Closed plus a bunch of degenerates for the source system RowID from the first layer of ETL.

[ngalemmo]

Unless you can connect the ticket identifiers from the different sources, all you can do is record what you know about the individual action. If you can connect them, then store each ticket from each source as a separate row with attributes to assign a common identifier and common attributes once a relationship has been identified. This avoids having to re-key facts. Also break up information about the ticket itself into other dimensions, such as who handled it, when, type of problem, resolution action and so forth.

If you can't connect them, then what you have is what you have. The data would still be useful, just a little less precise.

[Sponsored content]