BI Solution Enabling 2x Faster Reporting and Rich Self-Service Capabilities

About Our Client

The Client is a leading US hospitality investor, owner, and operator with top associate satisfaction rates in the industry. The company’s portfolio is approaching 100 premium hotels and resorts across globally acclaimed brands, including Marriott and Hilton.

Inefficient Reporting Hindered Business Decision-Making

The Client previously relied on multiple SSRS-based reports to monitor business performance across all its assets and for each asset individually. However, the existing reporting functionality didn’t satisfy the Client due to low report execution speed. In addition, there were no zero-code reports, so business users had to rely on their IT team to make any changes to the reports.

As a result, the legacy reporting solution was hindering insights into the Client’s business processes and preventing managers from making timely and informed decisions.

Looking to improve its reporting systems, the Client turned to ScienceSoft, a reputable IT vendor providing BI services since 2005.

2x Faster SSRS Reports Execution and Smooth Adoption of Power BI

ScienceSoft’s data engineer began by examining the existing reports and the infrastructure they were based on. After investigating five on-premises Microsoft SQL Server databases used to build Microsoft SSRS reports, the data engineer concluded that the low execution speed stemmed from inefficiently structured data sources.

Solving the performance issue became the starting point of the system’s improvement. ScienceSoft’s expert implemented several highly optimized stored procedures in T-SQL, which helped achieve 2x faster execution of the related SSRS reports.

To address the low flexibility of the reports, ScienceSoft’s team suggested introducing Microsoft Power BI as a replacement for the old SSRS system. With a tailored Power BI solution, reporting would become much easier: business users would be able to apply data filters, add or remove visual components, and create new reports without any coding skills.

The Client’s IT team approved the initiative; however, not all business users were ready for the transition — some strongly preferred working in the old format. To avoid potential business disruptions caused by low user adoption but still leverage the benefits of Power BI, ScienceSoft’s team suggested the following plan:

• Optimizing the performance of the existing SSRS reports by aggregating, cleaning, and structuring the data for them.
• Creating Power BI versions of the existing SSRS reports for the users ready to adopt the new technology.
• Extending and upgrading both kinds of reports in parallel to ensure high data availability and consistency across the two reporting systems.

With this tailored approach, some business users could continue to work in SSRS and enjoy faster report execution. At the same time, the users that moved to Power BI’s customizable dashboards helped drive the gradual adoption of Power BI across the organization.

Power BI Migration to the Cloud

Since the Client’s data infrastructure was deployed on-premises, it still impacted the reporting solution’s performance and flexibility, resulting in insufficient scalability and low cost-efficiency. To address these issues comprehensively, ScienceSoft suggested cloud migration as the next step of the system’s evolution.

Our team deployed an Azure-based Power BI solution as a single point of truth for enterprise-wide analytics. Via Microsoft Integration Runtime, the data gets from the on-premises Microsoft SQL databases to Azure Data Lake. After passing through an automated ETL process, the data lands in a data warehouse based on Azure Synapse Analytics. The DWH features dedicated Azure Synapse SQL pools for data processing (aggregation and denormalization) and storage. From the pools, the data gets to one of the five OLAP cubes. These cubes serve as sources of highly structured data, allowing users to slice and dice it at different angles and granularity levels. The data for the reports is updated every 24 hours to ensure its high accuracy and relevance.

ScienceSoft’s team paid particular attention to the optimization of data storage and processing costs, which was achieved in the following ways:

• Filtering for updated records. For daily data updates, the solution turns to the changes history stored in a dedicated Azure Synapse SQL pool and loads only the data that was changed since the previous update.
• Azure Synapse SQL pools run only when the ETL process is active (1–2 hours per day) and during the deployment of new reports versions, significantly reducing the solution’s operational costs.

A Highly Scalable BI Solution and 30+ Efficient Reports

With ScienceSoft's assistance, the Client promptly achieved 2x faster execution time for its legacy SSRS reports. Following the gradual transition to Power BI and the cloud, the Client now enjoys a highly scalable and cost-effective BI solution with rich self-service capabilities.

After more than two years of fruitful collaboration, the Client continues to rely on ScienceSoft for regular upgrades and maintenance of its BI system. As of May 2023, the new and upgraded reports cover 30+ KPIs, including hotel flow-through rate, revenue per occupied room, labor and operating expenses, lead closure rate, and more.

Technologies and Tools

Microsoft SQL Server, Microsoft SQL Server Analysis Services, Azure SQL Database, Azure Synapse Analytics, Azure Data Factory, Azure DevOps, Microsoft Power BI, Azure Automation.