How To Upload Data to a Public or Private Planning...

AmandaJMurphy · ‎11-03-2023

Purpose

This blog outlines how customers or partners can use the File Upload Custom Widget to call our new Data Import Service API to upload fact data from an MS Excel Worksheet or CSV file to a Public Version or to their own Private Version within a Story that has Optimized Story Experience enabled.

The sample code may be downloaded and customized further to meet specific customer needs.

Check out our short video containing the File Upload Custom Widget highlights.

Prerequisites

The File Upload Custom Widget may be installed on a SAP Analytics Cloud, Cloud Foundry environment.
SAP Analytics Cloud revision 2023 Q4 or higher.
The new model type that supports measures and accounts.
The story must have Optimized Story Experience enabled.
One widget per page may be used.
Widget should be installed separately on each tenant.
Importing to dimensions that contain hierarchies is not currently supported.
Upload of Fact Data is supported.

Overview

The steps to upload fact data from an MS Excel Worksheet, or CSV file, to a Public Version or to a user owned Private Version / Public Edit Version can be broken down into the following 8 easy to follow steps:

Prepare the source data.

Assign authorizations.
Install the File Upload Custom Widget.
Add the File Upload Custom Widget to an Optimized Design Experience Story.
Create an explicit Private Version (optional).
Configure the File Upload Custom Widget.
Run the import data job.
Review the results.

Let’s take a closer look at each step in greater detail.

1. Prepare the source data.

The MS Excel Worksheet or CSV file should contain one column for each dimension and measure in the model. Each column should have a name in the header, that can be later used for mapping.

In the following example, the first 5 columns in this worksheet represent dimensions that exist within a Planning model in SAP Analytics Cloud. Column 6 onwards, are pivot columns as they consist of fact data, including dates.

Figure 1: Sample Excel Worksheet Extract

It is helpful to assign a name to the worksheet and to also make note of the pivot column number that contains your fact data, as this information will be used later in the Job Settings configuration section of the File Upload properties panel.

In this example, column 6 is the starting pivot column number containing the fact data.

2. Assign authorizations.

Supported models are Planning and Analytic models that contain both measures and accounts.

There are 3 personas that are used for this process.

The Administrator who installs the custom widget.
The Story Designer who creates the story and configures the widget. This user requires design rights for the story or application.
The BI or Planner End-User requires READ and MAINTAIN rights on the model.

Figure 2: Read and Maintain Access Rights on the Model

3. Install the File Upload Custom Widget.

Download the File Upload Custom Widget json and zip files from the file-upload-widget versions folder in GitHub. Ensure to review the README.md prior to installation.

In SAP Analytics Cloud, select Stories and choose the Custom Widgets tab. Press + to open the Upload File dialog.

Figure 3: Add Custom Widgets

Choose File Upload Widget json and zip file within the Upload File dialog and select OK.

Figure 4: Upload File Dialog

Your File Upload Widget should be ready for use within an Optimized Design Experience Story.

4. **Add the File Upload Custom Widget to an** Optimized Design Experience Story.

Create or open an Optimized Design Experience Story.

Under the View section of the Toolbar, select the Left-Side Panel icon to open the Assets Panel.

Expand on Custom Widgets in the panel and select the File Upload Widget v*.*.*.

Drag the widget onto the workspace.

Expand the width of the widget to leave sufficient space to view job status information.

Figure 5: Add File Upload Widget to Workspace

5. Create an explicit Private Version. (Optional)

This step is optional for Planners who wish to upload fact data to their own Private Version. As an alternative, an existing Public Version may be used instead.

Within the Optimized Design Experience Story create an explicit Private Version.

See Creating Blank Public or Private Versions in SAP Analytics Cloud Help for details on Version Management.

6. Configure the File Upload Custom Widget.

The next stage is to configure the File Upload Custom Widget, in advance of running the data import job.

Select the widget on the dashboard, to highlight it.

Under the View section of the Toolbar, select the Right-Side Panel icon to open the File Upload builder panel.

Under Model Selection choose the name of the source model where the fact data shall be uploaded to.

Under Import Type, there are 2 Import Type options to choose from:

A. Fact Data.

Choose Fact Data in the drop down for upload of data to Public Versions.

B. Private Fact Data.

