SSAS

SQL Server Analysis Services OLAP cubes are used to look at data from a top down perspective. It is the big picture opposite to OLTP where you are at the row level and have to do work to aggregate data. In SSAS data is pulled from OLTP data mart and processed (aggregate) the data into a muti dimensional data cube.

OLAP Data Cube

OLAP Data Cube Example

Continuing from OLTP Datamart

If this were a OLTP database this query would take quite a while because all the hours days etc would have to be aggregated

SELECT COUNT(*) FROM table WHERE source = ‘Western Hemisphere’

In OLAP based on the storage we chose everything has already been pre-aggregated so it would run very fast

SELECT Count(NonEmpty({[Source].[Western Hemisphere].Children} ,[Packages]))

 

Working off our OLTP Datamart

 

We can now create a cube with 2 dimensions

Business Intelligence Studio SSAS
Business Intelligence Studio SSAS

Create a new DatSource

New SSAS DataSource
New SSAS DataSource

Use service account

Create new Data Source View

New Data Source View
New Data Source View

Create new cube

New Cube Wizard
New Cube Wizard

Select Measures

Select Measure Groups
Select Measure Groups

Select Dimensions

Select Dimensons
Select Dimensions

Dimensions

Click Next and Finish

SSAS Cube with Dimensions
SSAS Cube with Dimensions

 

Select project name and properties

SSAS Project Deployment

Next we need to design our dimension hiarchy

Edit Time Dimension
Edit Time Dimension

It should look like this

 

Add Dimensions ( Month Name, Quarter, Short Date, Year)

In Attribute Relationships, delete all relationships

 

When finished it should look like this

Go back to Dimension Structure and drag Year into hierarchies, Quarter, Month Name and Short Date

The date_id is useless as it is only a surrage key so set its visible property to false

Last make sure that the Dimension Usage has its granularity set to date_id

You should be  able to process at this point and we have our time dimension.

 

 

Destination (where people are navigating to on derekevans.us) should look like the following