Welcome to the third part of my series on automatically
loading data from source systems into PBCS. I promised tips and tricks, so here
is another neat way of identifying what can often be a useful variable in your
data load, the process ID.
Firstly, what is the process ID? It’s a unique identifier
assigned to each FDMEE process that you execute. It’s easy to spot when you
check your process details window, but not quite so easy to identify when you're running an automated script.
Why is this number important, I hear you cry? Well, for
reasons best known to themselves, there are a few vital operations within FDMEE
whose outputs contain the current process ID. A couple of these are crucial –
exporting data from PBCS using FDMEE (check my colleague Jaz’s blog out for a full
guide coming soon!) and also producing reports in FDMEE as I’ve blogged about here.
I’ve already covered the second one, so what about when
you’re exporting data from PBCS using FDMEE. This is an awesome way to move
data between cubes when you need to do complex mapping, the inbuilt data maps
functionality doesn’t quite cut the mustard for anything more complex than “map
everything to No Entity”.
So, the output from exporting data from PBCS unfortunately
comes out in this format: Target_Application_ProcessID.dat.
An example would be Workflow_Cube_1393.dat using the process ID from above.
Now, if you wanted to automate pulling data from one cube to
another, you would need to be able to identify that filename. Thankfully, the
Target Application name is constant and so is the .dat extension, but you need
to identify the process ID. But how can we do this?
The answer is to reuse a cheap trick I’ve blogged about
before – FDMEE reports are always at the bottom of a listfiles extract. So, if we were to run a dummy FDMEE report directly after our export from PBCS
then it can logically be deduced that the process ID of our report will be one
greater than the process ID of our export. And we can find out the report
process ID using the instructions here.
So, the process would be:
- Run your FDMEE Export from PBCS rule (rundatarule)
- Immediately run a FDMEE dummy report (the report itself doesn’t matter, we just need the process ID) (runDMReport)
- Using my previous blog find the process ID and take off 1 (set /a processID =%ID% - 1 )
- Run your standard FDMEE load rule with the data file Target_Application_%ProcessID.dat% as the input file(rundatarule again)3)
And boom! You’ve
just loaded data from one cube to another, in a ridiculously roundabout way.
But, if you have the requirement to map several dimensions into one for
reporting, this is the most elegant way I can piece together to achieve it.
Here’s the setup in action – notice particularly the name of
the file being used for process 1326 – you don’t even have to download the data
file, just keep it saved in the cloud and point your rule at outbox/TestApp_%processID% in your
batch file to achieve the below.
Again, please check this fantastic blog from my
colleague to see the missing piece of the puzzle, the export from PBCS via
FDMEE (step 1324 in the screenshot
above).
Stay tuned to my blog for the final instalment on FDMEE next
week, setting up drill-through to Microsoft Dynamics CRM!
Cheers,
Mike