Wednesday, October 28, 2020

Visualize Earning Potential in Real Time

Share

Business Case:

Our client, a multinational technology company, has an extended partner network responsible for generating millions of dollars in sales. To incentivize existing partners and engage prospects, our client showcases partner earning potential based on performance. Prior to our solution, our client manually calculated the earning potential presented to their partners and prospects. The process was time-consuming and prone to error. In partnering with us, our client’s goal was to design a solution that automatically calculates earning potential based on partner performance details.

Key Challenges:

•  Design single point of view for calculation of profit opportunities based on engagement details  
•  Enable ease of access for external stakeholders with set of predefined authentication methods  
•  Enable customization of deal and engagement structures for client managers  

Our Solution:

We designed a Power App that calculates profit opportunity based on partner engagement inputs.  

Figure 1: User Interface of Solution

Our solution consists of a user interface and an administrator interface. The user interface is divided into two sections: input and output. On the input side, users enter engagement, consumption, and projection details. On the output side, profit opportunities are automatically calculated and displayed to the user. 

The administrator interface allows authorized business managers to change calculation criteria and enable/disable criteria based on user level. Administrators can also configure data category and threshold. 

Figure 2: Admin Interface

For external access, partners and prospects can log in to the app with pre-approved IDs outside the core network.  

Business Outcomes:

With our solution, our client drastically reduced calculation time and effort. Partners and prospects can now easily enter and change performance metrics to view earning potential in real time.  

In addition, our client can onboard new partners faster by offering quicker access to earning potential figures. Now, our client can focus on growing their network and increasing their revenue. 

Highlights:

•    Developed a low-code Power App that calculates end to end earning potential in real time
•    Reduced manual effort and calculation time by 30% on average
•    Ensured ease of access and collaboration opportunities for users across multiple organizations


Monitor Service and Support Quality with Power BI

Share

Business Case:

Our client, the business management team of a multinational technology company, sells software around the globe. As service-based subscriptions overshadow the demand for products, customer lifetime is increasingly valuable. To maintain customer loyalty, it is imperative that our client’s service and support operations run smoothly.

Our client needed to monitor the quality of customer support their team provided to identify and address performance gaps. Our client’s previous tracking system rendered slowly and offered poor user interface and experience (UI/UX). Reports took almost an hour to refresh, and the system was unable to flag refresh error. Consequently, managers had to drive business and staffing decisions based on stale data, which led to logistical inefficiencies and missed opportunities.

Our client needed a solution that simplified their data lifecycle, improving their ability to derive actionable insights from up-to-date information.

Key Challenges:

•  Enable managers to easily track support quality and efficiency  
•  Reduce load and refresh time of reports and visualizations  
•  Improve the UI/UX of reports to ensure an optimized user experience 

Our Solution:

We developed a suite of Power BI reports built on top of Azure Databricks (ADB) Notebooks that ensure our client’s data refreshes quickly.  

Figure 1: Solution Architecture

Our client stored support and service data with both on-prem and cloud-based servers. We used ADB Notebooks to ingest data from multiple sources while avoiding overhead in the final Power BI reports.  

To ensure data was being transferred securely, we restricted report access using Power BI’s row-level security (RLS). Reports are shared through specific Azure Active Directory security groups to ensure further confidentiality. 

To improve the UI/UX of our reports, we turned to our in-house team of UI/UX specialists. We developed a Power BI theme that is used across reports to ensure consistency and quality. To ensure efficiency, we used our in-house Page Load Time (PLT) analyzing tool to check that report load time did not exceed a few seconds. We integrated a refresh failure flag into our solution architecture to notify the team of reports above the threshold. 

Business Outcomes:

Our Power BI reports ensured our client’s business managers identified the trends of high priority customer incident reports and support requests. Built on ADB Notebooks, our solution can quickly collect data from multiple streams, enabled managers to access to global support data at a glance. With optimized reports, our client can monitor the performance and Service Level Agreements (SLA) of their support team.  

Our solution reduced data refresh time from one hour down to a few minutes, eliminating stale data. Report visuals load in less than a few seconds, improving user experience. Power BI’s dynamic visualizations offer actionable insights, improving data storytelling. The support team can now easily quantify their performance and requirements. With our solution, management can anticipate staffing needs, increasing the team’s overall efficiency and customer satisfaction. 

Highlights:

•    Created a suite of Power BI reports that enable our client to track the quality of their global service and support
•    Reduced data refresh time by over 80% (one hour to a few minutes)
•    Implemented modern designs to create visually appealing, insightful reports


Friday, October 23, 2020

Complete Tasks Faster with a Custom Dynamic Checklist in Dynamics 365

Share

Business Case:

