in this video we're going to take a look at the cache transform
and how you can use it as part of a lookup function as well
all right so let's hop right into it the first thing we'll do is create a data flow
task and essentially what we're going to do is load a cache file which is a dot caw file
with data from our database and use that as part of a lookup and the reason you would
want to do this is because it's going to store that information locally for you and reduce the
number of times you have to call a database to get some sort of information as it says here it
reduces the database load you're storing this information in a file locally that you can use
and you don't have to worry about constantly calling back and forth to make those matches
okay let's create this cache file first thing we'll do is add a source
and in our case it's going to be a database table we will use conference alignment this is
what we'll fill the cache with and it just looks like this it's saying this city is aligned to this
conference you know golden state los angeles west et cetera so again this is just our source now
we want to add the actual cache transform which is under other transforms cache transform let's
drag this on and connect this now let's hop into this editor just like any other connection a cache
connection requires its own connection manager so let's create a new one we can call this
conference cache as we mentioned here this technically is coming from a file
so if you have a specific file that you want to use as a cache you could reference that here and
instead of looking up from a database in our case we do not have that so we're just pulling from the
previous component and it recognizes it here it's saying you know we see these are the three columns
the data types etc the only requirement at this point is you need to give it an index position
you know this is the unique value and what should be used in a future lookup in a lot
of cases this could be the id because you'll you know you'll be joining that way but for
our example we're going to do it based on city so we'll say city is unique it is the index and
that is what we want to look up on all available columns must be mapped i think we just need to
refresh this and select ok so in this data flow here we are loading a source table into cache
and now in our next step we can reference this cache as part of a lookup
let's go ahead and add another data flow task and i'll call this lookup values
we'll connect these and jump in here so we're going to add another source here
and we're going to use the table mba teams and what this is right now is just a city and a team
and if you remember the previous cache is city and conference so what we're going to do here is
is look this up through the cache we're going to say look up where
the city matches the other city and we'll get that value now we'll implement a lookup there it is
look up and this is where it can get interesting so what the default here
is oledb connection meaning here by default you're going to connect to some other database
and use that as a lookup but instead what we can do is use a cache connection manager which
will be using that cache file and store it in memory so that there's less hits to the database
cache mode here really the two that are important it was full and partial full is essentially saying
we're going to completely fill up the cache before we get to this lookup at that point there will be
only one hit to the database it's going to get the information and everything from there on out
is based on that file partial cache is saying we're going to start with what we have but if
something isn't matching the lookup we are going to call the database and see if we can find a
new match and if so we'll add it to the cache so in that scenario you're going to have some more
database hits because your cache at that current moment may not be complete so in this scenario
you're opening yourself up to potentially more database hits which may or may not be worth it
in this case we'll do full cache and let's go to connection conference cache this is
the only one we have so we're going to keep this and here's where we'll
do the lookups the lookup column we want is conference and what we will match on is city
to city one thing i want to point out is if you did not set that index value earlier when we said
you know city on our cash value is is the index position one this would throw an error and say
you know you need to let us know what do we need to start our lookup on from your cache you have to
make sure that's set look up operation we're going to add a new column we're not replacing anything
we're just saying look where the cities match and give us the value of conference let's click ok
now i want to see what this output is so i'm going to just add a derived column
i'm not not too concerned with this i just want to be able to add a data viewer so we can see
together what this data is looking like so to recap here we're loading up our cache with this
information from the conference alignment and then in the next step we're going to connect and get
another table but then look up that conference value from the cache by matching it with
some values here so let's see that was very quick and you can see here we have our city our team
and conference returned and that conference is from the cash that matched to this value
that'll do it for this video pretty quick and straightforward
obviously this is a very simple example but if you have a lot of lookups that you're doing in
your etl process that might be resulting in a lot of hits to your database and you want
to reduce that loading some things up into your cache for your lookups is a great way
to lighten that load a little bit and also potentially increase performance because
it'll be working in memory rather than having to do those constant hits so again that's it
for this video thanks for watching if you found this helpful please like the video
subscribe leave a comment if you have any questions and i'll see you at the next one thanks