About the Customer
Industry: Store Implementation
The London-based client deals with retail store implementation and brand location activation at different shopping environments like airports, border shops, ferries across the world. With the active global network and retail merchandising expertise, they work with an array of industries and brands to set up their retail environments.
The client has asked us for the business intelligence solution to track their project income, expense, P&L, location activities, project type, and so on. They wanted to make the best use of data to analyze, improve, and create better engagement with their worldwide customers.
Key Challenges
Data quality and data management were two major challenges that required careful planning and execution.
- Data Quality: Source data quality is the issues as we are receiving the many fields from source data which needs to be manipulated to populate in our reports. As many of the fields in the transaction system populate in the source system in text boxes by users without any validation. Hence, the quality of the data is very poor. Our experts have used power queries to cleanse main fields that are necessary for reporting: For example
Project size – project size is not defined separately in the transaction system. It was mentioned in the project title, so we have to extract the project size based on some business logic.
Project Type – Project type is text field in the source system so we have filtered data by applying certain business rules.
- Xero Integration: Client was using Xero as a financial application and therefore, extracting data using the API was also difficult to achieve for our team.
Our Solution
SPEC India understood the requirements and provided an end-to-end Business Intelligence solution that helped them to better analyze the Xero data to get insights on the project’s financial KPIs. It eventually helped customer to share the real-time information about the project’s status to key stakeholder and take better decisions to make the project success.
Our solution was integrated with the following features using Power BI:
- Cleansing the main fields like project size, installation date, project title data with power query and DAX
- Displayed the Projects Infographics from AWS
- Real-time KPIs – measures – Project count by Client, Region, Brand, P&L, Income, Expense
- MoM and YoY comparisons for client/projects/brands
- For-Profit and loss report by branch, we have used the following solution
- Extract Xero application data by using the API and store in MSSQL
- Load data in Power BI and create Profit and loss report
Tools and Technologies
Report Screens
Business Benefits
- Proved to be a cost-effective solution for their customers
- Provided effective and well-optimized reports with rich graphical displays
- Provided an enhanced insight to make an optimal business decision
- The solution was easy-to analyze and helps explore many data fields
- With the help of this solution, a business user can make a strong decision to grow or to focus on a particular area, region, and client