Connecting to SSAS

Contents[Hide]

1. Overview

This article provides details on options available when connecting to Microsoft SQL Server Analysis Services (SSAS) including Azure Analysis Services, and on how to filter data by user.

2. Connect to Analysis Services

See Connect to OLAP data and apply a formula for a walkthrough on creating and using a data connector for Analysis Services or other OLAP databases.

After setting the Data Provider dropdown of a data connector to Microsoft SQL Server Analysis Services, there are three options for the Windows Impersonation field:

The Server option is applicable if the user account running the Dundas BI application pool in IIS has the appropriate access to the SSAS server.
Choose Active Directory Password to enter the credentials to use for connecting using Azure Active Directory.
Otherwise, use the Specified option and enter the Windows domain credentials of a user that has access to SSAS.

The Impersonation setting has options detailed in the following sections.

2.1. Impersonation = None

This option is the default way to connect to SSAS, so you can leave the Impersonation field set to None.

This means the credentials that are specified in your data connector settings will be passed directly to the SSAS server. Every user who logs on and views dashboards based on this data connector is going to see the same data (because the same underlying Windows credentials are being used to connect to SSAS). So there is no real filtering by user possible with this option.

An OLAP cube's measures and dimensions listed and used on a dashboard

2.2. Impersonation = Effective Username

This option is designed to work in conjunction with Windows logon and Windows accounts in Dundas BI. The Windows username of the current Dundas BI user (e.g., a user viewing a dashboard) is passed to the SSAS server, where security settings that have been set up will take effect for each Windows user.

Technically, what happens is that the current Windows username is passed via the EffectiveUserName property of the SSAS connection string.

Follow these steps to use the effective username option:

Set up security on the SSAS server for each Windows user.
Make sure Windows logon is enabled in Dundas BI via the Log On Modes configuration setting.
Add Windows accounts in Dundas BI.
In the New Data Connector dialog, set Windows Impersonation to Specified and enter the domain credentials for a user which has admin rights on the SSAS server.
Set the Impersonation field to Effective Username.

Connect using effective username impersonation

The effective username option is easy to set up and use because it leverages existing cube security.

2.3. Impersonation = Roles

This option uses roles in SSAS to define security in the cube. In Dundas BI, user groups are created that mirror the names of the roles in the cube. Users in Dundas BI get added to one or more user groups, thus linking them to roles in the cube.

Roles impersonation allows you to control access to cube data on a per-user basis within Dundas BI based on which group that user's account is assigned to. Users will see different data displayed on dashboards, for example, depending on the Dundas BI user group (and corresponding SSAS role) they belong to.

The advantage of this option is that it works with all Dundas BI account types and leverages existing roles defined in your cube.

See Using SSAS roles impersonation for details on using this option.

3. Custom Data and Custom Attributes

An alternative to roles impersonation is to use the Custom Data property of SSAS data connectors in conjunction with custom attributes to secure access to the data. This method requires only one role on the SSAS side and allows you to manage data security using just the Dundas BI admin interface. Custom attributes are name-value pairs of information which Dundas BI administrators can assign on a per-user account or per-group basis.

See Using custom data and custom attributes to filter SSAS data for details on setting up this option.

4. Localization

Use the Locale Identifier field to specify the preferred locale ID. You can either indicate a static locale ID or use script to determine the appropriate ID to use.

For example, to match the data connector locale to the locale of the logon session, type the following script into the Locale Identifier field:

return currentSession.Culture.LCID;

Use the locale ID from the current logon session

Dundas BI, Managed Dashboards & Reports Support