MSDN Documentation

Creating Cubes in Analysis Services Multidimensional Modeling

This section guides you through the process of creating and configuring cubes within SQL Server Analysis Services (SSAS) using the multidimensional modeling approach. Cubes are the core data structures that enable OLAP (Online Analytical Processing) queries and provide a multidimensional view of your data.

Understanding Cube Components

Before creating a cube, it's essential to understand its fundamental components:

• Dimensions: Provide the context for analyzing data (e.g., Time, Geography, Product).
• Measures: Represent the quantifiable data you want to analyze (e.g., Sales Amount, Quantity Sold).
• Facts: The underlying data table in your data source containing the measures.
• Hierarchies: Structured levels within a dimension that allow for drill-down and roll-up operations.
• Kpis: Key Performance Indicators that measure business success.

Steps to Create a Cube

1. Launch SQL Server Data Tools (SSDT)

Open SSDT and connect to your Analysis Services instance. Create a new Analysis Services Project or open an existing one.

2. Create a Data Source View

A Data Source View (DSV) acts as an abstraction layer over your relational data source. It allows you to define relationships, rename tables and columns, and create calculated columns or measures.

1. Right-click on "Data Source Views" in Solution Explorer and select "New Data Source View".
2. Follow the wizard to connect to your data source and select the tables and views relevant to your cube.
3. Configure relationships between tables if they are not automatically detected.

3. Create a Cube

Once you have a DSV, you can create a cube.

1. Right-click on "Cubes" in Solution Explorer and select "New Cube".
2. The Cube Wizard will launch. Choose "Use existing tables" and select the fact table and related dimension tables identified in your DSV.
3. Select the measures you want to include in the cube.
4. Select the dimensions you want to include and specify how they relate to the fact table.
5. Review the generated cube structure and click "Finish".

4. Configure Cube Properties

After the cube is created, you can refine its properties in the Cube Designer.

• Dimensions Tab: Add, remove, or configure dimensions and their attributes. Define attribute relationships, hierarchies, and default members.
• Measures Tab: Add, remove, or configure measures. Set aggregation methods (e.g., Sum, Count, Average) and formatting.
• Calculations Tab: Create calculated measures and named sets using MDX (Multidimensional Expressions).
• Perspectives Tab: Define specific views of the cube for different user groups.
• Translations Tab: Provide translated names and descriptions for cube objects.

5. Define Hierarchies and Attributes

Within the Dimensions tab of the Cube Designer, you can further refine your dimensions:

• Attribute Hierarchy: By default, attributes can form a hierarchy.
• User Hierarchy: Create custom hierarchies that allow users to navigate data at different levels of granularity.
• Attribute Properties: Configure properties like IsAggregatable, AttributeHierarchyEnabled, and AttributeHierarchyVisible.

6. Create Calculated Measures and Named Sets

The Calculations tab allows you to extend the cube's analytical capabilities:

Calculated measures perform calculations based on existing measures and other data. Named sets group members from one or more dimension hierarchies into a predefined set.

Use MDX syntax to define these objects:

-- Example of a calculated measure
CREATE MEMBER CURRENTCUBE.[Measures].[Profit Margin] AS
    ([Measures].[Sales Amount] - [Measures].[Cost Amount]) / [Measures].[Sales Amount] ,
FORMAT_STRING = '0.00%';

7. Deploy and Process the Cube

Once the cube is designed, you need to deploy it to the Analysis Services server and then process it to load data.

1. Right-click on the Analysis Services Project in Solution Explorer and select "Deploy".
2. After deployment, right-click on the cube and select "Process".
3. Choose the processing option (e.g., "Full" or "Incremental") and click "OK".

8. Query the Cube

After processing, you can connect to the cube from client applications (like Excel, Power BI, or custom applications) using tools like SQL Server Management Studio (SSMS) or a BI client and query the data using MDX or DAX.

Best Practices

• Star Schema: Design your data source with a star schema (fact table surrounded by dimension tables) for optimal performance.
• Meaningful Naming: Use clear and consistent names for tables, columns, measures, and dimensions.
• Attribute Relationships: Define attribute relationships correctly within dimensions to ensure proper aggregation.
• User Hierarchies: Create intuitive user hierarchies that match business reporting needs.
• Performance Tuning: Regularly monitor and tune cube performance by analyzing query plans and optimizing aggregations.