EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB30336: Recommendations for physical modeling of attribute data in projects using the MultiSource option in MicroStrategy SQL Generation Engine

Community Admin

• Strategy

This article describes best practices for designing a data model to work with MultiSourcing in MicroStrategy.

MultiSource reporting design
Strategy SQL Generation Engine 9.x introduces a MultiSource reporting option, enabling a single report to access information from multiple databases.
SQL generation in Strategy 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 Strategy 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 Strategy 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 Strategy 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.
    Strategy does not have 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 Strategy 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 Strategy 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.
     


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (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 Strategy 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.
  5.  
    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 Strategy 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:
 

ka04W000000Odd7QAC_0EM440000002EUB.gif

 
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).
 

ka04W000000Odd7QAC_0EM440000002EUF.gif

 
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.

Comment

0 comments

Details

Knowledge Article

Published:

April 10, 2017

Last Updated:

April 10, 2017