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

Cable Testers and How to Use them in Network Environments

0 645 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Configuring Windows Mobility Center and How to Turn it On and Off

1 1402 1

Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 246 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts...

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