Choose Private Fact Data in the drop down for upload of data to Private Versions. Note that if the Story Designer has chosen “Private Fact Data” and the end user selects a Public Version in the upload, then the data will be written to the Public Edit Version.

Figure 6: Private Fact Data Import Type

Select Open Data Mappings Dialog from Data Mappings and either enter the column names from the file directly or choose Upload Template within Data Mappings dialog.

Choose the source worksheet name from the drop-down list.

Automatic mapping of columns should appear in the dialog. Carefully review these mappings and configure further mappings to the corresponding worksheet columns, as required.

Click Save when mappings have been successfully made.

Figure 7: Data Mappings Dialog Containing Worksheet Name

Select Default Values for further optional configuration of default values. The default value will be used for any measures or dimensions that have not been manually mapped or if the uploaded file does not contain a value for the measure or dimension.

When the version is specified as a default value, or if it is mapped, this version is displayed automatically as an option to users when running the upload job. If the version is not mapped in the file and if no default value is set, then the end user can select the version during the upload.

Next, select Job Settings.

Choosing the Reverse Account Sign checkbox reverses the sign of all fact data imported into models containing an Account dimension with an account type of Income (INC) or Expense (EXP). This option is available for Fact Data only.

Choosing the Execute With Failed Rows option writes only correct records to the specified version. Leaving this option unchecked means that fact data will be uploaded, only when all records pass correctness checks.

Choosing Ignore Additional Columns ignores columns in the file that are not required during the import process.

Choosing the Enable Pivot Settings option expands the pivot settings. This is a similar concept to using Pivot Tables in MS Excel.

Enter the Pivot Column Start number as the pivot column of the worksheet fact data, the Pivot Key and Pivot Value.

For example, in the below screenshot, Pivot Column Start is 6, as this is the first column containing fact data. Pivot Key is Date and Pivot Value is Cost.

Figure 8: Job Settings Sample Configuration

The Enable Date Format Settings allows custom date formatting to be applied to the fact data. If unchecked and no value is specified, then the date format in the source file must match the date format in the SAP Analytics Cloud model. For example, if the ‘Date’ dimension in the model is in the format ‘YYYY-MM’, then the date format of the source data must also be ‘YYYY-MM’.

Checking the Enable Date Format Settings box provides additional date flexibility, by allowing the administrator to specify the date type format in the source file, which will be converted to the date dimension configuration of the SAP Analytics Cloud model. This option is available for Fact Data only.

Figure 9: Custom Date Format Settings

7. Run the import data job.
Save the story and switch to Optimized View Mode.

The Upload Data button should contain a blue arrow to indicate that the widget is enabled.

Figure 10: Upload Data Widget Appearance in Story Optimized View Mode

Click the Upload Data action button and select the Source file.

Choose the preferred Public or Private Version from the Target Version drop-down, where the fact data shall be uploaded to, or see the default Version name that has been pre-selected in Default Values of the File Upload Panel, along with the source file and worksheet name.

Any errors found in the worksheet are displayed in the Upload Data dialog.

Figure 11: Upload Data Dialog Containing Sample Error Messages

In the above example, a different worksheet was selected, compared to what was previously mapped, resulting in errors.

Once errors are resolved with the source worksheet, choose Run to upload data to the specified version.

Figure 12: Upload Data Dialog Containing No Errors

Figure 13: Running Import Job

The File Upload widget provides an indication of a successful import of fact data.

Figure 14: Import Success Status

Select the information icon to view more details on the successful or failed import.

For failed records, a CSV file containing a rejection reason for each failed row of data, can be downloaded, and reviewed, to help resolve any errors found in the source file.

Figure 15: Download CSV Option for Failed Import Analysis

Troubleshooting Tips:

Expand the Jobs Timeline and select "Copy Invalid Rows URL". Paste into a new browser page to see failed record information.
A commonly experienced error is "Temporary storage does not contain valid records [1011]". In order to help diagnose this error further, a useful method is to switch the importing option from 'Fact Data' to 'Private Fact Data'. Some additional error messages is generally provided using this option through a download file containing a list of failed records and a corresponding failure explanation for each row.

8. Review the results.

Following a successful import into a Public or Private Version, it should now be possible to see the same fact data from the source worksheet within SAP Analytics Cloud.

