locked
Order to import tables for relationships RRS feed

  • Question

  • Hello,

    I am trying to understand relationships in powerpivot.
    I know how relationships work in databases, but they don't seem to work the same way in powerpivot, unless there is something that I'm not understanding, which is quite possible.

    I read that the order that tables are imported into powerpivot makes a difference because of the process used by the auto detection feature in powerpivot. I don't understand why the order is important, so I'm hoping that someone can explain it to me.

    Thank you
    Tracy

     

    Thursday, November 11, 2010 6:45 PM

Answers

  • Tracy,

    If your data are from different data sources, I do not think that order is a matter. Relationship auto-detection should have no impact on multiple data sources. Did you encounter any performance issue with different orders based on your test?

    From the link that I provided, we can find the following description:
    "If you import these tables from the same database, PowerPivot can detect the relationships between the tables based on the columns that are in [brackets], and can reproduce these relationships in the PowerPivot window. For more information, see Automatic Detection and Inference of Relationships in this topic. If you import tables from multiple sources, you can manually create relationships as described in Create a Relationship Between Two Tables."

    From this description, I think that we can conclude that for relationships auto-detection, it does not work if you import data from mutiple sources. However if you import tables from the same database, PowerPivot will automatically detect them. Besides of this, currently my recommendation is that you just regard relationship auto-detection as a subsidary method. If a relation is not automatically detected, just manually create it. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, November 18, 2010 6:50 AM

All replies

  • Hi Tracy,

    What are your meanings of "import order" and "relationships" here? Did you mean that you import tables multiple times with some orders?

     This article gives a very good explaination on the relationships, http://technet.microsoft.com/en-us/library/ee835534.aspx, however I do not find any information regarding the order you mentioned.

    If you import tables from a database, for example, a SQL Server database, in the import wizard, there is a button "Add related tables", you can click it and then you do not need to worry about the order.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, November 17, 2010 10:01 AM
  • Hi Tracy,

    The order of importing tables into PowerPivot is only important for PERFORMANCE reasons. You should always try to first import the table with the largest number of rows, i.e. your FACT table. Your DIMENSION tables you should ideally only upload after you've uploaded your FACT table.

    Note however this is not a technical requirement. It's just the recommended approach for fast uploading (huge) datasets.

    (It would be nice if somebody could give a nice technical explanation of the reasons for this!)

    Cheers,

    ---Hugo

     

     

    Thursday, November 18, 2010 12:05 AM
  • Hi Tracy,

    What are your meanings of "import order" and "relationships" here? Did you mean that you import tables multiple times with some orders?

     This article gives a very good explaination on the relationships, http://technet.microsoft.com/en-us/library/ee835534.aspx, however I do not find any information regarding the order you mentioned.

    If you import tables from a database, for example, a SQL Server database, in the import wizard, there is a button "Add related tables", you can click it and then you do not need to worry about the order.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help


    Hi Charles,

    I am referring to the order that tables are imported if they are coming from different data sources. For example, is some data is coming from a spreadsheet, some tables from a database and some data from a data feed.

    If those are the data sources, does it matter which order that they are imported into a powerpivot workbook?

     I will read the link to see if it provides some clarity. I've read most of pages on that site for powerpivot, but couldn't find answers to all of my questions.

    Through trial and error, I did discover that when data is coming from multiple data sources, auto detection doesn't always create relationships like it does when all tables are imported from a single data source.

    Thank you
    Tracy

    Thursday, November 18, 2010 1:29 AM
  • Tracy,

    If your data are from different data sources, I do not think that order is a matter. Relationship auto-detection should have no impact on multiple data sources. Did you encounter any performance issue with different orders based on your test?

    From the link that I provided, we can find the following description:
    "If you import these tables from the same database, PowerPivot can detect the relationships between the tables based on the columns that are in [brackets], and can reproduce these relationships in the PowerPivot window. For more information, see Automatic Detection and Inference of Relationships in this topic. If you import tables from multiple sources, you can manually create relationships as described in Create a Relationship Between Two Tables."

    From this description, I think that we can conclude that for relationships auto-detection, it does not work if you import data from mutiple sources. However if you import tables from the same database, PowerPivot will automatically detect them. Besides of this, currently my recommendation is that you just regard relationship auto-detection as a subsidary method. If a relation is not automatically detected, just manually create it. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, November 18, 2010 6:50 AM
  • The order that tables are imported into PowerPivot seems irrelevant to me. Where did you read otherwise?
     

    HTH

    Bob

    "LadyTester" wrote in message news:83b3a5ab-8900-4be6-b82b-dc8721d738ec@communitybridge.codeplex.com...

    Hello,

    I am trying to understand relationships in powerpivot.
    I know how relationships work in databases, but they don't seem to work the same way in powerpivot, unless there is something that I'm not understanding, which is quite possible.

    I read that the order that tables are imported into powerpivot makes a difference because of the process used by the auto detection feature in powerpivot. I don't understand why the order is important, so I'm hoping that someone can explain it to me.

    Thank you
    Tracy

     

    Thursday, November 18, 2010 7:54 AM