Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Validating the DF_SalesOrg data flow [page 44]

Next task: Saving the project [page 46]

4.8 Saving the project

You can save the steps you have completed and close Data Services at any time.

● To save objects in a project, select Project Save All .

● To save objects that display in the workspace, select Project Save .

● To save all changed objects from the current session, click the Save All icon in the toolbar.
● Or, simply exit Designer. Data Services presents a list of all changed objects that haven't been saved. Click
Yes to save all objects in the list, or select specific objects to save. Data Services does not save the objects
that you deselect.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Addressing warnings and errors [page 45]

Next: Ensuring that the Job Server is running [page 46]

4.9 Ensuring that the Job Server is running

Before you execute a job (either as an immediate or scheduled task), ensure that the Job Server is associated
with the repository where the client is running.

When the Designer starts, it displays the status of the Job Server for the repository to which you are
connected.

Icon Description

Job Server is running

Job Server is inactive

The name of the active Job Server and port number appears in the status bar when the cursor is over the icon.

Tutorial
46 PUBLIC Populate the Sales Organization dimension from a flat file
Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Saving the project [page 46]

Next task: Executing the job [page 47]

4.10 Executing the job

Execute the job to move data from your source to your target.

Complete all of the steps to populate the Sales Organization Dimension from a flat file. Ensure that all errors
are fixed and that you save the job. If you exited Data Services, log back in to Data Services, and ensure that the
Job Server is running.

1. Select Project Open and select Class Exercises.

The project appears in the Project Area.

2. Right-click the job JOB_SalesOrg in the Project Area and select Execute.
3. If you have not saved changes that you made to the job, the software prompts you to save them. Click Yes.

The software validates the job and displays the Execution Properties.

 Note

If you followed the previous steps to validate your job and fix errors, you should not have errors.

Execution Properties includes parameters and options for executing the job and to set traces and global
variables. Do not change the default settings for this exercise.

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 47
4. Click OK.

Data Services displays a job log in the workspace. Trace messages appear while the software executes the
job.
5. Change the log view by clicking the applicable log button at the top of the job log.

Log files

Log file Description

Trace log A list of the job steps in the order they started.

Tutorial
48 PUBLIC Populate the Sales Organization dimension from a flat file
 Log file Description

Monitor log A list of each step in the job, the number of rows proc­
essed by that step, and the time required to complete the
operation.

Error log A list of any errors produced by the RDBMS, Data

Services, or the computer operating system during the job
execution.

 Note
The error icon is not active when there are no errors.

 Note

Remember that you should periodically close the tabs in the workspace when you are finished working with
the objects in the tab. To close a tab, click the X icon in the upper right of the workspace.

Task overview: Populate the Sales Organization dimension from a flat file [page 37]

Previous: Ensuring that the Job Server is running [page 46]

Next: Summary and what to do next [page 49]

4.11 Summary and what to do next

In the exercises to populate the Sales Organization dimension table, you learned new skills that you will use for
just about any data flow, and you learned about using functions in an output schema and much more.

What you have learned in these exercises:

● How to output data to a datastore target

● Define a query transform including configuring input and output schemas, and setting processing options
● Validate a job and fix errors
● Execute a job to populate the Sales Org dimension table in the target data warehouse.

What is next: Populate the Time Dimension table with the following time attributes

● Year number
● Month number
● Business quarter

You can now exit Data Services or go to the next group of tutorial exercises. If you exit, the software reminds
you to save your work if you did not save it before. The software saves all projects, jobs, workflows, data flows,
and results in the local repository.

Tutorial
Populate the Sales Organization dimension from a flat file PUBLIC 49
Parent topic: Populate the Sales Organization dimension from a flat file [page 37]

Previous task: Executing the job [page 47]

Related Information

Populate the Time dimension table [page 51]

Tutorial
50 PUBLIC Populate the Sales Organization dimension from a flat file
5 Populate the Time dimension table

Time dimension tables contain date and time-related attributes such as season, holiday period, fiscal quarter,
and other attributes that are not directly ascertainable from traditional SQL style date and time data types.

The Time dimension table in this example is simple in that it contains only the year number, month number,
and business quarter as Time attributes. It uses a Julian date as a primary key.

1. Opening the Class_Exercises project [page 52]

We use the Class_Exercises project for all of the jobs created in the tutorial.
2. Adding a job and data flow to the project [page 52]
Prepare a new job and data flow to populate the Time dimension table.
3. Adding the components of the time data flow [page 53]
The components of the DF_TimeDim data flow consist of a transform as a source and a datastore as a
target.
4. Defining the flow of data [page 53]
Connect the objects in the DF_TimeDim data flow in the order in which you want Data Services to
process them.
5. Defining the output of the Date_Generation transform [page 54]
Define the Date_Generation transform so it produces a column of dates for a specific range and
increment.
6. Defining the output of the query [page 55]
Configure the query to apply functions to the output columns and to map those columns to an internal
data set.
7. Saving and executing the job [page 56]
After you save the data flow DF_TimeDim, execute the JOB_TimeDim job to populate the TIME_DIM
dimension table with the changed data.

Tutorial
Populate the Time dimension table PUBLIC 51
8. Summary and what to do next [page 56]
In the exercises to populate the Time Dimension table, you practiced the skills that you learned in the
first group of exercises, plus you learned how to use different objects as source and target in a data
flow.

