Fabric Terms

Capacity

Lakehouse Managed Table

  1. A managed table is a physical table that stores data in a Lakehouse.

Lakehouse Shortcut

  1. Shortcuts in the Files section are typically used for referencing external files or data sources, not for querying data directly.
  2. Shortcuts in the Tables section are used to reference structured data sources, making it easier to query and analyse data in a Lakehouse environment.
  3. Shortcut will not copying the external data into the Lakehouse.

Lakehouse View

  1. Views in Lakehouse allow users to create virtual tables that reference data from multiple tables across different Lakehouses.

Power BI DAX Formatter

  1. DAX Formatter in DAX Studio is a tool used for formatting DAX expressions in Power BI.

Power BI Model Explorer

  1. Model Explorer allows users to navigate and manage the data model, including tables, columns, relationships, and measures.

Power BI Tabular Editor (Best Practice Analyser)

  1. The Best Practice Analyser in Tabular Editor is a too that helps identify potential issues and optimisations in a Power BI semantic model.
  2. It can specifically highlight surrogate key columns with the Summarise By property set to a value other than None, making it easier to identify and optimise these columns.

Tabular Editor

  1. Tabular Editor allows users to programmatically change column properties, such as hiding columns, setting nullable, summarisation, availability in MDX, and marking columns as key columns.

Tenant

Subscription

Developer can deploy in Dev Test Workspace and view Prod Workspace

To setup a Dev, Test, and Prod workspaces, where Developers can only deploy to Dev and Test workspaces, and view Prod workspace.

You need

  1. Admin access to Deployment pipeline, to manage pipeline and deploy items.
  2. Contributor access to Dev, and Test workspaces.
  3. Viewer access to Prod workspace.

Fabric Configuration

Enable read-write access to dataset stored in Fabric via XMLA Endpoint

  1. Capacity settings determine the access level and permissions for datasets within the workspace.
  2. Adjust the capacity settings to allow read-write / read-only access via the XMLA Endpoint will enable the desired functionality for dataset stored in Fabric.

Create and Publish custom Direct Lake semantic model using External Tools

  1. From the Tenant Settings, set Allow XMLA Endpoints and Analyse in Excel with on-premises datasets to Enabled.
  2. From the Capacity Settings, set XMLA Endpoint to Read-Write.
  3. From the Tenant Settings, set Users can create Fabric items to Enabled.

Lakehouse

Handle frequent schema change in Copy Activity

  1. From the Destination Tab, set Table action to Overwrite.

Convert Lakehouse files from CSV to Delta format for Power BI Reporting

  1. Using the Load to Table feature in Lakehouse explorer.
  2. It allows you to convert the CSV files into the Delta Format with V-Order optimisation enabled.

SQL Analytics Endpoint

  1. Prevent new tables added to Lakehouse from being added automatically to default Semantic model of the lakehouse.

Performance Optimisation - Lots of useless logs, lots of small files

  1. Run the VACUUM command on a schedule.
    1. VACUUM command will remove files that are not referenced by Delta Table in the past 30 days, while ensuring the transaction log remains consist and the ACID properties of the table are maintained.
  2. Run the OPTIMIZE command on a schedule.
    1. OPTIMIZE command with Z-order or V-order parameter is used for optimising the physical layout of the data based on the values of a specific column.
    2. It is not intended for removing unreferenced files and maintaining the ACID properties of the Delta table.

Query S3 data with SQL Endpoint

  1. Use the Delta format.
  2. Create a shortcut in the Tables section.
    1. Shortcuts in the Tables section are used to reference structured data sources, making it easier to query and analyse data in a Lakehouse environment.

Prevent new tables been added to Lakehouse automatically

  1. Configure SQL Analytics Endpoint settings allows you to control the behaviour of new tables being added to the default semantic model of the lakehouse.
  2. By adjusting these settings, you can prevent new table from being automatically included in the semantic model.

Query data from multiple tables in different Lakehouses without copying data

  1. Views in Lakehouse allow users to create virtual tables that reference data from multiple tables across different lakehouses.

Data Factory

Lookup Activity

  1. The Lookup activity is to execute the stored procedure in a warehouse, and retrieve the returned value for downstream activities in a pipeline.
    1. It allows you to query a data store and retrieve a single row, which is suitable for executing the stored procedure and making its results available for further processing in the pipeline.

Pass values across multiple steps in Data Factory Pipeline

  1. Lookup activity can help you to execute the stored procedure in a warehouse, and retrieve the returned values for downstream activities in a pipeline.

Power BI

