In today’s data-driven world, leveraging AI to gain actionable insights is crucial for staying competitive. At Baker Tilly, we recognize the transformative potential of utilizing the Data Agent (formally AI Skill) feature within the Microsoft Fabric ecosystem. Data Agent seamlessly integrates AI into Microsoft Fabric by enabling the creation of custom chatbots that can provide analytics insights directly from your organizational data.
Our recent article on AI-powered insights highlights how custom chatbots can interact with data models and provide insights directly from organizational data. This solution allows businesses to query data lakes and access all three data layers of the medallion architecture, ensuring comprehensive and accurate insights.
Building on this foundation, Microsoft has announced significant enhancements to the Data Agent, including support for multiple data sources, integration with KQL Databases and Semantic Models, improved conversational support and a redesigned chat canvas. These improvements enhance the Data Agent’s ability to provide detailed and contextually relevant insights across various data layers and sources, further empowering businesses to make informed decisions.
Medallion architecture overview
Our approach to implementing Data Agent in Microsoft Fabric leverages the medallion architecture, which organizes data into three layers: bronze, silver and gold. Each layer serves a specific purpose, ensuring data is processed and analyzed efficiently.
Lakehouse (Bronze layer): This layer contains raw data ingested in its original format. It serves as the foundation for initial data analysis and quality research.
Warehouse (Silver layer): The silver layer holds cleaned and enriched data, providing a more detailed view of operational metrics. This layer is crucial for generating operational reports and identifying areas for improvement.
Fabric database (Gold layer): The gold layer contains aggregated and optimized data, making it ideal for detailed analytical reporting. It helps businesses monitor key performance indicators (KPIs) and other critical metrics.
Example use case:
Imagine a retail company that wants to analyze the purchasing behavior of its customers. By leveraging the gold layer, the company can perform a cohort analysis to understand how different groups of customers behave over time. The SQL query might look something like this:
SQL
SELECT
cohort_start_date,
COUNT(DISTINCT customer_id) AS cohort_size,
SUM(CASE WHEN purchase_date BETWEEN cohort_start_date AND DATEADD(month, 1, cohort_start_date) THEN 1 ELSE 0 END) AS month_1_purchases,
SUM(CASE WHEN purchase_date BETWEEN DATEADD(month, 1, cohort_start_date) AND DATEADD(month, 2, cohort_start_date) THEN 1 ELSE 0 END) AS month_2_purchases,
This query groups customers by their sign-up date (cohort_start_date) and tracks their purchases over subsequent months. The results can help the company identify trends in customer behavior and tailor their marketing strategies accordingly.
By utilizing the gold layer in Microsoft Fabric, businesses can perform detailed analytical reporting that goes beyond the capabilities of a tabular model. This enables them to gain deeper insights into their data, make informed decisions and drive business success.
Operational reporting with the silver layer
The silver layer in Microsoft Fabric, implemented as a warehouse, is designed to handle cleaned and enriched data. This layer is crucial for generating detailed operational reports that can be used for day-to-day activities and decision-making. The ability to rapidly sync data between multiple sources and combine them into comprehensive reports provides significant value for businesses. Key benefits of the silver layer include:
Rapid data syncing: The Silver layer allows quick data synchronization from various sources. This ensures the data is up-to-date and reflects the latest information from different parts of the organization. For example, sales data from other regions can be synced in real-time, providing a consolidated view of overall sales performance.
Detailed operational reports: The silver layer enables the creation of detailed operational reports by combining data from multiple sources. These reports can cover various aspects of the business, such as inventory levels, production efficiency and customer service metrics. This level of detail helps companies monitor their operations closely and identify areas for improvement.
Enhanced decision-making: Operational reports generated from the silver layer provide actionable insights to drive day-to-day decision-making. For instance, a report on inventory levels can help a business decide when to reorder stock, while a report on production efficiency can highlight bottlenecks in the manufacturing process.
Example use case:
Consider a manufacturing company that needs to monitor its production efficiency and inventory levels. By leveraging the silver layer, the company can generate a detailed operational report combining data from its production, inventory management and sales databases. The report might include metrics such as:
Five best practices to manage hedge fund cybersecurity risks
Semantic model for business insights: The semantic model allows Data Agent to understand and interpret complex data relationships, providing high-level business insights and enabling informed decision-making.
KQL database for processing logs: The KQL database provides system performance-level information, allowing Data Agent to monitor system health, identify performance bottlenecks and optimize resource usage.
By integrating Data Agent with this architecture, businesses can access and analyze data across multiple layers and sources, gaining deeper insights and improving operational efficiency. The following sections will detail each layer, demonstrating how Data Agent can be used to access and leverage this information effectively.
Leveraging the semantic model for business insights
The semantic model in Microsoft Fabric plays a crucial role in transforming raw data into meaningful business insights. By accessing the semantic model, Data Agent can understand and interpret complex data relationships, providing a unified view of the data that aligns perfectly with current reporting and analytical needs. Key benefits of the semantic model include:
Unified data view: The semantic model integrates data from various sources, creating a coherent view of the business. This ensures that all reports and analyses are based on consistent and accurate data, eliminating discrepancies and improving decision-making.
Contextual understanding: Data Agent leverage the semantic model to understand the context and relationships within the data. This enables the generation of insights that are accurate and relevant to the specific business context. For example, Data Agent can identify trends in sales data, correlate them with marketing campaigns and provide actionable recommendations.
Enhanced reporting: By accessing the semantic model, Data Agent can generate reports that align with existing reporting frameworks. This means that businesses can continue to use their familiar reporting tools and formats while benefiting from the enhanced insights provided by AI. The semantic model ensures that the data used in these reports is accurate, up-to-date and relevant.
Improved data governance: The semantic model enforces data governance policies, ensuring data is used appropriately and securely. This is particularly important for businesses that must comply with regulatory requirements and protect sensitive information.
By integrating the semantic model with Data Agent, businesses can unlock the full potential of their data. This approach ensures that insights are accurate, relevant and aligned with current reporting and analytical needs, empowering decision-makers to make informed choices and drive business success.
Detailed analytical reporting with the gold layer
The Gold layer in Microsoft Fabric, implemented as a Fabric SQL Database, is designed to handle complex SQL logic and provide detailed information that a tabular model cannot quickly generate. This layer is essential for businesses requiring in-depth data analysis and reporting. Key benefits of the gold layer include:
Complex SQL logic: The Fabric SQL Database supports advanced SQL queries, allowing businesses to perform intricate data manipulations and analyses. This capability is crucial for generating detailed reports that require complex calculations and aggregations.
Cohort analysis: One of the powerful features of the gold layer is its ability to perform cohort-driven queries. For example, a business should analyze customer retention rates by grouping customers based on their sign-up date and tracking their behavior over time. This type of analysis can reveal valuable insights into customer loyalty and the effectiveness of marketing campaigns.
Complex business logic: The gold layer can handle complex business logic that may not yet manifest in the semantic model. For instance, a company might need to calculate profitability by considering various factors such as discounts, returns and overhead costs. These calculations can be implemented using advanced SQL queries, providing a more accurate and comprehensive view of business performance.
Production efficiency: Number of units produced per hour, downtime and machine utilization rates
Inventory levels: Current stock levels, reorder points and product lead times
Sales performance: Sales volume by product, region and sales channel
This comprehensive report allows the company to monitor its operations in real-time, identify inefficiencies and make informed decisions to optimize its processes.
By utilizing the silver layer in Microsoft Fabric, businesses can drive operational reports that provide a detailed and up-to-date view of their operations. This enables them to respond quickly to changes, improve efficiency and ensure smooth day-to-day activities.
Data quality research with the bronze layer
The bronze layer in Microsoft Fabric, implemented as a lakehouse, contains raw data ingested in its original format. This layer is essential for initial data analysis and quality research, providing a foundation for understanding potential data quality issues, system processing and more granular system data that may not yet be incorporated into the silver, gold, or semantic models. Key benefits of the bronze layer include:
Identifying data quality issues: The bronze layer allows businesses to analyze raw data for inconsistencies, errors and other quality issues. By querying this layer, companies can identify and address data quality problems early in the data processing pipeline, ensuring that subsequent analyses and reports are based on accurate and reliable data.
Understanding system processing: The bronze layer provides insights into how data is processed and transformed through the data pipeline. By examining raw data, businesses can understand the impact of various processing steps and identify any inefficiencies or bottlenecks in the system.
Granular system data: The bronze layer contains detailed and granular system data that may not yet be included in the silver, gold, or semantic models. This data can provide valuable insights into specific aspects of the business that require a more detailed analysis. For example, raw log files from a web server can be analyzed to understand user behavior and identify potential issues with the website.
Example use case:
Consider a financial services company that needs to ensure the accuracy of its transaction data. By querying the bronze layer, the company can perform a detailed analysis of raw transaction records to identify discrepancies or errors. The analysis might include:
Data consistency checks: Verifying that transaction amounts match between different systems (e.g., payment gateway and accounting system).
Error detection: Identifying transactions with missing or incorrect information (e.g., missing customer IDs or invalid transaction dates).
System performance analysis: Analyzing raw log files to understand the performance of the transaction processing system and identify any bottlenecks or failures.
By addressing these data quality issues at the bronze layer, the company can ensure that the data used in subsequent analyses and reports is accurate and reliable. This leads to better insights and more informed decision-making.
By leveraging the bronze layer in Microsoft Fabric, businesses can perform data quality research and better understand their system processing and granular data. This foundational analysis is crucial for ensuring the accuracy and reliability of data, ultimately supporting better business insights and decisions.
System performance information with the KQL database
The KQL (Kusto Query Language) database in Microsoft Fabric provides detailed system performance-level information. By leveraging Data Agent to query the KQL database, businesses can gain valuable insights into system performance issues, such as load processing times, and proactively optimize their infrastructure. Key benefits of the KQL database include:
Detailed performance logs: The KQL database stores comprehensive logs of system activities, including performance metrics, error logs and resource usage. This data is crucial for identifying and diagnosing performance issues.
Real-time monitoring: Data Agent can query the KQL database in real-time to monitor system performance. This enables businesses to detect and address issues as they occur, minimizing downtime and ensuring smooth operations.
Proactive optimization: Businesses can identify patterns and trends that indicate potential bottlenecks or inefficiencies by analyzing performance logs. This allows them to optimize their systems proactively and improve overall performance.
Example use case: Load processing analysis
Consider a scenario where a company needs to monitor the performance of its data processing system, specifically focusing on load processing times. By querying the KQL database, Data Agent can pull out detailed logs related to load processing and identify any long load times currently occurring. The query might look something like this:
// Query to identify long load times
LoadProcessingLogs
| where Timestamp > ago(1h)
| where LoadTime > 10000 // Load time in milliseconds
This query retrieves logs from the past hour in which the load time exceeded 10 seconds (10,000 milliseconds). The results include the timestamp, load ID, load time, source system and error details. By analyzing these logs, the company can identify loads that are taking longer than expected and investigate the underlying causes.
For example, the analysis might reveal that specific data sources are experiencing delays due to network issues or that specific processing steps are causing bottlenecks. Armed with this information, the company can take targeted actions to resolve these issues, such as optimizing network configurations or refining processing algorithms.
By utilizing the KQL database in Microsoft Fabric, businesses can gain real-time insights into system performance and proactively address any issues. This ensures that their data infrastructure remains robust and efficient, supporting the overall data strategy and enabling smooth operations.
Conclusion
By leveraging the latest capabilities in Microsoft Fabric, businesses can implement Data Agent to access and analyze data across multiple layers and sources, providing a comprehensive view of their data and analytics. This approach enables organizations to gain deeper insights, improve operational efficiency and make data-driven decisions.
Data Agent in Microsoft Fabric offers a unique advantage by integrating seamlessly with the medallion architecture, encompassing the bronze, silver and gold layers, as well as the semantic model and KQL database. This integration allows Data Agent to:
Access raw data: Query the bronze layer for initial data analysis and quality research, ensuring that data is accurate and reliable
Generate operational reports: Utilize the silver layer to create detailed reports that drive day-to-day activities and decision-making
Perform advanced analytics: Leverage the gold layer to handle complex SQL logic and generate in-depth analytical reports that provide valuable business insights
Understand business context: Integrate with the semantic model to interpret complex data relationships and deliver insights that align with current reporting and analytical needs
Monitor system performance: Query the KQL database to gain real-time insights into system performance, identify bottlenecks and optimize resource usage
This comprehensive approach ensures that businesses can access all relevant data in one place, providing a level of detail and context that no other tool has yet been able to capture. By harnessing the power of Data Agent in Microsoft Fabric, organizations can unlock the full potential of their data, drive success and stay competitive in today’s data-driven world.
How we can help
With Data Agent, businesses can transform their data analytics processes, making informed decisions based on accurate, relevant and timely insights. Baker Tilly’s digital solutions team, working in collaboration with Microsoft, can help your organization leverage Data Agent in the Microsoft Fabric ecosystem to empower decision-makers to navigate complex business challenges and confidently seize new opportunities.
