Adventures in SSAS Writeback

I should probably forward this to the Daily WTF (I'm a dedicated reader) - but we'll see how well the story tells here, with an audience that has a passing knowledge of SQL Server.
As part of my new gig, we have a lot of existing "BI applications" (typically some ETL, an SSAS cube, and a set of SSRS reports) that are built to... a different standard than I'm used to.  I have no desire to imply that the people that made these applications are not good people.  It's quite likely they either didn't have the right technical knowledge to build things properly (IMO), or were under other stressors that caused or contributed to poor solution construction.
With that caveat out of the way, what I'm about to describe is definitely the worst use of writeback I’ve ever seen.  But I have limited history.  I’d be willing to wager it’s quite possibly the worst usage of writeback ever - let me know if you agree, or top it in the comments.  Either way, don’t ever let me find you doing this…

Background on Writeback

First, some background (from an admitted SSAS noob) so we’re all on the same page.
BI applications that involve SSAS usually mean multidimensional cubes are involved.  That’s where the “cube” name comes from, and it is typically the target of end-user queries.  The cube is built from regular database tables (or views, or queries) – typically designed a little differently, in star schema usually, but still regular tables.  Those tables are normally filled by an ETL process that reads data from source systems, transforming it into useful information that the cube effectively summarizes and presents.

From time to time, there are requirements from users that they would like to add or modify information in that cube in ways the source systems don’t (want to) support.  One common request is “what-if” scenarios.  Other times its wanting to enter or adjust budget or target data.  Sometimes it’s “corrections” or “adjustments” to computed values.  One way to accomplish this requirement is to have those users fill in a form/database/textfile somewhere that the regular ETL consumes, eventually including those numbers in the finished cube.  This method requires that the ETL and cube processing execute after adjustments are made to incorporate this new information in order to see the result of their addition/change/adjustment.

Cube writeback shortens this loop (if implemented as designed).  Instead of users filling in a form which simply becomes just another source for the ETL, the users can edit the contents of the cube "directly", and SSAS maintains a SQL table filled with those "adjustments".  No re-run of the ETL or cube processing is required.  Those "adjustments" are persisted and made available (almost) immediately to all users.  That instant feedback is a definite advantage of writeback, but it comes at a cost of limited auditability, and it doesn't always apply adjustments like you expect.

Now on to the crazy…

We're in the process of rebuilding, or at least reviewing, every BI application in the business.  Next on the list for me was a cube built for reporting KPIs, and I was told that it implemented writeback to allow for targets to be entered.  Being a new guy to this sort of thing, I just nodded my head and carried on.  Our standard process involves renaming all parts of the application - primarily in order to enforce internal consistency, but also to allow side-by-side comparison before launch.  As we got into the project, we had to make sure the writeback hooked up and adjusted our new cube appropriately, just like the old one did.  And that’s when we found… that writeback was NOT turned on in this cube.  That’s right – we go to the writeback properties in the cube, and Visual Studio says that writeback is NOT POSSIBLE due to other settings in the cube.  Double, triple checked.  Cube in production is the same.  No writeback.
But wait, I said – I saw a table labeled “writeback” in the ETL processes… we’re reading the writeback information… where is it coming from?  Unfortunately, being a noob, that was the WRONG question.  The right question would have occurred to me much earlier – when I actually saw the “writeback” table included in the ETL.  Going back to the backgrounder on writeback, just seeing that table should have been a red flag that something wasn’t right… the ETL shouldn’t even be involved in writeback at all, because it should be fully contained in the SSAS cube layer.
Anyway, we’re still left with the question – and now it’s even more pressing – where is this writeback data coming from?  Examining a spreadsheet that an end-user uses to fill in adjustments, it becomes clear.  There’s a SECOND cube, in a SECOND Visual Studio solution, modeled on the same regular tables, WITHOUT the settings that are incompatible with writeback.  The user updates the spreadsheet, which updates that second cube with adjustments.  The second cube – using standard writeback configuration – writes data to a SQL table.  Any query hitting the second cube will automatically, and immediately, show adjusted information.  However, the primary (real) KPI cube will not… until the ETL is run that reads the second cube’s (supposed to be internally used only) writeback table.  Unfortunately, this second cube data is also incorrect – because it doesn’t have the right processing configuration (the settings that prevent writeback from working).  So we’re left with the first cube showing “good” but unadjusted information (until ETL runs again), and a second cube that shows “bad” but adjusted information.

High Facepalms All Around

At the end of the day, what we have is an overly complex implementation of end-user data entry that requires two cubes, roughly double the maintenance attention, and double the processing power of just about any other style of end-user data entry system.  And to add insult to injury, it doesn’t even deliver the only positive differentiator of SSAS writeback compared to other systems: instant incorporation of adjustments into the cube.
Awesomesauce.