How to Learn SQL Server BI

Professional Development

One of the best parts of being involved with SQL Server is the wealth of information that is shared by professionals in the industry. This isn’t a skillset that has an impossible Catch-22 attached. In other words, you don’t have to get a job using SQL Server before you can learn SQL Server!

There are a variety of ways to learn SQL Server and MS BI ranging from free as in beer to a week long cruise to immersion events into the inner workings of the technologies. In this article I’m going to detail how I’ve been expanding my knowledge over the past couple years and, of course, learning on a continual basis.

Books

There’s just something about having a physical copy of a text that helps you learn and feel like you’re really committed. Luckily there’s no shortage of excellent literature about SQL Server, Business Intelligence, and Data Warehousing. But how can we cull the good from the not-so-good and decide how to spend our hard earned cash?

Just do it.

It’s no secret that I’m a big advocate of Amazon. My typical shopping process involves searching Amazon’s vast listings for a specific topic, sorting by best-selling and relevant, and then choosing the book(s) with the highest average score while applying a minimum to the number of reviews submitted. This process hasn’t failed me in finding some gems in the industry.

Another avenue to finding great books is through recommendations by bloggers. Some professionals write reviews on their blogs and offer their insight on the book’s merits. That said, Amazon also has many devoted reviewers who spend substantial time writing quality reviews. On the other hand, you likely know the qualifications of the blogger so it’s still useful to get recommendations from them.

Here are some of the books that I’ve purchased on Amazon. Note that I haven’t read through all of them; in fact, they’re all sitting on my desk slowly being picked apart!

Databases and Data Warehousing

The Data Warehouse ETL Toolkit: Practical Techniques
4.9 out of 5 stars (14 customer reviews)
Takes a deep dive into DW ETL issues such as staging, delivering dimensions and fact tables, metadata, and even real-time ETL systems.

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
4.4 out of 5 stars (37 customer reviews)
Demonstrates concepts of dimensional modeling in a relevant manner by introducing concepts in different environments in each chapter (e.g. retail, health insurance, manufacturing)

Databases Demystified (Demystified)
4.8 out of 5 stars (24 customer reviews)
Highly recommended even if you’re not new to database development. I still refer to it in order to make sure I’m following third-normal form when I design OLTP database projects.

Microsoft Business Intelligence

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)
4.4 out of 5 stars (11 customer reviews)
I’m currently studying for the 70-448 Microsoft certification using this book and so far, so good.

Programming in General

Pro C# 2010 and the .NET 4 Platform
4.5 out of 5 stars (38 customer reviews)
If you want to learn C# and .NET 4, this is a highly rated book. I’ve read through the first third or so.

Beginning ASP.NET 4: in C# and VB (Wrox Programmer to Programmer)
4.8 out of 5 stars (27 customer reviews)
Likewise, if you want to add ASP.NET to your knowledge base, this is a good read.

Code Complete: A Practical Handbook of Software Construction
4.7 out of 5 stars (141 customer reviews)
More for pure programmers, this book has many best practices for the art and science of software engineering.

Essential C++
4.1 out of 5 stars (30 customer reviews)
A straightforward book that will teach you both the basics and the intricacies of C++ programming and development.

There are many, many other great books for SQL Server and programming. Search Amazon for “SQL Server 2008” and you’ll get a long list of well-reviewed literature, and “programming” as a topic has a metric ton of great reads.

Online Video Training

There are times when learning through listening and watching can be more beneficial than reading a book. Luckily there are many resources online for SQL Server and BI training ranging from free community resources to subscription training services.

Tons of video content

One service I recommend is the library of Pluralsight training videos. They not only cover SQL Server and BI technologies but also dive into a wide array of programming disciplines including .NET, ASP, C#, iOS, and Android. It’s easy to get distracted by all of the knowledge available; I sometimes have to remind myself I’m there to learn about SQL Server! A limited trial is available and students have the option of signing up for three free months. I recommend paying for a month or two and learning as much as you can. Even better, see if your boss will cover the cost. It’s much cheaper than bringing in trainers!

