Project Botticelli

Most Popular

How to Make Enterprise Dashboards in Excel

Carmel Shows a Finished Enterprise Dashboard Made in Excel

Carmel Gunn is a Data Management Specialist from Prodata. Let her show you Excel as an enterprise-grade BI client in this short video, in which she builds a fully fledged enterprise dashboard—in under 30 minutes!—and then shares it with others, even on mobile devices, such as an iPad, iPhone, Android, or a Surface.

Presentations (PPTs)

Over 50 of the popular PPTs (not PDFs) on: Artificial Intelligence, Azure ML, Azure AI (incl Cognitive Services and Bots), Data Science, Power BI, Microsoft R and ML Servers, R in general, and advanced analytics. Includes Microsoft Ignite, Data Science Summit and the Advanced Analytics and Data Science Roadshows, as well as various keynotes and roadshows focused on the future of data and databases from global conferences from this and the past years are available here.

DAX: Calculated Columns vs. Measures

DAX Formula for Classifying Data Using a Calculated Column

Learn the difference between calculated columns and measures in Data Analysis Expression (DAX) in this free, 10-minute video by Marco Russo, a well-known book author on the subject. Calculated columns are calculated row-by-row when the content of a table is refreshed, whereas measures are computed at query time, by aggregating rows. However, they have different uses, including filtering or classifying data, so you often need to make a trade-off between purpose and performance.

MDX Basic Concepts

MDX Basic Concepts: Cubes and Tuples

MDX, or Multidimensional Expressions, has the reputation of being a difficult language, but this reputation is undeserved, even if the concepts it uses are different from those of relational databases. In this free, 29-minute video, part of our series on MDX, world-renowned SQL Server Analysis Services expert Chris Webb of Crossjoin Consulting and Technitrain introduces three basic concepts: the use of unique names to refer to objects, tuples and how they are used to return values from a cube, and sets, which are ordered lists. Understanding these ideas will give you the theoretical foundation you need to write your own MDX calculations, and queries, for SSAS and PowerPivot.

Introduction to Data Mining with Microsoft SQL Server

If you ever wanted to learn data mining, and predictive analyticss, start right here! Microsoft SQL Server comes with easy-to-use data mining tools, requiring very little formal knowledge of the subject to get started. This free data mining video tutorial is the first module, in this series, dedicated to explaining how to perform advanced analytics of your own data. In this video we explain: what is data mining, why would you use it, and how it is related to Big Data analytics, and we illustrate it with two short demos, showing Outlier Detection and Market Basket Analysis.

DAX in Action!

DAX Formula for Counting New Customers Using CALCULATE, FILTER, and COUNTROWS

See the power of Data Analysis Expression (DAX) language in BI Semantic Model (BISM) with PowerPivot and SQL Server Data Tools (SSDT) in this free, 20-minute video by Alberto Ferrari. In the demo shown in this video we create a simple data model, based on the AdventureWorks sample database. We show you how to write a challenging formula: counting how many new and returning customers make a purchase every month. If you would like to gain DAX skills, this video, first one in our series focusing on DAX, shows you what could be accomplished without even having to change the model of your data.

Azure ML: A Brief Introduction

Azure ML tasks

In order to do predictive analytics with Azure Machine Learning, you just upload, or import current or historical data, build and validate a model, and create a web service that uses your models to make fast, live predictions. This article, by Rafal, introduces these concepts, outlines the supported machine learning algorithms, and overviews the key functions of the ML Studio development tool.

Apache Hadoop, Big Data, Microsoft

Apache Hadoop technologies integrate with the entire Microsoft Application Platform on many levels. The purpose of this article is to outline some of those integration points, and to outline the possibilities of solutions and applications that this combination enables. Rafal discusses the business potential of this technology in some detail, focussing on both already-tested, and the more future-oriented applications, at all times trying to answer the question "Why should we use Apache Hadoop in conjunction with the Microsoft platform?".

What Are Decision Trees?

What are Decision Trees?

A decision tree is a tree of nodes. Each node represents an input value that makes the most profound difference to an output that you wish to study. This free 10-minute video by Rafal introduces this powerful analytical tool, and explains the concepts while analysing simple retail data in a demo.

Power View and BISM: A Short Introduction

Power View Scatter Plot (Animated Bubble Chart)

Power View, part of SQL Server 2012 Reporting Services, and the BI Semantic Model (BISM) are briefly introduced in this 10-minute video. The demo shows how to create the animated bubble chart (scatter plot) from your data.

What is the MDX Current Member Function?

Chris Webb explains what is the CurrentMember MDX function

The CurrentMember function is, probably, the most important function in MDX: understanding how it works is the key to being able to write all kinds of really useful calculations in MDX, such as time intelligence calculations. Let Chris Webb, noted authority on MDX, explain this function to you in this short, free video.

DAX: The CALCULATE Function

DAX: CALCULATE Function with ALL and FILTER

CALCULATE is the most important function in Data Analysis Expression (DAX) because it allows you to manipulate filter context, which is necessary for building real-world calculations. Let Marco Russo explain it to you in this 47-minute video tutorial that contains 5 detailed demos. You will learn: when to use CALCULATE, its syntax, how to manipulate filters at column granularity level, and how to transform a row context into a filter context. Learning these concepts will enable you to write advanced calculations in DAX, and it will make following the remaining modules of our DAX video series easier.

Microsoft SQL Server 2012 Business Intelligence (Article)

SQL Server 2012 Logo

This article, by Rafal, discusses pivotal BI aspects of Microsoft SQL Server 2012: BI Semantic Model (BISM), PowerPivot 2, Data Analysis Expressions (DAX), the future of OLAP Cubes and MDX (Multidimensional Expressions), and, briefly, it touches on the role of the Cloud for analytics.

Querying with DAX

Querying with Data Analysis Expressions using DAX Studio by Marco Russo

