SQL Server MVP and Business Intelligence Architect Bill Pearson leads this hands-on, intermediate-level collection of topics surrounding the use of SQL Server Analysis Services 2012 Tabular ("SSAS Tabular") and Data Analysis eXpressions (DAX)to deliver corporate-level business intelligence solutions.
SQL Server MVP and Business Intelligence Architect Bill Pearson leads this hands-on, intermediate-level collection of topics surrounding the use of SQL Server Analysis Services 2012 Tabular ("SSAS Tabular") to deliver corporate-level business intelligence solutions. Beginning with intermediate Data Analysis eXpressions (DAX), we move into intermediate SSAS Tabular data modeling. We then work with time and dates in the model and introduce the powerful Time Intelligence functions, which debuted in DAX and have no formal Excel counterpart. Finally, in a capstone module comprised of assorted solutions to common challenges we find in the business environment, we reason within the context of SSAS Tabular and DAX to develop successful outcomes. This course applies equally to SSAS Tabular 2012 and 2014.
Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, etc.) in regular conversions of these once-dominant applications to the Microsoft BI stack.
Introduction and Foreword Welcome to Organizational Business Intelligence with SQL Server Analysis Services 2012 Tabular - Intermediate Concepts. I'm Bill Pearson, and this is the Introduction and Foreword. This course continues an SSAS Tabular Basics course that I also have out on Pluralsight. We're going to talk first about for whom the course is intended, the course objectives, course modules, assumptions, and getting help with installation. This course is intended primarily for architects, developers, designers, and consumers working within the realm of analysis and reporting with a need or desire to learn more about SSAS Tabular and the opportunities and challenges it presents. The course is delivered from the unique perspective of a career business intelligence architect, myself, who has found Analysis Services Tabular to be a revolutionary, robust environment for crafting and operating scalable enterprise business intelligence solutions.
Intermediate Data Analysis eXpressions (DAX) Organizational Business Intelligence with SQL Server Analysis Services 2012 Tabular, Intermediate Concepts. I'm Bill Pearson, and this is Intermediate Data Analysis eXpressions. We'll begin the module with a quick review of DAX basics. This will be a reprise of several points we discussed in the Pluralsight basics course. We'll then move into general considerations and tools. We'll talk about query language options, query tools options, and considerations surrounding DAX versus MDX as query language choices. We'll then dive into DAX queries themselves introducing the EVALUATE clause, which is common to all queries and look at general DAX query structure. We'll then talk about controlling query output, and this from three perspectives, from the perspective of rows, the perspective of columns, and the perspective of groupings. Next we'll dive into special output needs, things like Cartesian products, scale or values, and lookup operations. Finally we're going to talk about working with inline measures, why we create measures within queries versus the Tabular model itself, the syntax for defining an inline measure, and the basic rules involved.
Intermediate Modeling in SSAS Tabular Organizational Business Intelligence with SQL Server Analysis Services 2012 Tabular, Intermediate Concepts. I'm Bill Pearson, and this is Intermediate Modeling in SSAS Tabular. Our objectives within this module are two-fold. First we're going to overview modeling within SSAS Tabular. We're going to review each of two basic models with which we'll work throughout the module with our examples. We'll look at all the different pieces that make up a Tabular model more or less as an overview of things that we covered in the basics course that I did for SSAS Tabular at Pluralsight. We're going to also deploy and process the models and discuss a lot of steps that are involved with that sort of thing. Next we're going to move into intermediate modeling in SSAS Tabular. We're going to do a quick overview and then talk about a calculations dimension, ratios, ranked bucketization, and allocation.
Time and Dates in SSAS Tabular (Part I) Organizational Business Intelligence with SQL Server Analysis Services 2012 Tabular, Intermediate Concepts. I'm Bill Pearson, and this is Time and Dates in SSAS Tabular (Part I). With this module, we'll begin a two-part examination of working with time and dates in SSAS 2012 Tabular. With an overview to begin, we'll talk about time/date analysis in general. We'll talk about why we do it, what we hope to accomplish from it, the levels of intelligence that might be involved, the structure needed to support it, things of this nature. Then we'll talk about two distinct groups of functions in DAX. We'll talk about time/date functions, the basic functions, and then we'll talk about time intelligence functions, which are more sophisticated and heavily relied upon structure and some other things. These two are often mixed and garbled in a lot of the literature out there, and I'll try to break out how they're different and why time intelligence functions are far more sophisticated than basic time/date functions. Next we'll talk about the heart of time/date support, which is the date table. We'll talk about the components that need to be therein, how we work with it, and so forth. We'll work with time and date data with basic DAX functions. We'll perform an overview and general discussion of the functions, look at the different functions, and then select several popular functions and look at them at work, working examples. Finally, we'll do the same thing with the DAX time intelligence functions, part 1, which will flow over into the next module where we'll look at DAX time intelligence functions and examine several representative time intelligence functions in action.
Time and Dates in SSAS Tabular (Part II) Organizational Business Intelligence with SQL Server Analysis Services 2012 Tabular, Intermediate Concepts. I'm Bill Pearson, and this is Time and Dates in SSAS Tabular (Part II). In this module we're going to resume with the DAX time intelligence functions where we left off in Part I. We'll perform an overview where we'll really kind of review what we discussed already in Part I, that'll be pretty brief, and then we'll move into more DAX time intelligence functions. Throughout the section, we're going to examine several representative time intelligence functions in action just like we did in Part I. We'll be looking at it from two perspectives, each of those functions. We'll be looking at them within the SSAS model itself inside SQL Server Data Tools where we can go in and look at how things are constructed, and then we'll also look at them from the user's perspective, you might say, in pivot tables. Next we'll take up time intelligence calculations for common business needs. This is almost like a set of recipes just to kind of bring everything together and show how we meet very common business needs out in the model with our time intelligence calculations that we've set up. We'll talk about To-Date aggregations, we'll talk about prior and parallel period calculations, alternative calendars, and moving or rolling aggregations. More detail about what exactly those are when we get there.