Chapter 9. Designing Import and DirectQuery Data Models

This chapter utilizes the queries described in Chapter 8, Connecting to Sources and Transforming Data with M, to create both an import and a DirectQuery Data Model. Relationships are created between fact and dimension tables to enable business users to analyze the fact data for both Internet Sales and Reseller Sales simultaneously by using common dimension tables and across multiple business dates. A combination of relationships, bidirectional cross-filtering, and DAX measures will be used to support the analysis of actual sales data versus the Annual Sales and Margin Plan. Additionally, the product dimension table is enhanced with a hierarchy to enable simple drill up/down, and a custom sort order is applied to columns in the date dimension table.

This chapter also includes recommended practices for model metadata, such as data categories, and tips to improve the usability of Data Models, such as a simplified field list. Finally, we...