TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

Create Processing Query Dialog Box (Analysis Services - Multidimensional Data)

 

Applies To: SQL Server 2016

Use the Create Processing Query dialog box in SQL Server Data Tools (SSDT) to create a processing query in the Notifications tab of the Storage Options dialog box. A processing query is query that returns a rowset containing the changes made to a table associated with an Analysis Services object since the last time the table was polled, in order to incrementally update the multidimensional OLAP (MOLAP) cache for the object. Analysis Services uses another query, referred to as a polling query, to poll a table associated with an object and determine whether the table has changed. Processing queries are not necessary when fully updating the MOLAP cache for the object.

Typically, the processing query is parameterized, and two parameters are currently supported:

  • The singleton value returned by the polling query during the previous scheduled polling.

  • The singleton value returned by the polling query during the current scheduled polling.

For example, the queries listed in the following table could be used to incrementally update the Customer dimension in the Adventure Works DW sample Analysis Services project.

Query typeQuery statement
Polling querySELECT

 MAX([CustomerKey]) AS LastCustomerKey

 FROM

 [dbo].[DimCustomer]
Processing querySELECT

 *

 FROM

 [dbo].[DimCustomer]

 WHERE

 (CustomerKey > COALESCE (@Param1, - 1))

 AND (CustomerKey <= @Param2)

For more information about incremental updates for scheduled polling notifications, see Proactive Caching (Partitions).

You can display the Create Processing Query dialog box by clicking ... on the Processing Query column of the grid for the Scheduled polling option on the Notifications tab of the Storage Options dialog box. For more information about the Notifications tab of the Storage Options dialog box, see Notifications (Storage Options Dialog Box) (Analysis Services - Multidimensional Data).

The query entered must be a valid query command for the underlying provider. The query is prepared with the underlying provider for validation, and to identify the columns returned. The dialog box can present two views:

  • Visual Database Tools (VDT) Query Builder

    For all users, the VDT Query Builder view provides a set of user interface tools for visually constructing and testing a SQL query.

  • Generic Query Builder

    For advanced users, the Generic Query Builder view provides a simpler, more direct user interface for constructing and testing a SQL query.

Data Source
Specifies the data source for the query.

Query definition
The query definition provides a toolbar and panes in which to define and test the query, depending on the selected view.

Toolbar
Use the toolbar to manage datasets, select panes to display, and control various query functions.

ValueDescription
Switch to Generic Query BuilderSelect to display only the options available to the Generic Query Builder view. Only the following options are displayed:

 SQL pane

 Result pane

 Toolbar, containing only Switch to VDT Query Builder and Run

 

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Switch to VDT Query BuilderSelect to display all of the options available to the Visual Database Tools (VDT) Query Builder view.

Note: This option is displayed only if Switch to Generic Query Builder is selected.
Show/Hide Diagram PaneShows or hides the Diagram pane.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Show/Hide Grid PaneShows or hides the Grid pane.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Show/Hide SQL PaneShows or hides the SQL pane.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Show/Hide Result PaneShows or hides the Result pane.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
RunRuns the query. Results are displayed in the Result pane.
Verify SQLVerifies the SQL statement in the query.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Sort AscendingSorts output rows on the selected column in the Grid pane, in ascending order.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Sort DescendingSorts output rows on the selected column in the Grid pane, in descending order.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Remove FilterRemoves sort criteria, if applicable, for the selected row in the Grid pane.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Use Group ByAdds grouping functionality to the query.

Note: This option is displayed only if Switch to VDT Query Builder is selected.
Add TableDisplays the Add Table dialog box to add a new table or view to the query. For more information about the Add Table dialog box, see Add Table Dialog Box (Analysis Services - Multidimensional Data).

Note: This option is displayed only if Switch to VDT Query Builder is selected.

Diagram pane
Displays the objects referenced by the query as a diagram. The diagram shows the tables included in the query, and how they are joined. Select or clear the check box next to a column in a table to add or remove it from the query output.

When you add tables to the query, the dialog box creates joins between tables based on the keys in the table. To add a join, drag a field from one table onto a field in another table. To manage a join, right-click the join.

Right-click the Diagram pane to add or remove tables, select all the tables, and show or hide panes.

System_CAPS_ICON_note.jpg Note


The contents of the Diagram pane, Grid pane, and SQL pane are synchronized, so that changes in one pane are reflected in the other two panes.

System_CAPS_ICON_important.jpg Important


Changing query types is not supported by the dialog box.

Grid pane
Displays the objects referenced by the query in a grid. You can use this pane to add and remove columns to the query and change the settings for each column.

System_CAPS_ICON_note.jpg Note


The contents of the Diagram pane, Grid pane, and SQL pane are synchronized, so that changes in one pane are reflected in the other two panes.

SQL pane
Displays the query as a SQL statement. Type to change the SQL statement for the query.

System_CAPS_ICON_note.jpg Note


The contents of the Diagram pane, Grid pane, and SQL pane are synchronized, so that changes in one pane are reflected in the other two panes.

Result pane
Displays the results of the query when you click Run on the Toolbar pane.

Analysis Services Designers and Dialog Boxes (Multidimensional Data)

Community Additions

ADD
Show:
© 2016 Microsoft