Our client, a Fortune 500 software company, generates millions of dollars in sales through product promotions. To launch promotions, marketing managers need to follow strict procedures. Prior to our solution, marketing managers needed to manually search for procedure documents in SharePoint and track the associated tasks in Excel. With different procedures for each promotion type and a high margin of manual error, the marketing team risked procedural non-compliance. In partnering with us, our client’s goal was to design a solution that dynamically populated procedure checklists based on the promotion request type.

Key Challenges:

•  Track task status/completion to ensure procedural compliance 
•  Reduce checklist completion time 

Our Solution:

We designed a solution that stores different checklists and dynamically populates them in Dynamics 365 based on the requested promotion type. 
Figure 1: Solution Architecture

Marketing managers can request different types of promotions directly in the Dynamics 365 platform. When the manager selects the promotion type, the solution populates the corresponding checklist in Dynamics 365, where the manager can track and complete tasks. Once the manager has completed all tasks, the promotion can be approved by leadership. If the manager does not complete all tasks, the manager receives a warning that prevents bypass and ensures compliance.  

The checklist has multiple tabs:  

•  General: provides task and status details 
•  Summary: provides user details 
•  Documents and Related: provide documents related to individual tasks in the checklist
Figure 1: Solution Interface

Business Outcomes:

Our Dynamics 365 checklist enabled our client to complete marketing checklists faster while ensuring process compliance. To allow for business growth, our client can easily customize checklist items based on updated management or process requirements.  

Our solution increases accountability and procedure adherence across our client’s marketing team. Marketing managers can now focus their attention on the promotion outcome rather than on manually tracking checklist tasks. Streamlined checklist processes enable managers to launch promotions quickly. Faster time-to-market increases the visibility of our client’s products and potential sales. 

Highlights:

•    Designed a solution that stores checklists and dynamically populates them in Dynamics 365 based on the requested promotion type
•    Reduced checklist completion time
•    Increased procedural compliance

Connect High-Impact Service Opportunities with Underutilized Support Team Resources

Share

Business Case:

Our client, a Fortune 500 technology company, has a product and services team that provides support to customers around the world. Every few years, our client reassesses their organizational structure to identify gaps and resource management issues. During this cycle, our client found many business activities where support team members were underutilized.

In partnering with us, our client was looking for a solution that enables them to visualize and analyze resource utilization across their entire customer support team. The solution needed to identify underutilized resources and connect them to customer support opportunities.

Key Challenges:

•  Visualize resource utilization data from multiple sources 
•  Automatically track resource allocation requests  
•  Proactively connect underutilized support resources to service opportunities  

Our Solution:

We developed a Power BI report and Power App that enable our client to view all support resource data and easily connect resources with service opportunities. We leveraged the capabilities of Azure Data Factory (ADF), Azure Databricks (ADB), and Azure SQL Database to create a reliable, scalable, and cost-effective solution. 

Figure 1: Solution Architecture

Our solution pulls data from our client’s existing “blocked services” system, an automated database of all “at-risk” and “blocked” support opportunities. At minimal opex, we can aggregate data from multiple sources using ADF pipelines, ADB, and Azure SQL Database. We track the engagement hours of support team members across the world. Our solution’s Power BI dashboards display resource utilization data in real time, enabling users to understand which resources are being under or over-utilized.  

To manage support gaps, we developed a Power App where managers can connect resources to service opportunities by clicking the app link provided in the dashboard. Azure Logic Apps automatically populates relevant data in the app interface.  

Once the manager sends the support request, the app creates an Azure DevOps work item assigned to the relevant team member. The DevOps item is auto-populated with all information input into the Power App to ensure the team member and manager can access identical information. To mark the completion of the request, the manager simply needs to update the work item status to Done. 

Business Outcomes:

Our end-to-end Power Platform solution provides our client’s support team with real-time data on resource utilization. Our solution connects underutilized team member resources to service opportunities with high business value. Managers can gain insight into opportunity areas by analyzing the impact of resource allocation within our Power BI dashboard. 

The Power BI resource utilization dashboards offer detailed information and insights on resource allocation so managers can identify underutilized resources at a granular level. Our solution offers massive load capacity, pulling data from multiple sources across the globe, without business interruption. For managers, analyzing resource allocation and assigning support requests is as simple as opening a Power BI report and clicking a link. Support team members can access all relevant information about the service opportunity assigned to them via Azure DevOps.  

We ensured the page load time (PLT) across all report pages was under our company-standard threshold of 10 seconds. Through continuous optimizations, we reduced the time to 6-8 seconds, enabling decision makers quicker access to key insights. Our solution was initially developed for one area within our client’s organization. After its success, our client requested we expand the solution across the entire customer support team. 

