Excel data Limit

[peter8848]

Dear All,

Just wondering when using excel to create report as it has graphs and Pivot tables etc but it cannot hold too much data. for instance, if we have more than 20k rows the report is getting slower. What other ways we can work around it? Could we use Access to hold the data and use excel for reporting?

Thanks,

Peter

 

[Misca]

Have you heard of Power Pivot in Excel (or its "big brother" Power BI desktop)? The Power BI data model they're based on handles multiple tables and a few million rows quite easily - and compresses the file size to a fraction of the original. They've taken Excel to the next level.

 

[peter8848]

Hi Misca,

Keen to learn that but not sure where to start from, does that mean if i have an excel 2016 I will have them both? In addition, what differs to both in contrast working with Access data?

Cheers,

Peter

 

[Misca]

You can use Access as the data source but if you want to do calculations in Excel, you must import the data to Excel somehow. Sure, you can use external data sources with "normal" pivot tables as well but they can't handle multiple tables and multiple data sources. The Power Pivot data model can do that and you can also build "portable" measures that you define once and use over and over again.

I must say that Microsoft has changed their Power Pivot policy several times over the years and I'm not sure what is the current policy. They introduced the Power Pivot (and their ETL-tool Power Query) in Excel 2010 as a free add-in but I have no idea if that's still the case. But at least the Power BI desktop and its monthly updates are free and you don't even have to use Excel if you choose to use them.

The catch with the desktop version is that you're only supposed to create your reports with that and use the cloud based Power BI to share your reports.

To find out more about the current MS policies etc. go to Microsoft web site. And if you want to learn to use them there's a ton of tutorials in YouTube. For Power Pivot tutorials I'd recommend the PowerPivot-playlists by ExcelIsFun but for the Power BI (desktop) there's so many good ones I don't know where to start. Besides, the basics are the same for both Power Pivot and Power BI.

The main difference is with Power Pivot you're still using Excel and its visuals where as Power BI comes with dozens of built-in visuals and if that's not enough, you can find a ton more from the web site. Another major difference is sharing the reports: With Power Pivot you're going to need Excel to read the reports as well where as with Power BI you can read the reports using basically any web browser and even mobile phones.

 

[SpillerBD]

If you have O365, you have Power Pivot. Microsoft changed the policy. Which made sense since PowerBI desktop is free.
If you bought the "disk" then you probably do not.

Power Pivot performs most calculations at Query or in the Data Model. A Source in the Excel worksheet space may suffer when calculations on the page exist and worse with Volatile functions. So even if you move to PowerPivot, you need you avoid sheet calculations to retain speed.

 

[peter8848]

Hi Misca,

Sounds like Power BI is a better options I would like to share this report with all other managers so they can view on the phone or excel etc. Is it free to download and easy to operate, also by using power BI, would you recommend to store the data in access or somewhere else if it is large?

Cheers,

Peter

 

[peter8848]

Hi SpillerBD,

Thanks for your help.

So did you mean storing the data in excel will retain speed and what recommendation you will have if we have large amount of data when using Power BI or PowerPivot.

Cheers,

Peter

 

[Misca]

As you know Excel becomes slow when you have a big Excel file with tons of worksheet calculations. And a slow data source slows down the Power BI as well. I believe that's what he meant. It's recommended to perform all the calculations in the data model if possible.

 

[SpillerBD]

Hi SpillerBD,

Thanks for your help.

So did you mean storing the data in excel will retain speed and what recommendation you will have if we have large amount of data when using Power BI or PowerPivot.

Cheers,

Peter

Its in the details. Storing data in the DataModel can retain speed. When working in the DataModel and using PowerPivot, calculations are only done at certain evaluation times and the results are stored in a more efficient manner. The data is in the Workbook but not on a worksheet.
IF the data is loaded into a worksheet, then you can still experience slowdowns because that storage method is less efficient.

PowerBI Desktop only uses the DataModel, so it forces a user to stick to the more efficient methods.
PowerQuery, can be the slowest part and is often needed. If a data connection can be created without PowerQuery, then some additional efficiency will be gained. During developemnent of any PowerBI/PowerPivot model, there are often many Query refreshes that are not needed in the end.

So its not really Excel that slows things down, but the use of Worksheets to store data and the use of WorkSheet functions to perform calculations.

The choice between PowerBI or PowerPivot is generally dependent on the IT support and willingness to pay for the additional service to Microsoft for PowerBI publishing/hosting.

 

[peter8848]

Hi SpillerBD,

I used powerpivot this time but I cannot see any benefit from using Datamodel as the size of the file still the same as data holds under normal excel file and the only difference I can see when you type formular in the Datamodel form the entire column follows. In addition to me powerpivot only provides links between different fors so save our time from doing lookup etc, Am i right or do i miss out something? I was hoping store the data in datamodel can reduce the size of the file or it only provide more available rows for data than excel?

thanks for your help.

Cheers,

Peter