This course teaches SQL Server Integration Services (SSIS) developers how to develop packages to perform extract-transform-load (ETL) operations for a data warehouse by using Business Intelligence Markup Language (Biml) as an alternative development approach.
It’s generally accepted that creating and maintaining ETL packages with SQL Server Integration Services (SSIS) is one of the more time-consuming steps in the data warehouse development process, but what if there was a way to save time that can be used to solve bigger problems or expand the data warehouse scope? Rather than spend seemingly endless hours working through repetitive SSIS package development tasks and more hours tweaking those packages as schemas evolve, you can use Biml to create a framework that gracefully adapts to changes and updates package designs in minutes. This course introduces you to the Biml language by progressively building out files to support a data warehouse environment until you have a complete solution for staging data and loading the staged data into target dimension and fact tables.
Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections
conferences, and various SQL Saturday and Microsoft-related events
Introducing Biml Hi. I'm Stacia Varga. Welcome to the Building Blocks of Biml course. This is module 1, introducing Biml. What exactly is Biml and is it worth your time to learn yet another language to get your job done? Is there really a better way to create and manage integration services packages? These are the questions I'll begin to answer in this module and by the end of this course, I hope you'll agree that the answer is a resounding yes to each of these questions. As for myself, I've been working with Biml for a couple of years now and I cannot imagine developing packages any other way now that I've learned to use Biml effectively in my business intelligence projects. Before we get started learning about the language, I think it's helpful to start with a background of Biml. So in this module, we'll start by reviewing a history of Biml. We'll learn who created it and why and then we'll look at some of the bigger problems that Biml can solve for you. Last, we'll explore the various tools that you'll need to start using Biml.
Creating Your First Biml Files Hi. I'm Stacia Varga. Welcome to module 2 of the Building Blocks of Biml course, Creating Your First Biml Files. In this module you learn how to get started with Biml by producing some simple packages that illustrate basic concepts that are necessary to learn first. We'll start by examining the structure of a Biml file so that you understand what you need at a minimum to create a package. Then we'll take a detour to explore online documentation so that you know where to look for additional information as you build out packages. IntelliSense does not provide you with all the intelligence that you need to properly define tasks and components. Much of the time you'll be able to reuse your own code because you probably use the same tasks over and over again, but you'll find the documentation useful when you need to define a task that you haven't encountered before in Biml. Then we'll go back to building out Biml files using some of the simpler and more common elements that represent tasks and we'll add attributes to some of these elements. If you're not familiar with these xml terms now, don't worry, you'll see exactly what they are and how to use them in the last section of this module.
Exploring Basic Biml Syntax in the Control Flow Hi. I'm Stacia Varga and this is module 3 of the Building Blocks of Biml course, Exploring Basic Biml Syntax in the Control Flow. In this module we're going to create Biml files that do more than we've seen in the previous modules. In fact, we're going to build packages that perform common data integration tasks as part of a data warehousing solution much like the patterns introduced in my Integration Services courses here at Pluralsight. We'll build on the concepts we learned in the earlier modules of this course and learn a few new things such as Biml objects that we use in the control flow to manage the order of operations and how some characters cause problems in Biml and the special characters we can use instead and we'll revisit the ExecuteSQL task by learning additional Biml to perform more advanced operations than we learned in the previous module.
Exploring Basic Biml Syntax in the Data Flow Hi. I'm Stacia Varga and this is module 4 of the Building Blocks of Biml course, Exploring Basic Biml Syntax in the Data Flow. In this module we focus on the data flow task and learn about working with Biml objects that produce data flow components and manage the pipeline. Most of the packages that we build to support data warehouse etl processes require us to move data from one place to another using the data flow task. Now when we're working with the data flow, we typically have at least two components, a source from which we extract the data, and a destination into which we load the data, and possibly we include transformations to change the data in some way, before we load it. In the data flow, we might need to override the default behavior of the pipeline path and when data won't transform or load properly, we might need to send it to an error output. In this module, we'll use a flat file as an error output so we need to learn how to define a connection and a destination to store data in a flat file, so let's take a look at how we work with these components in Biml.
Introducing Biml Automation Hi. I'm Stacia Varga. This is Introducing Biml Automation, module 5 of the Building Blocks of Biml course. In the previous modules of this course we've learned how to build Biml line by line to produce packages that we want, but frankly it's not much less tedious to copy and paste Biml the way we've been working so far than it is to work directly with the designer. In this module you're going to see that the real power of working with Biml comes from introducing automation with BimlScript. We'll start by learning what BimlScript is conceptually and then we'll review the components of BimlScript and then we'll learn various techniques for troubleshooting our BimlScript.
Automating Biml with Multiple Files Hi. I'm Stacia Varga. Welcome to module 6 of the Building Blocks of Biml course, Automating Biml with Multiple Files. In this module we'll expand our use of BimlScript and really start to see the power of working with this language in a variety of ways. First, we'll explore the RootNode object which allows us to work on an in-memory representation of our relational model and Biml objects. We can then automatic Biml development in a way that adapts to our environment by using multiple files to build and use the RootNode object. The way we automate Biml development is by using package templates that iterate through the RootNode objects and fetch object properties that we use to build definitions for the packages we need. Another option for using the RootNode object to automate Biml development is the GetBiml method. It's also useful as a troubleshooting tool, which we'll see how to do. Next we'll learn about using include files to consolidate and share common information such as variables and custom functions across our Biml files and then we'll try out the CallBimlScript method as another technique for consolidating commonly performed operations into separate files so that we can build a routine once and then reuse it many times.
Building Biml for Data Warehousing - Part 1 Hi. I'm Stacia Varga and welcome to the next to last module of the Building Blocks of Biml course, Building Biml for Data Warehousing, Part 1. In this module we'll combine the concepts from the previous modules with a few new concepts that allow us to build a simple, but end to end solution for a data warehousing workload and we'll continue learning about this in the next and final module of this course. In this module we'll start by reviewing the framework of the data warehouse that we'll be populating by using Biml. Then we'll review how we can generate staging tables based on metadata that we get from the source system, and next we review and implement a pattern that we can use for each extract package that we need to create in our solution so that we can pull data from a source database into the staging tables.
Building Biml for Data Warehousing - Part 2 Hi, I'm Stacia Varga and welcome to the last module of the Building Blocks of Biml course, Building Biml for Data Warehousing Part 2. In this module we will finalize all of the things that we've learned so far and add in a few new concepts so that by the time we're finished we have a simple end-to-end solution for data warehousing. In this module, now that we have the data staged, we can create load packages that move data from our staging tables into the target dimension and fact tables. And last we'll use the metadata from the extract and staging packages to produce master packages that orchestrate the entire process from beginning to end. When we finish this module we'll have a complete Biml solution that in turn creates a complete extract and load solution for data warehousing.