Free SQL Help for Devs, DBAs, and the Curious

Power BI, Microsoft's suite of business analytics tools, has brought forth an era of democratized data analysis. Composite models in Power BI allow users to have tables in their reports that are DirectQuery and Import at the same time. Thus, optimizing these composite models has become a crucial aspect of leveraging Power BI to its full potential. In this post, we will explore some strategies to tune your composite models for enhanced performance using SQL Server 2022, Azure SQL, Microsoft Fabric, Delta Lake, OpenAI + SQL, and Databricks.

Composite models in Power BI are versatile, but they can also be complex and potentially slow if not properly optimized. One common way to improve their performance is by adopting a star-schema design. In a star schema, there are fact tables and dimension tables. Fact tables contain the metrics, measurements or facts of a business process, while dimension tables contain descriptive attributes related to fact data.

-- SQL code for creating a star-schema
CREATE TABLE SalesFact (ProductKey INT, OrderDateKey INT, CustomerKey INT, SalesAmount DECIMAL);
CREATE TABLE ProductDimension (ProductKey INT, ProductName VARCHAR(100), ProductCategory VARCHAR(100));
CREATE TABLE DateDimension (OrderDateKey INT, OrderDate DATE, Month VARCHAR(50), Year INT);
CREATE TABLE CustomerDimension (CustomerKey INT, CustomerName VARCHAR(100), CustomerRegion VARCHAR(100));

In SQL Server 2022, the introduction of Temporal Tables can be particularly useful in maintaining historical data in your star schema design. Temporal Tables allow SQL Server to automatically manage history of data. This can be beneficial in scenarios where you need to analyze trends or patterns over time.

-- SQL code for creating a Temporal Table
CREATE TABLE SalesFact
(
   ProductKey INT,
   OrderDateKey INT,
   CustomerKey INT,
   SalesAmount DECIMAL,
   SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
   SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Azure SQL offers a range of built-in intelligence features that can help optimize your composite models. One such feature is the Automatic Tuning, which uses AI to automatically optimize your database performance. In addition, Azure SQL's Query Performance Insight tool can provide detailed insights into your database's workload, helping you identify potential performance bottlenecks.

-- SQL code to enable Automatic Tuning
ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Microsoft Fabric, an open-source framework for building distributed systems, can be used to distribute your data across multiple nodes, reducing the load on any single node and thereby improving performance. Delta Lake, an open-source storage layer that brings ACID transactions to Apache Spark™ and big data workloads, can be used in conjunction with Databricks to ensure data reliability and speed up query performance. OpenAI + SQL can help you automate the generation of SQL queries, further enhancing productivity.