Cognitive Information

How to fix “Usage Based Optimizer” connection errors in SSAS 2012

The Usage-Based Optimizer can be a very powerful tool for calculating aggregations, when it works. When it doesn’t work perfectly, then it can be frustrating to figure out. The devil is in the details. If you move data between environment, then you have even more details to find and fix.

Just for a quick overview, in Visual Studio for 2012 (BIDS for previous versions) your project properties deployment will point at an SSAS Server Instance which in turn has to have query logging configured to point to a SQL Server database.  That database table, then logs info about the queries including the name of the server, SSAS database, cube and measure group.  So it really forms a circular reference pointing all the way back around to the project in which you are working.

SSAS Usage-Based Optimizer Connects from the SSAS Project, to the server, gets the configurations and connects to SQL Server logging database.  The names have to match all the way around the circuit.

SSAS Usage-Based Optimizer Connects from the SSAS Project, to the server, gets the configurations and connects to SQL Server logging database. The names have to match all the way around the circuit.

There are a few basic things to check:
1. Check that the SSAS Database name equals the Object ID. If you have renamed the .cube file in the SSAS project, then these can get out of sync.
2. Check connection strings on your SSAS server configuration page.
3. Check SQL permissions on the database where SSAS is trying to log the queries.
4. Query the log table to make sure it has results which match the cube and measure group.
5. Fix the log table entries if you have moved the logs from Production down to Development.

Moving Between Environments:

If you log queries in Production and move the log to Development, you will have to run some update statements to fix your log table.   The MSOLAP_ObjectPath field contains a fully qualified reference to the SSAS server, database, ObjectID (Cube) and Measure Group.

  MSOLAP_ObjectPath explanation:
  SERVER   .Database                      .ObjectID          .MeasureGroupName
  COGNITIVE.Analysis Services Tutorial L10.Adventure Works DW.Internet Sales

To change server name references in the log table, just run a quick update script:  
  USE SSASLogsCognitive 
  GO
  UPDATE dbo.OlapQueryLog
  SET MSOLAP_ObjectPath = REPLACE (MSOLAP_ObjectPath, 'COGNITIVEPROD.','COGNITIVEDEV')
  GO

Common Error Messages:
“No Query Results…” 

If the table is empty, check permissions to make sure the SSAS Service account can write to that database.

If the table has results, check the MSOLAP_ObjectPath and be sure the measure group has some results.

“Cannot connect to database… Host actively refused connection”

Check the connections listed above, starting from your Project to your SSAS Server to SQL.

References:

http://www.jenstirrup.com/2009/04/optimising-aggregations-in-analysis.html

http://www.sqlchick.com/entries/2012/5/5/why-is-my-ssas-query-log-table-empty.html

Handy Tool for Aggregations:

http://bidshelper.codeplex.com/wikipage?title=Aggregation%20Manager&referringTitle=Documentation

 

 

Tags: , , ,

Sorry, the comment form is closed at this time.

Business Intelligence & Data Warehouse Consulting

%d bloggers like this: