Write an SQL statement

As previously mentioned, you can import data into your Power BI model by using an SQL query. SQL stands for Structured Query Language and is a standardized programming language that is used to manage relational databases and perform various data management operations.

Consider the scenario where your database has a large table that is comprised of sales data over several years. Sales data from 2009 isn't relevant to the report that you're creating. This situation is where SQL is beneficial because it allows you to load only the required set of data by specifying exact columns and rows in your SQL statement and then importing them into your semantic model. You can also join different tables, run specific calculations, create logical statements, and filter data in your SQL query.

The following example shows a simple query where the ID, NAME and SALESAMOUNT are selected from the SALES table.

The SQL query starts with a Select statement, which allows you to choose the specific fields that you want to pull from your database. In this example, you want to load the ID, NAME, and SALESAMOUNT columns.

SQL
SELECT
ID
, NAME
, SALESAMOUNT
FROM

FROM specifies the name of the table that you want to pull the data from. In this case, it's the SALES table. The following example is the full SQL query:

SQL
SELECT
ID
, NAME
, SALESAMOUNT
FROM
SALES

When using an SQL query to import data, try to avoid using the wildcard character (*) in your query. If you use the wildcard character (*) in your SELECT statement, you import all columns that you don't need from the specified table.

The following example shows the query using the wildcard character.

SQL
SELECT *
FROM
SALES

The wildcard character (*) will import all columns within the Sales table. This method isn't recommended because it will lead to redundant data in your semantic model, which will cause performance issues and require extra steps to normalize your data for reporting.

All queries should also have a WHERE clause. This clause will filter the rows to pick only filtered records that you want. In this example, if you want to get recent sales data after January 1st, 2020, add a WHERE clause. The evolved query would look like the following example.

SQL
SELECT
ID
, NAME
, SALESAMOUNT
FROM
SALES
WHERE
OrderDate >= ‘1/1/2020’

It's a best practice to avoid doing this directly in Power BI. Instead, consider writing a query like this in a view. A view is an object in a relational database, similar to a table. Views have rows and columns, and can contain almost every operator in the SQL language. If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query. Query folding will be explained later, but in short, Power Query will optimize data retrieval according to how the data is being used later.



Comments

Post a Comment

Popular posts from this blog

Azure built-in roles for tables

Azure RBAC provides built-in roles for authorizing access to table data using Microsoft Entra ID and OAuth. Built-in roles that provide permissions to tables in Azure Storage include: Storage Table Data Contributor: Use to grant read/write/delete permissions to Table storage resources. Storage Table Data Reader: Use to grant read-only permissions to Table storage resources. To learn how to assign an Azure built-in role to a security principal, see Assign an Azure role for access to table data. To learn how to list Azure RBAC roles and their permissions, see List Azure role definitions. For more information about how built-in roles are defined for Azure Storage, see Understand role definitions. For information about creating Azure custom roles, see Azure custom roles. Only roles explicitly defined for data access permit a security principal to access table data. Built-in roles such as Owner, Contributor, and Storage Account Contributor permit a security principal to manage a storage acc...
Read more

Explore Dataflows Gen2 in Microsoft Fabric

  In Microsoft Fabric, you can create a Dataflow Gen2 in the Data Factory workload or Power BI workspace, or directly in the lakehouse. Since our scenario is focused on data ingestion, let's look at the  Data Factory  workload experience. Dataflows Gen2 use Power Query Online to visualize transformations.  1. Power Query ribbon Dataflows Gen2 support a wide variety of data source connectors. Common sources include cloud and on-premises relational databases, Excel or flat files, SharePoint, SalesForce, Spark, and Fabric lakehouses. Then there are numerous data transformations possible, such as: Filter and Sort rows Pivot and Unpivot Merge and Append queries Split and Conditional split Replace values and Remove duplicates Add, Rename, Reorder, or Delete columns Rank and Percentage calculator Choose Top N and Bottom N You can also create and manage data source connections, manage parameters, and configure the default data destination in this ribbon. 2. Queries pane The ...
Read more

Select and configure an appropriate method for access to Azure Blobs

Azure Storage supports using Microsoft Entra ID to authorize requests to blob data. With Microsoft Entra ID, you can use Azure role-based access control (Azure RBAC) to grant permissions to a security principal, which may be a user, group, or application service principal. The security principal is authenticated by Microsoft Entra ID to return an OAuth 2.0 token. The token can then be used to authorize a request against the Blob service. Authorization with Microsoft Entra ID is available for all general-purpose and Blob storage accounts in all public regions and national clouds. Only storage accounts created with the Azure Resource Manager deployment model support Microsoft Entra authorization. For optimal security, Microsoft recommends using Microsoft Entra ID with managed identities to authorize requests against blob, queue, and table data, whenever possible. Authorization with Microsoft Entra ID and managed identities provides superior security and ease of use over Shared Key author...
Read more