Using Excel as BI Tool? Consider 64-Bit version.


Excel as Business Intelligence Tool. What do I mean?

For the purpose of this blog post, If you’re creating Excel based reports against Power Pivot Model, SQL Server Analysis Services data or creating reports by combining data from other data sources – then you are using Excel as a Business Intelligence Tool.

What’s the problem?

when you’re using Excel as a Front end tool for creating Business Intelligence reports – there’s a chance that you’ll be bringing in more data than excel could handle which would create “performance problems”.

How can 64-bit help?

Performance power offered by Excel 64-bit > 32-bit version.

Why? Because of the advantages that 64-bit computing has over 32-bit computing

How can YOU decide whether you need 64-bit version? (FOR BI FOLKS ONLY)

(prerequisite: your OS should be 64-bit)

If you don’t have Excel & planning to get it. Consider 64-bit!

If you already have Excel & it’s 32-bit – Do you see slow performance when you create your reports against Power Pivot model or SSAS? Do you have more than 4 GB RAM in your machine? YES? Try 64-bit.

What’s the down-side of 64-bit?

There are compatibility issues with 32-bit Excel add-ins. Read more here: Choose the 32-bit or 64-bit version of Office

Microsoft does not recommend using 64-bit excel.

What version of Excel support 64-bit?

Excel 2010 & onwards.

Case Study

In my current project, a Business Analyst who was creating reports against a SSAS (SQL Server Analysis services) cube & was seeing 1-2 min delays in “data refresh” each time filter values used to change. Clearly, She was not happy!

I looked at her computer configuration. She was using:

  • Excel 2010 32 bit
  • 8 GB RAM
  • 64 Bit edition of Windows 7

so I recommended 64-bit version of Excel. After the version was upgraded, we tried again! This decreased the time delays by 25x. The data was now getting refreshed in 2-4 seconds!