Rolling Out Power BI in a Cognos Shop

Here’s a scenario we come across almost every day: An organization is running Cognos Analytics. It has groups or departments that want to better leverage the capabilities of the tightly integrated Microsoft Office 365 product suite by visualizing data in Power BI. The temptation might be to duplicate the complexity, security and governance of data models that reside in Cognos Analytics into Power BI datasets.

But that solution is rarely simple, timely or effective. It ignores the years (more often, decades) of investment that has advanced the organization’s current data models to reliably create the reports their business runs on today.

Other clients we speak to realize the sheer volume of their Cognos assets makes moving them into Power BI daunting. As a result, they end up in project paralysis.

With all that in mind, let’s look at three options for accomplishing the goal of reporting in Power BI without spending years on a massive project.

Option 1: Connect Power BI to Cognos data

The first option is to connect Power BI to Cognos via the Senturus Analytics Connector. This tool was developed specifically to get to reporting in Power BI quickly and cost effectively. By deploying the Analytics Connector, your organization can quickly start leveraging all your existing Cognos data models from Power BI reports.

This works best if your organization intends to maintain the authoritative data model layer in Cognos and wants to leverage it in Power BI as well. This can also serve as an accelerator in migrating fully to Power BI, helping the project to quickly gain some visible “quick wins” to justify the investment needed to rebuild data models in Power BI and eventually jettison Cognos.

Option 2: Quickly convert Cognos reports to Power BI

Another option for creating Power BI reports that are similar to existing Cognos reports includes reusing the SQL that Cognos generates as the basis for the Power BI report. This is a down-and-dirty method that “gets things done.”

This approach is going to build up some tech debt and wouldn’t be recommended if you are looking to do an enterprise-grade deployment of Power BI. However, it might still be useful in scenarios such as proofs of concepts, small deployments and temporary projects.

How to quickly convert a Cognos report:

    1. View the generated SQL/MDX for the report.
      Cognos Analytics 12.0-10
    1. Select the queries one at a time and copy the Native SQL into a document or notepad. You will also need to understand the underlying connection of the query, typically a data warehouse database or source system database.
      Cognos Analytics 12.0-10
    1. Use the Get Data feature of Power BI and paste the first query into the SQL statement section. The connection type and credentials will depend on the underlying data source.
    2. Use Power Query Editor to create additional queries, then visualize the data in Power BI.
      Cognos Analytics 12.0-10
Option 3: Tool-assisted migration of critical Cognos models to Power BI 

The third option is to focus on migrating critical Cognos models with the assistance of our purpose-built Cognos migration tool, the Senturus Migration Assistant. This option is best suited to those who have time to invest in an enterprise-level project with the goal of building proper, streamlined Power BI datasets that will be durable for years to come.

Using the Migration Assistant to vet Cognos content, you can review the complexity, overlap, lineage and popularity of Cognos content to prioritize critical models and reports and minimize the overall project scope. Armed with that knowledge, you can then build clean and proper Power BI datasets.

Once validated, these datasets can then be certified for consumption throughout the organization. While a bit more involved and time consuming, this is the path to take if Power BI datasets will be the system of record for your enterprise data models for years to come.

Option 4: Tool-driven automatic migration of Cognos models and reports to Power BI

The fourth option is to leverage the Senturus Report InstaMigrator to “lift and shift” the structures of existing Cognos models and reports into Power BI.  This option helps to jumpstart your migration by automatically generating foundational structures in the Power BI file format and then storing them in a GitHub repository to facilitate project tracking, development/QA and deployment.

The integration of converted datasets and reports with GitHub creates a solid DevOps foundation for future report changes, additions and enhancements. When coupled with the Senturus Migration Assistant, the number of models and reports can be narrowed down to only what is currently being leveraged by your organization.

Senturus Upcoming Events

Cognos to Power BI: Common stumbling blocks

When faced with the introduction of Power BI into an established Cognos environment, organizations often wonder how best to facilitate a roll out. The solutions above offer different, effective ways to help get you there while ensuring reporting accuracy, security and business value.

Regardless which path you choose, introducing Power BI into your environment creates some common stumbling blocks:

  • Configuring Power BI service
  • Learning to administer the Power BI service (and capacities)
  • Educating end users to leverage the powerful capabilities of Power BI Desktop

Senturus can provide guidance to help you overcome these hurdles. We offer Power BI administration training and mentoring to help configure your environment, install gateways, apply security, decide on governance policies and drive engagement.

We can help you build the Power BI models and reports and provide end user training for Power BI Desktop to drive overall engagement. Get in touch with us if you have any questions.

Connect with Senturus

Sign up to be notified about our upcoming events

Back to top