An Item-based Approach to TM1 Model Design

[Harvey]

Hi all, I've kicked off the Flow blog with an article about "item-based" model design. Would be very interested to get people's opinions on this approach, whether you're using it, if not why not, etc.

Here's an excerpt:

The item-based design approach has the goal of allowing data input in a flat, tabular format without giving up the browsability and analytic capability of highly dimensional cubes. It also separates input from reporting in a very elegant fashion.

You begin with an input cube, which should have only the basic dimensions, plus a measures dimension to represent the columns of the input table, and item dimension to represent an arbitrary number of rows.

The measures dimension will include many string elements which map to other dimension elements. Thanks to the picklist feature in TM1 9.5+, these lists can even be restricted to ensure invalid entry does not occur.

A separate reporting cube is then created that maps the string elements to actual dimensions, for reporting and analysis, usually via rules. This cube has no data entry and populates itself entirely from data in the input cube. You could also use TI to populate such a cube without too much trouble, for implementations that have higher data volumes.

You can read the rest over at the blog.

Let me know if you'd like me to keep posting here about future blog articles.

[BigG]

I can see your logic ...I think, not my cup of tea. If we take the lowest common denominator approach when talking about our user base then we will get no where with analytics. If we have some data entry requirements wouldnt those people entering data want to see the results/ validate in the reports too, therefore need to understand the dimensional structures? Just keep the number of dimensions down in your input cubes... sorted

Only would do this approach for small data entry, probably not even then. Picklists are great though yes.

[Harvey]

Hi BigG, thanks for the feedback. I agree it works best with small-ish data.

How about the other advantages of the approach detailed in the article? I believe the flexibility of creating additional dimensions without significantly changing the input cubes (and hence the input forms) is a major advantage of the approach.

I have a good example in my follow-up article on the blog, if you're interested.

It shows how I was able to create an entirely new reporting cube from the same input data, with dimensionality that didn't previously exist.

All it took was a couple of new rules and feeders and it was done -- all in all it took about 15 minutes to update.

[PlanningDev]

This design approach was used somewhat frequently in Cognos Enterprise Planning. While string input was not something you could match to dimension elements, frequently drop down lists were used for lookup or accumulation style D-Links. The only downside to having the flexibility of changing your reporting cube, is that if you use excel reporting heavily, then dimensional changes to the cube cause reporting to break. If not then performance is really your only thing to worry about.

[jameswebber]

I wish we had followed this approach went designing our models... downloaded some of your stuff and will play.. not sure how far I will get on CE since I can't run models locally.

Thanks heaps for putting this out there

[Harvey]

Thanks James, let me know if you need any assistance getting things running.