Building a correct data model in Power BI desktop

hi mp390988 ,

you could create a star schema based on the tables provided depending on the level of detail required, which may require building your own keys.

most of the tables here should have a unique identifier. 

Assuming you omit the sales order header or join the sales order header on the sales order detail table and if you delete all existing relationships and create the ones below:

1. Dimensions : SalesTerritory (Territory ID Key), SalesPerson (CustomerID Key) , SalesStore (Store ID Key)

2. Fact: Sales Order Header / Sales Order Detail

3. One to many (from dimensions to fact)

For reference:

 How to setup a Star Schema Data Model in Power BI - Easy guide

Database Normalization – Normal Forms 1nf 2nf 3nf Table Examples

Hi mp390988 ,

When working with an OLTP system (transactional database), data is not structured in a star schema. You need to manually build it by extracting relevant data into fact and dimension tables. For example:

• Fact Table: Extract transactional data like sales amounts, dates, and IDs (e.g., CustomerID, ProductID).
• Dimension Table: Pull descriptive attributes for analysis, such as customer names and locations, or product categories and prices. You then establish relationships between the tables (e.g., CustomerID in both tables) to form the star schema.

On the other hand, if you're working with a data warehouse (OLAP system), the data is typically pre-organized in a star schema. For example, sales fact tables are already linked to dimensions like customers and products, designed for analysis. Here, you don’t need to create dimensions and facts—it’s ready for reporting in tools like Power BI. This saves effort and ensures optimized performance for analytics.
Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst