This is the second thread I am posting regarding problems with un-pivotting. Sorry, I’m still just not getting it. I have three “Leave” columns which I need to unpivot as shown by (1) below. But when I do unpivot, my numbers for NORMAL HOURS and NIGHT HOURS are thrown out because of the duplication, see (2) below.
I feel especially stupid here. Could someone please (once again) step me through how to fix this?
Ahhh okay. What a simpler way of thinking about things, just unpivot everything. Okay, I did this and created a conditional column where I classified each row as either “Paid Leave / Paid Work”. Got it!
I don’t mean to be difficult - but I can see I’m going to come across the scenario where I have a another column in the table which simply cannot be unpivotted. For example, if I had a dollars column in my table.
Question: How then would I be able to unpivot my three leave columns without messing my my dollar amounts?
Because your sample only includes the relevant table, I’m going to have to ask this outright, do you need to keep Leave Hours in the same table as Shift Hours?
Or, can we simplify your model by separating into two tables?
@Melissa - A layout where all the hours columns get pivotted, and the remaining 5 columns remain in tact (and Money Borrowed totals to $70.00) Thanks very much for your patience. If I can learn how to handle this scenario it is a leap forward.
@Heather, no - - there’s no reason why leave hours has to be in the same table. Neither money borrowed. I’m all for the simplest solution. I didn’t even think of that.
I’m looking for the “private message” button so I don’t have to continue to show my ignorance (I’m still working through Sam’s Learning Map). Alas I can’t find the button.
@Heather, @MelissaSplitting the query? …what do you mean? Am I able to select only the columns I want to bring in from excel? The only way I know of how I could split my table query is to bring everything in as a staging table, and then reference it - creating an Employees Worked Hours table and an Employee Leave Hours table. Is that what the both of you mean? Is there another way to “split the query” ?
@Rajesh, thank you! Yes that solution works. Can I safely assume you wrote that custom code for creating the tables? Goodness I am fumbling in the dark here. I have to work through Sam’s learning map.
@Melissa, @Heather. I’m just thinking of Sam’s 101 videos advising on the fat fact table at the bottom. And the filter tables at the top. With the “staging 'n shaping” solution, does that mean I end up having two fact tables which get connected by the index column I added? (Employee, Employee Leave Hours, Employee Worked Hours are all references to my staging table)
Not necessarily.
Because these tables share the same keys you can create relationships between your DIM and Fact Leave and between your DIM and Fact Worked. So no index or relationships between the 2 fact tables.
Filtering your DIM tables will result in filtering both Facts
There are lots of reasons you might have multiple fact tables, such as a budget and a sales table - while both are reporting on dollar values, they are in fact very different from one another.
As @Melissa has said, you can filter both tables by the fact table, the dimension tables don’t need to have a connecting relationship line between them.
a quick edit here - notice that I kept the hourly wage on each of the split tables, so you can use this to multiply the hours and get a dollar value. The reason I choose to do this is because I assumed that you would only have the current wage on the employee table. So this logic will allow you to have a change in hourly rate over time.
Hi @michellepace, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
