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.

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 Basics (2 pages) What is Power BI Desktop?
Installing and Updating Power BI
Getting Started (12 pages) Getting Started in Power BI Desktop Example for this Chapter ; Showing Keyboard Shortcuts
Working with Files Creating New Files ; Saving and Closing Files
The Power BI Desktop Screen
Getting Data
Transforming Data Editing a Query ; The Power Query Editor ; Removing and Renaming Columns ; Splitting Columns
Creating Visualisations Inserting a Visualisation ; Assigning Fields to a Visualisation
Formatting Visuals Searching for Format Properties
Filtering Visuals Adding Fields to the Filters List ; Applying a Filter
Working with Report Pages Changing the Page View ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages
Themes
Importing Data (10 pages) Our Example
Importing from Different Sources Re-Using a Data Source
Importing from Excel
Importing CSV or Text Files
Importing from SQL Server Using Queries and Stored Procedures
Importing from a Website
Importing a Power BI Dataset
Entering Data Manually Pasting Data ; Typing in Data
Data Models (10 pages) Data Models Viewing a Model ; Selecting Single Model Items ; Selecting Multiple Items ; Searching for Fields
Model Diagrams Arranging Tables in a Model ; Diagram Layouts ; Collapsing and Expanding Tables ; Controlling Expand/Collapse Field Visibility ; Seeing Table Information ; Hiding Objects in the Report View
Model Properties Table Properties ; Display Folders ; Default Number and Date Formatting ; Changing the Default Aggregation for a Field
Relationships The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction
Visualisations (10 pages) Overview of Visualisations
Working with Visualisations Adding a Visual ; The Visual Editing Panes ; The Visual Header ; Focus Mode ; Spotlight Mode ; Resizing a Visual ; Selecting Visuals ; Moving, Copying and Deleting Visuals ; Locking Visuals ; Aligning and Distributing Visualisations ; Grouping Visualisations ; Customising the Visuals Pane
The Selection Pane Changing the Visual Layer Order ; Changing the Tab Order ; Hiding Visuals ; Renaming a Visual
Visual Interactions Editing Interactions ; Visual Interaction Settings
Tables (15 pages) Basic Tables
Working with Table Columns Sorting Tables by Columns ; Re-ordering Columns ; Changing Column Widths Automatically ; Changing Column Widths Manually ; Changing Word Wrap Options ; Renaming Columns
Aggregating Data
Formatting Numbers Formatting Numbers within a Single Table ; Formatting Numbers for all Visuals (Method 1) ; Formatting Numbers for all Visuals (Method 2) ; Setting Custom Number Formats
Formatting Dates Displaying Dates as Dates ; Formatting Dates (Method 1) ; Formatting Dates (Method 2)
Formatting Visuals – General Visual Borders, Shadow and Background ; Titles
Specific to Formatting Tables Column Headers ; Totals ; Font Size and Typeface ; Table Padding and Gridlines ; Table Styles ; Formatting Columns Individually
The Wonderful Format Painter
Images and URLs in Tables Showing Images ; URLs in Tables
Conditional Formatting (7 pages)

