Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] Data Profiling on master file join using FOCUS Code.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE OPENED] Data Profiling on master file join using FOCUS Code.
 Login/Join
 
Neelima
Platinum Member
posted
Hi All,

I need to implement the data profiling FOCUS code to in fex. please educate me here.

Thanks In Advance.

-Neelima

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 186 | Location: INDIA | Registered: July 11, 2013Report This Post
Frans
Guru
posted Hide Post
PROFDATA CAR
HOLD FORMAT FOCUS AS TST

TABLE FILE TST PRINT *
END


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Nice Thread

I had never seen that before, good to know!


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Neelima
Platinum Member
posted Hide Post
Thank you Frans!!

When I am applying this code on cluster.its taking lots of time.
Can we improve this performance by selected columns. if yes , please help me with the code.

Thank you so much!!!

-Neelima


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 186 | Location: INDIA | Registered: July 11, 2013Report This Post
Clif
Guru
posted Hide Post
You can profile an individual column, for example:
PROFDATA CAR.COUNTRY.ORIGIN

However if you want multiple columns that would require multiple calls.

If your signature is correct and you are still using 7.7.03 you would see improved performance by upgrading to the current Release 7.7.09. Also in that release the full syntax of the command is:

PROFDATA synonym[.segment[.field]] [,,,BRIEF]


Where the new option, BRIEF, suppresses generation of the statistics Patterns Count, Mode and Median resulting in faster processing.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Doug at Kencura
Member
posted Hide Post
I had not used PROFDATA so played with it. It could be very useful for profiling data.

Turning SQL traces on, however, you can see it runs into optimization issues when profiling SQL tables. 
 SET TRACEOFF = ALL
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = SQLAGGR//CLIENT
 SET XRETRIEVAL = ON
 SET TRACEUSER = ON



The PROFDATA command wants to do multiple LST prefix operations which the SQL adapter doesn't like. The SQL engine doesn't summarize the answer set, but instead returns all records to let WebFOCUS do the aggregation and prefix operators. Here is the message:
 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2617) MULTIPLE LST. IN REQUEST:


Even when profiling a single column, PROFDATA still has a SQL optimization issue: 
 (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 (FOC2595) ONLY ADD, SUM, CNT, AVE, MIN, AND MAX PREFIXES CAN BE AGGREGATED



Using WF8.2.04, the BRIEF option didn't seem to help with this issue.

So perhaps somebody can tweak the PROFDATA command for SQL optimization.

This message has been edited. Last edited by: Doug at Kencura,


Sincerely,

Doug Lautzenheiser
Multiple products (FOCUS, WebFOCUS, iWay), releases (4-8.2), platforms (e.g., MVS, OpenVMS, Linux, iSeries, Windows), databases (e.g., Oracle, SQL Server, DB2, IMS, Hyperstage, etc.), integrated technologies (e.g., C/C++, R, Python)
Currently doing great things on WF8.2
 
Posts: 7 | Location: Midwest United States | Registered: February 14, 2007Report This Post
Clif
Guru
posted Hide Post
DataMigrator 7.7.09 corresponds to WebFOCUS 8.2.05.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Neelima
Platinum Member
posted Hide Post
Hi Clif, I am using 8204.

Doug,

Is there any way to get the location of PROFDATA batch file. So that we can drive the method from there.

Thanks.


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 186 | Location: INDIA | Registered: July 11, 2013Report This Post
Doug at Kencura
Member
posted Hide Post
If PROFDATA is a FOCEXEC, it is hidden well. I suspected a LET command might be changing what PROFDATA actually meant but don't see that either.

It could very well be that PROFDATA is an internal function that only IB can change. Opening a case about PROFDATA and SQL optimization might be a good idea.

This message has been edited. Last edited by: Doug at Kencura,


Sincerely,

Doug Lautzenheiser
Multiple products (FOCUS, WebFOCUS, iWay), releases (4-8.2), platforms (e.g., MVS, OpenVMS, Linux, iSeries, Windows), databases (e.g., Oracle, SQL Server, DB2, IMS, Hyperstage, etc.), integrated technologies (e.g., C/C++, R, Python)
Currently doing great things on WF8.2
 
Posts: 7 | Location: Midwest United States | Registered: February 14, 2007Report This Post
Neelima
Platinum Member
posted Hide Post
Hi Doug,

OK. I opened the case for same and lets wait for their some good response.

Thanks
Neelima


WebFocus 8104,8204
Excel/PDF/HTML/HTMLTABLE/XML/ALPHA/GIF file/GRAPH/Active technologies
 
Posts: 186 | Location: INDIA | Registered: July 11, 2013Report This Post
Frans
Guru
posted Hide Post
We've opened a case once for the performance thing, the problem is that patern, median, mode etc. cannot be converted to SQL in most cases.

if you use the BRIEF option it will only use optimized statements, be be aware to use the right syntax with 3 comma's:

PROFDATA CAR,,,BRIEF
HOLD AS TST

TABLE FILE TST PRINT *
END


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] Data Profiling on master file join using FOCUS Code.

Copyright © 1996-2020 Information Builders