Module 6: SSAS Cube Design

Introduction

• SSAS (SQL Server Analysis Services): Tool used to develop multi-dimensional cubes for analytics.
• Cube: Multi-dimensional object, unlike a two-dimensional table, used for advanced analytics.
• Purpose: To enable clients and end-users to make better decisions by providing accurate and comprehensive information.
• Multi-Dimensional Structure: Provides optimal performance for large datasets compared to traditional table models.

Key Topics Covered

1. BI Semantic Model: Why We Use It

   • Ensures clients have correct information for decision-making.
   • Two main solutions: Table solution and Multi-Dimensional solution.
   • Challenge: Handling large datasets efficiently without performance issues.
   • Solution: SSAS’s OLAP (Online Analytical Processing) model for optimal performance.

2. Classification of Users

   • End-users who analyze data independently.
   • Teams or groups needing a unified data source.
   • Importance of a single source of truth to avoid conflicts.

3. Dimension and Fact Tables

   • Dimension Tables: Have surrogate keys (e.g., dim customer, dim product, dim date).
   • Fact Tables: Have measures (e.g., sales amount, order quantity).
   • Combination: Dimension tables combined with fact tables create a cube.

4. Basics of Cube Construction

   • Data Source: Define and connect to data sources (e.g., SQL Server 2008 R2).
   • Data Source Views: Views pointing to base tables for cube creation.
   • Use of Wizards: Tools to guide through cube creation steps.
   • Impersonation Information: Defines how SSAS connects to data sources.

5. Building the Cube

   • Measure Groups: Fact tables in the OLAP cube.
   • Dimensions: Attributes and keys from dimension tables.
   • Deploying and Processing the Cube: Essential for aggregating and storing data efficiently.

6. Using the Cube

   • Browsing the Cube: Interactive exploration for analysis (e.g., slicing data by different dimensions).
   • SSAS Functionality: Supports high-level analysis and decision-making.

Practical Steps

1. Creating a Data Source

   • Connect to server and select database.
   • Define impersonation information for authentication.

2. Creating Data Source Views

   • Select relevant tables and establish relationships using matching columns.
   • Handle primary and foreign key relations logically.

3. Generating the Cube

   • Select measure group tables (fact tables).
   • Identify and configure measures (attributes of fact tables).
   • Define dimensions (attributes from dimension tables).

4. Deploying and Processing the Cube

   • Configure deployment properties.
   • Deploy to specified server and process for aggregations.

5. Exploring the Cube

   • Use SSAS browser to drag and drop dimensions and measures for analysis.
   • Make adjustments based on user requirements for different views and data representations.

Advanced Considerations

• Handling Large Data Sets: Importance of efficient data processing and cubic models to manage performance issues as data size grows.
• Data Aggregations: SSAS provides support for various aggregations (e.g., sum, average, count) enhancing decision-making capabilities.
• User Queries and Interactivity: Ease of generating complex reports dynamically without extensive SQL queries.

Summary

• Core Concept: SSAS cubes offer a robust solution for multi-dimensional analysis, providing optimal performance for large datasets and enabling better decision-making for users through easy-to-access, accurate data.
• Practicality: Building, deploying, and using SSAS cubes involve clear steps, from setting up data sources to configuring user-friendly interactive browser tools.