Spotfire® User Guide

Accessing data from Microsoft SQL Server Analysis Services

Follow the steps below to create a new connection to Microsoft SQL Server Analysis Services, and add the data either as imported or in-database data.

About this task

Tip: To learn more about creating data connections and working with data using the connector for SQL Server Analysis Services, see:

Create a connection and add external (in-database) data

Follow the steps below to add a connection with in-database data to Microsoft SQL Server Analysis Services.

Procedure

  1. Click Files and data .
  2. In the Files and data flyout, click Connect to.
  3. In the list of connectors, click Microsoft SQL Server Analysis Services.
  4. In the Microsoft SQL Server Analysis Services flyout, click New connection.
  5. In the Microsoft SQL Server Analysis Services Connection dialog, specify the Server you want to connect to.
  6. Select the Authentication method you want to use, and if you selected Username and password, enter your credentials.
  7. Click Connect.
  8. Select the Database and Cube of interest.
  9. Click OK.
    The Data Selection in Connection dialog is opened.
  10. Click to select data of interest in the panel to the left.
    Click on the top level to select the entire cube.
    Limit what is shown in the panel by selecting a single measure group, or by typing a search term in the search field.
    Double-click on a collapsed item to expand it, or double-click on a leaf node to send it to the Data selection in connection list.
  11. Click Add > to move the selected data to the Data selection in connection field.
  12. Dimension columns and hierarchies can be further limited by clicking the selected column or hierarchy in the Data selection in connection list and then, in the Settings pane, clicking Limit.
    This way, you can filter out certain values of the dimension to only include values of interest. For example, you can select data from a single country only. Note that it is not possible to limit measures, only dimensions, because the cube calculates the measures in the context of the selected dimensions.
  13. If desired, you can specify that the data in this connection should be available as external data only. Right-click on the cube name and select Load Method and the desired option.
  14. The final step, the Add data to analysis flyout, is a summary view of the data tables that will be added to your Spotfire analysis. Make sure that the data table that is added from your connection is configured as External, for in-database analysis.
    Tip: To change the load method of a data table to External, click the data table in the summary view, and then use the drop-down menus to select External.
  15. Click OK.

Create a connection and import data

Follow the steps below to import data from a Microsoft SQL Server Analysis Services cube.

Procedure

  1. Click Files and data .
  2. In the Files and data flyout, click Connect to.
  3. In the list of connectors, click Microsoft SQL Server Analysis Services.
  4. In the Microsoft SQL Server Analysis Services flyout, click New connection.
  5. In the Microsoft SQL Server Analysis Services Connection dialog, specify the Server you want to connect to.
  6. Select the Authentication method you want to use, and if you selected Username and password, enter your credentials.
  7. Click Connect.
    Spotfire connects to the specified server, and the cubes that are available on that server are listed in the Cube drop-down list.
  8. Select the Database of interest.
  9. Select the Cube of interest.
  10. Click OK.
    The Data Selection in Connection dialog opens.
  11. If you want to limit your import to data from a single measure group, select the measure group of interest in the drop-down list.
    You can also type any text in the search field to find objects containing that text only.
  12. Click on the object of interest in the left pane, then click Add >.
    See Icon Descriptions for information about what the different objects represent. If you select the cube icon, you will get all data in the cube. When it comes to hierarchies, you always have to add the entire hierarchy to the list, but you can use the Limit Data button to filter out higher levels of detail in the hierarchy from the actual selection at a later stage. You can repeat this step as many times as you want to add more data to the selection.
    The selected data is added to the Data selection in connection list.
  13. Dimension columns and hierarchies can be further limited by clicking on the selected column or hierarchy in the Data selection in connection list and then, in the Settings pane, clicking Limit.
    The Limit Data dialog opens.
  14. Clear the check boxes for categories that you want to remove from the selection.
  15. Click OK to close the Limit Data dialog.
  16. When you are done with your data selection it is recommended to click the Estimate Import Size button to get a hint on whether this selection will be possible to import or not.
    The calculation may take a while. No measures are taken into account in the calculation, so if you have added many measures, you may be unable to import the selection even if the estimated number of rows and columns is small. The estimated import size may be of help if an import should fail. Then, the next time you try to import data from that cube, you should try to make sure that the estimated size is smaller than the previous time.
  17. When you are done with the data selection, click OK.
  18. The final step, the Add data to analysis flyout, is a summary view of the data tables that will be added to your Spotfire analysis. Make sure that the data table that is added from your connection is configured as Imported, for in-memory analysis.
    Tip: To change the load method of a data table to Imported, click the data table in the summary view, and then use the drop-down menus to select Imported.
  19. Click OK.

Results

If the data selection was small enough, the data is imported.
Tip: Do all of the data selection work in a shared data connection and save it to the library instead of adding it to the analysis directly. This way you can reuse the work you have done later, or share your selections with others.
Note:
  • During import, sets are applied once. This means that selection of multiple sets with no intersecting data will result in a data table without any data.
  • PKIs are not supported.
Copyright © Cloud Software Group, Inc. All rights reserved.