Multi Sourcing in Mstr

MultiSource reporting design

MicroStrategy SQL Generation Engine 9.x introduces a MultiSource reporting option, enabling a single report to access information from multiple databases.

SQL generation in MicroStrategy 8.x and 9.x is focused on metric data. Unless the report contains no metrics at all, the first step in generating SQL is to choose the fact table(s) required to calculate a given metric, taking into account attributes in the template, the metric's dimensionality, and report or metric filters. Once the fact table is known, lookup tables are chosen to support required attribute forms not present in the fact table.

Therefore, the primary purpose of the MultiSource option is to allow metrics in the same report to be calculated against different databases. Integration of lookup table data from multiple sources takes place as needed to support metric calculation.

Implications for attribute data modeling

1. Attribute ID forms must be mapped into all databases where the attribute is relevant. Users should not create, for instance, one Region attribute for one database and a second Region attribute for another. The MicroStrategy SQL Generation Engine will treat such attributes as separate entities and will not join their data.
   
   
   It is not necessary to have a lookup table copy in every database to which the attribute is mapped. At minimum, the attribute's ID form should be mapped into relevant fact tables. Replicating lookup tables in secondary databases is a performance optimization to avoid transferring lookup data between databases, but it is not a requirement for multisource reporting.
   
   
   Users might wish to have a set of reports that run against the primary warehouse, and a another set that uses only a secondary warehouse. In that case, each warehouse could have its own set of schema objects with no cross-mapping.
   
   
2. Attribute element IDs must be consistent across databases. It is not possible to join tables, even within the same database, without common key data between them. Therefore, for example, it is not supported to have one set of Region IDs in one database and a different set of IDs for the same attribute in another database.
   
   
3. Lookup tables in different databases for the same attribute should have the same set of attribute elements. Every attribute in MicroStrategy 9.x declares a primary lookup table. The primary lookup table should be complete, containing all of the attribute's elements. Auxiliary lookup tables in other databases should contain the same set of attribute elements with the same ID values. There is no provision in MicroStrategy 9.0 to create an intermediate table containing a union of attribute elements from different sources. Thus, it is not supported to have a partial list of attribute elements in each database.
   
   
   The analogous scenario in MicroStrategy 8.x, with no multisource reporting, would be a partitioned lookup table. MicroStrategy 9.x does not introduce support for partitioned lookup tables for single-warehouse reporting, and without that logic, it cannot be supported in multisource reporting. (Database-side partitioning schemes may be used, as long as they are transparent in SQL.)
   
   
4. If lookup tables share the same name across databases, their table structures should match. The MicroStrategy metadata maintains a single list of available columns per table name. Thus, for a given table name, columns and data types should match across all database instances hosting the table. For the structures to match, the tables must have the same column names and the data types for each column must be "compatible" across databases. The MicroStrategy 9 Project Design Guide (PDF manual) lists compatible data types in Chapter 9, "Optimizing and Maintaining Your Project" > Accessing multiple data sources in a project > Providing attribute information in multiple data sources.
    

   Datatype	Compatible types
   Integer	Decimal or Numeric with scale 0
   Decimal	Numeric
   Double, Float, Real	Double, Float, Real (all are mutually compatible with each other)
   Date	Timestamp
   Time	Timestamp
   Char	VarChar (but not NChar or NVarChar)
   NChar	NVarChar (but not Char or VarChar)

   
   
   If table structures are different between databases, two scenarios are possible:
   
   
   • A table imported from a secondary database is missing columns that exist in other databases. The MicroStrategy SQL Generation Engine could end up writing SQL for columns that are absent in the database executing the pass.
   • A table imported from a secondary database includes columns not present in the primary warehouse. The new columns could not be added to the column list, or incorrect SQL could be generated against the primary database.
   
   
   The column list for a table is set by the first data source from which the table is imported. After that, it would not be valid to import a table with the same name missing some columns; it would be necessary to remove columns from the list, which would break schema objects already mapped to those columns. It is possible to import a table containing extra columns not already imported; however, the extra columns will not be added to the column list because they cannot be supported by other databases.

Note: The MicroStrategy 9 Project Design Guide states that lookup tables from secondary databases can share the same name as tables in the primary warehouse and include additional columns that will be available for use in schema objects. This statement is in error. When attempting to import the table, the following message is shown in the Warehouse Catalog:

Table LU_REGION is already being used in the project associated with a different DB Instance. This table has additional columns that are not present in the existing table. These columns will not be imported into the project.

If the user proceeds with the table import, additional columns are not shown when trying to create a new attribute form based on the extra column (in this case, ALT_DESC would appear at the top of the list, but is absent).

However, a lookup table in a secondary data source may have a different structure from the primary lookup table, provided the table has a different name. Thus, a workaround is to create a database view selecting the lookup table contents under a different name. The view may be imported into the project as an independent table, after which attribute forms may be mapped to it.