Excel Live Reports

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Excel Live Reports are refreshable XLS workbooks backed by a connection to a query that can be updated from Excel with a single click. This feature enables you to:

  • Unite the Alation query writing and cataloging capabilities with Excel’s formulas and charting to explore data more deeply on your own.

  • Share results with your colleagues so they’re empowered to be truly self-service when they want the most up-to-date numbers. Your colleagues who neither know SQL nor have a username for the database can see the latest figures without needing to ask you to run the query again.

Note

Excel Live Reports don’t work with queries that include query form variables.

Security of Excel Live Reports

When you create an Excel Live Report, your database credentials are stored in the Excel file in an encrypted format. People who have access to the Excel Live Report can run the query from the Excel file. This sends your encrypted database credentials to Alation and triggers the execution of the query. The query results are then sent back to the Excel file. This means that anyone who has access to the Excel file can access the data that the query returns.

Because of this, starting in Alation 2024.1.4, Excel Live Reports are disabled by default. If you want to use Excel Live Reports, you must enable them. See below for instructions.

Warning

Be sure to share the Excel file only with people who should have access to the data source.

Enable Excel Live Reports

Starting in Alation 2024.1.4, Excel Live Reports are disabled by default. Upon upgrading to 2024.1.4, Excel Live Reports will be disabled, and existing reports will no longer work until you enable the feature.

Customers using self-managed instances of Alation can enable this feature by setting the parameter alation.compose.live_report to true in alation_conf. For help with alation_conf, see Using alation_conf.

Note

Alation Cloud Service customers can request server configuration changes through Alation Support.

Create an Excel Live Report

To create an Excel Live Report:

  1. Open the query in Compose.

  2. Click Sharing & Access > Download Excel Live Report. The Create Excel Live Report dialog appears.

    ../../../_images/ExcelLiveReports_SetConnection.png

  3. To learn more about Excel Live Reports, hover over the information icon.

  4. Establish a connection to the data source:

    1. Click the menu button next to Choose a Connection and select the connection you want the Excel Live Report to use. When you hover over the connection name, you’ll see the connection URI in a popup so you can be sure you’re selecting the right connection.

      Note

      If you want to add or edit connection information, see Working with Data Source Connections.

    2. Click the menu button next to Connect as (Select User) and select the user you want the Excel Live Report to connect with.

  5. Click the Download button. The Excel document will download to your computer.

  6. Open the Excel file, then click Enable Content. This will enable the connection to the data source.

    ../../../_images/ExcelLiveReports_EnableConnection.png

  7. Navigate to the Data tab and click Refresh All. The data will appear in the spreadsheet.

    ../../../_images/ExcelLiveReports_RefreshAll.png

Open the Query from Excel

If you already have an Excel Live Report, you can open the query in Alation from Excel. This is useful if you want to edit the query and get new results in the Excel spreadsheet.

To open the query in Alation, click the link in the top row of the Excel spreadsheet.

../../../_images/ExcelLiveReports_LinkToQuery.png

Refresh the Query Results

If the query has been edited, you can get the new results in the Excel spreadsheet. To get the new results, click Refresh All on the Data tab.

../../../_images/ExcelLiveReports_RefreshAll.png