Archive for the ‘Uncategorized’ Category

SSAS Crashing Intermittently: Caused by Monitoring / AV Scans

April 8, 2013 1 comment

Problem Description:

Analysis Services is crashing intermittently and also producing min-dumps.


This could be one of the issues. For getting full analysis of Mini-Dump you can involve Microsoft Customer Support Services and ask them to analyze dumps.

In this case we found:

The issue is SMS client (CcmExce) does software inventory periodically, and it scans the data folder of Analysis Services. When a job needs to commit, it has to delete older version of data files. At the moment, the SMS client has a file handle on some of the database files, causing SSAS unable to delete the older version of the database, so commit fails and crashes SSAS

As you can see in this Process Monitor – CcmExec is browsing through SQL Folders:



We have resolved issue by making an exception in SMS client not to browse / scan SSAS Folders
– Data
– Temp
– Config
– Log


Exclude Analysis Services folders from Virus Scans, File Monitoring Tools, Systems Management Server Client – CcmExec.exe or any other 3p File Monitoring or File Backup Tool.

For SQL engine follow recommendations are given in this link –

Categories: Uncategorized

Microsoft TecheEd India 2013: Come and join our session

March 18, 2013 Leave a comment


Teched Bangalore 2013

Look for the SQL Developer track. If you are at TechEd, do join.

Categories: Uncategorized

Microsoft BI Authentication and Identity Delegation

March 11, 2013 Leave a comment


From straightforward client/server designs to complex architectures relying on distributed Windows services, SharePoint applications, Web services, and data sources, Microsoft BI solutions can pose many challenges to seamless user authentication and end-to-end identity delegation. SQL Server technologies and data providers expect to use Windows authentication while SharePoint Server uses Web Services Security (WS-Security). Flowing a user identity from a Windows or browser-based BI client application through a claims-based SharePoint service to a Windows backend system is not always possible due to various limitations in data providers, security protocols, and identity services. Network, forest, and federation topologies also influence the authentication flows. Familiarity with the authentication protocols and capabilities, delegation limitations, and possible workarounds is an indispensable prerequisite to delivering a positive BI user experience across the entire Microsoft BI solution stack in enterprise environments.

To review the document, please download the Microsoft BI Authentication and Identity Delegation Word document.

Categories: Uncategorized

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

February 20, 2013 Leave a comment


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:



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.




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.


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



SQL Server Analysis Services Port (SQL 2005 / 2008)

Categories: Uncategorized

DAX: Using filter and summarize in same Query

February 15, 2013 Leave a comment

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],


Categories: Uncategorized

PowerShell:How to List Database Roles and their Members

February 5, 2013 Leave a comment
   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; }

Categories: Uncategorized

Hardware Sizing a Tabular Solution (SQL Server Analysis Services)

January 11, 2013 Leave a comment

Applies to: SQL Server 2012 Analysis Services, Tabular Solutions

Summary: Provides guidance for estimating the hardware requirements needed to support processing and query workloads for an Analysis Services tabular solution.

Download from here (Hardware Sizing a Tabular Solution (SQL Server Analysis Services) Word document.

Categories: Uncategorized

Cleared SSAS Maestro (MCM)

December 19, 2012 1 comment


Finally achieved highest certification in SSAS world.

What is the SSAS Maestros?

The SSAS Maestro program was created as a way to share the lessons learned by enterprise customers of the SQL Customer Advisory Team (SQLCAT) using complex SQL Server 2008 R2 Analysis Services and a Unified Dimensional Model (UDM).


Because of the complexity of the subject matter and the depth of the lessons, the course has strict requirements, including the following:

  • Applicants will be accepted based on their depth of technical experience with Analysis Services.

  • The three-day course will include several labs that intentionally provide very little guidance.

  • Upon completion of the course, attendees will be given a take-home exam project that they will need to complete within thirty (30) days.

Following the technical conference conventions defining 400-level sessions, this is a 500-level course. It is modeled after the MCM SQL certification program.

Additional Microsoft hosted SSAS Maestro training courses will be available in the near future. Check back on this site for updates. For questions or for a list of courses hosted by certified Maestro trainers, please email

Learn more about Maestro…..

Categories: Uncategorized

SSAS Synchronization: Across different versions

October 24, 2012 Leave a comment


Problem Statement: Synchronization between different builds or versions of Analysis Services.

Solution: The Synchronize Database Wizard makes two Microsoft SQL Server Analysis Services databases equivalent by copying the data and metadata from a source server to a destination server. This wizard can also be used to deploy a database from a staging server onto a production server, or to synchronize a database on a production server that has changes made to the data and metadata in a database on a staging server.

Apart from Data it copies the metadata, which makes Synchronization between different builds unsupported and you can’t synchronize across major versions or service packs.

Categories: Uncategorized

SSAS encryption

October 23, 2012 Leave a comment

Description: Customer asking if AS supports TDE like encryption we got in SQL Engine.


SSAS doesn’t support any data encryption on disk, in simple words – SSAS doesn’t do any kind of encryption.

What it supports:


You can use Analysis Services using PUMP and configure pump with HTTPS

Windows File System encryption  –

You can use Windows File System encryption (Windows Server 2003) or BitLocker (on Windows Server 2008 and Windows Server 2008 R2) to encrypt the drive used to store cubes. Be careful, though; encrypting MOLAP data can have a big impact on performance, especially during processing and schema modification of the cube. We have seen performance drops of up to 50 percent when processing dimensions on encrypted drives though less so for fact processing. Weigh the requirement to encrypt data carefully against the desired performance characteristics of the cube.


Encrypting File System in Windows XP and Windows Server 2003

BitLocker Drive Encryption

Use ROLAP partitions –

Use ROLAP for very confidential data and store the data in a TDE encrypted Database.

Caveat – ROLAP comes with its Performance cost, so keep that in mind.

Categories: Uncategorized