PDA

View Full Version : Metadata SQL generation in Platform 3.5



codek
08-27-2009, 05:37 AM
Hi,

Looking at the SQL generated by metadata it now looks like the ID of the business table is used rather than the name as displayed in metadata editor.

In my case I have a physical table called:

BVH_PHARMA_COMPANY_STATUS

Unfortunately the ID generated for that by the 1.7 metadata editor is:

BT_BVH_PHARMA_COMPANY_STATUS_BVH_PHARMA_COMPANY_STATUS

Which is far too long for oracle.

In platform 2.0, and 1.7 i believe there were fixes made to work around this issue - in that it would never generate sql with an ID which is too long.

I think this change has been introduced because of the change made so that it now generates proper JOIN syntax.

So; Is this a bug, or should I just fix my metadata? It's certainly a regression failure from 1.7 to 3.5, but if thats how it is then so be it.

Perhaps if anything, the metadata should generate shorter business table id's?

Thanks,
Dan

wgorman
08-28-2009, 09:42 AM
Hi,

There is logic in the code that shrinks the name down below the maximum alias length of the database. Some databases don't have a max length, while others do. Are you seeing this behavior in the Metadata Editor or the BI Platform?

Will

codek
08-28-2009, 09:52 AM
this is in the bi platform, it is a CE ci build taken about a week after RC1 was released.

Same metadata file worked in 1.7. I can send the xmi file to a support call if you like? or I can email it direct or something?

Actually you should be able to reproduce in any old oracle example by setting the id of your business table to something very long.

codek
08-28-2009, 11:27 AM
Actually this is definately a bug. have a look at this generated sql:

BVH_PRODUCT_DATA_SOURCE BT_BVH_PRODUCT_DATA_SOURCE_BVH_PRODUCT_DATA_SOURCE JOIN BV_PRODUCT_DICTIONARY BT_BV_PRODUCT_DICTIONARY_BV_PRODUCT_DICTIONARY
ON ( BT_BVH_PRODUCT_DATA_SOURCE_B01.PRODUCT_DATA_SOURCE_ID = BT_BV_PRODUCT_DICTIONARY_BV_01.PRODUCT_DATA_SOURCE_ID )

Note that it joins to BV_PRODUCT_DICTIONARY giving it an alias of
BT_BV_PRODUCT_DICTIONARY_BV_PRODUCT_DICTIONARY

But in the join it uses an alias name of:

BT_BV_PRODUCT_DICTIONARY_BV_01

So the shortening code is firing on the actual join condition, but it isnt working correctly on the actual aliasing of the table name.

I'm actually away for the next 3 working days, so dont have time to get this raised up as a bug, but if you can't reproduce (should be pretty easy) i'll work on an example for you late next week.

wgorman
08-31-2009, 01:45 PM
I've reproduced this with the unit tests, I hope to have a fix in soon. You can track the bug at PMD-576 (http://jira.pentaho.com/browse/PMD-576)

codek
09-03-2009, 03:21 AM
many thanks. Once it's fixed i'll download the CI build and re-test.

wgorman
09-03-2009, 09:25 AM
Hi Dan,

I've checked in the fix. Let me know if you still have problems in the latest CI builds.

Thanks,

Will

codek
09-03-2009, 10:21 AM
I'm afraid the latest BIServer CI CE build doesnt work at all as it doesnt even start: (will post this elsewhere too.. )

