Definition:
Microsoft SQL Server Analysis Services (SSAS). It is data mining tool
and online analytical Processing (OLAP) in Microsoft SQL Server. It is
used as a tool by organizations to Analyze and make sense of information
spread out across many databases, Or in disparate tables or files.
Overview of SSAS:
SQL Server Analysis service is one of the technologies from Microsoft
Business Intelligence Stack. It is used to develop the online analytical
process (OLAP). In a simple way, SSAS used to Create cubes using data from
data marts for deeper and faster data analysis.
Cubes are multi dimensional data sources which have dimensions and facts as
its Basic constituents.Perspective dimensions can think of as master tables
and fact Can think of as measurable details. These details are generally
stored in a Pre-aggregated proprietary format. Users can analyze big amounts
of data and share MultiDimensional expression (MDX) is the query language used
to query a cube.
Example of dimensions can be product/customer, and similar simple example of
facts can be Orders/sales. The analysis could be analyzed sales in Asia pacific
geography since last 6 years. You can think of this data as a pivot table where
geography is the column Axis and years are the row axis, and sales can be seen
as the values. Geography can also have Its own hierarchies like Country->City->State.
Time can also have its own hierarchy like Year->Semester->Quarter. Sales could
then be analyze using any of these hierarchies for Effective data analysis.
How to develop a typical higher level cube using SSAS:
1) Reading data from a dimensional model
2) Configuring a schema in BIDS (Business Intelligence Development Studio)
3) Create dimensions, measures, and cubes from this schema
4) Fine tuning the cube as per the requirements
5) Deploying the cube
In this tutorial, we will use many topics. It is used to understand to create a basic cube.
The high-level outline is as follow:
1. Design and develop a star-schema
2. Create dimensions, hierarchies, and cubes
3. Process and deploy a cube
4. Develop calculated measures and named sets using MDX
5. Browse the cube data using Excel as the client tool
If you start learning SSAS, you should have a suitable relational database background.
But if you start working in a multi-dimensional environment, to stop thinkings From a
two-dimensional perspective, which will develop over time?
Limitations of SSAS:
1. Leverage SQL Server Analysis Services to produce Business Intelligence solutions
2. Create and deploy multidimensional data cubes
3. Extend hierarchies and exploit advanced dimension relationships
4. Build custom solutions with MDX
5. Put in place Key Performance Indicators (KPIs) to watch business objectives
6. Make smarter business decisions with data mining techniques