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.
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