Query Folding

  1. A process where Power BI pushes the query back to the data source, to filter and transform the data before importing it.
    1. i.e. let the data source engine to process the data, before ingesting it into Power BI.
  2. This process can help optimise performance by reducing the amount of data brought into Power BI.

Incremental Refresh

  1. When configuring incremental refresh in Power BI, the delta type of the column used to partition the data is critical for identifying the new data to refresh.
  2. If the delta type of the column has changed, it can lead to issues in identifying and refreshing new data, potentially causing the refresh failure.

XMLA Endpoint

  1. XMLA Endpoint is a connectivity feature that enables direct access to the underlying Analytics services engine of Fabric data model.
  2. It allows external tools, like Power BI, SSMS, and Excel to connect directly to Fabric datasets using XMLA protocol for advanced data management, analysis, and reporting.

Power BI Mode

  1. Import Mode.
    1. Imports all data into Power BI’s in-memory storage, offering fast query performance.
    2. Ideal for small to medium-sized datasets where fast performance is crucial.
  2. DirectQuery mode.
    1. Queries data directly from the data source in real-time, without storing it in Power BI.
    2. Useful for large datasets that exceed Power BI’s in-memory storage limit.
    3. DirectQuery will be used by DAX queries when user interact with a report built from the Direct Lake semantic model.
    4. Direct Query allow queries can be sent directly to the data source.
  3. Dual or Composite mode.
    1. Dual mode combines both Import and DirectQuery mode.

Display all rows including duplicates in PowerBI visual

  1. Modify the Summarise By property for all columns will ensure that all rows are displayed in the visual.
  2. By changing the summarisation method, duplicate rows will be included in the visual output, allowing all rows to appear.
  3. Other wrong options
    1. Add a unique field to each row. This will not guarantee that all rows are displayed in the visual. It may help in distinguishing between rows, but it does not address the issue of duplicate rows not being displayed.

Distinct Count vs Unique Count in PowerQuery

  1. Distinct Count.
    1. Distinct Count refers to counting the number of different values in a column, regardless of their frequency.
    2. for a list of [A, B, A, C, B, D], the distinct values are {A, B, C, D}, and the distinct count is 4.
    3. You use Distinct Count when you want the number of different entities, without caring how often each appears.
  2. Unique Count.
    1. Unique Count, on the other hand, counts values that appears only once in the column.
    2. for a list of [A, B, A, C, B, D], the unique values that appearing only once are {C, D}, so the unique count is 2.
    3. Unique Count is useful when you are interested in values that do no repeat in the data.

Calculate Duplicated Rows in PowerQuery

To calculate the number of duplicated rows, you can use

$$ \text{Duplicated Rows} = (\text{Total Rows} - \text{Distinct Count}) + (\text{Distinct Count} - \text{Unique Count}) $$

For example, with a list of [A, B, A, C, B, D]

  • Total Rows = 6
  • Distinct Count = 4, i.e. {A, B, C, D}
  • Unique Count = 2, i.e. {C, D}
  • Duplicated Rows = (6 - 4) + (4 - 2) = 4
  1. The first part calculates the number of rows that are not distinct, i.e. to get duplicated values {A, B}
  2. and the second part calculates the number of rows that are not unique, i.e. to get unique values {A, B}
  3. The sum is to get all the duplicated rows, which is {A, B, A, B} in this case.

Power BI File Formats

  1. PBIX.
    1. Power BI Desktop files that contains reports, data models, and visualisations.
  2. PBIT.
    1. Power BI Desktop template files, that contains a pre-defined set of data connections, queries, and data model settings.
  3. PBIDS.
    1. Power BI Data Source files, that contain information about data sources and their connection details.
  4. PBIP.
    1. PBIP is used for saving semantic models in Power BI Desktop when using the Tabular Model Definition Language (TMDL) extension.
    2. It allows for bulk changes to be made to the model.

Reduce Query Execution time for visuals on all pages on top of large dataset

  1. Use user-defined aggregation.
    1. User-defined aggregations allow you to pre-calculate and store aggregated values in the model, reducing the query execution time by avoiding the need to aggregate data at runtime.
  2. Use automatic aggregation.
    1. Automatic aggregation enables Power BI to automatically aggregate data at different levels of granularity, optimising query execution time by reducing the amount of data that needs to be processed for visuals in report.
  3. Other wrong options
    1. Query caching stores the results of previous queries in memory allowing faster retrieval of data when same query is executed again.
    2. While query caching can improve performance, it may not directly reduce query execution time for visuals on all pages for the reports.

Incremental Refresh fails in Power BI report

  1. The delta type of the column used to partition has changed.
    1. When configuring incremental refresh in Power BI, the delta type of the column used to partition the data is critical for identifying new data to refresh.
    2. If the delta type of the column has changed, it can lead to issues in identifying and refreshing new data, potentially causing the refresh failure.