By enabling our client to effectively manage and analyze resource allocation, we cut down on costs and save time. The client can act quickly on important service opportunities, increasing customer loyalty.  

Highlights:

•    Developed a Power BI report and Power App that enables support managers to analyze resource allocation and close service gaps
•    Enabled real-time data analysis from various data sources with a page load time (PLT) of 6-8 seconds
•    Reduced manual efforts by managers through a support request app that auto-populates service request data
•    Reduced manual efforts by support team members through a solution architecture that auto-populates customized Azure DevOps work items


Modernize Data Reporting and Management with Azure Databricks

Share

Business Case:

Our client, a global software giant, relies on Power BI reports to compare their product performance against competitors. Report data is highly confidential, only accessed by 100 members of our client’s international marketing and operations team. Due to the industry value of our client’s data, security is a high priority concern.

Our client wanted to reduce data processing costs and avoid data replication. Our client previously used SQL to store and process data. Recently, our client moved most upstream data sets and operational systems to Azure-based solutions. To ensure continuity, we proposed a new system architecture based on Azure Databricks (ADB) to process their data. ADB allows developers to mount and transform data from the source, meaning data is processed without being replicated.

In partnering with us, our client’s goal was to migrate data processing from SQL to ADB without interrupting business continuity.

Key Challenges:

•  Migrate 10+ data sources from SQL to ADB while enabling business continuity 
•  Implement high-level security that restricts access based on business category and location  

Our Solution:

We migrated our client from an on-premises SQL data processing system to a scalable Azure Databricks environment that can access numerous Azure-based data sources. 

Figure 1: Solution Architecture

We designed our new ADB solution on top of the existing system. To enable business continuity, we migrated our client in phases. During phase one we established a basic system with all data streams. Then, we migrated our client’s eight Power BI reports one by one. All reports use an optimized star schema that reduces dataset size and report load time.  

The ADB platform’s scalability offers a data management interface that can connect to various Azure data sources. Reports are updated daily using an import model that regularly refreshes back-end data. The ADB environment’s automated shutdown capabilities enable easy and cost-effective data refresh.  

The reports offer high-level, role-based security. We enabled Power BI’s row-level security (RLS) on all reports to ensure users can only access data within their geography/business area. 

Business Outcomes:

Migrating our client from SQL to Azure Databricks (ADB) enabled faster, more cost-efficient access to critical marketing and operations data. Our client’s previous system used multiple expensive virtual machines (VMs). By migrating to an ADB solution, we saved our client 50% in costs.  

ADB is optimized to our client’s Azure-based upstream data sources, enabling seamless data management throughout the pipeline. ADB is built to last and can be easily scaled for growing business needs. 

We migrated eight Power BI reports to our client’s new system that offer key insights into the business pipeline, opportunities, and competitors. Power BI’s row-level security (RLS) capabilities ensured role-based access to confidential information. In addition, our solution reduced report refresh time by 50%, from 40 minutes to 20 minutes, offering business leaders quicker access to key insights. 

Highlights:

•    Migrated eight Power BI reports and 10+ sources from SQL to Azure Databricks (ADB)
•    Increased cost savings by 50%
•    Reduced report load time by 50% (40 minutes to 20 minutes)
•    Secured confidential information with row-level security (RLS)


How to Optimize IT Infrastructure Costs

Share


A typical IT project requires constraint management to ensure optimal solutions for a product or service. The most common conflicting constraints are cost versus quality. Higher quality solutions lead to higher project costs and vice versa, something especially significant in the case of IT infrastructure costs. To enable our clients to opt for both reduced project costs and high-quality projects, we developed an IT optimization checklist.

How To Update IT Infrastructure

1.  Create a cross-functional techno-managerial team. This team needs complete access to IT data dating back several quarters to make informed decisions about specific needs. 
2.  Analyze the data to identify which elements of infrastructure contribute to rising infrastructure costs. Evaluate which elements are critical, and which can be replaced. 
3.  Identify lower cost alternatives to the existing system. Strategize with the team to balance important one-time costs and necessary recurring costs.  
4.  Identify potential repercussions of the proposed changes. Before implementation, coordinate with the development, support, and maintenance teams to ensure all possibilities have been considered.  
5.  Implement your solution.  

IT Cost Optimization Checklist

We identified common IT cost optimization opportunities based on our experience with clients and with internal operations. The following strategies focus primarily on organizations that use the Microsoft product suite. That said, many of the methods on this checklist are universally applicable.

