Widely supported APIs and functions - The OLAP Server and Microsoft PivotTable service support OLE DB, ADO, user-defined function, and decision support objects (DSO).
In the early 1990s OLAP tools were not too easy or intuitive for the end users to use. Microsoft recognized the need for a better database system that can be used for data warehousing and for tools that would make the data analysis an easy and enjoyable process. With the release of Microsoft SQL Server 7 and the tools that accompanied it, Microsoft was able to deliver a viable solution to the problem. Microsoft enhanced its solution tremendously with the new release of SQL Server 2000, included a new version of OLAP Services adding to it data mining capabilities and labeling them as Analysis Services.
Microsoft Analysis Services comes with some good features that address the complexity of the OLTP systems. These features are:
Ease of use - Many wizards, editors, and help materials are available to help the user at every step of the way.
Flexibility - Several storage modes are available through the Microsoft OLAP Server. These include storing cube data into multidimensional cube files (MOLAP), relational databases (ROLAP), or a hybrid of the two (HOLAP).
Scalability - The OLAP client can run on Windows 9x, Windows NT, Windows 2000, Windows XP and Windows Server 2003.
Integration - Security in Analysis Services is integrated with SQL Server and Windows NT security. This integration allows for powerful usage of OLE DB data sources as a result of having an OLE DB provider for OLAP services, and client- and server side cache. Many third-party vendors are developing integration adaptors between their products and Microsoft Analysis Services as part of the Microsoft Data Warehousing Alliance.
SQL Server 2000 is the foundation of the Microsoft data warehousing and Analysis Services. SQL Server relational databases serve as the main data source for the Analysis and data warehousing Services. Naturally, the data source could be any other database, such as Oracle, or any others that have an OLE DB Provider written for them.
Microsoft introduced data transformation services (DTS) into SQL Server 7 to facilitate the collection and transfer of data from its OLTP sources to the OLAP system. DTS was further enhanced in SQL Server 2000, with additional functionalities and refinements that make it an essential tool to use in data transformation and migration. DTS provides a means of moving the data from the source to the destination data warehouse. In the process, DTS performs data validation, cleanup, consolidation, and transformation of the data when needed.
Meta data is, by definition, data about data. In other words, the information about the way storage is structured in the data warehouse, OLAP, data mining, and DTS services are all kept as meta data, which is stored in the Microsoft Repository. The Repository is built to maintain such technical information about the data sources involved with the services mentioned above.
Microsoft Decision Support Systems (DSS) consist of the DSS Analysis server, also referred to as the OLAP server, and the PivotTable Service. The Analysis server is the heart of Microsoft Analysis Services, extracting data from heterogeneous sources across the organization for analysis and querying, as well as storing aggregated and processed data in the multi-dimensional database for quick access when needed. It can also utilize data stored in summary tables in the OLTP database as well. PivotTable Service allows the user to create cross-tab tables on the fly by specifying the columns and rows on which aggregations should be made. PivotTable service is a client-side processing tool, a standalone and distributable component.
Analysis Server can work with several tools on the OLAP client side, such as English Query, and Microsoft Office (especially, MS Excel and MS Access) allowing these tools to access its cube data through the pivot table service.
Analysis Manager is a graphical user interface (GUI) that allows the user to build an OLAP solution based on existing data sources. The screenshot below shows the main screen of Analysis Manager when it is first opened.
Analysis Manager can easily manage the OLAP databases. It provides wizards to add new data cubes, and to change the structure of existing cubes by adding or changing dimensions and their levels of complexity.
Data warehousing and OLAP is gaining great attention among information technology professionals, companies, and clients. It is a natural step in the evolution of data management, where large amounts of data are being collected by the organization every day. The data can yield tremendous benefits when analyzed. Microsoft recognized this trend and as a result began enhancing its enterprise database management system, SQL Server, to handle data warehousing needs.