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.