Figure 16: Empty Private Version

Figure 17: Private Version Containing Uploaded Fact Data

It is now possible to analyse, compare and maintain this fact data within this Private Version as desired.

Conclusion

The File Upload Custom Widget solution provides an example of how a custom widget can be created and used to upload fact data into SAP Analytics Cloud. The sample code, found in the file-upload-widget GitHub folder, may be downloaded and customized further to meet specific customer needs. A standard solution within the product is planned. Follow the SAP Analytics Cloud Roadmap for details of when this solution will be made available in the product.

Useful Links

Marc-An_BERTHET · ‎11-03-2023

Thank you for this custom widget. It is greatly appreciated.

What kind of licence should I have in order to use this feature ? :

SAC BI

SAC Planning standard

or SAC Planning pro

Thanks,

AmandaJMurphy · ‎11-03-2023

Thank you for your question Marc-Antoine. A Standard Planning license should be sufficient for version management and the installation and configuration of the custom widget.

Regards,

Amanda

gerd_schoeffl · ‎11-03-2023

Hi Marc-Antoine,

Basically we have to distinguish between different personas - the person that installs the widget on the system, the person who creates the story, and the end user who uploads the data in the file upload.

The first two personas would typically be some admin users with corresponding privileges.

For the end user it depends on the underlying model.

If the end user wants to upload data into a Analytical Model then a BI user license will suffice.

If the end user wants to upload data into a Planning Model then at least a Planning Standard license is necessary.

Best regards

Gerd

Marc-An_BERTHET · ‎11-03-2023

Thank you for your replies Amanda and Gerd ! Very clear

mholiveira · ‎11-06-2023

Hello,

Would it be possible to have a quick explanation of what means this error:

"Failed during validation for job [2901-2608]".

It appears when I try to run the button. On the console, I can see this:

Failed to load resource: the server responded with a status of 500 (Internal Server Error)

When I click Copy JSON stats (from Designer mode), I get this:

{"jobStatus":"READY_FOR_VALIDATION","jobStatusDescription":"Job f611ad37-6d81-4a50-ae7f-82dc9dc41b66 for modelID Cmt00s1f6e806ji3jc6gku5it2u has been created successfully and validated. Data has been pushed to temporary storage. Push more data or run job now.","importType":"factData","runJobURL":"https://XXX-q.eu10.hcs.cloud.sap/api/v1/dataimport/jobs/f611ad37-6d81-4a50-ae7f-82dc9dc41b66/run","jobPropertiesURL":"https://XXX-q.eu10.hcs.cloud.sap/api/v1/dataimport/jobs/f611ad37-6d81-4a50-ae7f-82dc9dc41b66","additionalInformation":{"totalNumberRowsInJob":1}}

Thanks,

Miguel

AmandaJMurphy · ‎11-06-2023

Hi Miguel,

I would suggest reviewing the contents of the source file, to ensure your data is correct and then retry the operation. Internal Server Errors often indicate browser communication issues that may now be resolved for you.

Best regards,

Amanda

ihar_stankevich · ‎11-06-2023

amanda.murphy Great job! Really needed functionality and nice example of using Custom Widgets.

My tests show me that functionality:

Append works the same as Update
Reverse Account Sign is not working

Job_Settings

Could you check from your side? Thanks.

Update: attach full settings for this case

ihar_stankevich · ‎11-07-2023

Update. Resolved. Everything is ok.

Note: when you change something in the settings of the widget in runtime is needed hard refresh: Ctrl+Shift+R. Simple refresh Ctrl+R is not working

saurabh_03 · ‎11-07-2023

amanda.murphy I tried to download custom widget from github and i get error when I try to upload in SAC teant.

Error

Can you check the widget once if it is working?

Regards,

Saurabh.

gerd_schoeffl · ‎11-07-2023

Hi Saurabh,

Could you please delete the downloaded json file, down load it again and try once more? I just tested the upload again and for me the latest version of the widget (from github) does not send any errors...

Many thanks and best regards

Gerd

saurabh_03 · ‎11-07-2023

Hi Gerd,

I followed the steps you mentioned but still got the same error. Can you direct me to correct file?

Also I am using save link to save the file. Is it the correct way?

Regards,

Saurabh.