Data Analysis Expressions (DAX) is useful for querying data, in addition to its more common use for defining measures and calculated columns in PowerPivot or tabular models. Marco Russo explains the tools, including DAX Studio, and the syntax necessary for writing DAX queries in this 46-minute video, containing 7 detailed demos, part of our series on DAX. You will learn about the EVALUATE statement, how to control projection of data by using ADDCOLUMNS and SUMMARIZE functions, how to use the ROW function to test new measures for your data model, and how to use DAX measures within MDX queries. By learning these concepts you will be able to use DAX queries as a data source for your reports, and for Excel tables.

Geospatial Data Exploration with Excel Power View and SharePoint

Bing Map Showing Data from Excel Using Power View

Create zoomable world maps that show your data using Power View in Excel 2013, and Bing! Rafal also shows geospatial aggregations, and a SharePoint dashboard with an animated bubble chart, in this free, 16-minute video.

Dashboards and Scorecards with Microsoft SQL Server and SharePoint Server

Dashboards and Scorecards Using Microsoft SharePoint Server and SQL Server

From an attractive, composite dashboard consisting of performance management scorecards, and reports, to the highly visual PivotViewer, this video introduces the world of dashboards running on Microsoft BI platform, focussing on SharePoint Server PerformancePoint Services and SQL Server.

Introduction to SQL Server 2012 Business Intelligence (Video)

Big Data

In this first, 45-minute module of our training course introducing Microsoft Business Intelligence technologies, we focus on Microsoft SQL Server 2012. The main objective of this video is to introduce the big picture of Microsoft’s data platform engine, released in 2012. This module discusses its 10 key BI innovations: BI Semantic Model (BISM), Analysis Services Tabular Mode, PowerPivot for SQL Server 2012, Power View, Self-Service Data Alerts, Big Data and Apache Hadoop integration, Columnstore Indexes (xVelocity for Data Warehousing), Spatial Data, Unstructured Data, and, briefly, Data Quality Services. Through a number of slides, graphics, and a short demo showing Power View in PowerPoint, you will learn how to take advantage of the newest abilities of SQL Server in your own BI projects. If you would like to deepen your skills, make sure you also watch the remaining modules in this series, which focus on: the new release of PowerPivot for SQL Server 2012, Power View, and the BI Semantic Model and Analysis Services Tabular Mode.

MDX Queries

Chris Webb introduces a basic MDX SELECT statement

In most cases an MDX SELECT statement is used to retrieve data from a SQL Server Analysis Services cube. Let Chris Webb introduce this important statement to you in this 20-minute video.

Data Mining Concepts and Tools

This 50-minute video introduces the fundamental concepts of Data Mining, a powerful analytical technology. You will learn about the process of data mining and the SQL Server Analysis Services (SSAS) Data Mining architecture, and its key concepts, including: Cases, representing your data, Mining Structures, used to describe Cases, Mining Models, and Mining Algorithms, which extract patterns hiding in your data. We briefly introduce 9 of the Microsoft data mining algorithms: Naïve Bayes, Clustering, Decision Trees, Association Rules, Sequence Clustering, Neural Networks, Logistics Regression, Linear Regression, and Time Series. You will also learn about Column Content and Data Types, Discretization, and data Distributions, as you follow the module and the 5 demos shown in it.

MDX Calculated Members

Chris Webb explains three ways to create calculated members in MDX

Calculated members are the most commonly used way of performing calculations in MDX. Let Chris Webb explain the different ways in which they can be created in this 45-minute video, part of our online MDX course.

Introduction to DAX in Excel

Using Aggregation Iterators (SUMX) to Calculate Gross Profit Margins

Introduce yourself to Data Analysis Expression (DAX) by learning the syntax and the fundamental functions of this language in this 40-minute video by Marco Russo and Alberto Ferrari, world-known SSAS experts. DAX is the language used to define calculation expressions in PowerPivot. It works in Excel 2010, and it is in the core of Excel 2013. DAX is also the programming language for Microsoft SQL Server Analysis Services Tabular Models. It has a simple syntax that will be instantly familiar to Excel users, because it replicates Excel syntax wherever possible. Nevertheless, DAX also introduces many new functions, in order to express tabular and columnar concepts, which are not part of Excel workbooks—Excel is focused on cells and ranges, while DAX focuses on entire columns and tables. By watching this video, which includes 9 demos, you will become familiar with the basic syntax of DAX, and you will be ready to write your first calculations.

What is Power Query?

Chris Webb Introduces Power Query

This short, free, 15-minute video by Chris Webb introduces Microsoft Power Query and its key functionality: data extraction from different sources, cleansing, transformation, aggregation, and loading of the results.

Books about BI

This article lists books about BI, Statistics, Data Warehousing and Management, which we recommend in conjunction with our courses and seminars.

DAX Evaluation Context

FILTER and ALL DAX Function for Context Manipulation

Evaluation Context is fundamental to understanding the behavior of DAX. Marco Russo explains: row and filter contexts, their propagation through relationships, and manipulation using: FILTER, ALL, EARLIER, VALUES, RELATED, and RELATEDTABLE functions in this 50-minute video that contains 8 detailed demos. Learning these concepts will enable you to write correctly functioning formulas, and it will prepare you to fully understand CALCULATE, the most important function in Data Analysis Expressions, as well as the remaining modules of our DAX video series.

Why Cluster and Segment Data?

Cluster-based data segmentation

Clustering is a popular data mining technique, often used for segmentation. Rafal introduces it in this short video, focusing on the reasons why it is useful for finding non-traditional segments. In the demo, you will see a clustering model, and we will use it to categorise new data in Excel.

Data Mining Model Building, Testing and Predicting with Microsoft SQL Server and Excel

Data Mining Model Building, Testing, and Predicting  Microsoft SQL Server

