How to: Add, View, Modify, or Delete a Named Query Using Data Source View Designer (Analysis Services)

In Microsoft SQL Server Analysis Services, you use Business Intelligence Development Studio to add, view, modify, or delete a named query in an Analysis Services project or database.

To add a named query

  1. In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you want to add a named query.

  2. In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:

    • Double-click the data source view.

    • Right-click the data source view and click Open.

    • Click the data source view and then on the View menu, click Open.

  3. Open the Create Named Query dialog box by doing one of the following:

    • In the Tables or Diagram pane, right-click an open area and then click New Named Query.

    • On the Data Source View Designer toolbar or on the Data Source View menu, click New Named Query.

  4. In the Create Named Query dialog box, do the following:

    1. In the Name text box, type a query name.

    2. Optionally, in the Description text box, type a description for the query.

    3. In the Data Source list box, select the data source against which the named query will execute.

    4. Type the query in the bottom pane, or use the graphical query building tools to create a query.

    Note

    The query-building user interface (UI) depends on the data source. Instead of getting a graphical UI, you can get a generic UI, which is text-based. You can accomplish the same things with these different UIs, but you must do so in different ways. For more information, see Create/Edit Named Query Dialog Box (Analysis Services - Multidimensional Data).

  5. Click OK.

    An icon showing two overlapping tables appears in the table header to indicate that the table has been replaced by a named query.

  6. Save the modified data source view by doing one of the following:

    • On the File menu, click Save Selected Items or Save All.

    • On the Data Source View Designer toolbar, click Save Selected Items or Save All.

    • Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.

To view or modify a named query

  1. In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you want to view or modify a named query.

  2. In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:

    • Double-click the data source view.

    • Right-click the data source view and click Open.

    • Click the data source view and then on the View menu, click Open.

  3. Open the Edit Named Query dialog box by doing one of the following:

    • In either Tables or Diagram pane, right-click the named query and then click Edit Named Query.

    • In either Tables or Diagram pane, click the named query and then click Edit Named Query on the Data Source View menu.

    • In either Tables or Diagram pane, double-click the named query.

  4. In the Create Named Query dialog box, do the following:

    1. In the Name text box, view or modify the query name.

    2. In Description text box, view or modify the description for the query.

    3. In the Data Source list box, view or modify the data source against which the named query will execute.

    4. In the bottom pane, view or modify the query. Use the graphical query building tools as appropriate. For more information, see Create/Edit Named Query Dialog Box (Analysis Services - Multidimensional Data).

    Note

    The query building user interface (UI) depends on the data source. Instead of getting a graphical UI, you can get a generic UI, which is text based. You can accomplish the same things with these different UIs, but you must do so in different ways.

  5. Click OK or Cancel as appropriate.

  6. Save the modified data source view by doing one of the following:

    • On the File menu, click Save Selected Items or Save All.

    • On the Data Source View Designer toolbar, click Save Selected Items or Save All.

    1. Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.

To delete a named query

  1. In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you want to delete a named query.

  2. In Solution Explorer, expand the Data Source Views folder, open the data source view in Data Source View Designer by doing one of the following:

    • Double-click the data source view.

    • Right-click the data source view and click Open.

    • Click the data source view and then on the View menu, click Open.

  3. Open the Delete Objects dialog box by doing one of the following:

    • In either the Tables or the Diagram pane, right-click the named query that you want to delete , and then click Delete Table From DSV

    • In either the Tables or the Diagram pane, click the named query that you want to delete, and then click Delete on the Data Source View Designer toolbar.

  4. In the Delete Objects dialog box, review the objects that will be deleted and the objects that will no longer be valid, then click OK or Cancel as appropriate.

  5. Save the modified data source view by doing one of the following:

    • On the File menu, click Save Selected Items or Save All.

    • On the Data Source View Designer toolbar, click Save Selected Items or Save All.

    • Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.