Skip to main content

Connecting to Excel via the AtScale Add-In

You can use AtScale's branded add-in to connect to Microsoft Excel via SSO.

Note

To obtain the AtScale add-in for Excel, reach out to AtScale Support.

Prerequisites​

  • AtScale must be configured with either Microsoft Entra ID using OpenID Connect or Okta using SAML.

  • The Store Tokens option must be configured for your IdP.

  • The Identity Broker must have an atscale-excel-plugin client configured with the following:

    • The offline_access client scope.
    • Valid Redirect URIs set to https://login.microsoftonline.com/common/oauth2/nativeclient.

  • All users who will use the add-in to log in to AtScale from Excel must have the offline_access and query_user roles assigned in the Identity Broker (note that these are automatically granted to admin users), as well as the necessary runtime permissions for the catalogs they will connect to.

  • Any federated users must have the offline_access access role assigned in the Identity Broker.

  • You must be using a supported version of Excel. For AtScale's currently supported versions, see AtScale Supported Tools and Platforms.

note

If you need to install a new version of the add-in, you must first fully uninstall any previous versions. This includes any versions from installer-based AtScale.

Install the AtScale certificate​

First, an AtScale admin needs to install the AtScale certificate:

  1. Extract the .zip file that corresponds to your OS architecture (x64, x86).
  2. Right click the Installer.Containers.msi file and select Properties.
  3. Click Digital Signatures, select the certificate, then click Details.
  4. Click View Certificate > Install Certificate.
  5. Select Local Machine.
  6. Select the Place all certificates in the following store option, then select Trusted Publishers.
  7. Click Ok, then click Next.
  8. Click Finish.

Install the AtScale add-in​

Next, an AtScale admin needs to install the Excel add-in:

  1. Extract the .zip file that corresponds to your OS/processor (x64, x86).

  2. Double click the Installer.Containers.msi file.

  3. Click Next.

  4. Complete the following fields, then click Next.

    • Redirect URL: The redirect URI configured for the Identity Broker client (atscale-excel-plugin). By default, this is https://login.microsoftonline.com/common/oauth2/nativeclient.
    • Client ID: The Identity Broker client ID. By default, this is atscale-excel-plugin.
    • Authorization URL: The Identity Broker's authorization URL. By default, this is <atscale_host>/auth/realms/atscale/protocol/openid-connect/auth.
    • Token URL: The token URL for your IdP in the Identity Broker. By default, this is <atscale_host>/auth/realms/atscale/protocol/openid-connect/token.
    • Servers: The server name, which needs to be updated in all the available connections in Excel sheet. You can include multiple separated by spaces. For example, <atscale_host>/engine/xmla.

  5. Click Next, then click Install.

If the installation was successful, a folder for the plugin appears in your Program Files folder. The next time you open Excel, a Login with AtScale tab appears in the ribbon.

Configure a connection to AtScale in Excel​

Before other users can use the add-in, a local user with the query_user role needs to create a new Excel workbook and configure a connection to AtScale in it.

To configure a connection to AtScale in Excel:

  1. In Excel, open a blank workbook.

  2. On the Data tab, select Get Data > From Other Sources > From Analysis Services.

  3. In the Connect to Database Server dialog, enter the connection information, then click Next.

    • Server name: Enter <atscale_host>/engine/xmla.
    • Log on credentials: Select Use the following User Name and Password and enter your AtScale username and password.

  4. In the Select Database and Table dialog, select an AtScale catalog as the database and a model as the table.

  5. Click Next.

  6. In the Save Data Connection File and Finish dialog, select Save password in file. Optionally, you can enter a description and name for the AtScale model you're connecting to.

  7. Click Finish.

  8. Share the report with other users who need access to it. These users will be able to authenticate with AtScale using their SSO credentials, as described in the following section.

Authenticate with AtScale from Excel​

Excel users with whom the report has been shared can authenticate with AtScale from Excel to run queries.

To authenticate with AtScale from Excel:

  1. In Excel, click on the Login with AtScale tab in the ribbon. The AtScale login dialog appears.
  2. Click Refresh All.
  3. Enter your credentials and log in.

If everything worked, all connections with the configured servers update to use the newly signed in user and are automatically refreshed.

Troubleshooting​

An ExcelPluginSSO\Logs\ExcelPluginLog.txt file with logs from the add-in is kept updated in the My Documents folder. The log can be used to debug issues and shared with AtScale Support.

Below are a few common issues you may encounter, as well as instructions for resolving them.

The add-in does not appear in the Excel ribbon​

Possible solutions:

  • Ensure the add-in has not been disabled by Excel:

    1. In Excel, go to File > Options > Add-ins.
    2. In the Manage select field, select Disabled add-ins, then click Go.
    3. Select the LoginToAtScale add-in and click Enable.
    4. Close and re-open Excel and see if the add-in appears.

The browser opens but the sign in page doesn’t load or shows an error​

Possible solutions:

  • Ensure you have set the correct params, especially the authentication URL and client ID.
  • If the Identity Broker reports an Invalid parameter: redirect_uri error message, ensure you have the correct Redirect URI set in the Identity Broker client settings, as well as the correct redirect URL param.
  • Ensure cookies are enabled in your browser.
  • Ensure your AtScale server is accessible from your computer.

After signing in, a "No connections found with the specified host name" error appears​

Possible solutions:

  • Ensure you have at least one connection to the AtScale instance configured in Excel.
  • Ensure you have set the correct servers param.

After signing in, a "Connection Test Failed For Updating the Host" error appears​

Possible solutions:

  • Ensure your IDP is configured correctly based on the steps above. This is usually associated with 400 Bad Request errors reported in the log file.

    • Ensure the Store Tokens option is enabled in the IDP settings.

  • Ensure the client is correctly configured.

    • Ensure the atscale-excel-plugin client has the offline_access scope added to it.

  • Ensure the user has the correct roles and has runtime permissions to interact with the catalogs. This is usually associated with 401 Unauthorized errors reported in the log file.

    • Ensure the user has the offline_access and query_user roles assigned in the Identity Broker.
    • The user should have runtime permissions to interact with the catalog.

  • Ensure there are connections to the AtScale servers configured in the Excel workbook.

    • If there are, the add-in will prompt for login when the workbook opens.
    • If there aren’t, it won’t prompt for login, so this will indicate some URL may be misconfigured.

After signing in, trying to interact with the connection throws a "The query did not run, or the database table could not be opened or a Excel cannot find OLAP cube" error​

Possible solutions:

  • Ensure the user has the correct runtime permissions to interact with the catalogs.