Housing and Urban Infrastructure Solutions Company Automates their Payroll Allocation and Archival using Microsoft Fabric


VNB Consulting assisted a Housing and Urban Infrastructure Solutions company with creating allocations of payroll data received from their payroll provider. They presently operate only for the current period and wanted to look at options of archiving the allocated payroll data and be able to derive intelligent analytics on the overall data.

Customer Profile

Customer is a leading Housing and Urban Infrastructure Solutions company who desire to be a company that leaves its footprint of excellence as evidence of our positive impact in communities. They continue to set the stage for better performance in the areas of urban revitalization, infill development, smart growth, and public private partnerships.

Customer Situation

Customer Situation

Customer wanted to create allocations of payroll data provided by the payroll providers for around hundreds of employees based on different pay codes. They receive the raw data from the payroll providers, and they need to perform several steps correctly do the allocations. The allocated payroll is the final entity for them for creating journal entries. Customer would like to do payroll analysis after the successful allocations. Their current system operates only on current period which has thousands of rows of data, but now they also want to store the historical allocated payroll data in some kind of archive table and then perform the next load for current period. The consolidation of historical and current payroll allocations would then allow for customer to derive better analytics and forecasting.

Our Solution Offering

Solution Offering

After a few initial analysis and design discussions with the customer on their exact requirements, our team suggested few options like: Microsoft Fabric, Dataverse, SQL Server, etc. keeping in mind their current technology infrastructure. At the end, VNB Consulting team and the customer decided to go with Microsoft Fabric because this solution was the closest and most efficient one to meet all their expectations. Microsoft Fabric allows for the storage and utilization of data in a standardized format, eliminating unnecessary replication and enabling seamless integration with various Microsoft tools. Our BI team came up with the solution in Microsoft Fabric by making use of the data warehouse, Dataflow Gen2 and data pipeline components. Here, Dataflow Gen2 will be utilizing its Power Query functionality to get the raw data from their payroll providers and then do the correct allocations of payroll data. After the successful allocations of payroll data, the data warehouse workload of Microsoft Fabric will be used to archive the allocated data before the execution for current period. Data pipeline is used to automate the process of creation of allocations for payroll data and archive the data in data warehouse. The data in data warehouse is consumed by the Customer for their reporting and journal entry needs. Our BI team also made sure to document all the technical details to list all the technicalities and general aspects of our solution for any future references.

Customer Benefits

  • All latest and archived allocated payroll data stored in a single storage i.e., Microsoft Fabric Data Warehouse
  • A clean and fast way of extracting data from various data sources and to store it in data warehouse for all the reporting needs after the required transformations to create the allocations
  • An automated system to create the payroll allocations for every scheduled interval (ex: days, weeks, etc.)
  • Quick turnover on the complete ETL solution
  • A solution which is easy to consume, learn and maintain
  • Detailed knowledge transfer and technical documentation with the explanation of all the aspects of the solution for future references.

Related Case Studies

Streamlined EDI 834 Benefit Enrollment Processing through Azure Logic Apps

VNB Consulting collaborated with a leading health insurance provider to transform EDI 834 benefit enrollment processes.


Empowering a Utilities Company with Seamless OBIEE to Power BI Migration for Amplified Analytical Advantages

VNB Consulting helped a Utilities company migrate from OBIEE to Power BI, resulting in significant cost savings while also improving their analytics capabilities.