I am looking for advice about a report I need to create against a complex oracle relational structure. The report I am creating requires 8 joins where two fields join from the fact to the dimension and 2 joins of one field from the fact to the dimension for a total of 10 joins from fact to dimension tables. There are only three conditions restricting the data returned and the results are expected to be about 1.2 million rows. In the results there are 8 columns or "BY" fields, and the values that return are "crosstabbed" across one of the dimension values.

I have tried several approaches so far.

1) Create one master file with all of the necessary joins and write a focexec against that master file using DEFINES for the "crosstabbed" values. Was going to send data to a comma-delimited text file.

This did not work. Was taking too much time. Got advice that the DEFINEs were probably a problem. So decided to try a different approach.

2) Use master file from 1, create a hold file, then DEFINE the "crosstabbed" values on the hold file and query it for the report, sending the results to COMT format flat file.

This try was killed after it ran 3 days (over a weekend). Needed a new plan.

3) Decided that it may work to bring data from fact and all 10 dimensions back as separate focus format hold files and make joins in WF. Then make DEFINEs on the fact hold file and query the hold file.

PROBLEM: Can't have two joins per dimension in focus hold files. Need yet another plan.

4) (Working on right now...) Modify master files for fact table and the 8 two-field join dimension tables to make concatenated keys in the master files. Then proceed with getting focus format hold files, joining them, defining "crosstabbed" values, and saving the results to COMT format file.

Question:

Am I approaching this in the right way?
Does anyone have any different ideas?


P.S. I also have the DBAs working on tuning the sql and checking the database for optimization.



Here is what Option 2 sql produced by WebFOCUS looked like:

SELECT
T25."METRIC_MERGE_VIEW_LABEL",
T1."ID_2",
T2."DATA_SET_LABEL",
T20."CODE_1",
T22."CODE_1",
T11."CODE_1",
T7."CODE_1",
T5."CODE_4",
T6."CODE_4",
T18."CODE_1",
T10."CODE_3",
SUM(T1."VALUE")
FROM
QWESTRPT.CDRACT_OPS_FACT_1 T1,
QWESTRPT.CDRACT_DATA_SETS T2,
QWESTRPT.CDRACT_DIM_20 T5,
QWESTRPT.CDRACT_DIM_19 T6,
QWESTRPT.CDRACT_DIM_4 T7,
QWESTRPT.CDRACT_DIM_22 T10,
QWESTRPT.CDRACT_DIM_5 T11,
QWESTRPT.CDRACT_DIM_12 T18,
QWESTRPT.CDRACT_DIM_13 T20,
QWESTRPT.CDRACT_DIM_14 T22,
QWESTRPT.CDRACT_ID_METRIC_MERGE_VIEW T25
WHERE
(T2."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND(T5."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND (T5."DIM_20_KEY" =T1."DIM_20_KEY")
AND (T6."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND (T6."DIM_19_KEY" = T1."DIM_19_KEY")
AND (T7."DATA_SET_KEY"= T1."DATA_SET_KEY")
AND (T7."DIM_4_KEY" = T1."DIM_4_KEY")
AND(T10."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND (T10."DIM_22_KEY"= T1."DIM_22_KEY")
AND (T11."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND (T11."DIM_5_KEY" = T1."DIM_5_KEY")
AND (T18."DATA_SET_KEY"= T1."DATA_SET_KEY")
AND (T18."DIM_12_KEY" = T1."DIM_12_KEY")
AND (T20."DATA_SET_KEY" = T1."DATA_SET_KEY")
AND(T20."DIM_13_KEY" = T1."DIM_13_KEY")
AND (T22."DATA_SET_KEY" =T1."DATA_SET_KEY")
AND (T22."DIM_14_KEY" = T1."DIM_14_KEY")
AND(T25."METRIC_KEY" = T1."METRIC_KEY")
AND (T1."ID_2" ='123456123456')
AND (T2."DATA_SET_LABEL" = '200102')
AND (T25."METRIC_MERGE_VIEW_LABEL"IN('ACCESS_FIRST', 'ACCESS_SECOND', 'ACCESS_THIRD','ACCESS_FOURTH', 'ACCESS_FIFTH'))
GROUP BY
T25."METRIC_MERGE_VIEW_LABEL",
T1."ID_2",
T2."DATA_SET_LABEL",
T20."CODE_1",
T22."CODE_1",
T11."CODE_1",
T7."CODE_1",
T5."CODE_4",
T6."CODE_4",
T18."CODE_1",
T10."CODE_3"
ORDER BY
T25."METRIC_MERGE_VIEW_LABEL",
T1."ID_2",
T2."DATA_SET_LABEL",
T20."CODE_1",
T22."CODE_1",
T11."CODE_1",
T7."CODE_1",
T5."CODE_4",
T6."CODE_4",
T18."CODE_1",
T10."CODE_3"
;


NOTE: The report will be "crosstabbed" by creating a DEFINE using the METRIC_MERGE_VIEW_LABEL values and the sum(VALUE).

Joan,

We use DB2 and have some very complicated requests against our dimension and fact tables. We would approach this as a SQL pass-thru situation. Rather than having FOCUS create the SQL statements, we would write our SQL code (perhaps with DBA or Data Architect assistance) to give us the result set we expect. Then use FOCUS to format and style the result set. Some complicated SQL code is best segregated from FOCUS.

Not sure about the etiquette for replying to a reply... but here goes.

Did you just write the SQL and send it as SQL or generate SQL from a master file describing the fact and joins to dimensions?

quote:

Originally posted by KevinG:
Joan,

We use DB2 and have some very complicated requests against our dimension and fact tables. We would approach this as a SQL pass-thru situation. Rather than having FOCUS create the SQL statements, we would write our SQL code (perhaps with DBA or Data Architect assistance) to give us the result set we expect. Then use FOCUS to format and style the result set. Some complicated SQL code is best segregated from FOCUS.

Joan,

We use the following syntax;

ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
ENGINE DB2
SELECT
....
....
....
....
TABLE ON TABLE HOLD AS &VIEWNAME FORMAT ALPHA

This lets the SQL server do all of the hard work. The SQL code after the SELECT can be as complicated as you need it to be. (Be aware of the 65,000 byte limit for the code you are passing). Of course you will replace DB2 with the syntax for your version of SQL (ORA, etc.) and the &DBNAME with whatever your database connection is named. &VIEWNAME can be replaced with whatever name you want your SQL output held as.

Note: The select statement is pure SQL and JOINS are performed at the SQL level. NO master file decriptions involved, and NO FOCUS JOIN restrictions. You can use sub-selects, grouping, computed values, and ordering at the SQL level before your result set is returned to FOCUS.