Using SQL Server Analysis Services for poorly-performing BI Systems

Home > Blogs > SQL Server > Using SQL Server Analysis Services for poorly-performing BI Systems

Using SQL Server Analysis Services for poorly-performing BI Systems

Like This Blog 0 Don Jones
Added by August 2, 2013

No matter how well-designed and –build a business intelligence (BI) system is, it’s always possible that your workload will exceed its capabilities, and you’ll end up being dissatisfied with its performance. In those cases, what can you do? Your options depend a lot on the underlying database and analysis engine; for this article, we’ll assume that you’re using Microsoft SQL Server.

Hardware is obviously the first and most obvious thing you can do. Bigger, better hardware will almost always help achieve better performance, even in a somewhat poorly-designed system. Hardware is also one of the few things that you can change and upgrade without affecting any of the BI system’s software. After all, you’re not redesigning anything, you’re just migrating it to a bigger machine. More memory is often the first thing to try, since that helps improve SQL Server performance on a variety of levels. More and faster processors are a good option too, although that upgrade will generally entail moving to an entirely new server. More and faster disk storage can make a significant impact as well.

There’s also the option of examining how well the software is performing. Whether you’re using an off-the-shelf BI system or one that’s been custom-designed for your organization, you can generally examine the performance of that software without impacting it and without needing a great deal of special knowledge. For example, Microsoft has long offered a SQL Server Analysis Services Performance Guide for the various versions of Analysis Services. This guide focuses on examining the performance of queries within Analysis Services, and can help you identify poorly-performing queries. Even if all you’re able to do is take that information to your BI system vendor, it can lead directly to improvements. In some cases, you may be able to make some changes to SQL Server’s settings to help tune server resources and achieve better performance, all without changing the basic BI software.

Query performance analysis does require some specialized skills, although they’re often within the reach of any experienced IT professional who works with Microsoft products, especially if they have some basic SQL Server experience. There are also third-party vendors who make prepackaged Analysis Services performance “advisor” tools, which are much more automated and can be used by someone with less SQL Server experience. These tools can help identify problem queries, offer recommendations for changing SQL Server settings, and even create reports that you could deliver to your BI system vendor to get them to help you make performance improvements.

Dealing with performance problems in a BI system can be frustrating, especially in a prepackaged system that you’ve neither designed nor learned to deal with. Depending upon the responsiveness of your BI system vendor, performance problems may indicate that it’s time to bring some SQL Server skills into your organization, either by hiring someone or by developing a relationship with an independent subcontractor. And don’t forget that there’s always the hardware option: Moving to a bigger, better, faster server can often relieve performance problems simply by giving SQL Server more power to work with.

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 156 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 488 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 630 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.