Some tables are just a list of lookup values. They hold no further information, so instead of making them dimensions we combine them into the base table. Here the linked values in the Customer Categories, Buying Groups and Delivery Methods tables will be substituted in the Customer table for their foreign key values. The Customer table will then hold those values directly and no longer need the links to the lookup tables.
Next we build a “Geography” dimension that we can attach to a city id and that will let us filter by city, state, country and continent.
And then we add our “Customer” dimension and our “Geography” dimension to our fact tables.
We have added the redundant City Id of the customers to both the Orders and Invoices fact tables. We can then link our Geography dimension to orders and to invoices. Filters flow from the 1 to the many in Power BI so if we filter by State we will get both Orders by state and Invoices by State. If we filter by Customer we will be all the Orders for that customer and all the invoices for that customer.
Last but not least, we add a calendar table.
We end up with a double star. Orders and invoices have (sometimes) different dates. They make up 2 different fact tables.
The 2 fact tables share 3 dimension tables, a geograpy table of cities/states/etc and a customer table and a calendar table. The calendar table is related to the order date for the orders and the invoice date for the invoices, so any filter placed on it will filter both fact tables at the same time.
Our next step will be to give our tables and their fields business user friendly names.