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 0Don 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.

You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

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.

You may also like:  Optional Parameters in SSRS

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

Videos You May Like

Agile Methodology in Project Management

0 30 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

Creating Users and Managing Passwords in Microsoft Office 365

0 41 1

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.

Detailed Forensic Investigation of Malware Infections – April 21, 2015

1 104 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

Share your thoughts...

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