Migration from OBIEE to Power BI with Report Server and SSAS

Nivea Benny

Over the past few months, I had the pleasure of working on an innovative case wherein we wanted to migrate our analytics reports from the Oracle Business Intelligence Suite, Enterprise Edition (OBIEE) platform to the Power BI model.

As the world is moving towards a data-driven culture, Power BI offers powerful analytics and rapid visualization features for organizations. With its powerful self-service abilities, business users are no longer dependent on IT service providers for extracting, transforming, and analyzing data.

The journey we took to migrate our existing OBIEE model to Power BI was challenging but at the same time exciting.

Following are some of the major challenges we faced during the migration:

Size limitations: OBIEE supports a robust model by using the concept of Raw Programming Data (RPD) wherein you can configure the entire database model in a single RPD file. In addition, it can handle a huge volume of data. In Power BI, there are certain limitations to the data it can handle. To handle this limitation, we decided to add the SQL Server Analysis Services (SSAS) tabular model between our database and Power BI. SSAS tabular models are in-memory databases that use the xVelocity analytics engine to deliver fast response time.
Compatibility issues: The database version we were using was not compatible with SSAS. Microsoft introduced SSAS tabular model with the release of SQL Server® 2012 Express. Before migrating to Power BI, we had to upgrade our SQL Server database. The upgradation of database included testing and migration of stored procedures, SSIS packages, and SSIS jobs.
Lack of expertise: In our team, no one had hands-on experience in Power BI or SSAS. We started attending online workshops and sessions to learn these tools. This migration project was a good learning experience for all of us.

Architecture for Power BI

In an enterprise environment, the volume of data is huge. Power BI (without Power BI Premium) only allows up to 1GB size of the model, which may not be enough for business users. Our client already had a data model and data warehouse in place with a huge volume of data. After taking into consideration the existing data model, volume of data, and customer requirements, we came up with following architectural model for our client.

An architectural model of Power BI for an enterprise environment.

A diagram elaborates the migration journey of an existing OBIEE model to Power BI. At first, the extract, transform, and load (ETL) tool is applied to the data warehouse to integrate and process data from disparate sources. Next, a blank project is created in SQL Server Data Tools (SSDT), and the data is imported from the data source to the SSAS tabular model to create a report on the Power BI desktop. Further, this report is published on the Power BI report server which provides visualization features and powerful analytics to an enterprise.

Data Warehouse

Data from various sources and stored in various databases cannot be used directly for visualization. Data warehouses are central repositories of consolidated data from multiple sources. To integrate and process the data from disparate sources, ETL (extract, load, transform) tools are used. In our project, we used the SSIS (SQL Server Integration Services) ETL tool.

Analysis Services

In the architecture for enterprises, SSAS plays an important role. By using state-of-the-art compression algorithms and multi-threaded query processors, the Analysis Services Vertipaq analytics engine delivers fast access to tabular model objects and data by reporting client applications like Power BI and Excel.

A typical analytical data model using the SSAS tabular model involves the following steps:
Creating a blank project in SQL Server Data Tools (SSDT), and importing data from data source to the SSAS tabular model
Filtering and renaming tables and columns
Creating the SSAS tabular model-specific elements like creating relationships, adding calculated columns, and creating measures and Key Performance Indicators (KPIs)
Creating roles and partitions
Deploying and processing database objects in the tabular data model
Connect to the tabular model by using a reporting client application

Power BI Report Server

Power BI Report Server is the on-premise solution for reporting today, with the flexibility to move to the cloud tomorrow. It is included with Power BI Premium, so you can move to the cloud on your terms. In an organization, Power BI report server can be used to:

Create interactive reports in Power BI desktop
Publish the reports directly to Power BI report server
View and interact in web browser and mobile devices

Power BI Reports

A Power BI report is a multi-perspective view into a dataset with visuals that represent different findings and insights from that dataset. A report can have a single visual or pages full of visuals.

The key benefits of Power BI are:

Power BI is cloud-based. It can be accessed through a web browser
Connects data easily
Connects multiple sources to create one report
Allows to drag and drop visuals
Provides Row-Level Security (RLS)

Summing Up

In this post, you will learn about implementing the Power BI architecture for an enterprise environment using components such as SSAS and Report Server. Report Server is an on-premises report server. You can create reports in Power BI Desktop or Pro, and viewers can use Report Server to access those reports on a web browser or mobile device, or receive them through email. With SSAS Tabular, there is no hard limit on the dataset size.

About the author

Nivea Benny

Nivea Benny is a Technical Lead in the Platform Solutions unit at TCS. With over 7 years in TCS, she has worked with national and international clients to provide solutions in Business Intelligence. She holds a bachelor’s degree in engineering from Calicut University, Kerala.

Contact

TCS is here to make a difference through technology.

We’re in it for good, driving positive change for the benefit of all.

Extraordinary expertise leads to remarkable results.

Want to be a global change-maker? Join our team.

Find the latest news about TCS in our Newsroom

Recent Press releases

Recent News

Recent recognitions

Upcoming events

TCS works hand in hand with world-leading investors.

TCS is here to make a difference through technology.

We’re in it for good, driving positive change for the benefit of all.

Extraordinary expertise leads to remarkable results.

Want to be a global change-maker? Join our team.

Find the latest news about TCS in our Newsroom

Recent Press releases

Recent News

Recent recognitions

Upcoming events

TCS works hand in hand with world-leading investors.

Transformation to Power BI

About the author

Nivea Benny

TCS is here to make a difference through technology.

We’re in it for good, driving positive change for the benefit of all.

Extraordinary expertise leads to remarkable results.

Want to be a global change-maker? Join our team.

Find the latest news about TCS in our Newsroom

Recent Press releases

Recent News

Recent recognitions

Upcoming events

TCS works hand in hand with world-leading investors.

About the author

Nivea Benny

Related reading

Accessibility Adjustments