SQL Server Analysis Services

Creating Cubes

A cube is a fundamental data structure in SQL Server Analysis Services (SSAS) Multidimensional models. It provides a multidimensional view of data, allowing users to analyze information from various perspectives. This section guides you through the process of creating a cube and configuring its basic properties.

Understanding Cube Components

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

• Dimensions: These define the perspectives or attributes through which you analyze data (e.g., Time, Geography, Products).
• Measures: These are the quantitative values you want to analyze (e.g., Sales Amount, Quantity Sold).
• Measure Groups: Collections of related measures, typically derived from the same fact table.
• Attributes: Properties within dimensions that provide detailed information.
• Hierarchies: Ordered sets of attributes within a dimension, enabling drill-down and roll-up analysis.

Steps to Create a Cube

You can create a cube using SQL Server Data Tools (SSDT) or by using scripting languages like XMLA or AMO.

Using SQL Server Data Tools (SSDT)

1. Open Your SSAS Project: Launch SSDT and open your existing SQL Server Analysis Services project. If you don't have one, create a new Analysis Services Project.
2. Add a New Cube: In Solution Explorer, right-click on the Cubes folder and select New Cube.
3. Cube Wizard: The Cube Wizard will launch, guiding you through the creation process.
   • Select Creation Method: Choose to create a cube from an existing data source view.
   • Select Tables: Select the fact table(s) that will provide the measures and the dimension tables that will define the cube's structure.
   • Select Measures: Choose the measures from your selected fact table(s) that you want to include in the cube.
   • Select Dimensions: Select the dimensions to be included in the cube. SSDT will automatically identify potential dimensions based on your table selections.
   • Finish: Review your selections and click Finish.
4. Cube Designer: Once the wizard completes, the Cube Designer will open, showing your newly created cube. You can now further customize its properties, add calculations, and define security.

Configuring Cube Properties

After creating the cube, you can refine its behavior and appearance in the Cube Designer:

• General Tab: Set the cube's name, description, and other general properties.
• Dimensions Tab: View and manage the dimensions associated with the cube. You can change dimension usage (e.g., Regular, Fact, Degenerate) and configure attribute relationships.
• Measure Groups Tab: Organize and manage measure groups. You can define aggregation settings, processing options, and storage modes.
• Calculations Tab: Create calculated members and KPIs using MDX (Multidimensional Expressions).
• Perspectives Tab: Define perspectives, which are subsets of a cube that provide a tailored view for specific user groups.
• Actions Tab: Define actions that users can perform on cube data, such as drilling through to transaction details.

Example: Creating a Simple Sales Cube

Let's say you have a 'Sales' fact table with 'Amount', 'Quantity' and related dimension tables like 'Product', 'Date', and 'Customer'.

1. Start the Cube Wizard.
2. Select the 'Sales' table as the fact table.
3. Choose 'Amount' and 'Quantity' as measures.
4. Select 'Product', 'Date', and 'Customer' as dimensions.
5. SSDT will generate a basic cube.
6. In the Cube Designer, navigate to the Dimensions tab. You might want to link 'Date' to the 'OrderDateKey' in the 'Sales' table.
7. Go to the Measure Groups tab to configure aggregation designs for 'Amount' and 'Quantity'.

Next Steps

Once your cube is created and configured, the next steps typically involve:

• Deploying the Cube: Deploy your SSAS project to the Analysis Services server.
• Browsing the Cube: Use client tools like Excel or Power BI to connect to and explore your cube.
• Further Refinements: Add calculated members, KPIs, and implement security roles.