Business Intelligence courseware and training manuals

We have a wide range of courses for software in the Microsoft BI stack, and hence a wide range of BI stack course manuals:

You can see details of how to license our Business Intelligence courseware (including volume requirements) here.

Integration Services courseware

Here is a list of all of our Integration Services manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introducing SSIS (2 pages) Overview of SSIS SSIS Packages and Solution Explorer ; Control Flow ; Data Flow
Using Visual Studio (4 pages) Starting Visual Studio Visual Studio and SQL Server Data Tools ; Running Visual Studio
Creating a Project
Visual Studio Windows The Solution Explorer, Properties and SSIS Toolbox Windows ; Floating Windows ; Auto-hiding Windows
Packages (7 pages)

Download this chapter 		Working with Packages Creating a Package ; Renaming Packages ; Opening or Editing Packages ; Viewing a Package’s XML ; Saving a Package ; Closing Individual Packages ; Closing Multiple Packages ; Copying Packages
The Package Tabs
Running a Package Executing a Single Package ; Making a Package the Start-up Object and Debugging a Project ; Monitoring Package Execution ; Using the Progress Window
Stopping Running a Package
Tasks (8 pages) Working with Tasks Adding a Task ; Renaming a Task ; Editing a Task ; Executing an Individual Task ; Deleting and Suspending (Disabling) Tasks
Working with Task Diagrams Selecting Tasks ; Moving Tasks ; Auto-layout of Diagrams ; Zooming In and Out
Annotations
Grouping Tasks Grouping Tasks ; Grouping using Sequence Containers
Data Flow (6 pages) Introducing Data Flow Sources, Transforms and Destinations ; This and Subsequent Chapters
Data Flow Tasks Creating Data Flow Tasks ; Switching to Data Flow Tasks
Connections Types of Connection ; Project-Level and Package-Level Connections ; Creating Connections in Advance ; Creating Connections as Part of Sources/Destinations
Creating Our Package
The Advanced Editor
Working with SQL Server (6 pages) Creating a SQL Server Connection
Creating a SQL Server Source/Destination Step 1 – Creating the SQL Server Source/Destination ; Step 2 – Choosing the Table or Query ; Step 3 – Mapping Columns
Creating SQL Server Tables
Execute SQL Tasks Step 1 - Adding an Execute SQL Task ; Step 2 – Assign a Connection ; Step 3 – Enter a SQL Statement ; Step 4 – Optionally, Parse your SQL Command
Working with Flat Files (11 pages) Examples for this Chapter
Existing Flat File - Source Connecting to a Flat File ; Configuring Rows and Columns ; Creating the Flat File Source
Existing Flat File – Destination
Creating New Flat Files Step 1 – Creating a Destination ; Step 2 – Starting a New Connection ; Step 3 – Choose a File Format ; Step 4 – Browsing to a Folder ; Step 5 – Choosing a File Name ; Step 6 – Configuring the New File (Rows) ; Step 7 – Configure the New File (Columns) ; Step 8 – Configuring Mappings ; Step 9 – Running your Package
Using Multiple Flat Files Our Example ; Adding the MultiFlatFile Connector ; Using a MultiFlatFile Connector
Working with Excel (5 pages) Excel Workbooks – Overview The Example Used in this Chapter ; What Can Go Wrong
Importing/Exporting using Excel Step 1 – Creating an Excel Connection ; Step 2 – Creating a Source or Destination ; Step 3 – Mapping Columns
The 32-Bit Problem The Problem ; The Solution
Data Viewers (2 pages) Using Data Viewers
Creating Data Viewers The Data Viewer Buffer
Transforms (7 pages) Adding Transforms
Sorting / Getting Unique Rows
Sampling The Sampling Transforms ; Incorporating Sampling Transforms into Data Flow
Combining and Splitting Data Introduction to the Transforms ; An Example using Multicast and Union All Transforms ; Configuring Union All Transforms
Aggregating Data
Data Types (3 pages) The Data Types in SSIS The Three Data Type Families in SSIS ; Data Types Listed in this Chapter
SSIS Data Types String Data Types ; Integer Data Types ; Boolean or Logical Data Types ; Non-Integer Data Types ; Date/time Data Types ; Time Data Types
Data Conversion Transforms (4 pages) Overview of Data Conversion
Creating Data Conversion Transforms Our Example: Importing Excel Unicode Data to Varchar Columns ; Adding a Data Conversion Transform ; Configuring a Data Conversion Transform ; Mapping Data Conversion Transform Columns ; The Complete Package
Variables (4 pages) Overview of Variables Data Types of Variables
Working with Variables The Variables Window ; Creating a Variable ; Deleting Variables ; Changing the Scope of Variables ; Choosing What to See
User and System Variables
Using Variables (5 pages) Example for this Chapter Creating the Variables Needed
Creating Row Count Transforms Adding a Row Count Transform
Creating Expression Tasks
Debugging Setting and Removing Breakpoints ; Debugging using Breakpoints
Derived Column Transforms (8 pages) Overview of Derived Column Transforms Our Example – Calculating Film Length Statistics ; Adding a Derived Column Transform
Simple Column Expressions
Concatenating, Casting and Conditions Simple Concatenation Won’t Work ; Casting ; The Ternary or Conditional Operator ; The Double = Sign When Testing Conditions ; The Relational Operators Allowed
More Examples and Syntax Example of a Mathematical Function ; Dealing with Nulls ; Example of a String Function
SSIS Functions (5 pages) Working with Strings of Text Functions to Find and Replace Text ; Getting Extracts from a String of Text ; Other Text Functions ; New Line and Other Special Characters
Date Functions The DATEPART Function ; Using DATEADD to Add Dates ; Using DATEDIFF to Take the Difference between Two Dates
Casting Data
Error-Handling (3 pages) Our Example
Configuring Error-Handling
Tracking Error Rows
Conditional Splits (4 pages) Our Example
Creating a Conditional Split Beginning a Conditional Split ; The Operators Allowed ; Typing Conditions ; Spotting Errors (Red Text)
Directing Conditional Split Output
Lookup Transforms (12 pages) Our Example
Creating a Lookup Transform Step 1 – Creating the Transform ; Step 2 – Choose the Lookup Table ; Step 3 – Redirect Non-Matching Rows ; Step 4 – Choosing a Cache Mode ; Step 5 – Choosing Columns to Output ; Step 6 – Choosing Columns to Capture
Dealing with Matched Rows
Dealing with Unmatched Rows Strategy 1 – Store Unmatched Rows for Inspection ; Strategy 2 – Add Unmatched Shop Names as Unknown ; Strategy 3 – Add Unmatched Rows to the Lookup Table
Caching Lookup Tables Benefits of using Cache Transforms ; Our Example – Looking Up Property Types ; Filling the Cache ; Using the Cache
File System Tasks (4 pages) Adding File System Tasks
Working with Files Creating a File Connection
Working with Folders Creating a Folder Connection ; Case Study: Creating a Folder
Precedence Constraints (4 pages) Simple Constraints
Expression Constraints Automatically Annotating Expression Constraints
Combining Constraints
Looping Over Files (11 pages) Our Example
Setting up the Package Emptying the SQL Server Table ; Creating a String Variable to Hold Each File
Looping Over Files Step 1 – Adding the Foreach Loop Container Task ; Step 2 – Configuring the Loop Task ; Step 3 – Capturing the File Path in a Variable ; Step 4 – Checking the Package Works
Omitting Certain Files
The Data Import Task The Steps to Follow ; Step 1 – Create an Excel Connection ; Step 2 – Make this Excel Connection Dynamic ; Step 3 – Initialise the File Path Variable Value ; Step 4 – Configure and Finish the Data Flow Task
Running the Package
Looping over ADO Rows (12 pages) How ADO Enumerators Work
Generating Multiple Files from a Single Table The Details of this Example ; Summary of the Steps to Follow ; Step 1 – Creating the Variables ; Step 2 – Create an Execute SQL Task to get Unique Shop Names ; Step 3 – Store the Results Set in the Object Variable ; Step 4 – Create the ADO Enumerator Loop ; Step 5 – Configure the ADO Enumerator Loop ; Step 6 – Add a Fixed Data Flow Task ; Step 7 – Make the Flat File Connection Dynamic ; Step 8 – Run your Package
Importing a Workbook’s Worksheets Creating the Variable ; Create the Outline Package ; The Foreach Loop Type – Creating a Connection ; Choosing to Return the Workbook’s Worksheet Names ; Creating the Data Flow Task (Source) ; Adding a Data Flow Destination ; Running the Package
SQL Parameters (5 pages) Passing Parameters by Position Our Example ; Creating the Variables ; Creating the Loop over Files ; Creating the Execute SQL Task using Parameters ; The Parameter Mapping Choices ; Running the Package
Passing Parameters by Name Creating the Stored Procedure ; Changing the Execute SQL Task
Merge Joins (5 pages) What Merge Joins Do
How Merge Joins Work
Creating the Package Step 1 – Loading the Data ; Step 2 – Sorting the Data ; Step 3 – Merging the Data ; Step 4 – Picking out the Differences ; Step 5 – Finishing the Package
Deployment (4 pages)

Download this chapter 		Overview of Deployment
Preparing to Deploy Creating a Catalog ; Creating Folders
Deploying Projects and Packages Starting Project Deployment ; Finishing Deployment ; Deploying Individual Packages
Executing Deployed Packages (6 pages) Executing Reports from the SSMS Menu
Viewing Reports Viewing Package Reports ; Viewing Catalog Reports
Executing Packages from SQL
Scheduling Packages Step 1 - Run SQL Server Agent ; Step 2 – Add a Job ; Step 3 – Adding Steps to the Job ; Step 4 – Choosing a Schedule ; Step 5 – Test your Job
Parameters (7 pages) Overview of Parameters Our Example ; Variables and Parameters Needed
Creating Parameters Creating Project-Level Parameters ; Creating Package-Level Parameters
Setting up the Example The Basic Control Flow Tasks ; Starting to Configure the Loop over Files ; Making the Loop Folder Dynamic ; The Data Flow Task Excel Source ; Finishing the Data Flow Task ; Running the Package
Deploying Packages using Parameters Deploying Package-Level Parameters ; Deploying Project-Level Parameters
Running Deployed Packages with Parameters

Reporting Services courseware

