Need Help with Data Model and Fact Tables

[Anonymous]

Hello,

I am new to Power BI but have some experience with databases and SQL.  I am setting up my first data model in Power BI and am unsure of how to structure my fact tables...

1. I have a fact table of monthly expenses by department and by equipment ID. (fields will be... dept, equipment ID, amount, date ( my query to pull my data for the model groups and summarizes transaction dates by month and assigns the last day of the month so I can build a smaller fact table).

2. I then have several cost drivers that I want to setup as either a single 2nd fact table or multiple additional fact tables. 

My question is...

Which is the better way to structure this.  The company wants to look at these expenses based on multiple cost drivers.  These cost drivers are held in unit accounts in our GL and I will extract the monthly balances and either load them all into one table and use the GL account # to differentiate them in my model.  Or I could load each driver into a separate fact table.  The fields in this table will  be dept, date, amount, and then if everything is in one table I would add the GL account that designates the driver (ex: tons produced, feet advanced, tons loaded...etc.)  Depending on the department will depend on which one or more I will use in various reports.

3. And then I will have a dimension table with department attributes and a date table.

[Anonymous]

Sorry, I had an issue with my posting above and didn't get exactly what I wanted.

My dimension table will be joined to the fact tables by the department and then date for my date table.  Attached is my first draft at a model with only one cost drivers.  From here I am not sure if I add extra fact tables for the other cost drivers or if I add the additional drivers and the account (driver name) to my current table called "tons".  In this model, "job" is "department".  Thanks.

[kentyler]

Coming to Power BI from SQL can be confounding at times, but it looks like you're doing fine. It would probably be better to add the gl account number and put all your driver information in one fact table. That way you model is 2 "stars", 2 fact tables that share 2 dimensions.

The relationship lines in Power BI really are about how the filters move when you place them, so with a star you can filter the dimensions and the relationship moves across to the fact table. Keeps it simple
Another big difference from SQL is that almost all Power BI models make use of a "Calendar table", a helper table that greatly increases your ability to filter. You can find lots of examples and free code for builgin them on the internet. The calendar would be another dimension and probably be related to both your fact tables.