November 1, 2011 at 3:12 pm
Greetings all!
So I have a bit of a tricky problem.
I have a cube, it has a dimension that has a composite key (yes this key is unique) and for all columns with the exception of one, works perfectly fine.
I have a field called "Purchase Order Number" hereafter to be named 'po#', that is giving me grief. It is telling me there is a duplicate value and errors in processing.
Here are the issues with that...there are THOUSANDS of duplicates but only this one fails. I know this because I deleted the offending rows out of the table and the cube processes just fine. Here is the fun part, if I delete it from the table, process full, then add it, and process update, it works just fine. But, if I try to process full again, it will fail.
There are NO nulls on the table...how do I know? Easy, every single column is not null in the definition, but just to be sure I ran a is null on every single column. Also it is not another field for the simple fact that if I change the offending value to something else, it also works.
It is just a simple number, we will just call it '1234' for now, (which ironically it is a 4 digit number). I have verified there are other 4 digit numbers similar to this, and they are fine. There is no extra spaces on the left or right. Just 4 regular numbers. I can even change it to another sequence of numbers and it works.
I am seriously beginning to think I am losing my mind 🙂
Any ideas?
Link to my blog http://notyelf.com/
November 1, 2011 at 4:24 pm
So after playing around for an hour or so with several properties for this field, I came across something that works!!
In the key column and name column expansion menu there is an option called 'trimming'. By default it is set to right. I decided to play with this until coming to the conclusion that turning this OFF works like a charm.
I have no idea why this does work however, as I had said before there are no leading or trailing spaces in the column. In fact I do an ltrim AND rtrim on the column before even inserting it into the table.
In any case it is working now. Perhaps if someone is in the know they can explain to me why its working now :). I don't like things working when I don't know why it is they are working!
Link to my blog http://notyelf.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply