Create OLAP Cube in SSAS

In this article, we are going to show you how to create an OLAP cube in SQL Server Analysis Services (SSAS) using a cube Wizard.

SSAS OLAP Cube is a combination of Measures (Derived, Calculated Measures) and Dimensions, calculations, perspectives, actions, partitions, key performance indicators (KPIs), and translations.

Create OLAP Cube in SQL Server Analysis Services

Within the Solutions Explorer, Right-click on the Cube folder will open the context menu. Select the New Cube option, as shown in the below screenshot.

Create OLAP Cube in SSAS 1

It will open the SSAS OLAP Cube wizard with the welcome page. If you don’t want to see this welcome page again, check the Don’t show this Page again option below.

Create OLAP Cube in SSAS 2

Click next button

Select Creation Method

Within the cube wizard, this page will give you 3 options to choose

  • Use Existing Tables: If you select this option, then the cube will use the existing tables in the Data Source View.
  • Create an Empty Cube: This option will create an empty cube with no Measures and Empty Dimensions. After completing the cube creation, we have to add them one by one.
  • Generate Tables in the Data Source: It will generate tables in the Data Source directly.
Create OLAP Cube in SSAS 3

It is always better to choose the option (Create an Empty Cube), that’s what I did in my experience, and I hope this is the best approach for any developer. Click on Next Button

Select the Data Source View

This page is used to select the Data Source View for our Empty cube. Here we can choose the data source if we already created or else we can select none, and later, we can create and use it. Again it’s a developer’s choice, but it is always good practice to create the data source and data source views at the beginning.

Create OLAP Cube in SSAS 4

Here we are selecting the Data Source View we created in the previous article. Please refer SSAS Data Source View article to understand How to create the Data Source View in SSIS

Click the Next button and rename the cube as per your requirement.

Create OLAP Cube in SSAS 5

Click the finish button to complete the cube creation.

Let’s look at our Solution Explorer to check for newly created Cube.

Create OLAP Cube in SSAS 6

Creating Cube Using Existing Tables

Here we are selecting the Existing Tables option to show you, How this gone to work.

Create OLAP Cube in SSAS 7

Click on the Next button

Select Measure Group Tables

Select the already created (existing) Data Source View. Here also we are selecting the Adventure Works DW2014, which we created in our previous article.

Create OLAP Cube in SSAS 8

Now select the Measure Tables (Measure Groups or Fact tables) from the list of available tables in the Data Source View.

Create OLAP Cube in SSAS 9

Click the next button.

Cube wizard will take you to the next page to select the individual Measures from Measure Groups.

Create OLAP Cube in SSAS 10

Here we selected some random Measures and clicked on the Next button.

Select Existing Dimension for SSAS OLAP Cube

This page allows us to select the required dimensions for cube creation from the list of available dimensions

Create OLAP Cube in SSAS 11

Click on the Next button and provide the cube name as per your organization’s requirements.

Create OLAP Cube in SSAS 12

Click the Finish button and see whether we created the SSAS OLAP cube successfully or not

Create OLAP Cube in SSAS 13