Training Module:
Learn about the options for your Presentation Layer and why Date dimensions are relevant.
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:
- Rename the Data Area to "MDW".
- Add a Database Schema named "MDW"
- 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 due to unforeseen errors. 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:
Add MDW Data Area
- Open the MDW Instance
- Right click on Data Areas > Add Data Area
- Enter name: MDW
- Click OK
Move Tables into the MDW
- Add the following tables to the MDW:
- Product
- Person
- Customer
- SalesOrderDetail
Rename Sales Order Detail
- In MDW rename the table SalesOrderDetail to SalesTransactions
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
- In MDW right click Tables > Add Date Table
- Name: Date
- Start Date: 2011-01-01
- Reorder the tables so the new Date table is first
Add Date Table Relations
- Create a relation from Date.DateValue to SalesOrderDetail.OrderDate
- Create a relation from Date.DateValue to SalesOrderDetail.ShipDate
configure incremental load
- Right-click MDW > Automate > Set up Incremental Load
- Select Fields to Include in Primary Key
- Check for accurate Primary Keys, click Next
- Select Fields for Incremental Selection Rule
- Click Auto Suggest, click Next
- Select Tables for Hard Deletes
- Don't select anything, click Next
- Select Tables for Soft Deletes
- Don't select anything, click Next
- Review Selections
- click Next
- 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 Bellow
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
give FEEDBACK
