You need to write a T-SQL query in OpsDW that returns the exception list for the finance team.
Which two queries meet the requirement? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
a) SELECT s.ShipmentID FROM dbo.Shipment AS s WHERE s.Status = 'Delivered' AND s.ShipmentID NOT IN (SELECT p.ShipmentID FROM dbo.ProofOfDelivery AS p);
b) SELECT s.ShipmentID FROM dbo.Shipment AS s WHERE s.Status = 'Delivered' AND NOT EXISTS (SELECT 1 FROM dbo.ProofOfDelivery AS p WHERE p.ShipmentID = s.ShipmentID);
c) SELECT s.ShipmentID FROM dbo.Shipment AS s INNER JOIN dbo.ProofOfDelivery AS p ON p.ShipmentID = s.ShipmentID WHERE s.Status = 'Delivered' AND p.ShipmentID IS NULL;
d) SELECT s.ShipmentID FROM dbo.Shipment AS s LEFT JOIN dbo.ProofOfDelivery AS p ON p.ShipmentID = s.ShipmentID AND p.ShipmentID IS NULL WHERE s.Status = 'Delivered';
e) SELECT s.ShipmentID FROM dbo.Shipment AS s LEFT JOIN dbo.ProofOfDelivery AS p ON p.ShipmentID = s.ShipmentID WHERE s.Status = 'Delivered' AND p.ShipmentID IS NULL;
02. A business unit wants to build reports on a shared semantic model that another team publishes, without duplicating the data in their own workspace.
Which connection type should they use?
a) A live connection to the semantic model
b) Import
c) A copy activity into a lakehouse in their workspace
d) A Dataflow Gen2 that writes the data to a warehouse in their workspace
You need to create the Premier Balance measure in DepositsModel.
Which DAX expression should you use?
a) CALCULATE([Total Balance], Account[Segment] = "Premier")
b) CALCULATE([Total Balance], FILTER(ALL(Account), Account[Segment] = "Premier"))
c) CALCULATE([Total Balance], KEEPFILTERS(Account[Segment] = "Premier"))
d) IF(SELECTEDVALUE(Account[Segment]) = "Premier", [Total Balance])
04. An enterprise-scale semantic model experiences slow query execution during peak business hours. Its large fact table is fully refreshed every day, and the refresh now runs into business hours, competing with report queries for capacity. The team needs to implement architectural optimizations.
Which three strategies should the team implement?
a) Implement aggregation tables
b) Reduce column cardinality
c) Increase number of visuals per report page
d) Convert the model's measures to calculated columns
e) Use incremental refresh
You need to create the Unique Skiers measure in ResortModel. The solution must meet the semantic model requirements.
Which DAX expression should you use?
a) COUNTROWS(Guest)
b) COUNT(LiftScan[GuestID])
c) DISTINCTCOUNT(LiftScan[ScanID])
d) DISTINCTCOUNT(LiftScan[GuestID])
06. You are using DAX Studio to connect to and troubleshoot a Microsoft Power BI report that is performing poorly. You need to capture the queries generated by the report.
Which feature in DAX Studio should you enable?
a) Query Builder
b) All Queries trace
c) Query Plan tracing
d) Server Timing trace
You need to create the Sales PY measure in CoffeeModel. The solution must meet the semantic model requirements and the general requirements.
Which DAX expression should you use?
a) CALCULATE([Sales], PREVIOUSYEAR('Date'[Date]))
b) CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH))
c) CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
d) CALCULATE([Sales], DATEADD('Date'[Date], -1, MONTH))
08. You are designing a Power BI semantic model that contains a measure named CompanyCosts.
You need to restrict access to CompanyCosts so that only a user named User1 can view the measure in reports. What should you implement?
a) object-level security (OLS)
b) data masking
c) dynamic row-level security (RLS)
d) static row-level security (RLS)
You are creating the predicate function for the security policy on dbo.FactInvoice. The policy will pass the Region value of each row to the function. The function begins with the following statement.
CREATE FUNCTION sec.fn_RegionFilter(@Region AS varchar(20)) RETURNS TABLE WITH SCHEMABINDING AS
You need to complete the function. The solution must meet the security requirements.
Which statement should you use?
a) RETURN SELECT 1 AS Allowed FROM dbo.RegionManager AS m WHERE m.ManagerUPN = USER_NAME();
b) RETURN SELECT 1 AS Allowed FROM dbo.RegionManager AS m WHERE m.Region = @Region OR m.ManagerUPN = USER_NAME();
c) RETURN SELECT 1 AS Allowed WHERE @Region = USER_NAME();
d) RETURN SELECT 1 AS Allowed FROM dbo.RegionManager AS m WHERE m.Region = @Region AND m.ManagerUPN = USER_NAME();
10. You are developing a complex semantic model that contains more than 20 date columns. You need to conform the date format for all the columns as quickly as possible.
What should you use?
a) ALM Toolkit
b) Tabular Editor
c) DAX Studio
d) VertiPaq Analyzer