This 1-hour-20-minute video discusses the entire lifecycle of a Data Mining Model. You will learn how to build models and mining structures, starting by creating a Data Source and Data Source View, how to train it with your data, and how to view the results. Most importantly, you will also understand how to verify a model's validity, by applying tests of accuracy, reliability, and usefulness. You will understand, and you will also see being used, such key verification techniques as: a Lift Chart, Profit Chart, Classification Matrix, and Cross Validation. Finally, you will see how to predict unknown outcomes using your model. Not only will you hear in-depth explanations, but you will also see 11 live demos, showing you all the aspects of working with Data Mining Models, including using SQL Server Data Tools (SSDT), and Microsoft Excel, for predicting (scoring) sales to future, potential customers, based on their demographic characteristics, and their shopping habits, just discovered using a Decision Tree, and a simple mining model.

Introduction to BI Semantic Model & SQL Server 2012 Analysis Services

Using SQL Server Data Tools to Create a New Tabular Project

The fourth module of this course focuses on the new BI Semantic Model (BISM), and its implementation as a tabular model hosted by SQL Server 2012 Analysis Services (SSAS) Tabular Mode. This 1 hour 20 minute video includes discussions, slides, and diagrams, and an in-depth, 35 minute demo block, showing you the key BISM and SSAS Tabular Mode principles in 13 detailed, hi-resolution demonstrations, which you can select, follow, pause and repeat at any time, by using Jump to a chapter links.

Multidimensional Analysis with Microsoft Excel and SQL Server

Multidimensional Analysis with Microsoft Excel and SQL Server

For most people, the easiest tool for opening and navigating a multidimensional (OLAP) cube is Microsoft Excel. In this short demo you can also see how to perform basic slice-and-dice analysis, drill into and filter using dimensions, and summarise trends with a sparkline.

What is Advanced Analytics, Data Science, Machine Learning—and What is their Value?

The most common question Rafal gets asked by budding data scientists is: how can I explain the value of data science to my customers?  This article explains the five key reasons for doing advanced analytics in terms of the short-term and strategic value it provides to business customers. Rafal also takes time to explain the terminology, covering the differences between data mining and machine learning, what is data science and advanced analytics, and he also explains the concept of the scientific method of reasoning.

Predictive Analysis with Microsoft SQL Server & Excel

Market Basket Analysis Using Microsoft SQL Server 2008 R2 and Excel

Predictive Analysis is an advanced form of Business Intelligence, which uses Data Mining. In this short demo you will see how Microsoft Excel makes it easy to use.

Introduction to PowerPivot for SQL Server 2012

The second module in this online training course, offers a 1 hour introduction to PowerPivot for SQL Server 2012, sometimes referred to as PowerPivot “2”. This module is of importance to those who need to model tabular data for analytics, or who simply want to explore a data set, before making a decision if it is worthwhile analysing it further. Also, if you plan on learning about BI Semantic Model, you need to know about PowerPivot “2”, as Tabular Mode of SSAS, and BI Semantic Model, make extensive use of the new PowerPivot features, extending them to enterprise needs. This module is presented as a series of slides, graphics, discussions, and 15 demos, recorded in high-resolution, so that you can follow the steps yourself.

Microsoft Business Analytics with Office 2013, SharePoint 2013 and SQL Server 2012

Microsoft Business Analytics with Office 2013, SharePoint Server 2013, and SQL Server 2012

Microsoft Business Analytics combines Business Intelligence with Data Warehousing and Big Data Analytics. This 1-hour 20-minute video by Rafal Lukawiecki introduces this concept by showing you how real-world analytics, created by a power user in Excel 2013, can be shared with others in a company, using SharePoint Server 2013, and subsequently scaled-up to the needs of an enterprise, by means of a SQL Server 2012 SP1 Analysis Services Tabular Model. Rafal covers the entire subject broadly, showing you some of the new Excel and SharePoint 2013 BI features, such as: the new Power View, Interactive Geospatial Maps, Data Models, Quick Explore, Timelines, and many more, in an extensive, 9-part, 55-minute, high-def demo, focusing on the enterprise lifecycle of user-created analytics, which forms the core of this video tutorial.

How to Calculate with Hierarchies in DAX?

Marco shows the use of hierarchies, to calculate Ratio-to-total, in DAX

Learn how to calculate hierarchical ratio-to-parent measures in DAX, and how to use them in pivot tables, with Marco Russo, BI expert from sqlbi.com.

MDX Numeric Functions and CURRENTMEMBER

Chris Webb discuses the Sum() MDX function

There are a number of important functions in MDX that return numeric values. Chris Webb explains these in this 30-minute, demo-driven tutorial, focusing on Sum(), Count(), Aggregate(), and other numeric functions like Min(), Max(), and Avg(). Importantly, however, half of this video is dedicated to the most important MDX function, CurrentMember, which is essential to writing real-world calculations, such as time intelligence.

Introduction to Power View in SQL Server 2012 Reporting Services

Introduction to SQL Server 2012 Reporting Services Power View

The third module of this course introduces the newest addition to the Microsoft Business Intelligence platform: Power View, which is part of SQL Server 2012 Reporting Services. You will hear explanations of the fundamental concepts of Power View, such as Measures, and you will see all of its key features, including data visualizations, such as the animated Bubble Charts, in 17 hi-def easy-to-follow demos. You will hear how to source data for Power View, and how to share your reports by exporting them to PowerPoint, as a presentation, with slides that literally come alive in front of your audience!

DAX Time Intelligence

Time Intelligence Semi-Additive Measures in DAX

Time Intelligence is a set of techniques for performing time and date-based calculations. It covers a broad set of calculations needed for business reporting. Let Alberto Ferrari, book author and a renowned Data Analysis Expressions expert, show you an in-depth tour of these functions, in this 1-hour video that contains 6 detailed, hi-def demos. You will learn about: calendar tables, time-based role dimensions, TOTALYTD, TOTALQTD, TOTALMTD, and the CALCULATE function used with time-oriented, predefined and custom filters, and dealing with semi-additive measures, including last-day, or last-non-empty aggregations.

What is Time Intelligence?

TOTALYTD DAX Time Intelligence Function