gerd_schoeffl · ‎11-07-2023

Hi Saurabh,

Start from here: github, click on 'versions'. You will find the .json file and a .zip file. I clicked on the .json file (you will see the content of the file) and on the upper right corner there is a button 'Download raw file'. I used that one.

Then you do the same with the .zip file and upload both to the system.

Best regards

Gerd

saurabh_03 · ‎11-08-2023

Hi Gerd,

I dont see an option to upload zip file but only json file.

gerd_schoeffl · ‎11-08-2023

Hi Saurabh,

I am not sure how you are trying to upload the custom widget but you definitely need the .json file AND the .zip file. Can you please have a look at the following blog?

Hosting and Uploading Custom Widgets Resource Files into SAP Analytics Cloud

It explains how custom widgets are used and there is a video guide on how to upload the widget files. Could you please check again?

Many thanks and best regards,

Gerd

jesgoncalves · ‎11-09-2023

Hello Dear,

It is a great article and function. I really appreciate it.

It would be perfect to have a similar custom widget to upload master data in a massive way directly from the story. I hope it is in the roadmap.

Best Regards

Jessica

rabab_mansouri · ‎11-09-2023

Thanks a lot for presenting this feature, but unfortunately I have the same problem here, when importing a file I get the following error "Failed during validation for job [2901-2608]".

mholiveira · ‎11-09-2023

Hello,

I tried this on another tenant and it worked.

So my conclusion was that the widget is evoking an external API/library that is blocked on my client.

BR,

Miguel

gerd_schoeffl · ‎11-13-2023

Hello Jessica,

Currently we are not working on an update of the custom widget for master data. However, the Data Import API that our custom widget is using to write data into SAC also supports master data. As the coding of the custom widget is open, partners/customers can enhance it in such a way that it is also capable of loading master data into the system.

Best regards

Gerd

mflumian · ‎11-14-2023

Hello,

I tested this widget and SAC gives me this error back

"Failed to insert data to temporary data store [1205]".

We read on Help Portal that we should open an incident, but maybe there is another way to solve it.

Can you please help me?

Thanks, Marco

AmandaJMurphy · ‎11-14-2023

Hi Marco,

The error indicates that there are technical issues when attempting to post the source data to a staging area. Please inspect and correct your source data, ensure mappings are compatible to the model and retry the operation.

For example, this error can be generated if a decimal has been entered into the wrong place during import. Hope this helps.

Best regards,

Amanda

ihar_stankevich · ‎11-14-2023

Hi amanda.murphy currently custom widget provides no more than 2000 failed/rejected rows. If more - the system cuts them.

Is it possible to change this limitation?

gerd_schoeffl · ‎11-14-2023

Hi Ihar,

The idea of the download for the rejected records is to give you an basis to what might be wrong in general with the upload file. For performance reasons we limited the number or rejected records you can download to 2000. If necessary you could modify the widget in such a way that it downloads more rejected records than it does currently.

What you can also do is to make sure the first 2000 errors are corrected and then run the upload again to test whether there are further rejected records.

Best regards

Gerd

ihar_stankevich · ‎11-14-2023

Hi Gerd,

In the Append import method, it is not advisable to rectify and upload the entire file again. While 2000 may seem like a substantial number of rows, the file actually contains only 167 failed rows (=2000/12).

Could you suggest, where to change this limitation? I obviously didn't find it in .js file.

Again, I agree that the file needs to be prepared better with some validation rules inside. Just to have this option if needed 🙂

Thanks

ihar_stankevich · ‎11-15-2023

Hi Gerd and Amanda,

from today we started to get this error. The same for any selected model

The custom widget is not working in runtime.

result of this request in browser: https://tenant.hcs.cloud.sap/api/v1/dataimport/models/XXXXXXXXXXXXXXXXXX

is:

{"error":{"code":"1d9d96b6-6a1d-4b60-5b8d-b22df8a2446b","message":"Model cannot be retrieved [1701-1801-3303]"}}

in the same time: https://tenant.hcs.cloud.sap/api/v1/dataimport/models/  shows info about all models

gerd_schoeffl · ‎11-17-2023

Hello Ihar,

There was a bug in the Data Import Service that has been corrected and deployed yesterday evening. Could you please try again? The error should not appear anymore.

