How to Create a Cube in Analysis Services

This topic provides step-by-step instructions for creating a new cube in Microsoft SQL Server Analysis Services (SSAS) using SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).

Prerequisites

Before you begin, ensure you have the following:

• SQL Server Analysis Services installed.
• A multidimensional SSAS project created in SQL Server Data Tools, or an existing SSAS database.
• A data source view that contains the tables and relationships you intend to use for your cube.

Steps to Create a Cube

Using SQL Server Data Tools (SSDT)

1. Open your SSAS project: Launch SQL Server Data Tools and open your existing Analysis Services multidimensional project.
2. Add a New Cube:
   • In Solution Explorer, right-click the Cubes folder.
   • Select New Cube....
3. Cube Wizard: The Cube Wizard will launch.
   • Click Next.
   • Select Creation Method: Choose "Use a wizard to create a cube". Click Next.
   • Select Data Source View: Select the data source view that contains the tables required for your cube. Click Next.
   • Select Tables:
     • Select the tables that will serve as fact tables for your cube.
     • For each fact table, select the related dimension tables.
     • SSDT will suggest dimensions based on existing relationships. Review and adjust as needed.
     
   • Select Measures:
     • For each selected fact table, choose the measures you want to include.
     • You can choose to automatically create measures from measure groups or manually select individual columns.
   • Create the Cube: Review the summary of your cube's structure. Provide a name for your cube. Click Finish.
4. Configure the Cube: After the wizard completes, the Cube Designer will open. Here you can:
   • Dimensions: Add, remove, or configure dimensions.
   • Measures: Add, remove, or modify measures.
   • Kpis: Define Key Performance Indicators.
   • Calculations: Create calculated members and measures.
   • Perspectives: Define different views of the cube.
   • Translations: Manage translations for cube elements.
5. Deploy and Process:
   • Save your project.
   • Right-click the SSAS project in Solution Explorer and select Deploy.
   • After successful deployment, right-click the SSAS project again and select Process to load data into the cube.

Using SQL Server Management Studio (SSMS)

While SSMS is primarily for managing existing SSAS objects, you can also create new cubes through its interface if you are connected to a running SSAS instance.

1. Connect to Analysis Services: Open SSMS and connect to your Analysis Services instance.
2. Navigate to Databases: Expand the Databases node in Object Explorer.
3. Right-click an existing database: Right-click on the SSAS database where you want to create the cube, then select New Database if creating a new one, or select the existing database.
4. Add New Cube:
   • Right-click the Cubes folder within your database.
   • Select New Cube....
5. Cube Wizard: The Cube Wizard will appear. Follow similar steps as described in the SSDT section (Steps 3a-3g) to select data sources, tables, measures, and define the cube structure.
6. Deploy and Process (if applicable): If you are creating the cube within a project context managed by SSMS or a separate deployment process, ensure the cube is deployed and processed.

Key Concepts

• Cube: A multidimensional data structure that allows for fast analysis of data.
• Dimension: Provides context for measures (e.g., Time, Geography, Product).
• Measure: The numerical data you want to analyze (e.g., Sales Amount, Quantity).
• Measure Group: A collection of related measures, typically derived from a single fact table.
• Data Source View (DSV): A logical representation of your data sources, defining tables, views, and relationships.

By following these steps, you can successfully create a cube in Analysis Services, enabling powerful analytical capabilities for your business intelligence solutions.