By: Dallas Snider | Comments (6) | Related: More > Professional Development Career Planning
Problem
Whether you are an experienced professional or early in your information technology career, starting as a SQL Server business intelligence developer can be a daunting challenge. Besides the technical skills required to be a proficient SQL Server business intelligence developer, there are numerous "soft" skills that can contribute to your success.
Solution
Speaking from personal experience, I had many years of working with a variety of databases, programming languages and operating systems before having the opportunity to become a SQL Server business intelligence developer. Having to concurrently learn SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS) was somewhat overwhelming at times. In addition to learning how to use these new tools, I also had to learn about a new organization and its processes. In this tip, I would like to share what I believe is important when starting as a SQL Server business intelligence developer.
Technical Skills
First and foremost, a SQL Server business intelligence developer is creating software and should follow acceptable software development practices. These practices include gathering requirements, designing and creating the objects, writing any needed logic, testing, documenting, utilizing source code management and deployment procedures.
All three of the SQL Server business intelligence tools utilize the SQL Server database engine, so it is imperative that you have working knowledge of the database engine. This includes how to create and alter objects, how to write efficient queries, and how to use the built-in functions in T-SQL to manipulate data. Also, understanding how the SQL Server Agent works and how to create and schedule jobs is also beneficial. Development for all three tools occurs in the Visual Studio integrated development environment. Therefore, it is necessary to have a familiarity with Visual Studio's debugging and deployment functionality along with the directory structure and files of a business intelligence project.
SSIS is an extract, transform and load (ETL) tool which allows you to read data from a variety of sources, process the data using built-in transforms and write the data to various types of destinations. It is imperative that you become familiar with the built-in extract, transform and load functionality in SSIS so you don't have to reinvent the wheel.
SSAS provides online analytical processing (OLAP) and data mining. In my opinion, this is the most difficult of the business intelligence tools to learn. Attaining an understanding of dimensional modeling and the SSAS objects is crucial. Some of these objects include dimensions, cubes, key performance indicators and data mining structures. SSAS also has the multi-dimensional expression (MDX) language which allows you to query an SSAS cube.
SSRS provides the ability to create and deploy reports to a web server. T-SQL or MDX queries can be written to populate the report. An important skill to have with SSRS is the ability to design concise reports that convey the information in the data to the report user. Part of this design strategy is to effectively utilize the appropriate graphical elements without making the report too busy.
Performance
A very important skill to have is the ability to troubleshoot and correct performance issues. There are a multitude of ways to generate correct results in each of the SQL Server business intelligence components. However, a poorly designed T-SQL query, SSAS dimension, or SSIS data flow can create serious performance problems in terms of processing time, server resource utilization or network bottlenecks. Some typical questions are:
Information Assurance
Never before has there been a heightened awareness of data security. So you have to ask, "Are the business intelligence objects you create allowing for confidentiality, integrity and availability of the information?" You have to ensure that any files that are read or written by SSIS are properly secured by the operating system. All database sources and destinations need to be appropriately secured following your organization's policy. SSAS provides role objects that can be defined and deployed from Visual Studio to secure cubes, dimensions and other components. SSRS provides security for the report server's folders and reports.
Testing
As with any software development process, sufficient testing must occur. Not only do you need to test to make sure you are processing or displaying the data correctly, you need to test to make sure your objects are handling errors correctly. SSIS provides error handling for its data processing objects. SSAS also has error handling options when processing cubes and dimensions. Hopefully, you will have a testing team that can ensure that the processes and objects you have developed are working correctly.
Source Code Management
Granted the business intelligence objects that are created are not written in a language such as C# or Visual Basic, but each object can be treated as a source code file that can be checked in and out of source code management tools. Visual Studio's interface with Team Foundation Server can be utilized to assist with source code management.
Communication
Having excellent written and verbal communication skills are essential for being a successful business intelligence developer, this includes being a good listener. You need to comprehend the customer's requests so you can translate their needs into working objects. Also, providing your status to your stakeholders, responding to testers, explaining your methodology and assisting with documentation are all types of communication that are required.
Domain Knowledge
You just graduated with a computer science degree, what do you know about hospital administration, direct marketing, or selling paint? Your degree says you know about data structures, third normal form and object oriented programming, yet the first day on the job you are on a team where no one is talking your technical language - they are speaking the language of the business. This is where it is very important to grasp what the organization does, understand the problems that are currently being experienced and how business intelligence can help solve these problems.
Summary
In summary, having sound foundations in software development and database systems will be extremely helpful in becoming a successful SQL Server business intelligence developer.
Next Steps
Are there any skills that were not stated in this tip that you believe are important? Please comment below to share your thoughts with the community. Also, please check out our other Professional Development and Career Tips.
- SQL Server Career Planning
- How to Find a Good SQL Server Recruiter
- Starting your SQL Server Career Path
About the author
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