Best regards

Gerd

ihar_stankevich · ‎11-17-2023

Yep, now DIS is working properly, custom widget too 🙂

thanks

hrishikesh11 · ‎11-17-2023

Hello amanda.murphy ,

Sometimes I get the "Temporary storage does not contain valid records [1011]" error and sometimes "Failed to insert data to temporary data store [1205]" error.

Can you please help me with the same?

How the file should be prepared to avoid this error?

mflumian · ‎11-20-2023

Hi Amanda,

the problem was due to the wrong formatting of the decimal numbers.

Now the custom widget works great.

Thanks for the support.

Best regards, Marco

AmandaJMurphy · ‎11-20-2023

Great to hear, thank you Marco.

AmandaJMurphy · ‎11-20-2023

Hello Hrishikesh,

Can you check an earlier comment regarding this error?

A similar scenario was due to the wrong formatting of decimal numbers on the source.

Another sample scenario could be attempting to load non-decimal values into a measure that requires decimals.

Please inspect and correct your source data, ensure mappings are compatible with the model and retry the operation.

Hope this helps.

Regards,

Amanda

hugh_gledhill · ‎11-23-2023

Hi amanda.murphy / gerd.schoeffl

Is there any way to interact with the widget via optimized story script APIs? E.g. I would like to be able to set design time properties of the widget, such as default values, via script.

Thanks,

Hugh

gerd_schoeffl · ‎11-23-2023

Hello Hugh,

It is possibel to trigger the file upload via a script but it is not possible to set design time properties via a script.

In order to trigger the file upload via script you have to create an instance of the custom widget in the story - you can make the trigger invisible.

Then you can call the upload via

NameOfFileUploadWidget.open();

Best regards

Gerd

rambo_8143 · ‎11-24-2023

While trying to implement the Custom Widget Couldn't load Initially it states (kind: 'main') then check the .JSON file and .JS file, later I'm getting now as the error mentioned as (kind: 'builder'). Can anyone help me on this.

gerd_schoeffl · ‎11-24-2023

HI Muthukkumaran,

I am not sure what is causing the problems bit maybe there is an issue with the state of your uploaded custom widget. Could you please download the custom widget from github again, go to stories->custom widgets and remove the custom widget (delete), and upload it again (both the .json and .zip file)?

If you are unsure how to upload the widget have a look at

Hosting and Uploading Custom Widgets Resource Files into SAP Analytics Cloud.

Best regards

Gerd

saurabh_03 · ‎11-24-2023

Hi amanda.murphy

I am facing the same problem above. I did try to change decimal places to align with file but still the issue persists. I have even tried to keep Integer number in Measure and still getting the same error.

Below are few screenshots:

gerd_schoeffl · ‎11-24-2023

Hello Saurabh,

Are yo uploading from csv or from xlsx? Excel does certain conversions automatically and helps to prevent certain format issues that might arise when the csv file has wrong data (for example the wrong decimal separator). Could you please try to upload an Excel file instead of a csv file (in case you did not so already)?

Best regards

Gerd

saurabh_03 · ‎11-24-2023

Yes I tried with csv file with only 1 record same as above.

gerd_schoeffl · ‎11-27-2023

Hello Saurabh,

Sure, but did you try uploading an Excel file? NOT csv?

Best regards

Gerd

rambo_8143 · ‎11-28-2023

Hi Gerd Schoeffl,

Exactly tried the same way but still the same error reflecting, Tried with the raw content file from the github only both JSON and ZIP file. Is there any other way that can possible to get clear from the error.

teressa_delavega1 · ‎11-28-2023

Hello,

Can you explain how you do it in another tenant? I´m facing the same issue, I already tried to upload csv, xlsx, confirmed the data ,also did a test with the localdev.json, but the issue remains , on the network console I can see this:

X-Xss-Protection:

1; mode=block

Status: 500Internal Server Error

If you know how to unblock or another solution it will be very appreciate, thanks in advance.

Teresa

AmandaJMurphy · ‎11-29-2023

Hi muthukkumaran_,

Please ensure all prerequisites are met as outlined in the blog.

