Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is creating a semantic model in Microsoft Fabric. The model includes a 'Sales' table with a 'TransactionDate' column. For reporting purposes, users frequently need to analyze sales by year, quarter, month, and day. To support these time intelligence calculations efficiently without manually creating many calculated columns, what is the best practice for the 'TransactionDate' column?
- AUse the built-in 'Date Hierarchy' feature on the 'TransactionDate' column.
- BImport a separate 'Date' table and create a relationship to 'Sales'[TransactionDate].
- CCreate separate calculated columns for Year, Quarter, Month, and Day in the 'Sales' table.
- DMark the 'TransactionDate' column as a date table.
Show answer & explanationAnswer & explanation
Correct answer: B. Import a separate 'Date' table and create a relationship to 'Sales'[TransactionDate].
Using a separate, dedicated 'Date' table (often referred to as a calendar table or dimension table) and relating it to the fact table's date column is a best practice. This provides a single source of truth for date dimensions, supports advanced time intelligence functions, and prevents data duplication.
Why the other options are wrong
- A. The built-in 'Date Hierarchy' is convenient but has limitations for advanced time intelligence and can lead to performance issues with large datasets.
- C. Creating many calculated columns in the fact table duplicates data and is less flexible for complex time intelligence.
- D. Marking the 'TransactionDate' column as a date table is incorrect; a separate dimension table should be marked as such.
Date Dimension Table
A dedicated table in a semantic model that contains all date-related attributes (year, quarter, month, day, day of week, etc.) and is used to filter and group data based on time.
- Provides a single source for date-related analysis.
- Essential for complex time intelligence functions (e.g., YTD, MoM).
- Relates to fact tables on their date columns.
Memory trick: A single date table rules all time analyses.