Each SQL view should only retrieve the columns required for the dimension or fact table. If necessary, the views should apply business-friendly, unambiguous column aliases with spaces and proper casing. Dimension table views should include the surrogate key used for the relationship-to-fact tables, as well as the business or natural key column if historical tracking is maintained as will be shown by the customer dimension example later in this section.

Fact table views should include the foreign key columns for the relationships to the dimension tables, the fact columns needed for measures, and a WHERE clause to only retrieve the required rows, such as the prior three years. Given the size of many data warehouse fact tables and the differences in how this data can best be accessed per the Query design per dataset mode section earlier, dataset designers should ensure that the corresponding SQL views are efficient and appropriate for the dataset.

A robust date dimension table is critical for all datasets and thus its SQL view and/or M query has a few unique requirements. For example, it should include integer columns that can define the default sort order of weekdays as well as sequentially increasing integer columns to support date intelligence expressions. The date table should also include a natural hierarchy of columns (that is, Year, Year-Qtr, Year-Mo, Year-Wk) for both the Gregorian (standard) calendar as well as any custom fiscal calendar. These columns enable simple drill-up/down experiences in Power BI and report visualizations at different date granularities that span multiple time periods, such as the prior two years by week.