In the beginning, there was SQL Server. And it was good!

Then, things got a bit more complicated.

As the market for data warehousing and business intelligence grew, Microsoft’s BI platform grew as well. Since Microsoft’s recent release of the Power BI toolkit for Excel, I’ve gotten questions around where each of Microsoft’s BI pieces fits into the enterprise puzzle.

Below, I outline SQL server and its services, and how each fits into the business intelligence field. Next week, I’ll take on the Power BI toolkit.

SQL Server: the foundation of Microsoft’s BI initiatives.
With its latest 2014 release, SQL incorporates new technology such as in-memory tables to help organizations deal with the mountains of data available in today’s enterprise. It has grown and changed a lot over the years, but at its core, it is still a reliable workhorse and forms the foundation of Microsoft’s BI platform.

SQL Server Integration Services (SSIS): Microsoft’s ETL toolkit.
A data warehouse isn’t terribly useful without any data, and the SSIS packages handle the task of getting data into and out of almost any data store in your organization: from flat files to SQL databases to more exotic sources such as webservices and legacy midframes. It’s not the most glamorous member of the family, but it’s essential for moving data from point A to B.

SQL Server Analysis Services (SSAS): a semantic data model on top of your SQL data.
If the above description sounds like Greek, think about it this way: SSAS provides a mechanism for capturing the relationships between tables, putting data into a standardized format that can be used by less technical team members safely, without the risk of delivering incorrect results. SSAS comes in two flavors: multi-dimensional and tabular. The multi-dimensional model provides a robust data modeling environment but can be complex to implement. The new tabular model provides a simpler development process but does not include all of the advanced functionality that the multi-dimensional model offers.

SQL Server Reporting Services (SSRS): your way to present data.
SSRS enables you to deliver your carefully collected and modeled data to business teams in reports and dashboards. These can be as simple as a chart emailed out weekly, or as complex as a real-time dashboard embedded in a SharePoint site. SSRS leverages the underlying SQL and SSAS layers to enable analysts to write reports without having to become technical experts.

With so many products to choose from, it’s essential to start from a solid grasp of your team’s needs and then work to select the software that meets them.

And there is even more to choose from. Check back in for next week’s post, where I walk through Excel and the “Power” family of BI tools for Excel.