Analysis Service (SSAS) Vs. PowerPivot

This post compares Analysis Services and PowerPivot. Continue Reading…

SSAS: Cube Deployment Issues with Deployment Wizard addressed in SQL Server 2008

If you recently upgraded to SQL Server 2008 or considering an upgrade, you would be glad to know that the Deployment Wizard of Analysis Services works in the way it should (long overdue). In earlier versions, if a new version of the Analysis Service database was deployed over an one, immediately after deployment all the roles would have no permission to the cube even if “retain role and members” option is chosen. Not only that, the role definition especially Dimension Data and Cell Data Access permissions would be lost after deployment. The only saving grace was the role membership remained intact. Continue Reading

SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises

Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). There are relative merits of each approach, which isn’t the topic of this post but more details can be found here. A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected.  

Continue Reading

Using SSIS Pivot Transform When Pivot Column is Not Available

Pivoting is typically used in DW solutions to load multi-valued dimensions. In an earlier post, I demonstrated how SSIS Pivot component works and the advantages of using SSIS Pivot component over its equivalent T-SQL Pivot statement. In this post, I explain a technique to Pivot data when Pivot Column is not explicitly available. Continue Reading

SSIS Pivot Transform vs T-SQL Pivot

Pivot transformation converts multiple rows of data into a single record by using an input column that has a finite set of distinct values. Pivoting is typically used in DW solutions to handle multi-valued dimensions. In SQL Server, Pivoting is done using either      

  1.  T-SQL Pivot statement or
  2.  SSIS Pivot Transformation

There are two significant benefits that stand-out in favour of using SSIS Pivot Transformation for ETL scenarios. The remainder of this post explains just that. Continue Reading

Alternatives to SSIS SCD Wizard Component

SSIS comes with an out-of-box SCD Wizard to handle Type 1 and Type 2 Slowly Changing Dimensions (SCD) which is a fundamental ETL requirement. However the SCD wizard component has some serious drawbacks – both from operational and functional perspectives that make it unusable for practical purposes. A good summary on the shortcomings of SCD Wizard component can be found here

Several workarounds have evolved over time and in this post I would like explore the different alternative options to handle Type 1 and Type 2 SCD without using the out-of-box SCD Wizard Component.  

Continue Reading

Pre-Installation Options for Analysis Services in SQL Server 2008

Some time back I published a post on post-installation steps for Analysis Services. That post was written with SQL Server 2005 in mind. In SQL Server 2008 – Data Dir, Log File Dir, Temp dir and Backup dir can now be specified as a step BEFORE installation of Analysis Services. There is no need for post-installation steps in SQL Server 2008, unless you missed configuring this step during installation. Continue Reading

Minimal Post-Installation Steps for Analysis Services

installDuring installation of Analysis Services, most of the properties are configured with a default value. These properties can be reconfigured any time after installation from SQL Server Management Studio. For most properties the default values works just fine. However, there are few properties that are best reconfigured immediately after installation to prevent down-time in production environment. Continue Reading

Row Insert from SSIS package Vs Transact-SQL Statements

SQL Server writes transaction logs sequentially. The size of transaction logs when using sql INSERT statement depends on the Recovery Model of the database and the type Transact-SQL statements used. Database recovery model can be – Simple (min), Bulk-Logged (intermediate) or Full (max). While this is true for Transact-SQL INSERT statements, records inserted through an SSIS Data Flow Destination (like SQL Server Destination) exhibit a different logging behaviour. This is because the INSERT statements issues from Integration Services engine is always minimally logged and it bypasses the recovery model of the database engine. This results in significant performance improvement by reducing the sequential disk writes, especially when dealing with huge recordsets which is typical in a data warehouse ETL scenario. That’s another reason why SSIS packages perform better over Transact-SQL Stored Procedures for ETL purposes. Continue Reading

Offline OLAP – Working with Disconnected Analysis Service Cube

Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Services Cube in disconnected mode. The Offline OLAP feature basically snapshots a version of the cube into a local cube file which can be used by Pivot Tables. Continue Reading