1

Hi guys, Before I begin explaining my issue, I want to tell you that I know I am in shady territory here. Nevertheless, I hope one of the Mendix gurus can help me out here since it would help us gain in performance and maintainability of our code. Situation I have created an entity called "ReportingCube" which I plan to use for BI type management reporting. The entity has a big amount of columns because data will be stored in a de-normalized way. In other words: instead of having to join multiple tables and do multiple retrieves and joins of table, my goal is to be able to execute one query (or very few queries) to retrieve all the data I want and show the data in a grid or feed it to a chart widget. Furthermore, entity "ReportingCube" has two references: one to an entity called "ManagerFund" and one to an entity called "DateDimension". Complex data Over a dozen of the columns in "ReportingCube" are based on complex calculations. For example: rolling standard deviation of the past 20 weeks, skewness, kurtosis, volatility, and more of these statistical measures. Now I could build microflows to calculate these values. The problem is that the microflows would become very complex. And with the way how Mendix works with Retrieve and Change actions, the process of updating the data in "ReportingCube" would take a long time. The latter is not a huge problem, the complexity of the microflows bothers me a bit. Oralce PL/SQL to the rescue Because I have 15 years of experience in PL/SQL, I knew I could easily write a database package that could build the data for entity "ReportingCube" relatively quickly. Furthermore, the code would be less hard to maintain if I structure the code efficiently. So I decided to follow this path. I created a mechanism using a process table and a database trigger to trigger the update of "ReportingCube" from Mendix. The package then regenerates the data, fills the reference tables for the connections to entity "ManagerFund" and "DateDimension" and I am done. So far so good... Error when querying "ReportingCube" from Mendix You can understand how disappointed I was when Mendix generated an error when I created a microflow that queries entity "ReportingCube". Unfortunately it's an error I cannot debug and is generated by the Mendix core. I have checked the data in the tables. All the joins are working correctly. I also traced the "ConnectionBus_Retrieve" node and checked the generated queries. These are also working find and return data when I execute them in my PL/SQL IDE. Some extra information: to populate the "ID" attribute of entity "ReportingCube" in my PL/SQL I have created my own sequence for generating primary keys. I wanted to use a sequence from Mendix for this, but I could not figure out which one to use so I created one myself. I have included the stack trace below. Hopefully there is a way to fix this within Mendix or by adding some extra PL/SQL code to the database... Stack Trace com.mendix.core.CoreException: ld: java.lang.IllegalArgumentException: Entity id should be not zero at Performance.MgrRiskAndReturns_PrepareRow (Retrieve : 'Retrieve most recent row from ReportingCube for ManagerFund. Interval = Week') at Performance.MgrRiskAndReturns_PrepareRun.nested.0f2baf2a-5a5c-4aec-8d06-8c2b59e91a4f [1 of 1] (SubMicroflow : 'Prepare Data for Row') at Performance.MgrRiskAndReturns_PrepareRun (NestedLoopedMicroflow : '') at Performance.DeepLink_Manager_RiskReturnReport (SubMicroflow : 'Prepare Data for Report') at {"name":"DeepLink.ExecuteDeeplink","type":"JavaAction"} at DeepLink.DeepLinkHome (JavaAction : 'Call 'ExecuteDeeplink'') at General.MF_InitializeDeepLinkSysOwner (SubMicroflow : 'Call DeepLinkHome') Advanced stacktrace: at com.mendix.core.Core.execute(SourceFile:301) Caused by: ld: java.lang.IllegalArgumentException: Entity id should be not zero at Performance.MgrRiskAndReturns_PrepareRow (Retrieve : 'Retrieve most recent row from ReportingCube for ManagerFund. Interval = Week') at Performance.MgrRiskAndReturns_PrepareRun.nested.0f2baf2a-5a5c-4aec-8d06-8c2b59e91a4f [1 of 1] (SubMicroflow : 'Prepare Data for Row') at Performance.MgrRiskAndReturns_PrepareRun (NestedLoopedMicroflow : '') at Performance.DeepLink_Manager_RiskReturnReport (SubMicroflow : 'Prepare Data for Report') at {"name":"DeepLink.ExecuteDeeplink","type":"JavaAction"} at DeepLink.DeepLinkHome (End : '') at General.MF_InitializeDeepLinkSysOwner (ShowForm : 'Show SystemOrganisation View') Advanced stacktrace: at mq.a(SourceFile:187) Caused by: com.mendix.core.CoreRuntimeException: java.lang.IllegalArgumentException: Entity id should be not zero at hA.b(SourceFile:204) Caused by: java.lang.IllegalArgumentException: Entity id should be not zero at ih.a(SourceFile:1494) at ie.b(SourceFile:99) at ie.<init>(SourceFile:89) at com.mendix.core.Core.createMendixIdentifier(SourceFile:1907) at p.a(SourceFile:106) at r.a(SourceFile:77) at q.a(SourceFile) at aU.f(SourceFile:456) at dJ.a(SourceFile:119) at dA.a(SourceFile:89) at dE.a(SourceFile:68) at x.a(SourceFile:43) at h.a(SourceFile:269) at hs.b(SourceFile:126) at hs.a(SourceFile:67) at ho.a(SourceFile:61) at ho.executeAction(SourceFile:21) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.retrieveXPathQuery(SourceFile:1044) at lL.b(SourceFile:122) at lL.a(SourceFile:84) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.executeSync(SourceFile:196) at ll.a(SourceFile:60) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.executeSync(SourceFile:196) at lP.a(SourceFile:60) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.executeSync(SourceFile:196) at ll.a(SourceFile:60) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.execute(SourceFile:297) at com.mendix.core.Core.execute(SourceFile:273) at deeplink.actions.ExecuteDeeplink.executeAction(ExecuteDeeplink.java:77) at deeplink.actions.ExecuteDeeplink.executeAction(ExecuteDeeplink.java:25) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.execute(SourceFile:219) at lk.a(SourceFile:69) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.executeSync(SourceFile:196) at ll.a(SourceFile:60) at mq.a(SourceFile:72) at mp.a(SourceFile:151) at mp.executeAction(SourceFile:98) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:57) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:457) at hA.b(SourceFile:193) at com.mendix.core.Core.execute(SourceFile:219) at gI.a(SourceFile:77) at gI.execute(SourceFile:55) at ja.a(SourceFile:319) at com.mendix.externalinterface.connector.RequestDispatching$Worker.a(SourceFile:157) at com.mendix.externalinterface.connector.RequestDispatching$Worker$a.a(SourceFile:148) at com.mendix.externalinterface.connector.RequestDispatching$Worker$a.apply(SourceFile:147) at akka.actor.Actor$class.apply(Actor.scala:545) at com.mendix.externalinterface.connector.RequestDispatching$Worker.apply(SourceFile:143) at akka.actor.LocalActorRef.invoke(ActorRef.scala:910) at akka.dispatch.MessageInvocation.invoke(MessageHandling.scala:25) at akka.dispatch.ExecutableMailbox$class.processMailbox(ExecutorBasedEventDrivenDispatcher.scala:223) at akka.dispatch.ExecutorBasedEventDrivenDispatcher$$anon$4.processMailbox(ExecutorBasedEventDrivenDispatcher.scala:123) at akka.dispatch.ExecutableMailbox$class.run(ExecutorBasedEventDrivenDispatcher.scala:195) at akka.dispatch.ExecutorBasedEventDrivenDispatcher$$anon$4.run(ExecutorBasedEventDrivenDispatcher.scala:123) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) at akka.dispatch.MonitorableThread.run(ThreadPoolBuilder.scala:192)

asked 2013-03-22

1 answers