15:20:58,485 ERROR [Digester] End event threw exception
java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor20.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.tomcat.util.IntrospectionUtils.callMethodN(IntrospectionUtils.java:930)
at org.apache.tomcat.util.digester.CallMethodRule.end(CallMethodRule.java:579)
at org.apache.tomcat.util.digester.Rule.end(Rule.java:229)
at org.apache.tomcat.util.digester.Digester.endElement(Digester.java:1058)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.endNamespaceScope(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.handleEndElement(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.endElement(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unk
nown Source)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.tomcat.util.digester.Digester.parse(Digester.java:1562)
at org.apache.catalina.startup.ContextConfig.applicationWebConfig(ContextConfig.java:352)
at org.apache.catalina.startup.ContextConfig.start(ContextConfig.java:1044)
at org.apache.catalina.startup.ContextConfig.lifecycleEvent(ContextConfig.java:261)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4148)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:926)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:889)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:492)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:448)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
Caused by: java.lang.IllegalArgumentException: Servlet mapping specifies an unknown servlet name TestSuite
at org.apache.catalina.core.StandardContext.addServletMapping(StandardContext.java:2323)
at org.apache.catalina.core.StandardContext.addServletMapping(StandardContext.java:2303)
... 46 more

wgorman
09-03-2009, 10:30 AM
Sorry about that, CI isn't the most stable environment. That problem should be fixed shortly.

Will

codek
09-03-2009, 10:33 AM
no probs, thats expected from ci :)

codek
09-04-2009, 05:12 AM
Just to confirm the fix works, many thanks, if i find time today i'll test our other metadata models. Brilliant work!

codek
09-04-2009, 06:20 AM
Hi Will,

I think that fix (maybe) has broken something quite severrely now.

In another model it is now generating this sql:

SELECT DISTINCT
BT_BV_SAFETY_ALERTS_BV_SAFET01.IS_OFFLINE AS COL0
,BT_BV_SAFETY_ALERTS_BV_SAFET01.PRODUCT_CODE AS COL1
,BT_BV_SAFETY_ALERTS_BV_SAFET01.APP_SERVER_EVENT_DATE_UTC AS COL2
,BT_BV_PRODUCT_DICTIONARY_BV_01.PRODUCT_NAME AS COL3
,BT_BVH_PRODUCT_CODE_TYPE_BVH01.NAME AS COL4
FROM
BVH_PRODUCT_CODE_TYPE BT_BVH_PRODUCT_CODE_TYPE_BVH01
,BV_PRODUCT_DICTIONARY BT_BV_PRODUCT_DICTIONARY_BV_01
,BV_SAFETY_ALERTS BT_BV_SAFETY_ALERTS_BV_SAFET01
WHERE
( BT_BV_SAFETY_ALERTS_BV_SAFET01.PRODUCT_CODE_TYPE_ID = BT_BVH_PRODUCT_CODE_TYPE_BVH01.PRODUCT_CODE_TYPE_ID )
AND ( null )

Note that the second join between prod dict and safety alerts isnt there, instead it just says "null"

I think this may be because of my complex join, as that causes this error:
11:17:19,232 ERROR [SqlGenerator] SqlGenerator.ERROR_0017 - Failed to parse complex join null
org.pentaho.pms.core.exception.PentahoMetadataException: SqlOpenFormula.ERROR_0003 - No Formula String Provided
at org.pentaho.metadata.query.impl.sql.SqlOpenFormula.<init>(SqlOpenFormula.java:145)
at org.pentaho.metadata.query.impl.sql.SqlGenerator.getJoin(SqlGenerator.java:921)

I had to use a complex join because it's a multi part key...

Should these still work? I guess so..

I'll send my xmi file to a support case for you.

Thanks!
Dan

codek
09-07-2009, 04:59 AM
clearly the problem is that the references to the business table in the complex join, are not the "shortened" references.

Presumably in 1.7 it would also parse the complex join and shorten those too?

wgorman
09-16-2009, 09:40 PM
Hello,

I just checked in a modified unit test to Pentaho Metadata verifying the correct column aliasing and shortening behavior when using complex joins, so something else must be going wrong with your query. Make sure your complex join is being parsed correctly by libformula. If that fails, you should see error logs in the console.

Will

codek
10-07-2009, 05:38 AM
Finally, i can confirm this works now, when i get the complex join syntax right. I will now update my other models for 3.5. Roll on GA...