Task 4.2: Query data by using SQL

• AUse Open in Excel, which runs the selected T-SQL query in Excel

  Correct

  Open in Excel downloads a workbook that executes the query, so Excel gets the full result rather than the preview.

• BCopy the whole results grid and paste it into an Excel workbook

  Incorrect

  The grid preview holds only the first 10,000 rows, so the copy would be incomplete.

• CUse Save as table and then download the table

  Incorrect

  Save as table creates a table in the warehouse, which she must not do.

• DUse Save as view and open the view in the visual query editor

  Incorrect

  That creates a view, and the editor's preview would still show only part of the rows.

In the SQL query editor, the Results preview shows only the first 10,000 rows when a query returns more. Highlighting a SELECT statement gives actions such as Save as view, Save as table (a CREATE TABLE AS SELECT into a warehouse), Open in Excel and Visualize results. Open in Excel opens the corresponding T-SQL query in Excel and executes it, so the results can be worked with locally.

• ACreate #recent with a global ## name so later runs can see it

  Incorrect

  Temp tables in the warehouse are session-scoped; a separate run still gets a new session.

• BRun both statements together in one execution of the query window

  Correct

  Each Run is an independent batch with its own session, so the temp table exists only within the batch that created it.

• CWrap the first statement in BEGIN TRANSACTION so the session stays open

  Incorrect

  Transaction control across separate runs is limited, because every Run is its own batch and session.

• DGrant herself CREATE TABLE permission on the dbo schema

  Incorrect

  Permissions aren't the issue; the temp table was created, but in a session that ended.

In the SQL query editor, every time you run a query, it opens a separate session and closes it at the end of the execution, so session context isn't kept between independent runs. Each Run submits an independent batch request, and a session exists only per batch, which also limits transaction control statements across runs. Running the statements together keeps them in one session, where a session-scoped #temp table is visible.

• AUse Save as view anyway, because the untranslated step runs at query time in Power Query

  Incorrect

  A view is a SQL object, so steps that can't be translated to SQL can't be part of it.

• BRemove or replace the step that can't be translated to SQL, then use Save as view

  Correct

  Save as view needs the visual query to translate fully to SQL; steps that don't fold block it.

• CUse Save as table in the SQL analytics endpoint

  Incorrect

  The endpoint is read-only, and Save as table writes a copy of the results into a warehouse table with CREATE TABLE AS SELECT, which goes stale.

• DSwitch the query to a DDL statement in the visual editor

  Incorrect

  The visual query editor runs only read-only SELECT statements; DDL and DML aren't supported.

The visual query editor uses Power Query diagram view, but only a subset of Power Query operations that support query folding is supported, and some steps make the banner say the query isn't supported as a warehouse view because it can't be fully translated to SQL. Save as view creates a view from a query that translates to SQL, and Save as table loads results into a warehouse table with CREATE TABLE AS SELECT. The editor runs only read-only SELECT statements.

• AZero-copy clones of every table into a reporting schema, recreated each morning

  Incorrect

  Clones would have to be dropped and recreated per table, and analysts' queries would have to name the clone tables.

• BA Direct Lake semantic model with automatic updates turned off, refreshed at 06:00

  Incorrect

  That controls a semantic model's data, but analysts query the warehouse with T-SQL from SSMS.

• CA warehouse snapshot of SalesDW whose timestamp a job rolls forward to 06:00 each day

  Correct

  A snapshot is a read-only point-in-time view with its own connection, and rolling its timestamp forward is instant and atomic.

• DA second warehouse loaded by a pipeline at 06:00 each day

  Incorrect

  Loading another warehouse copies the data, which the requirement rules out.

A warehouse snapshot is a read-only representation of a warehouse at a point in time. Its timestamp can be rolled forward on demand, with updates applied instantly as one atomic change, so consumers keep a consistent version of all tables through the same connection while ETL keeps changing the parent warehouse. Snapshots inherit their permissions from the source warehouse and require Import or DirectQuery for Power BI, not Direct Lake.

• AXMLA read-write enabled on the capacity

  Incorrect

  Read-only is enough for query operations in SSMS; read-write is for metadata changes.

• BThe Contributor role in the Sales workspace

  Incorrect

  Contributor gives Write permission, far more than she needs; Build permission on the model is enough for queries.

• CBuild permission on the semantic model

  Correct

  Build permission is required for read access through the XMLA endpoint, regardless of semantic model roles.

• DA Power BI Premium Per User license

  Incorrect

  PPU licenses matter for PPU workspaces; this model is on an F64 capacity.

By default, the XMLA endpoint of a capacity's semantic models workload is read-only, which allows client tools such as SSMS to query semantic model data and metadata; read-write is needed for metadata operations. The tenant setting Allow XMLA endpoints and Analyze in Excel with on-premises semantic models must be on. Build permission on a semantic model is required for read access through the XMLA endpoint, regardless of the existence of model roles.

• AEVALUATE { [Total Sales] }

  Correct

  Curly braces turn the scalar value into a one-row table, which EVALUATE can return.

• BEVALUATE [Total Sales]

  Incorrect

  EVALUATE needs a table expression; a scalar has to be wrapped in a table constructor.

