Transformation to Power BI

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.

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.

1. A typical analytical data model using the SSAS tabular model involves the following steps:
2. Creating a blank project in SQL Server Data Tools (SSDT), and importing data from data source to the SSAS tabular model
3. Filtering and renaming tables and columns
4. Creating the SSAS tabular model-specific elements like creating relationships, adding calculated columns, and creating measures and Key Performance Indicators (KPIs)
5. Creating roles and partitions
6. Deploying and processing database objects in the tabular data model
7. 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:

1. Power BI is cloud-based. It can be accessed through a web browser
2. Connects data easily
3. Connects multiple sources to create one report
4. Allows to drag and drop visuals
5. 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.