How to: Retrieve Data from an Analysis Services Cube (MDX)
To use data from an Analysis Services cube in your report, you must define an Analysis Services data source and create one or more report datasets. When you define the data source definition, you must specify a connection string and credentials so that you can access the data source from your client computer.
You can create an embedded data source definition for use by a single report or a shared data source definition that can be used by multiple reports. The procedures in this topic describe how to create an embedded data source. For more information about shared data sources, see Shared Data Sources and Embedded Data Sources and Creating, Modifying, and Deleting Shared Data Sources.
After you create an Analysis Services data source, you can create one or more datasets. Use the Multidimensional Expression (MDX) query designer to create MDX queries. For more information, see Analysis Services MDX Query Designer User Interface.
After you create a dataset, the name of the dataset appears in the Report Data pane as a node under its data source. For more information, see Working with Report Designer in Business Intelligence Development Studio.
After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.
To create an embedded Microsoft SQL Server Analysis Services data source
On the toolbar in the Report Data pane, click New, and then click Data Source.
In the Data Source Properties dialog box, type a name in the Name text box, or accept the default name.
Verify that Embedded connection is selected.
From the Type drop-down list, select Microsoft Sql Server Analysis Services.
Specify a connection string that works with your Analysis Services data source.
Contact your database administrator for connection information and for the credentials to use to connect to the data source. The following connection string example specifies the AdventureWorksDW database on the local client.
Data Source=localhost;Initial Catalog=AdventureWorksDWClick Credentials.
Set the credentials to use to connect to the data source. For more information, see Specifying Credential and Connection Information for Report Data Sources.
Note
To test the data source connection, click Edit next to the connection string text box. In the Connection Properties dialog box, click Test Connection. If the test is successful, you will see the information message "Test connection succeeded." If the test fails, you will see a warning message with more information about why the test was not successful.
Click OK.
The data source appears in the Report Data pane.
To create a dataset for a Microsoft SQL Server Analysis Services data source
In the Report Data pane, right-click the name of the data source that connects to an Analysis Services data source, and then click Add Dataset. The Query page of the Dataset Properties dialog box opens.
In the Name text box, type a name or accept the default name.
In the Data source text box, verify that the selected data source appears.
In Query type, verify that Text is selected. Click Query Designer to open the graphical query designer to build a query interactively. For more information, see Analysis Services MDX Query Designer User Interface.
Alternatively, you can paste a query from the clipboard directly into the Query pane, or import an existing MDX query from another report. To import an existing query, click Import, and then navigate to the .rdl file with the MDX query. Importing a query from an .mdx file is not supported.
After you create and run your query to see sample results, click OK. Click OK.
Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.