This section covers common tasks performed when working with multidimensional models in SQL Server Analysis Services (SSAS). These tasks are essential for designing, developing, and managing OLAP cubes.
Learn how to create the fundamental building blocks of your multidimensional models.
Define the connection to your relational data source and create a logical view of the data that Analysis Services will use.
Key Concepts: Connection Strings, Schemas, Tables, Views, Relationships.
Related Tasks:
Structure your business entities (e.g., Customers, Products, Time) into dimensions for analysis.
Key Concepts: Attributes, Hierarchies, Levels, Granularity.
Related Tasks:
Define the quantitative data points (measures) that users will analyze and organize them into measure groups.
Key Concepts: Aggregation Functions (SUM, COUNT, AVG), Calculated Measures, Semi-Additive Measures.
Related Tasks:
Assemble your dimensions and measure groups into a functional OLAP cube.
Key Concepts: Cube Structure, Dimension Usage, Cube Properties.
Related Tasks:
Learn how to integrate your SSAS project into a production environment.
Deploy your multidimensional model from SQL Server Data Tools (SSDT) to an Analysis Services instance.
Key Concepts: Deployment Wizard, Configuration Files, Target Server.
Related Tasks:
Update your existing multidimensional model in response to changing business requirements.
Related Tasks:
Ensure your data is up-to-date by processing your Analysis Services objects.
Understand the different processing modes and how to update dimensions, measure groups, and partitions.
Key Concepts: Full Process, Process Default, Process Incremental, Process Recalc.
Related Tasks:
Automate your processing tasks using SQL Server Agent.
Related Tasks:
Retrieve data from your multidimensional models using MDX or DAX.
Learn the Multidimensional Expressions (MDX) language for querying cubes.
Key Concepts: SELECT Statement, Tuples, Sets, Functions.
Example MDX Query:
SELECT
{[Measures].[Sales Amount]} ON COLUMNS,
{[DimProduct].[Category].[Category].MEMBERS} ON ROWS
FROM [AdventureWorksDW]
WHERE ([DimDate].[CalendarYear].[CalendarYear].&[2003])
Related Tasks:
While primarily for Tabular models, DAX can also be used in certain scenarios with multidimensional models, especially for report building tools that abstract the query language.
Related Tasks:
Maximize the efficiency and responsiveness of your multidimensional models.
Divide large cubes into smaller, manageable partitions for improved query performance and easier management.
Related Tasks:
Pre-calculate summarized data to speed up common queries.
Related Tasks:
Understand how SSAS uses internal structures for performance and how to leverage caching.
Related Tasks:
Control access to your multidimensional models and their data.
Define roles and assign permissions to users and groups to control access to cubes, dimensions, and measures.
Key Concepts: Role-Based Security, Cell Security, Row Security.
Related Tasks: