AWS Partner Network (APN) Blog
Migrating the SQL Server Reporting Services Platform to Amazon QuickSight for Cloud-Native Business Intelligence
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 QuickSight, customers look for implementation partners who have experience with QuickSight 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 QuickSight 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 QuickSight 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 QuickSight 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 QuickSight 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 QuickSight.
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 QuickSight for BI.
- Amazon QuickSight 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 QuickSight 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 QuickSight dataset using Amazon Athena.
Here’s how an SSRS report looks after being migrated to Amazon QuickSight:
Figure 2 – Amazon QuickSight dashboard.
SSRS Migration Best Practices for Amazon QuickSight
Let’s take a look at best practices migrating from SSRS to Amazon QuickSight:
- Convert SSRS report queries and stored procedures to AWS Glue jobs, which provides better control, scale, and checks for data quality issues.
- Using Amazon QuickSight 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 QuickSight’s dataset parameter, you can more efficiently filter in Amazon Athena to enhance the user experience.
Tooling for SSRS Migration to Amazon QuickSight
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 QuickSight 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 QuickSight 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 QuickSight with a structured data lake provides a faster, reliable, scalable solution with interactive, web-based dashboards. QuickSight’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 QuickSight.
IPsense has extensive experience in implementing data lakes on AWS and reporting solution using Amazon QuickSight. It’s migration tool helps customers migrate from SSRS to Amazon QuickSight 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.