Training videos can also get in-depth and quite technical. For example, check out Brent Ozar’s contributions to SQL Server tuning, DBA tasks, and Microsoft Certified Master (MCM) preparation videos.

Blogs

SQL Server DBA Blog

Professionals around the world are getting into the habit of blogging about their experiences and knowledge. While readers can pick up some useful technical tidbits by regularly reading blogs, an added benefits is that blogs sometimes exhibit the human side of the SQL Server industry. Motivational pieces, “soft skills” techniques, and opinions about one technology versus another are just some of the possible topics that bloggers cover.

Here are some blogs to get you started:

Stacia Misner, an expert in MS BI development and training, Founder of Data Inspirations
Paul Randal, SQL Server expert, co-founder SQLSkills
Kimberly L. Tripp, SQL Server expert, co-founder SQLSkills
Brent Ozar, SQL Server expert, MCM, co-founder Brent Ozar PLF
Vincent Rainardi, SQL Server BI Consultant

Community Websites, Article Series, and Twitter

Twitter Basics

As I’ve stated before, the community around SQL Server is part of what makes the industry special. One community-based website that is particularly helpful is StackOverflow. Users post questions and – get this – users post answers. The beauty is in the sharing of information, the comments system, and badges you can earn by helping others, among other fun.

SQLServerCentral is a community of over a million SQL Server users, developers, and administrators. Articles, editorials, and forum activity are regularly updated.

Lasty, the SQL community is very active on Twitter. For a head start on the SQL community on Twitter, check out Kevin Kline’s guide to SQL Server on Twitter, and Brent Ozar’s ebook on how to get Twitter-savvy in no time.

Local SQL Server User Group Community

SQL Pass

Last but certainly not least, local users groups are a core part of the SQL Server community experience. The Professional Association for SQL Server (PASS) sponsors local user groups across the nation and these groups have regular meetings to learn and network.

Another community phenomenon to watch is free training seminars like SQLSaturday. Events like these have incredible amounts of free training, networking opportunities, and overall bang-for-your-$0-buck.

Keep on Learning!

The opportunities for learning about SQL and networking with professionals seems to be endless nowadays. The key is to search out these resources and continually strive to improve your skills and knowledge.

SQL Server, Meet Business Intelligence

Technical

IT professionals, data analysts, and anyone whose work involves making sense of data rely on tools to get things done. At one end are files in a folder on your Windows desktop with Excel spreadsheets and Word documents. On the other end are complex data models forecasting the future being fed by terabytes of data loaded in real-time by multi-million dollar investments in infrastructure. Your job is to figure out how best to get the job done.

There are many options for tools in the database and business intelligence industry. To name a few, there’s Microsoft SQL Server/BIDS, Oracle/BI, IBM/Cognos, Teradata, and Sybase/IQ. The choice of which to choose, for the IT and data professional, comes down to experience and market demand. I’ve chosen to learn as much as I can about Microsoft’s offerings because of my five years of experience with the suite, the active community surrounding the tools, and the potential to get more work in the field using these skills.

Why SQL Server?

Microsoft SQL Server is undoubtedly one of the major players in the database industry. It has steadily improved since its Sybase roots in the early 90s and has attracted an involved community of users, developers, and administrators. Bundled with many packages of SQL Server is the Microsoft Business Intelligence Developer Studio, abbreviated as BIDS, which serves numerous important functions in managing, processing, analyzing, and reporting on data stored both in SQL Server databases and elsewhere. BIDS offers great value for business and provides a vast opportunity for professionals to develop important skills.

The Components of BIDS

BIDS is comprised of three major types of projects – Integration Services (SSIS), Reporting Services (SSRS), and Analysis Services (SSAS).

Integration Services

SSIS is a set of tools that allows developers to move and process data in an intelligent and timely manner. The basic idea is to take data from one location and transfer it to another while making necessary transformations to the data in between. This process of extracting from a source, transforming in between, and loading the data to a destination is abbreviated as an often used term called ETL. One or more ETL procedures that can grouped together into a package and these packages can then be run to perform the ETLs’ functions.

