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     star schema reporting

Read-Only Read-Only Topic
Go
Search
Notify
Tools
star schema reporting
 Login/Join
 
Member
posted
I'm looking for any sources or documentation on best practices using WebFOCUS to report against a star schema design - where we have a single fact table with numerous dimension tables. Our tables are Oracle. We are using WebFOCUS 7.6.2, with the Reporting Server running under zLinux on the mainframe.


WF 7.7.01
Reporting Server on zLinux or Windows
Client on linux
Output formats - EXL2K, HTML, PDF
 
Posts: 14 | Registered: October 06, 2006Report This Post
Gold member
posted Hide Post
Sharon,

In a star schema, you have the single fact table which needs to join the the dimensions. There should only be a n:1 relationship from the fact table to the dimensions.

If you use the fact table as you primary table and perform a 1:1 join (JOIN KEY IN FACT TO KEY IN DIM AS ...), then this should work very well as focus will look at the retrieval as being only one segment. (use CHECK FILE FACT PICT RETR to verify)



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
Master
posted Hide Post
Sharon, Gizmo

Can you both set up signatures please? See the sticky at the top. It helps you in the end by not having to reiterate your specs.

Gizmo

Unfortunately the order of wf joins are not honoured by Oracle. It is all handled by the optimizer and there is a setting to try and make it recognise star schemas. Other useful thing is to turn parallelism on.

Sharon

If you want quicker x10 to x20 results than Oracle dataware housing try putting it into XFOCUS files. Honest, that's what we do.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Gold member
posted Hide Post
hammo1j,

Regardless of the datasource, you should use the fact table as the host for the focus joins. Since the join is considered one-one, focus will append the dimension tables to the parent segment. If you start with a dimension table, the join syntax will be one to many and focus will now set up multiple paths.

With multiple paths, focus will very likely create multiple sql select statements rather than a single select statement.

This message has been edited. Last edited by: Gizmo,



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
Platinum Member
posted Hide Post
Sharon,

Hammo1j is right. Most of performance gains need to be obtained at the DBMS level. One cardinal rule to follow in a dimensional model is that you should make sure that all the joins and aggregations are happening in the DBMS(pass thru) else it is going bring back huge amounts of data and kill the performance.


WF7.1.4 Prod/Test, MRE, self serve, DM
 
Posts: 176 | Location: Desplaines | Registered: August 05, 2004Report This Post
Member
posted Hide Post
hamm01j - you requested I set up a signature. I'm not sure what you mean or how to do that.


WF 7.7.01
Reporting Server on zLinux or Windows
Client on linux
Output formats - EXL2K, HTML, PDF
 
Posts: 14 | Registered: October 06, 2006Report This Post
Master
posted Hide Post
the oracle optimizer will determine what it thinks is the best way to handle the SQL query that webfocus passes to it. The order in which you join the tables in webfocus is immaterial to the database, as the order of the tables and joins in a query is immaterial.

by dimension tables, do you mean lookup tables? I'm not sure if this is the same term. If it is, then what you've got is a normalized database, rather than a datawarehouse. With a normalized database, you have a lot more options for performance optimization. As a simple example...

Fact table has Salesman #, sales Amount and a seperate lookup table contains the Salesman's name. This is normalized.

In a datawarehouse, the data is denormalized to aide in the efficiency of query writing or OLAP'ing. The joins are created in advance of your query, so the table would show something like
Salesman #, salesman name, Sales amount.

Very different animals, optimizing performance is very different between the two as they are entirely different concepts...but both can work very well if treated properly.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Master
posted Hide Post
Sharon

Aloha - How to set up a Signature


John



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Master
posted Hide Post
Here's the switch to put star schema on

SQL SQLORA ALTER SESSION SET star_transformation_enabled=true ;
END

You will need Oracles dw extensions to take advantage of this.

Oracle dw is a series of add ons which are a vain attempt to up the performance on dw type queries via bitmap indicies, partitioning, star schema. Once you go beyond a few tables in your select structure, the optimizer is not up to the task and most of the these features do not work properly in 9.2 or 10i

Save yourself $2m and just build the structure you want in XFOCUS. A rdbms (which in my view is a total abonimation and in 30 years time the rest of the world will believe this) will never beat a pointer based database.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Master
posted Hide Post
Sorry to go into rant mode but one thing that is a bit annoying about wf sql passthru is that you cannot put

/* type comments */

This means you cannot put in oracle hints to the optimizer because they are contained within comments.

I think its not very smart that wf does this!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by hammo1j:
Sorry to go into rant mode but one thing that is a bit annoying about wf sql passthru is that you cannot put

/* type comments */

This means you cannot put in oracle hints to the optimizer because they are contained within comments.

I think its not very smart that wf does this!


Hmm, that's strange because I've actually used /* */ to include comments in SQL pass-thru with SQLORA adapter with no issues whatsoever [WF 5.3.2]. In fact, I was "forced" to use them because WF didn't like the fact that I was using single-line comments (identified by double-hyphen in Oracle); after changing them to /* */ my SQL statements were accepted and WF (and I) were happy!



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report 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     star schema reporting

Copyright © 1996-2020 Information Builders