I am working with employee data over multiple years. We import QuickBooks data using QQube and the Time table has 70 employees reporting anywhere from 3 to 12 labor codes a day with fields: Date, Employee, Duration (in Minutes), and Labor Code. We have reporting needs which includes employee data such as Rates, Teams, Locations, Departments etc. that change over time.
For Example, John will start by working for team A at $50.00, in Washington in the Research department and three months later he's switched to lead Team B, in New York's, Admin Department making $60.00. He leaves the company, comes back a year later now working for the Consulting department in team C in Florida making $75.00 an hour. Departments, Teams and locations are constant attributes included in our reporting.
When these changes only occurred on a regular, yearly, basis I started off with a design using an Employee Master file with about 13 columns for each year (There are other attributes) times multiple years so five years was 65 columns and growing. I knew that I would only work for me for a while, but it got me started.
The three ways I have considered track the data would be: 1) create and weekly append to a new fact table with all the “current” data, 2) create an Entity–attribute–value table that includes the date of the attribute change, the attribute, and the new attribute value and calculate the attribute values while reporting, Yikes, or 3) a combination of the two where I add calculated fields for the attributes to the existing Time table and calculate the attribute value using the Entity–attribute–value table. I would hate to think how long it would take to update the calculated fields during update using this approach.
I have looked at a lot of web postings and found no help. If the new fact table is the answer, then I need to create a process for appending to it in PowerPivot if possible as I would prefer to not create a process outside of PowerPivot when I already have the current data imported. Making this as seamless as possible is obviously one of my goals. I’m open to suggestions, that’s why I’m reaching out. Even pointing me to relevant articles or webinars would be helpful if they were inclusive enough. Thanks for your guidance.