Time Intelligence means doing calculations over periods of time or dates. Let Alberto Ferrari show you how to use TOTALYTD to compute Total Sales Year-to-Date in Excel in this 2-min video.

What is a Subselect in MDX?

Chris explains the MDX Subselect

Chris Webb introduces the subselect in this short 3-min video. It uses an MDX SELECT statement instead of a cube’s name, and it allows you to pre-filter the members on a hierarchy before you run a query.

Hierarchies in DAX

Marco Russo explains Parent/Child Hierarchies in DAX

Hierarchies, including parent-child ones, can be created using a combination of a few DAX formulas and a data model in Power Pivot or SSAS Tabular. Marco Russo, BI expert from sqlbi.com, shows you how to work with them in this 55-minute video. Well-implemented hierarchies can greatly enhance navigation and usability, so learn how to implement their calculation logic by watching this demo-rich tutorial.

Many-to-many Relationships in DAX

Alberto shows DAX formula for handling a many-to-many relationship

Learn how to use many-to-many relationships in DAX by applying formulas which correctly handle filter context propagation over relationships in this short, 3-minute video by Alberto Ferrari.

PI: Performance Intelligence

What is the purpose of Business Intelligence? Wouldn't the term Performance Intelligence make more sense, especially if we consider that BI is perfectly useful for non-business domains such as: government, education, military, healthcare, and many others? This article discusses Rafal's experience working with BI as a tool for achieving better performance, even if that performance is not directly related to a financial perspective of an organisation.

What is Azure ML Classic?

Rafal shows an Azure ML experimental model

Microsoft Azure ML is a cloud-based platform for designing, developing, testing and deploying predictive models. Let Rafal introduce it to you in this 10-minute video, which shows an experiment and a predictive web service in ML Studio.

Aggregations & Cube Performance

Chris Webb shows aggregation designer and its impact on cube performance

In this final, 1-hour video of our SSAS cube design course, Chris Webb tackles the goal we all aspire to: performance, which is directly under your control when you build aggregations. You will learn about different ways to build and tune them, and how to validate if they are actually providing any benefits to your queries. You will also find out how to balance performance goals achieved while querying with any degradation of cube processing time. As usual, you will see plenty of detailed demos, even focusing on the minutiae of such aggregation design concerns as many-to-many relationships, semi-additive measures, partitioning strategies, parent-child hierarchies and MDX calculations.

HappyCars Sample Data Set for Learning Data Mining

Data Mining Structures Included in the HappyCars Sample Data Set

HappyCars is our educational sample data set, used for teaching data science and data mining. It comes with SQL Server tables containing sample data, such as Customers, NonCustomers, Sales, and CustomerActivity, plus a few utility views, amongst others. It also comes with a SQL Server Data Tools (SSDT) project, HappyCarsDM, which contains a prebuilt data source and views, and a series of Mining Structures containing Mining Models, which we explain in the videos of our online Data Mining training course. We also provide a version suitable as a SQL Azure Database, which is particularly useful while learning Azure ML. It is available, at no additional cost, to our Full Access Members, as an educational aide, helpful when following our videos.

MDX: Member and Set Functions

Chris Webb explains the Ancestor MDX function

Being able to think in sets is the key to being able to write more advanced queries and calculations in MDX. Chris Webb introduces the commonest MDX functions that return sets in this 40-minute, demo-driven video, including: .MEMBERS, CROSSJOIN(), .PARENT, DESCENDANTS( ), ANCESTOR(), .NEXTMEMBER, .PREVMEMBER, .LEAD() and .LAG(). You will need these functions for rudimentary navigation using MDX.

Spatial Reporting with Microsoft SQL Server and SharePoint Server

Spatial Reporting with SQL Server Report Builder and SharePoint Server

Spatial reports can be created using Report Builder, which comes with SQL Server. They execute in SQL Server Reporting Services (SSRS) and they can be hosted in SharePoint Server. This video shows the steps you need to take to create a simple bubble report that displays customer numbers, taken from a cube, on a map of the United States. You will also see how to share that report with co-workers using SharePoint.

Decision Trees in Depth

Microsoft Decision Trees

Decision Trees are the most useful Microsoft data mining technique: they are easy to use, simple to interpret, and they work fast, even on very large data sets. At heart, a decision tree is just a tree of nodes. Each node represents a logical decision, which you can just think of as a choice of a value of one of your inputs that would make the most profound difference to the output that you wish to study. This almost 2-hour, in-depth video by Rafal starts with an explanation of the three key uses of decision trees, which are: data classification, regression, and associative analysis, and then takes you on a comprehensive tour of this data mining algorithm, covering it in slides and detailed, hi-def demos, which you can follow. Once you try a decision tree a few times, you will realise how easy, and useful they are to help you understand any sets of data.

What is Microsoft Power BI?

Rafal shows Power BI Q&A

This short 15-min video shows how Excel and Power BI make advanced analytics fast, visually pleasing—and easy, by understanding human questions about your data. You will even see it work on an iPad!

Data Warehousing and Integration with Microsoft SQL Server

Data Warehousing and Data Integration with Microsoft SQL Server

This video outlines basic concepts of Data Warehousing and Data Integration using Microsoft SQL Server, focussing on its Integration Services (SSIS) component.

Excel BI: Basic Concepts

Basic Excel BI Concepts

Carmel Gunn, a Data Management Specialist from Prodata introduces fundamental concepts of Excel BI: measures, dimensions, attributes, and hierarchies, while explaining how to use them in your pivot tables. In the extensive, 30-min demo, you will also learn about the layout of the Excel BI tools, including the Pivot Table Fields List and its Fields Section, the Design ribbon, and even how to add a new calculated measure.

DAX Patterns: Banding, New vs Old, Many-to-many

Alberto Ferrari Explains Many-to-many DAX Pattern

