You have a semantic model named Model1. Model1 contains five tables that all use Import mode. Model1 contains a dynamic row-level security (RLS) role named HR. The HR role filters employee data so that HR managers only see the data of the department to which they are assigned.

You publish Model1 to a Fabric tenant and configure RLS role membership. You share the model and related reports to users.

An HR manager reports that the data they see in a report is incomplete.

What should you do to validate the data seen by the HR Manager?

Reveal Solution Hide Solution

You have a semantic model named Model1. Model1 contains five tables that all use Import mode. Model1 contains a dynamic row-level security (RLS) role named HR. The HR role filters employee data so that HR managers only see the data of the department to which they are assigned.

You publish Model1 to a Fabric tenant and configure RLS role membership. You share the model and related reports to users.

An HR manager reports that the data they see in a report is incomplete.

What should you do to validate the data seen by the HR Manager?

Reveal Solution Hide Solution

You have a semantic model named Model1. Model1 contains five tables that all use Import mode. Model1 contains a dynamic row-level security (RLS) role named HR. The HR role filters employee data so that HR managers only see the data of the department to which they are assigned.

You publish Model1 to a Fabric tenant and configure RLS role membership. You share the model and related reports to users.

An HR manager reports that the data they see in a report is incomplete.

What should you do to validate the data seen by the HR Manager?

Reveal Solution Hide Solution

You plan to use Fabric to store data.

You need to create a data store that supports the following:

– Writing data by using T-SQL

– Multi-table transactions

– Dynamic data masking

Which type of data store should you create?

Reveal Solution Hide Solution

HOTSPOT

You have a Fabric tenant that contains a warehouse named WH1.

You run the following T-SQL query against WH1.

For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point. Hot Area:

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: Yes

Yes – Dimension.GetDirectReports is a scalar T-SQL function.

OUTER APPLY

The OUTER APPLY operator returns all the rows from the left table expression irrespective of whether it matches the expression from the right table. For rows with no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression. The OUTER APPLY is equivalent to a LEFT OUTER JOIN.

If you can achieve the same results with a regular JOIN clause, why and when do you use the APPLY operator? Although you can achieve the same with a regular JOIN, the need for APPLY arises if you have a table-valued expression on the right part.

SQL Server OUTER APPLY vs LEFT OUTER JOIN

Let’s take a minute and look at another example. The first query in the code block below, selects data from the Department table. It uses an OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is no match in the Employee table, SQL returns NULL, as you can see in the screenshots below.

The second query uses a LEFT OUTER JOIN between the Department and Employee tables. As expected, the query returns all rows from the Department table, even for those rows for which there is no match in the Employee table.

— Query #1

SELECT *

FROM Department D OUTER APPLY

(SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;

GO

— Query #2

SELECT *

FROM Department D

LEFT OUTER JOIN Employee E

ON D.DepartmentID = E.DepartmentID;

Box 2: No

It runs for each record/row.

Box 3: Yes

Reference: https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply

DRAG DROP

You have a Fabric tenant that contains a data warehouse named DW1. DW1 contains a table named DimCustomer. DimCustomer contains the fields shown in the following table.

You need to identify duplicate email addresses in DimCustomer. The solution must return a maximum of 1,000 records.

Which four T-SQL statements should you run in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: SELECT TOP(1000) CustomerAltKey, Count(*) Use TOP(1000) to return maximum 1000 records. Step 2: FROM DimCustomer SQL HAVING Example:

The following SQL statement lists the number of customers in each country.

Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5;

Step 3: GROUP BY CustomerAltKey

Step 4: HAVING COUNT(*) > 1

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

Reference: https://www.w3schools.com/SQL/sql_having.asp

DRAG DROP

You have a Fabric tenant that contains a data warehouse named DW1. DW1 contains a table named DimCustomer. DimCustomer contains the fields shown in the following table.

You need to identify duplicate email addresses in DimCustomer. The solution must return a maximum of 1,000 records.

Which four T-SQL statements should you run in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: SELECT TOP(1000) CustomerAltKey, Count(*) Use TOP(1000) to return maximum 1000 records. Step 2: FROM DimCustomer SQL HAVING Example:

The following SQL statement lists the number of customers in each country.

Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5;

Step 3: GROUP BY CustomerAltKey

Step 4: HAVING COUNT(*) > 1

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

Reference: https://www.w3schools.com/SQL/sql_having.asp

DRAG DROP

You have a Fabric tenant that contains a data warehouse named DW1. DW1 contains a table named DimCustomer. DimCustomer contains the fields shown in the following table.

You need to identify duplicate email addresses in DimCustomer. The solution must return a maximum of 1,000 records.

Which four T-SQL statements should you run in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: SELECT TOP(1000) CustomerAltKey, Count(*) Use TOP(1000) to return maximum 1000 records. Step 2: FROM DimCustomer SQL HAVING Example:

The following SQL statement lists the number of customers in each country.

Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5;

Step 3: GROUP BY CustomerAltKey

Step 4: HAVING COUNT(*) > 1

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

Reference: https://www.w3schools.com/SQL/sql_having.asp

You have a Fabric tenant that contains a warehouse.

A user discovers that a report that usually takes two minutes to render has been running for 45 minutes and has still not rendered.

You need to identify what is preventing the report query from completing.

Which dynamic management view (DMV) should you use?

Reveal Solution Hide Solution

You have a Fabric tenant that contains a warehouse.

A user discovers that a report that usually takes two minutes to render has been running for 45 minutes and has still not rendered.

You need to identify what is preventing the report query from completing.

Which dynamic management view (DMV) should you use?

Reveal Solution Hide Solution