Product Information
How To Upload Data to a Public or Private Planning Version Using a File Upload Custom Widget Within SAP Analytics Cloud.
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 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 model must contain both accounts and measures.
- The story must have Optimized Story Experience enabled.
- One widget per page may be used.
Overview
The steps to upload 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.
-
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 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 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).
Choosing the Execute With Failed Rows option writes only correct records to the specified version. Leaving this option unchecked means that 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 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.
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 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 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
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 Data
It is now possible to analyse, compare and maintain this 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 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
Check out our technical blog on how to further develop custom widgets File Upload Widget – How to develop custom widgets with the react framework
See SAP Analytics Cloud Custom Widget Developer Guide, to learn about how to extend Custom Widgets within SAP Analytics Cloud
For more information on Version Management see Create, Publish, and Manage Versions of Planning Data within SAP Analytics Cloud Help.
See Enable Optimized Story Experience within SAP Analytics Cloud Help for guidelines on how to enable Optimized Design Experience for Stories.
Attributes of an Account Dimension within SAP Analytics Cloud Help provides information on Account Types in a model.
Object Type Permissions within SAP Analytics Cloud Help outlines the Maintain permission meaning.
Custom Widgets code can be found at Custom Widget Samples.
Find out more about our Data Import Service API.
Find some more great content on SAP Analytics Cloud Custom Widget Framework below:
How to use Linked Analysis with Custom Widget
Announcing Custom Widgets Data Binding in SAP Analytics Cloud – Analytics Designer
Demystifying Custom Widgets for SAP Analytics Cloud
SAP Analytics Cloud – Widget Analysis
Bringing ChatGPT in SAP Analytics Cloud using Custom Widget
Leveraging ChatGPT to Create Custom Widgets for SAP Analytics Cloud
Quickly integrate SAP Analytics Cloud with other systems using custom widgets
If you have any questions, feel free to comment below or post a question to our SAP Analytics Cloud Questions & Answers forum.
Thanks for reading!
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,
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
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
Thank you for your replies Amanda and Gerd ! Very clear
Hello,
Would it be possible to have a quick explanation of what means this error:
It appears when I try to run the button. On the console, I can see this:
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
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
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]".
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
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:
Amanda Jane Murphy Great job! Really needed functionality and nice example of using Custom Widgets.
My tests show me that functionality:
Job_Settings
Could you check from your side? Thanks.
Update: attach full settings for this case
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
Amanda Jane 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.
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
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.
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
Hi Gerd,
I dont see an option to upload zip file but only json file.
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
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
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
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
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
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
Great to hear, thank you Marco.
Hi Amanda Jane 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:
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
Yes I tried with csv file with only 1 record same as above.
Hello Saurabh,
Sure, but did you try uploading an Excel file? NOT csv?
Best regards
Gerd
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.
Hi Amanda Jane 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?
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
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
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
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
Yep, now DIS is working properly, custom widget too 🙂
thanks
Hello Amanda Jane 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?
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
Hi Amanda Jane 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
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
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.
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
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.
Hi Muthukkumaran Muthukannan,
Please ensure all prerequisites are met as outlined in the blog.
Watch our short video containing the File Upload Custom Widget highlights for additional tips.
Best regards,
Amanda
Amanda Jane Murphy hi,
We found a bug:
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.
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