SQL Server Analysis Services (SSAS) Fundamentals
SSAS comes with your purchase of Microsoft’s SQL Server. It is a separate component and can be installed in two different modes, Multidimensional or Tabular. Multidimensional is the traditional method of building and OLAP cube. Tabular mode using in memory techniques to aggregate relational tables into a cube like format. Using Analysis Services enables you to leverage the power of OLAP analytics to quickly query millions of rows of pre-aggregated data. In addition to building and exploring data, SSAS ships with data mining algorithms that further allows you to enhance your data to get predictive analysis. Once the data is stored in the SSAS structures users can easily access it via Excel or other reporting/analytic software.
Understanding how SSAS looks at its data structure is the key to being able to effectively leverage the tool. I will start by defining basic concepts and components of SSAS.
- Dimension – Describes your data. Examples include, Geography, Color, Size, Time
- Member – Represents a single point on a dimension. For example, State would be a member in the Geography Dimension
- Value – is the significance of the member. Texas would be the data in the State member
- Attribute – An Attribute represents all of the Members within a Dimension. All of the states in the Geography dimension would an Attribute
- Tuple – A set of coordinates in the cube
- Set – Is a collection of Tuples enclosed by curly braces {}
- Slice – A section of the cube defined by a Tuple
- Scope – A section of the cube that is being used for calculations
- Measure – The value in a cell
Before beginning any new SSAS projects be sure you clearly understand the SSAS fundamentals. SSAS is a great solution to use when you have large amounts of data that you need to aggregate. This is an easy transition for users who are familiar with Excel Pivot Tables. They get the look and feel they are used to leveraging while having the processing power and speed of their data being on a server.