Download this chapter 		Conditional Formatting
Applying Conditional Formatting to Fields Applying Conditional Formatting through the Field Well ; Applying Conditional Formatting through the Format Pane
Gradient Effects Adding a Middle Colour
Rules-Based Conditional Formatting
Data Bars
Formatting Using Field Values Using SWITCH to Generate Colours
Displaying Icons
Matrices (3 pages) Overview of Matrices
Creating a Matrix Sorting in a Matrix
Multiple Rows, Columns and Values Disabling or Editing Stepped Layout
Text Boxes, Images and Shapes (4 pages) Non-Data Visualisations Drawing Text Boxes, Images or Shapes
Images Scaling Images ; Adding a Hyperlink to an Image
Text Boxes Inserting Values
Shapes Adding a Shape
Chart Basics (12 pages) The Parts of a Chart
Types of Chart Available
Creating a Chart Creating a New Chart ; Turning a Table into a Chart
Sorting Charts
Formatting Charts Legends and Series Colours ; Detail Labels ; Total Labels ; Plot Area and Background ; Gridlines ; Axes ; Conditional Formatting
Excluding and Including Data
Zoom Sliders
Small Multiples (4 pages) What Makes a Good Small Multiple?
Creating Small Multiples Setting Grid Width and Height ; Formatting Small Multiple Titles ; Formatting Small Multiple Charts
More on Charts (11 pages) Column and Bar Charts Continuous and Categorical Axes ; Category Width and Padding
Line Charts Line Formatting ; Dual Axis Line Charts
Combined Charts Formatting the Secondary Axis
Waterfall Charts Showing Breakdowns
Scatter and Bubble Charts Animating Bubble Charts
Other Chart Types Area Charts ; Ribbon Charts ; Pie and Donut Charts ; Treemap Charts ; Funnel Charts
Grouping and Binning (3 pages) Grouping Starting a Group ; Editing Groups ; Using Group Fields
Binning
Visuals for Dashboards (5 pages) Gauges Minimum, Maximum and Target Values ; Formatting Gauges
KPIs Creating Targets ; Colour Coding Targets ; Formatting the Numbers Displayed
Cards
Custom Visuals (4 pages) What are Custom Visuals?
Adding Custom Visuals
Using a Custom Visual
Removing Custom Visuals Removing Custom Visuals Pinned to the Visualisations Pane
Drill-Down (6 pages) What is Drill-Down? Enabling Drill Down
Using Drill-Down Drilling Down and Up ; Drill Down and Visual Interactions ; Viewing the Next Hierarchy Level ; Expanding All Levels in a Hierarchy
Drill-Down in a Matrix Choosing Row or Column Fields
Showing Data Tables
Slicers (9 pages) Slicer Basics Creating a Slicer ; Selecting and Clearing Items ; Changing Selection Behaviour ; Searching in Slicers ; Dropdown Slicers ; Slicer Orientation ; Formatting Slicers ; Customising your Slicer Header ; Images in Slicers ; Slicer Interactions
Number and Date Slicers Sliders ; Choosing Dates ; Picking Relative Dates ; Changing the Anchor Date
Sync Slicers
Hierarchical Slicers
Filtering Reports (8 pages) How Filters Work
The Filters Pane
Applying and Removing Filters Applying a Basic Filter ; Adding Fields to the Filters Pane ; Removing a Filter ; Advanced Text Filters ; Advanced Number Filters ; Relative Date Filtering ; Top and Bottom Filters
Sorting Filters Manually Arranging Filters ; Sorting Filters
Formatting Filters
Controlling Filters for End Users Locking and Hiding Filters ; Report Filter Settings
Drill-Through Filters (5 pages)

Download this chapter 		What are Drill-Through Filters?
Creating a Drillthrough Filter Step 1 – Create the Main Report Page ; Step 2 – Create the Drillthrough Target Page ; Step 3 – Create the Drillthrough Filter ; Step 4 – Use the Drillthrough Feature
Extra Drillthrough Options Keeping All Filters ; Drillthrough from Summarised Fields
Buttons for Drill-Through
Querying Data (13 pages) What are Queries?
Working with Queries Opening the Query Editor ; The Power Query Editor ; Default Query Steps ; Viewing Data at Different Steps ; Editing a Query Step ; Deleting a Query Step ; Adding a New Step ; Applying Query Changes ; The Advanced Editor
Transforming Data Changing Data Types ; Renaming Columns ; Removing Columns ; Removing Rows ; Sorting and Filtering Rows ; Splitting Columns ; Extracting Values ; Replacing Values ; Replacing Errors ; Duplicating Columns
Creating New Columns Creating a Formula ; Creating a Column by Example
Conditional Columns
Calculated Columns (8 pages) Introduction to Calculated Columns
Creating Calculated Columns Starting a New Column ; Typing a Formula ; Hints for Editing DAX ; DAX Editors
Conditional Functions The IF Function ; Operators in DAX ; The SWITCH Function
The RELATED Function
Blanks Testing for Blanks ; Creating Blanks ; Blank Arithmetic
Testing for Errors
Maps (9 pages) Overview of Maps
Choosing a Mapping Tool Installing Preview Map Features
Creating Maps Assigning a Location ; Bubble Size ; Conditional Formatting ; Using Legend Fields ; Heat Maps ; Map Styles
Geocoding Categorising Geographic Data ; Adding a Country Code
Latitude and Longitude Converting Postcodes ; Cleaning the Imported Data ; Configure the Data Model ; Assigning Fields to a Map
Drilldown in Maps Controlling Map Zoom
Publishing (13 pages) Overview of Publishing
An Infinite Number of Variables Your Power BI Licence ; Linking to Data ; Data Sources
Logging on to Power BI Service
Creating Workspaces
Publishing a Report
Viewing and Editing Reports Reports and Datasets ; Lineage View ; Viewing Individual Reports ; Editing a Report
Dashboards and Tiles Adding Tiles to Dashboards ; Listing Dashboards ; Viewing Dashboards
Sharing and Exporting Reports Creating a PowerPoint Presentation ; Exporting to PDF ; Generating a Public URL for your Report ; Embedding your Report in a Website ; Creating a Power BI Report File ; Sharing a Report
Power BI Apps (5 pages) What are Apps?
Creating Apps Starting to Create an App ; Configuring how Your App will Look ; Configuring your Navigation Menu ; Configuring Access to Data Sources ; Finishing Publication
Installing Apps

