Enabling SSAS Clustered Instance to listen on Multiple IP Addresses

  On a clustered instance, Analysis Services will listen on all IP addresses of the cluster group, but only on TCP port 2383. You cannot specify an alternate fixed port for a clustered instance Steps of making AS Clustered Instance Listening on Multiple IP Addresses: 1) Add Resource -> More Resource -> IP Address 2)…


YTD Based on Current System Date

  with member [Date].[Calendar].[currentyear] as aggregate(ytd((strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”)))) member [Date].[Calendar].[prevyear] as aggregate(ytd(parallelperiod([Date].[Calendar].[Calendar Year] ,1,(strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”))))) select { [Date].[Calendar].[currentyear] ,[Date].[Calendar].[prevyear] }on 0, [Measures].[Internet Sales Amount] on 1 from [Adventure Works] Well, my customer wanted to pass fix start date member and get the result so changed query as per Customer’s requirement. From your application you can pass value but…


During ETL detecting dirty records from Fact

Attribute Key Not found Error occurs in SSAS due to the following reasons :   Dimensions processed after measure groups (This one is simple, just process Dimension before processing a Cube) Presence of Orphan Records in Fact Table  (This one is tuff to handle from SSAS Perspective) These errors can be dealt easily at ETL…


Slowly Changing Dimension using SSIS

Surrogate Keys Also known as meaningless keys, substitute keys, non-natural keys, or artificial keys. A surrogate key is a unique value, usually an integer, assigned to each row in the dimension. This surrogate key becomes the primary key of the dimension table and is used to join the dimension to the associated foreign key field…


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…


How to Deal with Corruption in Analysis Services

  I had seen few scenario where you perform any action on Analysis Services you get weird Error Message like some store is corrupted Sample Error: An error occurred when instantiating a metadata object from the file, ‘\\?\J:\Program Files\Microsoft SQL Server\MSAS10.SQLInst\OLAP\Data\dbDev.0.db\testdim.2.dim\DimensionPermission 1.0.perm.xml’. To resolved Database Corruption follow below mentioned steps: Workaround 1 – Take the…


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…


Updated SSAS Profiler Trace Scheduler Tool

Well few weeks back I had developed a tool with which we can Schedule SSAS Profiler Trace, based on suggestion from peers made few changes – Apart of Scheduling one can start or stop traces manually in new release – http://asptscheduler.codeplex.com/


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…