Using AEP Queries (from CJA Data Set) to replace AA's Data Warehouse

[Ben_Step]

We are migrating to CJA from AA, and we have a number of data extracts that are sent to various other teams from Data Warehouse.  I want to replicate these extracts using AEP scheduled queries. But I'm coming up against a few tricky bits...

1. Is this the right tool to do this? Or should I just use the Connections > Destinations and send directly from the data set to an S3 bucket? It feels like the manipulations available in the query SQL is going to be pretty useful...

2. I can't get the values from CJA derived fields via the query, presumably becuase the derived fields are calculated at report time and so not saved back to the data set.  Has anyone come up with an elegant solution for things like Marketing Channels that are complicated derived fields that I'd rather not rebuild in SQL?

3. In Data Warehouse I can get the Adobe Visitor ID.  I'm trying to pull the identityMap.ECID.id value from the data set but it is always blank, even though CJA is reporting it as always populated. Is this another situation like the derived fields, where CJA is looking this up from somewhere else?  Is there a way to get a visitor ID so that I can use the sess_timeout function on a visitor id field? Or do I need to manually pass the ECID as part of the XDM schema?

Thanks!

[Harveer_SinghGi1 · Accepted solution]

Hi @Ben_Step , here are my thoughts,

1. Connecting AEP dataset to the destination will give you the hit level raw data but to replicate DWH which is processed/semi-processed data you should access CJA data which is available through query service

2. I haven't tried or figured out a way of fetching derived fields data in query service

3. You need to use the field as identityMap['ECID'][0].id to fetch the ECID from identityMap in query service

Cheers!