Forum Discussion

rabers's avatar
rabers
Regular Visitor
9 years ago

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?

Need Help
Tutorial Requests

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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.

     

     

     

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

     


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

     

    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

    • rabers's avatar
      rabers
      Regular Visitor
      9 years ago

      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?

       

       

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        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