Join conditions in FM

[tupac_rd]

Hi Gurus,

I am trying to create a FM model for a new Oracle db which has been created to replace an old access db/reports system. To being with there are 6 tables, and I have joined them and when I create the 1st report, to mimic the one in Access - the join conditions are created as with any other regular Oracle db,
for eg. table1 = table 2 and
table 2 = table 3 etc.,...

Even after adding the required filters to the report in report studio, I am not able to run the report. I am getting a temp table space error.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

So, I got the SQL from the Access report and used that SQL in report studio, and when I run against the new Oracle database, the report runs without errors. Total no. of records is 64465. The sql used in the report is

SELECT schema.table1.claimnumber,
schema.table2.modelversionnumber,
schema.table1.linenumber, schema.table1.revenuecode,
schema.table1.procedurecode,
SUM (schema.table3.unitsofservice) AS sumofunitsofservice,
SUM (schema.table3.totalcharges) AS sumoftotalcharges,
SUM (schema.table2.allowed) AS sumofallowed,
SUM (schema.table4.savings) AS sumofsavings,
SUM
(schema.table4.groupcharges
) AS sumofgroupcharges,
SUM
(schema.table4.allowedcharges
) AS sumofallowedcharges,
schema.table5.comments,
schema.table6.ratesheetcode,
schema.table2.basedoncode,
schema.table2.ratetype,
schema.table4.apcnumber,
schema.table4.apcstatusind,
schema.table2.deleteflag
FROM schema.table4
INNER JOIN
((schema.table2 INNER JOIN schema.table1
ON (schema.table2.claimnumber =
schema.table1.claimnumber
)
AND (schema.table2.linenumber =
schema.table1.linenumber
))
INNER JOIN
(schema.table6 INNER JOIN schema.table5
ON schema.table6.ratesheetid =
schema.table5.ratesheetid)
ON schema.table2.repriceterm =
schema.table5.ratesheettermid)
ON (schema.table4.ownerid =
schema.table1.ownerid
)
AND (schema.table4.claimnumber =
schema.table1.claimnumber
)
AND (schema.table4.linenumber =
schema.table1.linenumber
)
AND (schema.table4.ratesheetcode =
schema.table6.ratesheetcode
)
AND (schema.table4.modelversionnumber =
schema.table2.modelversionnumber
)
GROUP BY schema.table1.claimnumber,
schema.table2.modelversionnumber,
schema.table1.linenumber,
schema.table1.revenuecode,
schema.table1.procedurecode,
schema.table5.comments,
schema.table6.ratesheetcode,
schema.table2.basedoncode,
schema.table2.ratetype,
schema.table4.apcnumber,
schema.table4.apcstatusind,
schema.table2.deleteflag,
schema.table2.USAGE
HAVING ( ((schema.table6.ratesheetcode) = "XXXXXXX")
AND ((schema.table2.deleteflag) = 0)
AND ((schema.table2.USAGE) = "A")
)
ORDER BY schema.table1.claimnumber

How do I make FM write join conditions as in the above SQL instead of how it ususally writes.

Also, please let me know if I need to do something else.

Thanks

[blom0344]

Your group by clause has many , many objects. The having clause does not reference an aggregate and can therefore be changed to a where clause.  The SQL also sports an explicit order by which can be very costly

You mention > 60.000 rows as output. I am not surpised the database runs out of temp space with such a resultset.  What type of report are you going to build against such a massive set? 

[tupac_rd]

Hi Blom,

Thanks for the reply. This is a POC I am trying to do. The issue is if I stick the SQL as is into Report Studio, the report runs in like 12 minutes, but if I try to replicate the joins from the SQL into FM model/package and then build the report, the joins are not in that order. Cognos just avoids doing loop joins and instead uses the shortest join path etc., and the time it takes to run the report is like 30 minutes, and the data doesn't match the SQL report either...

Is there a way to make Cognos do loop joins.

Thanks

[blom0344]

Your SQL involves joins to logical expressions instead of simply having joins between tables.  If you want to simulate this in FM you can create SQL query subjects (so, instead if imported tables, create sql expressions) and use these in the model. This way it is possible to create more complex sql (inline structures; pre-aggregates)

I suspect the old Access source is a good place to start with. It was all too easy to write Access queries (the equivalent to the Cognos SQL subject) and use the query result as object within a Access model

[tupac_rd]

Hi Blom,

Thanks again for the reply. If possible can you please elaborate more, about how to replicate this in FM.

[blom0344]

