Training Module:
Sales Transactions
How to denormalize fact tables, create calculated fields, and custom views.
Table of ContentS
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).
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.
Add Sales Order Tables to DSA
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.
Field Calculations &
Custom Views
go to Table of Contents
Create Calculated Field
[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.
WHERE [OnlineOrderFlag] = 1
exercise import table and transform data
Use what you've learned so far to complete the following exercise.
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