Training Module:
Sales Transactions
How to denormalize fact tables, create calculated fields, and custom views.
Table of ContentS
- Data Setup and Denormalization
- What is Deployment & Execution?
- Add Sales Order tables to DSA
- Denormalize Header to Detail
- Field Calculations & Custom Views
- Create Calculated Field
- Create Custom View
Data Setup & DenoRmalization
go to Table of Contents
what is deployment and execution?
deployment
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.
execution
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).
- 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.
- 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.
- _R Postfix: The table contains all the raw data.
- _T Postfix: The table contains all the transformations.
- _L Postfix: The table contains the logs and error.
- _M Postfix: The table contains all the messages.
Add Sales Order Tables to DSA
- Open the Table Selector for ODX
- Search Salesorder
- Select the SalesOrderHeader and SalesOrderDetail tables and add to DSA tables
- Reorder the tables following the Product table to read as follows:
- SalesOrderHeader
- 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.
- Create a relation from SalesOrderHeader.SalesOrderID to SalesOrderDetail.SalesOrderID
- Add the following fields as lookups on SalesOrderDetail:
- OrderDate
- ShipDate
- OnlineOrderFlag
- CustomerID
- SalesPersonID
Field Calculations &
Custom Views
go to Table of Contents
Create Calculated Field
- Add a new field to SalesOrderDetail
- Field Name: GrossLineTotal
- Data Type: Numeric
- Open Field Transformations for GrossLineTotal
- Select Custom Value and Add
- Add OrderQuanitity and multiply by UnitPrice
-
- Deploy and Execute
[OrderQty]*[UnitPrice]
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.
- Drag SalesOrderDetail to the Views Node
- Name the view "Online Orders"
- Add the below WHERE clause to the Create View Statement:
-
- Deploy the view
- Right-click the view > Read View Fields
- Right-click the view > Map Custom View Fields
- Click Auto-Map
WHERE [OnlineOrderFlag] = 1
exercise import table and transform data
Use what you've learned so far to complete the following exercise.
- Add a new table from ODX PurchaseOrderDetail to DSA, preview the table
- 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?
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
