Starting as a SQL Server Business Intelligence Developer

By:   |   Updated: 2014-10-10   |   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:

  • Is the sort in SSIS causing too much of a bottleneck?
  • How can I reduce the cardinality of an SSAS dimension's attribute?
  • Is the query for my SSRS report returning unnecessary data?
  • Am I sending too much data across the network?

    • 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 categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip



    About the author
    MSSQLTips author Dallas Snider 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


    Article Last Updated: 2014-10-10

    Comments For This Article




    Friday, March 9, 2018 - 2:52:48 PM - Joseph M. Morgan Back To Top (75383)

     

     I was please to see that you included the importance of domain knowledge in your tip. In these days of "hat-rack developers"--we wear so many hats we look like hat-racks--we often act as BSAs as well as developers. Knowing how to get to the questions your business customers really want answered is half the battle in designing a data mart and reports or dashboards that meet their needs. 

    Joey

    Friday, July 14, 2017 - 7:20:25 PM - Ayree Back To Top (59366)

     WOW, thank you vety much for sharing this infirmation. It has really answered my questions i had. 

    Thank you again

     

    Saturday, October 11, 2014 - 2:54:35 PM - Shiv Prasad Back To Top (34935)

    Dear Sir,

    First of all I would like to congratulate you sharing your knowledge & experience. This really help me and found very much helpful. If possible, please do share carrier related tips on below areas as well.

    1. DB Architect

    2. Data modeler

    3. Big Data

    4. SQL DBA

     

    Regards,

    Shiv

     

    Saturday, October 11, 2014 - 1:55:59 PM - Sam David Back To Top (34934)

    Hello Dr.Dallas Snider,

     

    This article came just-in-time for much needed help. It is very insightful and true to the real life storyline.

    At present, I am struggling to learn T-SQL and especially Stored Procedures since there are no good books available!

    I will definitely follow your guidelines and tips however please do let me know if there exist any institute/college/university out there that teach/offer MS Business Intelligence Developer certificate program.

    I want to get started learning Stored Procedures but where do I begin?

    Thanks. Have a nice day.

     

    Regards,

    SD

     

     

    Friday, October 10, 2014 - 8:10:58 PM - Jim Back To Top (34929)

    So true, and around here (Miami, FL) they think all of this is worth only $ 50-55K year. If the budget is unrealistic, well best of luck getting it done and retaining anyone to maintain it ?

    Friday, October 10, 2014 - 2:29:49 AM - Rene Voller Back To Top (34916)

    Dear Dr Dallas Snider,

    I really enjoyed this article, especially the tips on technical skills and testing. 

     

    My experience lies very heavily with SSRS and taking the leap to SSIS and SSAS is quite daunting.  But it's one I am willing to take. BI is the future for many organisations and the sooner we can provide BI solutions the better.

     

    Kind regards

     

    Rene Voller

    UMGENI WATER

    SOUTH AFRICA

     

     














    get free sql tips
    agree to terms