Data Loading Options.
So earlier in this series, we looked at how to load
dimensions.
We saw how to work with slowly changing dimensions to
maintain historical accuracy, and we saw that loading
dimensions wasn't bad, as far as performance goes, because
dimensions are generally small.
This Nugget, we're going to focus on fact tables.
Fact tables are generally huge.
So we've got a whole new set of challenges to deal with.
How do we load an entire set--
a full data load, as we call it-- into our fact table?
How do we deal with incremental loads-- loads that
we only want to track changed data and new data
since the last load?
How do we deal with those challenges, and the big one,
how do we deal with performance?
Especially when we're dealing with an extremely large data
warehouse, loads could take a day.
So we obviously don't want to bring down
production for a day.
So I'm going to show you a really nice design pattern and
a couple of tips and tricks for dealing with performance,
and that's going to come in the form of something called
partition switching, which is a really, really cool feature
that was introduced in SQL Server 2005, that we'll get a
good demo on here so you can see how it works.
So we'll start with a whiteboard on what data
loading is all about.
So we'll talk about the full load or the initial load,
we'll talk about incremental loads, what those entail, and
there's a lot of different strategies for doing an
incremental load.
Much of it is going to depend on your source data.
So we'll take a look at what some of those variables are.
Then we'll also talk about partitions switching.
So we'll kind of get a good overview of everything that
this Nugget is going to cover on paper, and then we'll jump
into the Virtual Nugget Lab and put it all to use here.
We'll start with incremental and full loads.
I'll show you how to do a full load, we'll create a full load
package, we'll create an incremental load package, one
that uses both Dynamic SQL and one that uses CDC.
change data capture, a feature that was introduced in SQL
Server 2008 that we can use to help us
track all these changes.
And I'll get you familiar with the SSIS components, the CDC
control task, the CDC source.
We'll look at the CDC splitter.
So I'll give you a good run down on a package that uses
all of the CDC functionality.
It's pretty cool stuff and pretty easy to use.
And finally, as I mentioned, we'll look
at partition switching.
A great way to speed up our data warehousing loads.
And by the way, this isn't something you'll need to know
in great detail for the 70-463 exam, but it's certainly
something you'll want to be aware of, especially if you do
this in the real world.
And by the way, I remember when SQL Server 2008 came out,
Microsoft put a pretty cool article out there on how they
were boasting that they loaded a terabyte of data in 30
minutes using SSIS and partition switching.
So it's kind of funny, because we're going to do the exact
same thing that they did, just on a much smaller scale.
But you'll still get to see a good demonstration on how to
do partition switching, because it's
awesome stuff here.
It really is as good as it gets as far as loading data as
fast as possible in SQL Server.
Let's start here with the data loading overview, and I want
to get you familiar with what we're going to accomplish in
this Nugget and what we're going to work with, as far as
our data warehouse and our OLTP databases go.
So, in the Adventure Works Data Warehouse, we have this
data mart that deals with the internet sales.
So we're going to work with the FactInternetSales table,
and this data is modeled after many tables in the Adventure
Works database.
Most of them are going to sit inside of the sales schema.
Sales order header is where most of our data is
going to come from.
And there's a lot of other linkages that go on this.
We'll take a look at it when we get into our demo and show
you the big honking query going to use that pulls all
this data together, and that's the query we're going to use
to push that data into our FactInternetSales table.
So we're going to start this process from the beginning.
I want to show you this from scratch, that way you can get
an idea here of how you would load a fact table.
So, the first thing we're going to do is we're going to
empty out this fact table.
We're going to say this is the very first time we're loading
this fact table.
How do we do it?
And we're going to do it by creating a full load, an SSIS
package that is responsible for initially loading our fact
table, and it's good to have that package around in case we
ever need to do a full load again.
From there, we'll learn how to build an incremental SSIS
package, one the does incremental loads.
Incremental is anything that has changed or been added to
our source since our last load.
How do we figure that out?
That's really going to depend on your source data.
For instance, see this modified date down here?
That's a good thing.
If you have a modified date in your source, it's going to
make things a little bit easier, because we know what's
been changed when.
Now, even easier, if we have that same modified date over
in our fact table, because now, we can create a package
that the first thing it does is a look up into our
FactInternetSales table.
We can just run a simple aggregate query.
We don't have to actually do a look up.
And by the way, look ups are bad against your fact tables
in SSIS, and I'll talk a little bit more about that and
why we want to do set-based operations here shortly.
But in this scenario, we could run a quick aggregate to give
us a max on the modified date, which will be the starting
point for our query that hits our source.
We'll do anything after that date, and that will give us
all our changed records.
Finding all the new records is easy, because we can do the
same thing.
We can get a max on, say, our product key, and that's going
to be the beginning, we'll do greater than that key against
our source, and that will give us all of our new records.
So if you have modified dates in both places, it makes
things very easy for finding incremental and new data.
What if we didn't have it inside of our destination,
inside of our fact table?
Well in that case, we could create our own table.
Maybe we just stuff this inside of our audit
information, so every time the package runs, it'll log the ID
of the run, and then the modified date, the last
modified date.
So instead of that being stored in here,
it's stored in here.
And every time we ran this, then we would grab the last
modified date out of this table to use inside of our
package as the starting point.
So there's ways around that.
Now, the tricky part comes as what if we didn't have a
modified date in our source or our destination?
Now how do we figure out what records have changed?
Well, we have something called CDC, the changed data capture.
This was introduced in SQL Server 2008.
It's really nice.
It's really just SQL Server's way of tracking inserts,
updates, and deletes against a table.
Once you enable CDC on a database, it installs a bunch
of system tables and system objects that SQL Server will
then use to track any change data.
And we have CDC tasks and transformations that we can
use in SSIS to work with this changed data.
And it's actually kind of neat, because in the data
flow, we have a CDC source to hook into and grab all that
changed information.
We also a CDC splitter, which will take all that changed
data and throw it down it's own data flow.
So you have an insert data flow, an update data flow, and
a delete data flow that you can do whatever you want with
on those flows.
Another important aspect to loading up fact tables is
performance.
There's a couple of things we can do on the performance
side, partitions switching is going to be the big one, I'll
talk about it in a second, but also set-based operations.
Set-based operations is really just using SQL, using inserts
with selects and joins to take data from a source and quickly
put it into a destination.
It's all using SQL, because it's not row by row processing
like we do inside of our data flow.
And if you remember this, a few Nuggets ago, we talked
about how to load up a data warehouse.
Say we have our source database, we have a staging
database, and then we have a production database.
So we want to load up our staging using SSIS in whatever
means possible.
We can use look ups here and such, because we're not
hitting our production database.
We're loading it into a staging area, so we can take
all the time we need.
And then, from our staging to production is where we want to
use a set-based operation, because when we're going into
production, we want to load it as fast as possible.
We want to impact production as little as possible.
So I'll show you how to do exactly this first.
We will create a full and incremental load that uses all
of these scenarios and loads up our
staging data using SSIS.
Then, we'll take staging, we'll push it into production
using a set-based operation.
We could even take that a step further if we incorporate
partition switching into our strategy, because what this
allows us to do is take data from staging, put it into
production.
Rather than doing a set-based operation, we just simply do a
partition switch.
Now, in order for that to work, these need to be inside
of the same database.
And that's not a problem.
We'll put our staging tables right inside of
our production database.
We'll call it FactInternetSales_Staging,
we'll load that up, and then, in order to get our data from
staging to our production table, we'll just simply do a
partition switch, which is an instantaneous operation
because all you're doing is switching the data pointers
from one partition to another partition.
The best way to understand partition
switching is with an example.
So let's start at the beginning here.
Let's say that we wanted to partition our
FactInternetSales table.
The first thing we would do is create a partition function.
And let's say we wanted to split it up by year based on
the order date.
So we've got an order date inside of this table, so we
create a partition function on that order date column, and we
specify our ranges.
So let's say that this is our 2005 data in one file, 2006
data in another, and 2007 in yet another.
So our partition function contains a range for each one
of these years.
Then we create our partition scheme tied to the partition
function, and then we recreate our table tied to this
partition scheme.
Once we do our initial load, all of that data gets spliced
up and put inside of its appropriate file.
So now it's 2008, and we have a big data load to do to get
that 2008 data into our fact table.
How do we do it?
The first thing we do is create an empty partition for
our 2008 data.
We expand our partition function to include that
range, and now we create a staging table.
This staging table is the exact same structure.
It is our FactInternetSales table.
We'll just call it FactInternetSales_Staging.
And this is a non-partition table.
It's just a regular old table, nothing special about it.
It sits inside of the primary file group, uses the MDF file
to store its data.
So nothing special about this table.
The only requirement here, it needs to have
the same exact structure.
So now, we can use the old fashioned way, use SSIS to
load up this staging table.
But rather than use a set-based operation or even
just SSIS to load the data and push it into production, we
can run the switch statement ALTER TABLE.
We would alter our staging table against our fact
internet table, use the SWITCH SQL command, and SQL will just
switch the data pointers.
So now this file will look loaded, and the staging table
underlying data pages will look empty.
So it literally just switches it around.
It takes one second for SQL Server to do the switch.
And the really cool thing about this is, let's say that
we needed to load up three more years of data
all at the same time.
We can create a package with a parameter in it, say a year
parameter, and then we could execute the package with 2009,
execute it again with 2010, execute it again with 2011.
So now we can do concurrent loading.
We can load all of these at the same time, and when
they're done, we can just do switch, switch, switch,
switch, and that is as good it gets.
That's really cool.
And that's how Microsoft loaded a terabyte of data in
30 minutes, by doing this concurrent data loading all in
their own staging tables, and then just doing a switch
against all staging tables into their own partition.
Really neat stuff.
I'll give you a good demo of this when I get into the
Virtual Nugget Lab, because everybody should know how to
do partition switching.
And the reason you should know how to do this-- and it goes
well beyond data warehousing.
You can do this in large SQL Server databases.
But my biggest thing about this is now you don't have to
hit your production databases and your
production tables directly.
Any time I'm hitting a production table directly
using SSIS or any other means, I'm biting my nails the entire
time the process is running, praying something doesn't go
wrong, so I don't have to stay late or use extreme measures
to make sure that I get everything in and it's back to
the way it was.
Now, I can take my sweet time, load it into staging, ensure
that everything looks good, and when I've ensured
everything looks good, I just switch into production and
it's automatically there.
Really cool stuff.
And by the way, you can use this technique to unload data,
delete data, remove data.
You never want to run delete statements against your
production tables, especially large ones.
That will cause all kinds of chaos, locking blocking
problems in your production database.
Rather than do that, if you need to remove data, say our
2005 data is old, we can just create a table
and switch the partition.
And all that data will just magically disappear our of
production and end up in our other table.
So again, great stuff.
Works for loading and unloading data in tables.
All right, why don't we head into the Virtual Nugget Lab
and learn how to do all this wonderful stuff.
We'll learn how to load up our fact tables initially,
incrementally, use set-based operations, and work with
partition switching.
In the Virtual Nugget Lab, I have launched everything we're
going to need in this Nugget.
We have access to our support files directory here for this
Nugget, 12-DataLoading.
We'll be in here a little bit.
When we get into partition switching, we're going to need
somewhere to put those data files, so we have a data
folder ready for us.
In SSIS, obviously, we have a project that we're going to be
working with, 12-DataLoading.
I've pre-loaded our connection managers, and it's some SSIS
packages, one for full, one for incremental, one for
working with CDC.
And when we get into partition switching, we'll use our full
package here to show off partition switching.
And these are mostly empty.
The full and the incremental one we're
going to do from scratch.
The CDC one I've pre-built, we'll walk through it, and
I'll show you how it works.
We also have a couple of scripts down here to work with
in each one of these areas as well.
And we'll use these scripts from SQL
Server Management Studio.
So I also have that pre-launched with our
connection to our SQL Nugget instance.
So let's start by designing our full package, which is
what we're going to use to initially
load our data warehouse.
And by the way, we're going to use the Adventure Works 2012
database and our production data warehouse, so we're
working with both production databases here.
We're going to use our staging tables and put them inside of
our production database.
We're going to need that anyway for partition
switching, because in order for partition switching to
work, you need your tables to reside in the same file group.
So we're just going to create our staging tables right in
our production database.
It'll make it easier for set-based updates, we won't
have to cross-database queries and all that
kind of stuff either.
We can just go from our staging table to our
production table.
Pretty straightforward.
So let's head back into our environment here, and the
first thing we're going to need to do is drag and execute
SQL task into the designer, because we need to create our
staging tables.
So let's give this the name, how about CreateStaging, and
then let's open up our
FactInternetSales_Staging script.
I'm just going to copy this, and this is really just a
clone or our FactInternetSales table with
_Staging appended to it.
So we need the exact same structure, that way it'll make
set-based updates easy.
So we'll do all of our transformations and taking all
that data from our OLTP world, and pushing it into our
staging table, and it'll just be a straight shot into our
production table.
All right, so let's open up our create staging task, let's
assign a connection to it.
We're going to do this inside of our data warehouse, and
let's just paste in our code.
There we go.
So that will create our staging table.
Any time we run this package, if that staging table is
already there, it'll drop it and then recreate it.
And now we need to stage that data.
So let's go back into the SSIS toolbox.
Let's drag a data flow into here, and let's just call this
data flow, how about, Stage FactInternetSales data.
And now let's go in here, this is just going to be a straight
shot from our source to our destination.
Our source is actually going to be a command that is going
to contain our query that takes all the data from all
the tables inside of our OLTP world and models it as the
FactInternetSales table here in our data warehousing world.
So let's grab a source.
How about just an OLE DB source, here we go.
So we'll drag that onto here.
Let's also grab our destination while we're in
here, OLE DB destination, there we go.
And for our source here, we're going to use this OLTP query.
Let me flip over here to management studio just to show
you here so we can run it.
I'm going to open up a file.
We're just open up that query, same one that's in our SSIS
project, and here it is.
Here's what it looks like.
So again, we're taking a lot of data from many tables here.
You can see we're taking it from sales order header, sales
order detail, product, customer.
We're linking in our dimension, our product
dimension and our currency dimension, to get the keys
that our fact table expects out of there.
We're also hitting the product cost history, currency rate,
and then our human resources employee table there.
So if we just run this right now, this will grab all of the
data because we don't have any filters on it.
This is everything.
We're going to 60,000 rows loaded into our fact table
when we do our initial load, and this is
all of it right here.
So this is exactly what that fact table looks like.
So that's one key here.
Obviously we're going to need to create a query that the
results of that query are going to match the structure
of our fact table, right?
Pretty straightforward.
So let's copy this, head back into SSIS, open up our source.
We're going to set the source here as the data warehouse,
and then our data access mode here is SQL command, and we're
just going to paste in that query.
Now we're going to hit the columns button that will
generate all of the columns.
There we go.
So let's hit OK.
Source looks good.
Let's drag this down to our destination
and do the same thing.
So our destination is going to be our data warehouse, and
then our table is going to be our staging table.
But guess what?
It doesn't exist yet.
So you know what we're going to do?
We're going to create it.
Let's head back to the control flow, right click on Create
Staging, hit execute.
Now it's created.
So now let's head back into our data flow, into our
destination, change our connection once again here to
our data warehouse, and now we should see an _staging.
There it is.
We'll map it, everything will line up, since the column
names are the same as the output of the query, and we're
good to go.
So staging the data from our OLTP database into our staging
table looks good.
Let's head back to our control flow.
The last thing we need to do is push this data from our
staging table into our production table using a
set-based operation.
So let's head back into the SSIS toolbox, let's drag and
execute SQL Task onto here.
Let's just call this one Push Staging to Production.
There we go.
Let's open it up, assign a connection to it.
It's all going to be done inside of our data warehouse.
And this is as easy as it gets, really.
Here's the perfect set-based operation.
Insert, we want to go DBO.FactInternetSales.
That's production, right?
We want to insert into that table.
Let me just copy this by doing a select star from our staging
table, and because these structures line up, that's all
we need to do.
So this is a set-based operation because we're not
doing row by row.
If we were to load up this fact table by creating a data
flow and sending the flow, that flow would insert it row
by row into production, very slow process.
This way, using set-based, it just grabs all the data out of
our staging table and throws it all into production.
And it's pretty easy there, as far as coding
goes as well, right?
Look at that, two lines of code.
So we're good.
Let's hit OK.
Now we can just connect everything together, and our
full package is done.
Now let's pretend this really is an initial load, and right
now we have data inside of our FactInternetSales table.
So let's delete all the data.
Now, we removed all the constraints, so we should just
be able to do a truncate table FactInternetSales, highlight
that, hit F5 to execute it, and now, if we grab everything
here from our fact table, it's empty.
So our production table is empty, we just created our
staging table so that's empty.
In fact, here, let's just refresh our tables,
and here they are.
So production empty, staging empty.
This package will load up staging, and then do a
set-based operation to take that staging data and push it
into production.
Let's run it and see what happens here, and
see how long it takes.
Shouldn't take long at all, and we do have 60,000 rows, so
that's a good amount, but there you go.
Not too bad, right?
Now let's take a look here.
And look at that, we have data.
We have all 60,000 rows loaded into production.
So that's how we can do an initial load.
One of the easiest ways to do it, anyway.
Load up the staging, and then do a set-based operation to
push it in.
So with the initial load in, now we need to develop our
incremental package so all future runs only grab new and
changed data, rather than running the
full load every time.
So again, small data warehouses, that might work,
but extremely large ones, we need to reduce the amount of
time that these loads take.
So first, we're going to copy this task here, because we're
going to need the same exact create staging task over in
our incremental package.
Then we'll load up our incremental package.
I'm just going to paste this right in here, there we go.
And so we already have our create staging task done.
So the next step is finding new records.
How do we find which records are new in our source?
Well, what we can do is we can write a query, an aggregate,
that gives us that highest product key out of our
destination, out of our production data.
And then we can store that in a variable, and we can use
Dynamic SQL to pull that data from the source,
based on that variable.
Let's do it.
So let's grab another Execute SQL task, drag
that right down here.
Let's just call this one, how about, Get MAXKey from
Production.
Something like that, that looks good.
Connect these together, let's open this up, let's tie it to
our dw, our data warehouse here.
And then our SQL statement is going to be-- this should be a
pretty straightforward SQL query.
We're just going to do is SELECT MAX, and we're going to
SalesOrderNumber, that is the primary key of the table here,
as our MAX fact key from our production database, which is
our FactInternetSales.
That's it.
So if you're dealing with a really large fact table and
you do not want any of your queries affecting it
whatsoever, you can use the WITH NOLOCK in here, and that
will not generate any read or shared locks or anything.
It'll basically allow us to kind of run a ninja query
against the table so we don't affect anything in production.
So that's a good idea to do that against large tables.
So that's it.
We'll hit OK, and now we want to actually use that value
that that query returns here in our package.
So we're going to go up to the Result Set property on the
Execute SQL task, drop this down, choose single row, head
into Result Set, and add one.
We'll just call this zero, here.
And then we need to specify a new variable to
put the value in.
So let's call this value here our MAXKey, and everything
else looks good.
String variable, you can choose the container that it's
associated with.
We'll leave it at the package level, here.
We're going to need it in other tasks.
And we'll hit OK.
We hit OK here, and we're good.
So now that we have a way to identify new records and we
can use that in our data flow, along with a little dynamic
SQL, to get everything from our source after that key,
what about modified records, changed records?
Now, with this particular design, it's going to be a
little challenging doing it this way, because if you're
look in here in our FactInternetSales table, we
don't have a modified date in our destination, in our
production data.
So how could we do it in this scenario?
Well that's where CDC will come into play, and that's
exactly what I'll show you how to do with CDC.
But for now, for this specific incremental package, we're
just going to create a parameter.
So we're going to kind of do this the static way.
Let's call this parameter, how about, BeginModifiedDate.
Make this a string, and just put the first of the year in
here, so anything modified after the first of the year.
When we add some records in there to test this, we'll make
sure that they have a modified date after this date so we'll
see them come in.
So now the next step is getting a data flow in that
makes use of our variable and our parameter to get the new
and changed data.
So what I'm going to do is I'm going to head back to our full
package, I'm just going to copy onto the clipboard our
data flow, because it's going to be the
same exact data flow.
The difference is we just need to parameterize that query in
the source, all right?
So let's connect things together, let's go into the
data flow, let's go into our source,
scroll down to the bottom.
So our data flow grabs everything, we're going to add
a where clause here.
We're going to say WHERE sales order header .SalesOrderNumber
is greater than question mark.
We're going to use this parameters button to fill in
that question mark with a variable.
So that's all new data.
That will get us all our new data, and we also want to say
then, or soh.ModifiedDate greater than or equal to a
question mark.
That's going to run off of the premise that we just created.
So now let's hit this parameters button.
The first parameter is going to be our MAXKey that we did a
look up against production, and our second parameter is on
modified date, and we will use our package parameter that we
just created for that.
There we go.
So the last thing we need to do, once again, is push this
data from our stating table to our production table by doing
a set-based operation.
So let's head back into the control flow, let's head into
the SSIS toolbox, and drag an Execute SQL task down here.
And we'll give this one the same name as we did in our
previous package, Push Staging to Production.
And now let's connect these tasks together.
Let's go into our Execute SQL task.
Let's choose our connection as or data warehouse, and then,
as far as a SQL statement goes, I'm just going to paste
this in here, we'll go through it.
Two statements, one for the insert,
and one for the update.
And the thing is here, that we're going from staging to
production, two identical tables.
So for inserting records, this is easy.
Just like we did when we did our full package.
The only difference is we have a WHERE clause on so we can
identify those new records inside of our staging table.
To identify updated records, we do not have a modified date
inside of our staging or our production table, that was
purely in the source.
But we can still identify which records are modified
records in our staging area, because we can do anything
that's prior to the sale number.
Remember, anything after the sale number is a new record,
so anything prior to the sale number then, in our staging
table, is a modified record.
And I'm just going to update three fields in here.
We're setting the prod equal to the staging.
Obviously, in a real world, we'd want to do all of the
fields, but for now, just for demonstration purposes, we'll
just do these dates.
And we'll make sure that we change these dates and the
modified date in the source data when we do our testing,
so we see both new records and modified records flow into our
production table.
So this looks good.
The only thing we need to do here is we need to wire in
these parameters into our Execute SQL task.
So we can go right into our parameter
mapping, we can hit add.
We'll need to hit it twice.
First parameter-- actually they're both going to be the
same parameter, they're both going to be the User MAXKey,
so let's specify those.
And then they're both input, these are actually character
data, so we'll choose VARCHAR here.
Sales order number is a character.
And then we want to do zero for the parameter name, and
one for the second parameter name.
That lines them up, and that's it.
Let's test this package out.
So let's head back into Management Studio.
I have an insert statement here, a couple of them.
One of them will add one to the Sales Order header and the
sales order details.
Since these are inner-joined, we may have to make sure that
we have a detail record.
So if we execute this against Adventure Works, our OLTP
database, that'll add a new record.
Now, let's also get an updated record in here.
Let's go right into tables here in Adventure Works-- not
Data Warehouse, Adventure Works, there we go.
Let's head into our sales order header table down here.
There it is.
Let's just right click on it and choose Edit Top 200 Rows,
and let's change in order of date, or how
about a ship date.
Let's say the ship date has changed here.
Let's just do 2013-01-01, and we have to change modified
date too, since that's what we're running on.
So let's go back, and there it is.
This one, let's do 2013-02-01.
All right, we have changed data, we have new data.
Let's head back into the development environment.
And something really neat that I want to show you here is if
we had into our data flow and we double click on the actual
line itself, we can head down to data viewer and enable our
data viewer.
This is our window into the data flow.
Any time we execute a package from the development
environment and it hits this flow, any time it has a
magnifying glass on it, it's going to pause right there and
open up a window that'll show us all of
the data in the flow.
Really handy for troubleshooting and doing
stuff like this.
So this allows us to see those two changed records.
Let's hit run here and see if we get two changed records.
And you see, it'll pause right there until you
hit the play button.
Now we only have one record, and this is like a new one.
I know the highest sale order number before we did that
insert was 123 as the last three digits.
So 124 tells me this is the new record making it through,
but our modified record didn't make it over.
So let's stop this and find out why.
Let's head back into Management Studio, and I think
I know why.
If we scroll over to the left here, remember, this is
FactInternetSales.
Look at this.
We should have put a filter on, because the online order
flag is false.
So let's modify a record that is actually an
online order record.
Let's just put a WHERE clause here in our SQL statement,
WHERE OnlineOrderFlag equals 1.
We'll hit execute, so these are all online orders.
This is the kind of data we should be modifying.
So let's just head over here and change or modify a date.
We'll change it to today's date here, so this year's
date, 02-01, that looks good.
Now if we flip back and run our package, it should pause
on our data flow, and look at that.
We have two records.
So here's the one that we just changed.
Now if we hit run, it's going to send them over.
Awesome.
So we've got those two new records over into production
from our set-based operation, and that's how we can do
incremental packages using straight trans x equal.
Now, this will be a better solution for CDC, the change
data capture, because we don't have that modified date issue
where we would have to control it through a parameter.
Because if we were to run this package again, that same
record would keep coming over, because it has a modified date
greater than the date that we stored
inside of our parameter.
So again, we could do this easily if we did create our
own intermediate table that tracked every time we ran what
our last modified date was.
We could do that.
But CDC can do all that for us.
Let's see how this stuff works.
So what we're going to do is we're going to open up the
CDC.SQL script over here in Management Studio.
So we'll just do a File, Open File, and here
is our CDC.SQL script.
If we open this up, the first thing we need to do is enable
it on the database.
So what we can do is switch to Adventure Works 2012, and then
we will run the first EXEC statement, sp_cdc_enable_db.
This will turn it on, get all the objects installed for CDC
tracking inside of the Adventure Works database.
So let's do that first.
We'll just highlight those lines of code.
It'll switch this to Adventure Works, it'll
enable it in the database.
Now we want to enable it in the table.
And by the way, what that did, if we open up Adventurer Works
2012 here, tables, and let's go right into system tables,
check it out.
This is what it did.
It added a bunch of system tables that SQL will use to
track all of the changed data.
So now, we want to enable it for our sales
order header table.
So we need to pass in the source schema sales, the table
name, and then optionally, you can assign it a role here.
So let's hit execute, that'll enable it on a table, take a
little bit to set everything up, and we'll be good to go.
And that's really all there is to it.
So now CDC is fully enabled and running on the database.
Any time we make any changes into the sales order header
table, if we alter a row, add a row, whatever we do to that
table, SQL is tracking it underneath the hood in those
system tables that we just looked at.
And if you ever want to view CDC information for a
database, you can run the sp_cdc_help_change_data_capture
system stored procedure here, if we can execute, here it is.
This will show us all the tables that our CDC enabled.
And by the way, this is important if you ever want to
remove CDC, because see this capture instance here,
Sales_SalesOrderHeader?
You need that if you ever want to disable it for a table.
Same thing though, schema, name of the table, and then
the instance of the table.
If we were to run this, it would
disable CDC for the table.
The last thing we need to do for prep here is create
another staging table.
This one we're going to call cdcstaging, this way we don't
step on the previous demo that we did, kind of creating our
own staging area for the CDC demo.
And rather than model the entire FactInternetSales
table, we're just going to take the sales order ID out,
and with CDC's help, we'll be able to identify what
operation we did.
So this will make it very easy for us to do a set-based
update using this operation column, along with
the sales order ID.
So let's do this.
Let's switch over here to our Adventure Works DW 2012,
highlight our create table statement, hit execute, and
there we go.
And by the way, if we flip back here into SQL Server data
tools, that's exactly the same code I have here inside of our
create staging.
So that will get recreated every time we run the package.
So with CDC enabled at the database level, we have a
table enabled for CDC, and we have our staging
environments set up.
The last step is to create an SSIS package that controls all
that data, pushes the new and modified data into our
destination, our data warehouse.
In order to do this, we need to help out CDC
a little bit here.
And we do this through what's known as the CDC control task.
Inside of other tasks, you'll see here the CDC control task.
I have two of them on.
Let me enable both of them.
We have our CDC start, and at the very bottom, we
have our CDC end.
So the easy control task is there to tell CDC what to push
over and what it already pushed over, and it's going to
do this by managing state.
And let's open up our CDC start task.
So this is all done through an ADO.NET connection manager.
That's why I added an ADO.NET connection package manager
right down here.
We have project connection managers, these are both OLE
DBs, so we couldn't use these, they're not even available on
the drop down.
So that's why we created our own, called AW CDC that points
to Adventure Works 2012, where we have CDC enabled.
The next thing we need to do here is we need to specify the
control operation.
CDC expects you to do an initial load, so that's why we
have a package that does both here.
We're going to use this package first as an initial
loader, then we're going to switch these control tasks to
work on incremental changes only.
But you do need to do an initial load first so CDC
knows that you did one, and it can mark that you've done an
initial load, so it knows where to start for the
modified data and new records.
So that's why, during your initial load, you're going to
set up the control task first as a mark initial load start.
You also need to specify a variable.
This is so CDC can take all this state that it's going to
store using a table down below, and use it across
package executions.
So if you don't have a variable in here, just hit
new, give it a name, and there you go.
Now you have a variable for our package
to track CDC state.
Then also for, again, cross-package execution, where
is this variable data going to go when the package ends?
And so you can create a table here to put that data.
So again, specify the connection manager, hit the
New button here, which is what I did, and it will create the
table, create the structure here of what it
needs to store state.
So I did that, I hit OK and Run, and it created that
staging table, and you can also give it a name here.
And then once you hit OK, you're good to go.
And then you'll just need to go down to the very end of the
package, set up another CDC control.
The only difference here--
you'll want to point to the same variables and same tables
and such-- is mark the initial load end.
So that's the only difference between both of these control
tasks is one is the start and one marks it as the end.
So we're ready to go for our initial load.
Let's remove some of this incremental stuff out of here,
and wire up everything to our initial load data flow.
There we go.
And if we enable this data flow and head into it, you'll
notice we're not doing anything special.
We're simply using an OLE DB source pointed at our CDC
enabled table, and pushing all that data using an OLE DB
destination to our staging table.
We're not doing anything CDC special in here.
We're not using the CDC source or any CDC transformations.
That's because your initial load is really just all about
setting up the state for CDC using these control tasks.
That way it's going to take all of this initial load
information, store it into its CDC table so it knows where
our starting point is.
And that way, future incremental loads, it will
only send over the data that is changed.
So let's see how to do the incremental now.
Let's hook everything back into our
incremental data flow here.
There we go.
And let's enable everything, so we'll enable our
incremental load, we'll enable our set-based updates,
inserts, and deletes.
And the only thing we need to do here in control flow is
head into our CDC start, and get it off of
mark initial load.
Now we want to choose get processing range, hit OK, and
our CDC end, we want to hit mark processing range.
And by the way, this other one, mark CDC start, is if you
want to control where it goes.
Then you can have a little manual control
over where it starts.
And SQL Server controls it all through LSNs, which is what
the transaction log uses to mark transactions.
So let's go ahead and choose mark process range so the get
starts it, the mark ends it, and now the only thing we need
to do is go into our data flow, and this time we use a
CDC source.
So you open up your CDC source to find your connection
manager point at your CDC enabled table, the instance,
choose your processing mode, all with old values.
So what happens here is SQL Server tracks not only
inserts, updates, and deletes, but it tracks the before
update and the after update, which is a lot like what
triggers do.
So all with old values also gives you access to the data
how it looked before the change, so current values and
previous values kind of thing.
So we're going to choose net here to give us anything that
has changed since the last load, inserts, or updates, or
deletes, and then you choose the variable that holds he
information.
And we check out the columns, you can see the output is
everything from our table, our CDC table, plus CDC adds its
own extra columns in here to work with.
And the one we're really going to be concerned with here is
the operation count.
This is nice because this will allow us to identify what
operation occurred on the record.
Is it an insert, was it a delete, was it a before
update, an after update?
That kind of stuff.
So if we hit OK here, let's head down to the CDC splitter.
Now we don't really need the CDC splitter in this case,
because in our staging table, we're tracking what
operation it was.
But I wanted to show it to you here just so you can get an
idea of what it does, and it's really simple.
There's really no configurable properties on the splitter.
You just drag your source to the splitter and that splits
the output for us, and now we can identify through our data
flow what was inserted, what was updated, what was deleted.
So we could do transformations on specific ones, or just go
straight into our tables if we need to.
So that's pretty neat.
But all of these destinations do the same thing.
They just load it up, they load up the sale order ID and
the operation into our staging tables.
So they all do the same exact thing.
Again, we could just omit the splitter and do a source right
into one destination because, again, we store the operation.
But that's it.
Now, once all that data is loaded into staging, we can do
our set-based insert, updates, and deletes.
So we have an insert which, really, all I'm doing here is
now we're molding what the
FactInternet table is expecting.
Because we're just running a big honking select statement,
the same query we ran over when we talked about our
incremental package previously, this OLTP query.
It's just that we're also joining to it our CDC staging
on the sale order ID.
And we're putting a WHERE clause where operation column
in that staging table equals 2, which is inserts.
So that will do a set-based update against our production
table, using our staging table where that operations is two.
If we look at our updates SQL task, we're doing the same
thing we did with our previous incremental package, just
updating those three columns.
Only here's the important part--
where the operation equals 4, this is after updates, where 3
is before updates.
So again, CDC, great for auditing, give you that
snapshot before and after look of data.
And so 4 is updates, 3 before updates, and then finally
here, 1, is deletes.
So you can see where the operation equals 1, it will
identify all the deleted records here
in our staging table.
So the only thing left, really, to do
here is test it out.
So we need to modify some data.
Let's flip back over to SQL Server Management Studio, and
I opened up our sales order header table once again.
This time I remembered to add our online
order flag equals 1.
So now, if we change some data in here-- and
we can change anything.
We don't have to change just what we were looking at
previously, like the modified date, because CDC's enabled on
the entire table.
So let's just pick a record here, we'll enter test, and
there we go.
Now something else really neat, and we need to refresh
our tables here, but if you wanted to see and take a peek
inside of CDC, you could expand system tables.
And when we enabled it at the table level, the database
level added all these tables here.
But enabling it at the table level added a
Sales_SalesOrderHeader_cttable.
This is where SQL Server and CDC work together to track all
that information.
Because if we select top 1,000 rows, check it out.
Here's all the data for just that one change that we made,
because it tracks befores and afters.
And here's your operation, before, after, before, after.
Even though we only changed one, it's in here many times,
because it has revisions on it.
So if we close out of this, we head into SSIS, and we run our
package, we should get one row, and look at that, we do.
We have one row from our source, heading down our
splitter, heading down the update
path, and getting staged.
And then we don't obviously have any new records or
deleted records, so those won't find anything, but our
set-based update will have updated those fields inside of
our production database.
So that's how CDC works.
It's pretty straightforward.
There's really not a lot to it--
SQL Server does all the hard work, we just have to know and
remember to set up our start and our end, because these
control tasks just help CDC and SQL Server figure out,
through state, where it's at, and where it will go in the
future when it needs to send new and changed and deleted
records into our destinations.
So CDC can be a great alternative if you have no way
to identify changed information in
your SQL Server sources.
The last thing I want to show you here is how to do
partition switching, and we're going to do this just like we
did in our white board, where we're going to take our
FactInternetSales table, we're going to splice it up on
partitions by year.
And we're going to do this by using our full package.
So we're going to recreate our production table so we'll have
nothing in it, our staging table will have nothing in it,
and so this is a perfect thing, because we're going to
take all the data, push it into staging, and then do the
switch to get it in production, rather than a
set-based update.
So we can disable our set-based update, which is a
last step here in our full package.
We could just put our switch statement right in here in our
Execute SQL Task, but I want to do this manually so you can
see the process work.
So let's head back into Management Studio.
We're going to open up the query here
called partition switching.
And so what this query does is the first thing we're going to
do is we need to get some secondary data files in to
store each year's worth of data.
So we have four altered database statements that will
do just that.
So let's highlight all this code, hit execute.
That'll take a little, bit because it's creating 100mb
files, but where we're putting them is right here.
I created a data directory here in the 12-DataLoading
directory for this Nugget, and you can see that's creating
all of them and allocating all that space for all four files.
And look at that, we're good to go.
Now we need to set up our partition stuff, and we need
to switch our database here, because we're going to go
against production.
There we go.
And so we need to change to Adventure Works DW 2012, we
need to create our partition function, which specifies our
ranges for each year, and then we need to create a partition
scheme tied to that function, so that way, when we create
tables, we're going to create tables rather than our file
group, we're going to create them on this partition scheme,
which is tied to this function.
So let's do that.
We'll hit execute here, that will create our function,
create our scheme tied to that function.
Now we just need to drop our table.
We need to drop our table, because we're going to
recreate it on our partition scheme.
And we're doing this all on order date key, by the way.
So order date key is what will get passed through the
partition function in order to splice up that data across all
those files by year.
So let's highlight all of this code and drop our
current fact table.
There we go.
And now we can simply recreate our FactInternetSales table on
that partition scheme.
So let's do it.
Let's create the production table.
All right, now let's head down here and we can create our
staging table as well here.
I'm doing this just so we can compare apples to apples, but
this staging table does not have to be on a partition.
We can simply have a non-partition table, and then
when we do the switch, we just wouldn't specify a partition
for the staging table.
But we're going to create one on there anyway.
We'll hit OK, hit execute there, and we're good to go.
So the last thing to do now is do the switch.
But we need to load data into our staging table to do that.
But literally this is it for the switch statement.
We're switching partition four of our staging table with
partition four of our internet sales table, which is going to
be or 2008 data.
So let's head into SSIS.
Let's just disable our create staging, too.
We no longer need that.
We just simply need to stage the data.
And what we're going to do is go into our source, and we're
going to say where the year-- put a little
WHERE clause in here--
WHERE year, order date, equals 2008.
And that's an integer, so we don't need to
string enable it there.
That's it.
So this will only grab 2008 data out of our source.
That's all we should see in here.
If I hit the preview button, there it is.
All 2008 data.
So now we can simply execute this package, this will load
up our staging table with only 2008 data
inside of the 2008 partition.
So if we just check here, let's make sure that both
tables-- our staging table should have data in it, there
it is and our production table has no data in it.
So now we can run this really simple switch statement.
We're going to alter our staging table, switch the
partition four from our staging to the partition four
of our production.
Ready for this?
Boom, done, one second.
They should be reversed.
We should have data in our production table and no data
inside of our staging table.
Pretty cool, huh?
That's partition switching at its finest.
Just to reiterate, if your staging table is not built on
a partition, which most of the time, they won't be, then you
can just omit this part right here.
So you can do an ALTER TABLE, staging switch to production,
and then which partition in production you're
switching it into.
So there's partition switching.
Just wonderful stuff, doing even set-based updates and
operations against, production tables can cause problems in
extremely large data warehouses.
And who wouldn't want to do a one second metadata switch
from staging to production?
We can spend all the time in the world loading up staging,
and when we want to put it in production,
boom, just switch it.
Easy as that.
In this CBT Nugget, we learned how to load fact tables, and
saw the data loading options available to us.
We started with an overview, saw that we need a full
package for an initial load.
We need incremental package to deal with changes, and we saw
some of the challenges that go along with incremental loads,
and some of the technologies we could use to do so.
We then jumped into the Virtual Nugget Lab and we got
familiar with how to first do a full load.
We looked at how to do an incremental load, both using
SQL and using CDC, the change data capture
features of SQL Server.
At the end here, I showed you how to do partition switching
to quickly load up your production tables from your
staging tables.
I hope this has been informative for you, and I
thank you for viewing.