Excel Report was not passing right user id to SQL Server Analysis Services

Background: Windows 2003×64, SQL 2005 SSAS with Dimension Security Enabled In this scenario when we were opening an Excel Report deployed on  SharePoint it hangs the report when we try to expand the user hierarchy Resolution To work around this issue, disable the local SID cache on the domain member computer. To do this, follow…


Excel report with SSAS taking more time to refresh over Wan

We had seen scenarios where Excel Sheet with Pivot table takes less time locally but over Wan it takes more than hour to refresh. The issue which we fixed was on Excel 2007 with SQL 2008 AS (Cube had Cell Level Security Enabled) The resetting of the connection to Analysis Services within a report each…


YTD Based on Current System Date (Now() Function), based on same date pulling value for Last Year too.

  In this example we are trying to pull ytd based on current date using Now Function.   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")+"]"))))) Get Complete Example on http://blogs.msdn.com/b/karang/archive/2010/10/25/ytd-based-on-current-system-date-now-function-based-on-same-date-pulling-value-for-last-year-too.aspx


Dealing with 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…


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…


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. http://blogs.msdn.com/b/karang/archive/2010/08/16/sql-server-deployment-property-retain-configuration-setting.aspx


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’. http://blogs.msdn.com/b/karang/archive/2010/08/11/how-to-deal-with-corruption-in-analysis-services.aspx [All the posts are AS – IS and doesn’t carry any warranty]


Migrating OLAP SSAS 2005 Database to SQL 2008

  This blog talks about different procedures of moving SSAS 2005 Databases to SQL 2008 Databases 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 have SSAS 2008 Services Procedure: Backup SQL 2005 DB and restore it on…


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 with 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…