Forum Discussion
Building a correct data model in Power BI desktop
Hi,
When looking at the AdventureWorks 2022 database, there are plently of tables inside.
I imported all the tables for Sales in Power BI Desktop.
It looks like this:
Now, how does one build a star schema out of this?
Do you need to write custom sql statements to get all the columns for a particular dimension in one table and write seperate sql statements to get all the columns for the fact in one table and then do the joining?
Because all i have done is imported the tables as they are from the database but don't think it possible to join them up as a star schema. Am i right in assuming that when working with SQL Server, the tables you import are not ready made dimension and fact tables, you need to use columns from each table to build out your own inidividual fact and dimension tables?
What's the approach?
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
5 Replies
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_dataanalystHi mp390988
Thank you for being part of the Microsoft Fabric Community.
As highlighted by govind_021 adudani , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.
Best Regards,
Community Support Team _ C Srikanth.Hi mp390988
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!Best Regards,
Community Support Team _ C Srikanth.Hi mp390988
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
