Error "Conversion failed While processing one of the dimensions"

[former_member181966]

All,

We are getting following error when processing the one of our dimensions ? Any clue why ? and how to fix it ?

"Conversion failed when converting the nvarchar value ""to data type smallint . Object variable or with block variable not set"

Another question , During CPM311 class we been told , it is good idea to process all dimensions and optimize the application after every data load ? What you guys think about it?

-Saquib

[Former Member]

I am not sure why you're getting the error. It could be these is invalid data in the dimension.

As for processing the dimensions and optimising after every data load. That seems to be seriously over the top. Unless you're changing the dimensions, there is no reason whatsoever to process any of them. (Let alone all of them!).

As for optimizing the application, this is also not necessary after every data load. The data load process will by default process the application and the data will be available for reporting. Where this could be necessary is when there is a huge amount of data being loaded. It may be necessary to optimize, but seeing as dataloaded through the import packed, it is loaded into the FAC2 table which is a MOLAP partition in the MSAS cube and should be quicker in processing.

Optimizing makes the biggest difference when is empties the writeback FACTWB table which contains data that is captured through input schedules and goes into the RealTime ROLAP partition. If this is how all the data is being collected, then yes, at the end of the day, by all means optimise. I think you shouldn't have more than 100 000 rows in the writeback table (please correct me here if I am wrong).

(I am assuming BPC 7.0 MS here, not Netweaver version)

Tim

[former_member181966]

Thanks you guys for your valuable inputs.

Our SQL DBA was able to help us in resolving the issue that we were having in processing the Location dimension. The error was caused due to a special character in the Scaling column.

We are good now. BTW we are on BPC5.0.

Thanks,

Saquib

[Former Member]

Please, could you explain how you solved the problem through your SQL DBA? I have the same problem. Thanks.

[Former Member]

Hello,

Processing failed it's stated in the error "converting the nvarchar value ""to data type smallint " - you have empty value somewhere...

As for processing after each load, I don't think it's a good idea because you bring appset down during processing and processing might take quite some time (depends on the amount of data). On another hand, you have to process in order to have your reports up to date. Personally, we process dimension daily. However, sometimes users request processing during business day.

Hope that helps,

Akim