Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rabers
Regular Visitor

import and redesign data scheme

hi,

i want to get started with Power BI and i wonder,

for beginning, let's assume that I have the following tables:

1. SalesOrderHeader

2. SalesOrderDetail ((Linked to SalesOrderHeader by OrderID))

3. SalesPerson (Linked to SalesOrderHeader by EmployeeID)

4. Customer (Linked to SalesOrderHeader by CustomerID)

I wish to import those tables and create an ERD at the Power BI by joining the two SalesOrder Tables togather (have a newly created table and delete the two table that created it) as my center FACT table and leave the other two tables (SalesPerson and Customer) as DIM tables connected to the main FACT table by Join relation (a mini star-scheme ERD with 1 FACT and two DIMs).

can someone please explain how it's done?

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @rabers,

 

In your scenario, please follow steps below to achieve your requirement:

 

1. Connect to those four tables, then open Query Editor.
2. Merge table SalesOrderHeader and SalesOrderDetail to a new query, then rename the new table as ERD.

 

a1.PNG

 

a2.PNGa3.PNGa4.PNG

 

3. Hide those two tables: SalesOrderHeader and SalesOrderDetail. As table ERD reference those tables, we can’t deleted them.

 

a6.PNG


4. Build relationship between ERD, SalesPerson and Customer tables.

a7.PNG

 

Reference:
Common query tasks in Power BI Desktop

Shape and combine data in Power BI Desktop

Create and manage relationships in Power BI Desktop

 

If you have any question, please feel free to ask.

 

Best regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi Qiuyun,

 

Thanks for your quick and (very)helpful reply.

I did all that.

Now I have one FACT table (Orders) with two dimensional tables (SalesPersons and Customers).

The two tables which were merged into one FACT tables are hidden now.

Now, I'm facing another issue which is related to the way PBI works, I guess.

Let's assume that in the FACT table ~125k order items which belongs to ~31k OrderID.

When I create a bar chart with SalesPersonID as axis (dimension) and count distinct SalesOrders as value (metric) I get the same value (~31k) of SalesOrders per each SalesPersonID, where, in fact, there is a variance between each salesperson....

(for example SalesPersons A has 1000 orders, SalesPersons B has 3500 orders, etc.)

What am I missing here?

I noticed that the link between the newly merged table (Orders) and SalesPerson is weird, don't know why...

and one last thing, when tables were hidden - their relations didn't disappear automatically, shouldn't they?

 

 

Capture.PNGCapture1.PNG

Hi @rabers,

 


Let's assume that in the FACT table ~125k order items which belongs to ~31k OrderID.

When I create a bar chart with SalesPersonID as axis (dimension) and count distinct SalesOrders as value (metric) I get the same value (~31k) of SalesOrders per each SalesPersonID, where, in fact, there is a variance between each salesperson....

(for example SalesPersons A has 1000 orders, SalesPersons B has 3500 orders, etc.)

What am I missing here?


 

Would you please share more information, as you didn't mentioned SalesPersonID in your previous post? It would be better if you could share .pbix file for our analysis.

 


I noticed that the link between the newly merged table (Orders) and SalesPerson is weird, don't know why...

and one last thing, when tables were hidden - their relations didn't disappear automatically, shouldn't they?

 

As mentioned in my original post, you can manage the relationship manually. By default, when we hide the tables, relationship of hide tables will not be affected.

 

Best Regards,
Qiuyun Yu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft

 

can you, please, check out what i've posted?

what's wrong with that?

i've read what you posted but still it's not working for me.

also,

when creating a newly merged table and hiding the original tables they just become grey BUT relations still exists at the ERD, is that the way it should be?

Hi @rabers,

 

Would you please share .pbix file for our analysis?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.