CSV files in a server through Bex Query

[ravikiran_sap]

Hi BW Experts,

we have requirement to generate Bex Query output to csv files in a remote folder. Bex query has 61 fields in rows including nav attributes all are mandatory for user . Query output in CYTD (Current year YTD). everymonth user run it and get csv file in that location. How can we achieve this in bw including automating the same flow like process chain etc.?

APD i tried but getting short dumps due to memory issues and it has a limit of 60 fields. If APD is good option we can ask user to cut few fields to reduce 60 field limit.

please advice.

Regards,

RK

[vishalakshmi]

Alternative to APD is Query Designer Export Option, You can use the export functionality within the BEx Query Designer itself. While this may not automate the process entirely, it allows users to export the query results to a CSV file manually.

Consider writing a custom ABAP program that executes the BEx query and generates the CSV file. ABAP programs can handle large datasets and allow you to control memory usage more effectively. This program can be scheduled as a background job. Use a process type or custom ABAP program to move the CSV file to the remote folder. You can use a standard SAP function module like FTP_COMMAND or ARCHIV_CONNECTION_INSERT to transfer the file to a remote location.

Yes, alternatively you can connect to powerBI and You can export the visualizations or tables from your Power BI report to CSV files as needed. Power BI provides export options that include CSV format.

you can use Power BI's built-in scheduling and automation features. You can schedule report generation and export as part of a Power BI dataset refresh.

Using Power BI in conjunction with SAP BW can be a powerful solution for automated data extraction, transformation, and reporting, especially if you are experiencing memory issues with other methods.

Thanks,

Lakshmi.

[vishalakshmi]

Hello Ravi,

• Set up a Data Source and Transformation in SAP BW to extract data from the BEx Query. Ensure that all the 61 required fields, including navigation attributes, are included in the Data Source.
• Create a Data Transfer Process to transfer data from the Data Source to a Data Target (e.g., a DataStore Object or an InfoCube).
• In the DTP, you can perform any necessary data transformations or calculations required to get the data in the desired format for your CSV output.
• Create a Process Chain in SAP BW to automate the entire process. The Process Chain should consist of the following steps:

1. Start Process: Trigger the chain at a scheduled time (e.g., monthly).
2. Data Transfer Process: Execute the DTP to load data from the Data Source into the Data Target.
3. Export to CSV: After data is loaded into the Data Target, use the "Export Data to CSV" step in the Process Chain to export the data to a CSV file. This step allows you to specify the location where the CSV file should be saved.

• Schedule the Process Chain to run automatically at the desired frequency (e.g., monthly) using the SAP BW job scheduling functionality.
• If you still want to explore the use of APD (Analysis Process Designer), you can try reducing the number of fields in your APD to fit within the 60-field limit. However, APD may not be the best fit if you need all 61 fields.
• To address memory issues in APD, you can consider optimizing your SAP BW system's memory settings, such as increasing the available memory, if possible. Additionally, ensure that you are using the latest version of SAP BW, as newer versions often include performance improvements.
• Engage with your users to determine if any of the 61 fields can be categorized as optional or if there are any fields that can be aggregated or summarized to reduce the field count.

you can automate the process of generating CSV files from your BEx Query output in SAP BW while ensuring all the required fields are included

Thanks,

Lakshmi.

[ravikiran_sap]

HI Lakshmi,

thanks a lot for your valuable time and solutions. we have another query with 40 fields in rows. Still getting memory issues. Will exclude APD option and try the query as Datasource . we have this option as plan B actually. apart from these did you see any other solutions such as Connecting this bex query to power bi and schedule it there ?

[ravikiran_sap]

actually this requirement is not feasible in BW or Power BI. because we have nav attributes and normal charecteristics in same row pane. even or one month filter it is taking forever to generate output in Analysis for office.

[vishalakshmi]

Please up-vote if my answer is helpful.

Thanks,

Lakshmi.

[ravikiran_sap]

done.......