One crocodile’s learned opinion on extracting data from 3DExperience databases
Evolution is full of surprises. Did you know that cold-blooded animals once had a serious evolutionary advantage over their warm-blooded competitors?
Gorgonopsian was a super-predator who ruled the Earth long before the Jurassic era. This early warm-blooded ancestor to mammals had powerful jaws and large canines. But its numerous shortcomings made it ineligible for survival when the Permian catastrophe struck.
Who replaced Gorgonopsian? Proterosuchus – a predecessor of modern crocodiles who did not need as much food to survive thanks to its cold-blooded nature. Moreover, its slower metabolism allowed Proterosuchus to efficiently adapt to the hot and poisonous atmosphere of those times.
Which brings us to the question on how to efficiently extract large swaths of data from 3DExperience for reporting, external data processing, etc. With its rate of fire of approximately one object per second, the API is an unattractive option for both techies or business folks. The techies will have to deal with locks, deadlocks and overwhelming loads affecting regular users. The business folks will have to wait forever for their reports, hindering real time operations.
Naturally, this issue is not exclusive to 3DExperience. PLM systems are basically OLTP oriented, serving immediate small queries to large numbers of users. SMARTEAM, Permian-way primitive by modern standards due to its lack of a proper web solution, had exactly the same problem. To its advantage, its data model was extremely flat and easily understandable for anyone proficient with SQL. This permitted direct database techniques such as materialized views or even fast “create * as select” to generate huge reports and run analytics in a matter of minutes if not seconds.
A basic query to extract an object data from SMARTEAM database looked like this:
select * from SUPER_CLASS_TABLE, LEAF_CLASS_TABLE where SUPER_CLASS_TABLE = XXX and LEFT_CLASS.OBJECT_ID=LEAF_CLASS.OBJECT_ID
The above query formed a foundation for any further data extraction efforts, including analytic functions and advanced DWH style processing. Very simple and elegant, no locks or deadlocks.
3DExperience is certainly far more advanced in terms of its business-oriented features, but its architecture and data model sometimes make me long for the Permian era. To pre-build a large data-set for reporting or analytical purposes, one must start with something like this (about 2% of the overall code):
select distinct ro.LXTOID as oid,ro.LXTOLAT as vault_id,tnr.V3 as customer_name,tnr.V4 as customer_id from LXRO_223c1f9d ro join MXTNR tnr on ro.LXFROMLAT = tnr.LATID and ro.LXFROMID = tnr.ID and ro.LXTYPE = (select MXOID from MXRELTYPE where MXNAME = 'Project_Customer') and tnr.V2 in (select MXOBJ from dbo.MXDRVINFO where MXKIND = 1 and MXTYPE = ( select MXOID from dbo.MXBUSTYPE where MXNAME = 'Company')) )
The query is doing a join for several tables, each storing tens of millions or even hundreds of millions of records. It may easily run for hours, overloading the reporting engine and frustrating online users.
A much worse case is EBOM generation, something common to almost all 3DExperience implementations:
select ident.MXPHYSICALID as rel_physical_id , rt.MXNAME as relationship_def_ref , btf.MXNAME as from_type , tnrf.V3 as from_name , tnrf.V4 as from_revision , fident.MXPHYSICALID as from_physical_id , btt.MXNAME as to_type , tnrt.V3 as to_name , tnrt.V4 as to_revision , tident.MXPHYSICALID as to_physical_id , new_time( ro.LXCRDATE,'GMT','EST') as rel_created_date , new_time( ro.LXMODDATE,'GMT','EST') as rel_modified_date , a1.LXVAL as check_ref_des , a2.LXVAL as component_location , new_time( a3.LXVAL,'GMT','EST') as end_effectivity_date , a4.LXVAL as find_number , decode(a5.LXVAL,0,'F',null, null,'T') as has_manufacturing_substitute , decode( a6.LXVAL,'<>',d6.LXDESC,a6.LXVAL ) as notes , a7.LXVAL as plm_externalid , a8.LXVAL as quantity , decode( a9.LXVAL,'<>',d9.LXDESC,a9.LXVAL ) as reference_designator , a10.LXVAL as select_at_test , a11.LXVAL as xsource , new_time( a12.LXVAL,'GMT','EST') as start_effectivity_date , a13.LXVAL as unit_of_measure , a14.LXVAL as usage , a15.LXVAL as v_description , decode(a16.LXVAL,0,'F',null, null,'T') as isvpmvisible from mxeng.LXRO_a0348274 ro join mxeng.MXIDENT ident on ident.MXLATTICE = -1607171468 and ident.LXOID = ro.LXOID join mxeng.MXRELTYPE rt on rt.MXOID = ro.LXTYPE join mxeng.MXTNR tnrf on tnrf.ID = ro.LXFROMID and tnrf.LATID = ro.LXFROMLAT join mxeng.MXBUSTYPE btf on btf.MXOID = tnrf.V2 join mxeng.MXIDENT fident on fident.MXLATTICE = ro.LXFROMLAT and fident.LXOID = ro.LXFROMID join mxeng.MXTNR tnrt on tnrt.ID = ro.LXTOID and tnrt.LATID = ro.LXTOLAT join mxeng.MXBUSTYPE btt on btt.MXOID = tnrt.V2 join mxeng.MXIDENT tident on tident.MXLATTICE = ro.LXTOLAT and tident.LXOID = ro.LXTOID left join mxeng.LXSTRING_a0348274 a1 on a1.LXOID = ro.LXOID and a1.LXTYPE = 204535465 left join mxeng.LXSTRING_a0348274 a2 on a2.LXOID = ro.LXOID and a2.LXTYPE = -20206874 left join mxeng.LXDATE_a0348274 a3 on a3.LXOID = ro.LXOID and a3.LXTYPE = 216170697 left join mxeng.LXSTRING_a0348274 a4 on a4.LXOID = ro.LXOID and a4.LXTYPE = 283928490 left join mxeng.LXBOOL_a0348274 a5 on a5.LXOID = ro.LXOID and a5.LXTYPE = 773881041 left join mxeng.LXSTRING_a0348274 a6 on a6.LXOID = ro.LXOID and a6.LXTYPE = -186184238 left join mxeng.LXDESC_a0348274_MV d6 on d6.LXOID = ro.LXOID and d6.LXKIND = -186184238 left join mxeng.LXSTRING_a0348274 a7 on a7.LXOID = ro.LXOID and a7.LXTYPE = 1100103177 left join mxeng.LXREAL_a0348274 a8 on a8.LXOID = ro.LXOID and a8.LXTYPE = 366111927 left join mxeng.LXSTRING_a0348274 a9 on a9.LXOID = ro.LXOID and a9.LXTYPE = 1486232209 left join mxeng.LXDESC_a0348274_MV d9 on d9.LXOID = ro.LXOID and d9.LXKIND = 1486232209 left join mxeng.LXSTRING_a0348274 a10 on a10.LXOID = ro.LXOID and a10.LXTYPE = 554395630 left join mxeng.LXSTRING_a0348274 a11 on a11.LXOID = ro.LXOID and a11.LXTYPE = 102621115 left join mxeng.LXDATE_a0348274 a12 on a12.LXOID = ro.LXOID and a12.LXTYPE = 169647773 left join mxeng.LXSTRING_a0348274 a13 on a13.LXOID = ro.LXOID and a13.LXTYPE = 975595728 left join mxeng.LXSTRING_a0348274 a14 on a14.LXOID = ro.LXOID and a14.LXTYPE = 451824771 left join mxeng.LXSTRING_a0348274 a15 on a15.LXOID = ro.LXOID and a15.LXTYPE = 1100105676 left join mxeng.LXBOOL_a0348274 a16 on a16.LXOID = ro.LXOID and a16.LXTYPE = 1929432198 where ro.LXFROMLAT = -1607171468 and ro.LXTOLAT = -1607171468 and ro.LXTYPE in ( select MXOBJ from mxeng.MXDRVINFO where MXKIND = 1 and MXTYPE = ( select MXOID from mxeng.MXRELTYPE where MXNAME = 'EBOM')) );
While the above SQL query efficiently traverses a 3DExperience database, developing it takes a deep understanding of underlying object on relational data model. Such level of understanding comes from decades of experience working with 3DX databases at a fundamental level.
What often works well is a distributed architecture solution such as Oracle Active Data Guard. A separate Oracle instance can accept changes from the primary instance (protecting online users) and create necessary pre-processing structures like materialized views, while utilizing dedicated memory and various advanced features such as parallel processing. With that configuration, our team has been able to completely de-normalize a very large 3DExperience database and make it immediately ready for any further reporting or analytical processing – all in in less than a couple of hours.
Originally, Proterosuchus, hiding in the water, was an underdog compared to Gorgonopsian. But eventually Proterosuchus won the battle for survival, and we can still see its friendly descendants’ toothy smiles in Florida swamps and on National Geographic.
To ride 3DX into the future while not being overwhelmed by its (sometimes) heavy warm-blooded resource footprint, talk to the Senticore team and we will show you how to get an evolutionary advantage.