Once you have designed your loads, transformations and cubes, you will want to combine them all into a nightly or weekly build.
ORCHESTRATE-it allows you to identify the dependencies of each object, building a sequence in which data loaders, transformations and cubes can be run. You can even include custom steps such as your own SSIS packages or SQL Stored Procedures. Data Academy will then construct a single, fully logged SSIS package, which you can schedule using the SQL Agent or your own enterprise scheduler.
Building an Orchestration
An orchestration defines a sequence of steps, called "movements" - each of which represents a module-instance in Data Academy, such as a data loader or a transformation.
Creating The Orchestration
From the main menu, select "ORCHESTRATE-it" to see a list of all of the orchestrations defined in your warehouse. To add a new orchestration, click on the add button and enter an appropriate name. (The Integration Server property will default to the Data Academy server name, and should be left untouched in most instances.)
Editing The Orchestration
There are two ways of editing the orchestration. The blue "Edit" button allows us to edit the raw metadata, but Data Academy also includes a visual editor, which is accessed via the yellow "Edit" button.
In the visual editor, you will need to use the toolbar (in the top right) to manipulate the orchestration. The following table lists the functions of the toolbar.
| Add a new "movement" to the orchestration.
|
| Edit the properties of a "movement".
|
| Remove a "movement" from the orchestration.
|
| Link two "movements" sequentially to define a dependency.
|
| Remove a link (dependency) between two movements.
|
| Close the visual editor and return to the list screen.
|
Adding Movements
Click on the "Add" button on the toolbar, and select the item you want to add. The following table lists the various type of movement which can be added.
SQL Statement
| Any valid T-SQL statement, which will be executed in the context of the Data Academy database.
|
Data Loader
| A generated LOAD-it data loader package.
|
Transformation \ Star Space
| A transformation (TRANSFORM-it) or star schema (STAR-it)
|
CUBE-it LITE Cube
| Processes* a CUBE-it LITE Cube
|
CUBE-it Cube Space
| Processes* a CUBE-it Cube Space
|
SSIS Package
| Allows you to run any SSIS package saved on the local SQL Server.
|
Orchestration Status Check
| Checks the status of the last run of a different Orchestration |
* Processing adds new data to the cube, but will not intoduce new properties (dimensions, measures, etc.) until the cube is re-
built within Data Academy.
Linking Movements
Once you have added movements to your orchestration, you need to link them in the desired sequence, so that the data loaders run first, followed by the transformations, and finally the cubes.
To link movements, select two movements in sequence, using a simple mouse click. Then click on the "Link" button on the toolbar. Data Academy will redraw the screen appropriately. To "break" a link, select the link itself, and click the "Unlink" button on the toolbar.
Building Your Package
When you have defined your orchestration, you will need to build ("Generate") it, to create an SSIS package which will sequentially run all of the steps which you have defined, logging its progress (and any errors) to the Data Academy Data Services Event Log. Click on the 
icon on the main ORCHESTRATE-it screen in order to do this.
Running Your Package
You can run your package from the ORCHESTRATE-it screen, but more likely you will want to schedule it, using the SQL Agent. You will find that an Agent Job has been defined for just this purpose, so all you need to do is use Management Studio to establish a schedule!