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 -
Some things I want to be able to analyze from this would be:
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.
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.
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?