• CEVALUATE SUMMARIZECOLUMNS('Date'[Month Name], "Sales", [Total Sales]) ORDER BY 'Date'[Month Name]

  Incorrect

  Sort by column doesn't apply to DAX query results, so this sorts alphabetically.

• DEVALUATE TOPN(12, SUMMARIZECOLUMNS('Date'[Month Name], "Sales", [Total Sales])) ORDER BY 'Date'[Month Name]

  Incorrect

  TOPN doesn't use ORDER BY to pick rows, and Month Name still sorts alphabetically without its sort column.

• EEVALUATE SUMMARIZECOLUMNS('Date'[Month Number], 'Date'[Month Name], "Sales", [Total Sales]) ORDER BY 'Date'[Month Number]

  Correct

  Including Month Number in the query lets ORDER BY sort in calendar order.

A DAX query requires EVALUATE followed by a table expression. A measure or other scalar expression can be returned as a table by enclosing it in curly braces, a table constructor such as EVALUATE {[Total Sales]}. The Sort by column property doesn't apply to DAX query results, so the sort column must be included in the query to be used in ORDER BY. TOPN has its own ordering; ORDER BY only sorts the result.

• AAppend the workspace ID to the workspace name in the URL

  Correct

  Duplicate workspace names are resolved by adding the workspace ID to the name in the URL.

• BReplace myorg with the tenant's domain name

  Incorrect

  The tenant part identifies the tenant, not which of two workspaces with the same name to use.

• CSwitch the connection string to the v2.0 format with /home/myworkspace

  Incorrect

  That v2.0 form addresses personal workspaces, not one of two shared workspaces.

• DURL-encode the space in the workspace name as %20

  Incorrect

  Encoding special characters helps with names XMLA can't read, but both workspaces would still match.

The XMLA endpoint URL uses the format powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name], where myorg can stand for the tenant. When workspace names are duplicated, connect by appending the workspace ID to the name, for example Contoso Sales - followed by the GUID. The v2.0 format with home/myworkspace reaches personal workspaces, and special characters in workspace names can be URL-encoded.

• AVisualize results works only in the SQL query editor

  Incorrect

  Visualize results is available from the visual query editor too.

• BThe query must first be saved as a view

  Incorrect

  Saving as a view isn't required to visualize results.

• CVisualize results currently doesn't support queries with an ORDER BY clause

  Correct

  This is a listed limitation of the visual query editor.

• DORDER BY isn't valid T-SQL in Fabric Data Warehouse

  Incorrect

  ORDER BY is valid; only this feature doesn't support it.

The visual query editor provides a no-code way to build queries with Power Query diagram view. One of its listed limitations is that Visualize Results currently doesn't support SQL queries with an ORDER BY clause; ORDER BY itself is valid T-SQL.

• AVisual queries can't be saved at all

  Incorrect

  Visual queries are saved automatically every few seconds.

• BShared queries is available only for SQL queries, not visual queries

  Incorrect

  The restriction depends on the user's role, not the query type.

• COnly the warehouse owner can use Shared queries

  Incorrect

  Contributors and above can move queries there, not only the owner.

• DViewers and shared recipients can save queries only in My queries

  Correct

  Users in the Viewer role or shared recipients of the warehouse can't move queries to Shared queries.

As you build a visual query, it's saved automatically every few seconds, and all workspace users can save their queries in the My queries folder. Users in the Viewer role of the workspace or shared recipients of the warehouse are restricted from moving queries to the Shared queries folder.

• AINFO.VIEW functions, such as INFO.VIEW.MEASURES()

  Correct

  INFO.VIEW functions can run in calculated tables, columns, measures and DAX queries.

• BAny INFO function, such as INFO.MEASURES()

  Incorrect

  Other INFO functions can only be run in DAX queries, not in calculated tables.

• CDMVs queried through the XMLA endpoint

  Incorrect

  DMVs are queried by client tools, not used inside model calculations.

• DThe SELECTEDMEASURENAME function

  Incorrect

  It returns the measure in context of a calculation item, not a list of measures.

INFO DAX functions return information about a semantic model, like DMVs, and require semantic model admin permissions. INFO.VIEW functions, the friendlier counterparts of four INFO functions, can be run in calculated tables, columns, measures and DAX queries, so a calculated table can self-document a model. The other INFO functions can only be run in DAX queries.

• AAllow XMLA endpoints and Analyze in Excel with on-premises semantic models

  Incorrect

  That setting covers XMLA and Analyze in Excel connections, not the REST Execute Queries operation.

• BSemantic Model Execute Queries REST API

  Correct

  This setting lets users in the organization query semantic models with DAX through the Power BI REST APIs.

• CUsers can work with semantic models in Excel using a live connection

  Incorrect

  That setting covers exporting to Excel with a live connection.

• DAllow DirectQuery connections to Power BI semantic models

  Incorrect

  That setting controls DirectQuery to semantic models in composite models.

The admin portal's integration settings include Semantic Model Execute Queries REST API, which lets users in the organization query semantic models by using DAX through the Power BI REST APIs. XMLA endpoint connections and Analyze in Excel are governed by other settings.