SQL Server Analysis Services

The SQL Server makes the design of multi Dimensional cubes for OLAP related analysis and data mining simple and intuitive. OLAP allows users to interactively view and analyze large data sets in data warehouse type applications. OLAP uses a relational data warehouse as a data source and places pre-specified measures (usually Key Performance Indicators such as sales, course grades, and so on) into an appropriate context through dimensions (a sales dimension may be months of the year, a course grade dimension may be a semester or perhaps weekly exams). Essentially, multi dimensional 'cubes' are created by relating measures and dimensions in two-dimensional database tables. The constituent data for the cubes are pre-aggregated and analysis ready. Business users (rather than database developers) may now use tools to browse the information contained in the cube and perform ad hoc analysis.

The SQL Server 2005 Business Intelligence Workbench suite utilizes a SQL Server Relational Database to hold data; Analysis Services to create a multidimensional OLAP cube; Reporting Services to design and output professional reports from OLAP cube data; and a Data Mining component to extract and analyze data using algorithms that focus on specific measures.

Once appropriate dimensions and KPI measures are identified, Analysis Services may be used to construct an OLAP cube. Wizards make dimension creation quite intuitive. First, a data source view that imports the relevant database objects should be created. Afterwards, the dimensions and measures may be mapped to create the cube. A language called MDX may be used to query multidimensional cubes for complex output called 'calculated measures'. An Excel 'Pivot' table may also be used to query the OLAP cube.

The built-in cube browser may be used to view data in the cube. Users may drag and drop dimensions and KPIs into x and y axes to see graphs and tables with aggregate data such as KPI measured over time, shortfall from goals and so on.