I’m using WorkdayManager (Email as link) and BranchDistrictRegion (Branch Number as link) to slice my fact tables at the bottom. Now, I have a measure called BalanceLeft branched out from BeginingBalance measure (from Beginning Balance table) and Used (from Used table). And I want to show employee’s BalanceLeft by manager and by BranchDistrictRegion. I feel that my current setup will not work because I cant visualize how BranchDistrictRegion is gonnna slice Employees Name from WorkdayManager table.
Should I add an Employee table between like below as an intermediary dimension table?
If yes, then the problem becomes that I dont have a ready-to-use Employee table with both email and branch number that can slice all three fact tables. In this case, should I just create a calculated table to create a “local” employee table from Beginning Balance table since it has email and branch number?
It’s quite difficult to dream up and answer here based on the information provided. There is so much that is going on in this model and not seeing the details and also the measures that you’re using it’s very difficult to give you a good answer.
One thing I would adjust here though is these multiple layers of your lookup tables. Why can’t they just be all in one level?
And why can’t your employees table just be another lookup table just like you’ve already got? I’m not sure why you feel you need an intermediary table.
Usually an employee details table would just be a common look up table that would have relationships down to your fat tables which hold information around what employee does or is doing.
Potentially you could be looking at two layers of look up tables here. As employees have managers and also branch districts. So potentially they could be your top layer and then you have employees and dates and other things as your second layer look up tables.
If you do it this way you shouldn’t have to join the manager and branch tables to your fact tables because the filtering will go through one layer down to the next and then down ultimately to your fact tables.
Maybe just simplify this in your mind and you should see how it can be done quite easily I feel.
Your top 2 tables “Year” and “Time Off Type”
I am not sure you need. The main fact table “Beginning Balance” has both Year and Time off Type inside the fact table.
My [Year] table is filtering down to [Beginning Balance],[Used] and [Date] table because [Beginning Balance] and [Used] are at yearly level(2018,2019 etc) while my [Request] has specific date. So by selecting a specific year from [Year] table, it gives me balance and used in that year and dates of that year which then filters down to [Request] table. That’s why I put it above [Date] look up table. But yes, I can put [Type] on the same level at [Date] table.
Essentially, I want to slice this report by Managers and Branches. I definitely need them as look up tables. The thing is my customers may want to drill down to employee level for a specific manager/branch and the problem is that I don’t have an employee table that has both manager and branch data in it.
[Manager] table is an employee table (which does not have branch info) joined on itself to get manager info.
That’s why I was wondering, leaving the model the way it is, if I want to create a visual to show used vacation hours by branch and then drill down to employees(using employee field from Manager table), is it a correct way to do it? I cant visualize how this can bucket the employees from [Manager] table into the right branch since there is relationship between [Manager] and [Branch] tables.
Not sure if you have gone through this course yet but I highly recommend it as it contains all the best practices I have around setting up your models effectively. Especially the modelling part of the course module.
It covers in detail what I mentioned above. You should be able to simplify down what you are doing here a bit more that what you have at the moment.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
