SQL Server Analysis Services(SSAS)

SQL Server Analysis Services(SSAS)

SSAS full form is SQL Server Analysis Services.

Multi-dimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data.

SSAS is an analysis service platform, which can be used to create and manage the analysis database.

SSAS Contains Preaggregated data , internally creates analysis database, and once the analysis database is ready, it can be used for many purposes

It has 2 variants Multidimensional and Tabular.

One or more cubes can be presented in the analysis database.

Advantages of SSAS:

· High performance reports

· Multidimensional data analysis

· Slice and data analysis

· Data mining purpose

Using the Cube Data Base or Analysis Data Base:

There are several client tools to use Analysis database.

a. Analyzing the cube database data in the SSDT/BIDS browser.

b. Using the PIVOT table in the excel application to connect and work with cube database.

c. Using Reporting tools (SSRS, Cognos) to generate the reports.

d. By writing the MDX queries in the cube database.

e. Using Panorama Novaview and ProClarity tools to analyze the data.

SSAS Developer Roles:

· Understanding the Data base structure

· Designing the cubes

· Scripting

· Mdx language

SSAS Admin Roles:

· Installation

· Configuration

· Deployment

· Processing

· Security

· Managing[Backup and Restore]

· Monitoring & Trouble shooting

The basic concepts of OLAP include:

• Cube

• Dimension table

• Dimension

• Level

• Fact table

• Measure

• Schema

Data Source:

Connection string that defines how Analysis Services connects to a physical data store.

Data Source Views

A data source view contains the logical model of the schema used by Analysis Services database objects—namely cubes, dimensions, and mining structures. A data source view is the metadata definition, stored in an XML format.

Named Queries:

A Named Query is a SQL expression represented as a table. It Can be used to divide large and complex dimension table to smaller and simple dimensions, it can also help us to unite columns from multiple tables to single table .It allows us to extend our table schema without modifying underlying base tables.

Cube

The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that's been aggregated to allow queries to return data quickly.

Cubes are ordered into dimensions and measures. Dimensions come from dimension tables, while measures come from fact tables.

Dimension table

A dimension table contains hierarchical data by which you'd like to summarize. Examples would be an Orders table, which you might group by year, month, week, and day of receipt, or a Books table that you might want to group by genre and title.

Dimension

Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data: time or category in the examples above. Typically, a dimension has a natural hierarchy so that lower results can be "rolled up" into higher results. For example, in a geographical level you might have city totals aggregated into state totals, or state totals into country totals.

Level

Each type of summary that can be retrieved from a single dimension is called a level. For example, you can speak of a week level or a month level in a time dimension.

Fact table

A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, drug effectiveness information, or anything else that's amenable to summing and averaging. Any table that you've used with a Sum or Avg function in a totals query is a good bet to be a fact table.

Measure

Every cube will contain one or more measures, each based on a column in a fact table that you'd like to analyze. In the cube of book order information, for example, the measures would be things such as unit sales and profit.

Schema

Fact tables and dimension tables are related, which is hardly surprising, given that you use the dimension tables to group information from the fact table. The relations within a cube form a schema. There are two basic OLAP schemas: star and snowflake.

Star Schema:

Every dimension table is related directly to the fact table.

Snowflake Schema:

Some dimension tables are related indirectly to the fact table.

For example, if your cube includes OrderDetails as a fact table, with Customers and Orders as dimension tables, and Customers is related to Orders, which in turn is related to OrderDetails, then you're dealing with a snowflake schema.

Learn SSAS from Scratch

Url: View Details

What you will learn

• SSAS from Scratch
• SSAS Cube development

Rating: 3.95

Level: All Levels

Duration: 2 hours

Instructor: VenkatM M