Furthermore, database administrators can use SQL Server’s built-in scheduling tool, called SQL Agent, to schedule SSIS packages to run at certain times of the day. This allows for scheduling of a batch of ETL packages during off-business hours, such as in the middle of the night.

Reporting Services

SSRS is a set of tools to develop and view reports based off data sources. Because reports, whether tabular or graphical, are so important to decision makers in businesses, developing reports and supplying them in an accurate and timely manner is a cornerstone of business intelligence. SSRS provides an interface to pull data from data sources into what are called datasets. Reports are then formatted based on one or more datasets. Reporting Services also provides a website-based viewing functionality where reports can be viewed based on a user’s reporting access permissions.

It should be noted that while the SSRS report viewing functionality is very useful, many business opt for other user portals such as custom built solutions or Microsoft’s SharePoint suite.

Analysis Services

SSAS provides functionality which I like to group into two sections – multidimensional modeling and analysis, and data mining. Multidimensional modeling boils down to the development of measures and dimensions organized into what is called a cube. If you consider a database table to be a square, with its two dimensions row and column and its value per row & column intersection, then a multidimensional cube is a table with more than two dimensions. For example, sales for a particular product can be seen in a particular month, in a particular state, split out by all stores in that state. It’s this “slice and dice” functionality that makes SSAS appealing to many users.

Users can access these cubes via interfaces such as Excel, report writers can use the MDX query language to pull data into datasets, and statisticians can use them to create data mining models. The data mining functionality allows for numerous models to be performed on a data set, such as correlation. This functionality is one that I am still studying and will be certain to explain once I learn more.

The Whole is Greater than the Sum of Its Parts

Learning and using SQL Server can be very useful for your company and for your professional development. The addition of SSIS, SSRS, and SSAS to your environment can greatly improve your company’s ROI on technology, your daily productivity, and your value in the industry marketplace. It’s worth your while to check out these tools and get a feel for their capabilities.

How to get Started

In the next article in the Technical category of posts I’ll explain how to get evaluation copies of SQL Server and BIDS, as well as a development version, and how to put together a virtual machine to run SQL Server 2008 R2 evaluation edition and SQL Server 2008 R2.

Introduction to EmreMotan.com

Uncategorized

Hi, my name is Emre Motan, and I want to help you learn about Microsoft BI and Data Warehousing. Not just about all the fascinating technical aspects, mind you, but also about professional development and the community involved in this niche of the IT industry.

One More Blog to Add to the Millions?

With more than 166 million blogs why is there a need for one more? I believe every blog offers a unique experience particularly if it’s focused in its content and delivery. To that end, here is an outline of this blog’s direction:

Technical Content:

  • SQL Server Integration Services (SSIS)
  • SQL Server Reporting Services (SSRS)
  • SQL Server Analysis Services (SSAS)
  • Data Warehousing using SQL Server
  • SQL Server database engine
Professional Career Development
  • Types of positions using these technologies
  • Educational opportunities related to these technologies
  • Professional community involvement and events
  • Interviews about companies
  • Interviews about professionals

But Why do You Want to Start a Blog?

It’s quite simple, really: I’ve had so many “ah-hah” moments in the past couple years that I feel compelled to share them. Since I started learning as much as I could, as fast I could, in earnest about a year and a half ago, I’ve come a long way with the help of books and the SQL community. Previously, I designed systems to automate and better the work I was doing in ways that I knew would help my team but I had no idea how to implement my designs. There were so many things I didn’t know then that I know now and I want to help people get to those “ah-hah” moments sooner.

It’s All About the Community

Social media ushered in an unprecedented quantity, and quality, of interaction between professionals. Among the numerous bloggers in the SQL Server industry you’ll find experts in the database engine, SSIS, SSRS, T-SQL, professional development, and many other related topics. On Twitter you’ll find a vibrant community around SQL PASS (#SQLPass) and between professionals around the world. One of the best parts of a blog is the interaction between readers and authors, and between readers themselves. I invite comments to my posts and for readers to reach out to me directly. Enjoy!