This page has 0 threads | Add post

Courseware

Introduction to SSAS course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Analysis Services courses).

Click here to download a sample of the first two chapters of this Introduction to SSAS manual, or review how to license our courseware.

Chapter 1 - OVERVIEW OF SSAS TABULAR▼

Section	Title	Subsections	Page
1.1	What Tabular Models Do	Database Level Model Level View Level	8
1.2	Deployed Tabular Models		10
1.3	Compatibility Levels	Viewing the Compatibility Level of your Model	11

Chapter 2 - CREATING PROJECTS IN VISUAL STUDIO▼

Section	Title	Subsections	Page
2.1	Ensuring Analysis Services is Running		12
2.2	Running Visual Studio	Running Visual Studio Changing Start-up Options Changing Analysis Services Settings Installing the SSAS Extension	13
2.3	Projects and Solutions	Creating a New Project Files and Folders Created Having Multiple Projects in a Solution	16
2.4	Visual Studio Windows	Moving and Auto-Hiding Windows The Three Main Windows to Show	18

Chapter 3 - GETTING STARTED▼

Section	Title	Subsections	Page
3.1	Working with Models	Renaming Models	19
3.2	Adding Data		20
3.3	Workspace Databases	Using a Workspace Database Using an Integrated Workspace	21
3.4	Deploying your Database	Checking the Production Server Used Deploying a Model Viewing Deployment Progress Viewing the Deployed Database	23
3.5	Viewing your Model’s Data	Analysing a Model in Excel Browsing a Model in SSMS Creating a Pivot Table Based on a Model	26
3.6	Generating Measures	Using Generated Measures	29

Chapter 4 - IMPORTING DATA▼

Section	Title	Subsections	Page
4.1	Our Example Database		30
4.2	Loading Data	Step 1 – Choosing to Import Data Step 2 – Choosing your Data Format Step 3 – Choosing the Data Source Step 4 – Passing Security Step 5 – Encryption Support Warning Step 6 – Choose your Tables	31
4.3	Existing Connections		34
4.4	Importing from Excel		35
4.5	Importing CSV or Text Files		36

Chapter 5 - CREATING DATA MODELS▼

Section	Title	Subsections	Page
5.1	Data and Diagram View		37
5.2	Relationships	Viewing Relationships Editing Relationships Deleting Relationships Creating Relationships Managing Relationships	38
5.3	Hiding Columns and Tables	The Need to Hide Columns/Tables Hiding Tables Hiding Columns Choosing not to Display Hidden Columns and Tables	41
5.4	Renaming Tables and Columns	Renaming Columns Renaming Tables	43
5.5	Changing what you’ve Imported for a Table	Deleting Columns from a Model Adding Columns to a Model, or Changing Filtering	44

Chapter 6 - TRANSFORMING DATA▼

Section	Title	Subsections	Page
6.1	Transforming Data during Loading		45
6.2	Changing Data after Loading		46
6.3	Working with Queries	The Power Query Editor Default Query Steps Viewing Data at Different Steps Editing a Query Step Deleting a Query Step Adding a New Step Applying Query Changes The Advanced Editor	47
6.4	Transforming Data	Changing Data Types Renaming Columns Removing Columns Removing or Keeping Rows Sorting and Filtering Rows Splitting Columns Replacing Values	51
6.5	Creating New Columns	Creating a Formula Creating a Column by Example	54
6.6	Conditional Columns		56

Chapter 7 - EXCEL PIVOT TABLES▼

Section	Title	Subsections	Page
7.1	Creating a Data Model		57
7.2	Creating a Pivot Table		57
7.3	Drill Down		59
7.4	Working with Pivot Tables	Renaming Fields Refreshing Changes to the Model Changing Number Formatting Moving Rows and Columns Sorting Pivot Tables	60
7.5	Filtering		63
7.6	Slicers	Creating Slicers Removing Slicers (or their Filters) Changing the Number of Slicer Columns Changing the Style of a Slicer	64
7.7	Controlling Multiple Pivot Tables with Slicers	First Rename your Pivot Table Getting a Slicer to Control Multiple Pivot Tables An Alternative Method – Telling a Pivot Table which Slicers Control it	66
7.8	Formatting Pivot Tables		68
7.9	Choosing what to Display	Hiding Field Captions Inserting Blank Rows Subtotals and Totals Changing Report Layout Repeating Item Labels	69

Chapter 8 - POWER BI REPORTS▼

Section	Title	Page
8.1	Overview of Power BI and SSAS	71
8.2	Importing SSAS Data	72
8.3	Linking to an SSAS Model	73

Chapter 9 - CALCULATED COLUMNS▼

