Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn IB Bloggers
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Extracting SCD type from the SQL WF Repository database
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Extracting SCD type from the SQL WF Repository database
 Login/Join
 
Member
posted
We have hundreds of synonyms we need to confirm have the correct FIELDNAMEs marked with SCD Type of Logical Key Field. We have a SQL database for our WebFocus repository. I've looked around a little, but I'm not sure what to look for.

What table(s) and column(s) of the WebFocus repository database can I query to find this information? I would like to get a result of TABLENAME, FILENAME, FIELDNAME, ALIAS, SCD TYPE. A bonus item would be FIELDTYPE (index, read-only).

Thanks.

This message has been edited. Last edited by: Chuck Wolff,


WebFOCUS 8.1M
Windows, All Outputs
 
Posts: 3 | Registered: November 19, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
The synonyms should be on the reporting server, under the APPS directory.

You should be able to use SYSTABLE/SYSCOLUM to get the info.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.01OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code
 
Posts: 5618 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
I think you may be looking in the wrong place if you mean "WebFOCUS Repository" (and not an external "SQL Repository" managed from the DMC or WebConsole).

That kind of information is stored in the Synonyms (Master and Access files) on the WebFOCUS Reporting server, or I'm guessing in your case the DataMigrator server. In Release 7.7.07 and 8.2 we've added some procedures to simply extracting this information, but you can still obtain it in 8.1.

Most of the attributes you mention are in two (virtual) system tables (syscolum and systable) and you can extract that information in a DataMigrator flow (or a FOCUS TABLE request). These tables are distributed in the home/catalog directory which you can expose from the Application Directories context menu choice filter.

Alternatively to create a data flow with one of those tables as a source you can right click on the left side of a new workflow and instead of selecting a table enter one of the following.
_edahome/catalog/syscolum
_edahome/catalog/systable

Now you can use these virtual tables as you would a real table although you can't join them.

From systable to get the actual database table name
NAME, REALNAME

From syscolum you could select
TBNAME, NAME, ALIAS, READONLY

Note that FIELDTYPE=I is almost only used for indexes on FOCUS files not RDBMS tables. You can get the key information however.

Finally the attribute "SCD Type" is only used by DataMigrator and only in flows with a Load type of Slowly Changing Dimensions. As it happens that while that attribute is stored in the Access File it's not exposed in the syscolum table. If that's something you do need please open a case with InfoResponse requesting it as a new feature.
 
Posts: 325 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the feedback. To resolve my problem, I ended up using an entirely different approach. Our tables are modeled in ERStudio. I wrote a macro to read the model and re-create the synonyms. Our conventions allowed me to specify all of the pieces needed to create both the .mas and .acx files.


WebFOCUS 8.1M
Windows, All Outputs
 
Posts: 3 | Registered: November 19, 2014Reply With QuoteReport This Post
Guru
posted Hide Post
I'm glad you were able to work something else.

In the meantime we've added SCD_TYPE to SYSCOLUM and it will be available in the next release of the server.
 
Posts: 325 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Extracting SCD type from the SQL WF Repository database

Copyright © 1996-2017 Information Builders, leaders in enterprise business intelligence.