Mass Upload Excel Transaction Data Via Analytic cloud using Custom Widget

[kkaushik]

Hello Expert,

I am trying to upload Mass Upload Excel Transaction Data Via Analytic cloud using Custom Widget. I about to know there is an Custom Widget "Upload XLS" which can be used to achieve the functionality. But at some how I am not able to find the .JSON file over Public Repository which can be used to upload and use further.

Can anyone if have provide the same or let me know which is the best place to search the Widgets which can be used to load and use.

Or If you can suggest any other option to upload Mass Transaction data it will be helpful. FYI I tried Copy/Paste but it is not helpful.

[Rajkosnam]

Hi Kamal,

please refer below blog https://blogs.sap.com/2020/09/11/populate-master-data-in-sac-planning-model-from-an-excel-file-uploa...

thanks,

Raj

[kkaushik]

Thanks for response but this is for Master data for Generic Dimensions. I am looking for Transaction upload.

One more blog also there but I do not found Custom widget for the same.

[SJohnson]

This blog here explains how to use the custom widget to upload Transaction Data: How To Upload Data to a Public or Private Planning Version Using a File Upload Custom Widget Within ...

In the blog it points to the Github repository to download the files: analytics-cloud-custom-widget/Custom-Widget-Samples/file-upload-widget at main · SAP-samples/analyti...

If you're not interested in modifying the custom widget and want to run it as-is then you can download the JSON file and ZIP file from the versions subfolder on the Github repository, found here: https://github.com/SAP-samples/analytics-cloud-custom-widget/tree/bd302ac7e2db84d017d77e4a9495ce6a2a...

Regards,
Sean

[SJohnson]

Hi Kamal,
I wrote the original custom widget that you are referring to. In V1 the widget used the Table API within SAC to upload the data. This had some limitations as it would only really allow around 5k or less records to be uploaded due to performance issues. I have since updated this widget to use the new Data Import Services (DIS) API. The implementation of the custom widget within SAC is much easier now (e.g. it doesn't require a hidden table or manually manipulating table filters, etc.) and can handle very large data volumes (I tested with around 800k records). The only "down-side" of this new custom widget is that it only works with Public versions and it will always Publish the data after it has been run. Support for Private versions will be coming in another month or so, but for now we need to write to a Public version. Additionally, data must be entered at the leaf level of each dimension. With V1 as we were using the Table API we could submit data at a parent node and the default spreading logic would be applied. With the DIS this top-level data entry is not allowed.

Based on these requirements, I will let you decide which version of the custom widget that you want to try:

V2 material can be found here: Upload XLS V2

V1 material can be found here: Upload XLS V1

Both links point to shared folders which include:

• How-to guide
• Video demonstration
• Required files (JSON, JS and/or ZIP file)

I should note that the SAC Development team will be delivering an updated version of Upload XLS V2 custom widget with the November 2023 update so if you can wait until then, or if anyone else is reading this post after November 2023, then I would suggest using that version instead of the one I have built.

Regards,
Sean

[JefB]

Hi Sean,

Finally a performant/scalable solution for file upload leveraging the new Data import service API.

I really thought an acceptable solution was still going to be >1y away based on this roadmap item.
It would have been worthwile for your solution to be published in a blog post for the general community, as this approach beats all other current workaround blogs out there.

Thank you.

[JefB]

Hi sean.johnson

I just realized that this approach requires a full Professional Planning License, as indicated by SAP note https://me.sap.com/notes/3380048

Which makes this approach not useful anymore for the vast majority of planning (standard license) end users.

Can you confirm this?

[SJohnson]

Neither approaches will require a Professional license to execute, but the V2 approach may require a Professional license to generate the OAuth client ID and secret. However, the OAuth client ID and secret are only required if you choose not to re-use the auth token that SAC itself is using.

So your options, and licensing requirements are:

1) V1 approach requires minimum SACP Standard license to execute.

2) V2 re-using SAC login token (default approach) requires SACP standard license to execute

3) V2 using OAuth client ID and secret:

- may require SACP Professional license to create the OAuth client ID and secret. I need to do some testing to confirm this.

- requires SAC BI license to execute custom widget...and no SAC license if run outside of SAC.

Regards,

Sean

*** Update 10/12/23 ***

I have tested using the API with SSO (ie. re-using the SAC logon token) and a SAC-P Standard license and it worked without any issues. As long as the user is able to submit data in the traditional method (e.g. via a Table) then the API approach will work as well

[hugh_gledhill]

Hi sean.johnson ,

Does Upload XLS v2 support Classic Account models? In the instructions you mention them when talking about mapping measures, but the data import API documentation suggests that Classic Account models are not supported.

Thanks,

Hugh Gledhill

[SJohnson]

Hi Hugh,
I asked the Dev team about that comment as well and they explained that it was due to the fact that the Classic Account model does not expose their underlying Measure which is required for the API to work. However, they were the ones that informed me that as long as you include the column SignedData in your XLS/CSV file OR add it as a map it to the column that actually contains the value (as I explain in the How-To PDF) then it will work the same as the Measure-based model.

Regards,
Sean