Enable logging for usage based optimization.

Question

I need to enable logging for usage based optimization.  I've set all the properties on the SSAS Server,  but get nothing when running queries in Production.

Log \ Query log \ CreateQueryLog Table = true
Log \ Query log \ QueryLogConnection String = [my connection string]
Log \ Query log \ QueryLogSampling = 10
Log \ Query log \ QueryLog TableName = OLAPQueryLog

Is there a setting I'm missing somewhere?  Could it possibly be a permissions issue with the service account?

---

John Schroeder

Answer 1

Check whether the OLAPQueryLog was created, and if yes go back to the properties and set the CreateQueryLogTable to false.

Answer 2

Follow the below steps

1.       Set CreateQueryLogTable to true
2.       Set the value of QueryLogConnectionString appropriately
3.       Set the value of QueryLogSampling to the appropriate value for your environment
4.       Set the value of QueryLogTableName appropriately.
5.       Click "OK"

Check out the below link for details,

http://geekswithblogs.net/ManicArchitect/archive/2010/11/02/142563.aspx

Answer 3

I already did all this before posting and still get  nothing.  The table is never created.

---

John Schroeder

Answer 4

The DBA found this error message in the SQL log:

The description for Event ID '-1054801917' in Source 'MSSQLServerOLAPService' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them.  The following information is part of the event:'OLE DB error: OLE DB or ODBC error: CREATE TABLE permission denied in database ['MyDatabase'].; 42000.'

---

John Schroeder

Answer 5

So, it's a permissions issue as the account under which SSAS instance is connecting to your DB has no permissions to create a table in the database.

You have 2 options.

1. If you use windows authentication in the connection string to the log database, you need to grant appropriate rights to the account under which the SSAS instance is running to the log database.
2. Create a SQL User, grant him appropriate rights to the SSAS database and use that SQL user as SQL Authentication in the Log Database connection string.

The account needs:

• CREATE TABLE permission in the LOG database (at least for the time when the log table is being created - when you first time confirm the options dialog with CreateQueryLogTable = true
• INSERT permission to the table after it is being created to be able to insert the log records.

If you have a separate database for the OLAP Query Log, you can make the account a member of the db_owner database role. Then it will have a complete control over that db and as the db will be used only for the OLAP Query Log, then there is also no security issue.

Answer 6

The service account SSAS runs under has all the necessary permissions, but the create table is still being denied.   We have not tried SQL authentication yet.

---

John Schroeder

Answer 7

Seems, that it doesn't have all the necessary rights based on the error message. BTW, the SQL Server Instance is a Local Instance, or is running on another machine? Also The Service account is a Local Account or Domain Account?

Answer 8

Thanks, Pavel.  That is what I needed.  Seems SSAS was installed with Default Service Accounts that had no permissions.  From the Services app, I changed the LogOnAs to my own account (which has dbCreator permissions) and restarted SSAS.  From Management Studio, changing "CreateLogQueryTable" to "true" created the table.

---

Mario