Section	Title	Subsections	Page
9.1	What are Calculated Columns?	Differences between Calculated Columns and Excel Formulae	74
9.2	Creating Calculated Columns	An Alternative Method, using the Keyboard	75
9.3	Testing Conditions using IF		76
9.4	Editing DAX Formulae	Multi-Line Formulae Using the Tab Key Comments The DAX Editor Adding Functions	76
9.5	Working with Calculated Columns	Moving Calculated Columns Sorting by Calculated Columns	79
9.6	Using RELATED to Link Tables		81
9.7	The BLANK Function	Blank Arithmetic Using Variables to Improve Formulae	82
9.8	Aggregator Columns and SWITCH	The SWITCH Function	84
9.9	Error-Trapping		85

Chapter 10 - MEASURES▼

Section	Title	Subsections	Page
10.1	Calculated Columns vs. Measures	Difference 1 - Calculated Columns are Row-Based Difference 2 - Measures are Aggregated Difference 3 - Pivot Table Placement	86
10.2	Creating Measures	Step 1 – Choosing a Host Table Step 2 – Choosing a Cell Step 3 – Creating the Measure Step 4 – Naming your Measure Step 5 - Changing the Default Formatting Step 6 - Applying Filters to Test your Measure Step 7 - Testing your Measure in Excel	88
10.3	Using a Separate Measure Table	Creating a Separate Table Adding Measures	92
10.4	Filter Context		93

Chapter 11 - MEASURE SYNTAX▼

Section	Title	Subsections	Page
11.1	Basic Syntax Rules	Data Types Symbols you can Use	94
11.2	Aggregation Functions	Aggregating a Column Aggregating an Expression (X-Suffix Functions)	95
11.3	Calculating Ratios	Counting Rows using the COUNTROWS Function Creating Ratios: the Fields Needed The Final Pivot Table Summing Ratios wouldn’t Work	97

Chapter 12 - THE CALCULATE FUNCTION▼

Section	Title	Subsections	Page
12.1	Syntax of the CALCULATE Function		99
12.2	Removing Filters Using ALL	Removing a Single Filter Removing Multiple Filters Removing All Filters Bar One	100
12.3	Replacing Individual Filters	Replacing a Single Filter Replacing More than One Filter	102
12.4	Using VALUES to Edit a Constraint	On its own, VALUES is pointless Amending the Filter Context Filter	102

Chapter 13 - THE FILTER FUNCTION▼

Section	Title	Subsections	Page
13.1	Syntax of the FILTER Function		104
13.2	Filtering to Show Subsets of Data		105
13.3	Removing Filters using ALL	Removing and Partially Reinstating Filters	106

Chapter 14 - THE EARLIER FUNCTION▼

Section	Title	Subsections	Page
14.1	Overview of the EARLIER Function	Our Three Worked Examples What the EARLIER Function Does	107
14.2	Case Study: Running Totals		108
14.3	Case Study: Ranking using EARLIER		109
14.4	Case Study: Group Averages		110

Chapter 15 - BANDING▼

Section	Title	Subsections	Page
15.1	What is Banding?	Creating and Loading a Banding Table	111
15.2	Creating a Banding Formula		112
15.3	Sorting the Bands		113

Chapter 16 - RANKING▼

Section	Title	Subsections	Page
16.1	The RANKX Function	Syntax of the Rank Function Entering RANKX in a Calculated Column	114
16.2	Using RANKX in Measures	Step 1 – Creating the Measure to Rank Step 2 – using ALL	114
16.3	Changing the Sort Order		116

Chapter 17 - CALENDARS▼

Section	Title	Subsections	Page
17.1	The Need for a Separate Date Table	The Excel Approach – Date Functions The Tabular Approach – Create a Separate Table	117
17.2	Creating your Dates Table	Creating a Calendar Table in Excel Creating a Calendar Table in SQL Server	117
17.3	Creating Relationships	Creating Relationships Using Integer Date Keys	119
17.4	Announcing your Date Table to the World		121
17.5	Using Calendars in Pivot Tables	Sorting Months (and Other Fields)	122
17.6	Managing Date Granularity		123
17.7	Timelines		124
17.8	Creating New Aggregator Columns	Incorporating a Bank Holiday Table Creating a Calculated Column for Working Days	125

Chapter 18 - MULTIPLE CALENDAR TABLES▼

Section	Title	Subsections	Page
18.1	The Problem, and Two Solutions	Repeat the Table or the Relationship?	127
18.2	Solution One: Duplicate the Calendar Table	Importing and Linking to the Calendar Tables Creating the Pivot Table	128
18.3	Solution Two: Duplicate the Relationship	Creating the Duplicate Relationships The CALCULATETABLE Function The USERELATIONSHIP Function Our Measures	130

Chapter 19 - DATE FUNCTIONS▼

Section	Title	Subsections	Page
19.1	Contents		133
19.2	Period to Date	Using DATESYTD (and how Date Functions Work) TOTALYTD, TOTALQTD and TOTALMTD Changing the Financial Year End	134
19.3	Comparing with Previous Periods	The SAMEPERIODLASTYEAR Function Using DATEADD to Compare with any Previous Period	136
19.4	Parallel Periods		137
19.5	Moving Averages	Definition of a Moving Average Moving Average using DATESINPERIOD and LASTDATE Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE	138
19.6	Semi-Additive Measures	Useful Semi-Additive Functions Using the FIRSTDATE and LASTDATE Functions Using FIRSTNONBLANK and LASTNONBLANK Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK	140

