Contents

1 [[ 3.1 HOURS ]] SSAS Complete Tutorial - End to End - SQL Server Analysis Service - Analytics with Nags

1.1 #00:00 - Topics

Two Flavours

SSAS Tabular
SSAS Multi-dimensional

This video exclusively for SSAS Tabular only

1.2 #04:20 - Introduction

SSAS - SQL Server Analysis Services - Tabular Model

SQL Server Data Tools - used to develop Tabular module project and deploy to another server

Dictionary Encoding

Create Dictionary to fit into RAM

RLE = Row Length Encoding
Power BI uses data from SSAS Tabular
SSAS Tabular Modal - either premises or Azure

1.3 #13:30 - SSAS Tabular Installation

If already SQL Server Installed, go to Start > Programs > SQL Server Installation Center

In my system already SSAS Multidimentional Installed -

Custom Installation -

Database Engine Services

Analyis Services

Either Mutidimentional or Tubular - choose in next screen

Install DataTools

We can isntall from Visual Studio 2019 or we can download the file and install

Visual studio - SSDT

Services.msc - check SSMS, SSAS softwares available

Open Visual Studio - Start new project without template - Click Installed / Tabular or Multidiemntional

1.3.1 Software Cross Check

1.4 #39:16 - Data Modeling

1.4.1 #What is Star Schema

OLTP - Running the system - Model will be ER model

OLAP - Star Schema or Dimensional Modeling - for reporting - instead of insert selecting. Insert will be off office hours

How many subject areas do we have that much star schema will be.

Data Mart contains Fact table and related objects

One Fact table surrounded by multiple Dimention tables

Assign Keys in Dimension Table - Fact table will refer the key values from Dimention table

From Entity Relationship DB, we convert into Star Schema for OLAP

For example - AdventureWorks2014 is the source db for AdventureWorksDW2014. We transform the AdventureWorks2014 OLTP data to AdventureWorksDW2014 OLAP by using SSIS.

Each subject area will have one fact table

Surrogate key is alternate key for one table

1.5 #01:03:05 - Create Tabular Model

1.5.1 Format Measure / Columns

Pre requisites

Crate Star schema ready
Required software to be installed

Create SSAS Tabular and deploy into another server
Crate Cubes

Open Visual Studio 2017 SSDT - New Project - Analysis Services - Tabular - Analysis Services Tabular Project

Project opened with Solution Explorer and Tabular Model Explorer (Model.bim)

Rt Click Data Sources - Add data source

User Service account to connect the OLTP - Get the service account info from services.mci - the same should be added in SQL Server Login - Update the User role

Once datasource configured, it waill ask to select data - we can select the tables and related tables button to load all the tables

View - Grid View, Diagram View

DAX - as like as excel formula

Once developed in VS SQL Server Data Tool - Build then deploy

Watch from 1:25

Connect from excel - it connect as Pivot Table

1.6 #01:34:56 - What is DAX (Data Analysis Expressions)

How to format Measures OR Columns
Format (number, decimal, date, etc) we can apply in columns or in calculated fields
In Visual Studio SSDT Tool - F4 - Properties windows

1.6.1 Measure

DAX / Calculated Column / Measure in SSAS

DAX = Compute the dynamically created column

DAX = Data Analysis Expressions

New Product is calculated news column

Measures = summarizes the data into single value - this is not stored - happening dynamically

We can create Calculated Table by using DAX

In Visual Studio - Tabular Model Explorer - Click the next sheet icon in the canvass view,

We can duplicate one dim table and update the column as per our need

1.6.2 Calculated Column

1.6.3 Calculated table

1.7 #01:55:29 - Import Additional Data

How to Add New Tables in SSAS Tabular

If there is no related table in the database - we can create here
In VS SDT (SQL Server Data Tools) - From Data source - select the DB - Import Tables - Choose the required tables -
Remove unwanted columns in Fact or Dim table

At the time of importing, we can use select statement to reduce unwanted columns
After Imported, after some period if we decided to remove unwanted columns,

Delete the unwanted columns from the Canvas OR
Select the Table, Properties - Select or unselect the columns

1.8 #02:05:48 - Deployment and Processing

VSS SSDT

Develop the SSAS package in SQL Server Data Tools - then deploy into another server.
From VS EDE changes on tables to reflect the Database - we need to build and deploy

Processing Option- Default / Do Not Process / Full

Do not process means dont take the data from source and refresh once deployed

Using initial development time, we choose “ Do Not Process” -

Full - Full process will recreate everything.

1.9 #02:15:30 - Schedule Refresh SSAS Tabular

WE build Cubes from DW and deployed in SSAS Tabular Server - once deployed the SSAS will take the data from DW and loading SSAS is called Processing
In SSIS we have Analysis Services Processing Task
SSIS - Analysis Services Processing Task - Provide Server Details of Analysis Service - Initial catelog - the DW Database Name - Will display all the Dim and fact tables - Select Process Options

This SSIS package we can schedule by SQL Agent Jobs
From SSIS we can deploy from VS SSDT
Create a Job

Job Name / Steps / Run as ServiceAgent Account - Schedule -

1.10 #02:25:45 - Consume / Read SSAS Tabular

SSMS / Excel / Power BI

SSMS

Query - Similar to Select * from employee, in SSAS Query window , For MDX query

evaluate <FactInternetSales>

Evaluate filter(DimProduct, DimProduct[Language]=”English”)

Excel

Data - Get Data - From Database - From Analysis Service - Provide the Server credentials - Choose the Database - Select the Data Model Cube

All Fact and Dim tables will be displayed in Pivote Table - we can select

Power BI

Open Power BI - Get Data - Provide Analysis Server credentials - can choose Import or Connect live
Choose Visualizations - choose the fields the report will be ready

1.11 #02:35:15 - Perspective in SSAS Tabular Model

Perspective - Like OLTP View, we can connect multiple tables and provide module exclusive for that requirement

Structural changes we need to deploy then it will effect

1.12 #02:40:52 - Direct Query Mode in SSAS

Load the data from DW to SSAS Tabular - If we use Direct query then we no need to create a package using VS SSDTtool
Calculated Tables are not supported Direct Query
docs.microsoft.com/en-us/analysis-services/
Open VS SSDT - Open the SSAS Project -

DirectQueryMode is one of the properties in SSAS Project

1.13 #02:54:52 - Row Level Security (RLS)

VS SSDT - SSAS Project - Add Role
Need to add Members in Members tab
Deploy the project

MyNotes

KPI - Key Performance Indicators

In SQL Server Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. Typically, these calculations are a combination of Multidimensional Expressions (MDX) expressions or calculated members.

Poov KB

Search This Blog

Thursday, March 14, 2024

PL_SSAS Complete Tutorial -By Analytics with Nags