Semantic Model

Enable Scale-out for a Semantic Model

  1. At the Semantic Model level, setting the Large dataset storage format to On.
  2. This setting allows the semantic model to take advantage of scale-out processing capabilities for improved performance scalability.

Analyse frequently used columns in memory

There are some DAX query load unnecessary columns into memory. To identify the frequently used columns that are loaded into memory, you can

  1. Use the Vertipaq Analyser tool.
    1. The Vertipaq Analyser tool is specially designed to analyse and optimise data models create in Direct Lake mode.
    2. It provides detailed insights into the data model, including information on column usage and memory consumption.
    3. It makes it a suitable option for identifying frequently used columns loaded into memory.
  2. Query the $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS Dynamic Management View (DMV)
    1. Query this DMV provides information on the storage segments of columns within tables.
    2. By analysing this DMV, users can identify the frequently used columns that are loaded into memory.

Performance Optimisation

  1. Split OrderDateTime field into Date and Time columns.
    1. as it can help to reduce the memory used by Model, and optimise storage of data and time values.
  2. Replace TotalSalesAmount with a measure can help reduce the memory used by Model.
    1. as measures are calculated at query time, and do not require physical storage in the model.

Prevent implicit measures

  1. Use Microsoft Power BI Desktop.
    1. By using Power BI Desktop, you can enforce restrictions on the use of implicit measures and prevent report creators from populating visuals with them.
  2. Tabular Editor.
    1. With Tabular Editor, you can define restrictions and configurations within the model to prevent the use of implicit measures by report creators.

Identify Surrogate key column for SCD Type 2 table in Semantic Model

  1. Best Practice Analyser in Tabular Editor.
  2. It is a tool that helps identify potential issues and optimisations in a semantic model.
  3. It can specifically highlight surrogate key columns with the Summarise By property set to a value other than None.

Ensure user always use Direct Lake mode with semantic model

  1. From Model, configure the Direct Lake Behaviour option.
  2. It ensure that users’ queries always use Direct Lake mode.
  3. This option specifically controls the behaviour of the model to ensure that queries interact with the data in Direct Lake mode.

PySpark

Large dataset join a small dataset

  1. Use broadcast function in PySpark allows the smaller DataFrame to be broadcasted to all nodes in the cluster, reducing data shuffling during the join operation.
  2. This helps minimise the amount of data movement across the cluster and improves the performance of the join operation.

Note

  • crossJoin will create a Cartesian product between two datasets, resulting in combination of every row from the first DataFrame with every row from the second DataFrame.

Necessary fields for Slow Change Dimension (SCD) Type2

  1. an effective start date and time
  2. an effective end date and time
  3. an surrogate key

Filver vs KeepFilters

  1. When the FILTER function uses a nested CALCULATE function, replacing it with the KEEPFILTERS function can reduce execution time.
    1. This is because KEEPFILTERS preserves the existing filter context, while FILTER function can create additional filter contexts that may impact performance.

Table Maintenance Operations

  1. OPTIMISE.
    1. Consolidates multiple small files into large file.
  2. V-Order.
    1. Applies optimised sorting, encoding, and compression to Delta parquet files to enable fast read operations across all the Fabric engines.
  3. Vacuum.
    1. Removes old files no longer referenced by a Delta table log.
    2. Files need to be older than retention threshold,and the default file retention threshold is 7 days.

Miscellaneous

Get maximum value of a column with PowerQuery

  1. Table.Profile function in PowerQuery is specifically designed to provide a profile of the data in a table, including summary statistics such as minimum, maximum, average, and count for numeric columns.
  2. It’s the most appropriate choice for identifying the maximum value for the numeric columns in the dataset during the PowerQuery step.

Microsoft Fabric Capacity Metrics App

  1. is designed to provide detailed information on the capacity and performance metrics of the Fabric tenant.
  2. By using the app, you can identify whether throttling is occurring by monitoring the compute resources being utilised by the warehouse queries.

Dynamic Management View (DMV)

  1. sys.dm_exec_requests
    1. provide information about each request that is executing within SQL Server.
    2. By querying this DMV, you can identify teh current status, wait time, and other relevant details of the report query that is taking longer than expected to complete
  2. sys.dm_exec_sessions
    1. contains information about the active sessions in SQL Server.
  3. sys.dm_exec_connections
    1. provides information about the current connections to SQL Server.
  4. sys.dm_pdw_exec_requests
    1. Specific to Azure Synapse Analytics, and provide information about the requests executing within the distributed query processing engine.