Alberto Ferrari, BI expert from sqlbi.com shows how to solve common business patterns using the DAX language in this 50-minute, demo-focused video. Patterns are useful for two reasons: they let you learn advanced DAX techniques and, by adapting their code to your needs, you can use them as a quick recipe for your own scenarios. This video discusses banding, computing new vs old metrics, and many-to-many relationship patterns.

Data Science for Business

Rafal introduces the process of data science projects

Why should you care about data science? Let Rafal, who specialises in it, explain how it can help you improve your business, understand your customers and products, make your employees happier, and your own job even more satisfying, in his 34-minute video. Data science combines four data handling approaches with the scientific method of reasoning, which can guide the way in which you should run experiments before making business decisions.

Self-Service Data Mash-ups with Microsoft PowerPivot and SharePoint Server

Creating a Relationship Using Microsoft PowerPivot

This video shows data being mashed-up from multiple sources using Microsoft PowerPivot and DAX, all by the end user, in a self-service way.

Clustering in Depth

Clustering: Cluster Profiles Diagram in SSDT

Microsoft Clustering is a workhorse algorithm of data mining. It is used to explore data, segment and categorise it, and to detect outliers (or exceptions, anomalies). Each cluster represents naturally occuring groupings of your data points, grouped by their similarities, as described by their various attributes. In this in-depth, 1-hour 50-minute video, Rafal explains clustering concepts, the entire process, and all of the algorithm parameters. The detailed 12-part demo, which forms the heart of this tutorial, shows you the iterative process of clustering, explaining how to segment your own data, such as customers, or products.

Exists, MDX Solve Order, and Subselects

MDX Solve Order

This 50-min video, part of our MDX course, by Chris Webb, covers three separate, advanced MDX topics: autoexists and the Exists() function and the Existing operator, solve order, and subselects. Not only does Chris carefully explain these important topics, but he also takes time to show everything in his detailed, live demos, discussing the code, and the results.

More MDX Member & Set Functions

Chris Webb shows how to set date dimension type properties

MDX has a lot of set and member functions that make it easy to create all kinds of advanced common business calculations. Watch this full-length, 1-hour 40-minute, demo-rich video by Chris Webb to learn how to compute important business reporting metrics using MDX. You will learn about: ParallelPeriod(), PeriodsToDate(), YTD(), QTD(), MTD(), Filter(), NonEmpty(), TopCount(), BottomCount(), TopPercent(), BottomPercent(), Head(), Tail(), Generate(), StrToX/XToStr function family.

Data Science Concepts: Cases and Statistics

Rafal shows density plot using ggplot2 in R

Let Rafal, expert on predictive analytics, data mining, and machine learning introduce the most fundamental data science concepts in this 1-hour video: cases (or observations), with their inputs and predictable outputs, descriptive statistics, and the basic tools, including Azure ML, SQL, Excel, R, RStudio, and Rattle.

MDX: Previous Period Growths, Shares & Moving Averages

Chris Webb discusses Previous Period Growth MDX Pattern

In this 40-minute video by Chris Webb you will see common business calculations implemented in MDX: the relative tuple pattern, used for previous period growth calculations, percentage share calculations, and the relative set pattern, used for obtaining three-period moving averages.

Introduction to Azure ML Classic

Rafal discusses a scoring experiment design in Azure Machine Learning

This full-length, 1-hour 40-minute, in-depth video introduces every aspect of Microsoft Azure Machine Learning: tools and concepts, the processuploading datamodellingvalidating results, preparing and publishing scoring experiments and even using deployed machine learning web services by calling them from a Python application.

What is Market Basket Analysis?

Market Basket Analysis Introduced

Market Basket Analysis shows you which products are sold together with other products for reasons other than coincidence or independent popularity. Watch this 10-minute, free video by Rafal to get a better understanding of your sales patterns.

DAX AW Sample Data Set

If you would like to follow Marco and Alberto’s demos, you will find it easier if you use the same data as they do in the video. This workbook already contains calculated columns and measures created in the video. You can create new ones or remove the existing ones and creating the same ones as you have seen in the video. We offer this workbook as a free bonus for Full Access Members who are following our DAX online course.

Power Query Fundamentals

Chris Webb explains Power Query query editing steps

Let Chris Webb introduce the fundamentals of Power Query: connecting, transforming, and loading of the results, in this detailed, demo-driven, hands-on 45-minute video, which focuses on the Query Editor in Power Query.

Introducing Microsoft Power BI

Rafal introduces the components of Power BI and Excel BI

Let Rafal introduce all the key components of Microsoft Power BI in this in-depth, demo-rich 1-hour 15-minute video. You will learn about the key Power BI Sites feature and the amazing Q&A for natural language queries, and also about the cornerstone of this technology: Power Query, Power Pivot, Power View, and Power Map in Excel.

Irish Economic Crisis Visualised in Power BI

Where Did Ireland Get The Money From?

Carmel Gunn and Bob Duffy explore the Irish Economic Crisis using Power Query, Power Map, Power View and Q&A in this 40-min video. Could Power BI prevent a small country run a shocking €200bn debt?

Selecting, Filtering, and Sorting Data in Excel

Selecting, Sorting, and Filtering Data with Excel BI

There are many ways to select data in Excel: basic and advanced filtering, range selections, pivot table filtering, date and context filtering, sorting, ranking, grouping, slicing (including timelines), creating user-defined sets, and advanced data navigation. If you want to do things faster and better in Excel, make sure you become proficient with all the different ways of selecting data by watching this in-depth, 48-minute video tutorial by Carmel Gunn, Data Specialist from Prodata, and follow her many hands-on demos.

Using the Deployment Wizard

Chris Webb Shows How to Deploy a Cube Using the Wizard

Let Chris Webb, book author and a world-renowned expert on SQL Server Analysis Services (SSAS) introduce you to the world of practical cube design with this short, 5-minute video, which demonstrates how to use the SSAS Deployment Wizard. This tutorial is part of our cube design course.

Data Science Concepts: Machine Learning and Models

Rafal discusses confusion (classification) matrix and prediction thresholds

