Learn how we helped a leading pharmaceutical company fix and optimize their Power BI- and SSAS-based sales analytics platform for performance, report distribution, user authentication, and new insights!
The client is a global leading provider of pharmaceutical solutions for cancer, cardiovascular ailments, gastrointestinal issues, infections, neurological issues, respiratory problems, and inflammation.
The Power BI- and SSAS-based sales analytics platform provides customer, provider, and market intelligence around sales of the firm’s drugs and competitor products, including generics.
Third I experts were tasked with:
- Designing and developing a set of new Power BI reports and dashboards with actionable insights on sales, uptake share, and erosion statistics in the market, and publishing them on the web and as apps using Power BI Services. The audience for these reports were to be the C-suite, R&D, marketing, and sales.
- Tackling numerous performance and operational problems with the existing deployment including slow performance, complex slicing and filtering, report distribution issues, and security concerns.
Third I consultants deployed these reports and dashboards globally, improved overall platform performance by approximately 30%, and improved report load time from ~ 4 minutes to 10-12 seconds. Read on for a deep dive.
Problem #1: Slow Performance due to Nested Iterators in DAX
In the client’s existing environment, about 45 GB of data was sourced from a pharmaceutical ERP application, R-based projection system, biostatistics, and medical reporting services.
On top of this sourced data, there were numerous statistical computations done using DAX (some of them were nested calculations) in Power BI. On load and during slice/dice operations, report load times were longer because of these computations.
In order to improve the report performance, we performed a thorough assessment of their existing 50 DAX-based calculations.
- To trace DAX query performance, we used DAX studio to analyze the DAX which results in high CPU performance.
- We noticed that 20 DAX of nested iterations were used in the report that led to its slow performance.
- Below are a few DAX optimizations we tried that resulted in overall performance improvement by 30%.
- We added variables in DAX wherever possible.
- We replaced SUMX function with a SUMMARIZED function to improve report load time.
There were still 10 nested iterated DAX which resulted in slow performance. To resolve this, we then moved data exported from R systems & ERP to SSAS Tabular Cube using olapR API and integrated with Power BI with row-level security.
Our approach improved the report’s near-real-time data loading performance. With this, the reports that earlier took about ~ 4 minutes to load, now took only 10-12 seconds.
Problem #2: Applying Complex Slicing and Filtering for Dynamic Axis
A key requirement to show data by using a slicer and making the X-axis dynamic was a major technical roadblock.
In the data model, the data was present for all countries in the form of “Product Indicator Country (PIC)” combination. This was further categorized into “Country Level”, “Global Level”, and “EU-5.”
- If the “Country Level” option is selected then the client wants to display the data of all countries based on their PIC combination on the x-axis
- If they select “Global level” option then it should display the aggregate of each product on the x-axis for all the countries
- And if “EU-5” option is selected then the aggregated of product on the x-axis for 5 European countries should be displayed
To achieve this, we created a DAX table where we blended the PIC data (Country) & Product data (Global & EU-5) into a single column to be able to use it in the x-axis.
- Wrote 3 different DAX Measures for respective filters
- Created a table using DAX expression to show the required data on the x-axis
- Used "UNION" & "SELECTCOLUMNS" functions to bring the data into two columns.
- The first column provided data of dynamic x-axis and the second column gave the DAX measure output.
- In visualization, from the table created in step-2, dragged the first column on the x-axis field and the second column on the value field.
Using this table, we changed the data as per slicer selection along with x-axis dynamicity.
The users could now view the data based on filters (Country Level, EU 5 and Global Level) with faster load times leading to higher adoption.
Problem #3: Global Distribution of Reports using Power BI Apps
The client needed multiple Power BI reports for its users to be accessible from various locations. We created these reports and published them as apps to specific users for the client. This way we could manage permissions on these reports. Our quick and easy deployment of such reports to their users empowered the client to make timely data-driven decisions.
- Created the reports in the Power BI workspace.
- Then selected the reports that were required to be published.
- These reports were then published as Apps.
With Power BI, we could do real-time development, testing, and deployment while the end user could access the latest report on the Power BI App.
With this, the client could send a direct link to their users or the users could access the reports by directly downloading them from the published apps.
Problem #4: User Authentication & Security
There were 150+ users from various departments across the globe that were using these reports for their respective region/country. We authorized these users to access the reports through a secure sign-on authenticated by Microsoft Azure Active Directory to easily analyze data and drill down to get deep insights about their operations and create their own visual reports.
- We set up a Model View Controller (MVC) web application environment that supported Azure Active Directory Authentication.
- Created a Web app type application in Azure Active Directory and provided the Sign-On URL of the MVC application.
- Added the Azure Application ID, and Tenant ID in MVC application.
With these steps, we added the Azure Active Directory Authentication to the application and authorized users to access different reports.
This allowed fine-grained security controls on the reports/dashboards based on automated integration with Azure Active Directory.