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 171 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

An Overview of Office 365 – Administration Portal and Admin Center

0 913 3

This is part 1 of our 5-part Office 365 free training course. In this Office 365 training video, instructor Spike Xavier introduces some of the most popular services found in Microsoft Office 365 including the Admin Portal and Admin Center. For instructor-led Office 365 training classes, see our course schedule: Spike Xavier SharePoint Instructor – … Continue reading An Overview of Office 365 – Administration Portal and Admin Center

Windows 10 Features and Navigation – December 1, 2015

0 128 1

In this recorded Windows 10 webinar from December 1,2015, Windows Instructor Steve Fullmer presents the navigation and some of the new features associated with Windows 10 including Sysinternals Tools for Windows Client, Windows core concepts, exploring Process Explorer as well as some of the features that are not yet ready for prime time but will … Continue reading Windows 10 Features and Navigation – December 1, 2015

Write a Comment

Share your thoughts...

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