5.1 Opening the Class_Exercises project

We use the Class_Exercises project for all of the jobs created in the tutorial.

If you closed Data Services after the last exercise, log in to Data Services and follow these steps to open the
tutorial project.

1. Click Project Open .

2. Click Class_Exercises.
3. Click OK.

The Class_Exercises project opens in the Project Area.

Task overview: Populate the Time dimension table [page 51]

Next task: Adding a job and data flow to the project [page 52]

5.2 Adding a job and data flow to the project

Prepare a new job and data flow to populate the Time dimension table.

1. Right-click the project name Class_Exercises in the Project Area and select New Batch Job.

The new job appears under the Class_Exercises project node in the Project Area and an empty
workspace opens.
2. Rename the job JOB_TimeDim.

3. Right-click in the empty Job_TimeDim workspace and select Add New Data Flow .
4. Rename the new data flow DF_TimeDim.

5. Select Project Save .

The data flow is now ready for you to define.

 Note

A workflow is an optional object that you can exclude from a data flow. For this job we do not add a
workflow.

Task overview: Populate the Time dimension table [page 51]

Tutorial
52 PUBLIC Populate the Time dimension table
Previous task: Opening the Class_Exercises project [page 52]

Next task: Adding the components of the time data flow [page 53]

5.3 Adding the components of the time data flow

The components of the DF_TimeDim data flow consist of a transform as a source and a datastore as a target.

1. Click DF_TimeDim in the Class_Exercises project in the Project Area.

A blank data flow workspace opens.

2. Open the Transforms tab in the Local Object Library and expand the Data Integrator
node.
3. Drag the Date_Generation transform onto the data flow workspace.

The transforms in the Transform tab are predefined. The transform on your workspace is a copy of the
predefined Date_Generation transform.

4. Click the query button on the tool palette and click in the workspace.

A query object appears in the workspace. Arrange the query to the right of the Date Generation transform.
5. Open the Datastore tab in the Local Object Library and expand the Tables node under Target_DS.
6. Drag the TIME_DIM table onto the workspace and drop it to the right of the query.
7. Click Make Target from the popup menu.

All of the objects to create the time dimension table are in the workspace.

Task overview: Populate the Time dimension table [page 51]

Previous task: Adding a job and data flow to the project [page 52]

Next task: Defining the flow of data [page 53]

5.4 Defining the flow of data

Connect the objects in the DF_TimeDim data flow in the order in which you want Data Services to process
them.

1. Click the square on the right edge of the Date_Generation transform and drag a line to the triangle on the
left edge of the query.

Tutorial
Populate the Time dimension table PUBLIC 53
2. Use the same drag technique to connect the query to the TIME_DIM target.

The connections indicate the flow of data. Now you provide instructions in each object of the data flow so the
software knows how to process the data.

Task overview: Populate the Time dimension table [page 51]

Previous task: Adding the components of the time data flow [page 53]

Next task: Defining the output of the Date_Generation transform [page 54]

5.5 Defining the output of the Date_Generation transform

Define the Date_Generation transform so it produces a column of dates for a specific range and increment.

Connect all of the objects in the data flow in the correct order before you configure them.

1. Click the name of the Date_Generation transform in the Class_Exercises project in the Project Area.

The transform editor opens at right.

2. Type the following values in the Date Generation tab in the editor:

Start date 2002.01.01

End date 2008.12.31

Increment daily

Make sure that Join rank is set at 0 and Cache is not selected.

 Note

The Start Date and End Date options have a dropdown arrow, but you must type the values in for this
exercise.

3. Click the Back arrow in the upper toolbar to close the transform editor and return to the data flow.
4. Save the project.

The software moves the specified data to the Query transform as input.

Task overview: Populate the Time dimension table [page 51]

Tutorial
54 PUBLIC Populate the Time dimension table
Previous task: Defining the flow of data [page 53]

Next task: Defining the output of the query [page 55]

5.6 Defining the output of the query

Configure the query to apply functions to the output columns and to map those columns to an internal data
set.

1. Click the Query object in the project area under the DF_TimeDim data flow.

The Query editor opens. The Query editor has an input schema section with a single column, an output
schema that is copied from the target datastore, and an options section.
2. Drag the DI_GENERATED_DATE column from the input schema to the NATIVEDATE column in the output
schema.
3. Map each of the other output columns in the output schema by following these substeps:

1. Select the column name in the output schema.

2. Open the Mapping tab in the options section and type the corresponding function in the text area.
3. Select the next column and type the corresponding function.

The following table contains the column name and the corresponding function to enter.

Output column name Function in Mapping tab Function description

Date_ID julian(di_generated_date) Use the JULIAN function to set the Julian

date for that date value.

YearNum to_char(di_generated_date,'yyyy Use the TO_CHAR function to select only

') the year out of the date value. Enclose
yyyy in single quotes.

MonthNum month(di_generated_date) Use the MONTH function to set the month

number for that date value.

BusQuarter quarter(di_generated_date) Use the QUARTER function to set the quar­

ter for that date value.

 Note

For this tutorial, the business year is the same as the calendar year.

4. Click the Back arrow on the tool bar. .

5. Save the project.

These columns become the input schema for the TIME_DIM target table.

Task overview: Populate the Time dimension table [page 51]

Previous task: Defining the output of the Date_Generation transform [page 54]

Tutorial
Populate the Time dimension table PUBLIC 55