Star Schema Model Question

[BrentHamm]

Trying to set up a model for our system and am struggling with how to best set this up.

I made a simple model to illustrate. But let’s say we have 2.5 Mil InvoiceLines, 70,000 Customers, 100 Locations.

Customers belong to a location. (They can actually deal with other locations, but let’s not worry about that for now).

Do I pull location information into Customer Dimension? It seems like a waste, since I might have 10 columns in the Location Dimension that would be duplicated in the Customer Dimension.

(My model is not showing all the columns or other tables)

Thanks!

[BrentHamm]

The locations deal with customers in their area.  And rarely do we have customers that cross over to another one.  With that being said, there is a LocationID stored in the invoice as well.  I just left that out to keep this simple.

A location might want to see how many of "it's" customters did business this year etc.  Regardless of weather or not the business was at a different location.

I am trying to stay away from Snow Flake here, but not sure how i would do that.

[BrentHamm]

So, doing it like below?  Which is what i would normally do, but was just reading about Star Models vs Snow Flake models in Power BI, and that is making me look at it in different ways.

[vanessafvg]

the way you model it really depends on what you trying to do.  I wouldn't usually put a locationid in a customer table, specifically if there was more than one location to cutomer.   I find it very rarely one needs to create a snowflake, but thats my personal opinion.  It really depends on how big your model is and also what you are wanting to do with it.

[vanessafvg]

why do you want to put the location into the customer?   dim location seems good to me.

However if you wanted to create dimension with the customer and location, you can create a mini dimension  calling it customer location storing the primary key of customer and primary key of the location with its own dimension surrogate key being stored in the fact