none
MS Access and Limitations --> Power BI? RRS feed

  • Question

  • Hi folks,

    We are dealing with millions of records and things are running very slowly using the MS Access and Power Query (ETL work)

    I was curious if we move are queries and data to Power BI, will it help the performance and even the ETL work? 

    I'm sure someone has studies and gone through the migration process. If you could share your thoughts, I would greatly appreciate it. 

    Unfortunately, getting access to SQL Server even at the desktop level seems to be a little difficult around here. 

    Thanks

     

    Friday, October 25, 2019 2:43 PM

Answers

  • yeah so I had every intention of transferring full time to PowerBI and was very involved with that product a couple years past, but then have received a continuous demand of application development work and had to leave it to the side.

    definitely the volume indicates PowerBI to be the better choice.  Note that it is not a data input application for humans - there is no such Access-style forms for users to input data manually.  It is purely display/analytics of data that is already there (i.e. coming from SQL Server perhaps).

    I note the fact that you say things are running slowly in Power Query - and that is an issue in that PowerBi was derived off the Power Query base technology but has taken it to a whole new level.  Horse power counts if you use the local version of PowerBI rather than the cloud version - and typically one designs in the local version then deploys it to the cloud so.......  PowerBI is 64bit - but with millions of records the power of the hardware is an important factor..... so if your Power Query runs slow there's a chance your PowerBI will also if platformed on the same hardware - just FYI.  Definitely you want 16G RAM minimum and probably more......

    • Marked as answer by GoMSACCESS Sunday, October 27, 2019 12:34 PM
    Friday, October 25, 2019 10:59 PM
  • Well, if you use the Access database, and say:

    Ms Access, vb.net, c++, windows scripting with the Access (ACE) database engine, then at the end of the  day, you still using the SAME data engine and thus the speed difference is NOT GOING to change.

    Access as a database (without a network) will in most cases give SQL server a run for the money, and in fact often will run faster.

    Now, if a network is between you and the data?

    Well, then now of course SQL server will using run and work faster.

    However, as a stand-alone database on your desktop, you will as a general rule not obtain any faster or better performance by using Access as your data engine as opposed to say SQL server.

    So in "most" cases, throwing more hardware, or say adopting SQL server  is not going to speed things up (assuming no network between you and the database engine).

    In most cases, using Access or say SQL server, and it runs slow? Well perhaps it the amount of data, but often it is the approach, and how optimized the data processing approach you use is what will determine the overall performance here.

    In a pure desktop only environment, one will be VERY hard pressed to beat the speed of Access.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by GoMSACCESS Sunday, October 27, 2019 12:34 PM
    Saturday, October 26, 2019 5:34 PM

All replies

  • yeah so I had every intention of transferring full time to PowerBI and was very involved with that product a couple years past, but then have received a continuous demand of application development work and had to leave it to the side.

    definitely the volume indicates PowerBI to be the better choice.  Note that it is not a data input application for humans - there is no such Access-style forms for users to input data manually.  It is purely display/analytics of data that is already there (i.e. coming from SQL Server perhaps).

    I note the fact that you say things are running slowly in Power Query - and that is an issue in that PowerBi was derived off the Power Query base technology but has taken it to a whole new level.  Horse power counts if you use the local version of PowerBI rather than the cloud version - and typically one designs in the local version then deploys it to the cloud so.......  PowerBI is 64bit - but with millions of records the power of the hardware is an important factor..... so if your Power Query runs slow there's a chance your PowerBI will also if platformed on the same hardware - just FYI.  Definitely you want 16G RAM minimum and probably more......

    • Marked as answer by GoMSACCESS Sunday, October 27, 2019 12:34 PM
    Friday, October 25, 2019 10:59 PM
  • Well, if you use the Access database, and say:

    Ms Access, vb.net, c++, windows scripting with the Access (ACE) database engine, then at the end of the  day, you still using the SAME data engine and thus the speed difference is NOT GOING to change.

    Access as a database (without a network) will in most cases give SQL server a run for the money, and in fact often will run faster.

    Now, if a network is between you and the data?

    Well, then now of course SQL server will using run and work faster.

    However, as a stand-alone database on your desktop, you will as a general rule not obtain any faster or better performance by using Access as your data engine as opposed to say SQL server.

    So in "most" cases, throwing more hardware, or say adopting SQL server  is not going to speed things up (assuming no network between you and the database engine).

    In most cases, using Access or say SQL server, and it runs slow? Well perhaps it the amount of data, but often it is the approach, and how optimized the data processing approach you use is what will determine the overall performance here.

    In a pure desktop only environment, one will be VERY hard pressed to beat the speed of Access.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by GoMSACCESS Sunday, October 27, 2019 12:34 PM
    Saturday, October 26, 2019 5:34 PM
  • Are the MS Access Queries Convertible through some reverse engineering to work under POWER BI? That you know? Or do you guys know a tool to use to reverse engineer and come up with at least diagram showing a model that shows Queries calling each other and tables involved.

    Thanks for your time.

    • Edited by GoMSACCESS Monday, October 28, 2019 2:20 PM
    Monday, October 28, 2019 1:59 PM
  • there is no portability of Access queries or SQL statements into PBI.  PBI custom design involves the languages of DAX and M.

    as your original post states 'millions of records' it is doubtful that Access is the right tool.  One would need to verify that the file size is not going to exceed 2G. but even so it doesn't handle records in memory as efficiently as is the PBI architecture.

    assuming you are not building an application needing data input - and are only focusing on reporting - - I do advise you focus on PBI when involving millions of records; for instance a typical report: sales by quarter by product.  This is already built into PBI there is really no development per se - just option selects - and a wide variety of visual chart styles to select from.  PBI offers a robust community forum as well.

    Monday, October 28, 2019 2:34 PM