Home Forums Power Pivot Modeling Direction – Multiple Fact tables

This topic contains 5 replies, has 2 voices, and was last updated by  tomallan 5 months, 4 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #10023

    QuadGuy
    Participant
    • Started: 2
    • Replies: 3
    • Total: 5

    I am trying to build a data model from an Access database and ended up with 2 fact tables and many Dim tables. I know that this doesn’t make for a nice Star schema, but I just can’t seem to see a clear path to converting this model into a true Star schema model. Following along with the example in chapter 17 of your book, I came up with a schema that looks a lot like the example (see attachment). But not sure if it is correct…


    The project is about JOBS and BIDS.

    Here is what this is all about –

    • Each JOB can be put out for multiple BIDS with each BID going to a different GEN CONTRACTOR.
    • Each JOB can have a Status of Awarded, Lost, or Open
    • Each JOB may be awarded to only one GEN CONTRACTOR
    • Each Awarded JOB has a Contract Price that is probably different than the BID AMT

    I’m looking to report on the typical stuff you would expect from this kind of data:

    1. how many BIDS and BID $$$ did an Estimator produce per unit of time
    2. Contract $$$ for Awarded Jobs by Estimator per unit of time
    3. how many BIDS and BID $$$ did a Gen Contractor accept per unit of time
    4. Estimator ‘Win’ rate per unit of time (# of Bids sent out/number of Bids Awarded)
    5. Gen Contractor ‘Accepted’ rate per unit of time (number of Bids Accepted / # of Bids sent out)
    6. many others to be determined later…

    I built a very simple flat model to allow me to play around with the ways i might visualize this info in real life. The Power View screen shots are attached to help clarify my intent.

     

    I’m not looking for help with the measures so much as I am looking for guidance on the data model itself. I’ll toil over the calculations once I know I’m not driving into a brick wall.

    Any help is much appreciated.

    Thanks,

    Rich P

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

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Rich,

    Please tell me a little about the reporting perspective in this data model between jobs and bids:

    Does your business (1) award the jobs or (2) compete for the jobs or (3) analyze a market where others award jobs or compete for the work or (4) something else?

    #10044

    QuadGuy
    Participant
    • Started: 2
    • Replies: 3
    • Total: 5

    Hi Tom – Thanks for replying. I’m not sure I understand your question, but I will try to expalin the application:

    This is a sheetrocking company. They work with a handful of big construction companies (Gen Contractors {GCs}) that bid on large-scale commercial projects.

     

    Before the GCs can submit a bid to theeir client, they need to have all the bids from their subcontractors (sheetrock, plumbing, electrical, etc). Multiple GCs may ask for bids for sheetrocking for the same project (i.e. a hospital, dorm, etc.).

    The reports we want to produce would allow the sheetrocking Mgmt team to assess how their estimators are performing, and also how their GCs are treating them.

    For Estimators, it would be good to know how many Bids they send out per unit of time, and how many are going to each GC, how many dollars of business did they bid, how many bids were accepted, and what was the Job contract amount.

    For GCs, it woudl be good to know how many bids were submitted to each GC per unit of time, and how many of those bids were accepted so we can figure out the ‘win’ rate by GC and by Estimator.

    Make sense?

    I have been plugging along on this and have revised the model once again and it seems to be working out. I’m struggling with the Measures, but that’s a necessary part of the learning process, I know.  Here is what I have reduced it to. I would appreciate any thoughts on whether I went in the right direction or not.

     

    Thanks,

     

    Rich P

     

     

     

    #10045

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Rich,

    Years ago I worked for a general contractor, so its interesting to see the other side of the business.

    A few questions:

    I assume the award date is when the a bid was awarded to a general contractor, but not necessarily awarded to your company. Is this assumption correct?

    If your company is awarded a contract, a job number is entered. Is this assumption correct?

    Is the Bid ID created internally to your company, or is it the “request for bid” number generated by the general contractor or end-customer?

    What indicates in the FactBids table that you are submitting your bid through multiple general contractors for the same work?

    Is Bids per Job a success ratio (how many bids we made/how many jobs we were awarded) or is it measuring something else?

    There are two relationships between calendar and the bids table. Is the active relationship using bid date or award date?

    Tom

    #10046

    QuadGuy
    Participant
    • Started: 2
    • Replies: 3
    • Total: 5

    Actually, there is a JOBS table they enter a record in first. This is where everything starts.
    Then they have multiple BIDS tied to that JOBID, with each BID going to a different GC.

    The award date is a fact about the JOB. Period.
    It only gets filled in if the drywall company won the bid for one of the GCs

    If your company is awarded a contract, a job number is entered. Is this assumption correct?
        – No. JOBS are the main topic of the database.

    Is the Bid ID created internally to your company, or is it the “request for bid” number generated by the general contractor or end-customer?
    -The BID ID is an auto-generated primary key field

    What indicates in the FactBids table that you are submitting your bid through multiple general contractors for the same work?
    -The table is denormalized.

    In this data, you can see that there are 3 BIDS for 1 JOB. This also indicates that the JOB was awarded on 12/30/15. And it was awarded to GC ID 8 for a contract amount of $115,900

    Is Bids per Job a success ratio (how many bids we made/how many jobs we were awarded) or is it measuring something else?  
    -It could contribute to a number of metrics about the Estimator/Company:

    • [Avg Bids per Job] compared to the Win Rate of the Estimator (does more bidding result in more wins?)
    • Across the company, what is the average [Bids per Job] and how is each estimator stacking up against that number?

    There are two relationships between calendar and the bids table. Is the active relationship using bid date or award date?

    The active relationship is on the BidDate. But I use the AwardDate relationship to report on Contract wins and $$$ per unit of time.

     

    I take it that my images of the dashboard mock up I provided weren’t as helpful as I thought they would be? Thanks for working through this with me.

    Rich

    #10047

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Rich,

    The images are helpful. I also ask questions because they help me get my internal picture right.

    I think the last model with one fact table is much better than the first with multiple fact tables. Multiple fact tables can imply there is a many-to-many relationship between the different facts (which is not the case here).

    Not sure what happened to the images, but most no longer appear.

    If your dashboard will consist of pivot tables and pivot charts, many of your measures will probably not be necessary because of “filter context” will give one formula many meanings (depends on the context used).

    Also, the award date probably belongs in the jobs table (one job most likely has one award date). Additionally, many of the date-related fields like month and year do not belong in your fact table, but rather in the calendar table.

    If you would like to continue to discuss, let me know. You have an interesting application.

    Tom

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.