Logon failure: unknown user name or bad password - SSAS 2012

Error Message

Internal error: The operation terminated unsuccessfully.

The following system error occurred:  Logon failure: unknown user name or bad password.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW2012', Name of 'Adventure Works DW2012'.

Description

This error occurred when process a cube, this is happening while process only, the cube can be deployed successfully. There can be many reasons why “a connection could not be made to the data source”, but the connection parameter all varied and all fine. This issue was happened in SQL Server 2012 environment.

Solution

Reason was Set Impersonation Options not set.

When creating a data source object in an Analysis Service model, one of the settings that you must configure is an impersonation option. This option determines whether Analysis Service assumes the identity of a specific Windows user account when performing operational related to connection, such loading an OLE DB data provider or resolving user profile information in environments that support roaming profiles.

(More info http://technet.microsoft.com/en-us/library/ms187597.aspx)

Set impersonation options in SQL Server Data Tools

1. Double-click a data source in Solution Explorer to open Data Source Designer.
2. Click the Impersonation Information tab in Data Source Designer.
3. Choose an option described in Impersonation options in this topic.

Set impersonation options in Management Studio

In Management Studio, open the Impersonation Information dialog box by clicking the ellipsis (...) button for the following properties of these dialog boxes:

1. Database Properties dialog box, through the Data Source Impersonation Info property.
2. Data Source Properties dialog box, through the Impersonation Info property.
3. Assembly Properties dialog box, through the Impersonation Info property.

Cheers!

How to round a MDX value

In most of the case, you may want to perform a round operation. The one of the following ways can use for this.

Cheers!

MDX query to retrieve Dimension member properties (Unique_Name and MEMBER_KEY)

Analysis Services exposes intrinsic properties on dimension members that you can include in a query to return additional data or metadata for use in a custom application, or to assist in model investigations or construction. In here I am going to explain how to retrieve the properties through the SQL Server Management Studio (SSMS) and MDX.

In below simple MDX query retrieve all the members of Employee Hierarchy, when double click on any the member, Member Properties window will pop up. In this window some of the member properties will be display as shown below.

If we want to retire all the member properties, properties need to include in the MDX query.



To get through the MDX query, the following query format can be used.

Cheers!

How to: Create an Analysis Services Project Based on an Existing Analysis Services Database

In SQL Server Data Tools (SSDT), click New Project. In the New Project dialog box, in the Installed Template pane, select Business Intelligence Projects, and then select Analysis Services.

Select “Import from Server” and follow the steps as show in the below screen shots.

Select SSA Server and Database, then click next, new project will be created with existing objects in the SSAS Database.

Cheers!