SQL Server Analysis Services

Organizations need to drive actionable insights to people across the enterprise. Rely on one of the most widely used multidimensional analysis tools to build comprehensive, enterprise-scale analytic solutions that reach every user through familiar applications.

SQL Server Analysis Services (SSAS) provides

Rely on feature-rich, powerful, and easy-to-use online analytic processing (OLAP) and data-mining capabilities.
Scale analytical solutions to meet enterprise needs.
Enhance productivity with design aids and best practices.
Drive actionable insight with help from familiar tools.
Consolidate enterprise analysis data and KPIs.

Before getting in to SSAS, let us get few Terminologies understand.

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.
It is a set of related Measures (Fact) and Dimensions that is used to analyze data
Dimension
A Dimension is a group of attributes that represent an area of interest related to the measures in the cube, and which are used to analyze the measures in the cube.
Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data.
Fact Table
A measure is a fact, which is a transactional value or measurement that a user may want to aggregate. Measures are sourced from columns in one or more source tables, and are grouped into measure groups.
A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, or anything else that’s amenable to summing and averaging.



From the above picture, what did we get??

Fact / Measures
The values within the cube cells represent the two measures, Packages and Last. The Packages measure represents the number of imported packages, and the Sum function is used to aggregate the facts. The Last measure represents the date of receipt, and the Max function is used to aggregate the facts.

Dimension
The Route dimension represents the means by which the imports reach their destination. Members of this dimension include ground, non-ground, air, sea, road, or rail. The Source dimension represents the locations where the imports are produced, such as Africa or Asia. The Time dimension represents the quarters and halves of a single year.

Advantages of SSAS Cube

Very fast response to give you the information you have previously designed the cube to hold
SSAS is fast even on a large volume of data
SSAS calculated measures are fast execution-wise and easy reusable
They are defined centrally in the SSAS database, and the reports pick and choose the calculated measures they want.
Drilldown path is totally dynamic, you get clean and structured aggregations of the measures in the cube
A single structure to query: no joins, no underlying detailed knowledge of the data entities are required, since it is built on a fact

Where SSAS comes into picture… check the highlighted one...



Couple of schema types which we should have a look on are;

Star Schema
A relational database schema for representing multidimensional data.
It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions.
The center of the star schema consists of a large fact table and it points towards the dimension tables.
The advantage of star schema are slicing down, performance increase and easy understanding of data.



Snowflake schema

A star schema structure normalized through the use of outrigger tables. i.e dimension table hierarchies are broken into simpler tables.
In OLAP, this snow flake schema approach increases the number of joins and poor performance in retrieval of data. Since dimension tables hold less space, snow flake schema approach may be avoided.



Now lets get into creations of SSAS Cube.

Steps to create a Cube through SSAS

Create a new Analysis Services project
Define a Data Source
Define a Data Source View
Create Dimensions
Create the Cube
Use this Cube for Analysis

I will add screen shots of each step in the next part of this blog.

Keep improving...

Comments

Post a Comment

Popular posts from this blog

Interview Questions to Ask the Employer

It's your turn! As the interview comes to a close, one of the final questions you may be asked is "What can I answer for you?" Have interview questions of your own ready to ask. You aren't simply trying to get this job - you are also interviewing the employer to assess whether this company and the position are a good fit for you. Here are questions to ask the interviewer so you can ensure the company is a good match for your qualifications and interests. How would you describe the responsibilities of the position? How would you describe a typical week/day in this position? Is this a new position? If not, what did the previous employee go on to do? What is the company's management style? Who does this position report to? If I am offered the position, can I meet him/her? How many people work in this office/department? How much travel is expected? Is relocation a possibility? What is the typical work week? Is overtime expected? What are the prospects for growth and a...
Read more

Place .NET DLL in GAC

Today other team guy has asked me help as he was facing some problem in trying to place a .NET DLL in GAC where he does not have source code with him. Potentially you just have to follow regular steps as mentioned below to place the DLL in GAC; 1. Generating the public key: Go to visualStudio.Net command prompt and type the following command: sn -k keyfile1.snk Here keyfile1.snk is the keyfile we are generating. You can store this key file in the bin folder of the assembly. In order to do this u have to go to the bin folder. For example: C:\foldername\assemblyname\bin sn-k keyfile1.snk By doing this the keyfile is stored in the bin folder. 2. Than place the keyfile into the Assembly. Go to AssemblyInfo.cs file: [assembly: AssemblyKeyFile("C:\foldername\assemblyname\keyfile1.snk")] 3. Then build the Assembly. 4. Placing the assembly into GAC This process is fine when you have source code with you. What if you don't have the source code with you and you need to put it to t...
Read more

Windows Communication Foundation - FAQ

Image
Difference between Web Service and WCF? Major Difference is That Web Services Use XmlSerializer But WCF Uses DataContractSerializer which is better in Performance as Compared to XmlSerializer. Key issues with XmlSerializer to serialize .NET types to XML Only Public fields or Properties of .NET types can be translated into XML. Only the classes which implement IEnumerable interface. Classes that implement the IDictionary interface, such as Hash table can not be serialized. The DataContractAttribute can be applied to the class or a strcture. DataMemberAttribute can be applied to field or a property and theses fields or properties can be either public or private. Web Services : 1.It Can be accessed only over HTTP 2.It works in stateless environment WCF : WCF is flexible because its services can be hosted in different types of applications. The following lists several common scenarios for hosting WCF services; IIS WAS Self-hosting Managed Windows Service Important difference ...
Read more