MAQ Software Official Blog

Friday, November 4, 2022

ADX Implementation for a Global Cloud Management Software Provider

A system bogged down by requests and no existing solution in sight

As a global cloud management software provider, our customer helps IT service providers build and elevate their successful cloud businesses, and they have one primary goal—making their partners security compliant. These partners measured the compliance status by means of three different security metrics: Current Average Security Score, Average Security Score, and Max Recorded Security Score.

Day in and day out, our customer would receive countless requests from its partners (in JSON format) to process these security scores. Though able to respond to some requests, it became clear that the existing system was not scalable and couldn’t possibly respond to all these requests simultaneously. To solve the problem, the customer wanted a solution that could handle multiple requests and refresh data for near real-time reports.

The Ask

· Optimize and scale the existing data pipelines to handle up to 250 simultaneous requests.

· Have near real-time data refresh for batch requests.

· Publish real-time reporting insights for further analysis and decision-making.

Figure 1: Solution Architecture

Our winning solution

After a deep-dive analysis was performed by our team, the following solutions were identified to overcome the key challenges:

For pulling data from upstream sources and sending data to storage…

We conducted explorations using Azure Data Factory (ADF), Logic apps, and Function apps, finalizing ADF as the most suitable option for this functionality.

For data storage and processing…

We identified that Azure Data Explorer (ADX) provided the required degree of real-time reporting in contrast to other options. With this knowledge, we designed a cost-efficient solution using ADX for storage and processing that was reliable and secure.

For maximizing performance and minimizing issues…

To ensure that there are no performance issues with the system while maintaining a queue of requests, we used Azure Event Grids. The pipelines were implemented with the capability to handle both scheduled batch loads and just-in-time (JIT) requests. With scheduled batch loads, data was pulled for a defined timeframe, and for just-in-time requests, data was pulled once the request was received.

For all-around security…

To secure data transmission, secret key combinations were used to send calls to the Event Grid. All transmissions to and from the Power BI Service were encrypted.

And for everything else?

Power BI Embedded was used to publish reports to the web application, while Row Level Security (RLS) was implemented to enhance security.

Figure 2: Outcomes

Figure 3: Key Technologies

ADX Implementation for the Real Estate Industry

Out of step with the world of analytics

Like any other, the real estate industry is one that has many gaps. Our client—a Fortune 500 company from this industry—fills one of these gaps by offering design software that allows designers globally to build 3D models of both residential and commercial structures.

Though successful, they were not without their challenges. One area that their competitors excelled in was analytics. Our client struggled to gain real-time insights from large volumes of data generated from various applications, ranging from web to mobile to desktop. To stay ahead of the competition, our client needed a solution with scalable architecture, improved data latency, and faster data processing to manage this considerable amount of data while also lowering infrastructure costs.

The Ask

• Ability to analyze data from multiple sources in real time.

• Reducing the cost per license.

Figure 1: Solution Architecture

ADX: The Key to Our Problem

Our client wished to analyze their data in real-time, but traditional methods using legacy systems would’ve been too costly. So, we set out to find the best solution.

The first step was to perform explorations such as optimizing the existing architecture, using Data Lake architecture, and streaming solutions. After thorough testing and evaluation of various design patterns and services, we decided on the perfect fit for our client: Azure Data Explorer (ADX). With ADX as our go-to repository for data, a solution that streamed or batched data every few minutes was created, keeping costs low, enabling seamless scaling, and achieving impressive performance—all thanks to ADX’s powerful computational capabilities.

Figure 2: Impact

Monday, July 25, 2022

Real-time reporting for an organic supermarket chain

Business Case

Our client, an organic supermarket chain, sells affordable natural foods to thousands of shoppers every day. To better understand customer demand, teams relied on multiple daily sales reports. However, their existing analytics platform offered limited visualization and self-service capabilities. To build reports, teams exported data from the platform to Excel, where they manually organized and analyzed data – taking as long as 15 minutes to create a single report. The process was time-consuming, error-prone, and offered little return on investment (ROI). Knowing they needed a change, the supermarket giant began working with another supplier to migrate to Microsoft Power BI, a leading reporting and visualization platform. But when the supplier failed to deliver a solution on time, Microsoft introduced us to accelerate their migration. Over the next few months, we worked closely with our client’s teams to understand their needs and deliver an effective solution.

