Foundations of Semantic Models

1. Foundations of Semantic Models

In this lesson, we’ll understand the different types of Semantic Models, uncover techniques to boost performance, and explore how to efficiently manage large datasets with advanced features.

2. Default Semantic Model

In our exercises so far, we’ve worked with Default Semantic Model, which is automatically created when you set up a Lakehouse or Warehouse. You can easily edit it in the SQL Analytics Endpoint under the model section. While it’s quick to get started and great for small datasets, it does lack advanced features like Row-Level Security and hierarchies.

3. Custom Semantic Model

Now, let’s explore the Custom Semantic Model, which gives you even more control and flexibility than the default. You can create it using the New Semantic Model option, and instead of editing in the SQL Endpoint, you’ll get a full-featured editor. This model supports advanced features like calculated columns, hierarchies, and Row-Level Security,which we’ll cover in-depth later making it perfect for managing larger, more complex datasets.

4. Brief Overview of Dataset Modes

Now that we understand the types of Models, let’s look at how we can load data into them. First, Import Mode loads data into memory, providing fast queries but requiring regular refreshes. Next, DirectQuery Mode accesses live data for real-time results, though performance may slow with on-demand retrieval. And finally, Direct Lake Mode, it’s the best of both worlds, giving us near real-time access from OneLake and no refreshes needed. In Fabric, this is the mode we’ll be using most.

5. Composite Dataset Mode

While each dataset mode has its strengths, Composite Mode offers a unique solution for handling both real-time and static data within a single dataset. Imagine you have a Sales table that updates frequently, so you’d use DirectQuery for live data access. At the same time, your Product and Customer tables, which don’t change as often, could be in Import Mode for faster querying. However, balancing both modes can add some complexity!

6. User Defined Aggregations

Building on this, let’s explore how Aggregations optimize performance by summarizing large datasets into smaller, faster tables, reducing the need to query every detail. For example, imagine working with daily sales data from multiple stores over a year. Running reports on that would take forever! With aggregations, you can summarize it into monthly totals. Power BI can now use the summary table for faster reports without scanning every transaction. Aggregations are ideal when large datasets slow down reporting, especially for summary-level data like monthly or regional sales. By processing less data, your reports become quicker and more efficient.

7. Large Datasets in Power BI Premium

Another powerful way to optimize large datasets is through Power BI's Premium large dataset format, which handles data over 10 GB by storing it in compressed in-memory storage for faster queries. It supports incremental refresh, updating only the newest data for better performance. Additionally, the XMLA endpoint allows efficient read and write operations, ideal for managing large, complex datasets. These features ensure smooth handling of massive data while keeping reports fast.

8. Let's practice!

Let’s apply these concepts!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.