The File Upload Custom Widget may be installed on a SAP Analytics Cloud, Cloud Foundry environment. If you are not sure which environment you are using, please reach out to your SAP Account / Customer Engagement executive for assistance here.
SAP Analytics Cloud revision 2023 Q4 or higher.
The model must contain both accounts and measures.
The story must have Optimized Story Experience enabled.

Watch our short video containing the File Upload Custom Widget highlights for additional tips.

Best regards,

Amanda

ihar_stankevich · ‎11-30-2023

amanda.murphy hi,

We found a bug:

Add custom widget 1 to the page. Enable pivot setting. Pivot Value = Amount_LC.
Add second custom widget 2 to the page with fully the same settings but only with another measure. Enable pivot setting. Pivot Value = Quantity.

Result: in runtime when we run Custom Widget 1 data will load into Quantity.

Seems the last added widget rewrite this setting.

If create another story page (first for widget 1, second for widget 2) everything is fine.

gerd_schoeffl · ‎11-30-2023

Hi Ihar,

You can have only one file upload widget per page in a story. If you have more than one widget then then customizing of the other widgets will be overwritten by the last one. You can either put the widgets on different tabs within the same story or in different stories.

Best regards

Gerd

saurabh_03 · ‎12-01-2023

Hi Gerd,

Issue was with Version name. It should be public.Actual instead of Actual. I was able to upload the data in model using widget.

I wanted to know if we have an option to restrict member in dimension in widget. For e.g. Restrict only for few account or specific version when user uploads data.

Regards,

Saurabh.

gerd_schoeffl · ‎12-05-2023

Hello Saurabh,

When speaking about the 'Version' then you can set the specific fixed version in the default values when customizing the widget in the story. If no version is set in the default values (and no version mapped) to a column in the file then the end user gets prompted for the version. If a version is set in the default values then the value is fixed and the end user cannot change that version. Please note that the version has to be like 'public.Actual'.

When speaking about any other dimension then the default value is only used in case an entire column is not mapped or the dimension value is empty in a given row in the file. If the dimension is mapped to a column and the file contains a value for this column then the information from the file will be used. Thus for those dimensions the default value does NOT restrict the upload to a certain selection.

If you want to restrict the upload to say certain accounts you would have to use DAC or maybe data locking.

Best regards

Gerd

sandrajara_ · ‎12-05-2023

Hi to all,

Thank you for the explanation of the installation and operation of the widget

I am seeing an error where sometimes when I've already configured the widget and go to load the file, the icon disappears and I have to refresh the page several times to let me use it. What can this be due to?

Thanks all

meet_patel · ‎12-05-2023

Hi All,

While using the custom widget I see one issue.

When I am trying to upload excel file using data upload custom widget in public version (Import Type = Fact Data) and at that time it is working fine and successfully upload data into model and also able to see in model level.

But when I am trying to upload same excel file (with same data) in private version (Import Type = Private Fact Data) with same setting at that time it is showing issue saying Completed with Failures. So, it is failing to upload data in private version.

Issue Screenshot

Can anyone help why this is happing like that and how to get rid of this problem?

Thank you.

gerd_schoeffl · ‎12-05-2023

Hi Meet,

Can you please download the failed records and check for

Data Access Control

Data locking

Validations?

If you choose 'Fact Data' then data is uploaded directly into the model - just as when you execute an upload in the data management tab in the modeling. In this case not all checks are performed (e.g. data locking).

When you choose 'Private Fact Data' then data is NOT published but behaves like in a data entry in a table:

if a public version is chosen then the system opens a so-called public edit version (you see a little asterisk behind the version indicating that it is not published)

if a private version is chosen then data is written into this private version.

In both cases data needs to be published so any other user can see/use the data. And in both these case we check DAC and data locking when writing to the private/public edit version - and validation during publish.

Thus I assume that your data contains some records that are locked by data locking or DAC.

Best regards

Gerd

gerd_schoeffl · ‎12-05-2023

Hi Sandra,

That is definitely not the intended behavior. However it is hard to find a root cause... Could you maybe install the widget again? Does that help?

Many thanks and best regards

Gerd

meet_patel · ‎12-05-2023

Hi Gerd,

Thank you for the explanation.

But in my model level there is DAC and Data Locking Ownership are enabled but not restriction applied using DAC or Data Locking.

So, Read and Write column are blank and Data Locking Ownership column is also blank.