Key Challenges

• Enable self-service reporting

• Empower real-time insights

• Enable holistic view across teams

• Reduce operational costs

Our Solution

Our client knew they wanted Power BI, but supporting the speed and flexibility they needed also required a powerful analytics platform on the back end. We performed an in-depth analysis and determined the best combination for our client’s needs was Power BI and Azure Synapse. With this combination, we could perform direct queries between front-end Power BI reports and back-end data, enabling teams to access insights in real time. In addition, Azure Synapse provided higher data security than their existing platform. With Azure Synapse’s full data protection, access control, authentication, network security, and threat protection, teams can work with more confidence.

After migrating all data to Synapse, we built a robust data model that provides a holistic view across teams and sales channels. Now, teams can easily compare in-store purchases and ecommerce orders. Using our technical expertise and business knowledge, we helped teams create reports that maximize their insights. With Power BI’s slice and dice capabilities and drag and drop visualizations, teams can now edit and build reports with zero code. To ensure teams were confident using their new platform, we offered Center of Excellence (CoE) trainings and dedicated support hours.

Business Outcomes

Within three months, we migrated our client’s reports to Power BI and built a robust data model on Azure Synapse that provides a holistic view across teams. Now, report generation is automatic, collectively saving teams up to an hour a day. With a unified system and no manual effort, our client reduced their operational costs and increased their ROI. Teams can now easily access insights and be confident in their business decisions.

Thursday, July 7, 2022

Improving insights and reducing costs through unified reporting

Business Case

Our client, a leading office retailer, enables millions of customers to work more productively. To track sales, marketing, finance, and operations, teams relied on over 100 reports. The challenge? Reports were siloed across three different legacy platforms, which were no longer supported by the supplier, or too expensive to maintain. In addition to high licensing costs, teams required engineering support to edit or create reports – hindering high-level resources and increasing operational costs. On the back end, reports pulled from six separate data sources, leaving teams with inconsistent insights and stalled decision-making. To improve reporting and business impact, they needed a unified reporting platform and data model.

Key Challenges

• Migrate/consolidate 100 reports to single platform

• Build unified data model

• Improve data reliability and availability

• Enable self-serve reporting

• Reduce operational costs

Our Solution

We migrated all reports to Power BI and built a data model that pulled from Snowflake. Using our proven migration strategy, we worked with the sales, marketing, finance, and operations teams to understand the metrics they needed and the gaps they faced with current reports. Defining key points and consolidating shared metrics enabled us to create a single data model that supported needs across teams. In two months, we migrated all reports from teams’ various analytics platforms to Power BI. To optimize our workflow, we followed an Agile methodology comprised of the following steps:

1. Sprint Plan: Create a product backlog and define the scope and length of sprints.

2. Implementation: Using best practices, reusable templates, and themes, migrate reports and provide incremental report builds.

3. Performance Tuning: Refine the architecture and report layout to optimize the data model for performance.

4. Testing: Use a set of in-house performance analysis tools to automate testing, which tracks query performance and suggests visual layout and data validation optimizations. In addition, conduct UAT sessions to ensure the reports are user-friendly, high-performing, and optimized for their target audience.

5. Deployment: Automate deployment, enabling users to immediately access reports. Complete the transfer of ownership – hand off the code, reports, and workspace inventory to our client.

6. Decommissioning: Avoid redundancies by systematically retiring old reports without impacting ongoing business operations.

Once we completed migration, we trained teams in Power BI through our Center of Excellence (CoE) programs. Through these trainings, teams learned the best practices they needed to confidently build and edit their own reports.

Business Outcomes

In less than two months, we migrated 100 reports to Power BI and built a unified data model connected to Snowflake. With our solution, our client has reduced their operational costs, unified reporting across their organization, and enabled self-serve reporting. Now, teams can act on insights 80% faster and confidently build the reports they need.

Tuesday, July 5, 2022

Microsoft Azure Advisor: Everything You Need to Know

What is Azure Advisor?

