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.

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)

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.

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,

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.

hamm01j - you requested I set up a signature. I'm not sure what you mean or how to do that.

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.

Sharon

Aloha - How to set up a Signature


John

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.

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!

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!