Sql-server – SSAS and Visual Studio Optimization Wizard does not work

sql-server-2017ssasvisual studio

We have setup and logged data on our Production Server, but when we try to run the Optimizations from within Visual Studio it crashes immediately with

TITLE: Microsoft Visual Studio
------------------------------

The system cannot locate the object specified.


------------------------------
BUTTONS:

OK
------------------------------

The tables all exist, right where they should, with DBO as the owner. The logged in user has the rights to read the data.

The extra information on the error says:

Program Location:

   at EnvDTE.IVsExtensibility.RunWizardFile(String bstrWizFilename, Int32 hwndOwner, Object[]& vContextParams)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunAddNewItemWizard(String pszItemName, Int32 cFilesToOpen, String[] rgpszFilesToOpen, IntPtr hwndDlg)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunWizard(String wizardName, String newItemName)
   at Microsoft.DataWarehouse.DataWarehouseUtilities.RunWizard(String wizardName, IServiceProvider serviceProvider, Object[] stackParameters)
   at Microsoft.AnalysisServices.Design.AnalysisServicesUtilities.InvokeUsageBasedOptimizationWizard(MeasureGroup measureGroup, Partition[] selectedPartitions)
   at Microsoft.AnalysisServices.Design.Aggregations.AggregationsEditor.OnUBO(MenuCommand menuCommand, CommandHandlingArgs args)

The OLAP tables have the default names of dbo.OlapQueryLog and there is recent data in it. Im not sure what else I need to do to make this activate, but I never get as far as the wizard window.

Any ideas?

Best Answer

The answer isn't obvious.

You need BIDS Helper installed to your visual studio.

There are four buttons in the PARTITIONS tab one of which is the "Edit Aggregations" button. If it isn't there, you dont have the latest BIDS helper.

An aggregation Manager window will appear, with a treeview of your measures structure. Drill down to the Aggregations Design folder that appears in that tree, and right click it, or any of its children. Click the "Add Aggregations from Query Log" menu item, and you will be given a window where you can even edit the SQL that selects your Query log item.

This will allow you to achieve exactly the same thing that the Aggregations tab currently isn't.

For the record, the Aggregations tab issue remains, but is irrelevant as this method does everything we need.

Related Question