Simplify Data Model with 3 Fact Tables and 4 Dim Tables

[Rich_P]

I am trying to build a data model from an Access database and ended up with 3 fact tables and 4 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 model.

Here is a snapshot of my model as it starts

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 JOB can have one or more EMPLOYEES tied to it in different ROLES (i.e. PM,Estimator, etc)

Some things I want to be able to analyze from this would be:

• Tot BID $ per GEN CONTRACTOR per Year
• Number of JOBS awarded to a GEN CONTRACTOR
• Tot BID $ per year for each Estimator (using the Avg Bid Amt per JOB)

and many many more as you can imagine.

If I collapse the BIDS data into the JOBS table, then I end up with repeating data, so it seems like that isn't the way to go. Same goes for merging the EMPJOBROLES into JOBS.

In a 2nd pass at this, I moved the Estimator into the JOB table to drop the EMPJOBROLES table  and

moved the GCAWARDED to the JOBS table and that seemed to simplify things a bit.

Here is how that looks:

But I'm still not sure how to deal with the JOBS and the BIDS tables. Anybody have some solid advice to get me to the next level?

Here is a link to my Excel file in case it helps to describe a solution. This is just a bunch of fake data for simplicity.

Much appreciated.

Best,

RichP

[v-juanli-msft]

Hi @Rich_P

As I tested, it isn’t a matter to achieve something you want to be able to analyze as you list.

After you simplify the data model, create calculated columns in the “Bids” table.

Tot1 = CALCULATE(SUM(Bids[Bid Amt]),ALLEXCEPT(Bids,Bids[Gen Contractor ID]))

number = CALCULATE(COUNTROWS(Bids),ALLEXCEPT(Bids,Bids[Gen Contractor ID]))

total3 = CALCULATE(SUM(Bids[Bid Amt]),ALLEXCEPT(Jobs,Jobs[Estimator]))

Here is my pbix.

Best Regards

Maggie

[Rich_P]

Maggie - Thanks for responding. Though, I'm not sure I understand the new model you arranged.

Being new to this, I am trying to follow the 'rules' of making a Star Schema. This doesn't seem to be that shape. 

Should I be trying to reduce the Jobs and Bids to a single fact table? I'm thinking that there must be a proper 'pattern' to handle two fact tables in a One-to-Many relationship. 

Any other advice?

I have been trying some other modeling options in my real project, and tried to follow the example in Collie's/Singh's Excel User's Guide where they show Multiple Data tables. I came up with this schema, but I'm not sure it is the 'correct' model. The main idea here was to NOT joint the two fact tables, but instead, connect them through their common lookup (Dim) tables.

Anyone else have any sage advice?

Thank You.

Rich P