ServiceNow Integration to EDW - Data table relationship using PowerBI

[Kristoff15]

We have integrated ServiceNow data into our EDW and is accessed through SQL Server. We are to create visualizations using PowerBI while using the data tables we have in SQL Server. I have imported the tables into PowerBI but are having issues with the table relationships. Example the Incident table has a column/field named assigned_to_value but looking at the sys_user table, there isn't a field named assigned_to_value in order for me to connect the tables. I tried checking the schema map in ServiceNow but could not locate to which table I can find the assigned_to_value field. Similarly, assignment_group_value is not present in sys_user_group table. Any suggestions would be highly appreciated. Thanks.

[Marshall Parker · Accepted solution]

The value in a Reference field in ServiceNow typically contains the sys_id of the record from the underlying referenced table.

If you use the Dictionary to look for the table & field you are concerned with, you will see where the Reference points to.

For example, in an OOB instance the Incident table has a column called Assigned To that is a Reference field to the User table. If you have the raw data, you will see that assigned_to has a 32 digit string - this is the sys_id field from the sys_user table to reference to the user.

If your data has been normalized in the ETL out to EDW you may be seeing the value field [in this case the assigned_to_value may be calculated to show the display name of the user] - to connect the tables you should use the reference value via sys_id.