This 1-hour module, by Rafal, introduces the essence of data science: machine learning and its algorithms, modelling and model validation. Data science differs from traditional, statistics-driven approach to data analysis in that it extensively uses those algorithms for the detection of patterns that help us build predictive models. Make sure to watch this video before you progress to the ones introducing Azure ML.

Introduction to DAX

This 1-hour video introduces you to the language of DAX: Data Analysis Expressions. Marco Russo, and Alberto Ferrari, world-experts on this subject, and popular book authors, also explain how to get ready for your study, what to prepare, and what to expect in the end.

How to Succeed with Your First Data Science Projects

I have had my share of successful and failed projects since I have embarked on data science ten years ago. While I am happy to say that the rate with which I now succeed on customer projects is much better than in the past, that is not just because I know my field better. It is because I am better at setting my own and my customer’s expectations, and by being more careful in choosing the projects that I want to dive into. I would like to share some of my observations with those of you who are newer to this field. I would like to save you some frustration and to help you succeed as often as possible. Read on!

Next Year in Machine Learning, Data Science, AI and BI

ML, BI, DS, and AI Trends that Shaped 2019

The trends that shaped 2019 and predictions for the future of machine learning and analytics. Why you should abandon Hadoop, brush up on statistics and obsess less about technology.

What is Power BI?

Before you start to learn about how to use Power BI, it’s important to understand what Power BI actually is.

Designing Dimensions

Chris shows an attribute hierarchy of a time dimension

Let Chris Webb explain to you how to build dimensions: one of the foundations of multidimensional cube design, in this 1-hour video. You will also learn the concept of attributes, and their relationships, parent/child and ragged hierarchies, data aggregation, and Date dimensions.

Using SQL Server Data Tools-BI

Chris Webb Explains the Data Source Wizard in SSDT-BI

SQL Server Data Tools-BI (SSDT-BI, previously called BI Development Studio or BIDS) is the name for the Visual Studio templates used for building SQL Server Analysis Services projects, which are fully introduced in this 40-minute video by Chris Webb, expert on all things SSAS. You will learn how to create an SSDT-BI project, set its properties, connect to relational data such as a data warehouse, and how to create Data Source Views (DSVs).

Association Rules in Depth

Rafal shows association rules network dependency viewer

Association Rules is one of the most useful and well understood predictive analytical techniques, and a fundamental algorithm of classical data mining. Let Rafal teach you how to use it in SQL Server in this hands-on, detailed, 1-hour 40-minute, demo-filled video. You will learn about preparing data for analysis, making it efficient, use of the visualisers, including the dependency network viewer, and, most importantly, you will learn how to interpret the results. Rafal takes great care to explain the often confused metrics of the strengths of association: Rule Probability and Rule Importance, giving you plenty of examples to make it easier. Finally, no data mining algorithm would be complete without a set of parameters you can control, and all of them, including the very important MINIMUM_SUPPORT and MINIMUM_PROBABILITY are explained in this video.

MDX Performance Diagnosis with Profiler

Selecting relevant SQL Server Profiler events for SSAS query tracing

Watch this 30-minute video by Chris Webb to learn the steps necessary to use the SQL Server Profiler to identify performance problems in your MDX queries, and to learn about the interaction between the SSAS Formula Engine and its Storage Engine, focusing on the useful Profiler events, including Query Subcube Verbose.

Information Security

Information Security, with a focus on data protection, cryptography, and holistic security has been a long-term area of specialisation for Project Botticelli. Although most of our current work focuses on Business Intelligence, we continue to maintain our InfoSec skills and we actively apply stringent best security practices in our work.

What is Microsoft Power View?

Ben Watt shows a Power View dashboard

Ben Watt introduces you to Microsoft Power View in this short, 10-minute video.

Cubes, Measures and Measure Groups

Chris Webb shows Dimension-Measure Relationships and Measure Groups

Following on from the module on designing dimensions, Chris Webb teaches how to build cubes, measures, and measure groups, in this 1-hour 10-minute, in-depth, hands-on tutorial. You will also learn about various dimension-measure relationships, measure expressions, Distinct Counts, semi-additive aggregations, important properties such as FormatString, actions, like a drill down, perspectives and translations.

Cube Deployment, Processing and Admin

Chris introduces cube processing strategies

This 30-minute video by Chris Webb discusses the operational side of cubes: deployment, processing, backup and restore, synchronization, and scripting.

Power View in Depth

Ben Watt explains how to build enterprise dashboards using Power View in Excel

Following from his quick intro to Power View, Ben Watt presents a 50-minute in-depth tutorial leading you through all of the features of this data exploration and reporting tool. You will learn how to connect to data, use filters, all of the visualisations, how to format maps and how to share your reports using Power BI.

Cube Security

Chris explains how to create dynamic cube security

In this 30-minute video, part of our Cube Design series, Chris Webb explains SQL Server Analysis Services security. You will learn about administrative and data security, the latter on three levels: at cube, dimension hierarchy, and at cell levels.

Code and Data Samples (R, R Services, SSAS)

Download code and the data for a few of the demos used in Rafal’s Practical Data Science courses, including: classifier performance in R, mortgage default logistic regression and the 10 million row data set, cross-sell and recommendations using Association Rules in SQL Server Analysis Services Data Mining.

Lab Exercise Files and Presentation PDF

The Mastering DAX Workshop course includes lab exercises that you can (and should!) complete while you follow the course videos. In total, you get over 700M worth of data to work with. Each lab is introduced at the end of the relevant video module. In this section, we provide the complete exercise book and a single ZIP file including exercises for all the modules. You can also download a PDF with the slides used in the video course.

What is Artificial Intelligence?

What is Artificial Intelligence?

This video introduces the history of Artificial Intelligence (AI) and explains how it can be implemented using Machine Learning.

What is Power BI?

What is Power BI?

Before you start to learn about how to use Power BI, it’s important to understand what Power BI actually is.

What is Artificial Stupidity?

