Introduction
In today’s data-driven landscape, leveraging financial analytics stands as a cornerstone for informed decision-making and strategic planning. As part of our initiative, we’ve implemented robust Procurement and Spend analytics using Microsoft Fabric, harnessing data from Oracle EBS (E-Business Suite) to empower businesses with actionable insights in Power BI.
Understanding Procurement and Spend Analytics
Procurement and spend analytics involve the systematic analysis of an organization’s purchasing and expenditure data to extract valuable insights, including Procurement Overall, which provides a comprehensive view of procurement activities and identifies efficiency improvements and cost reductions; Approval History for Purchase Orders and Purchase Requisitions, which tracks approval processes and identifies bottlenecks to streamline workflows; Invoice Spend Data, which details spending based on invoices to manage cash flow and negotiate better terms with suppliers; and Spending Snapshot, which offers a quick overview of current spending to make informed budget decisions and identify cost-saving opportunities.
Key Components of Our Solution
- Data Sourcing from Oracle EBS: We’ve successfully extracted and processed crucial procurement data from Oracle E-Business Suite, ensuring accuracy and reliability.
- Cloud-based Data Storage and Processing: Leveraging Microsoft Fabric’s scalable infrastructure, we’ve stored raw data in Azure Blob Storage and efficiently processed extensive volumes of procurement data. This provides us with a comprehensive data pipeline solution for ingesting, transforming, processing, scheduling, and delivering data into the Fabric Warehouse..
- Analytics and Visualization: We have leveraged Power BI, now a part of the Microsoft Fabric family, to translate complex data into actionable insights.
- Self Service: Extensive Power BI semantic layers and data models are built to facilitate self-service for users by ensuring all the required fields are available in the semantic model.
- Security: Robust security setup controls the authentication and authorization, to ensure only authorized users can access the tool and the data.
Microsoft Fabric Reference Architecture
Data from the Oracle EBS are initially ingested into Azure Storage Blob. Following this, the data undergoes a series of transformations: first into the Bronze layer via copy activity in the data pipeline, then into the Silver layer schema, which is built on Fabric Warehouse and data is populated through the Dynamic stored procedure. The data is further cleansed and transformed to meet business requirements in the Gold layer, facilitated by Warehouse Stored Procedures. This project is based on Medallion architecture which is implemented using the Microsoft Fabric Warehouse.
This workflow, encompassing Bronze, Silver, and Gold layer scripts, runs automatically on a daily basis using Data pipeline schedules. Throughout this process, data is denormalized according to predefined data models and adheres to naming standards and conventions. Finally, Power BI accesses the Gold layer for reporting purposes, ensuring alignment with business needs.
Procurement Analytics – Data Flow
Keys Processes
- Data from the source system (Oracle EBS) is ingested into Azure Blob storage through a Microsoft Fabric Data Pipeline. After initial load, the data is pulled incrementally to Azure blob storage.
- Data from the Azure blob storage is ingested into Bronze layer – Warehouse Stage schema through Microsoft Fabric Data Pipeline.
- Then the data is ingested into the Silver layer schema. This merging of data from the Bronze layer to Silver layer of Microsoft Fabric is implemented through a Dynamic Stored procedure which is created in Fabric Warehouse.
- Once the data is merged from Bronze to Silver layer schema, the data files that were created in the Azure Blob Storage are moved to an archive location within the Azure Blob Storage.
- From Silver layer to Gold layer (EDW) the data mart is cleansed and transformed according to the business requirement through the stored procedures of Fabric Warehouse.
- The pipeline jobs are executed on a daily basis to get the most recent data from the source.
- The execution of the data pipeline jobs is automated using the built in scheduler.
- Power BI is connected to EDW layers for all the reporting needs.
Procurement Workflow – Example
Microsoft Fabric’s Data Pipelines are used for data processing and orchestration. Here, we have shown an example pipeline for the Procurements module
In the pipeline above, each activity is connected to the next and will be executed upon the completion of its dependent activity
- Configurations: In the first step of the pipeline, configurations related to source connections, target connections, warehouse schema names, file archive paths, and source system IDs are gathered.
- Source Files: In the second step, the file list is validated, and all the files that need to be processed and loaded from the source to the staging layer are gathered.
——————————-Start Loop——————————–
Bronze Layer Processes –
From this step onwards, the process enters a loop based on the number of source files available in the source file list. The number of iterations corresponds to the total number of source files
- Drop Table: The third activity is a script activity that drops the staging tables before recreating and loading the new data.
- Source To Stage (Loads to Bronze Layer): The fourth activity is a Copy Data activity. It uses the source connection to Blob Storage and the destination connection to the Fabric Warehouse. This activity copies the data from Azure Blob Storage to the Warehouse for each individual table in the bronze layer.
Silver Layer Processes –
- Stage To Ods (Loads to Silver Layer): This activity involves a dynamic stored procedure created in the Fabric Warehouse using T-SQL. The stored procedure takes parameters from the lookup configurations and source file list. Essentially, it loads data from the Stage to the ODS schema. The ODS schema is a cleansed version of the bronze layer, incorporating operations such as populating current dates and timestamps, setting active flags (soft deletes), and considering archive dates when setting active flags. Both update and insert operations are performed in this step. This layer maintains historical records and inserts or updates incremental records.
———————————End Loop———————————
Gold Layer Processes –
- Gold Layer Jobs: This is the initial phase in populating the data in the Gold layer. In this step, we identify the dependencies and task names in the sequence required for subsequent loading into the Gold layer.
- SQL Jobs: This is the final step where the actual data population into the Gold layer occurs. In this stage, the warehouse stored procedures are executed dynamically according to the dependency list gathered in the previous step. Both update and insert operations are performed here.
Gold layer tables –
- Procurement-SupplierSites
- Accounting-CodeCombinations
- Accounting-Ledgers
- Procurement-Requisition
- Procurement-ApproverHistory
- Procurement-PurchaseOrders
- Procurement-InvoiceDistributions
- Procurement-Combined
Microsoft Fabric Pipeline – Schedules
Moreover, we have used the Fabric’s data pipeline’s native scheduler to schedule the pipeline on a daily frequency.
Addressing Hard Deletes
In this project, we assume that we receive a complete dataset from the source on a monthly basis. A separate data pipeline is dedicated to managing hard deletes. Once the configurations and source file list are gathered, this pipeline enters a loop to perform a full load of the bronze layer. Subsequently, it invokes a dynamic stored procedure that updates the active flag in the silver layer if the corresponding target record is absent in the bronze layer. Additionally, the stored procedure considers archive dates during updates to the silver layer.
Procurement and Spend Analytics – Data Model
For Procurement and Spend Analytics, four distinct reports have been created to meet specific business needs.
- Procurement Overall
- Approval History for PO & PR
- Invoice Spend Data – Paid & Unpaid
- Spend Data
We’ve developed reports in Power BI based on the aforementioned data model. For audience management within the Procurement domain, we’ve utilized Power BI’s app feature.
An app is employed to curate packaged content and disseminate it to wider audiences. It amalgamates related content such as dashboards, spreadsheets, semantic models, and reports into a unified platform. Each app comprises one or more of these content types bundled together. In this instance, we’ve established the ‘Procurement Workspace’ as our designated app
Procurement APP
Procurement Overall Dashboard & KPIs
Procurement Overall dashboard provides a comprehensive view of procurement activities like spend analysis in procurement and identifies efficiency improvements and cost reductions.
Procurement Overall Dashboard
The Procurement Overall report offers a holistic view of the organization’s procurement activities, tracking the flow from requisitions to orders and finally to invoices. It highlights the top suppliers contributing to the procurement process and provides a month-by-month procurement analysis using power BI . Additionally, the report includes a detailed PO analysis, showcasing specific transactions and their statuses.
Procurement Overall KPIs
- Requisition Amount: This KPI represents the total value of all requests for procurement, indicating the demand for goods and services within the organization.
- Order Amount: This KPI measures the total value of all purchase orders issued, reflecting the organization’s commitment to procuring the requested goods and services.
- Invoice Amount: This KPI tracks the total value of all invoices received from suppliers, providing insight into the actual costs incurred by the organization.
- Invoice Paid: This KPI indicates the total amount that has been paid against the invoices received from suppliers. It reflects the organization’s payment performance and financial obligations met.
- Invoice Unpaid: This KPI shows the remaining amount that has not been paid against the invoices received. It helps in identifying outstanding payments and managing cash flow.
- PO Number: A unique identifier assigned to a purchase order. It is used to track and manage the order throughout the procurement process.
- Supplier Name: The name of the company or individual providing the goods or services. It is crucial for identifying and managing relationships with the organization’s vendors.
- Supplier Number: A unique code or number assigned to a supplier for identification and tracking purposes within the organization’s procurement system.
The KPIs serve as a barometer for the organization’s procurement health, enabling stakeholders to monitor efficiency, identify trends, and make informed decisions. By examining these KPIs, the organization can assess its procurement strategy’s effectiveness and take steps to optimize its processes.
Approval History for PO & PR Dashboard and KPIs
Approval History for PO & PR Dashboard and KPIs
Approval History for PO & PR KPIs
- Approval Requisition Amount: This KPI helps track the organization’s commitment to spending on requested goods and services. It reflects the demand for procurement.
- Approval Order Amount: This KPI indicates the actual financial commitment made by the organization for procuring goods and services.
- Type: Distinguishes between different procurement documents, such as Requisition or Invoice or Order.
- Status: Indicates the current stage of the PO or PR in the approval process such as Approved or Rejected or WQHD.
- PO Number/PR Number: Unique identifiers for Purchase Orders (PO) and Purchase Requisitions (PR), used for tracking and management.
- Department Leader: The individual responsible for overseeing the department that initiates the PO or PR.
- Supplier Name: The name of the company or individual providing the goods or services. It is crucial for identifying and managing relationships with the organization’s vendors.
By monitoring these KPIs, an organization can gain a clear picture of its procurement activities, identify bottlenecks in the approval process, and make informed decisions to improve overall efficiency.
Invoice Spend Data – Paid and Unpaid Dashboard and KPIs
Invoice Spend Data – Paid and Unpaid Dashboard
The “Invoice Spend Data – Paid and Unpaid”report offers an analysis of the organization’s financial transactions related to invoices. It encompasses both the amounts that have been paid and those that are still pending, providing a clear picture of the company’s current financial commitments and payment practices. The report categorizes this data by various dimensions such as ledger names, vendor names, years, and countries, allowing for a multi-faceted view of the organization’s spending behavior.
Invoice Spend Data – Paid and Unpaid KPIs
- Invoice Amount: This KPI measures the total value of all invoices generated, reflecting the cost of goods or services procured.
- Amount Paid: This KPI indicates the total amount that has been paid against the invoices, showing the organization’s payment performance.
- Amount Unpaid: This KPI represents the total amount still owed on the invoices, highlighting outstanding financial obligations.
By examining these KPIs, stakeholders can assess the efficiency of the payment process, identify areas where financial management can be improved, and ensure that the organization maintains a healthy cash flow.
Spend Snapshot Dashboard and KPIs
Spend Snapshot Dashboard
The “Spend Snapshot” report offers an analysis of the organization’s invoice-related expenditures, encompassing both paid and unpaid amounts. It provides a clear picture of the company’s current financial commitments, payment practices, and the efficiency of the payment process. The report categorizes this data by various dimensions such as time (monthly and quarterly), association with purchase orders, and geographical distribution, allowing for a multi-faceted view of the organization’s spending behavior.
Spend Snapshot KPIs
- Invoice Amount: This KPI measures the total value of all invoices generated, reflecting the cost of goods or services procured.
- Amount Paid: This KPI indicates the total amount that has been paid against the invoices, showing the organization’s payment performance.
- Amount Unpaid: This KPI represents the total amount still owed on the invoices, highlighting outstanding financial obligations.
- Invoice Amount With PO: This KPI represents the total value of all invoices that are associated with a Purchase Order (PO). It indicates the spending that has been pre-approved and is part of the organization’s planned procurement process.
- Invoice Amount Without PO: This KPI accounts for the total value of all invoices that have been processed without an associated Purchase Order. It reflects ad-hoc spending or expenses that may not have gone through the standard procurement approval process.
By examining these KPIs, stakeholders can assess the efficiency of the payment process, identify areas where financial management can be improved, ensure that the organization maintains a healthy cash flow, and make informed decisions to optimize spending.
Benefits of Our Solution
- Informed Decision-Making: Empowering stakeholders with timely and accurate insights for strategic decision-making.
- Operational Efficiency: Streamlining procurement processes, optimizing resource allocation, and identifying cost-saving opportunities.
- Scalability and Flexibility: Fabric’s scalable infrastructure facilitates handling large datasets while offering flexibility in adapting to evolving business needs.
- Compliance and Security: Ensuring data integrity, privacy to maintain trust and credibility.
- One Stop Solution: One stop solution for all the procurement data needs.
Conclusion
Our implementation of procurement and spend analytics on Microsoft Fabric represents a commitment to harnessing the power of data for strategic advantage. By leveraging Fabric’s robust infrastructure, we empower businesses to make informed decisions, mitigate risks, and drive sustainable growth in an ever-evolving market landscape
How Dataplatr Can Support?
Dataplatr specializes in data engineering & analytics with pre-built data models for Enterprise Applications like Oracle EBS, Workday, Salesforce to empower businesses to unlock the full potential of their data. Our pre-built enterprise data engineering models are designed to expedite the development of data pipelines, data transformation, and integration, saving you time and resources. Our team of experienced data engineers, scientists and analysts utilize cutting-edge data infrastructure into valuable insights and help enterprise clients optimize their Sales, Marketing, Operations, Procurements, Financials, Supply chain, Human capital and Customer experiences.
Curious and would like to hear more about this article ?
Contact us at [email protected] or Book time with me to organize a 100%-free, no-obligation call
Follow us on LinkedIn for more interesting updates!!