Training Module:

Finalizing tHE Modern Data Warehouse

Learn about the options for your Presentation Layer

and why Date dimensions are relevant.

Table of ContentS

• Presentation Layer
  
• The Presentation Layer
  
• Data Area Setup
  
• Add MDW Data Area
  
• Add Tables from DSA to MDW
• Rename Sales Order Detail
• Date Dimension
  
• What is a Date Dimension?
  
• Add a Date table
• Add Date table Relations
• Incremental Load: DSA to MDW
  
• Automate MDW Incremental Load
  
• Configure Incremental Load
  

Presentation Layer

go to Table of Contents

The Presentation Layer

Once data has been properly cleaned, consolidated and modeled it is made available to users. This final stage of the data warehouse is known as the Presentation Layer. Depending on the size and overall performance of your data warehouse resources, this layer may be a completely separate set of tables, or for reduced data duplication, you can simply keep the data right where it is.

In-place presentation layer

Because data is not moved again, this option results in better performance of overall data warehouse processing which makes it the ideal choice if processing speed is a higher priority than uptime.

To take this approach:

1. Rename the Data Area to "MDW".
2. Add a Database Schema named "MDW"
3. On each table you wish to present to users:
• Right-click > Table Settings > Table Schemas > Valid schema: MDW
  

separate data area

By separating data cleansing and transformation from the Presentation Layer, you reduce risk of the presentation layer going offline during scheduled executions. This makes this the ideal choice if uptime is a higher priority than processing speed.

To take this approach, continue to the next tutorials below:

Data Area Setup

go to Table of Contents

Add MDW Data Area

1. Open the MDW Instance
2. Right click on Data Areas > Add Data Area
3. Enter name: MDW
4. Click OK
   

Add Tables From DSA to MDW

1. Add the following tables to the MDW:
1. Product
   
2. Person
3. Customer
4. SalesOrderDetail
   

Rename Sales Order Detail

1. In MDW rename the table SalesOrderDetail to SalesTransactions
   

Date Dimension

go to Table of Contents

what is a date dimension?

A Date dimension is a table in a data warehouse that maintain various attributes about any given day within a set period. For each date it has attributes for year, quarter, month, week, day of the week, weekend or weekday and so on. Similar to how other dimensions are used, the date dimension makes it easy to filter or categorize fact data based on certain date periods, allowing for efficient querying and analysis of time-based data.

By filtering on a single date dimension attribute, you could easily show:

• What were the total sales for 2015?
  
• Do sales dip on Wednesdays?
• Did sales increase from Q1 to Q2?
  

Add a Date Table

1. In MDW right click Tables > Add Date Table
1. Name: Date
   
2. Start Date: 2011-01-01
2. Reorder the tables so the new Date table is first
   

Add Date Table Relations

1. Create a relation from Date.DateValue to SalesOrderDetail.OrderDate
   
2. Create a relation from Date.DateValue to SalesOrderDetail.ShipDate
   

Incremental Load: DSA to MDW

go to Table of Contents

Automate MDW incremental load

1. Right-click MDW > Automate > Set up Incremental Load
   
2. Select Fields to Include in Primary Key
1. Check for accurate Primary Keys, click Next
   
3. Select Fields for Incremental Selection Rule
1. Click Auto Suggest, click Next
   
4. Select Tables for Hard Deletes
   
1. Don't select anything, click Next
   
5. Select Tables for Soft Deletes
1. Don't select anything, click Next
   
6. Review Selections
   
1. click Next
   
7. Deploy & Execute the MDW.
   

configure incremental load from DSA

Read more on setting up incremental load

1. Hold the right mouse and drag the desired table from DSA to MDW and select Add New Table
   
2. Right click the Data Area and select Automate-> Set Up Incremental Load
   
3. Include the desired table for incremental load
4. Include the primary key if its not already set
5. Manually select field for incremental load or use the Auto Suggest option
6. Select table for Hard Deletes
7. Deploy & Execute the MDW.
   

Section Quiz...

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

When should you choose to keep your Presentation Layer "in-place"?

When should you Present your data in a separate Data Area?

Why would my Data Warehouse need a Date Dimension?

When you're ready, see Answers Below

Section Quiz Answers

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

When would you choose to keep your Presentation Layer "in-place"?

When processing speed is more important than uptime.

When should you Present your data in a separate Data Area?

When uptime is more important than processing speed.

Why would my Data Warehouse need a Date Dimension?

In order to easily filter related fact table measures based on various date related attributes.

Congratulations! You've completed the training module

Finalizing Modern

Data Warehouse