November 18, 2024

Enabling self-service for supply chain analytics with Microsoft Fabric and Direct Lake

  












Effective supply chain analytics is critical for success. Especially for retail companies, an efficient supply chain helps meet customer demand, optimize inventory, and ensure timely product delivery. It directly impacts cost management, operational efficiency, and customer satisfaction too. A well-managed supply chain also enables faster response to market trends, helping companies stay competitive and adaptable in a dynamic retail landscape. 

Many technologies enable advanced supply chain analytics offering unique capabilities in data integration, processing, and real-time insights. Among these, Snowflake is one of the key platforms. This is due to its powerful data-sharing capabilities, high-performance computing, and seamless integration with BI tools—making it valuable for optimizing and scaling supply chain operations.


 

About the scenario

A grocery retailer undergoing digital transformation aimed to empower its supply chain team with data-driven decision-making. The company selected Microsoft Fabric, a robust data platform designed to deliver efficient self-service analytics. Using Microsoft Fabric’s capabilities and AI (artificial intelligence) tools like Microsoft Copilot, the retailer gained faster data access and additional scalability. This approach fosters operational efficiency, providing a low-maintenance solution that delivers reliable insights and supports seamless data processing.


Key business requirements

The migration process included the following aspects:

·              Self-service analytics: Empowers non-technical users to generate insights.

·        Comprehensive data coverage: Includes data from the last three years at highly granular levels (~5 TB).

·        User support: Accommodates 250+ users with tailored data access and permissions for different roles.

·        Generative AI integration (Copilot): Simplifies insight discovery with tools like Microsoft Copilot.

·        Scalability and performance: Scales to meet increasing demands without compromising performance.

·        Security and data access control: Enforces role-based access to protect sensitive data.

·        Integration with multiple data sources: Unifies data from over 20 sources for comprehensive analysis.



Unfolding the solution

Choosing Direct Lake

Selecting the right storage mode was essential for self-service analytics. Direct Lake was selected as it provided high-speed, real-time data access from Delta tables in Microsoft OneLake. This choice eliminated data imports and frequent refreshes, allowing analysts instant access to large datasets. Direct Lake’s unrestricted dataset size in Microsoft Fabric SKUs (stock keeping unit) supported anticipated data growth. Integration with Power BI, Excel, and other tools was another benefit, offering immediate insights.


Conducting proof of concepts (POCs)

A proof of concept (POC) evaluated the feasibility of migrating the client to Microsoft Fabric’s Direct Lake model. Essential elements like calculated columns, static tables, row-level security (RLS), and time intelligence were replicated. This phase involved mapping approximately 180 relationships and defining around 400 measures. Azure Databricks (ADB) notebooks handled calculated tables and columns, while Azure Data Lake Storage (ADLS) shortcuts created a Lakehouse. Direct Lake semantic models supported Power BI reports. As part of this implementation, we also evaluated aspects relating to security, time intelligence, and refresh latency. A 50+ concurrent user load test was also conducted.

The POC successfully met all requirements and passed all tests.

Figure 1: Architecture


Solution design: A lake-centric approach

Following the POC’s success, a solution was built using a lake-centric architecture, centered on Microsoft Fabric and Direct Lake. Key components included:

1.     Capacity planning and SKU selection: The F256 SKU was chosen to support three years of data while meeting performance needs. Direct Lake’s lack of dataset restrictions made it more cost-effective than import mode, which would require a higher SKU.

2.     Improved data access with self-service and AI integration: The solution enabled users to access insights and information faster with Smart Narratives and Copilot chat. Users are now eager to start using AI Skills.

3.     Automated operations and cost efficiency: Azure DevOps CI/CD ensured reliable deployments, while Microsoft Fabric notebooks automated dataset caching. Scheduling optimized Microsoft Fabric capacity, scaling to F256 during business hours and down to F64 during off-peak times.

4.     Data storage: Azure Data Lake Storage Gen 2 serves as the primary data storage. ADLS shortcuts were chosen due to their high performance and fast processing times.

5.     Semantic model mode: Direct Lake mode was selected for the semantic model. This is because Direct Lake mode supports scalability without dataset restrictions, accommodating future data growth needs.

6.     Security and access control: Using 'workspace identity' for ADLS shortcuts eliminated key management, while RLS provided fine-grained control over data access.

7.     Performance optimization: Pre-warming techniques reduced first-load times. Testing demonstrated improved performance with ADLS shortcuts over managed tables. 



Solution impact 

This solution enables both technical and non-technical users to make data-driven decisions with ease. Users access operational data through Excel, Power BI, and Copilot’s chat-based interface, building models for demand forecasting, inventory optimization, and delivery route planning. Users can work with granular data efficiently, experiencing reduced refresh latency and faster load times. The solution eliminated manual tasks, enabling seamless work with live data. All in all, the solution boosts supply chain responsiveness and agility.



Interested in learning more?

As a Microsoft Fabric Featured Partner, MAQ Software brings deep expertise in helping organizations unlock the full potential of Microsoft Fabric. Whether you're looking for guidance on implementing data solutions or optimizing your existing platform, we’re here to support you every step of the way. Contact us at Sales@MAQSoftware.com or explore our apps or consulting services on Microsoft Azure Marketplace: