Connecting to SSAS
This article summarizes the different ways to connect to Microsoft SQL Server Analysis Services (SSAS) including Azure Analysis Services, and to filter data by user.
2. Connect to Analysis Services
Create a new data connector from the main menu and give it a filename.
Set the Data Provider dropdown 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.
2.1. Impersonation = None
This option is the default way to connect to SSAS. It involves passing the credentials as specified by the data connector directly to the SSAS server.
Enter the SSAS Server Name and select the Database Name you want to connect to.
Leave the Impersonation field set to None. Click Submit to complete the data connector setup and start the initial discovery.
After a few seconds, go to Explore and locate your new data connector. Expand it to find a list of native OLAP cubes containing measures and hierarchies which you can drag directly to the dashboard canvas.
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.
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 it is assumed cube security for each Windows user has already been set up.
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.
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 which 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. 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.
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:
5. See also
- Connect to OLAP data and apply a formula
- Set up date mapping on a native OLAP cube
- Using SSAS roles impersonation
- Using custom data and custom attributes to filter SSAS data
- Using security hierarchy to filter SSAS data by user
- Writing data scripts with DundasScript
- SSAS Data Connection Issues
- Configuration settings
- Dundas BI - System Requirements