Create Queries for SQL Server Analysis Services
To use SQL Server Analysis Services (SSAS) cube data in the Query Builder, you can drag a cube to the Query Builder or you can create a Query first and send that to the Query Builder. The Database Explorer is used to create and save Queries from SSAS cube data. First, you must build a multi-dimensional grid to view the selected data from a cube. Then, you can save the multi-dimensional view as a Query.
Saved Queries appear in the Object Explorer when you are connected to the original SSAS data source. You can then use the saved Queries in the Query Builder to query against, to join with other Queries, or to join with data from other data sources.
To view cube data (build multi-dimensional grid)
- Right-click an SSAS Cube in the Object Explorer and select View Details. Then select the Data tab.
- In the Data tab, Dimensions and Measures display in the left pane and a pivot grid displays in the right pane. To display data, you must build a multi-dimensional grid/view.
- To build a grid/view, drag Dimensions and Measures from the left pane to the grid in the right pane.
- Drag Dimensions to the grid areas marked Drop Row Fields Here.
- Drag Measures to the grid areas marked Drop Data Items Here.
-
To get data, click Refresh in the wizard bar.
Notes:
- To display data automatically when adding Dimensions and Measures, select the Refresh browser immediately option.
- To remove Dimensions and Measures from the grid/view, drag them back to the treelist.
To create and save a Query
- After building the multi-dimensional view for your selected data, click Save Query in the wizard bar.
-
In the Create Saved Query dialog, enter a name and a comment (optional), and then click Execute. The saved Query is added to the Queries node in the Object Explorer.
Note: The Query name must be unique. A Query cannot have the same name as a Cube or another Query.
To send to the Query Builder
To send a saved Query to the Query Builder
- Right-click a Query in the Object Explorer and select Query Builder, or drag the Query to the Query Builder diagram pane.
Note: If you use the Editor to write a SQL statement, each SSAS Query must have an alias.
Notes:
- To view the data of a saved Query, right-click the Query in the Object Explorer and select View Details, then select the Data tab.
- To view the SQL of a saved Query, right-click the Query and select View Details, then select the Query Info tab.
- To expand or collapse the Dimensions and Measures treelist, right-click the treelist pane and select Expand All /Collapse All.
- To delete a saved Query, right-click the Query and select Operations | Drop Saved Query in the Object Explorer.
- The state of your multi-dimensional grid/view for a Cube is preserved if you move away by selecting another Cube or a Query. The state is preserved for the duration of your current Toad session. Click Refresh to display data after returning to grid/view.
- SSAS Queries are similar to views. The definition of each Saved Query is stored locally in a hidden table in Local Storage.
- See Considerations and Limitations with Analysis Services Connections for more information.
Related Topics
About SQL Server Analysis Services Data Sources
