Passing Parameters to Mappings

I received a question from a client today about the best way to add a date parameter to a mapping. In this case, they were looking for the max date in a particular table, and they wanted to use that date as a source in a mapping. Below I'll demonstrate some of the different ways this is possible. A slight disclaimer: I'm using OWB 11gR2 for this demonstration. All the same bits are in 10gR2 and 11gR1 as well... they just look slightly different. Later on I'll be talking about parameters in process flows. To see a 10gR2 example of binding parameters in a workflow, have a look at this posting.

There are a lot of ways to skin this cat. First off, if the date is being pulled from a table that is already a source in the mapping, it's very easy to just use an expression to get the max date out of that source, and feed it into the target. In this case, I'm using SQL analytics to get the MAX date for TIME_ID over the entire result set, which equates to '12/31/2001 12:00:00 AM':

Yes... workable, and it keeps everything inside a SQL statement. Though I doubt this is really what the client was looking for. Instead, he wants to capture the value once, and store that value inside of a variable of some kind, and then use that variable to pass the value through to the mapping. The first thing to do is write a PL/SQL function that gives me the value I want so that I can import that function in as a transformation and use it anywhere I want to. The function as I wrote it is listed below, and after compiling it, I just use the import transformations option:

SQL> CREATE OR REPLACE FUNCTION staging.get_date
  2     RETURN date
  3  AS
  4     l_date DATE;
  5  BEGIN
  6     SELECT MAX(time_id)
  7       INTO l_date
  8       FROM sh.sales;
  9
 10     RETURN l_date;
 11  END;
 12  /
 
Function created.
Elapsed: 00:00:00.65
SQL>

The next way to approach this is using a constant in the mapping. I drag a constant operator onto the palette, and set the constant expression equal to the function call:

The better choice for this though is to create a mapping input parameter that allows me to pass that parameter in with each run.  I can even set a default value so that it works exactly like a constant, but then I have the flexibility to override that parameter when the mapping is called externally. Below is the expression builder for the default value of the GET_DATE input parameter:

But taking a step back, I think the best solution would be to collect this parameter inside a process flow, and then pass that value to the mapping. This gives me the flexibility to pass that date to multiple mappings. Even if I only have a single mapping now that needs this date, it seems very likely that I could have another one in the future. So I create a process flow, put the transformation operator and the mapping operator on the flow palette, and pass the value in:

Now I create a variable called CURRENT_MAX_DATE so that I can grab the value of MAX_DATE from the transformation once, and store it for the duration of the process flow. So I bind the variable to the output from the GET_DATE transformation, and then I bind the the MAX_DATE input parameter for the MAP_SALES_FACT to the CURRENT_MAX_DATE variable.

As you can see from the MAX_DATE inspector above, I could have bound the mapping parameter straight to the result of the GET_DATE function. However, I'm thinking ahead here, and if I ever add another mapping, I'll want to call the function only once instead of once per mappings. Also, a date like this seems like it could be pervasive through several portions of the data warehouse load. In cases such as this, I recommend capturing the date in a higher-level process flow, and then passing it down to embedded process flows using parameters in the same way. In this case, I highlight the START activity in the Structure window for the process flow, and then I select the "+" button to add a new process flow parameter. Then, when that process flow is dropped on the palette of another process flow, the process flow input parameter can be bound to a variable just as the mapping parameter was: