SQL Server connection in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding SQL Server Connection Strings in Power BI

Which of the following connection strings correctly connects Power BI to a SQL Server instance named SQLPROD01 on the default port, using Windows Authentication?

A Data Source=SQLPROD01;Initial Catalog=SalesDB;Integrated Security=False;

B Server=SQLPROD01,1433;Database=SalesDB;User Id=admin;Password=pass123;

C Server=SQLPROD01;Database=SalesDB;Integrated Security=False;User Id=admin;Password=pass123;

D Server=SQLPROD01;Database=SalesDB;Trusted_Connection=True;

💡 Hint

Windows Authentication uses Trusted_Connection=True or Integrated Security=True.

Option D uses Trusted_Connection=True, which enables Windows Authentication. It specifies the server and database correctly without specifying a port, which defaults to 1433.

❓ dax_lod_result

intermediate

DAX Measure for SQL Server Data Refresh Status

You have a table RefreshLog with columns RefreshDate and Status. You want a DAX measure that returns the count of successful refreshes in the last 7 days.

Which measure produces the correct result?

A SuccessfulRefreshes = COUNTROWS(FILTER(RefreshLog, RefreshLog[Status] = "Success" && RefreshLog[RefreshDate] <= TODAY() - 7))

B SuccessfulRefreshes = CALCULATE(COUNTROWS(RefreshLog), RefreshLog[Status] = "Success" && RefreshLog[RefreshDate] <= TODAY() + 7)

C SuccessfulRefreshes = CALCULATE(COUNTROWS(RefreshLog), FILTER(RefreshLog, RefreshLog[Status] = "Success" && RefreshLog[RefreshDate] >= TODAY() - 7))

D SuccessfulRefreshes = CALCULATE(COUNTROWS(RefreshLog), RefreshLog[Status] = "Success", RefreshLog[RefreshDate] >= TODAY() - 7)

💡 Hint

Use FILTER inside CALCULATE to apply multiple conditions correctly.

Option C correctly filters rows where Status is "Success" and RefreshDate is within the last 7 days (greater or equal to TODAY() - 7).

❓ visualization

advanced

Best Visualization for SQL Server Query Performance Over Time

You have imported SQL Server query performance data with columns QueryName, ExecutionTime (in milliseconds), and ExecutionDate. You want to visualize trends of execution time for multiple queries over the last month.

Which visualization type is best suited for this purpose in Power BI?

A Table showing <code>QueryName</code> and average <code>ExecutionTime</code> for the last month

B Line Chart with <code>ExecutionDate</code> on X-axis and <code>ExecutionTime</code> on Y-axis, with separate lines for each <code>QueryName</code>

C Pie Chart showing percentage of total execution time by query

D Stacked Bar Chart showing total execution time per query for the last month

💡 Hint

To show trends over time for multiple categories, use a line chart with series.

Option B allows you to see how execution time changes over dates for each query separately, making trends easy to spot.

🔧 Formula Fix

advanced

Troubleshooting SQL Server Connection Timeout in Power BI

You try to connect Power BI to a remote SQL Server but get a timeout error. Which of the following is the most likely cause?

A The SQL Server instance is not configured to allow remote connections.

B The Power BI desktop version is outdated and incompatible with SQL Server.

C The SQL Server database is empty with no tables.

D The Power BI report has too many visuals causing slow rendering.

💡 Hint

Timeout errors usually relate to network or server settings.

If SQL Server does not allow remote connections, Power BI cannot connect and will timeout. Other options do not cause connection timeout.

❓ data_modeling

expert

Optimizing Data Model for SQL Server DirectQuery in Power BI

You are using DirectQuery mode to connect Power BI to a large SQL Server database. To improve report performance, which modeling practice is best?

A Use star schema with fact and dimension tables, minimize calculated columns, and push calculations to SQL Server views.

B Import all tables fully and create many calculated columns in Power BI.

C Create many-to-many relationships between all tables to increase flexibility.

D Disable query folding to ensure all transformations happen in Power BI.

💡 Hint

Good data modeling and pushing calculations to the source improves DirectQuery performance.

Option A follows best practices: star schema simplifies queries, minimizing calculated columns reduces load, and pushing calculations to SQL Server leverages its power.