- Our client needed help prioritizing and estimating the effort involved in migrating roughly 2,500 legacy paginated reports from Cognos into Power BI Report Builder, as well as requiring assistance with some of the resulting migration work.
- With less than five months to sunset Cognos reports used across a variety of business areas, the Aptitive team helped to relieve pressure with our Power BI paginated reports solution.
- Aptitive saved this manufacturing client approximately $250,000 on licensing costs while still providing the report-scheduling functionality they required.
Just the Headlines
Short on time? Here are the key facts.
This manufacturing client didn’t want to renew their Cognos license for their existing legacy reports because they would have to spend approximately $250,000 on licensing and were already making a push to move reporting activity into a Power BI environment. They had originally identified roughly 2,500 Cognos reports and needed help to prioritize and assess the level of effort required to migrate their legacy paginated reports into Power BI Report Builder reports. Additionally, the client wanted a solution that would ensure the existing functionality to schedule report subscriptions would still be available.
Power BI Report Builder
Our client was facing pressure to sunset their Cognos reports by the end of the year, within about four and a half months. Business users across different functional areas (including manufacturing, sales, accounting, and supply chain) relied on these reports for their day-to-day operations and needed to be able to trust the reports. Additionally, the existing reports were built with a tool that generated large amounts of complex SQL and they needed expertise to untangle and optimize them for a Power BI reporting environment.
Aptitive worked with the client team to prioritize and assess the level of effort required for reports they wanted to migrate, also identifying the sources of our client’s existing Cognos reports and checking for areas to optimize. For example, because the SQL from the Cognos reports was generated by the tool’s metadata model and not written by a person, there were some places where the Aptitive team could remove extra joins or unnecessary aggregation that weren’t needed in the report. This sometimes resulted in better performance but always resulted in a more readable/maintainable query for the future.
We then migrated report queries to be able to be run as Snowflake views and used the newly created views to build Power BI datasets that connect directly to Snowflake. These Power BI datasets were used to create the final paginated reports.
After that, Aptitive used Power Automate and Power BI Service to manage the report schedules. These were slightly different from our client’s original method of scheduling reports, where an administrator would have to set up each schedule. Now, they use Power BI Service for most of the reports so users can schedule their own subscriptions in a “self-service” way. A few reports that are sent externally or are used by very specific teams still have schedules set up by an administrator with Power Automate.
As the Aptitive consulting team went through the user testing cycle, we were able to make iterative improvements to the UI/UX of the reports as requested, such as removing infrequently used columns or adding filters for particular fields.
Aptitive facilitated a seamless transition of reports from legacy reports into Power BI paginated reports. We saved our client approximately $250,000 on licensing costs by moving their Cognos reports into Power BI, which they had already budgeted for. Plus, in eight weeks, they could maintain all existing business functionality while completing the migration.