2.2. Add a database table

Under the data connector, find the [Sales].[SalesOrderDetail] and [Sales].[SalesOrderHeader] tables.

Drag the [Sales].[SalesOrderDetail] table to the canvas.

When you add the first table, Dundas BI automatically creates a simple ETL process consisting of two transforms, with data flowing from left to right. Note that the name of your new data cube (e.g. DataCube1) is displayed in the status bar at the bottom.

The first node in the ETL process is a SQL Select transform, which represents a query to select data from the [Sales].[SalesOrderDetail] table.

The second node is a Process Result transform, which represents the output or result of the data cube (ETL process). This transform doesn't do any data processing but it allows you to configure the measures and hierarchies that will be made available to downstream items, such as metric sets.

An ETL process can have multiple inputs (for example, multiple Select transforms that query data from different databases) but always only a single output (the process result).

2.3. Add a second database table

Add the second table [Sales].[SalesOrderHeader] to the canvas. The second SQL Select transform appears highlighted, which means the transform is not yet connected to the ETL process.

2.4. Insert a join transform node

From the toolbar, click Insert Common or Insert Other to see the transforms that you can add to the canvas. What happens when you insert one of these transforms depends on what you've already selected on the canvas. If you don't have anything selected beforehand, this will insert the new transform and leave it unconnected. If you have selected an existing transform before inserting, the new transform will be placed just after the existing transform and connected to it. You can also select an existing connection link between two transforms and insert the new transform between as described next.

Click to select the connection link between the first SQL Select transform and the Process Result transform.

A Join transform is inserted into the ETL process between the first SQL Select and the Process Result. The Join transform appears highlighted because it requires two inputs but only one input is connected.

Instead of inserting a join transform manually and connecting the 2nd select transform to it, a shortcut is to simply drag a connecting line from the 2nd (unconnected) transform to the 1st transform. A join transform will be automatically inserted and connected to both inputs.

Choose the Join Type (which can be Inner Join, Left Join, Right Join, or Full Join).

Look at the two lists of database table columns. Each list corresponds to one of the inputs to the Join transform. To form a unique key binding for the join operation, drag a column from the first list and drop it over a column from the second list. In this example, Dundas BI automatically identifies SalesOrderID as the unique key, which you can see by the icon displayed beside each column that is part of the binding. Note that Dundas BI will set this binding for you automatically in some cases.

Use the checkboxes to select or de-select columns for inclusion in the output. For example, select OrderQty, ProductID, and SalesOrderID from the left list. Select OrderDate and SalesPersonID from the right list.

2.7. Join with a third database table

The ETL process so far does a join on two database tables. What if you add a third table? Since a join transform has only two inputs, you need to insert a second join transform into the ETL process to accomplish this.

First, go to the Explore window and locate the [Person].[Person] table. Drag the table to the canvas. The table appears as a third SQL Select transform which is highlighted because it is not connected to the ETL process yet.

Next, select the last connection link, click Insert Common from the toolbar, and click Join. This inserts a second Join transform into the ETL process. The Join transform is highlighted in color because it has only one input connected.

Click the SQL Select transform for the Person table, press and hold the CTRL key, and then click the Join transform that was just inserted. Click Connect from the toolbar. Everything is connected properly now and the final ETL process appears similar to the figure below.

2.8. Configure the 2nd join transform

Once the second Join transform is connected, it must be configured. Right-click (or long-tap) over the second Join transform and select Configure.

In the Join configuration dialog, choose Inner as the Join Type.

Form the unique key binding by dragging the SalesPersonID column from the left list and dropping it onto the BusinessEntityID column from the right list. Finally, de-select all columns in the right list except for FirstName. The de-selected columns won't appear in the output of the join transform.

2.9. Configure the process result

The ETL process is almost complete. The remaining step is to configure the measures and hierarchies that will be the output of this data cube.

Click the Process Result transform to see the Data Cube Elements panel, which shows the list of output measures and hierarchies for this data cube (ETL process). Measures and hierarchies are color-coded differently.

At this stage, you can optionally hide a measure or hierarchy from the output by clicking its grey minus icon on the right. The measure or hierarchy will be listed under a HIDDEN section but you can easily unhide it by clicking its plus icon. A measure or hierarchy hidden this way is hidden from the data cube output but will still be available for linking in hierarchy keys (for example, if you subsequently want to use this data cube to define a hierarchy).

Columns such as 'ProductID' are referred to as implicit hierarchies. You can add value to your data cube by replacing such columns with multi-level hierarchies which have been pre-defined outside of the data cube.

For example, to replace the ProductID column with an existing 'Product' hierarchy, click the Edit icon on the right of the ProductID column.

The shown list includes statistics for each column, such as the sum, average value, minimum and maximum values, and distinct count.

2.11. Check in

Check in your data cube from the toolbar so others can use it too.

2.12. Re-using Data Cubes

In most cases, the same process flow has to be repeated over and over in different data cubes. Re-using a data cube will allow users to create a basic data cube and re-use it as a part of other data cubes.

For example, you have an existing data cube using SalesOrderHeader and SalesOrderDetail tables:

2.12.1. Notes for re-using data cubes

A single source data cube cannot be used twice in the same data cube. This includes recursive use of the source data cube (using a data cube that uses a data cube).

If the source data cube is warehouse-cached, the target data cube will also be using data from the warehouse, unless a different storage type is selected for the target data cube. In contrast, if the source data cube is in-memory-cached, the target data cube will not be pulling data from memory.

If the source data cube has user-defined hierarchies, those hierarchies will be ignored and implicit ones used instead.