Toad Data Point 6.2.3

Chat now with support

Create Amazon Redshift Connections Create ODBC Connections Create Oracle Connections Create Snowflake Connections Create SQL Server Connections Create SharePoint Connections Understand Toad Connections Troubleshoot SQL Server Connections

Understand

Determine the Best Method to Locate Objects Object Explorer Filter Databases and Objects Understand a Table Using the Database Explorer Search for Objects Create an Database Diagram to Use as a Query Template Browse Data in Related Tables

Query

Tutorial: Build a Query Visually Create Cross-Connection Queries Reverse Engineer Queries Build a Subquery Edit SQL Editor Shortcut Keys View Result Sets Compare Differences in Data Export Data with One Click Export Share Excel Linked Queries

Report

Tutorial: Create a Toad Data Report Manage Reports

About Managing Reports DB2 Reports MySQL Reports ODBC Reports Oracle Reports SQL Server Reports

Publish and Share Reports

Automate

Getting Started with Automation Tutorial: Automate Excel Reports Automate Tasks Use Database Automation Activities Use File Automation Activities Use System Automation Activities Use Variables In Automation Execute/Schedule Automation Scripts

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.
1. Drag Dimensions to the grid areas marked Drop Row Fields Here.
2. 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

After building the multi-dimensional view, click Send to Query Builder in the Wizard bar to send the query 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.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Toad Data Point 6.2.3 - User Guide

Create Queries for SQL Server Analysis Services