Advanced Power BI - Data courseware

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

Chapter Sections Subsections
Manipulating Columns (8 pages) Splitting Columns Splitting by Number of Characters ; Splitting by Delimiters ; Splitting by Positions ; Splitting by Change in Case or Character Type ; Splitting into Rows ; Retaining Quotation Marks
Merging Columns
Extracting Data Replacing or Adding Columns ; An Example ; The Possible Options
Creating Columns (10 pages) Ways to Create New Columns
Columns from Examples Step 1 – Start the Feature ; Step 2 – Show some Examples ; Step 3 – Confirm the Formula ; Step 4 – Review your Formula ; Step 5 – Understanding your Formula
Built-up Columns
Custom Columns in M M Prefixes ; Our Example – Elapsed Days ; Creating a Custom Column ; A Special Case: Last Refresh Date/time
Conditional Columns
Indexing Columns
Combining Queries (11 pages) Ways to Combine Data
Loading Multiple Files from a Folder Setting the Folder ; Combining the Files ; Setting the Template for Import ; Tidying Up the Results ; An Outline of How it Works
Appending Queries Combining Queries using Append ; Tidying up your Results
Merging Data Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Final Touches
Fuzzy Merging Setting a Similarity Threshold ; Enforce Case Sensitivity ; Using a Transformation Table
Manipulation Transforms (8 pages) Unpivoting Data Loading the Original Data ; Unpivoting the Data ; Renaming your Columns
Grouping Data Optional Step – Remove Excess Columns ; Starting Grouping ; Specifying How to Group Data
Pivoting Columns Remember Matrices ; Our Example for Pivot Columns ; Creating the Necessary New Columns ; Pivoting by Columns
Transposing Data Replacing within a Column ; Joining Rows Together
Normalising Data (6 pages) Our Example
Extracting Unique Lists Duplicating the Original Table ; Creating a Unique List of Directors/Genres
Numbering the Lists
Storing Id Numbers not Names Merging the ID Column ; Expanding the Merged Columns ; Removing Unnecessary Additional Columns ; Making Sure the Right Table Loads ; The Final Relationships ; Finishing your Data Model
Data Profiling (3 pages) Showing Profiling Data Displaying Pop-up Column Profiles ; Grouping Profiled Data
Query Dependencies
Filtering Queries (3 pages) Removing Nulls and Errors
Filtering Dates Previous or Next Time Periods ; Matching the Earliest/Latest Date ; Specific Time Periods
Error Handling (5 pages) Default Treatment of Errors Errors in the First 1000 Rows ; Errors in Subsequent Rows
Fixing Errors in a Separate Table Step 1 – Duplicate the Table ; Step 2 – Remove the Errors from One Table ; Step 3 – Correct the Errors in the Second Table ; Merging the Data Back Together
Fixing Errors In Situ Replacing Errors ; Handling with a Custom Column
Datasets and Dataflows (5 pages) Overview
Loading from a Power BI Dataset
Power BI Dataflows Creating a Dataflow (the Data Source) ; Creating a Dataflow (the Transforms) ; Saving your Dataflow ; Loading Data from your Dataflow
Row-Level Security (9 pages) Overview of Row-Level Security Limitations and Constraints
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
Switching data sources (4 pages) Changing the Data Source Manually
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
Simple Parameters (8 pages)

