Analysis Model
using SSAS
Lecture Three
Ejada Internal Use Only By Eng. Ahmed Abdelhakim
Agenda
1) Lecture Objectives
2) Semantic Model / Analysis Layer / Self-Reporting Layer
3) SSAS Overview
4) SSAS Tabular Model Elements
5) Points of Focus
6) Summary & Resources
2
Ejada Internal Use Only
Lecture Objectives
By the end of the lecture, you will be able to:
✓ Understand What is Semantic Model and Benefits of using it
✓ Understanding SSAS, Tool Capability, and Key Features
✓ Identify SSAS Model Types and Differences between them
✓ Identify the Full Workflow including the Analysis Services
3
Ejada Internal Use Only
Semantic (Analysis) Model
A semantic model is a logical layer that contains transformations, calculations, and
relationships between data source objects, in a single place.
Advantages of including such layer in the workflow are:
❖ Single source of truth for reports across an organization that ensure business
analysts have the same definition, Fixed Standards, and context on the data.
❖ Can be consumed by different users for reporting and analysis purposes.
❖ Enables Self-Service Reporting for Non-Technical Users.
❖ Provide Significant Improvement in Performance, Compression, and Security.
4
Ejada Internal Use Only
SSAS Overview
Microsoft SQL Server Analysis Services (SSAS) is an analytical data engine (VertiPaq)
used in decision support and business analytics.
It provides enterprise-grade semantic data models for business reports and applications
such as Power BI, Excel, Reporting Services reports, and other data visualization tools
SSAS Some Key Features:
❖ Ease of use Tool with Drag and Drop GUI
❖ Same Data Preparation, Modeling, and Languages as Power BI
❖ Supports Tabular and Multidimensional Model
❖ Authorization Support (i.e., Roles, Column and Row Level Security)
❖ Integrate Well With Other Microsoft BI Tools such as SSIS and Power BI
Ejada Internal Use Only
SSAS Model Types
Tabular Multidimensional
• Newer Technology • Matured Product
• Stores Data in Memory • Stores Data in Disk
• Easier and Faster • Harder and Slower
• Uses Advanced Compression Techniques • Uses MDX as Formula Language
• Uses DAX as Formula Language
• Enable Self-Service BI
• Promising with New In-Memory Databases
❖ Tabular is the default and recommended mode for new projects by Microsoft due to its Advantages
Ejada Internal Use Only
Tabular Model Elements
➢ Data Sources and Tables:
Stores The Data Warehouse Connections and The Selected Tables from it, In addition of
Derived Tables called Calculated Tables.
➢ Measures and Calculation Groups:
Measures are calculations used to perform complex calculations or aggregations on
data, Created Using Dax, while Calculation Groups reduce the number of measures by
grouping every set of relative measures derived from base measure.
➢ KPIs :
A KPI is used to gauge performance of a value, defined by a Base measure, against a
Target value, also defined by a measure or by an absolute value
➢ Translations:
- Can Support multi-language versions within the model definition
Ejada Internal Use Only
Perspectives and Roles
❖ Perspectives:
Perspectives define viewable subsets of a model that provide focused, business-specific, or
application-specific viewpoints of the model,Equivalent To Data Marts.
i.e., E-Commerce System Possible Prespectives are [Sales, Purchasing and Inventory]
❖ Roles:
Roles Define member permissions for a model, Members assigned to each role can have some
restrictions specified by role definition and security constraints
i.e., in the Same E-Commerce Example:
Sales Staff can only see Sales Prespective Data (Table-Level Security)
Sales Managers can see extra columns than Sales Staff Like Salary (Column-Level Security)
Every Manager can only observe sales of his related region (Row-Level Security
Ejada Internal Use Only
Semantic Layer Workflow
Fetch The Data Data Modeling Security Layer
Connect To The Data • Create Measures, KPIs and Define Roles and Table,
Warehouse and Specify Calculation Groups Column, and Row Level
The Required Tables • Create and Validate Security and Map
Relationships Between Tables Members to Roles
• Specify Different Perspectives
Consume Model Deployment Translations
Through data Deploy The Model with Support Multi-Language
visualizations, BI Scheduling and [Model – Table] for the Model
dashboards and reports, Level Processing Strategy
By using specific
Prespective and Role
Ejada Internal Use Only
Points of Focus
❖ Calculation Groups, KPIs
❖ Assign Members to Different Roles with Security Constraints
❖ Working with Translations
❖ Detail Rows Expressions in Measures (Drill through for Details)
❖ Different Types of Processing (Full, Default ,etc.)
❖ Deployment Methods and Processing Script
❖ Schedule Processing with SQL Agent Server
Ejada Internal Use Only
Summary & Resources
❑ Semantic Model (Analysis Layer) is a logical layer that contains transformations, calculations,
and relationships in a single source of truth.
❑ SSAS (Part of SSDT Family) is an analytic modeling tool with powerful engine for building
semantic models for business reports and dashboards, providing performance and security.
❑ Advantages of including such Layer including Single source of truth, Build model once for
many consumptions, Performance, Security, Decoupling, and Translations.
Topic Resources
Building Tabular Models in SSAS | Pluralsight (AZURE Dev. Tools free 30-Day)
SSAS Tutorials [[ 3.1 HOURS ]] SSAS Complete Tutorial - End to End - SQL Server Analysis Service
Ejada Internal Use Only
Thank You
Ejada Systems Company Limited شركة إجادة للنظم المحدودة
[Link] info@[Link]
[Link] | info@[Link]
Ejada Internal Use Only