PRB: SELECT in Incremental Update Filter Might Return ODBC Error Message (271770)

CAUSE

When OLAP Services performs an incremental update on a cube partition in OLAP Services, the filter is appended to the WHERE clause of the SQL statement that is sent to the data source. If the filter contains a SELECT statement this creates a sub-SELECT in the WHERE clause.

If the partition on which the incremental update is being performed uses dimension members to define data slice information on the partition, the query is prepared as a parameterized query. Microsoft SQL Server does not allow parameterized queries to contain a sub-SELECT in the WHERE clause and returns a syntax error.

WORKAROUND

Replace the SELECT statement in the incremental update filter statement.

For automation of incremental updates, the SELECT can be placed into a separate ActiveX task that runs the query and places the results into a string that then updates the Filter property of the OLAP Data Transformation Services (DTS) Task.

For example, here is a code sample for an ActiveX task that takes the maximum Store_id of the Inventory fact table in the Foodmart database and creates a filter statement for a OLAP DTS Task named "Update Quarter 2":

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()

dim oOLAPTask		'OLAP DTS Task
dim props		'Properties Collection for OLAP DTS Task
dim oServer 		'SQL Server
dim oResult		'ResultSet
dim sMaxKey
dim sFilter



	'Create a SQLDMO Server Object.
	Set oServer = CreateObject("SQLDMO.SQLServer")

	'Open the connection to the server.
	oServer.Connect "SQLServer", "sa"

	'Select the Store_ID for the filter.
	set oResult = oServer.Databases("Foodmart").ExecuteWithResults("Select Max(Store_ID) FROM Inventory_fact_1998 ")
	
	'Retrieve the result.
	sMaxKey = CStr(oResult.GetColumnLong(1,1))
	
	'Build the New Filter
	sFilter = "Inventory_fact_1998.store_id = " + sMaxKey + " "	

	'Find the OLAP Task.
	set oOLAPTask = DTSGlobalVariables.Parent.Tasks("Update Quarter 2")
	
	'Get the Properties Collection for the OLAP Task
	Set props = oOLAPTask.Properties
	
	'Set the new Filter Expression.
	props("Filter").Value = sFilter

	'Disconnect from the SQL Server.
	oServer.DisConnect

	Main = DTSTaskExecResult_Success
End Function

NOTE: Make sure that you select the Execute on main package thread in the Workflow Properties Options tab for this task.

REFERENCES

For more information about changing the filter of the OLAP DTS Task, search on the "Changing Properties of an OLAP Services Processing Task" topic in the OLAP DTS Task Addin documentation.

For more information about DTS and ActiveX Scripting, search on the "ActiveX Scripting in DTS" topic in Microsoft SQL Server Books Online.

PRB: SELECT in Incremental Update Filter Might Return ODBC Error Message (271770)

SYMPTOMS

CAUSE

WORKAROUND

REFERENCES