Monday, July 13, 2020

QlikView to Power BI Migration




The steps we followed to carry out our migration strategy are detailed in this blog.

1. Requirements Gathering and Analysis

We needed to understand our client’s extant state of affairs. During the Requirement Gathering and Analysis Phase, we evaluated the client’s existing reporting platform. We examined the QlikView reports, usage, UI/UX, audience, data, and security implementation.

Through this we defined the scope and acceptance criteria, and created a report and data estate inventory. Altogether we had 16 reports to migrate. The data source consisted of 20+ SQL databases and 30 Excel sources.

2. Planning and Design

We performed a detailed gap analysis. This identified the different features, and visualization and modelling variations in Power BI as compared to QlikView. We highlighted alternatives to the challenges identified, including:

1.    QlikView provides ‘Clear Selections’ feature that clears all the filters on a report. We used the ‘Reset to Default’ option to reset all filters to default.
2.    QlikView supports dynamic variables in a report that can be expressed in visuals. We used ‘What-If’ parameters in Power BI that serve the same purpose
3.    QlikView has a feature that shows all the filters applied in a section. We used the new filter experience in Power BI that shows all the filters applied on a visual.

Through this, we were able to identify a template and prepare a mock-up of reports for review. We separated the datasets based on the reports’ refresh frequency (daily/hourly). We broadly classified the reports as “Financial” and “Operational.” We then sub-categorized them based on their complexity: “simple,” “medium,” and “complex.” We proposed a solution architecture and an execution timeline to the client.
Figure 1: Solution Architecture
Figure 2: Execution Timeline

3. Execution

We performed a detailed gap analysis. This identified the different features, and visualization and modelling variations in Power BI as compared to QlikView. We highlighted alternatives to the challenges identified, including:

1.    Sprint Plan – We set up weekly calls with the client, created a product backlog, and defined the scope and size of the sprint.
2.   Implementation – Using best practices, the proposed template, and theme, we began the report migration, providing incremental builds. We combined some of the simple reports with the same data source to form common data models.
3.    Performance Tuning – We combined the smaller datasets and optimized the data models to reduce data latency.
4.    Test – We used a set of in-house tools to perform automated testing that tracked query performance, and provided insights on visual layout and data validation.
5.    Deployment – We marked the closure of our sprints by deploying the reports and readying the build for UAT.

4. Deployment and Post-Production

This stage helped us ensure the end user was satisfied and comfortable with the new system. During the Deployment and Post-Production phase, we:

1.    Conducted user acceptance testing (UAT) through numerous user acceptance sessions.
2.    Got the sign off for production and UAT.
3.    Conducted a premium capacity analysis to establish a suitable refresh schedule for dataflows.

To complete the transfer of ownership, we handed off the code, reports, and workspace inventory to the client. We also proposed our decommissioning strategy for the older reports.

5. Decommissioning

In the Decommissioning Phase, we got the sign-off from stakeholders to systematically retire the old QlikView reports. We updated the end URL of the reports. We also tracked the usage of existing QlikView reports until we eventually brought those down to nil.

Benefits

  Standardized reporting mechanism across the business divisions. 
  Reduced redundant reporting through report consolidation. 
  Enhanced security through role-based access control using RLS. 
  Cost reduction by enabling EF to retire QlikView and the maintenance of two BI tools.