There's a new, free, thin client OLAP tool available for download from the microsoft.com site. It's developed by the Grape consortium in asia. I haven't had a chance to look at it very closely, and I don't think it works yet with Yukon, but still seems to warrant a look, particularly since it's now a Microsoft download. To find it, Google for "OLAP GrapeCity Microsoft" and look for the reference at microsoft.com.
- Reed
Data write-back in Analysis Services has always been a bit frustrating because it's ultimately limited to leaf-level storage. (Not that leaf-level data write-back is not extremely useful--think of the millions of budgets that are input by using Excel spreadsheets--leaf-level data input without the benefits of an OLAP engine.) At any rate, Yukon write-back is still going to be ultimately leaf-level. One option for efficient high-level write-back is to "allocate" the values for each level to a single "dummy" member. But there's another option that we used occasionally in AS2K that would be even easier to implement in Yukon.
In AS2K, you can create Calculated Cell expressions that change the value of a single cell. So you can simulate data write-back by writing a calculated cell that assigns the target value to the specific single-cell subcube.
In Yukon, you get the same effect as a Calculated Cell by simply executing a script assignment. So, in a hypothetical cube with two non-measure attributes--account, and period, if the user enters the value 1000 for March Net Profit, you could "store" that value by using the simple assignment statement
([March], [Net Profit], [Amount]) = 1000;
Over the course of a session, your code collects these assignment statements, and at the "commit" portion, exports them to a process that parses, allocates and loads into a processable fact table.
-- Reed
OLAP is fundamentally numeric. Through "official" data write-back, you can't write comment values to a cell. That doesn't mean your app can't do it on the side
Inside an app, you could capture the tuple of the cell that gets the comment, convert the tuple into a unique relational key, and store the comment with the tuple. You then do the reverse on query.
Emir pointed out that you could accomplish the same effect completely within the cube: create a "comments" dimension that begins essentially empty. Then use dimension write-back to create the unique key for a tuple and add it as a new dimension member, with the comment text as the "name" of the member.
You could then create an action on the cell that looks up the unique value--perhaps using a User Defined Function (UDF).
- Reed
