KB45179: Best practices for avoiding metadata issues that result from Duplicate Columns in MicroStrategy and how duplicate columns can be created.

While some duplicate columns in a Strategy schema are necessary or benign, a user may experience issues that require the assistance of Technical Support in merging or removing duplicate columns. While Technical Support can help to resolve these issues, the user would like to know how to avoid them in the future. This technical note describes various best practices for avoiding these duplicate columns.

1. Avoid lower-case table names. Some database platforms are case sensitive while others are not. In order to support all of these platforms, the MSTR detection of tables/columns must be case sensitive behind the scenes. KB5521  explains that Strategy does not allow more than one table or column with names only different in cases, but since the table/column detection is case sensitive, issues can sometimes occur.
2. Be mindful of names and data types when pulling tables from different warehouse databases into the same metadata. If the names or data types of the columns across the databases differ in any way, a duplicate column will be created in each scenario regardless of which Column Merging Options are used. Different mappings of database datatypes to Strategy datatypes for different databases, case mismatches, and prefix mismatches all have a chance of causing a new duplicate column object to be created instead of using an existing one.
3. See KB16858  to learn how to avoid true duplicate columns in Logical Views. (This same idea applies to the Freeform SQL editor.) If the column is created using the 'Add' button and also Automatic mapping is turned on, the attributes won't make reference to the existing column since a new column object is created.
4. See KB323289  on how to avoid duplicate columns when updating table structure and importing new tables to bring same new column into project. 

Below is an explanation on the criteria used to merge a column depending on the Column Merging Options when we add a new table to a project. We will use the case of columns having the same name:

1. Use most recent data type: Columns will always be merged. The column data type of the later added column will be used.
2. Use maximum denominator data type: If the columns' data types are compatible, they will be merged based on current rules of data type compatibility:
                       a.Same data types with different precision and /or scale are considered compatible. The target data type will be augmented to have the   higher precision and /or scale of the two types.
                        b. DssDataTypeUnknown is compatible with any known type and is always augmented to the other type.
                        c. Integral numeric data types are mutually compatible and are augmented according to the following: short -> unsigned -> integer -> long -> Int64
                        d. Fixed-length floating point data types are mutually compatible and are augmented according to this: Float-> Double
                        e. All string / text data types are mutually compatible. They are augmented as follows: Char -> VarChar -> LongVarChar
                        f. All binary data types are mutually compatible. Augmentation is: Binary -> VarBin -> LongVarBin
                        g. Different Date, Time, TimeStamp types are always augmented to TimeStamp
3. Do not merge: Merge only when columns are a perfect match, same datatype, precision and scale. Otherwise it will create another column and rename it. In order to avoid duplicate columns, Strategy Technical Support recommends not using this option.

KB45179