Excel & SQL Server: Easy Setup for MSOLAP Data Provider

Table of Contents

Getting Your Excel Talking to Analysis Services: A Guide to MSOLAP¶

Hey Excel fans! Ever tried to connect your spreadsheet to Analysis Services and gotten stuck? It’s all about having the right MSOLAP data provider, which acts like a translator between Excel and your Analysis Services data. This guide walks you through the setup, making it easy peasy.

image just illustration

MSOLAP: The Bridge Between Excel and Analysis Services¶

Think of MSOLAP (Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server) as the key that unlocks Analysis Services data for Excel. Every Analysis Services version has a matching MSOLAP version, like a lock and key. If they don’t match, you’re out of luck. Here’s a handy cheat sheet:

Analysis Services Version	MSOLAP Provider Version
SQL Server 2008	MSOLAP.4
SQL Server 2012	MSOLAP.5
SQL Server 2014	MSOLAP.6
SQL Server 2016	MSOLAP.7
SQL Server 2017 and later	MSOLAP.8 (This one’s the “evergreen” version, meaning it’s designed to stay compatible)

For more details about MSOLAP and other Analysis Services client libraries, check out the official Microsoft documentation on Analysis Services client libraries. It’s a goldmine of info!

Pro Tip: Using the right MSOLAP version is crucial for a smooth connection. For more on connection string properties, dive into the documentation on connection string properties. Need older MSOLAP versions? Microsoft support has you covered with instructions on How to obtain the latest versions of MSOLAP.

Excel’s MSOLAP Selection Process¶

Excel uses the MSOLAP provider installed on your computer. Let’s say your connection string shows MSOLAP.5. That means Excel is using version 5. If you have multiple MSOLAP versions installed, Excel goes by what’s configured in your registry.

Here’s where things can get tricky:

• You have MSOLAP.5 installed and registered.
• You install MSOLAP.6 to connect to Analysis Services 2014, but you forget to update the registry.

Excel will stubbornly stick with MSOLAP.5. Problem is, you can’t use an older MSOLAP version with a newer Analysis Services version. It’s like trying to fit a square peg in a round hole!

Taking Control: Updating Your Registry¶

Want to tell Excel exactly which MSOLAP version to use? Time to tweak the registry! These keys determine Excel’s MSOLAP choice:

• Office 32-bit MSI: HKEY_LOCAL_MACHINE\\SOFTWARE\\Classes\\Wow6432Node\\CLSID\\{308FF259-8671-4df4-B66C-9851BFACF446}\\ProgID\\(Default)

• Office 64-bit MSI: HKEY_LOCAL_MACHINE\\SOFTWARE\\Classes\\CLSID\\{308FF259-8671-4df4-B66C-9851BFACF446}\\ProgID\\(Default)

• Office 32-bit C2R: HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\15.0\\ClickToRun\\REGISTRY\\MACHINE\\Software\\Classes\\Wow6432Node\\CLSID\\{308FF259-8671-4df4-B66C-9851BFACF446}\\ProgID\\(Default) or HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\ClickToRun\\REGISTRY\\MACHINE\\Software\\Classes\\Wow6432Node\\CLSID\\{DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}\\ProgID

• Office 64-bit C2R: HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\15.0\\ClickToRun\\REGISTRY\\MACHINE\\Software\\Classes\\CLSID\\{308FF259-8671-4df4-B66C-9851BFACF446}\\ProgID\\(Default) or HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\ClickToRun\\REGISTRY\\MACHINE\\Software\\Classes\\CLSID\\{DBC724B0-DD86-4772-BB5A-FCC6CAB2FC1A}\\ProgID\\(Default)

MSI vs. C2R: What’s the Deal?

Not sure if your Office is MSI or Click-to-Run (C2R)? In Excel, go to File > Account. See an “Office Updates” section? It’s C2R. No “Office Updates”? It’s MSI. Simple!

image just illustration

Wrapping Up¶

Connecting Excel to Analysis Services doesn’t have to be a headache. By understanding the role of MSOLAP and how to configure it correctly, you can unlock the power of your data. So go ahead, give it a try!

We’d love to hear your experiences! Did this guide help you? Any tips or tricks to share? Leave a comment below. And if you’re hungry for more data insights, be sure to check back for more helpful articles!