MicroStrategy ONE

Connecting data sources to a project

You can connect a project to a data source through a database instance. A database instance specifies warehouse connection information, such as the data source name, login ID and password, and other data source specific information. For information on creating a database instance, see the Installation and Configuration Help.

Once database instances have been created for your data sources, you can connect them to your project. However, keep in mind that if you include multiple data sources in a project, the data sources should all fit into the same logical data model and warehouse structure planned for your project. For information on planning a logical data model and a physical warehouse structure, see The Logical Data Model and Warehouse Structure for Your Logical Data Model.

The procedure below describes how to include multiple data sources in a project.

Prerequisites

  • A project has been created.

  • Database instances have been created for the data sources to include in a project.

  • You must have the MultiSource Option to connect multiple data sources to a project.

To include multiple data sources in a project

  1. In Developer, log in to a project.

  2. Right-click the project and select Project Configuration. The Project Configuration Editor opens.

  3. From the Categories list, expand Database Instances, and then select SQL Data Warehouses.

  4. In the Database Instance pane, select the check box next to the database instances for the data sources to include in a project.

    Selecting a check box for a database instance also makes its data source available for use with Query Builder and Freeform SQL. The availability of multiple data sources through Query Builder or Freeform SQL does not require the MultiSource Option. However, only one data source can be used at a time in a Query Builder or Freeform SQL report. For information on Query Builder and Freeform SQL, see the Advanced Reporting Help.

  1. In the drop-down list near the top, select a database instance to act as the primary database instance.

    The primary database instance acts as the main source of data for a project and is used as the default database instance for tables added to the project. Non-database related VLDB property settings are also inherited from the primary database instance.

To determine the order of data source access

You can define the order in which data sources are used to provide data as part of the MultiSource Option, when the same data can be retrieved from multiple data sources.

  1. From the Categories list, expand Report definition, and then select SQL generation.

  2. In the Database Instance Ordering area, you can define the order in which data sources are used to provide data as part of the MultiSource Option, as described below:

    1. To define a priority in which each data source is used to access data as part of the MultiSource Option, click Modify. The Select Objects dialog box opens.

    2. Move any applicable database instances for the project to the Selected objects pane.

    3. You can then use the up and down arrows to change the priority of each database instance, with the database instance at the top of the list having the highest priority. Highest priority means that the database instance is used first if it is one of the database instances that can be used to retrieve the requested data. In addition to using this order for a project, you can enable or disable the use of this ordering for individual reports, as described in the Developer Help (formerly the MicroStrategy Desktop Help).

    4. You have the following options to determine the order in which data sources are selected for the project:

      • Use MultiSource Option default ordering (default): If data is available in multiple data sources through MultiSource Option, the primary database instance is used if it has the necessary data. If the data is only available in other secondary data sources, one of the secondary data sources that includes the data is used to retrieve the necessary data using some basic internal logic. Any data source priority that you defined using Database Instance Ordering is ignored.

        By selecting this option, this MultiSource Option default ordering is used for all reports in a project by default. You can enable or disable the use of this ordering for individual reports, as described in the Developer Help (formerly the MicroStrategy Desktop Help).

      • Use project level database instance ordering: If data is available in multiple data sources through MultiSource Option, the data source used to retrieve the data is based on the priority that you defined using Database Instance Ordering. If data is only available in a data source that is not included in the priority list, then an applicable data source is chosen using the standard MultiSource Option logic.

        By selecting this option, the data source priority list that you defined for the project is used for all reports in a project by default. You can enable or disable the use of this ordering for individual reports, as described in the Developer Help (formerly the MicroStrategy Desktop Help).

To save your changes and complete the configuration

  1. Click OK to save your changes and close the Project Configuration Editor.

  2. MicroStrategy includes additional techniques that can help to improve the performance of MultiSource Option in certain scenarios, including:

    • If the data source you use with MultiSource Option supports parameterized queries, you can enable the use of parameterized queries to improve the performance of MultiSource Option. For information on enabling the use of parameterized queries, see Improving database insert performance: parameterized queries.

    • Using MultiSource Option means that multiple passes of SQL are executed to return the result from more than one data source. You can execute these multiple passes of SQL at the same time by using parallel SQL execution. For details on how parallel SQL execution works and how it can be enabled using the Parallel SQL Execution VLDB property, refer to the System Administration Help.

The data sources you included in the project can now be accessed from the Warehouse Catalog and Architect to import tables into the project, as described in Adding data into a project below.