Xantrion Future-Proofs Their Data Model and Increases Dashboard Performance by over 70%
MSP uses Smoother Consulting to optimize reporting and drive best practices for internal users.
Xantrion is a Managed Service Provider (MSP) with locations in Boston, New York, Atlanta, Grand Rapids, and multiple locations in California. They focus on providing best in class service to clients for their cybersecurity and technology needs by employing many talented individuals with an array of technical skills. Those skills are used to swiftly solve customer issues, and sometimes overlap internally, giving the Xantrion an edge by always improving different aspects of their business.
Xantrion has been using Power BI to analyze their support ticket call logs and discover anomalies that potentially cause bottlenecks in service. BI has allowed the MSP to find trends and continually refine their services to meet the evolving needs of their clients.
Snapshot
Industry: Cybersecurity & IT Support
Company Size: 80-100 people
Volume: 3,200+ tickets/month
Support: 8,000 devices; 4,500+ users
Year Founded: 2000
The Challenge:
A BI tool that provides reliable data to make informed decisions, ultimately allowing the business to focus on providing the best service in a cyber-threat landscape that is always changing.
As Richard Ericksen, Vice President of Client Services, and his team were building reports on an existing Power BI dataset, they realized the reports had become difficult to manage and understand. Rich noticed duplicated fields in the data, performance was deteriorating over time with increasing amounts of data, and he started questioning the integrity of the reports. Previously the data model was overwhelming, making it difficult for Xantrion to make business-related decisions. Without access to certain information, they were not able to make data-driven decisions. A tool that provided such valuable information was now in need of a complete check-up to ensure its continued dependability.
Xantrion had over 20 SQL queries and more than 30 million rows of data analyzing call logs and support ticket information. They needed an expert to review the Power BI data model, identify areas of improvement, and implement changes to make the reports perform more efficiently. The goal of this project was to simplify and improve Xantrion’s data model. This would then allow them to have access to certain data when they needed it without having to navigate through clutter. As the dataset continued to grow, Xantrion wanted to make sure their reports were dependable and built on a reliable foundation.
“We took a screenshot of [the existing Power BI dataset] and it was like a bomb had gone off...”
The Solution:
A backwards approach to bring clarity to reporting metrics and untangling a web of data model components.
Smoother Consulting partnered with Xantrion to help rebuild confidence in its internal BI reports. A series of optimization tools and some old-fashioned data modeling principles came into play. By collaborating, they delved into the significance of various reports and metrics, and determined the insights these reports should convey. Smoother Consulting then proceeded to map out an optimal approach for extracting data from Xantrion’s SQL database, using a star-schema methodology, developed a data modeling roadmap based on the data lineage activities.
To diagnose and optimize the old report, several tools were employed, including Best Practice Analyzer, Performance Analyzer from Tabular Editor, as well as DAX Studio’s DMV functions. These tools enable data modelers to establish benchmarks and pinpoint areas that affect the overall report performance. By analyzing the results, a data modeler gains valuable insights into potential culprits causing performance bottlenecks, which an experienced modeler can leverage to design a model that eliminates these bottlenecks
“Figuring out [the issues], coming back to us with the results, and integrating them into our systems - That is the big value.”
70%
Faster rendering of data visualizations
90%
File size reduction
98%
Cardinality reduction
Data Model Design Changes:
Replace single-use tables with efficient Data Analysis Expressions (DAX) measures
Dedicated calendar table
Implement a star schema architecture
Optimize measures written in Data Analysis Expressions (DAX)
Push Data Analysis Expressions (DAX) columns upstream with SQL statements in Power Query
Looking Ahead:
A single successful project leads to a partnership allowing Xantrion to rely on us instead of having to figure issues out themselves.
After revamping Xantrion’s formerly overwhelming data model, we were able to simplify that model which allows Xantrion to obtain the data they need. Most importantly, with access to the data that is needed, they are able to make data-driven decisions for their business, which was not always possible before. With Xantrion, we were able to analyze the issue and then take the steps needed to solve it and resulted in a simplified model to which they are able to understand. According to Richard Ericksen before it was “dense and complicated” and after our services the model is “simplified and we don’t feel overwhelmed.”
Through the work we have done with Xantrion, we are now a trusted go-to expert when they face any unique issues. They no longer have to try to solve these issues on their own, they now have an expert in their back pocket to refer to when needed. The partnership between Xantrion and Smoother Consulting has successfully addressed the challenges Xantrion faced with their Power BI reports, resulting in an agile data model and reliable analytics. The optimization efforts have led to improved rendering performance, reduced file size, and enhanced data integrity. Xantrion can now leverage these improvements to make data-driven decisions, meet evolving client needs, and excel in the ever-changing cyber-threat landscape.
“The thing that brings me the biggest piece of mind is that if something unique comes up and we need to tackle it, we know that we can reach out to you and solve that together.”
