Forum Discussion

BIXL's avatar
BIXL
Resolver I
8 years ago
Solved

Performance best practice with wide tables

Hey All,

In general we know that wide tables=bad performance in Power BI models.

Say for the sake of the example that I have a 500 columns table that contains a lot of measures and some attributes keys.

I'm interested to know if in regard to performance consideration there is any difference from a one 500 columns table than a two 250 columns  tables (with 1 to 1 relationship by pk) or even ten 50 columns tables (with 1 to 1 relationship by pk).

 

If anyone has a solid knowledge about that question I would be happy to have it shared.

 

Thanks

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    BIXL

     

    Separating frequently used column and other useless columns so that it will not consume more memory when calling frequently used columns only. For the internal working of VertiPaq engine, you can refer to this article: The VertiPaq Engine in DAX 

     

    Regards,

     

     

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    BIXL

     

    Try to remove useless columns when importing data into Power BI to reduce the data load memory. In your scenario, I think you may split some of columns into dimension tables and connect to fact table on key columns. Depends on usage, you can also include frequently used columns into same table together. 

     

    Please refer to an article below:

    Data Import Best Practices in Power BI

     

    Regards,

    • BIXL's avatar
      BIXL
      Resolver I
      8 years ago

      Hi v-sihou-msft

      Thanks for your reply.

      I am aware of the best practices described bellow and in the link you provided.

      My question was in regard to the way the VertiPaq engine works.

      Since there are some unique situation where there are a lot of measures in a single table the question was if in engine performance point of view you should split them into several tables connected by the same PK. 

      could you elaborate some more about :

      "Depends on usage, you can also include frequently used columns into same table together" - Does this affect performance ? or just a more "user friendly" model, for which I'm totally on.

       

      Thanks

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        8 years ago

        BIXL

         

        Separating frequently used column and other useless columns so that it will not consume more memory when calling frequently used columns only. For the internal working of VertiPaq engine, you can refer to this article: The VertiPaq Engine in DAX 

         

        Regards,