At BICG we are among the early adaptors of SQL 2016 and upgraded existing SQL 2014 server to SQL 2016. We went ahead and started moving away from DataZen and started creating Mobile Reports against exiting upgraded Tabular Models.

Everything started to looks good until we hit few roadblocks, first with creating DAX query and parameters in DAX DataSet, solution and workaround of which is explained here; and then some of our existing queries started giving below error.

Memory error: Allocation failure :
Not enough storage is available to process this command. .
If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

OR
Executing the query …
An unexpected exception occurred.

The query runs fine only once after the cube is processed but error out on subsequent run. Also note that the error comes up only when using DAX query and not when we are using Management Studio’s OLAP browser.

The Problem

After narrowing down the issue, we could figure out that there is problem with all the measures that were created using Count, CountA or Count of Rows expressions.
Now this clearly looks like a bug which is not yet reported to Microsoft. But we hope that soon it will be taken care in subsequent updates.

The Solution

Right now there is no permanent fix to the problem but we have rather interesting workaround.
Define a temporary measure in your DAX query which is giving the error. There is no need to use the measure, just define it and the problem will be solved.

For example, if below is your query which is giving the exception.
EVALUATE
(
SUMMARIZE (‘Sales’,
‘Region'[Country],
“CountRecords”,[Count of Records]
)
)
ORDER BY ‘Region'[Country]

Change it to :-
define measure ‘Sales'[tempCountRecord] = calculate([Count of Records])
EVALUATE
(
SUMMARIZE (‘Sales’,
‘Region'[Country],
“CountRecords”,[Count of Records]
)
)
ORDER BY ‘Region'[Country]

Notice the first line. The new temp measure is defined but not used.
Happy SQL 2016 and stay tuned for more such tips and ticks.

Leave a Comment

Your email address will not be published. Required fields are marked *