SQL Server Video
12 page views
9014 video views
2024, August 10, Saturday

#036 How to visualize SQL code in Power BI with Native query

Apresentamos como identificar o códido SQL utilizando a consulta nativa do Microsoft Power BI e a ferramenta SQL Server Profiler.

We'll get to know the techniques:

1. Get data from a SQL Server source (GET DATA):

To get data from a SQL Server source in Power BI, you can follow these steps:
1. In Power BI Desktop, go to the "Home" tab.
2. Select "Get Data" and choose "SQL Server" from the list of data sources.
3. Enter the connection information, such as server name, database, and credentials.
4. Use the graphical interface to select tables or write custom queries.

2. Understand DATA CONNECTIVITY MODE, STORAGE MODE:

Connectivity and storage mode can be configured in Power BI to optimize performance and interactivity. There are modes like "Import" (importing data into Power BI) and "DirectQuery" (running queries directly against the database).

3. Native Query (QUERY FOLDING):

Query Folding refers to Power BI's ability to translate and send some of the query logic to the underlying database for execution, improving performance. This is commonly used in relational data sources such as SQL Server.
Example:
```
let
    Source = Sql.Database("YourServer", "YourBank"),
    SourceQuery = Source{[Schema="dbo",Item="YourTable"]}[Date]
In fashion
    SourceQuery
```

4. Identify queries by using

SQL Server Profiler (QUERY):

SQL Server Profiler can be used to monitor the queries that Power BI executes in SQL Server. This helps you understand the performance and efficiency of queries generated by Power BI.

5. Increase report performance by disabling filters on visual interactions

Power BI (QUERY REDUCTION):Disabling filters

on visual interactions can be done to optimize report performance. This prevents unnecessary queries from being sent to the database, reducing the load.
Example:
```
 DisableRelationshipFilter is used to disable related filters on a specific table
Evaluate
DisableRelationshipFilter('Table1')
```

These are basic concepts and examples that may vary depending on the complexity of your environment and the specific requirements of the Power BI report.