Create a cube sourcing a MS-SQL linked class/table
Hello all,
I'm trying to build a cube based on a linked table but seems that IRIS is not able to do it :O
Long story short, I have a linked table in IRIS that sources a Microsoft SQL table (using standard linked feature from the portal). It works fine, I can access it using SQL as many other times. On top of that, I've created in DeepSee (ok, Analytics) a cube that uses this class as source. It compiles correctly, no errors given. When I build it with 100 records, all goes well and using Analyzer I can see results.
Existing cube deleted. Fact table built: 1,000 fact(s) (Max number of worker(s) used at a time: 1) Fact indices built: 1,000 fact(s) (1 worker(s) used) Complete Elapsed time: 1.788246s Source expression time: 0.000856s
The problem comes when I try to build with more than 1000 records, I get this weird error:
ERREUR #5540: SQLCODE : Message 400 : Process 11156 failed to compile Cached Query Class %sqlcq.USER.cls151 with these errors: ERREUR #5002: Erreur ObjectScript: <MAXSTRING>CompileRtns+282^%occRoutine > ERREUR #5030: Une erreur s'est produite lors de la compilation de la classe %sqlcq.USER.cls151 ERREUR #5002: Erreur ObjectScript: <NOROUTINE>DescribePhase2+9^%occSysDescriptor *%sqlcq.USER.cls151.1 SQLTEXT: SELECT %ID,BLNumber "DxBLNumber",BookingNumber "DxBookingNumber",CargoCycleId "MxCargoCycleIdN",EMECargoCycleNumber "DxEMECargoCycleNumber",EmptyFull ...
For what I can see, somehow the system is trying to build a select that is throwing MAXSTRING error. Weird.
So, I tried to other way round: I've imported all the records into a class in IRIS with the exact same properties and records. And it works! :-O Building the cube with millions of records goes smoothly.
At this point, I guess something is preventing the cube to write on temporary globals when using the cube sourcing the linked database. Or that maybe this is simply not possible. Anyone has gone into same situation and has a trick or solution for this?
Thanks for reading me :)
Comments
Hello David, to use linked table you need to create a data connector:
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…