▢  Downgrade low-importance servers (based on VM utilization). 
▢  Decommission unused or obsolete servers. 
▢  Downgrade low importance SKU clusters in production. 
▢  Update the data storage mechanism to a more cost-effective solution. For example, you can replace Azure Data Lake Storage (ADLS) with Blob storage for data back-ups. Blob storage is a convenient and cost-effective way to store static data. 
▢  Delete unused Azure Databricks (ADB) assets. Ensure your subscription covers only required assets. 
▢  Use the trial version of ADB on lower-impact environments. The pre-trained version of ADB operates for 30 days and doesn’t charge for running high-end jobs. Systems like a Dev environment function equally well on a pre-trained trial version of ADB.  
▢  Automate metadata deletion in ADLS. Ensure you are not wasting storage space on duplicate or unnecessary data. 
▢  Automate servers and VMs based on active usage (shut down during non-business hours). 
▢  Automate disk clean up on servers.  
▢  Establish daily automated alerts on monthly cost forecasts. Alerts enable you to immediately address abnormal cost spikes, forecast cost trends, and continuously optimize.  
▢  Ensure resources are only available where they need to be available. Many projects occur exclusively in a local environment, but still get pushed to global duplication. Cut down on resource use by ensuring local projects only push local duplication. 

Why Optimize Your IT Infrastructure?

Optimizing your IT infrastructure is an easy way to cut down on costs without impacting business operations. One of our clients, an international technology giant, became concerned about spiraling IT infrastructure costs. The cost of cloud usage was skyrocketing due to higher volumes of data, but the project budget remained fixed.

We recommended our client transform their IT infrastructure. We have worked closely with this client for several years, and already had an established DataOps team within their organization. We analyzed our client’s subscriptions over the last two years, informing our cost optimization opportunities with trends displayed over the last year. After evaluating our client’s ~80 VMs, we developed an optimized solution that reduced costs by over 25%. Between April and September of this year, we have increased IT cost savings for our client by almost 30%.


Want to learn more about our data management expertise? Visit our Data Management Hub

Friday, October 16, 2020

Consolidate Power BI Sales Reports to Effectively Track Global Performance

Share

Business Case:

Our client, the sales team at a multinational software company, needed to track product performance. The sales team, which operates across multiple geographies, previously used several Power BI reports to track the effects of global marketing. Each role within the sales team had its own Power BI report, but the team was unable to view the reports collectively. In addition, our client’s existing system operated with multiple upstream sources. Tracking information across reports was a timely and complicated process.

Our client needed a way to track global product performance and to identify customer accounts with upsell opportunities.

Key Challenges:

•  Create a unified Power BI sales report  
•  Provide up-to-date product information across accounts  
•  Identify upsell opportunities within accounts  
•  Implement role-based security  

Our Solution:

We consolidated our client’s seven Power BI reports into two sales and marketing reports. One Power BI report targets leadership and management while the other is for sellers and team members. The reports offer a customized view for each user based on role and access details.  

Figure 1: Solution Architecture

Our Power BI report can capture and display data at various levels in less than 10 seconds. We replaced the existing live connection system with an import data model that is able to pull data from multiple sources with a faster load time. 

To consolidate our client’s reports, we examined their existing seven reports for common data points. We performed a gap analysis to determine which reports were critical, which were redundant, and which could be combined. By reducing the quantity of reports from seven to two, we reduced maintenance efforts. With numerous upstream data points, our client struggled to ensure consistency across reports. Our solution resolves this challenge by checking the health status of the upstream server and pulling data into different tables.  

To ensure continued security, we implemented different access levels based on the report user. Our leadership report enables the leadership team to access data at an area level, enabling a complete view of product marketing numbers. The seller report uses row-level security (RLS) to ensure users can only access their own data and the data of their direct reports. The seller report requires users to select their name from a dropdown, ensuring they receive the correct security access. Both reports consolidate relevant product information, including sales stage, engagement details, relevant milestones, and close rates. 

The Power BI report can be filtered by region, product, manager, and more. The report also offers insights into future upsell opportunities based on an account’s performance in the last month.  

Business Outcomes:

Our consolidated Power BI report enabled our client to gain quick access to product marketing and sales insights. Our solution enables sellers and managers to target key accounts, presenting detailed and up-to-date data in a single location. Users can check for relevant key performance indicators (KPIs) based on their role. We also added a KPI that enabled our client to gain insight into accounts that offer an upsell opportunity based on the last month’s performance.  

By using an import data model, we reduced page load time (PLT) from 25 to less than 10 seconds, enabling speedy access to critical data. We increased time savings for users who now only open one report to view all relevant data. At the time of writing, our solution has 200 weekly users. 

Highlights:

•    Reduced the number of Power BI sales reports from seven to two by integrating key performance indicators (KPIs) into a single role-based view
•    Reduced page load time (PLT) from 25 seconds to under 10
•    Enabled daily data refresh
•    Developed a key Power BI report for our client, with 200+ weekly users


Subscribe to: Posts (Atom)