locked
Indexing in Power BI RRS feed

  • Question

  • Kindly suggest if its possible to create index on tables in power BI after importing it. I am relating it to any DB, like we create index on table columns in DB which help improving select where col1 = val1 and col2 = val2 or join kind of query. Similarly does it work the same way in power BI as well because after importing tables with data power BI is also like a DB.

    If Yes, how will we create INDEX on power BI table columns (especially index on multiple columns)

    Will that improve performance of DAX formula or other joins in model.

    Thanks a lot.

    Saturday, July 4, 2020 2:09 AM

Answers

All replies

  • When you import a table in Power BI, every column is compressed and already "indexed".

    You can find more details about this process finding material about VertiPaq (we have several chapters on The Definitive Guide to DAX and many articles and videos on SQLBI.

    However, because you are writing this message on Power Query section of Power BI forums, it's important to understand that the data is compressed and stored in VertiPaq after you complete the Power Query transformation, that is managed by either the data source engine (like SQL Server) or the M engine (using temporary uncompressed tables in Power BI). Therefore, Power Query does not benefit of the VertiPaq engine, because it cannot read data stored in that format.


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn DAX!




    Saturday, July 4, 2020 7:06 AM
  • Hi Macro, thanks a lot for your response so if we create a table or perform transformation in power query, will it not get advantage of indexes present on power BI table for performance improvement?
    Sunday, July 5, 2020 5:38 PM
  • Correct - as I said "Therefore, Power Query does not benefit of the VertiPaq engine, because it cannot read data stored in that format."

    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn DAX!




    Sunday, July 5, 2020 8:17 PM
  • Hi Macro, Thanks a lot for clearing my doubts and providing in depth knowledge of how things work at backend. Just one last confirmation I need - is DAX using those indexes created by power BI and it gets benefits of indexes for speed improvement? My understanding is that in Power BI sequence of execution is - first all commands of Power Query are executed which does work of importing data and transforming tables as required, after that all DAX formula will be executed to calculate measures, columns etc. Finally visualizations are created. DAX works on VertiPaq engine which is also called xVelocity engine.

    Also there is no mechanism in power BI to create index with any commands, its all done only automatically at backend?

    Tuesday, July 7, 2020 2:25 AM
  • Basically, the description is correct. The only correction is about terminology. What you call "index" is the natural way to store data in memory, whereas an index is usually a copy of the data created for performance reasons. You do not have two copies of the same data in memory.

    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn DAX!




    Tuesday, July 7, 2020 5:22 AM