What is Artificial Stupidity?

Artificial Intelligence of today easily becomes Artificial Stupidity. This short video explains its dangers, and suggests when it is OK to use AI.

Microsoft Machine Learning Technologies: View Towards 2020

New Azure Machine Learning: Performance Metrics

What is new in the new Azure Machine Learning? What is changing in the rest of the Microsoft machine learning platform, notably ML Server and SQL Server ML Services, the 2019 Big Data Clusters, and frameworks: Automated ML, ML.NET, MicrosoftML, RevoScale, and MLLSpark? You will also see an overview of the popularity of non-Microsoft tools in this video—make sure to read the essay, under the video, too.

Working with Iterators

Many DAX functions, including all the aggregations, iterate a table executing a DAX expression for each row, in a specific row context. Using these functions, called iterators, it is important to control the granularity of the calculation in case a context transition is executed for each iteration. In this module, you will see different examples that explains how to control the granularity of an iterator, so that you can obtain the expected result.

Connecting to Data Sources

There are three ways that you can work with data in Power BI Desktop. First of all, the most commonly-used method is to import data into a dataset. This involves copying data from your data sources into Power BI’s own internal database. This gives you the fastest possible query performance and the ability to use all of Power BI’s features but it also has some downsides too: refreshing data can be slow, you have to refresh data regularly so that it stays up-to-date, and there are limits on the amount of data you can import into a dataset.

Table Functions

The learning goal of this module is to introduce you to the notion of table functions and to quickly describe the most important ones, but not to provide a detailed explanation of all the functions yet, as that happens in the later modules. Above all, you will see the interaction between tables and relationships and how to create calculated tables.

Data Refresh

After a report has been published you will want to ensure that the data in that report stays up-to-date. For reports that use DirectQuery data sources or Live Connections, your data will always be up-to-date without you needing to do anything. Reports that use imported data, though, will need to have the data behind them refreshed on a schedule.

Power BI New Features and Course Updates

Power BI New Features and Course Updates

This short video explains how we have updated the Power BI course modules with the new features that were added during 2018.

Querying with DAX

You can use DAX to create calculated columns and measures, but you can also use DAX as a query language with the help of specific tools such as DAX Studio. The query syntax is based on the EVALUATE statement, which materializes to the client the result of a table expression. For this reason, after learning how to write DAX queries using EVALUATE in DAX Studio, you will learn a number of table functions useful in both queries and other DAX expressions.

The Future of Power BI

The Future of Power BI by Chris Webb

This video contains some thoughts about what 2019 holds for Power BI, based on various announcements made by Microsoft during 2018, especially its focus on enterprise features.

Data Model for Irish Economic Crisis Video

You can examine the crisis line and other economy metrics discussed in Bob and Carmel’s Irish Economic Crisis Visualised in Power BI video using the these two Excel 2013 workbooks. One contains the full data models with all the relationships and most source data, DAX calculations and Power View visualisations, while the other one has been optimised to work well with the Power BI Q&A feature, making good use of its synonyms feature. We offer these workbooks for Full Access Members only.

Power View Excel Demo Worksheet

If you would like to use the same Excel worksheet as the one shown in Ben’s Power View videos, please get it here. It is available for Full Acess Members.

Administration and Auditing

If you are a Power BI administrator you will have access to the Admin Portal in the Power BI site. This contains a number of settings that control how Power BI features behave, or even whether those features are available to users at all.

Machine Learning for Security Applications: Why?

Hugh Simpson-Wells Interviews Rafal Lukawiecki about Machine Learning

This interview with Rafal Lukawiecki and Hugh Simpson-Wells discusses applications of machine learning for IT security purposes, including: authentication, authorisation, log analysis, and breach prevention.

CALCULATE Function

CALCULATE is by far the most important, useful, and complex function of the DAX language. In reality, the function itself is an easy one. It only performs a few tasks, but the number of scenarios where CALCULATE is necessary, along with the complexity of the formulas that can be written with CALCULATE, make a full chapter absolutely necessary.

DAX and the Data Model

DAX is the query and calculation language of the Power BI Data Model. DAX can be used to create three types of object in Power BI Desktop: calculated columns, which add new columns containing derived values to an existing table; measures, which control how data aggregates up in reports; and calculated tables, which are tables derived from other tables loaded into the Data Model.

Transforming Data with the Query Editor

Once you have connected to your data sources you can use the functionality of the Query Editor to transform it so that it is in the format you need it for Power BI - which is typically a dimensional model.

 

Modelling Data

So far we have seen how the Query Editor can be used to generate tables of data - after this point, these tables are loaded into the Power BI data model, the internal database that stores all imported data in Power BI, and into a dataset.

Evaluation Contexts

At this point in the course, you have learned the basics of the DAX language. You know how to create calculated columns and measures, and you have a good understanding of common functions used in DAX. With the knowledge you have gained so far, you can already create many interesting reports, but you will need to learn evaluation contexts in order to create reports that are more complex.

The Future of AI—How to Avoid Artificial Stupidity

The Future of AI—How to Avoid Artificial Stupidity

In this detailed video I explain how to get started in building not just good AI, but, above all, the kind that avoids the risks of artificial stupidity, by combining several powerful approaches: machine learning, programming in logic, and modern data.

Power BI Premium and Power BI Embedded

Power BI Premium and Embedded

Most users of Power BI will be using it with Power BI Pro licences, but there are other options, and this video explains what Power BI Premium and Power BI Embedded are and when you would want to use them.

Connecting to Data Sources

Connecting to Data Sources

There are three ways that you can work with data in Power BI Desktop: import data, Live Connection, and Direct Access.

Time Intelligence in DAX

In this section, you will learn how to implement common date-related calculations such as year-to-date, year-over-year, comparisons over years, running total, and moving annual total.

Machine Learning for IT Security: From ML to Security AI

Security Artificial Intelligence

This video explains how to get started with machine learning for IT security purposes, leading to powerful, potentially dangerous, autonomous Security AI systems.

