Create your first OLAP Cube in SSAS

I get below links to create olap cube in SSAS using database AdventuresWorksDW. These links provide very clear cut guidance (step by step) to create a cube in ssas.

Let's create an Analysis Services Cube using the new Cube Wizard that comes with SQL Server 2008 Business Intelligence Development Studio.

Please follow the steps below (make a click on each step for details):

1. Create an Analysis Services Project
2. Create a Data Source
3. Create a Data Source View
4. Create a Cube
5. Create perspectives
6. Deploy the Analysis Services Project

Storage types of cube MOLAP, ROLAP, HOLAP

A cube is a structure made of number of dimensions, measures, etc.
Cubes usually rely on two kind of tables like 'fact-table' (for cubes) & 'dimension-table' (for cube’s different dimensions).
A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).
It store the data in pre-aggreagted form.

Cube Storage type:
MOLAP (Multi dimensional Online Analytical Processing)
MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

ROLAP (Relational Online Analytical Processing)
ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube. Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

HOLAP (Hybrid Online Analytical Processing)
HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type. Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification
and will process the aggregations again.With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.

We use MOLAP storage type generally for deliver high performance


More clear image to understand the storage type of cubes:-

OLAP CUBE

What is OLAP cube:- 
An OLAP cube is a collection of measures (facts) and dimensions from the data warehouse.
An OLAP cube is a multidimensional database that is optimized for data warehouse and online analytical processing (OLAP) applications.
An OLAP cube is a method of storing data in a multidimensional form, generally for reporting purposes.
In OLAP cubes, data (measures) are categorized by dimensions.

Data and aggregations are stored in a (pre-summarized across dimensions) optimized format to offer very fast query performance.
MDX (multidimensional expressions) query language is used to interact and perform tasks with OLAP cubes.
The MDX language was originally developed by Microsoft in the late 1990s, and has been adopted by many other vendors of multidimensional databases.

Although it stores data like a traditional database does, an OLAP cube is structured very differently.
OLAP cubes, however, are used by business users for advanced analytics.
Thus, OLAP cubes are designed using business logic and understanding.
They are optimized for analytical purposes, so that they can report on millions of records at a time.

When to use CUBE-
There are three reasons for adding a cube to your solution:
1. Performance-  A cube’s structure and pre-aggregation allows it to provide very fast responses to queries that would have required reading, grouping and summarizing millions of rows of relational star-schema data.
The drilling and slicing and dicing that an analyst would want to perform to explore the data would be immediate using a cube but it could take longer when using a relational data source.

2. Drill down functionality-  Many reporting software tools will automatically allow drilling up and down on dimensions with the data source is an OLAP cube.
    Some tools, like IBM Cognos’ Dimensionally Modeled Relational model will allow you to use their  product on a relational source and drill down as if it were OLAP but you would not have the performance gains you would enjoy from a cube.

3. Availability of software tools-  Some client software reporting tools will only use an OLAP data source for reporting. These tools are designed for multi-dimensional analysis and use MDX behind the scenes to query the data.

Disadvantage of using SSAS cube-
SSAS processes data from the underlying relational database into the cube.
After this is done the cube is no longer connected to the relational database so changes to this database will not be reflected in the cube.
Only when the cube is processed again, the data in the cube will be refreshed.

So Basically what the cube is:-
A cube is a structure made of number of dimensions, measures, etc.
Cubes usually rely on two kind of tables like 'fact-table' (for cubes) & 'dimension-table' (for cube’s different dimensions).
A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).
It store the data in pre-aggreagted form.

see also storage type`s of cube MOLAP, ROLAP, HOLAP

Cannot define PRIMARY KEY constraint on nullable column in table

create table parent(id int , name varchar(30));
alter table parent add constraint pk_parent primary key (id);

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ''.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

CAUSE:
We are trying to add primary key constraint on column which is nullable.

SOLUTION:

Modify column to not null
alter table parent alter column id int not null;
alter table parent add constraint pk_parent primary key (id);
Command(s) completed successfully.

Could not create constraint. See previous errors.

Execute below query to get the error:

create table parent(id int , name varchar(30));
create table child(id int, parent_id int);
alter table child alter column id int not null;
alter table child add constraint pk primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table '' that match the referencing column list in the foreign key ''.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Cause:
We are trying to create a FOREIGN KEY constraint on a table and the column being referenced as a FOREIGN KEY is not a
PRIMARY KEY on the other table.

SOLUTION:1

Add primary_key on referenced column.
alter table parent alter column id int not null;
alter table parent add constraint pk_parent primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:2

create unique index idx_uni on parent (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:3

alter table parent add constraint uq_parent_id unique (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.