Sales Transactions¶

Denormalizing fact tables, creating calculated fields, and custom views.

In this module: adding sales tables to DSA · creating relationships and lookups · calculated fields

Transaction table granularity¶

Most business processes happen in batches. At a grocery store you pick up ten items and buy them all at once. Transactions work this way across business: a single "order" or "header" transaction, containing multiple detailed line items. In databases that's a set of related tables, each with its own granularity.

In AdventureWorks this is the SalesOrderHeader table, with the granularity of one sales order, and the SalesOrderDetail table, with the finer granularity of one item sold.

To reach a star schema and simplify analysis, denormalize the lower-granularity header values down into the higher-granularity detail table.

Add the sales order tables to DSA¶

1. Open the Table Selector for the Ingest instance.
2. Search Salesorder.
3. Select SalesOrderHeader and SalesOrderDetail and add them to the DSA tables.
4. Reorder them after the Product table, as: SalesOrderHeader, then SalesOrderDetail.

Create relations and lookups¶

1. Create a relation from SalesOrderHeader.SalesOrderID to SalesOrderDetail.SalesOrderID.
2. Add these fields as lookups on SalesOrderDetail:
   • OrderDate
   • ShipDate
   • OnlineOrderFlag
   • CustomerID
   • SalesPersonID

Create a calculated field¶

Field transformations let you modify existing data in many ways — reverse the sign of numeric values, trim fields, return a set number of characters, and so on.

1. Add a new field to SalesOrderDetail.
2. Field name: GrossLineTotal.
3. Data type: numeric.
4. Open Field Transformations for GrossLineTotal.
5. Select Custom Value and click Add.
6. Multiply order quantity by unit price:

[OrderQty]*[UnitPrice]

7. Deploy and execute.

Create a custom view¶

Custom views are a powerful way to bring in custom SQL. Use parameters wherever possible, then map the custom view fields so data lineage is maintained.

1. Drag SalesOrderDetail onto the Views node.
2. Name the view Online Orders.
3. Add this WHERE clause to the create-view statement:

WHERE [OnlineOrderFlag] = 1

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

Reference: Custom Views · Use parameters in your scripts, views and calculations

Exercise¶

Import a table and transform data, using what you have learned:

1. Add a new table, PurchaseOrderDetail, from the Ingest instance to DSA, and preview it.
2. Transform the RejectedQty field so all positive values become negative.

Quiz¶

Worth being able to answer these before the Solution Architect exam:

• How many transformations can you add to a field?
• What script language is used in the Custom Transformation window?
• Can you do multiple lookups on one conditional lookup field?

Next in the course¶

Finalize the Presentation Layer · Deliver Instance