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!
Answers (2)
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.
Comments:
-
Thank you so much for your reply! I've tried loading the tables into Power BI directly but I'm getting stuck relating the fields and I'm sure it's something simple I'm doing wrong.
Do you have any instruction for relating the full name in the User table to the Owner ID in the HD_TICKET table? I've tried doing both the Merge Queries and the Manage Relationship options but I don't think I'm making the right selections. - jasti 5 years ago-
Using the Merge Queries method:
In the Power Query Editor, select the HD_TICKET table.
Click the Merge Queries button.
From the HD_TICKET table, select the OWNER_ID column.
In the dropdown menu below the table, select the USER table.
From the USER table, select the ID column.
Click OK
A new column should be added to the HD_TICKET table named USER (it might be ORG1 USER) with values of "Table"
In the column header, on the right side there is a button to Expand the Table (it looks kind of like a set of weights on a bench press). Click that button.
Select the columns from the User table that you want added to the HD_TICKET table (USER_NAME, FULL_NAME, EMAIL, etc.).
The HD_TICKET table should now have the additional columns that you selected from the USER table. Note, if you want to go back later and change the columns added you can click the cog wheel next to the "Expanded ORG1 USER" in the applied steps. - chucksteel 5 years ago
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!
Comments:
-
The biggest issue I have with relationships is that you can only have one link between two tables. For instance, you are linking HD_TICKET.OWNER_ID = USER.ID so you can report on ticket owners. PowerBI will not allow you to create a link of HD_TICKET.SUBMITTER_ID to USER.ID, so you can't create a report that includes both owners and submitters. - chucksteel 5 years ago
-
Oh I agree, it's daft! Just thought I'd put the answer from a different perspective for clarity's sake. - Honkytonk 5 years ago
