My personal encounter with synonyms in universe was not a pleasant one. It took long hours and ultimately the ‘trick in my bag’ for it to work.
Environment: SQL Server 2005; BO XI R3 SP1 FP3
Scenario: A Universe needs to be built that will source data from large tables in different databases.
Problem Statement: Data in deferent databases can be accessed using linked servers (if the databases are physically in different machines) or direct views (if the databases are in the same machine). In our case, the databases were in the same machine. ‘Select *’ views were created in the target database, but that was not a viable solution as the tables in the source database were huge. Any query on the views even with filtering conditions might result in full table scans depending on the tables involved.
The next option was to use synonyms that would not do a full table scan of the source tables whenever they are accessed. The problem is that synonyms cannot be accessed in the “Table Browser” with the default strategies.
Pic 1: BO Universe "Table Browser" does not list synonyms
SAP was contacted in this regard, and they said that synonyms cannot be accessed in the universes both in case of Oracle and SQL Server databases at it current state of patch level.
The Solution: The only other solution to achieve this was to use external strategies for tables that can be set in the Universe parameters.
Pic 2: Setting up universe to use 'External Strategies'
“Table Browser” within the universe typically runs a metadata query in the selected database based on the database type. This query returns the table and column definitions in a format usable by the “Table Browser”.
When the strategy is changed to use “External Stratgy”, Designer looks for a particular file for the definition of the query that needs to be run. This file is available at <install_path>\BusinessObjects Enterprise
Pic 3: Determine location of strategy file
Note: We are discussing an example of SQL Server 2005 accessed over ODBC, for other database and connection type, the file locations would be different.
By now you would have guessed that this file would have the metadata query required for generating the list of tables and columns, and you are correct. The SQL query is available at the following tag:
<Strategy Name="Synonyms">
<Type>STRUCT</Type>
<SQL>
The aim now is to update the SQL query that will also bring in the synonym details. The problem here is that the column definitions of synonyms are not stored in the system tables of a SQL Server database, and hence would not show up in the “Table Browser” window. To make it work, a query needs to be written that would extract the base_object_name from the synonyms and query the parent database for the actual table definition. This is made possible with the following query.
____________________________________________________________________
/******SYNONYM PART*****/
select distinct
isTable.table_catalog,'|',
isTable.table_schema,'|',
Psysobjects.name,'|',
Psyscolumns.name,'|',
case
when Psyscolumns.xtype in (127,104,106,62,56,60,108,59,52,122,48) then 'N'
when Psyscolumns.xtype in (175,167) then 'C'
when Psyscolumns.xtype in (61,58) then 'D'
when Psyscolumns.xtype in (35) then 'T'
when Psyscolumns.xtype in (173,34,165,36,189,231,239,99) then 'B'
else 'C'
end,'|',
'N','|'
from (select * from rakshit.dbo.sysobjects) Psysobjects, /*source table database of the synonym*/
(select * from rakshit.dbo.syscolumns) Psyscolumns,
(select * from rakshit.INFORMATION_SCHEMA.TABLES) isTable,
sys.synonyms isSyn
where (Psysobjects.name = reverse(substring(reverse(isSyn.base_object_name),2, charindex('[',reverse(isSyn.base_object_name),1)-2)))
and (Psyscolumns.id = Psysobjects.id)
and (isTable.table_name = isSyn.name)
/******TABLE PART*****/
SELECT DISTINCT
isTable.table_catalog,'|',
isTable.table_schema,'|',
dbo.sysobjects.name,'|',
dbo.syscolumns.name,'|',
case
when dbo.syscolumns.xtype in (127,104,106,62,56,60,108,59,52,122,48) then 'N'
when dbo.syscolumns.xtype in (175,167) then 'C'
when dbo.syscolumns.xtype in (61,58) then 'D'
when dbo.syscolumns.xtype in (35) then 'T'
when dbo.syscolumns.xtype in (173,34,165,36,189,231,239,99) then 'B'
else 'C'
end,'|',
'N','|'
FROM dbo.sysusers,
dbo.sysobjects,
dbo.syscolumns,
INFORMATION_SCHEMA.TABLES isTable
____________________________________________________________________When this query is run, the following output includes the table and column definition in the format usable by the “Table Browser”.
Pic 4: Extract for "Table Browser"
After changing the query with the above in the strategy file and saving it, the synonyms should be available for use in the designer. BUT, in most cases this query does not work fine when used from the universe and would result in no results in the “Table Browser”. The changed query does not run fine because there are multiple databases involved and access restrictions/grants do not work properly when the query is called from within BO Designer.
This is where a trick from ‘my bag of tricks’ comes to play. At the end of the day, BO Designer just need data in the above format to populate the “Table Browser”, it does not care where the data is coming from.
The Trick: Create a database view with the metadata query and use that as the source in the strategy file. As we are creating a view for the metadata query, it will be always up to date with new tables or synonyms. The only caution is that the view would require proper column names. The SQL section in the strategy file would look like:
<Strategy Name="Synonyms">
<Type>STRUCT</Type>
<SQL>SELECT table_catalog, delim1,
table_schema, delim2,
tab_syn_name, delim3,
col_name, delim4,
col_type, delim5,
value1, delim6
FROM DEV_D.dbo.UNIVERSE_METADATA
ORDER BY 1, 3, 5
</SQL>
</Strategy>
Note that the order by clause is very important to make sure that the table and columns are grouped properly.
And, now you have synonyms in your universe. Enjoy your cruise…
No comments:
Post a Comment