Facet Technologies Logo

(309) 689-3900

Call our office!

3024 W. Lake Ave.

Peoria, IL 61615

8:00AM - 5:00PM

Monday – Friday

Case Study: Azure AI Search

About the project

FacetTrak, a comprehensive field service management platform, aims to enhance its customer support and operational efficiency by integrating Azure AI Search. This implementation focuses on improving ticket searches by utilizing AI-powered semantic search capabilities, allowing users to quickly retrieve relevant information. 

FacetTrak’s existing search capabilities, based on MySQL, required an upgrade to handle advanced AI search features without significantly altering the core database structure. Azure AI Search provides this functionality, but the integration required both technical adjustments on Azure and a migration strategy to SQL from MySQL. 

Challenges

  1. Data Migration: FacetTrak’s primary data source was MySQL, but Azure AI Search requires SQL for advanced features. Therefore, data migration had to be automated without disrupting ongoing operations. 
  2. Search Enhancement: FacetTrak needed to integrate Azure’s AI semantic search capabilities, which demanded configuration and optimization of data sources, indices, and indexers. 
  3. Security and Encryption: Ensuring secure connections between FacetTrak, Azure, and the SQL database, including encryption using SSL certificates, was critical. 

      Technical Challenges

        1. Automated Data Migration: The SQL Server Migration Assistant (SSMA) CLI command was integrated into a scheduled SQL job to ensure that live data from the MySQL database was seamlessly migrated to the Azure-hosted SQL Server. 
        2. Stored Procedure for Search Optimization: The stored procedure consolidated ticket data into the new table, ensuring that the Azure AI indexer had updated data for accurate search results. 
        3. DNS and Security Setup: A DNS record was created to point to the Azure VM hosting SQL, ensuring secure connections with a purchased SSL certificate. 

        Scope

          1. Azure Setup
          • Search Service Creation: In the Azure portal, an Azure AI Search service was created under the Basic pricing tier to enable advanced search capabilities. 
          • Data Source: The data source for tickets in FacetTrak was linked to an SQL Server hosted on an Azure VM (ftsql.facettech.com). The VM was configured with a purchased SSL certificate, and change tracking was enabled on the newly created table to monitor updates. 
          • Index Setup: The index consisted of rows (documents) from the new table. Each document represented a ticket, with a custom scoring profile to prioritize search results based on ticket descriptions over company names or log text. 
          • Semantic Search Configuration: Azure’s semantic search feature was activated for FacetTrak, allowing more relevant and context-aware results for users. 
          • Indexer Scheduling: An indexer was set to run daily at 1 AM to pull new or updated tickets from the SQL data source using SQL’s change tracking. 
          1. FacetTrak Setup
          • Activation of AI Search Features: FacetTrak enabled the Azure AI Search tenant feature, allowing tenants to utilize AI-powered searches via the Azure service. 
          • Configuration Settings: 
          • Azure AI Search Index 
          • Azure AI Search Key 
          • Azure AI Semantic Search (optional) 
          • Azure AI Search Service 
          • JavaScript SDK: The SDK was integrated within FacetTrak’s interface to allow seamless querying from the Azure AI search service. 
          1. Automated Database Migration
          • SSMA (SQL Server Migration Assistant): This tool was employed to automate the daily migration of MySQL data to SQL. The SSMA script was set to run at 12 AM daily, ensuring FacetTrak’s SQL data source was constantly updated with the latest information. 
          • SQL Jobs and Proxy Configuration: The automated SQL job ensured that the migrated data was processed into a new table via a stored procedure. 

        Audit

        After meeting with the client, a Software Requirements Specification (SRS) document was created after going through the scope of the project. The client did not specify a firm deadline for the project. Research went into the process of getting ink levels from printers. 

        Results 

        Enhanced Search Capabilities

        The integration of semantic search allowed users to perform context-aware queries, leading to faster, more accurate ticket retrieval.

        Seamless Data Migration

        The automated MySQL-to-SQL migration ensured that live data was always up to date without manual intervention

        +

        Improved User Experience

        Field service teams could now retrieve relevant tickets quickly, reducing time spent searching and improving service delivery.

        Secure Data Transfer

        The use of SSL certificates and secure SQL connections ensured that data remained protected throughout the process. 

        Conclusion

        The integration of Azure AI Search in FacetTrak not only improved the search functionality but also automated the data migration process and enhanced security. By leveraging Azure’s AI capabilities, FacetTrak now offers a more efficient, intelligent search experience for its users, improving operational efficiency and customer satisfaction.