Geometrical Illustration

Training Module:

Sales Transactions

How to denormalize fact tables, create calculated fields, and custom views.

Geometrical Illustration

Table of ContentS

Hexagons Background

Data Setup & DenoRmalization

what is deployment and execution?


TimeXtender creates or modifies existing tables, views, and stored procedures in the target storage database. Deployment is required when tables, views, fields, or lookups are added or changed.


TimeXtender transfers and cleans data, resulting in a populated table. After the execution is complete the table can be previewed or queried. Execution can be initiated manually or run on a schedule. TimeXtender can execute multiple threads of data simultaneously (configured by editing the default execution package).

  1. Differential Deployment: When enabled, TimeXtender calculates what steps require deployment (e.g. altering the raw table, or updating the data cleansing procedure.) and selects only those steps for deployment. When disabled, all steps are deployed.
  2. Managed Deployment : When enabled, TimeXtender calculates dependencies and deploys the objects in the optimal order. When disabled, you need to ensure objects are deployed in the correct order.

During the deployment and execution TimeXtender generates multiple objects in SQL and can be viewed in SSMS. The table name without the postfix is the valid table.

The following postfix tables are generated by TimeXtender as seen in the gif.

  1. _R Postfix: The table contains all the raw data.
  2. _T Postfix: The table contains all the transformations.
  3. _L Postfix: The table contains the logs and error.
  4. _M Postfix: The table contains all the messages.

Add Sales Order Tables to DSA

  1. Open the Table Selector for ODX
  2. Search Salesorder
  3. Select the SalesOrderHeader and SalesOrderDetail tables and add to DSA tables
  4. Reorder the tables following the Product table to read as follows:
    1. SalesOrderHeader
    2. SalesOrderDetail

Denormalize Header to Detail

Most business processes occur in batches. Take for example a trip to the grocery store, you purchase 10 items, but you buy them all at once. Transactions occur this way in many other areas of business as well as Sales, Orders, and Shipments, etc. You'll have a single "order" or "header" transaction, containing many smaller detailed line items within it. In databases this is handled by a set of related tables, each with their own Granularity. In AdventureWorks this is handled with the Sales Order Header table, with the granularity of a single Sales Order. And the Sales Order Detail table, with a finer granularity of a single Item sold.

    • In order to achieve the desired Star Schema as well as simplify analysis, we will denormalize the lower granularity "Header" values down to the higher granularity "Detail" table.
  1. Create a relation from SalesOrderHeader.SalesOrderID to SalesOrderDetail.SalesOrderID
  2. Add the following fields as lookups on SalesOrderDetail:
    1. OrderDate
    2. ShipDate
    3. OnlineOrderFlag
    4. CustomerID
    5. SalesPersonID
Hexagons Background

Field Calculations &

Custom Views

Create Calculated Field

  1. Add a new field to SalesOrderDetail
    1. Field Name: GrossLineTotal
    2. Data Type: Numeric
  2. Open Field Transformations for GrossLineTotal
    1. Select Custom Value and Add
    2. Add OrderQuanitity and multiply by UnitPrice

  3. Deploy and Execute


Create Custom View

Custom Views are a powerful tool to implement custom SQL Code. Be sure to use parameters whenever possible. Then you can Map Custom View fields to ensure data lineage is maintained.

  1. Drag SalesOrderDetail to the Views Node
  2. Name the view "Online Orders"
  3. Add the below WHERE clause to the Create View Statement:

  4. Deploy the view
  5. Right-click the view > Read View Fields
  6. Right-click the view > Map Custom View Fields
    1. Click Auto-Map

WHERE [OnlineOrderFlag] = 1

exercise import table and ​transform data

Use what you've learned so far to complete the following exercise.

  1. Add a new table from ODX PurchaseOrderDetail to DSA, preview the table
  2. Transform the field RejectedQty to change all the positive values to negative

Section Quiz...

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

In a field transformation, what is the Custom Value operator for?

Why should you denormalize Header/Detail into a single Fact Table?

Do Custom Views support Data Lineage?

Brushstroke Arrow Smooth Curve Down Small

When you're ready, see Answers Bellow

Section Quiz Answers

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

In a field transformation, what is the Custom Value operator for?

Custom Value operator allows you to define a custom field value using SQL code

(e.g. Calculated Field, Concatenation, etc.).

Why should you denormalize Header/Detail into a single Fact Table?

To achieve the desired Star Schema and simplify analysis.

Do Custom Views support Data Lineage?

Yes, by using parameters in the view statement remembering to Map Custom View Fields

want to Learn even more?

Learn even more advanced ways of using custom views from TimeXtender Tuesdays

Congratulations! You've completed the training module

Sales Transactions


Thumbs Up Illustration
Thumbs Up Illustration