CODEWITHSUNDEEP
sql-serverssassql-server-2012cube
Raj More
Raj More

Reputation: 48024

SSAS cube processing error about column binding

This is an error message I get after processing an SSIS Cube

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.

However, it gives me no indication of what column binding is too small.

How do I debug this?

Upvotes: 7

Views: 27284

Answers (10)

Brian Vander Plaats
Brian Vander Plaats

Reputation: 2287

Alternate Fix #1 - SQL Server 2008 R2 (haven't tried on 2012 but assume this will work).

  1. Update / refresh your DSV. Note any changed columns so you can review.
  2. Open each dimension that uses the changed columns. Find the related attribute and expand the properties KeyColumns, NameColumn and ValueColumn.
  3. Review the DataSize properties for each and if these do not match the value from the DSV, edit accordingly. Property Sheet

Alternate Fix #2

  1. Open the affected *.dim file and search for your column name / binding.
  2. Change the Data Size element: <DataSize>100</DataSize>

As Esc noted, column size updates can affect the Dimension Usage in the cube itself. You can either do as Esc suggests, or edit the *.cube file directly - search for the updated attribute and related Data Size element: <DataSize>100</DataSize>

I've tried both fixes when a column size changed, and they both work.

Upvotes: 4

Esc
Esc

Reputation: 195

This error message has been driving me crazy for hours. I already found which column has increased its length and updated the data table in the source which was now showing the right length. But the error just kept popping up. Turns out, that field was used in a fact-to-dimension link on Dimension Usage tab of the cube. And when you refresh the source, the binding created for that link does not refresh. The fix is to remove (change relationship type to 'No Relationship') and re-create that link.

Upd: Since that answer seems to be still relevant, I thought I'd add a screenshot showing the area where you can encounter this problem. If for whatever reason you are using a string for Dimension-to-Fact link it can be affected by the increased size. And the solution is described above. This is additional to the problem with Key, Name, and Value Columns on the Dimension Attribute. Dimension Usage screen

Upvotes: 10

Fabio Bruna
Fabio Bruna

Reputation: 99

Running into the same problem, the answer from Esc can be a solution too. The cause is much more 'hidden' and the more obvious solutions 'Refresh' and 'Data type discrepancy check' don't do any good in my case.

I did not find a proper way to "debug" this problem.

Upvotes: 0

user3420568
user3420568

Reputation: 1

Simple thing to try first - I've had this happen several times over the years.

  1. Go to data source view and refresh (it may not look like anything happens, but it's good practice)
  2. Edit dimension. Delete the problem attribute, then drag it over again from the data source view listing.
  3. Re-process full.

As others have mentioned, data with trailing spaces can be the cause as well. Check for them: SELECT col FROM tbl WHERE col LIKE '% '

Upvotes: 0

Treubber
Treubber

Reputation: 1

I encountered the same problem, refreshing the data source did not work. I had a Materialized Referenced Dimension for the Fact Partition that was giving me the error. In my DEV environment I unchecked Materialize and processed the partition without the error.
Oddly, now I can enable Materialization for the same relationship and it will still process without issue.

Upvotes: 0

Alex
Alex

Reputation: 1

I encountered this problem. The question decided by removing leading and trailing spaces and functions rtrim and ltrim.

Upvotes: 0

RollTide
RollTide

Reputation: 302

ESC is correct. Install the BIDS Helper from CodePlex. Right click on the Dimensions folder and run the Data Discrepancy Check.

Dimension Data Type Discrepancy Check

This fixed my issue.

Upvotes: 4

Raj More
Raj More

Reputation: 48024

In my particular case, the issue was because my query was reading from Oracle, and a hard-coded column had a trailing space (my mistake).

I removed the trailing space, and for a good measure, Cast the hardcoded value to be CAST ('MasterSystem' as VarChar2(100)) as SOURCE

This solved my particular issue.

Upvotes: 0

AceAlfred
AceAlfred

Reputation: 1191

In my case the problem was working on the cube on live server. If you are working on the cube live, connecting to the server this error message pops up. But when you are working on the cube as a solution saved on the computer you do not get the error message. So work on the cube locally and deploy after making changes.

Upvotes: 0

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

  1. Open your SSAS database using SQL Server Data Tools.
  2. Open the Data Source View of the SSAS database.
  3. Right click an empty space and click Refresh
  4. A window will open and show all changes to the underlying data model.

Documentation

Upvotes: 2

Related Questions