Choose software▼
- .NET manuals
- Access manuals
- Business Intelligence manuals
- C# manuals
- Excel manuals
- Microsoft Office manuals
- Power Apps manuals
- Power Automate manuals
- Power BI manuals
- Programming manuals
- Python manuals
- Report Builder manuals
- SQL manuals
- SSAS manuals
- SSIS manuals
- SSRS manuals
- VBA manuals
- Visual Basic manuals
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:
- Basic Power BI courseware
- Advanced Power BI - Data courseware
- DAX courseware
- Reporting Services courseware
- SSAS - Tabular model courseware
- SSAS - Multi-dimensional courseware
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:
- 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.
- 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.