Table Transformations¶

Different ways you can optimize and perform table transformations.

In this module: schema behavior · aggregate tables · table inserts · snippets

Schema behavior¶

Database schemas let you apply a specific schema to a table or a set of tables. They are useful for limiting access to tables that are not needed for reporting, which simplifies building reports from the data warehouse.

Schemas can be applied to data warehouses, data sources and individual tables. Precedence runs from most specific to least specific:

1. Schema settings applied at the table level win over
2. settings applied at the data source, which win over
3. settings applied at the data warehouse.

These are the schema behavior options available:

Use schemas to separate valid tables¶

Timextender uses the schema with the Main Default Schema behavior as the destination for any ETL process — it is the default destination for tables newly created during ETL. The Main Valid Schema, in contrast, is applied to all valid tables in the data areas. It conceals the tables involved in the ETL process so that other users only see the relevant items.

Create ETL schema as default and set MDW as valid schema¶

At the table level you can change schemas by adding or setting the schema you want from the drop-down in the table settings.

1. Right-click → Database Schemas → Add Database Schema under the MDW data area.
2. Enter ETL and create the schema.
3. Once created, select Schema Behavior → Main Default Schema for ETL.
4. Select Main Valid default schema for MDW.

Aggregate tables¶

Aggregate tables are streamlined versions of existing tables. They summarize data where the detailed transactional records are unnecessary — financial or sales reporting, for example. This lets you report directly from the data warehouse, so analysts get to the essential customer metrics quickly.

These aggregation types are available:

Aggregate table for monthly sales by customer¶

1. Select Add Aggregate Table under Advanced on DSA.SalesOrderDetail.
2. Enter MonthlySalesByCustomer as the name for the aggregate table.
3. Select CustomerId and OrderDate (value as Month) as the Group By parameters.
4. Select LineTotal as the aggregate parameter and Sum as the aggregate type.
5. Finish the table with OK.
6. Deploy and execute.

Table inserts¶

Inserting rows of data from one table into another is called a table insert. It is a simple concept that maps data from one source into a table, and it comes with other important functionality such as incremental loads. You can also build SQL unions using table inserts.

Add a table insert¶

1. Add TransactionHistory and TransactionHistoryArchive to the DSA.
2. Select Add Table Insert from Advanced on TransactionHistory.
3. Provide Transactions as the name, TransactionHistoryArchive as the insert-from table, and the data destination table (Raw or Valid).
4. Use the Auto map function to speed up the process.
5. Alternatively, select each mapping manually in the Field Mapping area.

Add a table insert selection rule¶

1. Select Add Table Insert Selection Rule from Table Inserts on your destination table.
2. Select the operator you want to use to select the data.
3. Add the value to select the data. Click Add.
4. Deploy and execute the table.

Related reference¶

• Slowly Changing Dimensions — track history on the tables you transform.
• Optimization — performance settings for the tables you build here.
• Prepare instance — where these transformations live.