DP-600 Course Notes

This objective covers building warehouse queries using no-code tools like the Visual Query Editor, SQL, KQL, and DAX. The core task involves selecting, filtering, and aggregating data based on the provided data sources and transformations. This card focuses on selecting the appropriate Microsoft Fabric data store based on specific workload requirements. The core principle is aligning store characteristics – schema, query language, transaction support, and governance – with the needs of the analytical application. This objective covers establishing the foundational processes for bringing data into Fabric, ensuring it's discoverable, and designed for operational needs. It centers on selecting the appropriate ingestion approach based on source characteristics and downstream requirements. The OneLake catalog provides a central repository for discovering governed Fabric data items, exposing metadata like lineage, endorsement, and sensitivity. Real-Time hub is the dedicated discovery and management surface for streaming and event data, optimized for low-latency access and real-time analytics. Copying data is a deliberate design choice when a managed local copy, transformation boundary, or independent retention is required. A shortcut, relying solely on a connection, is suitable only when direct referenced access is sufficient. This objective covers creating reusable relational objects within Fabric Warehouse using T-SQL. These objects – views, stored procedures, and functions – are designed to encapsulate logic and facilitate data transformation and serving. This objective covers the core design principles of a star schema, emphasizing the relationship between fact and dimension tables, the importance of denormalization for performance, and the correct implementation of pre-aggregations and surrogate keys. This objective covers preparing data for analysis by addressing common data quality issues. It covers techniques for identifying and resolving duplicates, handling null values, ensuring correct data types, and selecting appropriate join strategies.

• Visual Query Editor vs. SQL: Visual Query Editor is a no-code interface for warehouse transformations; SQL provides programmatic control and is required for reusable database objects.

• Prioritize the no-code approach with Visual Query Editor whenever possible, especially for initial prototyping and simpler queries.

Implement workspace, item, row, column, object, and file access controls within OneLake. This involves understanding the various mechanisms for granting and restricting access to data, including workspace roles, item sharing, and OneLake security roles, and recognizing that effective access requires a layered approach. Configure Git integration, PBIP projects, and deployment pipelines to manage Power BI Desktop project definitions within a Fabric lakehouse environment. This objective covers applying sensitivity labels and endorsing analytics items within OneLake. It emphasizes that labels are protective measures, not access controls, and that endorsement signifies a trusted, recommended item. This objective covers performing impact analysis and managing reusable semantic assets through XMLA. Understanding dependencies and utilizing XMLA's capabilities are key to minimizing disruption when modifying shared analytical models.

• Workspace roles offer broad access to items within a workspace, while item sharing provides more targeted access to a specific item. Use workspace roles for general access needs and item sharing for more controlled access.

• Understand the hierarchy of access control. Access is granted through multiple layers – workspace roles, item permissions, sharing links, OneLake roles, and potentially Microsoft Entra groups. Always verify access across all applicable layers.

This objective covers establishing star schema relationships and selecting appropriate storage modes to optimize data access and performance. Understanding star schema design and utilizing bridge tables when necessary are key to building effective models. Optimize Direct Lake semantic models using Direct Lake's capabilities, including DirectQuery fallback (SQL only), incremental refresh, and DAX tuning to improve performance and manage capacity. This objective covers leveraging DAX features to create dynamic and reusable calculations. Key techniques include using variables for readability and performance, employing iterators like SUMX for row-by-row aggregation, strategically utilizing CALCULATE and FILTER for context modification, applying windowing functions for time intelligence and relative calculations, and leveraging information functions for conditional logic without altering the underlying data. Implement calculation groups, dynamic format strings, and field parameters to manage measure complexity and enable user interaction within reports. This objective covers designing and managing semantic models that combine diverse data sources and storage modes – specifically composite models – to leverage the capabilities of Power BI.

• One-to-Many vs. Many-to-Many: One-to-many uses a direct dimension-to-fact relationship. Many-to-many *requires* a bridge table to define the grain and filter path.

• Focus on the business requirements driving the model. The correct relationship cardinality and storage mode are determined by the data and how it will be used.