My focus recently has been in the Business Intelligence workload of SharePoint. This has seen me involved in building OLAP cubes in SQL Server Analysis Services (SSAS). All has been going well until I recently made a change to a couple of the dimensions and when I tried to perform a Deploy I started receiving this error “OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.” associated with a number of the dimensions. If I tried running a process on the entire project things seemed ok but as soon as I cam back to the Deploy I hit the error again. The frustrating thing about trying to resolve this :
- First, this error doesn’t really give you a single place to start looking. Its hard to even tell from the output window in Visual Studio 2012, which, if any, dimensions the error is associated with.
- Last, there are plenty of references to this exact error online (e.g. timeouts etc.) but not a lot of answers and the answers I found don’t seem to relate to my circumstances.
The only potential clue I had was one warning line after each of the errors that referenced the a field in the dimension. I have found there are a lot of referential integrity errors across the tables and databases that make up the data source view (DSV) and turns out there were nulls in some of the fields that potentially have caused the problem. I went through where I could and created named queries to replace the tables in the DSV in an attempt to reduce possible referential integrity issues and also trim down the data to main focus of the BI work. However there was still a long list of errors when I attempted to deploy. After a lot of trial and error I found that in my situation the following process enabled my to over come the HY008 error!
- For each field in each dimension delete the field and add it back in again. This may mean you have to rebuild the hierarchy and relationship in the dimension. In at least one case I had to completely delete the dimension and rebuild it from scratch.
- Once you are happy with each dimension process the given dimension (not the whole cube).
I found after I had done this for each dimension referenced in the error output list I still had to process the overall cube (right-click on the cube in the project explorer in Visual Studio 2012 and select Process).
Once the cube processing complete successfully I was then able to perform the Deploy.