Advanced Data Loading

Everything that happens in the Query Editor is translated, behind the scenes, into a language called M. M is a functional language designed specifically for data loading and transformation.

Data Import Concepts

Importing data into Power BI happens inside the Query Editor. A task to import data from one or more data sources into Power BI is called a Query, and a Query consists of one or more Steps, each of which either connect to a data source or transform the data in some way.

 

Power BI Desktop vs Excel

Once you have loaded data into Power BI and modelled it, it is time to build reports. Power BI Desktop is the obvious choice, but you can also use Excel. Power BI Desktop is the best choice when you want to build eye-catching reports, reports that work well on mobile devices, and reports with a somewhat static structure. Excel is the best choice when you just want to explore data, or if you want to build financial-style reports.

Cloud and Desktop Power BI Dashboards and Reports

When you start to build a report in Power BI Desktop you are given a single, empty page to work with. Before you start building your report, though, you may want to configure the properties of the page that govern its size and shape, and how it expands and contracts to fill different sized screens. There is also the option of creating a separate layout of your report for viewing on a phone.

Evaluation Contexts and Relationships

After you have seen the basics of evaluation context and the CALCULATE function, it is time to analyze how the relationships of a data model affects their behavior. You will learn how to transfer a row context following relationships, and how the filter context automatically propagates through relationships.

DAX and the Data Model

DAX and the Data Model

DAX is the query and calculation language of the Power BI Data Model. This video introduces its fundamental concepts: calculated columns, measures, and calculated tables.

Modelling Data

Modelling Data

This video explains how to model data using tables, relationships, hierarchies, and it covers important rules you need to follow, including restrictions on keys.

Advanced Relationships

In this final section, you will learn how to handle complex relationships between tables by leveraging the DAX language. The goal is not to just give you pre-built patterns that you can use in your model. Instead, we want to show you unusual ways of using DAX to build complex models, to widen your idea of relationships, and to let you experience what you can achieve with DAX formulas.

Connecting to Data Sources (2018 Updates)

Connecting to Data Sources (2018 Updates)

This video contains new content that is related to the Connecting to Data Sources section, and covers new features that have been added in late 2018.

Administration and Auditing

Administration and Auditing

This video introduces the Admin Portal in the Power BI site. It controls how Power BI features behave, and which are available to your users.

Data Import Concepts

Data Import Concepts

Importing data into Power BI happens inside the Query Editor. A task to import data from one or more data sources into Power BI is called a Query, and a Query consists of one or more Steps, each of which either connect to a data source or transform the data in some way.

Licensing

Licensing

Power BI offers a number of different licensing options, all of which are discussed in this video.

Data Refresh

Data Refresh

This short video explains how to keep your Power BI report up-to-date if you are not using DirectQuery or Live Connections—imported data needs a scheduled refresh.

Hierarchies in DAX

In this section, you will learn how to create calculations over hierarchies and how to use DAX to transform a parent-child hierarchy into a regular hierarchy managed by the data model, hiding levels that should not be visible in a ragged hierarchy.

Building Reports in Excel

There are two ways to connect Excel on the desktop up to data stored in Power BI: you can use the Analyse in Excel feature that was shown in an earlier section, or you can use the Power BI Publisher for Excel add-in which is probably easier to use for most users. Once you have created the connection you can use an Excel PivotTable to browse data in Power BI.

Transforming Data with the Query Editor

Transforming Data with the Query Editor

Once you have connected to your data sources you can use the functionality of the Query Editor to transform it so that it is in the format you need it for Power BI - which is typically a dimensional model.

Transforming Data with the Query Editor (2018 Updates)

Transforming Data with the Query Editor (2018 Updates)

This video covers new features in Power BI that are relevant to the Transforming Data with the Query Editor section of the course.

Building a Date Table

Almost all data models need to analyze data over time. The first step for handling date calculation is to create a Date table, which simplifies any date-related calculation. Because of its importance, you should be careful when you create a Date table.

Power BI Reports (2018 Updates)

Power BI Reports

This video covers new features in Power BI that have been released during 2018 and that are relevant to Power BI report design.

DAX and the Data Model (2018 Updates)

DAX and the Data Model (2018 Updates)

This video introduced new features in Power BI that have been added in 2018 and that are relevant to the DAX and the Data Model section of the course.

Advanced Data Loading

Advanced Data Loading

Everything that happens in the Query Editor is translated, behind the scenes, into a language called M. M is a functional language designed specifically for data loading and transformation.

Modelling Data (2018 Updates)

Modelling Data (2018 Updates)

This video discusses new features in Power BI that are related to the Modelling Data module of this course.

Azure Analysis Services

Azure Analysis Services

This video introduces Azure Analysis Services: a cloud-based version of SQL Server Analysis Services, which is essentially the same database engine that Power BI uses but available as a separate service, enhanced with sophisticated development tools and a rich security model.

Power BI Desktop vs Excel

Power BI Desktop vs Excel

Once you have loaded data into Power BI and modelled it, it is time to build reports. Power BI Desktop is the obvious choice, but you can also use Excel. This video explains both approaches.

Cloud and Desktop Power BI Dashboards and Reports

Cloud and Desktop Power BI Dashboards and Reports

This video introduces report building in Power BI, both for desktop, cloud, and mobile use.

Sharing, Distribution, and Security

Sharing, Distribution, and Security

This video shows different ways to share and secure Power BI reports.

Building Reports in Excel

Building Reports in Excel

This video shows the Power BI Publisher for Excel add-in to connect Excel on the desktop to data stored in Power BI, and the Excel Cube Formulas, which are more suited for highly-structured reports.

Advanced Filter Context

In this section we are going to uncover all the complexities of evaluation contexts and show many examples of expressions that look wrong at first sight, but only because you do not have a full understanding of how they work. Learning these detailed topics is mandatory if you want to fully understand DAX and learn how to use it in the more complicated scenarios.