How Does SSAS Work?

A typical implementation workflow includes installing a SQL Server Analysis Services instance, creating a tabular or multidimensional data model, deploying the model as a database to a server instance, processing the database to load it with data, and then assigning permissions to allow data access. When ready to go, the data model can be accessed by any client application supporting Analysis Services as a data source.

To create a model, Visual Studio with Analysis Services projects extension, also known as SQL Server Data Tools or simply SSDT, is used. Typically, a model can be based on a Tabular or Multidimensional project template. The project template contains folders for all of the objects needed in a model. Wiizards are used to create all of the basic elements, such as data sources, data source views, dimensions, cubes, and roles. SSAS dimensions are groups of attributes based on columns from tables or views in a data source view. A cube is a data structure in SQL Server Analysis Services (SSAS) that is built, using OLAP databases, to allow near-instantaneous analysis of data

Models are populated with data from external data systems, usually data warehouses hosted on a SQL Server or Oracle relational database engine (Tabular models support additional data source types). Models specify query objects, such as cubes, but also specify dimensions that can be used in multiple cubes, calculations and KPIs that encapsulate business logic, and interactions such as navigation and drill-through behaviors.

To use a model, it is deployed to a server instance that runs databases in a particular server mode, making the data available to authorized users who connect through Excel or other applications.