Chapter 20 - KEY PERFORMANCE INDICATORS (KPIS)▼

Section	Title	Subsections	Page
20.1	About KPIs	Three Parts of a KPI	142
20.2	Creating a KPI	Step 1 – Create the Measures Needed Step 2 – Starting to Create the KPI Step 3 – Customising your KPI Step 4 – Displaying the KPI in Excel	143
20.3	Absolute Targets		146

Chapter 21 - HIERARCHIES▼

Section	Title	Subsections	Page
21.1	Using Hierarchies	Advantages and Disadvantages of Hierarchies	147
21.2	Creating Hierarchies	Step 1 – Getting the Correct Fields in a Single Table Step 2 – Creating the Hierarchy Step 3 – Including Fields in your Hierarchy Step 4 – Using a Hierarchy	148

Chapter 22 - PROTOTYPING IN POWERPIVOT▼

Section	Title	Subsections	Page
22.1	Enabling PowerPivot		150
22.2	Using PowerPivot		151
22.3	Summary of Differences between PowerPivot and SSAS Tabular	Difference 1: Pivot Table Field List Difference 2: Implicit Measures Difference 3: Creating Measures in the Client Difference 4: Linked Excel Tables	152
22.4	Uploading PowerPivot Models into Tabular	Importing a PowerPivot Model	155

Chapter 23 - PERSPECTIVES▼

Section	Title	Subsections	Page
23.1	What are Perspectives?		157
23.2	Creating Perspectives		158
23.3	Using Perspectives	Switching Perspectives in a Model Choosing a Perspective when Analysing Data in Excel Choosing Perspectives for Data Connections Changing the Perspective for an Existing Pivot Table	159

Chapter 24 - SECURITY▼

Section	Title	Subsections	Page
24.1	Overview of Security	Types of Role in SSAS Tabular Levels of Database Access	161
24.2	Server Administrators		162
24.3	Managing Roles	Managing Roles in Visual Studio Managing Roles in Management Studio	163
24.4	Controlling Data Access (Row Filters)	Row Filters in Visual Studio Row Filters in SSMS	165
24.5	Two Ways to Test Row Filters	Testing using Excel Testing using Connection Strings	167
24.6	Creating a Permissions Table		168
24.7	Dynamic Security	Two DAX Functions Needed Using CustomData for Dynamic Security Incorporating a Test for the User Name	169

Chapter 25 - DAX STUDIO▼

Section	Title	Subsections	Page
25.1	Using DAX Studio	Installing DAX Studio Connecting to your Model Refreshing your Model	171
25.2	Five Uses of DAX Studio	Use 1 - Getting at DAX Functions Use 2 – Writing DAX Queries Use 3 – Better Formatting Use 4 – Saving DAX Use 5 – Getting at Internal Data	172

Chapter 26 - DAX QUERIES▼

Section	Title	Subsections	Page
26.1	Overview of DAX Queries	DAX and MDX Creating a DAX Query in SQL Server Management Studio	174
26.2	SQL versus DAX Querying		175
26.3	Listing Tables (EVALUATE)	Listing All Rows Ordering Rows Starting at a Particular Row Returning N Rows Only Sampling Rows	176
26.4	Summarising	Summarisation Syntax Examples of Summarisation Using ROLLUP to get All Combinations	178
26.5	Filtering in DAX Queries	Example: Summarising Sales for Birds Only Example: Summarising Centre Size for Selected Towns	180
26.6	Adding Columns	Example: Counting Rows Example: Transaction Statistics by Town Defining Measures First	181
26.7	Combining Table Results	GENERATE and GENERATEALL	183
26.8	Other Useful Functions	Using ROW to Display One Row of Data Using CONTAINS to Check if a Field Value Exists	184

Chapter 27 - PROCESSING▼

Section	Title	Subsections	Page
27.1	Column Storage	Row versus Column Storage Data Compression	185
27.2	Ways to Speed up Processing	Sort Tables First Omit High-Cardinality Columns where Possible Avoid Calculated Columns Avoid Expensive Queries Use Server Timings Don’t Store DateTime Columns Consider Splitting Id Columns	186
27.3	Processing Concepts	What Processing Involves (Data/Metadata) Two Stages of Processing Types of Processing The Most Efficient Strategy	190
27.4	Processing in Visual Studio	Processing an Individual Table Processing All Tables for a Given Connection Processing an Entire Model Changing Processing Options for Deployment	192
27.5	Processing in SSMS	Processing an Entire Database Processing Tables	194

Chapter 28 - PARTITIONS▼

Section	Title	Subsections	Page
28.1	Partitions		195
28.2	Partitions in Visual Studio	Creating Partitions in Visual Studio Processing Partitions in Visual Studio	196
28.3	Partitions in SSMS	Processing Partitions in SSMS	198

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

SSAS course manuals