What Does This Do?
Recently, a client was trying to figure out why a template wasn’t working and it came down to just one checkbox, the Only Send Changed Rows checkbox. By default, this is checked when you start building a template, but what does it mean, and why do I care? If we are working with a grid of 100 accounts, showing the projections for each month of the year (12 months), we have 1200 cells that could be updated (12 months * 100 accounts). If we change the projection for only one account in one month, then only one cell is being changed. When saving, we need to send data for cells back to the database. If we only changed one cell, sending one cell back is faster than sending 1200.
When Would I Change This?
This sounds like something that should always be enabled, right? Nope. There is a very specific, but common, case when this checkbox needs to be disabled. Imagine you are building a template where folks enter driver projections for their budget. Maybe they enter how many units they expect to sell over each of the next 12 months. In a hidden range, there are formulae that take the driver and multiply it by standard rates to figure out ancillary revenue or additional costs to be budgeted. If a user changes a driver in the top section, Clarity knows that rows has changed and marks it correctly. Clarity does NOT know the values in the hidden section have changed, so those never get saved back to the cube. This creates a very frustrating experience for the person developing this report. It appears that the saving just isn’t working. The numbers from the hidden section never get updated in the cube. The Excel formulae are perfect. The only challenge is that Clarity doesn’t know it needs to send these cells back to the cube. If you look at the olap datamap properties page, shown above, and ever see the combination shown for BudgetNR, you NEED to disable this feature. The combination of Allow Update and Preserve Formulas requires that the Only Send Changed Rows feature be disabled. This is when we uncheck the Only Send Changed Rows checkbox. This auto-magically fixes our issue and everything works as expected. The calculated values in our hidden range are saved back to the cube and we are done.
Any Downside?
The only downside is that each time this template saves, it MUST send back every cell. In our example above, we are sending back all 1200 cells to be saved every time. For 1200 cells, the difference won’t even be noticeable. Over 10,000 cells, the template will start feeling clunking and quite slow. As a rule of thumb, templates should take less than 3 seconds to save. From my experience, 3 seconds is about the amount of time someone can wait without losing their train of thought, or losing their feeling of flow. When our train of thought or flow is lost, we easily get frustrated. As with all template development, make sure you only show the cells you need, use bottom level leaves and avoid making people scroll around the screen. Following those simple principles will always lead to happier users, and fewer support calls.