SSAS (SQL Server Analysis Services) Tabular – Error when Processing – OLE DB or ODBC error: Server: The operation was cancelled by the user ..
The above error is caused when processing an SSAS Tabular database.
NOTE:This only applies to when using MDX and an SSAS OLAP Cube to get your data into SSAS Tabular.
NOTE 2:The reason that I use MDX and the SSAS OLAP Cube to get data in is for 2 reasons.
· The first is that as we know it is always a lot quicker and more efficient to get data out of SSAS OLAP.
· Second is due to querying the SSAS OLAP, the underlying data warehouse can then continue importing data. So that there is no load on our data warehouse.
In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database and select the following:
· Get the Internet Sales Amount for a specific day
o NOTE:I did create a Member value, which originally generated the error
§ If you have to just use a standard measure this will NOT occur.
· Use a filter to filter out our unwanted data for the specific date.
You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330
· I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012
Below are the details as to why this happened and what caused it to happen. I will show this in a series of steps below with images to better explain.
I will also show how to resolve this error so that it will not happen in the future.
1. First we will start with our MDX Query so that we can get our dataset, which when using SSAS Tabular will get imported with the valid columns.
Withmember [Measures].[CurrentDayAverage] as
([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])
Select nonempty {[Measures].[CurrentDayAverage]} on 0
, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 0)} on 1
from [Adventure Works]
a. And here is the Results below:
b. 
2. Next I have modified the query using the filter so that our data has changed. This is so that if the next time the processing ran, we would get no data returned
a. NOTE: As you will see below, our MDX query is valid and it does run successfully.
Withmember [Measures].[CurrentDayAverage] as
([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])
Select nonempty {[Measures].[CurrentDayAverage]} on 0
, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 15000)} on 1
from [Adventure Works]
b. If you look above I have changed the filter from > 0 to > 15000
c. And here is the Results below:
d. 
e. NOTE: Because in the Results Tab there is no column information returned as per the screenshot above when processing the SSAS Tabular it would error with the following:
i. OLE DB or ODBC error: Server: The operation was cancelled by the user ..
f. And if we had to click on the Message Tab we would see that the MDX Query was successful
g. 
3. Now the query below is how I modified it, so that even if there was no data returned it would still show the columns.
Withmember [Measures].[CurrentDayAverage] as
([Date].[Date].&[20050701] ,[Measures].[Internet Sales Amount])
Select {[Measures].[CurrentDayAverage]} on 0
, nonempty {Filter(([Date].[Date].&[20050701]),[Measures].[Internet Sales Amount] > 15000)} on 1
from [Adventure Works]
a. And here is the Results below:
b. 
c. NOTE: What I did was to remove the nonemptyfrom the query in the select on the columns (0), in order for it to display the column
d. NOTE 2: In all the blogs I have read, it is recommended practice it always include the nonempty as part of the query. This speeds up the query and in most cases returns the required information.
4. Now when we process our SSAS Tabular database it will not error.
a. 
