Migrating the SQL Server Reporting Services Platform to Amazon Quick Sight for Cloud-Native Business Intelligence
by George Randolph, Juan Roldan, and Saunak Chandra | on 20 DEC 2023 | in Amazon Quick Sight, Analytics, AWS Partner Network, Business Intelligence, Customer Solutions, Database, Intermediate (200), Migration & Transfer Services | Permalink | Comments | Share
By George Randolph, CEO – IPsense
By Juan Roldan, Head of Analytics – IPsense
By Saunak Chandra, Principal Solutions Architect – AWS
IPsense
Many Amazon Web Services (AWS) customers use Microsoft SQL Server for their transactional and analytics needs. Over time, these customers likely built hundreds of SQL Server Reporting Services (SSRS) reports off the SQL Server transactional database.
The SSRS platform is a reporting tool that primarily produces static, tabular reports without any visual charts or interactivity. Upon users’ reporting requests, the platform relies on on-demand execution of SQL queries and stored procedures on the server.
Many AWS customers have sought to migrate their business intelligence (BI) platform to a cloud-native solution that can scale and provides a unified experience. This includes options such as a variety of interactive visual charts that’s easy to embed in other applications and includes natural language query (NLQ) query capability.
When migrating from SSRS to Amazon Quick Sight, customers look for implementation partners who have experience with Quick Sight and tooling that automates migration of business logic residing in SQL Server as stored procedure.
IPsense is an AWS Specialization Partner and Managed Service Provider (MSP) with the Amazon Quick Sight Service Delivery designation and AWS Competencies in Migration and Data and Analytics Consulting. An AWS Marketplace Seller, IPsense has developed an SSRS report migration acceleration tool that helps customer automate SSRS reports conversion into AWS Glue jobs and Quick Sight datasets, letting customer modernize their BI platform on AWS.
Solution Overview
Customers opt for the breadth and depth of purpose-built data and artificial intelligence (AI) services on AWS. Amazon Quick Sight is a scalable, serverless, embeddable BI service built for the cloud. It lets users create and publish interactive BI dashboards and highly-formatted paginated reports that can be delivered to users’ inboxes or scheduled to deliver to Amazon Simple Storage Service (Amazon S3) locations.
Through generative BI, Amazon Quick Sight makes it easy for users to become more productive authoring dashboards, as they can use intuitive and powerful natural language experiences to add visuals, create calculated fields, and format visuals efficiently.
The diagram below shows the recommended architecture for customers migrating from SSRS to AWS.
Figure 1 – Data lake architecture for SSRS migration to Amazon Quick Sight.
To support the migration, we propose an architecture that satisfies the following criteria:
- Structured data lake on AWS with data marts using AWS Lake Formation.
- AWS Database Migration Service (AWS DMS) to replicate ongoing transactional data from SQL Server into the AWS data lake raw zone.
- AWS Glue jobs to process the raw transaction data to apply business transformation logic, producing a curated data for reporting and analytics.
- Orchestration of replication and business transformation is achieved through AWS Step Functions state machines.
- After the data is curated, it’s published in the lake zone cataloged in AWS Glue Data Catalog.
- Prepared data in the data lake is made available via Amazon Athena to all tools, including Amazon Quick Sight for BI.
- Amazon Quick Sight is used for dashboards and reporting needs for business users.
- AWS IAM Identity Center is a central repository for administering user access to AWS services including Quick Sight dashboards.
IPsense has built a Terraform module that can be deployed on an AWS account. Once deployed, customers can run an AWS Lambda function that accepts SQL Server database instance connectivity details as parameters. It then converts SSRS report queries and stored procedures to AWS Glue jobs.
You can then run the AWS Glue jobs which migrated the SQL Server data into an Amazon S3 data lake. Once the data is migrated to S3, the deployed Terraform code contains a crawler to register the migrated data into AWS Glue Data Catalog. This process accelerates building a data lake with over 1,000 tables of around 16TB of storage and 25 million objects stored in Amazon S3. After the data lake is built, you can build a Quick Sight dataset using Amazon Athena.
Here’s how an SSRS report looks after being migrated to Amazon Quick Sight:
[
](https://d2908q01vomqb2.cloudfront.net/77de68daecd823babbb58edb1c8e14d7106e83bb/2023/12/07/Ipsense-SSRS-Quick Sight-2.png)
Figure 2 – Amazon Quick Sight dashboard.
SSRS Migration Best Practices for Amazon Quick Sight
Let’s take a look at best practices migrating from SSRS to Amazon Quick Sight:
- Convert SSRS report queries and stored procedures to AWS Glue jobs, which provides better control, scale, and checks for data quality issues.
- Using Amazon Quick Sight row-level security (RLS) streamlines operations by reducing the count of reporting objects to manage, while ensuring data is secured based on users and groups.
- Filtering rules in SSRS are linked to dataset columns which influence how a column or row is displayed in the reports.
- Implement automated processes to speed up information extraction from SSRS files.
- The migration process is a good opportunity to enhance reports using insightful visuals combined with ML capabilities like forecasting or anomaly detection.
- One of the challenges of SSRS report migration is adapting complex queries where SSRS query parameters were used. Using Quick Sight’s dataset parameter, you can more efficiently filter in Amazon Athena to enhance the user experience.
Tooling for SSRS Migration to Amazon Quick Sight
IPsense created a tool specifically to expedite the migration process that automates the conversion of SSRS .rdl files into SQL queries to be used for building Quick Sight datasets.
The tool accepts an SSRS .rdl file as input, and then reads the metadata in the .rdl file containing SSRS report information. This includes tables, stored procedure filter, parameters, and rules. Next, it builds a spreadsheet detailing Quick Sight dataset queries.
Figure 3 – Output generated by SSRS report conversion tool.
The tool also builds a visual diagram processing all SQL queries from each dataset to identify their relationships, creating a diagram to assist the migrating team.
Figure 4 – Visual representation of the Amazon Athena tables.
Conclusion
Amazon Quick Sight with a structured data lake provides a faster, reliable, scalable solution with interactive, web-based dashboards. Quick Sight’s row-level security feature is a key factor, allowing dashboards to be securely shared with customers to view their own data only. By leveraging IPsense’s expertise, customers can achieve up to 60% time savings in development and migration to Quick Sight.
IPsense has extensive experience in implementing data lakes on AWS and reporting solution using Amazon Quick Sight. It’s migration tool helps customers migrate from SSRS to Amazon Quick Sight to streamline report conversions.
.
.
IPsense – AWS Partner Spotlight
IPsense is an AWS Specialization Partner with over 10 years of native cloud computing experience. Its mission is to help customers achieve the state of the art in cloud computing.
This is a companion discussion topic for the original entry at Migrating the SQL Server Reporting Services Platform to Amazon QuickSight for Cloud-Native Business Intelligence | AWS Partner Network (APN) Blog