Doing the Forbidden: Fixing Cubes and Dimensions on Production et al.

Okay, now this could be a completely nuh-uh topic. But in the real world these things can and do happen. Example: There is this cube developed a couple of years ago, deployed on various environments such as QA, UAT, Production etc. Then something goes wrong when some new dimension data has text that is longer than what the field allows – ETL fails. You figure it is a small change to the dimension table: field QuarterName on table DimDate needs to be varchar(15) instead of the varchar(9) that it is. So you go fix it directly on the database – Big mistake when it comes to ALM etc. However, the ETL works. But, when you process the dimension on SSAS you get an error:

Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine.

This is because the dimension definition still holds the older definition of the source field. Now in order to fix this the “proper” way would be to locate the source solution, fix the problem on it, and then re-deploy the cube (read: time consuming). But, when the urgency and speed of fixing the issue takes priority, or when there is that hanging doubt that the source solution might actually be a bit outdated and improperly source controlled, there comes a time in a man’s or woman’s life that they have to do the forbidden.

So in order to fix the issue on site, where there are no development tools to open up the cube for editing, nor when additional tools such as BIDS helper are not allowed to be installed, you may need to take the dusty gravel road:

  1. Open SQL Server Management Studio, and connect to the cube
  2. Locate the dimension, right-click and choose Script Dimension As > ALTER To > New Window
  3. This will open up an XMLA query with the underlying code for the broken dimension
  4. Now comes the harder part: locating the field definition. A quick Find after typing in the field name would surely find what you were looking for
  5. Fix the error and press F5, this would update the dimension definition

image image

Processing the dimension would show you that all is now well.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s