By: Scott Murray | Updated: 2014-05-23 | Comments (7) | Related: > Analysis Services Dimensions
Problem
So you did everything correctly with your OLAP project. You had a trained Project Manager leading the team; users were directly involved from the beginning and you had clear definitions of the scope of the project and ultimately all the various reporting needs and objectives. You even had the end users sign off the field list that they said was ALL they needed for ALL their reporting requirements, forever and ever. Fast forward two months after the final project sign off, and now the end users are "creeping" the scope of the project. They now tell you that field xyz, which was supposed to only ever (ever) have 10 characters, now needs to be twenty characters long or maybe switched from an integer to a character based data type. How do you go about making these structural changes in the database and cube? How does this affect my SSAS Data Source View (DSV) and ultimately my cube?
Solution
In order to run through the complete structural change process, we will again use AdventureWorks 2012 SSAS and SQL Server databases; the SQL Server database and SSAS database are available on Codeplex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. Once you download and install the SQL Server database and SSAS database, open up SQL Server Data Tools (SSDT) and SQL Server database in Management Studio 2012 and navigate to the AdventureWorks. For our examples, we will discuss making changes to a dimension attribute field that is also the key field.
Changing the Field Structure
For the first part of this tip, let us explore the promotions dimension. Specifically, we will first use the Promotion Category field.
Notice how the field size for the both the NameColumn and KeyColumn are set to 50 (in some cases the key field will be different than the name field which could be the topic for another tip!). This size matches the field size in the base table, shown below, upon which that dimension is derived.
So what happens when the end users tell you, they need a Promotion Category that exceeds the 50 character size originally set at project design time. Maybe the new category is something like: "Promotion category to beat all other promotion categories for ever and ever", which is 75 characters. First we need to adjust the column size in the source system by running the following code. This code additionally updates several rows to use the new promotion category which extends beyond 50 characters.
USE AdventureWorksDW2012
GO
ALTER TABLE
[dbo].[DimPromotion]
ALTER COLUMN
[EnglishPromotionCategory] NVARCHAR(100);
GO
UPDATE
dbo.DimPromotion
SET
[EnglishPromotionCategory]='Promotion category to beat all other promotion categories for ever and ever'
WHERE
[EnglishPromotionCategory]='Customer';
GO
At this point, we have added a new promotion category, so we need to reprocess that dimension. Unfortunately, when we run the Process Full Option, the process will halt with a binding 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," as shown below. The error message does give us some clue as to what the problem is, but it is still somewhat ambiguous as to exactly what is wrong. We do know the problem is with the Promotion Category attribute, and if we just completed the above change, we would know we have more work to do. However, in some organizations, you may not be in charge of the back end database, so you will need to know where to look in the backed database for changes.
SSAS does have a refresh feature to push through structural changes to the DSV from the root database tables. To initiate the refresh, you must open the SQL Server Data Tools (SSDT), and then open the cube or OLAP project in question, AdventureWorksDW2012Multidimensional in this example. Next open the DSV and then click the Refresh Data Source View button (which looks very similar to the cube and dimension processing button). The refresh checks the underlying database for changes; finally, the refresh option confirms which fields and items are changed or added as illustrated below.
Now that we updated the DSV, we should be able to process our dimension with these updates, right? Unfortunately in many instances, the following error persists and for whatever reason, the DSV changes do NOT flow through to a dimension containing the field that was changed.
We need to review the dimension properties in order to fix the issue; to do so, double click on the dimension within the Solution Explorer, Promotion for our example. Once the dimension is open, clicking on Promotion Category field and opening up the properties window shows that the DataSize field is still showing a size of 50 for both the KeyColumn and NameColumn. Thus we also need to make the field size changes here too.
As displayed next, we must update the Datasize to 100. If the KeyColumn and NameColumn fields are sourced from the same field, then both fields will need to be updated. I often see folks forget to update the NameColumn DataSize field.
After making these changes, we now have success in processing the dimension as illustrated below.
The changes we have made up to this point have been to the dimension which is displayed in solution explorer. In my past experience, I have seen some situations where the changes made to the dimension structure in the Solution Explorer are NOT flowing through the actual Cube Dimensions that have been added to an individual cube. In those cases, you must open up the cube by double clicking on the cube in the solution explorer which opens the Cube Structure tab. Next, as shown below, navigate to the Dimensions window and expand the dimension in question by clicking on the plus sign next to the dimension which requires changing. Finally, click the Edit Dimension link, Edit Promotion for this example.
From this point, you would follow the same instructions outlined earlier in this tip in order to change the field's DataSize properties.
Of course, we just reviewed making a change to the DataSize; however, changes to the data type also may occur and would need to follow the same process. As a final point, on a few occasions, even after making the described changes to the dimension at both the solution and cube level, processing the dimension still results in binding errors. In those cases, the only solution I have found is to remove the dimension from the cube and then add it back (note you do not have to delete the dimension, just remove it from the cube). Unfortunately, that means reprocessing the full cube!
Conclusion
Modeling a new data warehouse always requires much persistence and questioning of end users. Inevitably, even after making an unprecedented effort to get the data model correct and current, changes will occur. Often these changes lead to backend database alterations including adjustments to field data sizes. Once the changes are made to the backend database, they must be moved to the DSV in Analysis Services. SSAS provides a tool to refresh the DSV metadata, but the DSV "refresh" does not make it way to the dimensions which include the impacted fields. Without making changes at the dimension level, binding errors will result when attempting to process the dimension and cubes. In order to alleviate these issues, the dimension, both at the solution and cube level, must be updated. Specifically, both the KeyColumn and NameColumn must be updated for the field in question. Once these updates are made, most often you will now be able to process the dimension and ultimately the cube without error.
Next Steps
- DSV Refresh supported changes - http://technet.microsoft.com/en-us/library/ms174881.aspx#bkmk_changlist
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-05-23