Here is a list of all of our Reporting Services manual chapters (you can download the ones marked):

Chapter Sections Subsections
Getting Started (7 pages) What is Reporting Services?
Visual Studio and SQL Server Data Tools A Brief History of Version Numbers ; Installing SQL Server Data Tools
Report Server Projects Opening Visual Studio ; Creating a New Project ; Closing a Project ; Opening an Existing Project ; Using the Start Page
The Visual Studio Screen Auto-Hiding Windows ; Opening and Closing Windows ; The Main Windows ; Repositioning Windows ; Resetting the Window Layout
Working with Reports (5 pages) Creating Reports
Report Views Design View and Report Preview ; Code View
Managing Reports Opening and Closing Reports ; Saving Reports ; Renaming Reports ; Deleting and Removing Reports ; Importing Reports
Report Templates Creating a Report Template ; Using a Report Template
Basic Report Items (7 pages) Report Items Adding Items to a Report
Selecting Report Items Selecting Single Report Items ; Selecting Multiple Items
Manipulating Report Items Moving Report Items ; Resizing Items ; Using the Properties Window ; Deleting Items ; Aligning Items ; Distributing Items
Images, Rectangles and Textboxes Images ; Rectangles ; Textboxes
Formatting (6 pages)

Download this chapter 		Three Ways to Format an Item Toolbars ; The Properties Window ; The Properties Dialog Box
Changing Colours Choosing Basic Colours ; Custom Colours ; Colour Gradients and Patterns
Formatting Text Formatting All or Part of a Textbox ; Basic Font Formatting Options ; Text Alignment
Formatting Borders The Borders Toolbar ; The Borders Dialog
Formatting Numbers and Dates Using Built-In Formats ; Creating Custom Formats
Getting Data (8 pages) Data Sources and Datasets The Wise Owl Movies Database
Data Sources Shared and Embedded Data Sources ; Creating a Shared Data Source ; Using a Shared Data Source ; Creating an Embedded Data Source
Datasets Shared and Embedded Datasets ; Creating a Shared Dataset ; Using a Shared Dataset ; Creating an Embedded Dataset
The Query Designer Building a Query
Editing Data Sources and Datasets Editing Data Sources ; Editing a Dataset
Basic Tables (12 pages) Creating Tables Inserting a Table ; Tables and Datasets ; Using the Field Selector ; Dragging Fields into a Table
Views of a Table Tables in Design View ; Tables in Report View
Selecting in Tables Selecting the Table ; Selecting Cells
Table Properties The Tablix Properties Dialog Box ; The Text Box Properties Dialog Box
Rows and Columns Selecting Rows and Columns ; Changing Column Widths ; Changing Row Heights ; Preventing Row Height Changes ; Inserting and Deleting Columns ; Inserting and Deleting Rows
Merging and Splitting Cells Merging Cells ; Splitting Cells
Controlling Column Headers How Not to Make Column Headers Repeat ; Repeating Column Headers on Each Page ; Making Column Headers Scroll
Sorting and Filtering (6 pages) Sorting Sorting in the Query of a Dataset ; Sorting in a Table ; Sorting in a Group ; Interactive Sorting
Filtering Filtering in the Query of a Dataset ; Filtering in the Dataset Properties Dialog Box ; Filtering a Table ; Filtering in a Group
Grouping in Tables (11 pages) Grouping Basics
Grouping Rows in a Table Step 1 – Build a Basic Table ; Step 2 – Apply Grouping to the Detail Row ; Step 3 – Choose Which Field to Group By ; The End Result
Removing Groups Deleting Groups in the Grouping Pane ; Deleting Groups in a Table
Formatting a Grouped Table Adding Group Headers and Footers ; Rearranging Group Titles ; Adding Group Totals
Multiple Groups Adding a New Parent Group ; Adding a Child Group
Page Breaks Inserting Page Breaks between Groups
Collapsible Groups Step 1 - Hiding Detail Rows ; Step 2 - Setting Toggle Items
Expressions (17 pages) Overview of Expressions Choosing to Create an Expression ; The Expression Builder Dialog Box ; Referring to Dataset Fields ; Referring to Report Items ; Inserting Functions
Ad-Hoc Table Columns Creating an Ad-Hoc Column Expression
Calculated Fields Adding a Calculated Field to a Dataset ; Using a Calculated Field ; Adding Calculated Fields to a Query
Working with Numbers Basic Arithmetic ; Numeric Functions
Conditional Functions The IIf Function ; The Switch Function ; Comparison Operators ; Logical Operators
Working with Text Concatenating Text ; Text Functions
Working with Dates Returning the Current Date ; Date Functions ; Intervals for Date Functions ; Formatting Dates
Aggregating Data Aggregate Functions ; Adding Aggregates the Quick Way ; Changing the Aggregate Function ; Specifying the Scope
Lookup Functions The Lookup Function ; The LookupSet Function
Built-In Fields Built-In Fields in the Expression Builder ; Built-In Fields in the Report Data Window
Placeholders Creating a Placeholder
Dynamic Formatting (5 pages) Formatting with Expressions Which Properties can be Calculated?
Calculating Colours Using Named Colours ; Using Custom Colours ; Converting RGB to Hexadecimal Values
Calculating Font Properties Dynamic Font Sizes ; Decorating Text ; Other Font Properties
Dynamically Hiding Report Items The Hidden Property ; Calculating the Visibility of Report Items
Printing and Exporting (7 pages) Printing Reports Print Layout View ; Choosing to Print a Report
Page Setup Page Setup Options in Preview ; Page Setup Options in Design View ; Controlling the Page Size
Headers and Footers Adding and Removing Headers and Footers ; Adding Items to Headers and Footers ; Expressions for Headers and Footers ; Controlling Page Numbers in Groups
Exporting Reports Choosing to Export a Report ; Creating Worksheet Names in Excel
Testing the Render Format Displaying a Title Page in Excel Only ; A List of Render Formats
Parameters (17 pages) Introduction to Parameters Displaying the Parameters Pane
Report Parameters Creating a Report Parameter ; Using a Report Parameter in a Filter ; Parameter Data Types ; Dealing with No Rows ; Displaying Parameter Values in the Report
Query Parameters Writing a Query using Parameters
Organising Parameters Changing the Order of Parameters ; Using the Parameters Pane
Default Values Typing in a Default Value ; Calculating Default Values ; Getting Default Values from a Dataset
Null Values Allowing Null Values ; Dealing with Nulls in Filters
Drop Down Lists Manually Entering Available Values ; Using a Dataset to Populate a List
Multi-Value Drop Down Lists Allowing Multiple Values ; Using Multiple Values in Filters ; Using Multiple Values in Queries ; Displaying Multiple Values in a Report
Cascading Drop Down Lists Creating Cascading Drop Down Lists ; Using Cascading Drop Down Lists
Conditional Formatting with Parameters Parameters in Formatting Expressions
Indicators (6 pages) Creating Indicators Adding an Indicator to a Table ; Assigning a Field to an Indicator
Editing Indicators Changing the Indicator ; Changing the Indicator’s Field ; Changing the Aggregate Function
Formatting Indicators Changing Indicator Colours and Icons ; Formatting the Background of an Indicator
Indicator Size and Position Controlling the Size of an Indicator ; Preventing an Indicator from Stretching
Scaling and Measurement Units Changing the Scope of Scaling ; Changing the Measurement Units ; Adding and Removing Bands
Gauges (12 pages) What are Gauges? Anatomy of a Gauge
Creating Gauges Inserting a Gauge into a Table ; Assigning a Field to a Gauge
Editing Gauges Selecting Part of a Gauge ; Adding and Deleting Items in a Gauge
Editing a Scale Changing the Limits of the Scale ; Formatting Tick Marks ; Changing the Scale’s Dimensions
Editing a Pointer Changing the Pointer Type ; Setting the Pointer Size and Position ; Changing the Pointer Fill Colour
Editing a Range Setting the Start and End Position ; Changing the Size and Position ; Changing the Fill Colour
Editing the Gauge Choosing a Frame ; Formatting the Frame ; Setting the Size and Position
Editing a Label Setting the Value Displayed ; Anchoring a Label
Multiple Items Multiple Pointers ; Multiple Scales ; Multiple Gauges ; Creating Child Gauges
Charts (10 pages) Creating Charts Inserting a Chart into a Report ; Inserting a Chart into a Tablix Item ; Assigning Fields to a Chart ; Previewing a Chart
Editing Charts Parts of a Chart ; Changing Chart Properties ; Adding and Removing Elements in a Chart
Chart Colours Choosing a Colour Palette ; Creating a Custom Palette ; Calculating Colours ; Synchronising Colours in a Table
Using Multiple Value Fields Adding Multiple Value Fields to a Chart ; Multiple Value Axes ; Using Different Chart Types
Sparklines Adding a Sparkline to a Report or Tablix Item ; Editing Sparklines
Databars Adding a Data Bar to a Report or Tablix Item ; Editing Data Bars
Matrices (7 pages) Overview of Matrices Adding a Matrix to a Report ; Assigning Fields to a Matrix
Editing a Matrix Changing the Aggregate Function ; Replacing Empty Cells ; Displaying Row and Column Totals
Sorting a Matrix Sorting by Row and Column Fields ; Sorting by Values
Matrix Headers and Titles Inserting the Extra Row ; Controlling the Height of the Header Row
Repeating and Scrolling Headers Making Row and Column Headers Repeat
Multiple Grouping Levels Creating Multiple Grouping Levels
Lists (4 pages) Introduction to Lists What a List Really Is
Creating a List Inserting a List into a Report ; Adding Fields to a List ; Adding Labels to Fields
Grouped Lists Adding a Group to a List
Subreports (6 pages) An Overview of Subreports
Creating the Child Report Step 1 - Create the Dataset ; Step 2 - Create a Parameter ; Step 3 - Filter the Dataset ; Step 4 - Design the Report
Creating the Parent Report Step 1 - Create the Dataset ; Step 2 - Design the Report
Combining the Parent and Child Reports Step 1 - Insert a Subreport into the Parent Report ; Step 2 – Assign a Report to the Subreport ; Step 3 - Pass a Value to the Child Report’s Parameter ; Running the Report
Drillthrough Reports (6 pages)

