July 13, 2011, 4:32 p.m.
posted by redstink
The appeal of a single source technology provider, like Microsoft and SQL Server 2005, is that it makes the process of building a DW/BI system easier in several ways. First, many elements of the architecture are predefined. The major technology issues you need to tackle involve data sizing, server configurations, and performance, rather than which products to buy and whether they work well together. Some organizations may need to develop or buy functionality to meet specific business requirements, like large-scale consumer name and address matching. Many organizations will also want to add one or more third-party, user-oriented query tools to the mix.
The Microsoft toolset includes credible versions of all the tools you need to build and deliver a solid, viable data warehouse and business intelligence system. Some components of the SQL Server 2005 architecture are more than credible: Analysis Services, for example, is one of the top OLAP engines available.
Many of the tools are designed specifically to support dimensional data warehouses. For example, Integration Services has the Slowly Changing Dimension transformation, and Analysis Services is built with dimensional constructs from the ground up.
The tools are open and programmable. If you want to build a heterogeneous DW/BI solution, you can swap out any component. If you want to build a fully automated DW/BI management system, you can script any operation in practically any programming language you wish.
The toolset includes software beyond SQL Server. This book has focused almost exclusively on SQL Server 2005 because DW/BI at Microsoft begins in SQL Server. But it doesn’t end there. Even the version of Office that’s available at the time of this writing, and as SQL Server 2005 ships, is functional and extremely popular for accessing and manipulating data. Business users love Excel, and that’s where they want their data to end up. The Office suite can access the DW/BI system directly (albeit awkwardly), and Reporting Services makes it easy for a business user to save a report to Excel.