Skip to main content

Data Connections

Creating an Excel Connection​

By connecting a new Excel workbook directly to the Analysis Server, you can create custom reports, even if the standard exported report from the Analysis Server Console is unavailable.

  1. Create a blank Excel workbook.

  2. In the Data tab, go to Get Data > From Database, then select From Analysis Services.

    Getting Data from Analysis Services

    Getting Data from Analysis Services

  3. In the Data Connection Wizard, specify the Server name and provide your Login credentials. You can choose to use either Windows Authentication or manually enter a username and password. Once done, click Next.

    Specifying the Server and Login Credentials

    Specifying the Server and Login Credentials

  4. Choose the database and cube to use. Click Next.

    Specifying the Database and Cube

    Specifying the Database and Cube

    Each database has different cubes, like Applications, License Monitor, and Storage. These cubes may not always be present in all databases. Most of the time, the Applications cube is used.

  5. Enter the desired File Name to save the data connection file. Optionally, you can add a Description and a Friendly Name to the file details. Then, click Finish.

    Saving the Data Connection File

    Saving the Data Connection File

  6. Specify the desired data settings in the Import Data prompt. Click OK.

    Import Data

    Import Data

    A PivotTable will be ready for report creation.

    Empty PivotTable

    Empty PivotTable

Connecting to an Existing Data Connection​

  1. In the Data tab, select Existing Connections.

    Existing Connections

    Existing Connections

  2. In the Existing Connections prompt, select the Open iT connection from the list, then click Open.

    Selecting an Existing Connection

    Selecting an Existing Connection

  3. In the Import Data prompt, choose PivotTable Report and New worksheet, then click OK.

    Importing Data to a New Worksheet

    Importing Data to a New Worksheet

    note

    Insert the data on a new worksheet to avoid overlapping data from conflicting with the initial PivotTable.

    Creating a report using a connection to an existing database is entirely independent of the other PivotTable(s) in the Excel file.

Modifying the Data Connection​

  1. In the Data tab, select Queries and Connections.

    Queries and Connections

    Queries and Connections

  2. From the Connections tab of the Queries and Connections side menu, right-click the Open iT data connection, then select Properties.

    Connections

    Connections

  3. Modify the connection properties as needed, then click OK.

    Connection Properties

    Connection Properties