Download this chapter 		Overview of Drillthrough Reports The Example for this Chapter
Creating the Child Report Step 1 - Create the Dataset ; Step 2 - Hide the Parameter ; Step 3 - Design the Report
Creating the Parent Report Step 1 - Create the Dataset ; Step 2 - Design the Report
Creating Report Actions Step 1 - View the Action Properties ; Step 2 - Configure the Action to Open a Report
Using Drillthrough Reports Running the Parent Report ; Returning to the Parent Report
Multiple Parameters Creating Child Reports with Multiple Parameters ; Creating the Parent Report
Maps (8 pages) Maps in SSRS Map Data
Creating a Map Report Creating a Dataset ; Inserting a Map into a Report
The Map Wizard Step 1 – Choosing a Map Source ; Step 2 – Cropping and Resizing the Map ; Step 3 – Choosing a Type of Visualisation ; Step 4 – Selecting a Dataset ; Step 5 – Linking the Dataset to the Map ; Step 6 – Choosing Which Field to Display
Editing Maps Map Options and Properties ; Map Layers
Colour Rules Controlling Colour Bands ; Types of Colour Rules
Deploying Report Projects (4 pages) What is Deployment?
Preparing for Deployment Finding the URL of the Report Server ; Setting the Target Server for a Report Project ; Choosing Whether to Overwrite Data Sources
Deploying the Project Deploying a Report Project
The Reporting Services Web Portal (13 pages) Overview of the Web Portal
Getting to the Web Portal Finding the Web Portal URL ; Browsing to the Web Portal
Using the Web Portal The Web Portal Menu ; Viewing Item Options ; Finding Items ; Using Favourites
Working with Reports Viewing and Exporting Reports ; Downloading Reports ; Moving and Deleting Reports ; Managing Reports
Report Builder Editing Reports in Report Builder ; Viewing Windows in Report Builder ; Inserting New Items in Report Builder ; Saving the Report
Uploading Items Uploading Files in the Web Portal
Security in Reporting Services Users, Groups and Roles ; Assigning Users to System-Level Roles ; Assigning Users to Item-Level Roles ; Creating New Roles
Managing Data Sources and Datasets (7 pages) Deployed Data Sources and Datasets Viewing Shared Data Sources and Datasets
Managing Data Sources Editing Data Sources ; Creating a New Data Source
Data Source Credentials Viewing Credential Options ; Storing Credentials in a Data Source ; Prompting for Credentials
Managing Datasets Creating a New Dataset ; Viewing a Dataset’s Data ; Editing a Dataset
Schedules, Snapshots and Subscriptions (11 pages) Alternative Ways to Run Reports
Schedules Running the SQL Server Agent ; Managing Shared Schedules ; Configuring a Schedule
Caching Reports and Datasets Enabling Caching ; Cache Expiration Settings ; Cache Refresh Plans ; Setting Parameter Values ; Running a Report from a Cache Snapshot
Report History Snapshots Creating a History Snapshot Manually ; Creating Scheduled History Snapshots ; Retaining History Snapshots
Subscriptions Types of Subscription ; Creating a Subscription ; Using a Windows File Share Destination ; Using an Email Destination ; Assigning Parameter Values ; Managing Subscriptions
KPIs and Mobile Reports (10 pages) Other Types of Report
KPIs An Example KPI ; Creating a KPI Dataset ; Creating a KPI ; Setting the Value Field ; Setting the Goal Field ; Setting the Status Field ; Setting the Trend Set Field ; Related Content
Mobile Reports Choosing to Create a Mobile Report ; Adding and Editing Visualisations ; Formatting Visualisations ; Viewing a Visualisation’s Data ; Adding Data to a Mobile Report ; Changing Report Settings ; Saving a Mobile Report

SSAS - Multi-dimensional courseware

Here is a list of all of our SSAS - Multi-dimensional manual chapters (you can download the ones marked):

Chapter Sections Subsections
Multidimensional BISM (6 pages) Types of Data Storage Structures Relational Databases ; Multidimensional Model
Comparison of the Two Storage Methods OLTP and OLAP ; A Diagram Showing OLAP versus OLTP
Cubes Cubes as Pivot Tables ; Cubes as SQL Query Results ; Cube Size
Summary of Jargon Dimensions, Measures and Facts
Creating multidimensional projects (6 pages) Your Choice of Project A Summary of the Differences between the Two Models
Data Tools / Visual Studio SQL Server Data Tools and Visual Studio ; Configuring SQL Server Data Tools ; Running Visual Studio
Configuring Visual Studio Changing the Start-Up Options ; Changing how Windows Appear
Creating a Project
Data Sources (2 pages) Creating a New Connection
Connecting to a Data Source
Data Source Views (12 pages) What is a Data Source View?
Creating a Data Source View Choosing Tables ; Filtering Tables ; Choosing Related Tables ; Finishing the Data Source View Wizard
Editing Data Source View Layout Working with Diagrams ; Adding Tables to a Diagram or Data Source View ; Adding Relationships ; Adding Logical Primary Keys ; Changing to Diagonal Layout ; Finding Tables ; Zooming In ; Selecting and Moving Tables ; Showing Tables of Data
Named Queries Named Queries versus Views ; Creating a Named Query
Calculated Columns
Dimensions (18 pages) Our Example
Creating a Dimension Specifying the Dimension Table and Columns ; Finishing the Dimension Wizard
Dimension Attributes Primary Key Attribute ; Non-Key Attributes ; Adding an Attribute after the Wizard has Finished ; Hierarchies
Deploying Dimensions Deploying a Project
Browsing a Dimension
Making Changes to a Dimension Editing a Dimension ; Changing How Attributes are Listed ; Renaming Dimensions and Attributes ; Changing the Column Displayed ; Changing the Sort Order ; Removing Ordering to Speed Up Processing ; Renaming the Top Node of a Dimension ; Renaming the Unknown Value
Displaying Other Columns (AttributeHierarchyEnabled) How to Display Additional Columns ; The Problem - Attribute Hierarchies are Exposed ; Method 1 - Disabling Browsing when Creating a Dimension ; Method 2 - Disable the AttributeHierarchyEnabled Property
Grouping and Banding Using Discretization ; Using your Own Groups
Publishing Dimensions and Cubes (12 pages) The Stages of Publication The Stages (Building, Deployment and Processing)
Building a Project Differences between Build and Rebuild ; Differences between Building a Project and Solution
Deploying a Project Setting the Target Server and Other Properties ; Deploying ; Changing How Deployment Affects Processing
Processing a Dimension or Cube
Browsing a Dimension or Cube
Deployment Warning and Error Messages Viewing Deployment Messages ; Dismissing Specific Warnings ; Dismissing Warnings Permanently
Viewing the Analysis Services Database
Typical Problems (and their Solutions) Do a Full Process on your Dimensions ; Deleting the Analysis Services Database
Cubes (12 pages) Cubes Overview Example Used for this Chapter ; Terms Used for Cubes
Using the Cube Wizard Step 1 – Starting the Wizard ; Step 2 – Choosing Measure Groups ; Step 3 – Choosing Measures ; Step 4 – Choosing Dimensions ; Step 5 – Finishing your Cube
The Cube Designer Displaying Measures in a Grid ; Editing Dimensions from Cube Designer
Deploying and Browsing Cubes Deploying a Cube
Choosing What to Display in a Cube The Parts of the Browser Window ; Showing Cube Data ; Analysing Cube Data in Excel
Filtering a Cube
Star and snowflake schemas (4 pages) Snowflake Schemas An Example of a Snowflake Schema ; How Snowflake Schemas Work ; Advantages and Disadvantages of Snowflake Schemas
Star Schemas Example of a Star Schema ; Retrieving Data in a Star Schema ; Advantages and Disadvantages of Star Schemas ; Characteristics of Star Schema Tables
Hierarchies and relationships (12 pages)