Advisor is an optimization tool that analyzes your Azure environment and recommends improvements for performance, reliability, security, cost, and operational excellence. Advisor offers user-friendly dashboards and tools that enable you to maximize your insight and take action. Advisor is free with your organization’s Azure subscription and is available through your Azure portal.

Business Benefits

For many large enterprises, manually analyzing Azure resources to ensure optimal performance is time-consuming and error-prone – especially if you don’t know what to look for or how to fix it. Advisor automatically identifies problem areas in your Azure environment and offers personalized optimization recommendations, so you can follow best practice. With Advisor, you can:

• Save time by automatically identifying gaps and improving resource performance

• Reduce costs by identifying idle and underutilized resources so you can scale appropriately

• Strengthen security by identifying gaps

Features

Overview Dashboard

The overview dashboard provides you with a quick glance of your active recommendations and their impact (high, medium, low), grouped by category: reliability, security, performance, cost, and operational excellence.

Advisor Score

Your Advisor score (displayed as a percentage) represents your environment’s overall performance. For example, a score of 100% indicates you have implemented all best practices. Alternately, a score of 50% indicates you can improve your Azure performance by implementing more best practices. Your Advisor score refreshes every 24 hours and is the sum of your category scores divided by the sum of the highest potential score from each category. In total, there are five categories: reliability, security, performance, cost, and operational excellence. Your applicable categories vary based on your active subscriptions.

Recommendations

Advisor provides personalized best practice recommendations based on the five pillars of the Microsoft Azure Well-Architected Framework:

• Reliability

• Security

• Performance

• Cost

• Operational Excellence

Each recommendation is paired with potential benefits, potential score increase, and impacted resources.

Filters and Grouping

For added control, you can filter dashboards by subscription, recommendation status, and recommendation type. If you have multiple subscriptions, you can group dashboard insights by subscription.

Monitoring

To stay proactive, you can set up alerts when new recommendations are detected for your resources. Using a variety of configurations, you can prioritize subscriptions, resource groups, categories, and level of impact. You can choose to receive alerts via email and text message, or automate actions using webhooks, runbooks, functions, logic apps, or by integrating with external ITSM solutions. In addition, you can set up recommendation digests, which provide periodic reports your active recommendations. For more about how to set up alerts, review this Microsoft guide.

Downloading

To share insights, you can download score/recommendation reports as a CSV or PDF file.

Getting Started

1. Log in to your Azure portal.

2. On the Azure homepage under Azure services, select the Advisor icon.

Note: If you do not see the Advisor icon, type “Advisor” into the search bar and select Advisor from the search results.

Thursday, June 16, 2022

Enhancing fintech analytics to provide millions of borrowers with better loan options

Business Case

Our client, a leading fintech company, enables thousands of financial institutions to engage millions of borrowers with better loan options. Our client was on a mission to expand their analytics platform when they faced a critical block: Their existing platform architecture was at maximum data capacity. To onboard new customers, our client needed a more scalable analytics solution. In addition, our client wanted to enhance their platform’s reporting experience. Existing reporting was limited and required users to export data to Excel for manual analysis, delaying insights. To increase their product value and onboard more customers, our client needed a scalable architecture with embedded reporting.

Key Challenges

• Enable analytics platform to scale to 1000+ customers

• Enable self-serve, near real-time analytics

• Enable AI/ML capabilities for future innovation

• Improve security of financial data

Our Solution

We rebuilt our client’s analytics platform using Azure Synapse, Azure Data Lake Storage, Azure Data Factory, Azure Databricks, and Power BI. To ensure operational and technical excellence throughout the build, we followed the five pillars of the Azure Well-Architected Framework and leveraged migration strategies from Microsoft’s Cloud Adoption Framework.

Reliability: Implemented query replica within Azure Analysis Services (AAS) to ensure resource intensive queries do not impact ETL processing. Configured secondary and backup resources to ensure 100% resource availability.

Security: Enabled role-based access, disabled public access to storage accounts with PII data to ensure partner data is isolated within the ecosystem. In doing this, we greatly reduced the risk of security threats.

Cost Optimization: Implemented auto-scaling in lower environments, enabled Databricks to scale down when inactive, and deployed Power BI report on cost monitoring to scale services as needed.

