Data Provider - SQL Server Analysis Services (SSAS)

Modified on Mon, 10 Jun 2013 05:22 PM by Steve C. — Categorized as: Data Connectors, OLAP

Click to return to: Documentation | Connecting to Data | Creating a Data Connector


Overview

Dundas Dashboard can connect to the following versions of SQL Server Analysis Services (SSAS):


Tip: Dundas Dashboard can also connect to SSAS 2012 tabular model instances - the steps are the same as for a multidimensional database.

Connecting to SSAS

From the General tab of the Data Connector Designer screen, configure the data provider for SQL Server Analysis Services as follows:

  1. Click inside the Server name auto-complete box. A discovery of available servers on your network is initiated. A dropdown menu will appear if one or more servers are found. Choose a server name from the menu or type the name of the SQL Server Analysis Services instance in the Server name box.
  2. Click inside the Database name auto-complete box. A discovery of available databases from your SQL Server Analysis Services instance is initiated. A dropdown menu will appear if one or more databases are found. Choose a database name from the menu or type the name of a database in the box provided.
  3. Choose the Impersonation option from the following choices:
    • The default option is None, which means that impersonation will not be used.
    • The Connect using current Windows account option is available if you have turned on Windows Authentication login via the Dundas Dashboard Configuration File (i.e. the authenticationMode element in the configuration file is set to either Windows or LocalAndWindows). Use this impersonation option to pass in the username of the current Windows user to SSAS via the EffectiveUserName property of the SSAS connection string.
    • Select the Connect using Dashboard Groups option if you want to connect to SSAS by passing in SSAS Role names based on the names of Dundas Dashboard Security Groups of which you are a member. An example SSAS impersonation scenario is as follows:
      • In Dundas Dashboard, suppose you belong to a security group called SalesTeam.
      • In SSAS, you have defined a corresponding role which is named SalesTeam as well.
      • In Dundas Dashboard, create a data connector to SSAS and select the Connect using Dashboard Groups option.
      • When Dundas Dashboard connects to SSAS, SalesTeam is passed in as a SSAS role.
      • From Dundas Dashboard, you will be able to see only the data that the SalesTeam role is permitted to access in SSAS.

For more details on SSAS roles, including limitations on acceptable characters in role/group names, see Dundas Dashboard and SSAS Roles.

For more details on managing Dundas Dashboard users and security groups, see the article on Administration.

SQL Server Analysis Services (SSAS) provider option.

SQL Server Analysis Services (SSAS) provider option.


Microsoft PowerPivot

You can also use the SSAS data provider to connect to Microsoft PowerPivot Excel (XLSX) files hosted on a SharePoint 2010 server. In this case, just set the Server name to the URL of the XLSX file on the SharePoint server. For example:

http://spserver/PowerPivot/powerpivot1.xlsx

Here is a more-detailed list of steps for connecting to PowerPivot files:

  1. Create a new data connector.
  2. In the Connect as fields, enter the credentials for a user who has access to the PowerPivot file. Note that this may need to be a service user/account.
  3. Choose Microsoft SQL Server Analysis Services 2005 or above as the Provider.
  4. In the Server name box, enter the URL that points to the PowerPivot XLSX file on your SharePoint server/farm.
  5. The Database name dropdown list should be automatically populated with available PowerPivot files. Select the one you want.
  6. Click Save to complete the connection.

Connecting to PowerPivot files.

Connecting to PowerPivot files.


User-based filtering using custom attributes

Another way to implement user-based filtering of data is to pass a custom attribute value (which contains user-specific information) into the SSAS connecting string by using the CustomData advanced property of a SSAS data connector. Your SSAS administrator can then set things up so that different data can be returned by Analysis Services depending on the custom attribute value (which is extracted from the connection string).

For more details, see the example in SQL Server Analysis Services - Advanced Properties.

Notes


Related topics


Click to return to: Documentation | Connecting to Data | Creating a Data Connector