Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSAS - Building a Cube

Using SSAS in Visual Studio 2015 to Build a Cube and Data Source View

This article describes how to create a SQL Server Analysis Services project and connect to a Data Warehouse database to create a Data Source View and a Cube. The database used is AdventureWorksDW2012.

Creating the SSAS Project and Connecting to the Data Source

To begin, open Visual Studio and select Analysis Services Multidimensional and Data Mining project.

SSAS Project Selection

Right click the Data Source in the Solution Explorer and click New Data Source. Complete each step in the Wizard to connect to the Database AdventureWorksDW2012.

SSAS Data Source Connection

Creating a Data Source View

Right click Data Source Views in the Solution Explorer and select New Data Source View to launch the Wizard. This cube includes only the Product and Customer Dimensions tables and the Fact Internet Sales table.

SSAS Data Source View Wizard

Click through the Next buttons to Finish and complete the Data Source View.

SSAS Data Source View Schema

Creating the Cube in SSAS

Right click on Cubes in the Solution Explorer and select New Cube to start the Cube Wizard. Select the FactInternetSales table as the Measure Group table.

SSAS Cube Wizard

Select the New Dimensions, if not already selected for you.

Cube Dimension Selection

Click Finish to Complete the Cube.

SSAS Complete Cube Wizard

The Cube Structure window should now appear.

SSAS Cube Schema

Double click on each of the Dimensions in the Solution Explorer to add attributes to them. Only the Key will be in the Attributes pane when it is first created. The fields from the Data Source View must be selected and Dragged over to the Attributes pane to complete the Dimension.

SSAS Dimension Structure

Some of the Dimensions may not have been automatically created when creating the Cube. To Manually create the Dimensions in this project, right click on Dimensions in the Solution Explorer and click on New Dimension.

SSAS Adding Dimensions to Cube

Select the Dimension to add from the drop down and click next.

SSAS Dimension Source

Select the attributes to include in the Dimension and click Next.

SSAS Dimension Attributes Selection

Click Finish to complete the addition of the Subcategory Dimension. Dimensions will need to be added for Product Category and Geography by repeating these same steps.

Completing SSAS Dimension Manual Addition

Adding Dimension Usage to Measures in the Cube

The newly added Dimensions will not appear in the Dimension Usage tab of the Cube as they need to be added. The cube may be deployed and processed without them and they will appear in SSMS using a connection to the Analysis Server in the Object Explorer, but they will not appear in the Metadata tab of an MDX query as there is no relationship established with the Measure group. So they are unusable until this step is completed.

SSMS View of Analysis Server SSMS MDX Query Metadata

In the Cube, click on the Dimension Usage tab and click on the button to Add Cube Dimension.

SSAS Dimension Usage

in the Select Relationship Type drop down pick Referenced. Select the Intermediate Dimension Relationship (if not already selected) as Product and select the Reference and Intermediate dimension Attributes as the Product Subcategory Key and click OK.

SSAS Dimension Referenced Relationship

The Dimension and Measure groups are now related for Product and Product Subcategory. The same steps may be used to add Cube Dimensions for Product Category (to Product Subcategory) and Geography (to Customer).

SSAS Dimension Usage Cube Updates

Deploy and Process the Cube

In the Solution Explorer, right click the Project Name and click on Deploy.

SSAS Deploy Cube

A window will pop up and provide the details for any errors encountered or it will let you know that the Deployment was Successful.

SSAS Deploy Pop Up

Right click the Project name again and select Process to finish the Cube.

SSAS Process Cube

A pop up window will tell you how the Process is progressing and let you know once it has completed.

SSAS Progress Process Window

To Confirm that Deployment and Processing was successful, a connection to Analysis Services may be opened in SSMS and then select an MDX query to test the new Cube. This query provides the Sales data for the Product Categories in the cube.

MDX Query of Cube Data

To change the data format in the cube, go back to the Cube Structure and click on the Measure (Sales Amount in this case) and select Format String from the Properties section and change to Currency.

Sales Amount Cube FormattingFormat String in Cube SSAS

After Deploying and Processing the cube again, we can rerun the query to see that the sales amounts are now formatted as Currency.

MDX Query of Cube Formatted Data