Home Forums Power Pivot Multiple business entities and how to "roll them up"

This topic contains 8 replies, has 2 voices, and was last updated by  tomallan 3 years, 1 month ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #4342

    kpfeif
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    This is my first forum post…I’m now on the business side, but I was in IT for many years.  I’m having a hard time NOT thinking in terms of the RDBMS model.  I’m also a longtime Tableau user, so sometimes I think about what works in there, too.

    I’m having a really difficult time putting the data together right.  Before I started with Power Query and Power Pivot, I had the megatable, complete with data at different granularities within it.  Total disaster.  I know Power Pivot can do this…I just need some help with how I should look at this.

    I’ll try to summarize this as well as I can.

    I have customers.  I keep track of their addresses and other info.  I have monthly snapshots of the customer.

    I have salespeople.  They, too, can change – address, names, region, etc.  I have monthly snapshots of them, too.

    So far, so good.  Here comes the part I have a problem with.

    We sell 2 different types of “things” to our customers – Products and Services.

    I have a product-sales table.  There’s a row for every product order, by salesperson.  Cost, etc., is included.

    I have TWO service-sales tables.  Why?  There are two different kinds of services – “Fixed” and “Variable”, each with their own table.  Each service type has different types of data, but both can be viewed in common ways…such as no matter what type of service, there’s still a salesperson, and a dollar amount.

    All of these info – salespeople, customer, products, services (both type) – all have dates connected to them.  We want to look at the data at the month-level.

    The problem here is that I want to look at monthly sales in a consolidated way – i want to see dollars per salesperson, dollars per regions, etc., but I may want to look at dollars per service type, dollars for products, etc.   Each of types of things we sell – products, fixed services, and variable services, has unique details about them – they’re not common.  So, I can’t simply merge all of them together.  I may want to know, for instance, detail on the product types we’ve sold in certain regions.

    I think I’m doing a terrible job at explaining this.

    I think I know what this should look like if it were a RDBMS.  Power Pivot should be easier, but hey, there are still challenges I need to overcome.

    How do I break this problem up and move forward?  Any suggestions?

     

    BTW – really looking forward to the Indy class coming up!

     

    • Kris

     

    #4343

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Hi Kris,

    May not be as hard as you think, looks like you will have a model with 3 fact table and shared dimensions for calendar, customers, regions, sales people.

    Do not worry about physically merging products, fixed services and variable services. I believe you can virtually merge them on the same pivot because they share a common set of lookups.

    For starters, if you can put together some sample anonymized data: 3 separate fact tables (product sales, fixed service sales and variable service sales, each with their own separate date and amount columns), plus lookup tables for shared dimensions such as customers, regions and sales people, I can throw in a calendar table.

    I am sure that the separate fact tables may have their own individual lookup tables (such as products for product sales), but initially, let’s not worry about them. Once you see how this solution works (a technique learned from Rob), you will be able to take it from there and add the tables on your own without any worries.

    Looking forward to the sample data,

    Tom

    #4344

    kpfeif
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

     

    Thanks!  I’ll put together something shortly.  I think the results of this will be a huge “ah ha” moment for me…like the time many, many years ago that all of a sudden, object-based programming clicked and made total sense to me.

     

    #4345

    kpfeif
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

     

    Ok, I think I have some example data that should describe the problem I’m facing.  I totally made up the data, and there’s not a lot of it, but I think it’ll work.  I changed things around, too, to simplify it a bit.

    Each tab explained…

    RegularWorkers – every month, we get a snapshot of our current employee workforce.  There’s some descriptive info for each employee.  Note that this data can change each month.  There’s one record per employee, per month.  The timesheets system doesn’t share the key with the employee data system, so we need to have the primary key of the timesheet system as a foreign key on the employee data table ( TimeClockID).  Also, some employees never charge time using the timesheet system – they are fixed overhead (thus, Fixed = “Y”).   For each fixed employee, the average full-time-equivalent for them is listed (1=40 hours/week.  0.5 = 20 hours/week).  Each employee is assigned to an department.  Sometimes the employee changes departments.

    RegularWorkersTransfers – Sometimes employees change jobs internally.  Any employee that transfers out of their current department is on this table.

    NewWorkers – there’s a row for each worker that joins the company.

    Terminations – if an employee leaves, he/she is on this table.

    Departments – this is the list of departments within the company.

    JobInfo – These are the different jobs that the company is currently working on.  Each Job has a single department in charge of it.

    FixedCharges – Sometimes we have an external company do work for one of the jobs we’re working on.  All we get is the job its charged to, the date, the amount, and the external company used.

    TimeWork – This is where I’d like the magic to happen :).  Two types of workers are listed – employees (we have info on them in the RegularWorkers table) and Temps, which we have little info, other than the name of the temp employee provider (in “WorkerSource” column).  Each worker charges a JobID, for a certain number of hours, at a certain rate.  Sometimes those rates are different for the same employee and the same job.

     

    The trick is I want to bring everything together and be able to ask questions.  Part of this process would mean to summarize both the TimeWork and the FixedCharges table together, as that’s the “total picture” of what we charged to a Job.  Other things we’d like to ask…

    For each job, what was the percentage of Employee Charges, Temp Employee Charges, and Fixed Charges?

    For a given job, how many employees that worked on that job left the company or transferred?

    For a given department, what was the ratio or number of Employee, Temps, and Fixed Charges?

    How did these numbers change by month?

    How many hours were charged to each job by Temp workers, by WorkerSource?

    What’s the average hourly rate by each WorkerSource, by month?

    Ok, I hope I’m clear enough with this…if something doesn’t make sense, it’s probably because I screwed up the data.

     

    Thanks again!  I’m sure I’ll have an epiphany soon.

     

    Attachments:
    You must be logged in to view attached files.
    #4347

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Kris,

    Are you using Excel 2010, 2013 or 2016?

    Tom

    #4350

    kpfeif
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

     

    Hi, we’re running Excel 2013, 64-bit.

     

     

    #4361

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Kris,

    Request for clarifications:

    In the table for fixed charges, there is no explicit column for department. Can the table inherit the department from JobInfo?

    In JobInfo there is one snapshot date, yet the jobs are listed twice. Can I ignore the second listing?

    In JobInfo, Job AA-134-02 is named Better Mousetrap Research and then later the same job number appears to be re-named to Even Better Mousetrap Design and given a different department-in-charge, which conflicts with the table description that “Each Job has a single department in charge of it.” Should this listing be ignored or should the job number be changed or is it possible that jobs can change departments and change names?

    Tom

    #4366

    kpfeif
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

     

    Ah sorry.   My fault.

     

    1. Yes, the table can inherit the dept from Job Info.
    2. Ignore the second – that was my screw up.
    3. Indeed, the job names/dept in charge can change.

    Thanks!

     

     

    #4385

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Kris,

    Attached workbook for you to evaluate.

    Has three data (fact) tables: EmpWork, TempWork and FixedCharges.

    Also added a basic calendar table that consist of rows for year-months.

    Since job names and job departments in charge can change from month to month, the basis of relationships between jobs and EmpWork, TempWork and FixedCharges became job id + year-month.

    Although there are three fact tables, measures from each fact table can co-exist in the same pivot because power pivot only considers the filter context for each individual cell when evaluating formulas. So when shared lookup table columns (like those in calendar or job tables) make up the filter context, the measures from different fact tables can peacefully co-exist side by side.

    Also used Power Query to help shape the various tables (such as separating RegWorkers into EmpWork and TempWork).

    Tom

    Attachments:
    You must be logged in to view attached files.
Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.