• Resources
  • Jobs
  • Blog
  • Contact
  • 415.538.8484

DesignMind

Big Data, Business Intelligence, and Data Analytics Consultants

 
  • Company
    • Meet the DesignMind Team
    • Partners
    • Subscribe
    • Resources
    • Datasheets
    • White Papers
  • Services
    • Big Data Consulting
    • Business Intelligence & Data Warehousing
    • Cloudera Integration
    • Data Science
    • SQL Server and Database Services
    • Software Development
    • Technical Staffing
  • Videos
  • Meetups
  • Clients
March 16, 2016Angel Abundez

Analysis Services Tabular Process Optimization

How to use Analysis Services Tabular to get near real-time updates for your Power BI dashboards

Analysis Services provides three different approaches for creating a business intelligence semantic model: tabular, multidimensional, and Power Pivot. Analysis Services Tabular is a great self-service backend for use with the Power BI service, however keeping it updated and refreshed for the enterprise can be confusing. There are many processing options at the database, table, and partition level to help you speed up processing time, but they can be hard to navigate.

A good place to start is a Microsoft whitepaper, PerfAnalysis Services Tabular Cubeormance Tuning Tabular models in SSAS 2012.  This document explains processing options in a lot more detail. In a recent business intelligence engagement, we looked at this whitepaper and other sources in order to optimize our client’s experience with Power BI.  Our goal was to achieve the best performance possible, and along the way we learned a few things we want to share.

Parallel Processing

A key to our goal was realizing that sending the “Process Full” command to our Tabular database processed groups of tables in parallel. After extensive checks in SQL Profiler, we realized the engine figures out which tables to run in parallel. It’s very quick on small tables and larger ones take longer as the engine imports and compresses that data in-memory. After a while, we realized we could do something about the smaller tables in our project.

On the same engagement, we used Master Data Services (MDS) for data stewards to maintain small entities such as KPI targets. We’d bring in these entities to display thresholds on multiple dashboard tiles dynamically (as illustrated below). We wrote “Process Full” commands against these small tables in a separate XMLA script and marked the batch with Transaction=false and witnessed these tables getting refreshed in less than a few seconds! We ran our XMLA command in a SQL job every few minutes and it gave users the experience of making real-time updates to their Power BI dashboard tiles.

Master Data Services

We also found some schemas that did not require a refresh most of the day. For example, we had a historical inventory fact table that held daily inventory snapshots. Once the cube processed the most recent daily snapshot, there was no need to process the table at all for the rest of the day. So we grouped all the other schemas in a separate XMLA command, marked it Transaction=false, made sure we checked that the fact table was caught up, and then let the SSAS hardware and software do the rest of the heavy lifting. This saved a ton of time in cube processing and allowed us to establish a more frequent data refresh schedule.

Incremental Processing

Snapshot fact tables like the Inventory one just mentioned seemed like a great use case for the “Process Add” command, but we learned a couple things implementing this command. First, this command will process new rows in your model by changing the table’s partition query to filter new data rows, or “delta rows”, since Analysis Services does not auto-detect incremental data. If you have a sizable snapshot fact table like we did, this can save you a ton of processing time, but you have to be careful.

The textbook description of “Process Add” says you’ll change the partition query of your table to process new data. What most books won’t tell you is that you could (and should) control when and how to change the partition query of a table. A couple key points to take away from our findings:

  1. Once you’ve successfully run Process Add, change the partition query back by removing the WHERE clause effectively removing the filter for delta rows.
  2. Check whether a Process Add is needed by comparing the last snapshot date in your fact table with the last snapshot date in your tabular model.

The first point will save you a headache in case someone tries to reprocess the table or partition ad hoc after running “Process Add”. The second is so you don’t accidentally process the same delta rows multiple times.

Conclusion

The good news is that Analysis Services Tabular yields high performance analytical databases that integrate easily with Excel, Reporting Services reports, and other BI applications from Microsoft and third-party applications. We hope these tips will save you processing and development time. It’s important for end users of Power BI to get their data refreshed as frequently as possible. With Power BI’s Enterprise Gateways and their DirectQuery capability, it becomes a game of how to optimally process your cubes.

Pravina Parthasarthy is a Business Intelligence engineer at DesignMind.  She specializes in SQL Server Analysis Services Tables and Power BI.

Recent Posts

Advanced Bookmarks and Buttons in Power BI

January 17, 2021

How To Export Underlying Data in Power BI

December 15, 2020

Right Sizing Your Power BI Gateway on AWS

May 26, 2020

Creating Power BI Custom Visuals

April 21, 2020

My Power BI report is too slow. What tools and techniques should I use?

March 2, 2020

Are Your Power BI Performance Issues Due To High Memory Consumption?

January 17, 2020

Power BI Parameters – How to Use & Update Parameters in the Power BI Service

December 9, 2019

Power BI Tips: How To Increase The Number of Rows Exported to Excel

September 6, 2019

Power BI Data Modeling Tips for Developers and Analysts

October 15, 2018

Company

  • About
  • Team
  • Clients
  • Partners
  • Privacy and Cookie Policy

Resources

  • Blog
  • Resources
  • White Papers
  • Datasheets

Community

  • Microsoft Technical Groups
  • Bay Area BI User Group
  • San Francisco Data Platform
  • Silicon Valley Data Platform
  • San Francisco Power BI
  • Twitter
  • LinkedIn
  • Subscribe
  • Contact

© 2021 DesignMind. All rights reserved.