{"id":421,"date":"2013-04-30T07:39:26","date_gmt":"2013-04-30T12:39:26","guid":{"rendered":"http:\/\/www.cognitiveinfo.com\/?p=421"},"modified":"2013-04-30T07:39:26","modified_gmt":"2013-04-30T12:39:26","slug":"how-to-fix-usage-based-optimizer-connection-errors-in-ssas-2012","status":"publish","type":"post","link":"http:\/\/cognitiveinfo.com\/?p=421","title":{"rendered":"How to fix “Usage Based Optimizer” connection errors in SSAS 2012"},"content":{"rendered":"

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.<\/p>\n

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.\u00a0 That database table, then logs info about the queries including the name of the server, SSAS database, cube and measure group.\u00a0 So it really forms a circular reference pointing all the way back around to the project in which you are working.<\/p>\n

\"SSAS<\/a>
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.<\/figcaption><\/figure>\n

There are a few basic things to check<\/strong>:
\n1. 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.
\n2. Check connection strings on your SSAS server configuration page.
\n3. Check SQL permissions on the database where SSAS is trying to log the queries.
\n4. Query the log table to make sure it has results which match the cube and measure group.
\n5. Fix the log table entries if you have moved the logs from Production down to Development.<\/p>\n

Moving Between Environments:<\/strong><\/p>\n

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.\u00a0\u00a0 The MSOLAP_ObjectPath field contains a fully qualified reference to the SSAS server, database, ObjectID (Cube) and Measure Group.<\/p>\n

\u00a0 MSOLAP_ObjectPath explanation:\n\u00a0 SERVER\u00a0\u00a0 .Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .ObjectID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .MeasureGroupName\n\u00a0 COGNITIVE.Analysis Services Tutorial L10.Adventure Works DW.Internet Sales\n\nTo change server name references in the log table, just run a quick update script:\u00a0 \n  USE SSASLogsCognitive \n  GO\n\u00a0 UPDATE dbo.OlapQueryLog\n\u00a0 SET MSOLAP_ObjectPath = REPLACE (MSOLAP_ObjectPath, 'COGNITIVEPROD.','COGNITIVEDEV')\n\u00a0 GO<\/pre>\n
Common Error Messages:<\/strong>
\n“No Query Results…”\u00a0 <\/em><\/p>\n
If the table is empty, check permissions to make sure the SSAS Service account can write to that database.<\/p>\n
If the table has results, check the MSOLAP_ObjectPath and be sure the measure group has some results.<\/p>\n
“Cannot connect to database… Host actively refused connection”<\/em><\/p>\n
Check the connections listed above, starting from your Project to your SSAS Server to SQL.<\/p>\n
References:<\/strong><\/p>\n
http:\/\/www.jenstirrup.com\/2009\/04\/optimising-aggregations-in-analysis.html<\/a><\/p>\n
http:\/\/www.sqlchick.com\/entries\/2012\/5\/5\/why-is-my-ssas-query-log-table-empty.html<\/a><\/p>\n
Handy Tool for Aggregations:<\/p>\n
http:\/\/bidshelper.codeplex.com\/wikipage?title=Aggregation%20Manager&referringTitle=Documentation<\/a><\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":2,"featured_media":424,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[52,97,100,109],"class_list":["post-421","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ssas","tag-errors","tag-sql-server-2012","tag-ssas","tag-usage-based-optimizer"],"_links":{"self":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=421"}],"version-history":[{"count":0,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/421\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/"}],"wp:attachment":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=421"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}