Karan Gulati (SSAS Maestro)

Symptoms:

SSAS Named Instance running on cluster with two nodes, on one instance we are able to connect SSAS using Name Instance but when you failover to other node and try to connect we get error message

SSAS Cluster Virtual Server Name – SSASVirtualServer

Instance is MySSAS

Two Nodes:

NodeA

NodeB

When NodeA is owner of SSASVirtualServer and we connect SSASVirtualServer\MySSAS it works but when we failover SSASVirtualServer to NodeB and try to connect  SSASVirtualServer\MySSAS it fails with error:

A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.

 

Cause:

The startup of SQL Browser account does not have permission to access msmdredir.ini. The startup account of SQL Browser should have both Read and Write permission to the ASconfig folder or its child objects.
By default, the SQL Browser will periodically check and update the 90\Shared\ASConfig\msmdredir.ini file to ensure it knows the named instance SSAS info(port..etc) and it will translate client who needs to connect to the named instance SSAS to the correct name and port.

Solution:

a. If the SQL Browser is running under "NT Authority \local service" account, ensure the account has permission (read/write) to the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder and its child objects. If the SQL Browser is run under other account, please ensure the same.

b. If you are not sure about permission change from Local Service to Local System and restart services.

Today, we have resolved issue of same nature – thanks Saman Alaghehband for his time and patience

Note: On Cluster Environment it is always recommended to connect using SSAS Virtual Server Name

Refer:

SQL Server Analysis Services Port (SQL 2005 / 2008)

In this SQL Query we are grouping Sales by Year and Color plus adding a filter of 5000

select CalendarYear,color,sum(SalesAmount) from [DimProduct] join FactInternetSales
on DimProduct.[ProductKey]=FactInternetSales.[ProductKey]
join DimDate
on dimdate.datekey=FactInternetSales.OrderDatekey
Group by CalendarYear,color
Having sum(salesamount)>5000
order by CalendarYear,color

Equivalent Dax of achieving same is:

evaluate( filter (summarize (‘Internet Sales’, ‘Date'[Calendar Year],
‘Product'[Color] ,"Sales Amount",sum(‘Internet Sales'[Sales Amount]))
,Calculate(sum(‘Internet Sales'[Sales Amount]))>5000))
order by ‘Date'[Calendar Year],
‘Product'[Color]

   1:  [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

   2:  $ServerName = ".\sql2008r2"

   3:  $DB = "Adventure Works DW 2008"

   4:  $Server = New-Object Microsoft.AnalysisServices.Server

   5:  $Server.Connect($ServerName)

   6:  $SSASDatabase = $Server.Databases.Item($DB)

   7:  $SSASDatabase.Roles | Select Name, Members
 

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }