Excel Upload and Adjustment Template Design.

[rdclapp]

Hello!
I am looking for some help/advice on the Excel upload function in UX. I need to build an “Adjustments Input Screen” This is isn’t anything out of the ordinary. The input cube needs to support input of adjustment amounts by month, and string elements that direct those amounts to impact the right set of intersections (Account, product, etc). The string elements apply for “All time”. The users also require the ability to upload the adjustments via an excel. I have tried 2 different designs and I continue to hit the same issue in the Excel uploader.

Design 1) Split the string measures into their own cube. This seemed like good idea since the time dimension does not apply to them. Then create a view with 2 sub views having line items on the rows. This resulted in the same error.

Design 2) Merge the 2 cubes and put the string values in leaf input element in the time dimension and put the numeric adjustments in the time elements where they belong. This made the view feel a little disjointed, but since it was against one cube I figured the excel uploader would work.

Same error, which seems to be linked to the nested column dimensions

Other options I have considered.

Merging Time and Measures into 1 dimension so they can be viewed as a single set instead of as a tuple of the 2. Not my favorite option because it compromises the dimension’s reusability, or leaves me with another copy of Time that I need to maintain,

Splitting Time into Year and Period, putting period into the measures with the string elements. This means that line items can be different across years, making adjustment trend tracking a challenge. (Line 1 is not the same type of adjustment every year, and rolling totals are next to impossible to see in the adjustment cube)

Anyone know a better option?

[cw-ch-scott]

The Excel Upload widget definately copes with nested row and column dimensions. The only requirement to get this to work is that in the view definition the grid dimensions (and the stacking order) must match to the Excel file and the content of column(s) A (+n) and row(s) 1 (+n) must contain principal names or valid aliases of the grid dimensions.

The Excel Upload also copes with string as well as numeric data. AFAIK there shouldn’t be issues with mixing string and numeric data in the grid, or at least I haven’t heard of an issue with this before.

What the widget doesn’t cope well with is blank space in the column and row headers. It should be just a single continuous grid. Also somethgin to be aware of is that empty cells in the grid will be skipped and excluded from the batch. (So if you want to clear a numeric cell you must include a “0” value in the Excel cell and likewise to clear a string a " " value.) As the update is in a batch any invalid row or column headers (or a string value to a numeric cell) will fail the entire batch. This is something which isn’t very user friendly and we are aware of and there is an ER already to add additional pre-validation and better user error feedback before the commit (just a matter of prioritization, as you know there are always lots of competing things to work on).

With the available details this is going to be difficult to solve as to what exactly the issue is. It looks like the data preview is apearing and the failure is on the commit? I think it would be good to have a quick 1:1 support call to get more info @wwang are you able to take this up?