KB30793: How to insert Microsoft SQL Server 2005 Synonym to Warehouse Catalog in MicroStrategy

• Strategy

Microsoft SQL Server 2005 introduces the concept of a synonym. A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base object by using a synonym instead of using a two-part, three-part, or four-part name to reference the base object.

Summary:
Microsoft SQL Server 2005 introduces the concept of a synonym. A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base object by using a synonym instead of using a two-part, three-part, or four-part name to reference the base object.
Strategy Architect can access synonyms just like tables from database. In order for this access to be available some changes have to be made in the construction of SQL used by warehouse catalog.
1) In Strategy Desktop 9.3.x /Developer 9.4.x go to "Schema" menu and click "Warehouse Catalog..."

2) On Warehouse Catalog, access the read settings by going to Options > Catalog Read Settings > Settings:

3) Change SQL for tables and then for columns inserting the following SQL statements:
TABLES:


SELECT DISTINCT S.name NAME_SPACE, T.name TAB_NAME 
FROM sys.all_objects T  JOIN sys.schemas S on T.schema_id = S.schema_id    
WHERE T.type IN ('U', 'V', 'SN') AND S.name IN ('dbo', N'#Login_Name#')

COLUMNS:


SELECT S.name NAME_SPACE, T.name TAB_NAME, C.name COL_NAME, C.user_type_id DATA_TYPE, C.max_length DATA_LEN, C.precision DATA_PREC, C.scale DATA_SCALE   
FROM sys.all_objects T   
JOIN sys.columns C ON T.object_id = C.object_id   
JOIN sys.schemas S ON T.schema_id = S.schema_id   
WHERE T.name in (#TABLE_LIST#)   
UNION  
SELECT S.name NAME_SPACE, SY.name TAB_NAME, C.name COL_NAME,   
C.user_type_id DATA_TYPE, C.max_length DATA_LEN, C.precision DATA_PREC, C.scale DATA_SCALE   
FROM sys.all_objects T   
JOIN sys.columns C ON T.object_id = C.object_id   
JOIN sys.schemas S ON T.schema_id = S.schema_id   
JOIN sys.synonyms  SY ON T.name = (SUBSTRING(SUBSTRING(SY.base_object_name,   
                                CHARINDEX('.', SY.base_object_name)+ 2,   
                                len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name)   
                      ), CHARINDEX('[',SUBSTRING(SY.base_object_name,   
                                CHARINDEX('.', SY.base_object_name)+ 2,   
                                len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name)   
                      )) + 1, LEN(SUBSTRING(SY.base_object_name,   
                                CHARINDEX('.', SY.base_object_name)+ 2,   
                                len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name)   
                      )) - CHARINDEX('[',SUBSTRING(SY.base_object_name,   
                                CHARINDEX('.', SY.base_object_name)+ 2,   
                                len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name)   
                      )) -1))

4) Click OK in the above editor to confirm the new SQL for Warehouse Catalog. Click OK for the first "Warehouse Catalog Options" and click "Save and Close" to save new options and close the Warehouse Catalog.
5) When Warehouse Catalog is opened again, Synonyms are now included and can be used in the project similar to table objects.
IMPORTANT NOTE:
SQL provided works only when base_object_name has format of [database name].[schema name].[table name] which is the standard format for MS SQL Server 2005.

Comment

0 comments

Details

Knowledge Article

Published:

May 12, 2017

Last Updated:

May 12, 2017

SELECT DISTINCT S.name NAME_SPACE, T.name TAB_NAME FROM sys.all_objects T JOIN sys.schemas S on T.schema_id = S.schema_id WHERE T.type IN ('U', 'V', 'SN') AND S.name IN ('dbo', N'#Login_Name#')

SELECT S.name NAME_SPACE, T.name TAB_NAME, C.name COL_NAME, C.user_type_id DATA_TYPE, C.max_length DATA_LEN, C.precision DATA_PREC, C.scale DATA_SCALE FROM sys.all_objects T JOIN sys.columns C ON T.object_id = C.object_id JOIN sys.schemas S ON T.schema_id = S.schema_id WHERE T.name in (#TABLE_LIST#) UNION SELECT S.name NAME_SPACE, SY.name TAB_NAME, C.name COL_NAME, C.user_type_id DATA_TYPE, C.max_length DATA_LEN, C.precision DATA_PREC, C.scale DATA_SCALE FROM sys.all_objects T JOIN sys.columns C ON T.object_id = C.object_id JOIN sys.schemas S ON T.schema_id = S.schema_id JOIN sys.synonyms SY ON T.name = (SUBSTRING(SUBSTRING(SY.base_object_name, CHARINDEX('.', SY.base_object_name)+ 2, len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name) ), CHARINDEX('[',SUBSTRING(SY.base_object_name, CHARINDEX('.', SY.base_object_name)+ 2, len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name) )) + 1, LEN(SUBSTRING(SY.base_object_name, CHARINDEX('.', SY.base_object_name)+ 2, len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name) )) - CHARINDEX('[',SUBSTRING(SY.base_object_name, CHARINDEX('.', SY.base_object_name)+ 2, len(SY.base_object_name)-CHARINDEX('[', SY.base_object_name) )) -1))