Create Cubes

This document guides you through the process of creating a cube in SQL Server Analysis Services (SSAS) Multidimensional modeling. Cubes are the central data structures in SSAS, enabling multidimensional analysis of your business data.

Understanding Cubes

A cube is a data structure that allows users to analyze large amounts of data from multiple perspectives. It consists of:

• Dimensions: These represent the business perspectives or contexts for analysis (e.g., Time, Geography, Product).
• Measures: These are the numerical values that users want to analyze (e.g., Sales Amount, Quantity Sold).
• Hierarchies: Within dimensions, hierarchies provide drill-down and roll-up capabilities (e.g., Year > Quarter > Month > Day).

Steps to Create a Cube

1. Using SQL Server Data Tools (SSDT)

The primary tool for creating and managing SSAS cubes is SQL Server Data Tools (SSDT). If you haven't already, download and install SSDT for Visual Studio.

2. Creating a New Analysis Services Project

Open Visual Studio and create a new project. Select the Analysis Services Project template under the Business Intelligence or Analysis Services categories.

3. Adding Data Sources

In your Analysis Services project, you'll need to define one or more data sources that will provide the data for your cube. This typically involves connecting to an existing relational database (e.g., SQL Server, Oracle).

1. Right-click on the Data Sources folder in the Solution Explorer.
2. Select Add Data Source.
3. Configure the connection string and authentication details for your data source.

4. Creating a Data Source View

A Data Source View (DSV) is a unified representation of the data from one or more data sources. It acts as the semantic layer for your Analysis Services models.

1. Right-click on the Data Source Views folder.
2. Select Add Data Source View.
3. Select your defined data source(s).
4. Choose the tables and views from your relational database that you want to include in the DSV.
5. You can define relationships between tables directly in the DSV designer.

5. Creating a Cube

This is the core step where you define the cube structure.

1. Right-click on the Cubes folder.
2. Select Add Cube.
3. The Cube Wizard will launch. You can choose to create a cube from scratch or use a predefined cube template. For most scenarios, creating from scratch is recommended for full control.
4. Select Tables: Choose the tables from your Data Source View that will form the basis of your cube. Typically, you'll select fact tables (containing measures) and dimension tables.
5. Select Measures: The wizard will suggest measures based on the fact table. You can select which measures to include and configure their aggregation properties.
6. Select Dimensions: The wizard will also suggest dimensions based on related tables in your DSV. You can select which dimensions to include.
7. Finalize Cube: Review the summary and click Finish.

6. Configuring the Cube Designer

Once the cube is created, you can use the Cube Designer to further refine its structure.

• Dimensions Tab: Define and configure hierarchies within your dimensions. You can create parent-child hierarchies, user-defined hierarchies, and more.
• Measures Tab: Add, remove, and configure measures. Set aggregation types (e.g., Sum, Count, Average) and format strings.
• Kpis Tab: Define Key Performance Indicators (KPIs) for your cube.
• Calculations Tab: Create calculated members and measures using MDX (Multidimensional Expressions).
• Layout Tab: Arrange dimensions and measures logically.

7. Deploying the Cube

After designing your cube, you need to deploy it to an Analysis Services instance.

1. Right-click on the project in Solution Explorer.
2. Select Deploy.
3. Configure the Deployment Target to specify the Analysis Services server and database name.

8. Processing the Cube

Deployment creates the cube structure, but it needs to be processed to load data from the underlying data sources.

1. Right-click on the cube in Solution Explorer.
2. Select Process.
3. Choose the processing option (e.g., Process Full, Process Default).
4. Click Run.

Next Steps

With your cube created, deployed, and processed, you can now connect to it using client reporting tools like Power BI, Excel, or Reporting Services to perform sophisticated business analysis.