We are evaluating, together with some customers, the possibility of migrating their on-premise versions of EPM to cloud.
For the Application part, including the porting of the OLAP database and the replacement of ImportMaster, there shouldn’t be any insurmountable problems.
What we are struggling to reconfigure, however, is the entire data staging part, understood here in a general terms as data quality, data preparation, and relational reporting.
An example can clarify better than many words what we need. In one project, we use Application Studio to pass parameters to a stored procedure that explode the BOM and then we use it to display the results (see below the sql code).
Unfortunately, several SQL predicates currently are not available in Compass (specifically, a recursive CTE table is missing) and this is bloking us.
We know that with standard cloud licenses, three environments -PRD, TRN, and TST— are provided to cover the needs of development, quality, and production, but in this case, they all share the same problem.
What to switch one tenant with a local on premise installation only for development? Is it this technically and contractually possible?
Thank in advance.
Alberto
with CTE as (
SELECT 1 as levels
,ltrim(b.mitm) as Parent_item
, ltrim(b.sitm) as Child_Item
,a.dsca as Articolo_Figlio_Descrizione
,b.qana as quantità
,a.cuni As UM
, CASE WHEN a.kitm=1 THEN 'Acquisto' ELSE 'Prodotto' END as Tipo_Articolo
, CASE WHEN a.subc=1 THEN 'SI' ELSE '' END as Conto_lavoro
, CASE WHEN b.cpha=1 THEN 'SI' ELSE '' END as Fantasma
from tibom010 as b
inner join tcibd001 as a
on ltrim(a.item) = ltrim(b.sitm)
where ltrim(b.mitm) like 'A0840157-AA11AA11AB'
union all
select levels+1
,ltrim(p.mitm) as Parent_item
, ltrim(p.sitm) as Child_Item
,a.dsca as Articolo_Figlio_Descrizione
,p.qana as quantità,a.cuni As UM
, CASE WHEN a.kitm=1 THEN 'Acquisto' ELSE 'Prodotto' END as Tipo_Articolo
, CASE WHEN a.subc=1 then 'SI' else '' end as Conto_lavoro
, CASE WHEN p.cpha=1 THEN 'SI' ELSE '' END as Fantasma
from tibom010 as p
inner join tcibd001 as a
on ltrim(a.item) = ltrim(p.sitm)
inner join CTE
on ltrim(p.mitm) = ltrim(cte.Child_Item)
)
select *from CTE
order by 1,2
OPTION(MAXRECURSION 10000)
