Microsoft Power BI Consulting

Data-Driven Success: A Retail Case Study in Revenue Growth and Customer Insights

The Client

Based in the Middle East, the client has offices all over the world. They are a global commodity trading and logistics company.

The Challenge

The challenges that presented themselves were

• Managing the vast amount of data from various sources
• Validating the data as per the Excel files
• Analyzing the data in a better way using appropriate visual representation with the large amount of data
• Adding the comment and other invoice-related details in the PowerBI reports
• Maintaining data confidentiality
• Distributing the reports as per the roles and department

The Solution

What we did was

• Data Extraction and management:
  • As the Excel files were in the client’s shared location, we created a Robocopy script which is a command-line utility built into Windows operating systems to copy the files from one location to another and this can be scheduled using the Task schedular available in the windows.
  • After copying the data, we stored it in a location and configured it as source in the SSIS (SQL Server Integration Services which is a tool used for ETL purposes) package along with the connections of the source databases.
  • We created the solution in the SSIS that will extract the data from the sources and apply the required transformations as per the business logics and store the data at a centralized location/destination database.
  • This process is incremental which will fetch only the updated or newly created data from the source.
  • We also implemented the error handling that will update the client and the developer team if the package fails for any reason.
• Data Validation:
  • To validate the data and to check if any data discrepancy is there or not, we created the solution in the SSIS that will check the data in both the sources (In the master Excel file and the database) and share the anomalies/data discrepancy over an email of the respective entity manager.
• Data Analysis using PowerBI reports:
  • Once the data arrives at a centralized location/destination database, after applying the required logics, we used the tables in the PowerBI to make the reports which can help to analyze the data in a better way that can help to take wise decisions.
• Manual data entry using PowerApps in the PowerBI reports:
  • To achieve this requirement, we used the inbuilt PowerApps visual in the PowerBI Desktop and created a new PowerApps to use it in the reports. As the end users wanted to add the data manually, this integration of PowerApps with PowerBI Desktop enables the end users to enter the data or comment from the reports through the embedded PowerApps using the inbuilt PowerApps visual in the PowerBI Desktop.
• Data Confidentiality:
  • To achieve data confidentiality, we applied the Row Level Security (RLS) available in the PowerBI Desktop. For creating the tables to apply the RLS, we created an SSIS package that would update the RLS table as per the data in the RLS Excel file maintained by the client.
  • We created a group on the Azure Active Directory as per the department and added the same group on the PowerBI service to apply the Row Level Security (RLS).
• Content Sharing:
  • To share the reports/content to the specific group of end users, we created the apps (a collection of dashboards, reports, and datasets that are packaged together for a specific purpose or audience) on PowerBI service with different audiences as per the access/requirements.
  • We added the group created on the Azure Active Directory in the app workspaces to give access to the content as per the access/requirements.

Other Features

• Data Extraction and management:
  • The proposed solution made data extraction and management so easy and automated the task of fetching the updated data daily from various sources and storing it to the destination database after applying the required data cleaning and transformation.
  • This solution significantly reduced time and manual effort involved in the task.
• Data Validation:
  • Prior to implementation, validating large data was laborious and error-prone due to manual intervention. With the new solution, automated data comparison detects and reports discrepancies promptly, enhancing accuracy and efficiency in data validation processes.
• Data Analysis using PowerBI reports:
  • After implementing the solution and creating PowerBI reports with schedule refresh, the data analysis process became so seamless and accurate as the report will show the updated data as per the business logic.
  • Data Analysis process became more user friendly and interpretable because of the visual representations of data. This will help the business to take decisions in a better way.
• Manual data entry using PowerApps:
  • By integrating the PowerApps with the PowerBI report, the end users can enter the data manually and see the changes instantly in the PowerBI report. This feature enables the end users to add comments for a specific product or transaction using the report only eliminating the need for another tool.
  • Users can save time and effort by interacting with a single report for analysis, adding comments, and inputting other data efficiently.
• Data Confidentiality: 
  • The use of Row level security (RLS) eliminated data confidentiality-related issues.
  • Extracting data from client Excel for RLS tables allows for automated change in the RLS policies, eliminating manual updates and saving time. This ensures consistency and accuracy in implementing RLS measures.
• Content Sharing: 
  • With apps and audiences, it is now easy to distribute the content to the required group of people. It only requires sharing the link of the app workspace.
  • In case of adding any new user, we just need to add the user to the respective audience in the app.

The Result

The implementation of an end-to-end data analytics solution has not only addressed the challenges of managing vast data sources and ensuring data accuracy but has also enhanced decision-making processes through visual data analysis. By integrating tools like PowerBI and PowerApps, the company has enabled manual data entry and improved data validation processes by automation using SSIS. The implementation of Row Level Security ensures data confidentiality, while content sharing mechanisms facilitate targeted report distribution. This comprehensive solution has not only streamlined operations but has also driven revenue growth and provided valuable customer insights for the business.