Download this chapter 		Dimension Hierarchies Explained Our Example ; Default Primary Key Attribute Hierarchy ; Natural Hierarchies ; Unnatural or User-Defined Hierarchies
Creating a Hierarchy The Hierarchies Created by Default ; The Hierarchy for this Worked Example ; Step 1 - Adding the Necessary Attributes ; Step 2 - Creating the Hierarchy ; Step 3 - Adding Levels ; Step 4 - Renaming the Hierarchy and its Levels ; Step 5 - Testing your Hierarchy
Using Hierarchies within Cubes Hiding Dimensions (AttributeHierarchyVisible Property)
Creating Attribute Relationships Attribute Relationships Warning Messages for Hierarchies ; The Attribute Relationships Pane ; Creating a Relationship by Drag and Drop ; Creating a Relationship Manually ; Rigid and Flexible Relationships
Hierarchies for a Snowflake Schema Creating our dimension ; Creating Snowflake Schema Hierarchies
Parent-Child Hierarchies (8 pages) What is a Parent-Child Hierarchy?
Creating a Parent-Child Hierarchy Creating a Self-Join on the Trainer Table ; Starting the Dimension Wizard ; Choosing Attributes ; Parent and Key Attributes ; Deploying and Browsing the Parent-Child Dimension
Tidying Up a Parent-Child Dimension Removing the All Level ; Showing Who is Boss ; Hiding the Unknown Member ; Showing Organisational Level Names
Limitations of Parent-Child Hierarchies
Time Dimensions (14 pages) Overview
Issues to Consider for Time Dimensions Three Ways to Create Time Dimensions ; Choice of Primary Key ; Reasons to Use your Own Time Dimension ; SQL Procedure to Generate your Own Time Dimension
Time Dimensions Using Tables you’ve Created Step 1 – Check your Tables ; Step 2 – Create your Data Source ; Step 3 – Start to Create your Dimension ; Step 4 – Choose your Dimension Attributes ; Step 5 – Creating Calendar Hierarchies ; Step 6 – Creating Attribute Relationships ; Step 7 – Renaming Attributes ; Specifying a Dimension as a Time Dimension
Autogenerating Time Dimensions Step 1 – Choosing where to Store your Dimension ; Step 2 – Configuring the Dimension ; Step 3 – Generating the Table ; Step 4 – Viewing the Final Dimension ; Step 5 – Making Changes to the Final Dimension
Measures (6 pages) Working with Measures Choosing Measures within a Cube ; Adding New Measure Groups and Measures ; Renaming Measures and Measure Groups ; Creating Measure Folders
Formatting Measures Syntax of Formatting Strings
Aggregating Measures Basic Aggregations (Sum, Min, Max) ; Counting ; Other Functions
Multiple Fact Tables (6 pages) Our Example
Creating a Cube with Multiple Measure Groups
Unrelated Dimension Settings
Dimension Usage
Creating Measure Groups from Dimension Tables
Advanced Dimensions (4 pages) Role-Playing Dimensions
Referenced Dimensions When to Use a Reference Dimension ; Our Example ; Creating a Reference Dimension
Error Configuration Controlling When and How Dimension Errors are Reported ; Controlling MDX Error Messages
Calculations (6 pages) Our Example The Base Cube
Creating Calculations
Fine-Tuning Calculations Formatting Calculations ; Choosing Display Folders for Calculations ; Non-Empty Behaviour Box ; Conditional Formatting
Calculated Measures using MDX (4 pages) What is MDX? Types of MDX
Viewing Calculations as Script
Typing in MDX Expressions Typing in a Calculation ; Using MDX Calculations in Cubes ; Viewing MDX Script in Form View Again ; Basic Syntax
Writing MDX Queries (12 pages) Our Example Our Example Dimensions
Creating a Basic Query
Axes Number and Order of Axes ; Examples of Queries Using Axes
Using the Query Editor Commenting Queries ; Commenting Out Instructions ; Running Part of a Query
Measures in MDX Queries The Default Measure Aggregation ; Changing the Default Measure and Aggregation ; Displaying Multiple Measures ; What the Simplest Query will Display
Referring to Individual Members Basic Syntax ; Using Names or Keys
Sets of Members Ranges of Members ; Named Sets
Tuples
WHERE Clauses
Examples of MDX Expressions (12 pages) Year to Date How the YTD Function Works ; Pre-Requisite: a Time Dimension of Type Year ; Creating the YTD Measure
Ratios Our Example Hierarchy ; Calculating the Ratio against the Immediate Parent ; Calculating the Ratio against an Ancestor ; Avoiding Top-Level Errors ; Avoiding Divide-By-Zero Errors
Previous Period Comparisons The PrevMember Function ; The ParallelPeriod Function
Ranking An Example of the Rank Function ; ASC/DESC versus BASC/BDESC
Conditional Formatting
Aggregating Members of a Dimension
KPIs (8 pages) What are KPIs? Our Example KPIs
Parts of a KPI Possible Values for Status and Trend
Creating KPIs Creating the Necessary Calculated Members ; Creating a KPI ; Assigning a Value and Goal to the KPI ; Setting the Status Expression ; Setting the Trend Expression ; Browsing your KPIs
Using KPIs in Client Tools
Using KPI Functions
Using Client Software (8 pages) Using Excel as a Client Creating a New Connection to SSAS ; Linking to SSAS Using an Existing Connection ; Creating a Pivot Table in Excel
Using Reporting Services as a Client Creating a Data Source ; Creating a Dataset ; Creating a Report
Cube Actions (6 pages) Types of Action Available URL Actions ; Drill-Through Actions
Adding a URL Action Creating a URL Action ; Decide on the Scope of your Action ; Choosing a Target for your Action ; Applying a Condition ; Setting the URL ; Customising the Caption ; Viewing your Menu
Adding a Drill-Through Action
Mining Models (20 pages) Introduction Our Example Data ; Mining Models and Mining Structures ; Creating a Project and Data Source View
Creating a Mining Model Step 1 – Starting the Mining Structure Wizard ; Step 2 – Choosing Tables ; Step 3 – Choosing the Key, Input and Predictable Columns ; Step 4 – Making Initial Data Type Choices ; Step 5 – Allocating Data between Testing and Validation ; Step 6 – Finishing the Wizard ; Step 6 - Changing Content Types ; Step 7 - Fine-Tuning the Mining Model ; Step 8 – Deploying the Mining Model
Viewing Mining Model Results
Reviewing the Accuracy of the Model Step 1 - Choosing Data for your Accuracy Testing ; Step 2 – Charting the Accuracy of your Model (Lift Charts) ; Viewing Summary Accuracy Figures
Using the Model Results DMX
Adding a Model to an Existing Mining Structure Adding a Model ; Using the Added Clustering Model ; Comparing Clusters Visually ; Looking at Individual Clusters ; Comparing Two Clusters
Data Mining Algorithms (6 pages) Data Mining Algorithms Association Rules Algorithm ; Decision Trees ; Clustering Algorithm ; Neural Networks ; Logistic Regression ; Linear Regression ; Naive Bayesian ; Sequence Clustering Algorithm ; Time Series Algorithm
When to Use Which Algorithm
SSAS 2008 R2 Considerations (2 pages) Browsing Cubes
The Cube Browser LocaleIdentifier Language Problem

SSAS - Tabular model courseware

Here is a list of all of our SSAS - Tabular model manual chapters (you can download the ones marked):

