AWS Business Intelligence Blog
New enhancements in Amazon QuickSight: Programmatic export to Excel format
Amazon QuickSight is a scalable, serverless, embeddable, machine learning (ML)-powered business intelligence (BI) service built for the cloud. It enables you to create and publish interactive dashboards and highly formatted reports accessible through web browsers or mobile devices. With a pay-as-you-go pricing model, QuickSight offers a cost-effective way to analyze data from various sources, gain insights, and make informed, data-driven decisions. It offers features such as automated insights, contribution analysis, one-click forecasts, and the ability to perform ad hoc analysis by letting users ask questions about the data in everyday language and get new visualizations in seconds, making it a comprehensive solution for your BI needs.
QuickSight continues to innovate and enhance the developer experience in data analytics. Recent updates to QuickSight Enterprise Edition include scheduled and programmatic export to Excel. These features aim to improve data sharing, enhance automation, and better align QuickSight with the business ecosystem that frequently depends on Excel for advanced analytics and reporting.
You can now export content to Excel workbooks by selecting multiple tables and pivot table visuals from any sheet of a dashboard on the QuickSight console via schedules or programmatically via a set of new Snapshot Export APIs. This post outlines these new functionalities and guides you through their implementation.
Key benefits
QuickSight offers the following key benefits with this feature:
- Multiple worksheet export – Supports exporting multiple tables and pivot table visuals from a dashboard as separate worksheets within a single Excel workbook. Additionally, this offers an advantage in some cases over exporting to CSV because the CSV export requires one visual per CSV file.
- Format preservation – Maintains important Excel formatting such as number and date types, text formatting, currency, column width, and headers, reducing post-export adjustments. Conditional formatting will be supported in the near future.
- Security measures – Offers anonymous user-level customization based on tag-based rules for programmatic access and row- and column-level security for scheduled reports via the QuickSight console.
The following are key features for scheduled reports via the user interface:
- Automated scheduling – Allows you to set up automatic sending of Excel workbooks at specified intervals (daily, weekly, monthly, yearly).
- Recipient filtering – Enables sending reports to specific users or groups within a QuickSight subscription, supporting up to 5,000 members for scheduled emails.
- Billing management – For authors and admins, the feature comes at no extra charge. For readers, it costs one session per report, up to a monthly maximum. Excel export is priced based on Report Units and requires a subscription to Pixel-perfect Reporting. A report unit is defined to be up to 100 pages long or 100 MB in size. PDF pricing is calculated as page count (in 100s), whereas Excel and CSV pricing is calculated as file size in megabytes (in 100s).
- Subscription control – Provides options to subscribe or unsubscribe from scheduled reports, adding a layer of flexibility and personalization.
- Security measures – Registered user-level customization incorporates row-level security (RLS) with or without tag-based rules, and column-level security (CLS) to ensure that users get the email reports only with data they have permission to access.
The following are key features for API-based exporting:
- Programmatic control – Allows for automated, code-based exporting of Excel workbooks, enabling seamless integration into existing workflows or systems.
- Dynamic customization – Enables developers to specify which tables and pivot tables to include, offering real-time customization of Excel output.
- Output control – Allows developers to select the Amazon Simple Storage Service (Amazon S3) target for the snapshot.
- Scheduled vs. ad hoc – Can be used for both scheduled regular reports and ad hoc, one-time exports based on specific conditions or triggers.
- Batch operations – Enables bulk exporting operations. This is useful for handling large datasets or multiple dashboards.
- Error handling – Provides robust error handling options for managing exceptions or issues during the export process.
- High granularity – Allows for detailed specification of parameters such as sheet names, date ranges, and more, offering high granularity in report customization.
- Resource optimization – Can be set to run during off-peak hours, optimizing system resources for other tasks.
- Security measures – Incorporates RLS with tag-based rules for anonymous users.
Export a snapshot using the Snapshot Export APIs
In July 2023, QuickSight announced the release of three new Snapshot Export APIs. These APIs are available in QuickSight Enterprise Edition with a subscription to Pixel-perfect Reporting. The three Snapshot Export APIs are:
In addition to pixel-perfect PDF and CSV content, the StartDashboardSnapshotJob API now supports Excel content. It starts an asynchronous job that generates a dashboard snapshot. It supports the following per one API call:
- One pixel-perfect PDF
- One Excel workbook with up to five tabs (each for one tabular visual from the dashboard)
- Five CSV files
In this section, we show how to use the AWS Command Line Interface (AWS CLI) to export a snapshot to an Excel workbook using the Snapshot APIs. For our demo, we use the AWS CLI in AWS CloudShell in an AWS account with an active QuickSight account (Enterprise Edition) with a subscription to Pixel-perfect Reporting. For more information about using the AWS CLI in CloudShell, refer to AWS CloudShell – Command-Line Access to AWS Resources.
After you have identified the dashboard and visuals you want to export out to Excel, you need to get the dashboard, sheet, and visual IDs. You can get this information either from the describe-dashboard-definition API or the QuickSight console. On the QuickSight console, navigate to the dashboard and then the visual you want to include, choose the options menu (three dots), and choose Embed visual. In the pane that appears, choose IDs for developer.
When you have the dashboard ID, sheet ID, and visual IDs you want to include in the snapshot, create a JSON file in a text editor or in any integrated development environment (IDE). For a complete syntax of the JSON file, refer to StartDashboardSnapshotJob.
Export without an S3 destination
In our first example, we select one visual from the example dashboard to include in the snapshot—the visual entitled “Quarterly Queue Report.” We keep it simple for now and don’t specify an S3 destination, tag-based security, or parameters. The JSON file looks like the following:
You need to upload the JSON file into your CloudShell environment. When you’re in the working directory of the cloud environment, you can run StartDashboardSnapshotJob in CloudShell:
In the preceding code, jsonFileName.json is the name of the file you just uploaded into the CloudShell environment. It contains the snapshot definition.
To check on the status of the job, run describe-dashboard-snapshot-job. Note that every new StartDashboardSnapshotJob API call needs to have its unique SnapshotJobId. In our example, the unique snapshot job ID is exceldemo:
Run the describe-dashboard-snapshot-job-result command to get the results for the export job:
If unsuccessful, the response will include the errors encountered for easy troubleshooting. If successful and no S3 destination was specified, the response will contain a downloadable, pre-signed, and short-lived S3 URI link. The download URL has a life of 5 minutes. Copy and enter the link in another tab or browser to download the snapshot in Excel format.
You can add multiple tabular visuals (up to five) in the same API call. Each visual will be sent to a different tab in the exported Excel workbook. The order in which you list the visuals determines the order of the worksheets in the Excel workbook.
If the visuals are from the same sheet, provide the additional visuals IDs to the VisualIds list:
One visual
If the visuals come from different sheets, separate the visuals out by SheetId:
Three Visuals
As mentioned before, you can also include one pixel-perfect PDF and up to five CSVs in the same API call. Each additional file type will have to be added to the FileGroups section. In the following example, we add a pixel-perfect report that will be exported out separately as a PDF:
If you do so without specifying an S3 destination for your files, the response will contain multiple pre-signed and short-lived (5 minutes) URLs, one for each file you specify. In the preceding example, the response has two URLs, one for the Excel snapshot and another one for the PDF snapshot. Note that the PDF snapshot is exporting out a pixel-perfect report and as such the SelectionScope was set to ALL_VISUALS instead of SELECTED_VISUALS, which was the case with the Excel snapshot where selected visuals were included.
Export to an S3 destination with parameters enabled
Additionally, you can specify an S3 destination for your exported snapshot files in the SnapshotConfiguration section of the JSON file. The user making the API call must give QuickSight write access to the S3 bucket. In the following example, in addition to specifying the bucket name, we also provide a bucket prefix, called snapshots, so that all new exported files are placed in their own folder:
When the export is successfully complete, the bucket should have the following files.
Note that if you run DescribeDashboardSnapshotJobResult, the response will also include the paths to the two files included in the snapshot. Additionally, the API call will automatically save the JSON file containing the export definition details to the bucket.
You can also include parameters in the snapshot configuration, which act as filters on the exported data. You can specify parameters in the SnapshotConfiguration section. The following example uses a multi-value parameter called RegionParam:
Tag-based row-level security
At the time of this writing, the Snapshot APIs support anonymous users only. As such, they support using RLS with tag-based rules to restrict access to a dataset when embedding dashboards for anonymous users. To learn how to enable RLS with tag-based rules, refer to Using row-level security (RLS) with tag-based rules to restrict access to a dataset when embedding dashboards for anonymous users.
After you have tag-based RLS active on a dataset, you can pass them to the Snapshot API by editing the UserConfiguration section:
Scheduled reports to Excel
In QuickSight Enterprise Edition, with a subscription to Pixel-perfect Reporting, authors can now set up scheduled exports of tables and pivot table visuals from any sheet of a dashboard.
For steps to schedule a report using Excel format, refer to Scheduling and sending reports by email.
Conclusion
The addition of scheduled and programmatic exports to Excel in QuickSight Enterprise Edition marks an advancement in the platform’s utility and adaptability. With these new functionalities, QuickSight becomes more aligned with the business ecosystem that frequently relies on Excel for analytics and reporting.
Key features such as multiple sheet export, format preservation, and Region availability make the tool more user-friendly and universally accessible. From a security standpoint, the tag-based rules and row- and column-level security measures provide an additional layer of customization and data protection. For developers, the new Snapshot Export APIs offer a level of control and granularity that can significantly automate and optimize data workflow systems.
In summary, these new features further enable QuickSight to serve as a robust, flexible, and secure BI platform suitable for a wide range of data analytics requirements.
About the Authors
Gilbert V Lepadatu is a QuickSight Sr. Specialist Solution Architect. With experience in two Fortune 100 companies, he has spearheaded data science, analytics, and automation initiatives, achieving significant revenue growth and operational efficiencies. Leveraging a PhD in Philosophy and two masters’ degrees, Gilbert has also contributed to academia, teaching classes on statistics and data visualization. His diverse skill set encompasses AWS services, data visualization, reporting and pipelines, data science and analytics, machine learning and communication across technical levels.
Rahul Easwar is a Senior Product Manager with Amazon QuickSight. He is the product leader for Amazon QuickSight Pixel-perfect Reporting responsible for the product launch in 2022 and continues to focus on new product innovations. Rahul has over 15 years of experience implementing and leading global Analytics programs for organizations across various industry verticals.
Rajat Maru, an SDM on the QuickSight Reporting team, brings 19 years of engineering leadership to the table. With expertise in domains like Business Intelligence, eCommerce and Logistics, he currently leads teams focused on exporting dashboards/reports and enabling them for content distribution.