I would take the original access model and check whether any query definitions (instead of tabels) where used to come up with the ultimate SQL. Are you aware of the possibility to create your own logical SQL subjects?

[tupac_rd]

Yes, that's the first thing I had looked when I started looking at access, and they were all simple joins between the tables in Access.... maybe I am not looking at the right place...

and yes, I think we can create our own datasource query subjects with joins between 2 or more tables etc., So, isn't that the same as Merge query subjects....as a model QS?

[blom0344]

Perhaps check again. In Acces you can build a query, save it and reuse it in the model (as a virtual table/ equivalent of a database view) In FM you can define your own equivalent of a db view as an SQL subject.  Merging is essentially taking 2 database query subjects as basis for a new model query subject

[tupac_rd]

Thanks for the reply again.

I created the Access report/query and joins to the tables myself in Access, and I get the exact same query which was given to me, and I posted here initially. I am not able to replicate it in FM. Perhaps there is a setting in Access to join them in that order, I am not sure how or where...

[blom0344]

Acces uses its own Jet SQL which will never be replicated identically when you build a FM against Oracle. The latest Oracle versions are ANSI compliant, meaning that where clause should only contain filters and no joins. If you look closely at your Jet-SQL example you will notice that it is build with a mix of ansi and non-ansi syntax. There are both ansi style joins ('ON') join expressions in where clauses. This may impact query execution order. (and results)

If you can run a visual explain of the query in Oracle (DBA?) then you may get an idea in which order joins are executed. In FM this would mean merging the right tables to new query subjects to ensure join precedence..

[tupac_rd]

I think I finally figured out what was going on. I don't need the loop joins in Cognos. But the difference in no. of rows returned was due to the 'Auto Group and Summarize' property...

So here is what I don't understand

The access query which I have posted does have the group by, so all I did in Report/Query Studio was add the columns and run the query with 'Auto Group and Summarize' in the query property selected to 'Yes' by default. The number of rows is different than what I get from Access. But if I change the 'Auto Group and Summarize' in the query property to 'No', I get the same number of rows in both Cognos and Access query.

So, with 'Auto Group and Summarize' 'Yes', I am getting more rows compared to if it is set to No. I thought it should be the other way around.

So, what is it I am doing wrong. 

[Lynn]

Take a look at the SQL that gets generated for your report (you can do this in Report Studio). My guess is that you are not seeing anything wrong at all....it's a feature, not a bug.  ;)

The auto group and summarize means that measures get aggregated and duplicates suppressed.

If you data has three rows (below) you'd see just that when auto group and summarize is off:

Product -- Amount
A  -- $1
A  -- $2
B -- $5

With auto group and summarize turned on (the default behavior), you would see:

Product -- Amount
A  -- $3
B -- $5

[blom0344]

Very odd indeed..  The standard setting allows aggregate setting (usage in FM) to be used. This normally should yield fewer output in term of rows returned. It is a matter of comparing the SQL generated (like Lynn suggested) to see what is going on..

[tupac_rd]

I checked the SQL, and there are differences

If Auto Group and summarize is no, it is nice and clean

select * from table 1, table 2
where table 1 column 1 = table 2 column 1
etc,

For Autogroup and Summarize yes,

the SQL is ugly with all these 'over partition' statements in the sql and I am getting many more rows than with seting as no, which I really don't understand

[Lynn]

You might also compare the native SQL with the Cognos SQL. This would help you see any processing that may be happening locally rather than on the database server.

Getting more rows is very strange and I missed that point when I read your earlier post.

As Blom indicated, the usage property in FM set to fact identifies which items get aggregated when auto group and summarize is "yes". If there are not facts in your query you'd typically see a select distinct with that property set to "yes".

[Arsenal]

just a quick thought....over partition means somehow Cognos is trying to create a cube on the and maybe that's why it's running out of the temp space that is assigned to the UID on oracle?

2 years back at a client, when we upgraded from 8.2 to 8.3, suddenly some of the reports started to run slower. The Db was Teradata. When we started looking at the SQL, we noticed all these "over partition" clauses in the SQL for one of the slow running reports. After troubleshooting for a day or two, it was pointed out that the usage of a particular function that was turned off in 8.2 had not been turned off in 8.3 leading to the generation of the "Over partitions" in the SQL..I think it was "Analytical Functions" settings in one of the system files but I've forgotten the name of the particular system file

Maybe if you can find the same setting in your installation and see if the same option is available and can be turned off, you'll see better sql?

just a thought