Technical: Microsoft – SQL Server – v2008/R2 – Business Intelligence Studio – Cube Browsing – Error dragging measures\attributes to view pane

Background

When Browsing Cube, one of the errors one might encounter as you add measures and attributes to the pane is one that reads:


Retrieving the COM class factory for component with CLSID 
{C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. 
(Microsoft Visual Studio)

Full Error Message

Error Text


===================================

Retrieving the COM class factory for component with CLSID {C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. (Microsoft Visual Studio)

------------------------------
Program Location:

   at Microsoft.AnalysisServices.Controls.MiscUtilities.GetMixedDataObjectForFilterAndPivotTable(DataObject dataObjectForFilter, PivotTableDataObject pivotTableDataObject)
   at Microsoft.AnalysisServices.Controls.PivotTableBoundMetadataBrowser.GetDataObject(TreeNode node)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.OnItemDrag(ItemDragEventArgs e)
   at System.Windows.Forms.TreeView.TvnBeginDrag(MouseButtons buttons, NMTREEVIEW* nmtv)
   at System.Windows.Forms.TreeView.WmNotify(Message& m)
   at System.Windows.Forms.TreeView.WndProc(Message& m)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.WndProc(Message& msg)
   at Microsoft.AnalysisServices.Browse.CubeBrowser.CubeBrowserMetadataTreeView.WndProc(Message& msg)



Error Image

 

RetrievingTheComClassFactory

 

 

Resolution

There are correlating errors on the .Net and prominent fixes includes:

Repair / Install Microsoft Office 2003 Web Components

 

The OWC track did not work for us.

Inspect MS Windows registry

Searched the Windows registry for C966837E-970C-4F4F-A91D-D81E21ABBBDB and found the stub, but could not find CLASS (CLS) branch.

VSIntegrationNativeHelpers

 

 

Tried fiddling with MS Windows Registry

Tried fiddling with MS Windows Registry, but could not find good starting data on the Internet; note that data has to match SQL Server Version, etc.

Install latest Product patch

Downloaded and Installed MS SQL Server v2008/R2 SP2.

BTW, patch is available @ http://www.microsoft.com/en-us/download/details.aspx?id=30437

 

Possible Workaround

Registry Tweaking

BTW, if you want to try to tweak your registry and see if that might work and you avoid installing\re-installing a Service Pack, here is a working version:



Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}]
@="MarshalledToIStreamDataObject Class"
"AppID"="{B2463DC8-B3FA-4BEC-945E-60219DCC6FD8}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\InprocServer32]
@="c:\\Program Files (x86)\\Microsoft SQL Server\\100\\Tools\\Bin\\Microsoft.DataWarehouse.VsIntegration.Helpers.dll"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\ProgID]
@="VsIntergrationNativeHelpers.Marshalle.2"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\TypeLib]
@="{84F2933D-1F4E-43D8-9006-372E64998B36}"



Please keep in mind that the registry entry pasted above is for MS Windows 2008/R2 SP2. Other versions of SQL Server will invariable have version specific entries.

 

Listening To

