We are using an extender business rule to run FdxExecuteDataUnit and write the data (with dimensionality) to a file. The file generated seems to be a standard format with a column for each dimension. Does anyone have any ideas on how to include the Member IDs in the file export?
Thank you,
Denise
I often use a CustomCalculate Package to export or extract larger amounts of Cube data, as it gives me far greater precision as to what I output and how. Run the CustomCalculate DM Package for the desired Data Units. For each Data Unit, fetch a DataBuffer of the Cube data to be exported, then cycle through the DataBuffer outputting whatever fields you like and in whatever format you want. If you need to export data from all the data units into a common data file or data table, you will want to output each Data Unit's data to a common Global variable with locking or bulk insert to a common local SQL table so that you don't have multiple parallel threads headbutting. Since you are invoking this from an Extender rule, you would have the Extender rule grab the final Global variable or SQL table and output it as desired. But you could just as easily launch all this from a Dashboard Data Adapter, or whatever you want.
rhankey do you have any sample code you could include here? I have business rules today that use data buffers but they are always for performing calcs and not using them to export data to file. (I edited this reply to take out the question about the data unit. I forgot, if I call the BR through a DM Custom Calc then I can define the data unit there).
I am not an IT person. My skills are limited to taking what we have (and was built by integration partner), reusing code and extending it :)
Thank you
Denise
Its strange that OS with their love of Guids, PKs, and Ids would only extract string names in FDX. I suspect its not a big ask for a feature to optionally include ID columns in the FDX library functions.
If you can wait a few more moments while your FDX is running, you could parse the result using a multi-threaded approach in an assembly or BR to add ID columns.
What is the use case for having the IDs in the resulting file? It could be that its better to derive IDs on consumption of the file instead of during construction. Just depends on where performance matters most.
RobbSalzmann the specific use case here is exporting data from OneStream to stage in ACDP and use in Power BI. Because Fdx only extracts stored data there is a need to apply the OS hierarchy to the data (which we already have an extract of). We felt the best way to marry the data with the hierarchy was through the member IDs in case anything would ever be renamed. We can not use the new Power BI connector because we are not Saas and our organization does not want to make the investment needed to get the connector. I figured the Fdx would be the quickest way to get the data. Additionally, it is easy to use the function to define the data needed. I am not great with buffers and writing code but I can probably piece something together by copying from existing rules we have.
I appreciate your feedback and will consider above. Feel free to share any other ideas you may have.
Thank you
Denise
Hi Denise
You can get a dictionary of any members you need to capture e g key = Name, value = MemberId. Then create a new DataTable and add additional columns as required
Then you can loop through your fdx DataTable and use the dictionary to reference the Member Ids so that you can derive the datarow values to pass into your new column e.g MemberID in your new DataTable
Using a dictionary shouldn't really impact performance from this extra step in your code. I have used this approach before to derive extra columns from fdx data
Hope this helps
Sam
would you have sample code of a business rule that would execute this sequence of steps or do you know of one in GolfStream?
Thank you
Denise
