Storing Comments as ‘Non-Numeric’ Measures

I think perhaps it is best to be clear from the outset just what OLAP is for.

I see two main & interrelated uses:

1. Reporting & querying of ACTUALS

2. Business modelling; i.e. PLANNING

The first use is the one that gets most of the attention, usually in the form of multidimensional cubes on top of the datawarehouse.

Here, everything is based on underlying tables; the familiar dimension & fact tables & the resulting star/snowflake schema.

If forecasts/budgets are to be included then they are expected to be brought into the datawarehouse; again the numbers are in a fact table.

The reality is that these forecasts/budgets are the result of a business model built using OLAP technology.
Or at least could be, as OLAP technology can build very effective business models (ask anyone who has used TM1).

The business rules mean that much fewer numbers need be input for each forecast & many strategies can be modelled.

So if using OLAP technology for both uses, all ACTUALS from the underlying transactional systems can be brought in 'Datawarehouse' style.
But end-user initiated PLANNING comes into the cubes from the opposite direction.

The nature of planning is these numbers are constantly being adjusted & refined until the plan is committed.
This means that 'real-time' OLAP is needed.
As an end-user inputs a number it must be instantly available.
Not reliant upon a process that takes it through the datawarehouse.

I think this is adding up to me arguing for a looser connection to underlying tables.
That the planning numbers are stored in the cube only until the end of the day when the 'final' fact table can be created.
And, this is then updated as appropriate each day.

The way TM1 works (I think) is writeback values are stored in RAM but also written to a transaction file (in case of crash).
Normally at the end of the day the changes stored in RAM are written to the hard disk.

SQL/AS writebacks straight away to a writeback table which gets bigger & bigger until amalgamated with the original fact table.
Hence the need for a bit of SQL programming.

So, it's not as straight forward as TM1 but it can handle writeback.

With the LookUpCube function it is also possible to flow numbers from one cube to another, or even calculate new numbers based on numbers in other cubes.
So the business models can seemingly be built in AS just as in TM1.
Not as easily though.

This all adds up to separate cubes for actuals & forecasts.
And as I'm after the variances, the question is where to calculate/store them.

I tend to favour the idea of calculating them on-the-fly in the front-end & not storing them anywhere.

However, I have the desire to store comments.
These comments are likely to be at an aggregated level, by which I mean the difference between an aggregated ACTUAL & an aggregated FORECAST.

As these numbers come from cubes that used shared dimensions, it would be desirable to have a 'comments' cube.
Hence, the desire for a 'non-numeric' measure in a cube.
And when written to the hard disk would likely have dimension coordinates that are at an aggregated level.

AS doesn't allow non-numeric measures though.

So if I've no comments cube to store the comments in, there is no great need to store the variances that are not being explained.

I hope this shows Microsoft that although they have come a long way with AS, there is still more work to do.