The Dynamics of the Microsoft SQL Server Business Intelligence Tools
1. SQL Server 2000
a. SSAS (Data warehousing, OLAP, and data mining)
Including the components of Analysis Server, Analysis Manager, Decision Support Objects, Client Components, and the Sample Applications. Selectable in SQL Server 2000 installation
b. DTS
DTS is a node of the SQL Server Enterprise Manager in SQL Server 2000. Part of SQL Server installation.
c. Reporting Services
It was first released in 2004 as an add-on to SQL Server 2000. In order to use Reporting Services you must also have both SQL Server 2000 as the data source and Visual Studio .NET 2003 as the IDE.
Background: Before 2004, there are a number of third-party reporting products available, the most (in)famous one being Crystal Reports. Starting from 2004, Microsoft released their own reporting solution software, Reporting Services. Reporting Services was an add-on for SQL Server 2000, and has been made available by Microsoft since early 2004. Furthermore, Reporting Services is licensed as part of SQL Server 2000, which means if your company already has a licensed copy of SQL Server, you're in luck - Reporting Services can be installed and run on the same server for no additional license fee.
2. SQL Server 2005, SQL Server 2008, SQL 2008 R2 - BIDS
a. SSAS: BIDS + SSMS
b. SSIS: BIDS + SSMS
c. SSRS: BIDS/Report Builder + SSMS + Report Manager
Note:
1. SQL Server 2005 uses Visual Studio 2005 Shell, .NET Framework 2.
2. SQL Server 2008 and 2008 R2 uses Visual Studio 2008 Shell, .NET Framework 3.5
3. SQL Server 2012 – SQL SERVER DATA TOOLS (SSDT), included in SQL Server 2012, replaces BIDS. Also notice there are two versions of SSDT: one for SQL Server and another for Visual Studio.
a. SSAS - Tabular model added
b. SSIS - Project Deployment added
c. SSRS: - No major enhancements
Note:
1. SSDT for SQL Server 2012 – Initially Visual Studio 2010 Shell, .NET Framework 4.0. When you create the first BI project, it may force you to download and install SSDT one more time to upgrade the .NET Framework to 4.5.
(See http://www.mssqltips.com/sqlservertip/2804/introduction-to-sql-server-data-tools/)
The above article stated that after upgrading the SSDT, the SSDT will be VS 2012 shell. However, in my case, it is still a VS 2010 shell with .NET 4.5.
2. When I opened Visual Studio 2012 ultimate. Surprisingly, I did not see any BI templates installed. Googled and found this link: http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx.
After Downloaded SQL Server Data Tools for Visual Studio 2012 and installed it, I now have BI templates in VS 2012 Ultimate. This is SSDT for VS 2012 - adding BI templates to VS 11.0.61030.00 .NET Framework 4.5.51641, independent of the SQL Server Data Tools - adding VS shell with BI templates to SQL Server.
3. As the file is x86, and my SQL Server 2012 is x64, I have to install it as a new instance to avoid the installation error.
4. SQL Server 2014 – use the same SSDT as that for SQL Server 2012 so far.