Chapter Sections Subsections
Overview of SSAS Tabular (4 pages) What Tabular Models Do
Deployed Tabular Models
Tabular or Multi-dimensional Models? The Two Types of Model ; Similarities between the Two Model Types ; Why you should Use Tabular Models ; Why you should Use Multi-dimensional Models
Adding a Tabular Model Instance (2 pages) Installing a Tabular Model Instance
Creating Projects in Visual Studio (7 pages) Ensuring Analysis Services is Running
Running Visual Studio Visual Studio Versions and Names ; Running Visual Studio
Configuring Visual Studio Changing Start-up Options ; Changing the Default Server Location
Creating a Project Files and Folders Created ; Projects and Solutions ; Creating Separate Folders for Solutions
Visual Studio Windows Moving and Auto-Hiding Windows ; Solution Explorer and Properties
Models (10 pages) Working with Models Adding Models ; Editing Models
Adding Data
The Workspace Database Changing the Workspace Server
Deploying your Database Checking the Production Server Used ; Deploying a Model ; Viewing Deployment Progress ; Viewing the Deployed Database
Viewing your Model’s Data Analysing a Model in Excel ; Browsing a Model in SSMS ; Creating a Pivot Table Based on a Model
Generating Measures Using Generated Measures
Model tables (15 pages) The Make-a-Mammal Database
Loading Tables Step 1 – Choose to Load Tables ; Step 2 – Connecting to the Database ; Step 3 – Choosing Data Tables ; Step 4 – Loading the Data ; Step 5 – Saving your Model
Importing More Tables using an Existing Connection
Filtering Rows and Columns on Import Ignoring Columns ; Filtering Rows
Data and Diagram View Zooming In and Out in Diagram View
Hiding Columns and Tables The Need to Hide Columns/Tables ; Hiding Tables ; Hiding Columns ; Choosing not to Display Hidden Columns and Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Relationships Editing Relationships ; Creating Relationships ; Editing and Creating Relationships Manually
Pivot Tables in Excel (16 pages) Creating the Model and Measures
Creating a Pivot Table
Drill Down
Quick Explore
Working with Pivot Tables Renaming Fields ; Refreshing Changes to the Model ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Aligning Slicers
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
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
OLAP Tools
Other Data Sources (11 pages) Formats Supported
Starting to Import from Other Sources Using 32-bit Excel or Access Data Sources on 64-bit Computers
Linking to Access
Importing Excel Workbooks
Importing Text or CSV Files
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from Reporting Services
Importing from Analysis Services Importing from Multi-Dimensional Models ; Designing the Cube Query in MDX ; Importing from Tabular Models using MDX ; Importing from Tabular Models using DAX
Calculated Columns (8 pages) What are Calculated Columns? Differences between Calculated Columns and Excel Formulae
Creating Calculated Columns An Alternative Method, using the Keyboard
Working with Calculated Columns Moving Calculated Columns ; Sorting by Calculated Columns
The Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH The IF Function ; The SWITCH Function
Power View Introduction (11 pages) What is Power View?
Enabling the Power View Add-in
Creating a Connection to your Model Step 1 – Starting to Create a Connection ; Saving a Connection for Reuse ; Creating a Power View Report
Basic Visualisations (Tables) Starting a Table ; Adding Fields to a Visualisation ; Removing Fields ; Changing Column Order ; Changing Aggregation Methods ; Sorting a Table or Visualisation ; Showing or Hiding Totals
Selecting, Resizing, Moving and Deleting Resizing Tables and Fields ; Moving Tables and Fields ; Selecting and Deleting Objects
Power View Window Settings Controlling what’s on Screen ; Popping Out Windows
Formatting Tools on the POWER VIEW Ribbon Tab ; Number Formatting
Text Boxes and Images Text Boxes ; Images
Filtering in PowerView (5 pages) Types of Filter in Power View
Slicers
Tiles Types of Tile ; Creating Tiles ; Removing Tiles ; Adding Visualisations to Tiles
Filters Setting Report-Level Filters ; Filters for a Specific Table, Chart or Matrix ; Applying Filters
Matrices and cards (2 pages) About Matrices and Cards Matrices ; Cards and Callouts
Drill-down for Matrices
Charts in PowerView (8 pages) Introduction to Charts
Creating Charts Step 1 – Create a Basic Table ; Step 2 – Make this into a Chart ; Step 3 – Make this a Multiple Chart ; Step 4 – Setting the Title and Legend ; Step 5 – Filtering the Chart
Drill Down in Pie Charts
Scatter and Bubble Charts Scatter Charts ; Colouring and Labelling Scatter Charts ; Bubble Charts ; Playing Bubble or Scatter Charts
Measures (7 pages) Calculated Columns vs. Measures Calculated Columns are Row-Based ; Measures are Aggregated ; Pivot Table Placement ; Note on Differences from PowerPivot
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
Filter Context
DAX Editors (6 pages) Summary of Alternate Editors Shortcomings of the Built-in Editor ; Third-Party Tools Available
DAX Studio Installing DAX Studio ; Connecting to your Model ; Refreshing your Model ; Hints on Using DAX Studio ; Copying Measure Formulae into SSAS
DAX Editor Installing and Running DAX Editor ; Using DAX Editor ; Connecting to the Right Database ; Problems with DAX Editor
Measure Syntax (5 pages) Basic Syntax Rules Data Types ; Symbols you can Use
Aggregation Functions Aggregating a Column ; Aggregating an Expression (X-Suffix Functions)
Calculating Ratios Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios wouldn’t Work
The CALCULATE Function (5 pages) Syntax of the CALCULATE Function
Replacing Individual Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Using ALL Removing a Single Filter ; Removing Multiple Filters ; Removing All Filters bar One
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Filter Context Filter
The FILTER Function (3 pages) Syntax of the FILTER Function
Filtering to Show Subsets of Data
Removing Filters using ALL Removing and Partially Reinstating Filters
The EARLIER Function (4 pages) Overview of the EARLIER Function Our Three Worked Examples ; What the EARLIIER Function Does
Case Study: Running Totals
Case Study: Ranking using EARLIER
Case Study: Group Averages
Banding (3 pages) What is Banding? Creating a Banding Table
Creating a Banding Formula
Sorting the Bands
Ranking (4 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating the Measure to Rank ; Step 2 – using ALL
Customising Ranking Changing the Sort Order ; Dealing with ties ; Hypothetical sorting
Calendars (11 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The Tabular Approach – Create a Separate Table
Creating your Dates Table Creating a Calendar Table in Excel ; Creating a Calendar Table in SQL Server
Creating Relationships Changing Column Data Types in Preparation ; Creating Relationships ; Using Integer Date Keys
Announcing your Date Table to the World
Using Calendars in Pivot Tables Sorting Months (and Other Fields)
Managing Date Granularity
Timelines
Creating New Aggregator Columns A Bad Solution ; The Better Solution: a Bank Holiday Table ; Creating a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Multiple Calendar Tables (4 pages) The Problem, and Two Solutions Repeat the Table or the Relationship?
Solution One: Duplicate the Calendar Table
Solution Two: Duplicate the Relationship The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures
Date Functions (11 pages) Contents
Period to Date Using DATESYTD (and how Date Functions Work) ; TOTALYTD, TOTALQTD and TOTALMTD ; Changing the Financial Year End
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Using PERIOD Tables Step 1 – Create a Period Dimension ; Step 2 – Create the Date Measures ; Step 3 – Create the Necessary Dimension Measure ; Step 4 – Creating your Pivot Table
KPIs (5 pages)

Download this chapter 		About KPIs Three Parts of a KPI
Creating a KPI Step 1 – Create the Calculated Fields Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying the KPI in Excel
Absolute Targets
Hierarchies (9 pages) Using Hierarchies Advantages and Disadvantages of Hierarchies
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
Parent-Child Hierarchies What is a Parent-Child Hierarchy? ; Step 1 – Create a List of Parent Ids (the PATH Function) ; Step 2 – Working out the Path Depth (the PATHLENGTH Function) ; Step 3 - Finding Managers at Each Level (the PATHITEM Function) ; Step 4 – Returning each Manager’s Name (the LOOKUPVALUE Function) ; Step 5 – Creating a Hierarchy ; Step 6 – Dealing with Blanks ; Step 7 – Suppressing Extra Levels (the ISFILTERED Function)
Perspectives (4 pages) What are Perspectives?
Creating Perspectives
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
Prototyping in PowerPivot (8 pages) Enabling PowerPivot
Using PowerPivot
Summary of Differences between PowerPivot and SSAS Tabular Different Pivot Table Field List ; Implicit Measures ; Creating Measures in the Client ; Linked Excel Tables
Uploading PowerPivot Models into Tabular Preparation: SQL Server 2012 Updates ; Preparation: Remove Linked Tables? ; Importing a PowerPivot Model
Using DAX and MDX Queries (12 pages) Comparing DAX and MDX Which Packages Support which Language?
Querying Data in Management Studio Management Studio Queries using MDX ; Management Studio Queries using DAX
Creating SSRS Reports Creating a Shared Data Source and Starting a Dataset ; Querying for SSRS using MDX ; Querying for SSRS Using DAX
DAX (or MDX) in PowerPivot
Integration Services Data Sources Step One – Creating a Data Source and Connection ; Step Two – Choose a Provider ; Step Three – Choosing a Database ; Step Four – Pasting your DAX or MDX Query
DAX and MDX for Excel Step 1 – Starting to Create the Connection ; Step 2 – Finishing the Connection ; Step 3 – Make your Connection use a Query ; Step 4 – Using this Connection ; Step 5 – Setting the Query
Writing DAX Queries (11 pages) DAX Studio
SQL versus DAX Querying
Listing Tables (EVALUATE) Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows
Summarising Summarisation Syntax ; Examples of Summarisation ; Using ROLLUP to get All Combinations
Filtering in DAX Queries Example: Summarising Sales for Birds Only ; Example: Summarising Centre Size for Selected Towns
Adding Columns Example: Counting Rows ; Example: Transaction Statistics by Town ; Defining Measures First
Combining Table Results GENERATE and GENERATEALL
Other Useful Functions Using ROW to Display One Row of Data ; Using CONTAINS to Check if a Field Value Exists
Processing (10 pages) Column Storage Row versus Column Storage ; Data Compression
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
Processing Concepts What Processing Involves (Data/Metadata) ; Two Stages of Processing ; Types of Processing ; The Most Efficient Strategy
Processing in SSDT Processing an Individual Table ; Processing All Tables for a Given Connection ; Processing an Entire Model ; Changing Processing Options for Deployment
Processing in SSMS Processing an Entire Database ; Processing Tables
Partitions (5 pages) Partitions
Partitions in SSDT Processing Partitions in SSDT
Partitions in SSMS Processing Partitions in SSMS
DirectQuery (5 pages) What is DirectQuery? How Normal Tabular Models Work ; How DirectQuery Works
Enabling DirectQuery Limitations of DirectQuery ; Enabling DirectQuery Mode for a Model ; Dealing with Errors ; Working in DirectQuery Mode
Deploying DirectQuery Models The Four Possible Query Modes for Deployment ; Switching Query Mode after Deployment
Security (11 pages) Overview of Security Types of Role in SSAS Tabular ; Levels of Database Access
Server Administrators
Managing Roles Managing Roles in SSDT ; Managing Roles in Management Studio
Controlling Data Access (Row Filters) Row Filters in SSDT ; Row Filters in SSMS
Three Ways to Test Row Filters Testing using Excel ; Testing using Connection Strings ; Testing by Running as a Different User
Creating a Permissions Table
Dynamic Security Two DAX Functions Needed ; Using CustomData for Dynamic Security ; Incorporating a Test for the User Name

Basic Power BI courseware

Here is a list of all of our Basic Power BI manual chapters (you can download the ones marked):

Chapter Sections Subsections
Power BI Desktop Basics (2 pages) What is Power BI Desktop? Downloading Power BI Desktop ; Starting Power BI Desktop ; Signing in to an account
Getting Started (11 pages) Example for this Chapter Showing Keyboard Shortcuts
Working with Files
The Power BI Desktop Screen
Getting Data Choosing the Table of Data to Import
Basic Querying Starting to Query Data ; Removing the Rating Column ; Globally Replacing Text in a Query ; Returning to your Table
Creating Visuals
Formatting Visuals Searching for a Format Property
Filtering your Data
Working with Reports Changing how you View Reports ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages
Importing tables (12 pages) Our Example
Importing from Different Sources Importing from Facebook ; Importing from Excel ; Importing from a CSV or Other Text File ; Importing from SQL Server ; Importing from a Website ; Pasting Data into a Model ; Loading data from Access ; Importing from Analysis Services
Typing in Data
Hiding Columns and Tables Hiding Columns ; Hiding Tables ; Showing or Unhiding All Hidden Columns/Tables
Visualisations (7 pages) Overview of Visualisations
Working with Visualisations Adding a Visualisation ; Editing Visualisations (Fields, Format or Analytics) ; Zooming In and Out (Focus Mode) ; Spotlighting a Visual ; Resizing a Visualisation ; Moving, Selecting and Deleting Visualisations ; Locking Visuals ; Aligning and Distributing Visualisations
Using the Selection Pane Displaying and Using the Selection Pane ; Hiding Visuals ; Renaming Visuals
Visual Interactions (2 pages) Visual Interactions How Visualisations Affect Each Other by Default ; Editing Interactions ; What the Different Settings Mean
Tables (17 pages) Basic Tables
Working with Table Columns Sorting Tables by Column ; Re-ordering Columns ; Changing Column Widths ; Changing Word Wrap Options ; Renaming Columns
Aggregating Data
Formatting Tables Formatting Numbers within a Single Table ; Formatting Numbers for All Tables ; Formatting Dates ; Font Size and Typeface ; Putting Borders around Tables ; Table Padding ; Putting Gridlines within Tables ; Formatting Columns Individually ; Table Styles ; Titles ; Totals
The Wonderful Format Painter
Conditional Formatting (Colour Scales / Data Bars) Applying Conditional Formatting or Data Bars ; Choosing Colour Scales ; Divergent Colour Scales ; Configuring Data Bars ; Rules-Based Conditional Formatting
Images and URLs in Tables Showing Images ; URLs in Tables
Matrices (3 pages) Overview of Matrices
Creating a Matrix
Multiple Rows or Columns Customising or Disabling Stepped Layout
Text boxes, images and shapes (3 pages) Overview Our Example ; Adding Text Boxes, Images or Shapes ; Working with Images ; Working with Text Boxes ; Working with Shapes ; Bringing to Front/Sending to Back
Charts (general) (10 pages) The Parts of a Chart
Types of Chart Available
Creating a Chart Creating a Chart from Scratch ; Turning a Table into a Chart
Sorting Charts
Formatting Charts Legends and Series Colours ; Data Labels ; The Plot Area ; Axes ; Tooltips ; Colour Saturation
Excluding and Including Data
Charts (specific) (13 pages) Column and Bar Charts Showing All Axis Titles (Minimum Category Width)
Line Charts Line Scaling, Widths and Markers ; Superimposing Trendlines and Statistics
Area Charts
Ribbon Charts
Pie and Donut Charts Showing Percentages of Totals
Treemap Charts
Combined Charts Controlling the Secondary Axis
Funnel Charts
Waterfall Charts Setting Breakdowns
Scatter and Bubble Charts Creating Scatter and Bubble Charts ; Properties Unique to Scatter Charts ; Data (Details) Fields ; Playing Bubble Charts
Custom Visuals (3 pages) What are Custom Visuals?
Adding Custom Visuals Deleting a Custom Visual
Showing Single Figures (4 pages)

Download this chapter 		Gauges Components of a Gauge ; Gauge Fields ; Formatting Gauges
KPIs
Cards
Multi-Row Cards (1 page) Creating a Multi-Row Card
Querying Data (13 pages) What Queries Do
Working with Queries Creating Queries ; Saving/Closing a Query ; Deleting Parts of a Query ; Deleting All Subsequent Steps ; Inserting Steps ; Changing what a Step Does ; Grouping Queries
Common Query Transforms The Source and Navigation Steps, and Other Automatic Steps ; Filtering Rows ; Sorting Rows ; Deleting Columns ; Moving Columns ; Splitting Columns by Delimiter ; Splitting Columns by Number of Characters ; Replacing Text
Adding Columns Inserting a Column using a Formula ; Using Column by Example
Conditional Columns
Relationships (7 pages) Relationships
Browsing Relationships Resetting Layout ; Fitting to Screen ; Maximising a Table
Creating Relationships The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction
Using Relationships in Tables Our Example ; Showing Columns from Parent Tables ; Showing Columns from Child Tables
Combining Tables (6 pages) Merging Rows Using UNION to Combine Tables ; Appending Rows from One Table to Another
Merging Tables (Joins) Starting a Merge Query ; Choosing a Join Type ; Displaying Columns from the Joined Table
Combining Workbooks when Importing Step 1 – Getting All the Files in a Folder ; Step 2 – Combining the Files
Slicers (8 pages) Slicer Basics Adding Slicers ; Selecting and Clearing in Slicers ; Changing Selection Behaviour ; Searching in Slicers ; Cascading Slicers ; Images in Slicers ; Formatting Slicers ; Making Slicers Drop Down ; Horizontal Slicers
Synchronised Slicers
Date Slicers Relative Date Slicers ; Changing the Anchor Date
Maps (10 pages) Overview of Maps
Creating Maps Colour Saturation ; Category Labels ; Bubble Sizes
Geocoding Avoiding Problems 1: Choosing a Field Category ; Avoiding Problems 2: Forcing Localisation
Latitude and Longitude Getting Latitude and Longitude from Postcodes ; Cleansing the Converted Data ; Creating a Relationship ; Creating the Near-Final Map ; The Last Step: Mending the Relationship
Filled Maps (Choropleths)
Shape Maps Enabling Shape Maps ; Using Shape Maps
ArcGIS Maps (7 pages) Creating ArcGIS Maps Editing ArcGIS Maps
Formatting Maps Changing the Base Map ; Choosing a Theme ; Symbol Appearance
Pins
Showing Drive Times
Reference Layers
Filtering reports (4 pages) How Filters Work Three Levels of Filtering
Adding and Removing Filters Removing Filters
Other Filter Options Combining Filters (Advanced) ; Relative Date Filtering ; Top and Bottom Filters
Drill-through Filters (3 pages) What Drill-Through Filters Do
Creating the Filtered Report Pages Creating the Report Pages ; Applying a Drill-Through Filter
Using a Drill-Through Filter
Binning and grouping (4 pages) Grouping Starting a Group ; Allocating Items to Members of the Group ; Using Groups you’ve Created
Binning
Hierarchies (2 pages) Using Hierarchies
Creating Hierarchies Deleting Hierarchies
Drill-down (6 pages) Overview of Drill-Down The Pre-Requisite for Drill-Down to Work: Multiple Axis Fields
Using Drill-Down Turning Drill-Down On and Off ; Drilling Down ; Drilling Back Up ; Special Considerations for Matrices ; Going to the Next Hierarchy Level ; Expanding All Levels in a Hierarchy
Showing Data
Listing Records
Templates (1 page) Creating Templates Creating Templates ; Using Templates
New Columns (8 pages) Overview of New Columns Reasons to Use New Columns ; DAX
Creating New Columns Starting a New Column ; Typing in a Formula ; Hints for Editing DAX ; DAX Editors ; Using AND and OR in DAX Conditions
Using IF and SWITCH The IF Function ; The SWITCH Function
The RELATED Function
Blanks Blank Arithmetic
Testing for Errors (IFERROR)
Basic Measures (5 pages)

Download this chapter 		Measures Always Involve Aggregation
Creating Measures Starting your Measure ; Creating and Formatting a Measure Formula ; Using your Measure in a Visualisation or Filter ; The Importance of Giving DAX what it Asks for
DAX Aggregation Functions Aggregating Expressions
Measures Referring to Measures
Changing Filter Context (6 pages) Filter Context Revisited
The CALCULATE Function Syntax of the CALCULATE Function ; Removing Filters ; Removing Multiple Filters ; Removing All Filters Except One ; Replacing one filter ; Replacing Multiple Filters
Filter Function Using Calculate instead of Filter
Building on the Filter Context (VALUES) Without the VALUES Function ; With the VALUES Function
Advanced DAX (3 pages) Variables
The EARLIER Function Our Two Worked Examples ; What the EARLIER Function Does ; Example: Number of Purchases on the Same Date ; Example: Ordering Purchases by Date
Calendars and Dates (8 pages) What Calendars Are, and Why to Use Them
Calendar Tables within Power BI Desktop Calendars using the M Language ; Renaming the Date Column ; Creating New Columns
Importing Calendar Tables Creating a Calendar Table in Excel ; Creating a Calendar Table in SQL Server
Using Calendar Tables Simple Relationships ; Sorting Months ; Multiple Table Relationships
Case Study: Working Days
Date Formulae (8 pages) Cumulative to Date The TOTALYTD, TOTALQTD and TOTALMTD Functions ; DATESYTD, DATESQTD, DATESMTD Functions
Comparisons with Previous Years The SAMEPERIODLASTYEAR Function ; The DATEADD Function
Parallel Periods
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Moving Averages
Power BI Service (4 pages) Introduction to Power BI Service
Publishing Reports from PB Desktop
Reports within Power BI Service Creating Reports ; Editing Reports ; Filtering Reports ; Another Way to View Items in Power BI Service
Dashboards and tiles (9 pages) Overview of Dashboards
Working with Dashboards Creating a Dashboard ; Editing and Deleting Dashboards ; Setting a Featured Dashboard ; Refreshing Dashboards
Working with Tiles Adding Tiles ; Moving and Resizing Tiles ; Changing Dashboard Tile Flow ; Exporting Data for a Tile ; Viewing the Report or Dataset for a Tile ; Changing a Tile’s Title and Subtitle ; Changing what Happens when you Click on Tiles
Live Pages
Adding Tiles Directly Examples of Each Added Tile Type
Refreshing Data (6 pages) What Needs Refreshing
On-Premises Data Gateways Types of Gateway ; What Constitutes a Gateway
Installing and Configuring Gateways Step 1 - Getting Gateway Software ; Step 2 – Running the Installation Software ; Step 3 – Signing In ; Step 4 – Assigning Datasets to Gateways
Refreshing Data Manually Refreshing Data ; Scheduled Refreshing ; Viewing Refresh History ; Scheduled Refreshes
Sharing (7 pages) Ways to Share
Publishing an App Step 1 - Creating an App Workspace ; Step 2 – Publishing to the App Workspace ; Step 3 – Add Dashboards ; Step 4 – Start to Publish your App ; Step 5 – Configure your App ; Step 6 - Getting Access to a Published App
Sharing Dashboards Subscribing to Dashboards
Sharing Reports Saving Reports (PDF and PowerPoint)

Power BI for Excel courseware

Here is a list of all of our Power BI for Excel manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introduction to PowerPivot (2 pages) The Course Database
The Power BI Components
Creating Data Models (12 pages) Going into PowerPivot
Importing Tables Step 1 – Connecting to the Database ; Step 2 – Choosing Data Tables ; Step 3 – Importing the Data ; Step 4 – Saving your Work (the xVelocity Engine)
Importing More Tables using an Existing Connection
Filtering Rows and Columns on Import Ignoring Columns ; Filtering Rows
Data and Diagram View Zooming In and Out in Diagram View
Hiding Columns and Tables from Client Views Hiding Columns ; Hiding Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Formatting Table Columns
Relationships (3 pages) Viewing Existing Relationships
Creating Relationships Creating Relationships in Diagram View ; Creating Relationships Manually
Pivot Tables using PowerPivot (16 pages) Creating the Data Model
Creating a Pivot Table Step 1 – Starting the Pivot Table ; Step 2 – Choosing Fields
Drill Down and Quick Explore Drill Down ; Deleting drill down results ; The Quick Explore Option
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers and Timelines Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Inserting Timelines ; Controlling Multiple Pivot Tables with Slicers/Timelines
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
Using Excel Workbooks (4 pages)

Download this chapter 		Linking to Excel Tables Creating Tables ; Naming a Table ; Listing Tables ; Linking to Tables
Importing Excel Workbooks
Links to Other Data Sources (5 pages) Formats Supported
Linking to Access
Importing Cubes
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from a CSV File
Getting Data (8 pages) A Potted History of Get & Transform
What Get & Transform Does
Getting Data Step 1 - Creating the Query ; Step 2 – Load or Edit?
Managing Queries
Linking to a Website
Linking to an XML File
Other Data Sources
Transforming Data (12 pages) Editing a Query The Query Editor
Editing Query Steps The Hard Way – the M Language ; The Easy Way – Query Steps
Adding Simple Transforms Removing Columns ; Reordering Columns ; Filtering Rows ; Sorting Columns
Splitting Columns, etc. – a Case Study Step 1 – Splitting by Number of Characters ; Step 2 – Splitting by Delimiter ; Step 3 – Replacing Values ; Step 4 – Changing Data Types ; Step 5 - Merging Columns ; Step 6 – Inserting a Start Date Column ; Step 7 – Getting the Month Number ; Step 8 – Aggregating the Data ; Step 9 – Renaming Query Steps ; Step 10 – Running your Query
Managing your Queries Renaming Queries ; Grouping Queries
Recent Data Sources
Calculated Columns (7 pages) What are Calculated Columns? Differences between Calculated Columns and Excel Formulae
Creating Calculated Columns An Alternative Method, using the Keyboard
Working with Calculated Columns Moving Calculated Columns ; Sorting by Calculated Columns
The PowerPivot Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH Using SWITCH
Measures (10 pages) Query Context
Implicit Measures Viewing Implicit Measures
Using AutoSum Using an AutoSum Measure in a Pivot Table
Creating Measures in Excel Step 1 - Starting to Create the Formula ; Step 2 – Choosing the Table ; Step 3 – Completing the Dialog Box
Step 4 – Checking your Formula Step 5 – Using your Formula
Creating Measures in PowerPivot Step 1 – Choosing where to Start ; Step 2 – Creating the Formula ; Step 3 – Renaming the Measure ; Step 4 – Using the Measure
Measures vs. Columns – the Differences Difference 1 - Measures Aggregate ; Difference 2 - Use in Pivot Tables ; Difference 3 - Key Performance Indicators (KPIs)
Basic DAX Formulae (6 pages) Aggregation Functions
X-Suffix Functions Simple Aggregate Functions don’t Work with Expressions ; Aggregate Functions with X Suffix ; Our Example SUMX Function
Calculating Ratios Counting Rows ; Creating Ratios: Measures Needed ; The Final Pivot Table ; Summing Ratios doesn’t Work
DAX Variables
The CALCULATE Function (4 pages) Syntax of the CALCULATE Function
Replacing Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Removing a Single Filter ; Removing Multiple Filters
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Query Context Filter
The FILTER Function (3 pages) Syntax of the FILTER Function
Filtering to Show Subsets of Data
Removing Filters using ALL Removing and Partially Reinstating Filters
Ranking (3 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating an Intermediate Measure ; Step 2 – using ALL ; Changing the Sort Order
The EARLIER Function (2 pages) Case Study: Ranking using EARLIER
Case Study: Group Statistics
Calendars (13 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The PowerPivot Approach – Create a Separate Table
Creating a Calendar Table Creating a Calendar in Excel ; Creating a Calendar Table in SQL Server ; Creating a Calendar Automatically
Using your Calendar Change 1 – Mark your Calendar as a Date Table ; Change 2 - Changing Column Data Types ; Change 3 - Creating Relationships
Creating Pivot Tables using Dates Sorting Months Correctly ; Sorting Months by Financial Year
Multiple Date Tables Solution 1 – Multiple Calendars ; Solution 2 – Multiple Relationships
Worked Example – Working Days Calculation Step 1 – Create a Bank Holiday Table ; Step 2 – Create a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Date Functions (9 pages) Formulae Covered The Underlying Principle
Period to Date The Year-to-Date Function Family ; Dates to Date Functions ; Fiscal Year Calculations
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods Comparing with the Previous Year’s Year-to-Date
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Hierarchies (3 pages) Using Hierarchies Advantages and Disadvantages of Hierarchies
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
KPIs (5 pages)

Download this chapter 		About KPIs The Three Parts of a KPI
Creating a KPI Step 1 – Create the Measures Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying Parts of the KPI
Absolute Targets
Perspectives (2 pages) Overview of Perspectives What Perspectives Are ; Using Perspectives in PowerPivot
Creating and Editing Perspectives
Power View (4 pages) Enabling Power View
What is Power View?
Creating a Power View Visualisation
Why Not to Use Power View

Advanced Power BI courseware

Here is a list of all of our Advanced Power BI manual chapters (you can download the ones marked):

Chapter Sections Subsections
Advanced Query Editor (14 pages) Loading Multiple Files Setting the Folder ; Setting the Files ; Setting the Template for Import ; Tidying Up the Results ; How it Works
Appending Queries
Merging Data Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Tidying the Final Table
Query Dependencies
Switching the Data Source using M Showing the Advanced Editor ; The Excel Query ; Creating a SQL Server Version of our Table ; Switching the Data Source ; Resolving Problems ; Viewing the Results
Unpivoting Data Loading the Original Data ; Unpivoting the Data ; Renaming your Columns
Dynamic Filtering without Parameters (8 pages) Our Aim
Creating the Slicers The Postcode Slicer ; The Date Slicer
Displaying a Dynamic Title Step 1 – Create a Table ; Step 2 – Creating the Date Bound Measures ; Step 3 – Creating the Title Measure ; Step 4 – Create a Card ; Step 5 – Coping with Multiple Slicer Choices ; Step 6 – A Quick Measure to Display Multiple Values
Dynamic Filtering using Parameters (9 pages)

Download this chapter 		Overview of Chapter
Working with Parameters Accessing Parameters ; Creating Parameters ; Basing Parameters on Lists ; Managing Parameters
Entering Values for Parameters Entering Parameter Values in Query Editor ; Entering Parameter Values in Power BI Desktop
Filtering using Parameters
Referencing Parameters in Power BI Step 1 – Loading Parameters into your Data Model ; Step 2 – Creating a Table to Hold Measures ; Step 3 – Creating the Title Measure ; Step 4 – Creating a Card to Display the Measure
Dropdown Parameters (3 pages) List Queries
Creating the List Query Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a New Query
Creating the Dropdown Parameter
Parameters and Stored Procedures (4 pages) Overview
Loading Data from a Stored Procedure
Making Parameters Dynamic Creating the Parameters ; Referencing the Parameters in the Advanced Editor ; Editing Parameter Values
Dynamic Data Sources (6 pages) SQL Server Dynamic Connections First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Changing the Server or Database ; The Query Editor Step Created
Dynamic Connections to Excel Step 1 – Create a Connection (Get Data) ; Step 2 – Create a Parameter ; Step 3 – Parameterising the Connection Used ; Changing the Worksheet Used
Data Analytics (6 pages) Overview of Lines
Adding Lines
Trend Lines and Forecasts The Combine Series Option
Forecast Lines
Symmetry Shading and Ratio Lines Preparing your Data for Symmetry Shading ; Creating a Ratio Line and Symmetry Shading ; Post-Script on Log Scatter Charts
Custom Visuals (6 pages) Installing Custom Visuals
Chiclet Slicers Step 1 – Get the Images ; Step 2 – Ensure you have an Image URL ; Step 3 – Create your Chiclet Slicer ; Step 4 – Setting the Images ; Step 5 – Formatting the Slicer ; Step 6 – Testing your Slicer
The Play Axis Visual How it Works ; Setting the Play Axis Slicer Field ; Animation Settings ; Formatting the Caption
Tooltips (10 pages) Overview
Minimal Tooltips
Displaying Field Values Adding Additional Fields
Static Report Pages Step 1 – Create the Tooltip Page ; Step 2 – Setting the Page Size ; Step 3 – Hiding your Report ; Step 4 – Creating the Tooltip Report ; Step 5 – Assigning a Report Page to a Tooltip
Dynamic Report Page Tooltips Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning Fields to the Tooltip Report Page ; Step 4 – Choosing When to Show your Tooltip ; Step 4 – Choosing whether to Keep Filters ; Step 5 – Assigning your Tooltip Report Page to a Visual
Manual Assignment
Quick Measures (7 pages) About Measures
Creating Normal Measures Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure
Starting Quick Measures
Case Study 1 – London versus Regional Sales Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure
Case Study 2 – Listing Slicer Selections Creating the Slicer Selection Quick Measure ; Viewing the DAX Formulae ; Displaying the Slicer Selection
Converting Postcodes (4 pages) Overview of the Problem
Converting Postcodes to Latitude/Longitude
Getting at the Latitude/Longitude Data Loading the Latitude and Longitude Data into Power BI ; Removing Duplicate Postcodes and Creating a Relationship ; Combining Columns ; Showing Latitude and Longitude in Maps
MapBox Visual (8 pages) Introduction Getting the MapBox Custom Visual
Enabling your Map with a Token
Creating a Map Searching for a Place (Geocoding) ; Choosing the Map Style
Selecting Points Using the Lasso and Polygon
Different Types of Map Circle Maps ; Cluster Maps ; Heat Maps ; Switching Maps on Zoom
Shape Maps (7 pages) Introduction Enabling Shape Maps
Finding a Map Some Useful Terms ; Finding a topoJSON File
Creating the Shape Map Starting the Map ; Assigning a Map File ; Reviewing Map Keys ; Creating Map Keys in your Data ; Setting the Location Field ; Zoom Settings ; Setting a Tooltip ; Setting the Default Colour ; Setting Map Colours
Synoptic Panels and Maps (8 pages) Overview
Creating the Image Creating a Base Image ; Adding this Image in Synoptic Designer ; Filling In your Picture ; Naming the Areas ; Saving your Image
Creating the Visual Tying your Data to your Map ; Importing the Synoptic Panel Custom Visual ; Creating a Synoptic Panel
Formatting the Visual Colour Saturation ; Settig Colour Thresholds (“States”)
Buttons and Actions (4 pages) Overview Types of Button
Assigning Actions to Shapes or Images
Creating Buttons Adding Buttons ; Default, Hover and Selection Effects ; Formatting Buttons
Bookmarks (6 pages) Example of Bookmarks
Preparing to Use Bookmarks Naming your Visuals in the Selection Pane ; Displaying the Bookmarks Pane ; Creating Bookmarks
Configuring your Bookmarks Step 1 – Data Settings ; Step 2 – Display Settings ; Step 3 – Update the Bookmark ; Step 4 - Linking to your Bookmarks
Linking Back to the Home Page Creating the Home Bookmark
Uses of Bookmarks (13 pages) Ideas in this Chapter
Clearing Filters and Slicers Creating the Slicers and Action Button ; Clearing the Slicers ; Creating and Customising your Bookmark ; Attaching the Bookmark to your Action Button
Clicking on Pictures to Filter Charts Setting the Scene ; Setting the Bookmarks to Filter ; Attaching the Bookmarks to the Images
Sort Icons Creating the Buttons ; Create the Bookmarks ; Configure the Bookmarks
Tabbed Controls Creating the Visuals ; Creating the Tabbed Effect ; Creating the Bookmarks ; Attaching the Bookmarks to the Tabs
Moving between Pages Creating the Pages ; Synchronising the Home Page Slicer ; Creating the Bookmarks ; Assigning Actions
Pop-Up Buttons Creating the Pop-Up Message ; Naming the Parts of the Pop-Up Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks
Themes (4 pages) Applying Existing Themes
Using the Themes Gallery
Limitations of Themes Themes don’t Override Custom Colours ; Custom Visuals don’t Support Themes
Creating Custom Themes (8 pages) Overview
Understanding the Parts of a Theme Foreground and Background Colours ; The TableAccent Property ; The DataColors property
Understanding JSON JSON is just like XML ; Objects and Name/Value Pairs ; Arrays
Creating a JSON File Creating a New File ; Adding a Line ; Adding an Array ; Changing Colours ; Saving your Changes
Extending Custom Themes (6 pages) Syntax of Visual Styles CardName Values
Examples of Visual Styles Example: Setting the Default Font ; Example: Setting the Default Legend ; Example: Setting the Default Title ; Example: Pie Chart Labels ; Example: Setting a Border
This Courseware’s Theme
Templates (6 pages) Creating a Template
Using a Template
Ideas for Templates Themes ; Data ; Parameterised Data Connections ; Parameters for Filtering ; Corporate Logos ; Layouts
Power BI Mobile (8 pages)

Download this chapter 		What is Power BI Mobile?
Limitations of Mobile Reports Problems with Titles ; Portrait and Landscape Issues ; Standard Formatting across Report Types ; Grid Mesh Size
Creating Mobile Reports
Using Mobile Reports Using Drill-Down on Mobile Phones
Mobile Dashboards Designing Mobile Dashboards ; Using Mobile Dashboards
Question and Answer (3 pages) Q&A in Power BI Desktop
Q & A in Power BI Service
Synonyms
Analysing in Excel (5 pages) Pivoting Report Data
Preparation in Power BI Desktop Tidying your Data Model ; Creating a Measure Table ; Creating Measures
Preparation in Power BI Service
Analyzing in Excel
Row-Level Security (10 pages) Overview of Row-Level Security Limitations and Constraints
How Roles Work
Roles in Power BI Desktop Creating Roles ; Testing Roles
Roles in Power BI Service Configuring the App Workspace ; Assigning Users to Roles ; Testing your Roles
Dynamic Security Creating a Permissions Table ; Creating a Role
Creating Groups of Users Creating the Tables ; Linking the Tables ; Creating the Role

DAX courseware

Here is a list of all of our DAX manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introducing DAX (1 page) Where is DAX Used?
Creating a Data Model (6 pages) The Woozle Data Model The Geographical Tables ; The Product Category Tables ; Some Terms
Creating Reports in Power BI Desktop Loading the Data Tables ; Creating Visuals in Power BI
Creating Reports in PowerPivot Loading Tables into Data Models in PowerPivot ; Creating Pivot Tables in PowerPivot
Writing DAX (6 pages) Calculated Columns Referring to Columns/Fields ; Referring to Tables ; Fully Qualified References
Writing DAX Laying out your Formulae ; Using Multiple Lines ; DAX Formatter ; Comments
DAX Syntax Functions and Arguments ; Mathematical Operators ; Concatenating Text
DAX Studio (3 pages) Using DAX Studio Installing DAX Studio ; Connecting to your Data Model
Four Uses of DAX Studio Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX
Testing Conditions (3 pages)

Download this chapter 		Testing Single Conditions The IF Function ; Relational Operators ; Logical Operators ; Using IN to Test if Items Exist in a List
The SWITCH Function
Linking Tables (4 pages) The RELATED Function
Dealing with Blanks BLANK Arithmetic
The RELATEDTABLE Function
Working with Data Types (5 pages) DAX Data Types
Scalar Date Functions
Scalar Text Functions Finding and Replacing Text ; Converting Text ; Formatting Text ; Getting the Length of and Extracting Text
Scalar Number Functions
Trapping Errors (2 pages) Using the DIVIDE Function
Using IFERROR Generating your Own Errors using ERROR
Creating and Storing Measures (6 pages) Implicit Measures Recognising Implicit Measures ; Showing Implicit Measures in PowerPivot
Creating Measures Creating Measures in Power BI Desktop ; Creating Measures in PowerPivot
Using a Measures Table Creating a Measures Table in Power BI Desktop ; Creating a Measures Table in PowerPivot
Aggregating Data (6 pages) Automatically Aggregating Data Choosing Automatic Measures in PowerPivot ; Changing Automatic Measures in Power BI
DAX Aggregation Functions
Aggregating Expressions The Problem ; Why the Simple Solution Won’t Work ; The Answer – X-Suffix Functions ; Syntax of AggregateX Functions
Calculating Ratios Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios Wouldn’t Work
Filter Context (5 pages) Our Simple Example
How Filter Context Works What We’re Working Towards ; Step 1 – Assembling the Data ; Step 2 – Working out the Filter Context ; Step 3 – Getting the Filtered Data for the Context ; Step 4 – Aggregating the Data
Row Context (3 pages) Row Context for Calculated Columns
Iterator Functions Normal Aggregate Functions Use Filter Context ; Iterator Functions Use Row Context
The CALCULATE Function (9 pages) Syntax of the CALCULATE Function
Removing a Constraint Our Example ; A Quick Note on Ratios ; The Formula for this Example ; How this Works
Removing Multiple Constraints Using Multiple ALL Functions ; Using ALLEXCEPT
Replacing a Constraint Filter Context Revisited – Column Storage ; How Replacing Filter Context Really Works
Using ALLSELECTED
Context Transition
The VALUES Function (7 pages) Introducing the VALUES Function
Detecting the Number of Values The HASONEVALUE Function ; Using COUNTROWS to Count VALUES
Using VALUES to Modify Filter Context The Obvious Way doesn’t Work ; Using the VALUES Function to Solve the Problem
Parameter Tables
Dynamic Titles using ISFILTERED Dynamic Titles for Single-Value Filters ; Dynamic Titles for Multi-Value Filters
Variables (5 pages) Referring to Measures within Measures
Creating Variables
Lazy Evaluation and its Implications
Storing Tables in Variables
Debugging using Variables
The FILTER Function (7 pages) The Basic FILTER Function Using CALCULATE as an Alternative to FILTER
FILTER as an Iterator Function Starting Off – Our Example ; Getting the Filter Context ; Row Context within this Filter Context ; Deriving the Final Result
Multiple Conditions in FILTER Functions Combining Conditions using && and || ; Combining Conditions using AND / OR ; Combining Conditions by Nesting the FILTER Function
Using ALL and FILTER
FILTER and CALCULATE aren’t Equivalent
The EARLIER Function (5 pages) Case Study of the EARLIER Function Our Example ; An Outline of the EARLIER Function ; Row Context within Filter Context ; The Final Formula
Another Example – Running Totals
Using Variables instead of the EARLIER Function Ranking Sales using Variables ; Running Totals using Variables
Banding (3 pages) What is Banding? Creating and Loading a Banding Table
Creating a Banding Formula
Sorting the Bands
Ranking (5 pages) The RANKX Function Syntax of the Rank Function ; Intellisense for the RANKX Function
RANKX for Calculated Columns
Ranking Measures (Existing Columns) The Most Common Problem – Omitting ALL ; The Solution using ALL
Ranking using Aggregate Calculations RANKX is an Iterator Function
Ranking with Context Suppressing Totals ; Ranking over Selected Items
Calendar Tables (6 pages) What are Calendar Tables? Requirements for a Calendar Table ; Why you Need a Calendar Table
Creating a Calendar Step 1 – Getting the Calendar Data ; Step 2 – Loading and Linking to the Calendar Table ; Step 3 – Mark your Table as a Date Table ; Step 4 - Setting a Sort Month
Date Granularity
Special Days
Multiple Date Tables (8 pages)

Download this chapter 		The Problem, and Two Solutions Repeat the Table or the Relationship?
Solution One: Duplicate the Calendar Table Step 1 - Importing and Linking to the Calendar Tables ; Step 2 – Renaming Tables and Fields ; Step 3 – Using your Multiple Calendars
Solution Two: Duplicate the Relationship Creating the Duplicate Relationships ; The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures
The CROSSFILTER Function One Solution – Change the Relationships Permanently ; A Better Solution – Use DAX to Temporarily Cross-Filter ; Multiple Cross-Filtering
How Time Intelligence Functions Work (4 pages) Our Example
Filter Context Reminder
Year-to-Date using CALCULATE
Year-to-Date using Time-Intelligence Functions The DATESYTD Function ; The TOTALYTD Function
DAX Date Functions (10 pages) Contents of the Chapter
Period to Date Using DATESYTD, DATESQTD and DATESMTD ; Using TOTALYTD, TOTALQTD and TOTALMTD
Changing the Financial Year End Functions with a Year End Date Argument ; Displaying Data for Different Financial Year Ends
Referencing Previous Periods The SAMEPERIODLASTYEAR Function ; The DATEADD Function
Parallel Periods
Moving Averages Definition of a Moving Average ; Moving Average using DATESINPERIOD and LASTDATE ; Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE
Semi-Additive Measures Useful Semi-Additive Functions ; Using the FIRSTDATE and LASTDATE Functions ; Using FIRSTNONBLANK and LASTNONBLANK ; Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK
Hierarchies (7 pages) Calculating Percentages The Problem ; A Solution
Parent-Child Hierarchies What is a Parent-Child Hierarchy? ; Step 1 – Create a List of Parent Ids (the PATH Function) ; Step 2 – Working out the Path Depth (the PATHLENGTH Function) ; Step 3 – Create a Measure Showing the Number of Levels ; Step 4 - Finding Managers at Each Level (PATHITEM and LOOKUPVALUE) ; Step 5 – Creating a Hierarchy ; Step 6 – Checking your Relationships ; Step 7 – Dealing with Blanks
Introducing DAX Queries (5 pages) Overview of DAX Queries
DAX Queries are like SQL
Where to Write DAX Queries
Debugging using DAX Queries A Worked Example ; Debugging using Table Variables ; Using Queries to Debug
Syntax of DAX Queries (12 pages) Listing Tables (EVALUATE) Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows
Summarising Summarisation Syntax ; Example of Summarising by a Single Column ; Example of Summarizing by Multiple Columns ; Using SUMMARIZECOLUMNS ; Using ROLLUP to get All Combinations
Filtering in DAX Queries Example: Summarising Sales for Birds Only ; Example: Summarising Centre Size for Selected Towns
Adding Columns Example: Counting Rows ; Example: Purchase Statistics by Town
Using SELECTCOLUMNS Adding Columns using SELECTCOLUMNS
Defining Measures and Variables Defining Measures ; Defining Variables
Using ROW to Display One Row of Data
Combining Table Results using GENERATE and GENERATEALL

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post