Utillisation of the SAP Analytics Cloud (SAC) Excel Add-in to Read, Edit, Process and Publish Data

To enable the SAC Excel Add-in, you must first open excel and go to “File” and then “Options”.

Once opened navigate to the “Customize Ribbon” option and ensure that the “Developer” is checked and press “OK”

Installing the Add-in

Open a blank workbook in Excel go to the developer tab and “Add Ins” OR to the Insert tab and “Get Add-ins”

Then navigate to the store header and search for “SAC” and the Add-in Should appear “SAP Analytics Cloud for Office” – you can now press “Add”

You should now see the SAP Analytics Cloud tab on the top, click this and go to “Sign In”
Here you will need to insert the URL for your SAC tenant this could be your production or test environment.

Once inputted the URL press “Go” and you will then be prompted to login, please use your SAC credentials to login:

You are now connected to your SAC cloud tenant; this will allow you to view models in your SAC system directly from Microsoft excel. Once logged in you can then start to add tables from models which you can use to analyze your data in excel. We require a table to be introduced as a reference for our SETDATA function. Click on “Add Table”.

When you select the add table option you want to go to the directory of where your models are kept. The screenshot below shows the path to models you should be familiar with. You want to select the model you want to update the of.

For this example, we would like to push data into the “Sales_Planning” model which we have selected to bring in. You can now use this table as a live view by adding dimensions on the right panel which mimics the designer panel in SAC. However, for this demo we are just introducing this table to allow for a reference point for our template.

Now that we have introduced our table, we must create a template to allow for the SETDATA formula to reference the different dimensions within our model for it to push the data. To do this we must use the correct format (TUPLE; line by line) with all dimension inputs outlined, similar to the format used for flat file uploads into a model. It is also important that when populating the cells of the dimensions you use the ID’s and not the descriptions to avoid errors – I.e for version using “Public.BUD” instead of “Budget” as shown below:

Aggregating identical lines

Once all data lines that are in, we must now consider the identical data lines. There will be examples of data lines that may have the same input fields but different values. For this it is important to aggregate those values to capture the total data. As default the SETDATA logic takes the highest value for that line and processes it. Using this information, we can introduce the excel logic below to ensure our identical lines are being aggregated.

The first column we must create is to identify identical combinations:

Formula =A4 & B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4

Our second column is to aggregate the “Value” column if there are matched lines:

Formula =SUMIFS(J:J, K:K, K4)

We can now use the reference table and template created with the SETDATA logic to push the values into the model. We do this by first introducing another column which will hold our SETDATA formula. Then we use the SETDATA formula which follows the logic as shown below.

=SAP.SETDATA(“Table Name: Reference to Table where you intend to push the data – In our e.g this will be Table2”, Cell reference to the Desired Input values -in our e.g this will be the Aggregated values column- “L4”, “Name of Dimension”, cell reference, “Name of Dimension”, cell reference .. )

SETDATA Logic in the example above is as follows:

=SAP.SETDATA(“Table2”,L4, “Version”, A4, “Sales Planning Measures”, B4, “Affiliate”, C4, “Channel”, D4, “Country”, E4, “Product”,F4, “SKU”,G4, “Level”, H4, “Date”, I4)

Please Note: It is important when referencing the dimensions in the SETDATA formula to use the exact same naming convention used when setting up the model. You can use the table designer view to find the exact names of the dimensions.

Finding The Table Name

The SAC Excel Add-in by default names the tables in the order in which it was introduced, you can find the table name in the left panel when selected the cells of the table. In our case the table name is “Table2”.

Refreshing Data:

Once the SETDATA function is setup we can now use the “refresh” function which will run the function and return the current values that are in the model for each line of data, this is presented by the “Value (Set Data Push)” column. This column holds the SETDATA formula and is populated by the current data in the model.

Process Data:
From our example the “Value (Set Data Push)” column displays the current value in the model. Whereas the “Value” column is the new value we wish to push. The “Process Data” function will be enabled when it notices changes in the “Value” column to allow you to push these new values through. When you run the “Process Data” function This will push the “Value” column to the “Value (Set Data Push)” column whilst also taking into consideration the aggregation of identical lines.

Once clicked the “process data” function it will run and update the model with the new data. However, these values will not be persisted in the model until “Publish Data” is selected.

Publishing Data:

As shown above after running the “Process Data” we see the values now updated in Column M. We can also see the “Process Data” function is now grey whereas the “Publish Data” function is now available. To persist the data, you must run the publish data else the data changes will not be saved to the SAC model.

Revert Data:

If for some reason you are not satisfied with the data changes you may use the “revert data” option to go back to the last published state. Example shown below

There is not an upload log that is generated like when you upload a flat file. However, if you wish to monitor the data changes made you can always navigate to the “Data changes” screen in SAC as shown. This will give you a similar view to the template created to track the data changes by line and you can also filter the changes here. Please note tracking data changes must be enabled in the model configuration first in order to view this screen.

Points to Note:

When you use the “refresh” option on the excel sheet it re-runs the function, but values only get pushed when you select “process Data”.

“Datacell not input enabled” Error
– This error is due to the dimension being in a flat hierarchy. The Excel Add-in cannot write to un-booked cells if there is a flat hierarchy present therefore, we must enable a hierarchy or alternatively create a second hierarchy that mimics a flat hierarchy to write data to these cells.

Best Practice:

– Always select the “refresh” option when switching on another tab to get latest values.