Listening to Brett Eldredge – Raymond (Video) [http://www.youtube.com/watch?v=txCUwSKo1kg]

 

 

Technical: Microsoft – SQL Server – Analysis Services / BIDS – Error – Message – Dimension [xxx] : No key attribute is defined

Technical: Microsoft – SQL Server – Analysis Services / BIDS – Error – Message – Dimension [xxx] : No key attribute is defined

Introduction

Mistakenly removed an Attribute, now when I try to build the SSAS project, I am getting an error message stating: “Dimension [xxx]: No Key attribute is defined”.

Background

Ordinarily the message should not be difficult to fix, but the Business Intelligence Development (BIDS) environment does not allow use to set the Dimension’s key.

Set-Table Dimension Properties

Interestingly enough, BIDS hides and shows various settable attributes based on which Dimensions is selected.

Dimension Properties – Time

DimensionTime

Dimension Properties – Fact

DimensionFactUsage

Dimension Properties

From the screen shots above, we can quickly see that based on which Dimension we have selected, the properties we can set varies.

Here is a quick summary as to which properties are enabled.

Item Name Item Value Explanation
AttributeAllMemberName
Collation
CurrentStorageMode Molap
DependsOnDimension Specifies the dimension that this Dimension depends on
ErrorConfiguration
ID
Language
Name
ProactiveCaching ROLAP/HOLAP/MOLAP
ProcessingGroup ByAttribute
ByTable
ProcessingMode Regular
LazyAggregations
ProcessingPriority
ProcessingRecommendation
ProcessingState
Source
StorageMode Molap Molap
Rolap
InMemory
Type Regular
Time
Geography
Organization
BillofMaterials
Accounts
Customers
Products
Scenario
Quantitative
Utility
UnknownMember Visible
Hidden
None
AutomaticNull
UnknownMemberName If not specified, the default value is “Unknown” is used
WriteEnabled False False
True

Fix

From our list above, we see that none of the properties contain the Key setting.

So how do we set a Dimension’s key:

  • Access the “Solution Explorer” panel
  • Navigate to the Dimensions tree
  • Choose the Dimension
  • Double-click on your selection
  • The Dimension Structure panel is activated
  • Access the “Dimension Structure” tab
  • Select the specific Attribute that you should be the Key Attribute
  • Right click on your selection
  • And, from the drop-down menu select “Set Attribute Usage”\Key

By the way, the options available from the “Set Attribute Usage” option are:

  • Regular
  • Key
  • Parent

DimensionKeyAttribute

Crediting

Crediting Vinuthan from Aditi Technologies

Happy

Happy that I am able to capture drop-down menu options from the free version of WinSnap.

Free version of WinSpan ( 1.1.10) available @ http://www.ntwind.com/software/winsnap/download-free-version.html

Documentation available @ http://www.ntwind.com/tutorials/how-to-capture-a-popup-menu-with-winsnap.html.

References

Technical: Microsoft – SQL Server – Analysis Services (v2012) – Deployment – Error – 08001 – No Such host is known

Introduction

While deploying a SSAS Solution, I ran into a bit hard to identify\trace error.

Errors

SQL Server Aliased Connection

Error 4 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A 
network-related or instance-specific error has occurred while establishing a 
connection to SQL Server. Server is not found or not accessible. Check if instance 
name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; TCP Provider: No such host is known. ; 08001. 
0 0 
Error	5	
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IISLogDW', Name of 'IISLogDW'.
0	0

Resolution – Changed Solution Data Sources to use actual server name.

I had created an Alias Connection by running cliconfg.exe and entering DB Connection Details:

SQLServerClientNativeNetworkUtility-IISLogDB

It seems that SQL Server Analysis Service (SSAS) does not consider SQL Server Alias Configurations.

As so we changed Solution Data Sources from using SQL Server Aliased connection to use actual server name:

DataSourceDesigner

Login failed for user

The other error we got is pasted below:

Error 4 OLE DB error: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$MSSQL2012'.; 28000; Cannot open database "IISLogDW" requested by the login. The login failed.; 42000. 
0 0


Error 5 
Errors in the high-level relational engine. A connection could not be made to the 
data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. 
0 0

Resolution – Grant SQL Server Permission

And, it was far easier to debug and address.

--create login 
create login [NT SERVICE\MSOLAP$MSSQL2012]
from windows;

--use [db-name]
use [IISLogDW]
go

--create db user
create user [NT SERVICE\MSOLAP$MSSQL2012]
	   from login [NT SERVICE\MSOLAP$MSSQL2012]
go

-grant object level read permission
grant select on [dbo].[DimTime] to [NT SERVICE\MSOLAP$MSSQL2012];
grant select on [dbo].[factSales] to [NT SERVICE\MSOLAP$MSSQL2012];

Same Error

Same Error  (in MS Event Viewer) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.


OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related 
or instance-specific error has occurred while establishing a connection to SQL 
Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQLServerBooks Online.; 08001; Named Pipes Provider: Could not open a connection to SQL 
Server [53]. ; 08001.

Same Errors (in SQL Server Profiler \ Profiling SSAS) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.



Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while 
establishing a connection to SQL Server. Server is not found or not accessible. Checkif instance name is correct and if SQL Server is configured to allow remote 
connections. For more information see SQL Server Books Online.; 08001; Named Pipes 
Provider: Could not open a connection to SQL Server [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Calendar Year' attribute of the 'Dim Time' dimension from the 'salesDimensionDW' database was being processed. Server: The current operation was cancelled because 
another operation in the transaction failed. Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB 
error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Full Date Alternate Key' attribute of the 'Dim Time' 
dimension from the 'salesDetectionDW' database was being processed.

Summary

Though Business Intelligent Development (BIDS) and SQL Server Data Tools support SQL Server Named Aliases, it seems SQL Server Analysis Services does not support SQL Server Named Aliases.

 

 

References