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

Power BI Desktop courseware

Here is a list of all of our Power BI Desktop 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 (9 pages) Example for this Chapter
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 Visualisations Formatting a Visualisation
Filtering your Data
Working with Reports Changing how you View Reports ; Page Settings
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 (4 pages) Overview of Visualisations
Working with Visualisations Adding a Visualisation ; Editing Visualisations (Fields, Format or Analytics) ; Zooming In and Out (Focus Mode) ; Resizing a Visualisation ; Moving, Selecting and Deleting Visualisations ; Aligning and Distributing Visualisations
Visual Interactions (2 pages) Visual Interactions How Visualisations Affect Each Other by Default ; Editing Interactions ; What the Different Settings Mean
Tables (16 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 ; Formatting Dates ; Font Size and Typeface ; Putting Borders around Tables ; 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
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
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 (2 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 (5 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
Date Slicers (Timelines)
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
Parameters (11 pages) Disadvantages of Filtering without Parameters
Creating Parameters
Editing Parameters Changing the Definition of Parameters ; Changing the Value of Parameters
Referencing Parameters in Queries Example: Referencing Date Parameters in Filters ; Example: Referencing a Text Parameter in Filters ; Showing Query Results
Showing Parameter Choices in Titles Step 1 - Loading Parameters ; Step 2 – Creating a Measure ; Step 3 – Creating the Necessary Visualisations ; Step 4 – Customising your Card
Multiple Value Parameters
Using Parameters for Data Sources Dynamically Switching SQL Server Databases ; Dynamically Switching Excel Workbooks
Lists and parameters (3 pages) What Lists Are Using Lists for Parameters
Creating Lists Creating Lists in the Query Editor ; Creating Lists from Queries
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
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) The Need for Calendars
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)
Getting Data (3 pages) Importing Third Party Apps
Importing Excel Workbooks Pinning Parts of a Workbook ; Publishing from Excel
Analysis and Insights (6 pages) Quick Insights Scoped Quick Insights
Asking Questions Displaying the Question Box ; Asking Questions ; Getting Help while Asking Questions ; Mixing Datasets ; Featured Questions
Using Synonyms
Row-level security (4 pages) How Roles Work
Row-Level Security in Power BI Desktop Creating Roles ; Testing Roles
Roles in Power BI Service Assigning People to Roles in Power BI Service ; Testing Roles in Power BI Service
Mobile Phone Considerations (3 pages) Power BI Desktop for Mobile Phones
Mobile View for Power BI Service
Power BI Mobile

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

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