This site uses cookies. Learn more

Report Optimization and Forecasting in Power BI for an Affiliate Management Agency

P2H helped a leading affiliate management company improve its data reporting efficiency, reduce costs in AWS, and enhance data-driven decision-making.


    Accelerate partners LLC


    Affiliate marketing


    Our Client has helped many brands grow with innovative affiliate marketing services. They design and manage promotional campaigns, build influencer programs, and nurture business relationships. As a professional agency, their service offering is a strategy supported by relevant data.

    The company’s analytics platform uses Microsoft Power BI to collect business metrics across the affiliate network space. They rely on its robust reporting functions to benchmark various KPIs, including metrics related to marketing campaigns and partner’s performance. Some of their clients required monthly and even weekly reviews of their affiliate campaigns, so the system had to work perfectly.

    As Our Client expanded its client base, its databases grew from thousands to several hundred thousand records. Eventually, generating a single report in Power BI might take more than five hours.

    While the client initially asked us to analyze and optimize their reporting system, in the course of further cooperation with Our Client, we exposed other areas for improvement. Ultimately, it soon became apparent that we had to reconfigure most of the back end.

    Another part of the client’s task was to solve the issues caused by the exponentially growing infrastructure costs. Our Power BI analyst reviewed the inefficiencies and greatly improved both performance and computation costs, thus reducing costs for Our Client.


    We fulfilled the client’s core requirements and continue to enhance their
    analytical capabilities. Our achievements so far include the following:

    • Power Query optimization accelerated the report generation capabilities and solved critical bugs.

    • The improved Power BI performance made the platform more convenient for the client’s analytics and accounting teams.

    • We decreased the time needed to generate analytics reports in Power BI from five hours to less than ten minutes.

    • The data grouping and minimized calculations led the company to save cost for computing calculations in AWS.

    • The client was pleased with the analytics module, which allowed them to forecast income from different marketing campaigns, predict the performance of future partners, and prioritize the most profitable channels.


    Data grouping

    Our Power BI expert reorganized large tables that accumulated all the business metrics and thus became unmanageable. Some of the data were needed only once or weren’t relevant after a specific period. We decoupled those data into separate tables and archived them to reduce file sizes.

    Then, we divided the rest of the table into several smaller tables based on the data type. Instead of one gigantic file, the client had tables categorized by parameters like the creation date, customer location, and other variables. This alone made report generation faster and alleviated the computing strain on AWS.

    Forecasting module

    During further collaboration with the client’s team, it was decided to create a forecasting system, as it would provide benefits across the organization. Specifically, this would allow Our Client to estimate marketing expenses and channel performance and determine which investment possibilities would be the most profitable for their clients.

    As part of the initiative, the team developed custom machine learning models. Our engineer trained the models with the client’s historical business data, used AutoML to refine their accuracy, and implemented them in Power BI. The final model could predict affiliate marketing performance based on historical data and follow customizable variables like:


    Our Client could use machine learning models to predict the profitability of their clients’ campaigns and test potential partnerships.

    Back-end and front-end optimization

    We modified the back end in Power Query to improve the system’s performance and stability. Here are some of the steps we took:


    We removed about 70% of queries that affected the system and were excessive. This required us to tune DAX expressions and RLS rules and rewrite logic to avoid performance-expensive formulas. We also reduced the number of rows, columns, and measures performed at runtime.


    We fully rewrote a significant number of the Power Query parameters. Our Power BI analyst applied appropriate indexes, defined table partitions, and materialized summarized data with indexed views.


    Our engineer replaced expensive processes with streaming operations that don’t read all the data before returning results. In addition, P2H kept transformations in query design to a minimum to improve performance.


    We also applied preload to ensure that scripts for embedding Power BI content were downloaded before navigating the file. This greatly improved content display speed in embedded reports and dashboards.


    Bidirectional filtering often leads to performance issues, so we applied single-direction relationships and referential integrity in models wherever possible.


    The files were already lighter after we divided tables across files and minimized calculations. On top of that, by using binning, clustering, and compression techniques, we ensured all the files are under 1 GB.


    • Many Power BI analysts don’t devote enough attention to optimization and scalability. This may not pose a problem until a platform starts trying to handle the increased load. But it can be difficult to see exactly what’s causing the platform’s performance issues, which is why we needed to study the data source and model layers first.

    • We found that all of the data for Power BI was in very large files. Data for metrics that the company required continuously and data that required only infrequent access were bundled together, putting a strain on the system and decreasing the system’s performance.

    • Most of the calculations in reports and the requests to back-end sources were excessive. Many operations weren’t pre-calculated, meaning that calculations had to be calculated at runtime. This affected computing performance negatively and increased AWS computing costs. In most cases, we had to manually rework the reports.

    • Our consultant also ran into data limitations of the Power Query online service from Power BI. The company’s cloud storage plan limited single file size to 1 GB. So, we had to find ways to reduce file sizes so they wouldn’t have to upgrade to a more expensive subscription.

    Team Composition

    Our Power BI engineer carried out the project under the supervision of the project manager and with the indirect participation of the client’s CTO and business analyst.

    Future Plans

    • Solution Design and Consulting

      Our Client wants us to optimize the rest of their reports. At the same time, we take necessary measures to help the client avoid similar issues in the future. Additional work will include back-end and front-end development for the Power Query platform.

    • Software Development

      Our Client is currently reworking its input data setup for AWS. We aim to optimize Power BI for the new setup, further reducing technical overhead and processing costs.

    • Support

      On top of this project, we’re working to transform clients’ designs into high-performing applications, websites, and automated solutions.

    Case Studies

    Let us know how we can help

    Let’s discuss your project! Drop us a line and our team will be in touch with you.

      Maximum total files size 25mb


      Thank you for getting in touch!

      We appreciate you contacting us.
      One of our colleagues will get back in touch with you soon!
      Have a great day!

      This site is registered on as a development site.