SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: In SSAS 2008 I am getting an error message when generating usage based aggregations: "Internal error: The parameter is not valid."

A: Usage based optimization (UBO) reads usage data from OlapQueryLog table that is defined in the SSAS parameters. By default that table name is OlapQueryLog. When you are trying to generate Usage Based aggregations and as a result getting error message: ""Internal error: The parameter is not valid.", that means that some of your records in the OlapQueryLog table does not match current SSAS database structure. This could happen for different reasons, that we will not talk about here.

To confirm that this is your problem, select all record from OlapQueryLog table for one measure group:

SELECT [MSOLAP_Database]
,[MSOLAP_ObjectPath]
,[MSOLAP_User]
,[Dataset]
,[StartTime]
,[Duration]
FROM [dbo].[OlapQueryLog]
WHERE MSOLAP_ObjectPath LIKE '%Fact Internet Sales%'

From the result set select data from the field Dataset and copy it to the notepad (make sure WordWrap is disabled). Do visual record inspection. In the correct result set all records will have comma symbols in exactly the same columns. If there is at least one record where comma symbol is missalligned, that means that you have corrupted records.

To fix this:
- Delete records from the OlapQueryLog table and start collecting query usage information again
- Or investigate your OlapQueryLog table and delete just the incorrect records. This is possible either by deleting records that are older than certain date or maybe you can hand pick just incorrect records.

In SQL Server 2008 Analysis Services with SP1, error message looks like this:

TITLE: Aggregation Design Wizard
------------ --------- ---------
Internal error: The parameter is not valid.
To restart the process, resolve the problem, and then click Start.
------------ --------- ---------
BUTTONS:
OK
------------ --------- ---------

Without SP1, error message looks something like this:

TITLE: Aggregation Design Wizard
------------------------------
Errors in the aggregation designer. The '2,0000,000,000,0000,00' usage based optimization query is not valid. For the appropriate format, refer to the product documentation.
To restart the process, resolve the problem, and then click Start.
------------------------------
BUTTONS:
OK
------------------------------