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)
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, this article gives you the missing piece of the puzzle, the export from PBCS via FDMEE (step 1324 in the screenshot above). Stay tuned for the final instalment on FDMEE next week, setting up drill-through to Microsoft Dynamics CRM!
Until next time,
PS. To keep up to date on all my latest blogs, subscribe on http://stay-ahead-in-the-cloud.blogspot.co.uk/.