SSAS – make cube in 5 minutes

To support my current database team with additional database skills I decided to start some series focused on BI area. This POST describes quick start with Analysis Services – Multidimensional Databases.

I already wrote post regarding Analysis Services (Create SSAS database) and its settings for multidimensional mode. What should be prepared in advance are some relation data (Adventure Works) and Data Tools studio supporting Analysis Services. You can find in my previous post.

GOAL:

Let’s make a simple cube including 1 measure and 2 dimensions.

In short:

  1. Let’s create new project first – Multidimensional SSASCreate Analysis Services Database
  2. Create Data Sources
  3. Create Data Source View
  4. Create Dimensions
  5. Create CUBE
    1. Define measures
    2. Create dimension relationships
  6. Deploy solution to server
  7. To be continued

Create Analysis Services Multidimensional and Data Mining project first.

New project
New project

Define Data Sources. In our case, there is one Data Source to relation database. There could be defined more Data Sources from different connections. In Solution Explorer, right click on Data Sources section and click on New Data Source, define connection info in dialog box and confirm to create Data Source. Solution Explorer with defined Data Source should look like on picture bellow.

Solution explorer Data Source
Solution explorer Data Source

As next step we must define objects for logical database model. Right click on Data Source Views section in Solution explorer and click on new Data Source Views and Wizard to create Data Source Views appears. Select Data Source in next window, Adventure Works DW in our case and you should see dialog with list of objects form your Data Source on left side. Choose objects for your data model and continue with next.

Data Source Wizard
Data Source Wizard

After selection you should have chosen objects on the right side of Select Tables and Views window. We selected DimCustomer, FactInternetSales and DimDate in our case.

Data Source View Wizard
Data Source View Wizard

As last step name Data source View and Data Source View Wizard is finished.

Data Source View Wizard
Data Source View Wizard

In case you have relational layer defined with referential integrity, it should be transferred to the Data Source View. In case that primary and foreign keys are missing you can create logical relationships between tables in Data Source Views. It is possible to create more Data Source Views, in case you have lots of database objects it is good practice to split them according to their logical area.

When above mentioned steps are done you can see the same Solution Explorer as on picture bellow.

Solution explorer - Data Source View
Solution explorer – Data Source View

Let’s check data model in Data Source Views on picture bellow. You can see that relationships were created according to relation layer by default.

Data Source View - model
Data Source View – model

To create our cube, we use Cube Wizard where measures and dimensions are defined.

Cube Wizard
Cube Wizard

To speed up our process, check Use existing tables. In next window, select Fact table to define measures. You can click on Suggest button to check it automatically (based on defined Data Source View).

Cube Wizard - measure
Cube Wizard – measure

To finish Cube Wizard choose dimensions you use in the cube.

Cube Wizard - dimension
Cube Wizard – dimension

Now you should see created Cube and Dimensions in Solution explorer.

By double click on cube you see cube structure work space, with information regarding Measures, Dimensions, Data Source View etc.

Cube structure
Cube structure

On the top menu of the Cube Structure Workspace you can see menu yo can navigate to other sections, like Dimension usage, KPIs, Actions etc. What is interegsting now is to check Dimension usage you defined relationship between facts and dimensions. As you can see Cube Wizard did this task automatically.

Dimension usage
Dimension usage

Now we can deploy our solution to server. Go to Project -> Properties see Configuraion dialog as on picture bellow. In Deployment section set Target Server and Database name. In other options you can set for example Processing Option to specify whether Analysis Services objects should be processed with deployment of the project. Leave Default in our case to Process cube after deployment.

Project Configuration - Deployment
Project Configuration – Deployment

After you confirm above mentoned seting go to solution explorer, right click on project and click on Deploy in context menu on picture bellow.

Deploy
Deploy

After deployment we can Browse deployed and processed cube in Cube Browser – top right menu.

Cube Browser
Cube Browser

As next steps we could extend dimensions by other attributes, try to create Hierarchy, add another dimension, etc.
Lots of scenarios you can try now, but to be continued in next posts.