XMLA– Clear Analysis Services (SSAS) database cache

How to clear cache of a cube <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> <CubeID>Adventure Works DW</CubeID> </Object> </ClearCache>   How to clear cache of a Database <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> </Object> </ClearCache>


SSAS Tip – IndexBuildThreashold

<IndexBuildThreshold> Index creation is controlled by the number of rows, specified in the msmdsrv.ini (default location of msmdsrv.ini file – ?:\Program Files\Microsoft SQL Server\MSAS10_50.SQL2008R2\OLAP\Config\msmdsrv.ini) file as <IndexBuildThreshold>, with a default of 4096. Partitions with fewer rows will not have indexes built. Which means if you have less than 4096 records in a Partition you will…


Different Kinds of SSAS Processing in simple words…..

Often time I been asked this quest – what will be the impact of processadd on indexes or aggregations, do I need to recreate aggregations or Indexes after creating Process Update or what is the right approach of handling processing, so thought of writing this article. Here are the major Process Types in SQL 2005…


SQL Server Deployment Property (Retain Configuration Setting)

  Well one of my customers had asked the as significance of this property as he didn’t find anything on net so thought of writing an article about it. Retain Configuration Setting: Will retain all properties of Connection String except Password. Project which you want to deploy, have SQL DB Server Name – Server A…


Migrating OLAP SSAS 2005 Database to SQL 2008

Well one of my peer from Consulting team had asked this question tried looking for MSDN article but not find any so thought of writing it to my blog: Question: How to Migrate Database from SQL 2005 to SQL 2008 Scenario 1 – Moving SSAS Databases from SQL 2005 SSAS to another Server where we…


Running AS Command(XMLA / MDX) as a Batch Job Using SSIS Package

  – Get ASCMD for right version 2008 2005   -Create a file with XMLA / MDX Command and give extension as xmla/mdx   -Create one batch file with command text as “d:\asjob\ascmd -S karan2k8-64\sql2005 -i d:\asjob\backup.xmla” In my instance- ASCMD is located in D Drive ASJob folder that’s why I had given it in…


SSAS Profiler Scheduler

I have been asked by many customers and seen in many social sites, many people were asking about the procedure of scheduling SSAS profiler traces so one fine day thought of writing a utility. I have developed a small app which will help in scheduling SSAS Profiler Tracing using predefined Trace Template. This Utility can be useful if you…


Moving Data Directory of Analysis Services (SQL 2005 / 2008)

Applies to (Data,Backup,Log & Temp) You cant move Bin and Config Directory Manually, recommended way is Re-Insallation Here are the steps: From GUI: Start Microsoft SQL Server Management Studio, Connect to Analysis Services Server Right mouse click on server name, then choose properties. Change property “DataDir” to contain new location Stop Analysis Services Service Move…


Why do we need SPN for File Server (NAS / RAS / File Share System) DNS Alias (Cname)

Very often we use UNC location while taking backup of SQL or Analysis Services Database or try to upload data in SQL Tables using bulk load command where text or csv file located in File Server so we access it by giving UNC. Sometimes File Server Machine (NAS / RAS or a computer where we…


SQL 2005 (SQL 2008) Analysis Services Server Side Tracing

  With Analysis Services 2005 (2008) you can collect traces without using gui this feature is known as Server Side Tracing. In this article we’ll talk about how to achieve Server Side Tracing. How to create a Server Side Trace Open SQL Server profiler connect to Analysis Services and in an Event Selection define the…