Download this chapter 		Parameters, Slicers and Filters Parameters Needed for this Example
Working with Parameters Accessing Parameters ; Creating Parameters ; Managing Parameters ; Loading Parameters into Power BI
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 – Adding a Quick Measure to Reference the Parameter Value ; Step 2 – Create a Card to Display the Measure
Using Parameters with Templates
Dropdown Parameters (4 pages) Our Example
Creating a Simple List Parameter
Using List Queries for Parameters Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a List Query ; Step 3 – 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 Connections (6 pages) SQL Server Dynamic Connections First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Referencing your Parameter ; 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 ; Step 4 - Changing the Worksheet Used
What-if Parameters (5 pages) Our Example The Steps to Follow
Creating and Using What-If Parameters Step 1 – Create the What-If Parameter ; Step 2 – Customise the Slicer ; Step 3 – Create a Quick Measure to Show your Data ; Step 4 – Display your Measure in a Visual
Custom Functions (14 pages) Overview
Example – Splitting Postcodes Step 1 – Getting the Initial Data ; Step 2 - Creating a Parameter ; Step 3 - Duplicate your Parameter ; Step 4 – Create a Query Based on the Parameter Value ; Step 5 - Turning this One-Off Query into a Function ; Step 6 - Understanding the Resulting List of Queries ; Step 7 – Test your Function ; Step 8 – Invoke your Function for Each Table Row ; Step 9 – Expand the Results
Example - Combining Selected Worksheets Setting Up your Base Query and Parameter ; Link the Query to the Parameter ; Creating your Custom Function ; Invoking your Custom Function ; Expanding the Results
Example – Combining All Worksheets
Combining Tables from Web Pages Getting the Table of Genres ; Getting at the URL ; Creating a Parameter ; Getting the List of Films for a Genre ; Integrating the List of Films and your Parameter ; Creating the Function ; Invoking the Function
Getting External Links
HTML and JSON (5 pages) Overview
Scraping HTML Creating an HTML.Table Function ; Expanding the Table Returned ; Getting the Readable Text Only
Transforming JSON Parsing JSON (or XML) ; Expanding the Resulting Record ; Extracting the List of Topics ; Creating the Relationship
Using APIs (10 pages) Introducing APIs
Some Simple Examples Random Dog Pictures ; XKCD Cartoons ; Displaying Images Properly
Case Study – Exchange Rates Finding an API ; Getting the URL Needed ; Turning the Results into a Usable Table ; Adding Country Codes ; Getting the UK Exchange Rate
Multiple Table APIs Importing individual Tables ; Simplifying Column Titles ; The Need for a Bridging Table ; Starting the Bridging Table ; Removing Non-Essential Columns ; Expanding the List of Films
Data Insights (4 pages) Overview of Data Insights Three Warnings
Text Analytics Detecting Language ; Extracting Key Phrases ; Scoring Sentiment
Tagging Images

DAX courseware

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

Chapter Sections Subsections
Getting Started (5 pages) Introducing DAX Where is DAX used? ; How DAX is Used 1 - Calculated Columns ; How DAX is Used 2 – Measures ; How DAX is Used 3 – Queries
The Construct-a-Creature Database The Database Tables and Relationships
Column Storage Row versus Column Storage ; Data Compression ; Implications for Loading Data
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 ; Pressing the TAB Key ; 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
Five Uses of DAX Studio Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX ; Use 5 – Getting at Internal Data
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
Trapping Errors (2 pages) Using the DIVIDE Function
Using IFERROR Generating your Own Errors using ERROR
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
Measures (11 pages) Introduction to Measures What Measures Are ; Examples of Measures
Creating a Measures Table
Creating Measures
Quick Measures Starting a Quick Measure ; Creating the Base Value ; Setting any Filtering ; Using a Quick Measure
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 Matrix ; 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 the Year as Text ; Step 5 - 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 ; Interlude - 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
Parent-child hierarchies (4 pages) What is a Parent-Child Hierarchy?
Creating 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 – Creating your Visual
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

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 - 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

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

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