Service Desk Reports Using Microsoft Power BI
I'm trying to create some dynamic Service Desk reports using Power BI but I'm getting stuck when it comes to relating Owner ID and Submitter ID back to an actual name. IN the HD_TICKET table I have almost all of the information I need to create the graphs I want, however, I am only presented with an "ID" field with numbers rather than actual names of employees.
Any idea how to relate these tables or otherwise get the numbers to generate actual names for my reports? Thanks in advance!
User information is stored in the USER table. If you are collecting your data from the appliance via a SQL statement, then you need to join to the USER table based on the ID values in the HD_TICKET table. For example:
LEFT JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
This creates a join to the user table with an alias of OWNER, so in the select statement above you can select OWNER.FULL_NAME, etc.
If you are loading the tables into Power BI directly, then load the USER table and you can either create relationships, or create custom columns using the Merge Queries wizard in the Query Editor. I prefer the second method, mostly because I find the relationships that Power BI creates to be very finicky.
Chuck's assertion that Relationships are finicky is correct - They are! But as an alternative answer to Chuck's (in case anyone bumps into this in future), here's an example of how I map the Relationships inside PowerBI's Relationship manager rather than via a query. It's pretty simple, but is less 'clean' than only selecting the data you want via a query at the start;
'All Tickets' is a custom PowerBI table I've created with a query that combines Archive and live tickets, so don't expect to see that in your K1000!