MSOLAP is a native OLE DB provider for Analysis Services

A common task during ETL is to process the OLAP dimensions and the cube after all the dimension and fact tables are loaded into the data warehouse. Integration Services provide two special control flow tasks for this purpose, the Analysis Services Processing Task and the Analysis Services Execute DDL Task.

Before you can use either of these two tasks, you will need to create a SSIS Connection Manager that knows where the Analysis Services server is. The Connection Manager is just the name of the GUI in Integration Services. Behind the fancy GUI, it’s just a connection string that uses the native data provider, the Analysis Services OLE DB provider (or just short as MSOLAP DLL).

Location in the connection string causing “connection cannot be made”

In the above screenshot, I not only provided the Server name but also the Location which I made it the same as the server. The testing of the connection in the GUI was successful.

After the GUI is completed, the final connection string looks like this.

Provider=MSOLAP.5;Data Source=localhost\MULTI2012;Integrated Security=SSPI;Initial Catalog=SSAS2012DEMO;Location=localhost\MULTI2012

The control flow task Analysis Services Execute DDL Task is usually the more flexible choice for processing Analysis Services objects than the Processing Task. But to makes sure that my connection string is correct, I used the Analysis Services Processing Task, which is the GUI version of the DDL task, to test the connection string. I ran into this “connection cannot be made” error.

It turned about the problem is in the Location. Removing the Location from the connection string solved the connection issue.

AS a matter of fact, the shortest connection string that can pass the connection is just this:

Data Source=localhost\MULTI2012

No explanation for Location in connection strings for MSOLAP and ADOMD.NET provider

I checked both the connection string document in MSDN library for MSOLAP and ADOMD.NET , and couldn’t find much information about what the Location is supposed to be.

AdomdConnection.ConnectionString Property

Connection String Properties (Analysis Services)

In the first link for the ADOMD.NET provider it just simply states:

Location: Sets the name of the server.

In the second link for the MSOLAP provider, I couldn’t even find any mentioning of Location.

Three types of data providers for Analysis Services

There are really three types of data providers for Analysis Services.

Data providers used for Analysis Services connections

Analysis Services provides three data providers for server and data access. All applications connecting to Analysis Services do so using one of these providers. Two of the providers, ADOMD.NET and Analysis Services Management Objects (AMO), are managed data providers. The Analysis Services OLE DB provider (MSOLAP DLL) is a native data provider.

More ways to access Analysis Services in SSIS than the DDL Task and the Processing Task

Check out this book if you want to know more than just the DDL Task and the Processing Task to access Analysis Services in SSIS.

Microsoft® SQL Server® 2008 Integration Services Problem–Design–Solution

Quote from the book:

* Using the control flow object Analysis Services Processing Task is the most straightforward

approach, and is discussed in the next section. This allows any SSAS objects (that involve

processing) and their processing method to be selected. A few drawbacks are listed in the next

section.

* Although the Analysis Services Execute DDL Task may sound as if it only allows objects to be

modified, created, or deleted, its functionality goes way beyond Data Definition Language

(DDL). This task runs an SSAS XMLA script. XMLA includes the capability not just to run DDL,

but also query and process. Therefore, this task is very useful for SSAS processing, because the

XMLA can be modified in an SSIS package before it is executed.

* Another approach involves using the Execute Process Task to call the ASCMD executable that

comes with SSAS. This executable can run MDX, Data Mining Expressions (DMX), and XMLA,

and also contains some command – line switches to make your processing more dynamic.

The data flow in SSIS includes two destinations: the Dimension Processing destination and the

Partition Processing destination . These allow data directly from the pipeline to be pushed into

either an SSAS dimension or SSAS partition, respectively. Unlike all the other SSIS approaches

discussed here, this approach is the only one where data is pushed directly to SSAS. The

other approaches essentially tell SSAS to start pulling the data from a data source. Like the

other SSIS approaches, this approach is described later in this chapter.

* Finally, the programming API for SSAS, called Analysis Management Objects (AMO), allows the

interface into the SSAS object model and can leverage the processing methods to process any of

the SSAS objects described. To use AMO for processing, you must leverage either the Script Task

in the control flow, or the Script Component in the data flow.