Operational Excellence: Created Terraform automated scripts for Azure resources deployment. Implemented proactive monitoring for pipeline bottlenecks, ETL execution, and failures.

Performance Efficiency: Implemented parallel processing and concurrent querying of underlying data model for 1000+ customers using Azure Databricks.

In addition, our automated deployment framework uses continuous integration/ continuous delivery (CI/CD) pipelines to create Azure landing zones by focusing on identity, network, and resource management. To deploy Azure landing zones, we used a proprietary approach that combines the benefits of both the “start small and expand” and the “enterprise-scale”. Using industry-standard best practices and our center of excellence for Azure infrastructure setup, we ensured the right configuration to build a strong foundation and avoid rework in the future. This approach reassures our customers about our capabilities while creating a secure and reliable environment that is built to last.

Business Outcomes

With our Azure Synapse-based solution, our client’s platform now offers powerful self-service, near real-time analytics, enabling their customers to reach millions of borrowers faster. The platform now has the capacity to scale and support over 1000 customers. With Azure Synapse, our client can easily integrate machine learning models like fraud detection and recommendation engines without major architecture changes. To accelerate onboarding, we developed an automated deployment framework that onboards new customers in a single click, reducing setup time from days to hours.

Friday, December 31, 2021

Accurately Forecast Customer Sales with Machine Learning (ML)

Business Case:

Our client, a multinational food and beverage chain, operates thousands of retail stores and generates billions of dollars of annual revenue. Our client needed to understand the impact of weather, promotions, discounts, product launches, holidays, and other events on sales. The client’s existing predictive sales model routinely underestimated sales volume at both the aggregated and daily level. Our client also needed to better understand the causes of seasonal and daily spikes in sales.

Key Challenges:

• Improve the accuracy of future sales predictions.

• Identify and analyze patterns in data for nonlinear fitting and predict future sales using historical data.

• Examine the correlation between weather data (precipitation, temperature, pressure, wind speed, cloudiness, and so on) and sales at a specific longitude and latitude.

• Analyze the impact of factors such as product launches, promotions, discounts, and holidays on predicted sales.

• Include seasonality variables to explain seasonal fluctuations in the sales time series.

Our Solution:

We built a Sales Forecasting Engine on Microsoft Azure Databricks that allowed our client to quickly and accurately predict sales.

Solution Design:

We worked with the client’s marketing operations and finance teams to collect and analyze their sales data, promotion and discount data, and store events data. We also used National Oceanic and Atmospheric Administration (NOAA) historical weather data from the US government to develop the weather model. We extrapolated the historical data and used application programming interfaces (APIs) to connect the data to our machine learning (ML) model to predict weather.

Highlights:

• Used R libraries and custom functions to cleanse and preprocess the data.

• Used descriptive statistical analysis to tackle skewness and kurtosis for the features.

• Performed Fourier transforms to decompose sales, analyze trends, and remove noise from the sales time series.

• Applied logarithmic, exponential, and S-curve transformations to features to introduce nonlinearity as per real scenarios.

• Developed hybrid regression models to predict future sales using nonlinear, multiplicative, probabilistic, regularized, and deep learning approaches.

Figure 1: Architecture of Forecasting Engine

Business Outcomes:

Our supervised ML predictive model empowered our client to analyze the impact of weather, promotions, discounts, product launches, holidays, and daily events on sales and execute business decisions accordingly. The model also identified the delay between an event and the seasonal spike, which enabled our client to maximize sales following an event.

Our hybrid ML model is far more accurate than the previous ML model. The prediction runs on an aggregated and daily basis, and the model retrains itself once actual sales figures are injected into the model.

Our model’s Mean Absolute Percentage Error (MAPE) value was 0.09—as compared to the previous model’s MAPE value of 0.13. (a lower value indicates greater accuracy).

Highlights:

• Forecasted sales depending on weather variations for the client’s store at a specific longitude and latitude.

• Analyzed the positive and negative impacts of daily events such as discounts, promotions, launch events, and holidays on predicted and actual sales.

• Statistically identified and explained seasonal spikes in sales time series.

• Identified the lag period for daily events to explain the behavior in time series.