Linked Server for Analysis Services

Following command creates a linked server named AM_OLAP:
EXEC
@server = N'AM_OLAP', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers avaoilable on SQL Server under Linked Server > Prover node in SSMS Object Browser
@datasrc=N'MyOlapServer', -- machine that host Analysis Services
@catalog=N'MyOlapDatabase' -- Analysis Services database (cube)


If database server and Analysis Services are on different machines, queries executed through it may have a problem with "double hop" (fact that security context cannot be trasfered from one server to the other):

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Connectionlnfo)

Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server "OLAP_AM",

OLE DB provider “MSOLAP” For linked server “OLAP_AM” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “OLAP_AM’ returned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)

To resolve that problem you have 3 options:
1. Run SQL queries from data server (you need to be remotly connected to the database server)

2. Enable use of Kerberos on the database server

3. Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:  

EXECmaster.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AM_OLAP',@useself=N'False',@locallogin=NULL,@rmtuser=N'myDomain\MyLogin',@rmtpassword='########'
Now you may be able to use it in queries like this:
Select * From OpenQuery(AM_OLAP, 'mdx query')

Axis numbers specified in a query must be sequentially specified

Select ({[Dim Date].[Hierarchy].[Month Name].&[2011]&[June].Lag(11):on rowsfrom [AML DW]where
[Dim Scenario].[Domain ID].&[1002]);



Error message from Analysis Services: Query (1, 8) Axis numbers specified in a query must be sequentially specified, and cannot contain gaps.

Problem was that MDX query specified what will be placed on rows, but not on columns.
In Analysis Services, columns are first axis and rows second.

(
{[Dim Date].[Hierarchy].[Month Name].&[2011]&[June].

{[Dim Status].[Status].&[7],
[Dim Status].[Status].&[10],
[Dim Status].[Status].&[9]}
) Select ( [Measures].[Alerts Count]) on Columns,Lag(11):[Dim Date].[Hierarchy].[Month Name].&[2011]&[June]},on rowsfrom [AML DW]where
[Dim Scenario].[Domain ID].&[1002]); ([Dim Domain].[NAME].&[AML - Suspicious Activity], ([Dim Domain].[NAME].&[AML - Suspicious Activity],
[Dim Date].[Hierarchy].[Month Name].&[2011]&[June]},
[Measures].[Alerts Count],
{[Dim Status].[Status].&[7],
[Dim Status].[Status].&[10],
[Dim Status].[Status].&[9]}
)