(v24) Connecting to SSAS

Connecting to SSAS

This applies to: Managed Dashboards, Managed Reports

Several options are available when connecting to Microsoft SQL Server Analysis Services (SSAS) including Azure Analysis Services. You can also filter data by user.

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 drop-down 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 Symphony 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.

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.

Impersonation = Effective Username

This option is designed to work in conjunction with Windows logon and Windows accounts in Symphony. The Windows username of the current Symphony 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:

1. Set up security on the SSAS server for each Windows user.

2. Make sure Windows logon is enabled in Symphony via the Log On Modesconfiguration setting.

3. Add Windows accounts in Symphony.

4. 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.

5. Set the Impersonation field to Effective Username.

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

Impersonation = Roles

This option uses roles in SSAS to define security in the cube. In Symphony, user groups are created that mirror the names of the roles in the cube. Users in Symphony 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 Symphony based on which group that user's account is assigned to. Users will see different data displayed on dashboards, for example, depending on the Symphony user group (and corresponding SSAS role) they belong to.

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

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

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 Symphony admin interface. Custom attributes are name-value pairs of information which Symphony administrators can assign on a per-user account or per-group basis.

See Using a Security Hierarchy to Filter SSAS Data by User for details on setting up this option.

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;

For more information, see:

• Set Up Date Mapping on a Native OLAP Cube

• Using SSAS Roles Impersonation

• Using Custom Data and Custom Attributes to Filter SSAS Data

• Using a Security Hierarchy to Filter SSAS Data by User

• SSAS Data Connection Issues

• Configuration Settings