Numeric vs String Attributes – which is more efficient?

Introduction

An interesting question was posted on the TM1Forum about the most efficient way to craft a rule. This raised the subsequent question of how TM1 attributes are stored behind the scenes, and the impact of that on performance.

The documentation is vague on this point, and, although the arguments presented in the TM1forum post made sense, there wasn’t a definitive answer.

I thought I’d put it to the test.

The Method

I set up a cube with a large item dimension (4,000 elements) and a measures dimension containing an element for each calculation technique I wanted to test. I then wrote a rule for each measure, using one of the methods suggested in the TM1Forum post, as follows:

  • Measure 1: Used an IF statement to test if the category name ends with a ‘1’ or a ‘2’, and returned 1 if it does.
  • Measure 2: Added a numeric attribute to the category dimension which would contain 1 if the rule should return 1.
  • Measure 3: Added a string attribute to the category dimension which would contain ‘1’ if the rule should return 1.
  • Measure 4: Used the string attribute, but converted it to a numeric value before checking if it was 1 or 0.

I crafted a cube view that contained roll-ups of all the dimensions except the measures. I then calculated the cube view for each measure and timed how long it took to get a result.

I repeated the test three times for each measure, arbitrarily altering the rule and re-saving to ensure TM1 wouldn’t use cached values.

The Results

Interestingly, the test seems to have confirmed the assertion on the TM1 forum post that numeric values are actually stored as strings and converted. Here are the numbers, in seconds and %, for the detail-oriented:

Measure 1

Measure 2

Measure 3

Measure 4

Total

                     39

                     91

                     88

                   108

           326

                     43

                     99

                     97

                   125

           364

                     43

                   100

                     97

                   125

           365

                     42

                     97

                     94

                   119

           352

12%

27%

27%

34%

100%

Note:  the discrepancy between the first test and the subsequent ones can be explained by the fact that my laptop was running on battery for the latter ones.

It is clear that using a simple IF statement, even one containing two SUBST calls in its conditional, is more than double as efficient as using an attribute to determine behaviour. Of course, you get many benefits from using the attribute approach, so in many cases, it is still worth the overhead.

Converting the attribute to its numeric equivalent using NUMBR within your rule is markedly slower than comparing the string without conversion, but there is very little difference between using a string attribute over a numeric one. One is roughly as efficient as the other when reading from the attribute cube.

This means that numeric attributes are indeed most likely stored as strings, but the overhead of the conversion is negligible, and does not operate in the same way as the NUMBR function.

I would guess that internally TM1 skips validation of the string during the conversion, as the UI makes it impossible to enter a non-numeric value into a numeric attribute. The NUMBR function can’t do this, as it can accept any string as input.

I found these results very interesting, and they give us a framework to determine which method is best practice.

Conclusion

So what can we to take away from this small test? Here are a few tips:

  1. Using hard-coded logic is likely to be more efficient. However, hard-coding always goes against maintainability, so only do this if you’re sure the logic will never change, nor will the elements involved! This is a big assumption to make, so be very careful with it! Even when you can be sure, only choose this method if speed is the #1 priority in the model.
  2. When flexibility is needed, use a numeric attribute as a flag to determine rule behaviour. Since there is no significant overhead to using a numeric attribute over a string, the best choice is to go with the numeric. TM1 could one day change the way it stores numeric attributes, which could result in this approach being more efficient than storing at a string. However, it is unlikely that any changes in TM1 will lead to a string attribute becoming more efficient to read than a numeric one.
  3. Never convert your attributes manually using the NUMBR function. There is no benefit to this method, and it is significantly slower, so steer well clear of it.
I hope this article helps you make the right call when writing dynamic rules in the future! Happy modelling!
I’ve attached the model I used for testing, and the spreadsheet containing the results I obtained.

TST Model Data.zip (69.00 kb)

TM1 Attribute Tests.xlsx (10.09 kb)

From Agile to Anarchy (and back again)

Introduction

TM1 development has undergone a subtle evolution over the years.

As a relatively early adopter (I still refer to TM1 as an Applix product occasionally, and I remember the original version of TM1Web) of the technology, I’ve watched this evolution with interest, and feel what I have been doing with Flow, is an attempt to bring that evolution full circle and get back to those early glory days.

In this article, I reminisce, wax lyrical, and take a look at the present and future state of selling and implementing TM1.

Warning: this post may contain heavy doses of nostalgia!

Agile Roots

Anyone else remember a time when the typical TM1 implementation went as follows:

  • Get the client’s general ledger accounts and data
  • Write a few TIs to create the account and cost center structures, and suck in the data
  • Build some Excel reports to show the new data off
  • Hook up a few manual input templates for the tricky stuff
  • Send the users to a TM1 training course
Yes, these were simpler times, times when “Excel on steroids” was the sales pitch and demonstrating the “what-if” feature would get genuine wows from the crowd.
 
We used to sell in a couple of meetings, build POCs in hours, often while the potential client watched, and put together an entire system in weeks rather than months.
 
Perhaps we can remember them fondly, even wistfully. But, sadly, it has been a long time since I was involved in a project that simple, and I believe those kinds of projects are, for the most part, behind us.
 
Now businesses expect a full budgeting and planning application, capable of multiple scenarios and rolling forecasts, and able to collect and collate data from many disparate sources around the globe.
 
TM1 has evolved somewhat to try to meet these needs, but have we evolved as consultants?

Agile Decline

As the Agile methodology became popular in IT and software development projects, those of us in the TM1 sphere were starting to realize we were becoming less and less agile.

I recall speaking on the phone with the owner of a TM1 distributor, discussing the possibility to working with them. This must be two or three years ago now. To my surprise, he started talking about sitting with the customer on a time and materials basis, and building the model with them as they watched and participated.

Of course, I said, “you can’t work like that! We’ve got to lock down the requirements in a formal document, perform a technical design to those specifications, and restrict any modification with a formal change request process!”

It was at that point, in the back of my mind, that it hit me how much TM1 development had changed. The willingness to sit down with a customer, discuss their needs, and build a solution had been replaced with a fearful and almost paranoid IT mentality.

I realized that TM1 modelling and development had become as complex as software development projects, and had evolved to encompass the same rigid processes. The very thing that had originally attracted me to TM1 development — the freedom to build a solution without inflexible requirements — was now gone.

The Problem

So how did we lose the Agile edge that used to define and differentiate TM1 implementations? How did we go from a strong customer focus to formalization, obfuscation and general ass-covering?

The answer is simple. TM1 got bigger — and I’m talking bigger in every way.

Firstly, it was acquired by Cognos, then IBM, and was suddenly thrust into the light of big business. No longer was TM1 the surprising underdog. Now it was expected to go head to head with its enterprise-ready robust big brothers and hold its own.

Concordantly, TM1 started getting larger, more complex implementations. Pre-sales guys who had once gotten away with using the word “scalable” to mean you could add lots of RAM were now being asked if TM1 could be used in a server farm, collecting data from thousands of disparate data sources across global WANs, to calculate an entire organization’s planning, forecasting and consolidation in near real time.

And as a result of all this, we as TM1 implementors got scared. And those of us with an IT background knew exactly what to do: add more layers of process.

However, TM1 did not have the tools to support the Agile processes we were used to following. Deployment was done by manually copying files. Testing was done by manually clicking a mouse. And demonstrations to the customer were performed sparingly, as they took much time to set up and present.

Worst of all, providing any kind of workflow for the customer was embarrassingly lacking. Sure we could fire off external scripts to send out email notifications or SMSes, but the solutions were hardly robust or maintainable.

So we fell back on design and documentation as the crutch to get us through. Write reams of documentation, force the customer to sign off, then quote and build based what was “agreed”.

The fact that describing a financial model in a generic way was often more difficult than building it was neither here nor there.

Reclaiming Agile

Many old-school TM1 implementors have noticed this change, at least on an instinctive level, and tried to develop processes and methods to get back to the old ways. However, most of these were just band-aid solutions, and fell short of tools found in other areas of software development.

Watching this with frustration over the past few years led me to take a step back and look at the bigger picture and think through the problem without letting myself be clouded by prior assumptions.

Flow OLAP is the result those musings, and we hope that our partners are finding value in the tools and applications we’ve released so far.

However, this is just the tip of the iceberg. Keep giving us your support, and we promise to keep innovating until TM1 development has come full circle, and we can reclaim our Agile glory days!

Hey, I warned you about the nostalgia, didn’t I?

Techniques for Building Generic Models

What is a Generic Model?

Generic modelling has the goal of satisfying customer requirements in way that takes into account future needs and enhancements.

Often while gathering requirements, a BA will focus on the customer’s short term needs without considering wider-reaching goals, enhancements, and ongoing maintenance.

In order to use generic modelling techniques, we need to include as much information as possible in our BRDs and use it to design a system that will be more robust, adaptable, and easier to work with and use in the future.

But wait, I hear you say, customers are all different! In fact, each one demands a completely new implementation from scratch! Well, sure, customers will always tell you that their needs are unique and they do things in unconventional ways, and most of them DO have their quirks.

But at the end of the day financial applications end up as one or more lines in GL, no matter how complex the underlying calculations and logic. Isn’t a version a version? Isn’t a currency a currency? Isn’t a year a year, and a product a product?

Legislation and international business standards drive the types and techniques of financial reporting they are trying to produce, so there is bound to be commonality from one customer to the next, even if they are unaware of it.

The idea is to take this commonality and abstract it using generic modelling concepts, in order to reduce the painful redundancy of starting each development cycle from scratch.

This article focuses on the basic development techniques and disciplines you can use in TM1 design and development that will help you achieve a more generic result.

Benefits of Generic Models

Generic models are characterized by the following beneficial features.

Reusability

Ever feel like you’re writing the same calculation logic over and over again? Well, chances are, you are doing exactly that.

Let’s face it, currency conversion rules, for instance, are always eerily similar each time you do them. Likewise, rules to roll forward opening balances.

And what about cube structures? Have you ever noticed that a Loans module is similar to, say, a Project Capex module? Not exactly the same, but each is a list of repeated payments over time, with various other values calculated from that core structure.

If you build with generic principles, you can easily take your Capex module, copy it, add some measures and adapt the rules to satisfy the requirements of a Loan module. This saves hours of design and development time, and keeps the model consistent and familiar to users.

Adaptability

The more generic a model is, the easier it is to make changes.

For example, if you write generic currency rules for each cube, and even include currency conversion in cubes where the customer has said it is not necessary, it is much easier to support additional currencies if the customer requires them later.

And if you are attempting to take an Agile approach to an implementation, being able to change or update a design quickly, and have development respond, is one of the most valuable abilities you can have.

Maintainability

If a model is designed using generic techniques, it is built from the ground up with maintainability in mind. Your goal here is to allow an administrative user to adapt model behaviour without requiring advanced development skills.

Any rules or processes should remain unchanged, with only the inputs to their calculation logic — be those lookup cubes, attributes, or process parameters — requiring update. This helps the customer maintain their own application, without feeling overly reliant on the original consultants who implemented it.

Deployability

Often you’ll want to change the behaviour of a model from development to staging to production. Generic models only require a data change to update their behaviour, so are much easier to maintain in a multi-tiered deployment scenario.

Reduced Risk

When you reuse the same structures and techniques instead of making code changes to adapt a model’s behaviour, you’re inherently reducing the risk of introducing new bugs into the system. To some extent this also alleviates testing requirements, as you only need to test the reusable portion of the module once.

Downside of Generic Models

Speed/Efficiency

Writing rules or processes in a generic way will inevitably add some degree of overhead to your calculations. This is unavoidable, but can be mitigated with various strategies and intelligent design.

Readability & Handover

Often generic rules and TI processes involve more complex code, with frequent lookups and if statements that can confuse developers who might be new to the project.

Likewise, if a customer wants to hire an in-house developer to maintain the model you’ve built for them, they may have some difficulty understand what was done and why in the original design.

This is why it is important that the code is formatted well and commented with meaningful explanations.

How to Build Generic Models

When all is said and done, building models in a generic way is really just a set of disciplines. You need to discard some of your old habits and begin to think generically with every rule and TI you write!

Use lookup cubes and attributes

Hard-coding is the enemy of generic models. If you have a literal string in your rules or TI, you better be sure the thing that string represents is never going to change!

For instance, imagine you are writing a rule that copies the closing balance of the previous year to the current year. Your first thought might be to write a rule like this:

['Opening Bal', '2014'] = N: DB('This Cube', ...,
    'YTD Dec',
    '2013',
    !This Cube Measure
);

Of course this would work, but why limit it to one year? Use an attribute so you don’t have to write the rule again for next year, like so:

['Opening Bal'] = N: DB('This Cube', ...,
    'YTD Dec',
    ATTRS('Year Dim', !Year Dim, 'Previous Year'),
    !This Cube Measure
);

Looks pretty generic, now. But what if you wanted to take this model and deploy it in Australia? The fiscal year starts in July in Australia, so using ‘YTD Dec’ isn’t going to cut it. Assuming you’re using consolidations to tally up your YTD figures, you can restructure the dimension easily enough, but you need to draw your opening balance from YTD Jun.

Again, we can use a lookup to help:

['Opening Bal'] = N: DB('This Cube', ...,
    DB('Lookup Cube', !Year Dim, 'Fiscal Year End'),
    ATTRS('Year Dim', !Year Dim, 'Previous Year'),
    !This Cube Measure
);

You could take these ideas even further. If you want to explore more specific techniques for supporting flexible time dimensions, check out our previous article “Flow’s Date Lookup cube – Back to the Future!” and its sequel, “Back to the Future, Part II“.

Never depend on a particular structure in code

The problem above arises because we have assumed that our YTD values will be structured in a certain way. To some extent, you can avoid assuming a few fundamentals about your design as you code rules and TI, but you can certainly minimize them.

For instance, what if, instead of changing the structure when we want to set a different end of fiscal year month, we decided to name our month elements generically? If we simply called our months, ‘M00’, ‘M01’, ‘M02’, etc, up to ‘M12’, would we have a more generic result?

Well, yes, we could directly reference ‘M12’ in our opening balance rule, which would be attached to ‘M00’. This would mean we wouldn’t need to adjust our YTD consolidations to accommodate a different fiscal year start month.

Use aliases as display labels

However, the above solution is a little aesthetically ugly and not particularly intuitive!

To solve this, we could add aliases to each of these elements to make the display names fit in with our fiscal year period. When deploying in Australia, we could simply name our ‘M01’ element ‘Jul’ to update the behaviour, and remove the need for the costly lookups. In this way, our consolidations could remain static, and would need to be changed to deploy to a different region.

You can apply this technique to many other dimensions, where you substitute an arbitrary code for the primary element name and use aliases to determine the display value. This can make models that were appropriate for one client more transferable to another, and makes your development more agile should a customer change their naming preferences.

Just make sure you save all your subsets with the display alias turned on, and use those subsets in all your important views!

Use standard time dimensions

There has been much discussion about standard time dimensions in the TM1 community, and it seems not everyone agrees. This is just a matter of emphasis. Some place more emphasis on the browsability of cubes and views, while others seek a more flexible and maintainable approach.

Whichever time dimension structure you choose, it is of great benefit to standardize these across your entire module. Even if your users would like to vary the form in which they input data, you can standardize reporting cubes to ensure users get a consistent view.

Check out our previous article “An Item-based Approach to TM1 Model Design” for more information on standardizing reporting cubes across the entire model.

Use a common dimension order

Likewise, it is preferable to keep the order of dimensions as standard as possible. Some developers like to optimize each cube individually by tweaking the dimension order, but in the age of large amounts of available RAM, I believe it is much more preferable to have a predictable dimension order.

This cuts down development time and removes the need for developers to be constantly opening up cubes or using the DB formula generator to ensure the dimension order is correct. It also makes rules much more readable and assist handover to new developers or on-site admins.

Use parameters & global variables to define the behaviour of TI processes

TI processes are generally purpose-built, especially those generated with the wizard. But if you put in a bit of extra time to write these processes generically, you can often use them on future jobs and will save time overall.

Have a look at the our articles “Array Types in Turbo Integrator” and “Returning values from Turbo Integrator processes”  for techniques and tricks to make processes work in more generic ways.

Use dynamic (MDX) subsets and saved subsets effectively

Well-designed dynamic subsets are a great way to ensure changes in your element names or dimension structures will have a minimal impact on saves views and the reports that are based upon them.

Likewise, saved subsets can be utilized to set values such as current month and year. This way, all you have to do is update the named subset, and all the reports and views based on it will reflect the change.

Try to avoid unnamed subsets as much as possible, as these will just lead to confusion and errors down the road.

Create dynamic reports

This is probably a whole article in itself, but when you are writing Excel-based reports, it’s very easy to create them in such a way that they will break if underlying structures or names change.

Create named lists with SUBNM formulae to ensure rows and columns do not get out of sync. Use dynamic paging techniques to display small subsets of data that the user can scroll through. And finally, judicious use of active forms (yes judicious, as Active Forms can become very cumbersome very quickly if you overuse them!) can keep your reports and templates dynamic and responsive.

There are many other tricks you can use, but the main point is, think through the impact of changes to the underlying model and try to develop strategies to help your reports survive the turmoil!

Conclusion

This article has only scratched the surface of the various ways you can create more generic models, just by adopting a few habits and disciplines in your modelling work.

Sure, there is a bit of extra work up front, but the pay-off is huge, and increases exponentially over time! Each implementation, you’ll add to your bag of tricks, and this will leave you with more time to attend to the finer details and give your customer a higher degree of satisfaction.

Back to the Future, Part II

Introduction

Most financial models are based on the fiscal year, rather than aligning with the calendar year.

In many countries, these two calendars align, but in some it does not. In Australia, for instance (my home country), we have a Jul – Jun financial year, and both New Zealand and Japan have their own, too.

Propel Planning, Flow’s budgeting and planning application, always supported the idea of changing the starting month of the fiscal year, but we made some recent improvements which led to a better date lookup cube.

I thought I’d spend some time discussing it and provide a download link for those that are interested.

What was wrong with the old one?

Admittedly, there was not much wrong with the original date lookup cube, and it could still be used for most purposes.

However, there was often a necessity to map from Fiscal Year to Calendar Year and back again to do simple lookups, and we decided we it was worth finding a more efficient way to do this since it’s so core to the Propel Planning system.

To be honest, the former date lookup cube was something I built years ago, for us in consulting where the fiscal year period would be known well in advance of starting the job. And since I started working abroad, I don’t think I ever had a single implementation where the fiscal year was not aligned with the calendar year, and never really had to consider it.

However, Propel Planning is a dynamic, generic budgeting and planning system that can be sold world-wide, so it was time to face the music!

The Approach

A fresh look

I took a different approach this time, in several ways.

Instead of creating all the data in Excel and importing it into a TM1 cube, I created all the data in TM1 rules. The logic behind this was that we can easily export the rule-based calculations, delete the rules, then import the data as static numbers, so this technique just gives us an option to keep a rule-based lookup if we ever want to.

In the previous version of Propel, changing the start month was quite a significant effort. We had a Turbo Integrator function to do all the hard work, of course, but it had quite a lot to do – updating attributes, shifting data around in the date lookup, reordering elements. We even had a hidden cube that would provide data to the final date lookup when the TI process was run.

We realized this time round the designing a smarter, more flexible date lookup cube, along with the Month and Year Period dimension would make this configuration much simpler, and render most of the tomfoolery in the TI process unnecessary.

Here is the new data structure:

Generic Month & Year Period Elements

The most important part of designing this cube was avoiding the need to rename elements in the Month and Year Period dimensions. Instead of using “Jan”, “Feb”, “Mar” for the Month element names, we instead used “M01”, “M02”, “M03”, etc. Likewise, the Year Period dimension contains the elements “Y01”, “Y02”, “Y03”, etc.

This gives us freedom to interpret each month element as a particular named month, based on the Fiscal Year. So, in Australia, we would simply set the “Display Name” attribute on “M01” to “Jul”, and similarly for subsequent months.

Now we can always assume the element name “M01” is the first month in the fiscal year.

But it’s not quite that easy. We still need to make sure our date lookup cube calculates the correct calendar month for each Fiscal month.

Fiscal Month Offset

In order to do this, we use a simple attribute in the Month dimension called “Fiscal Month Offset”. This value is referenced by the date lookup cube when calculating the calendar month. The numeric value represents the number of months to “shift” the calendar month from the fiscal month.

For instance, if our fiscal year starts in July, we need to put a 6 in this value. The date lookup rules will then equate “M01” to “M07”, which is calendar month Jul.

This way, we can still look up calendar dates if need be.

Calendar vs Fiscal Date Measures

Hence, the date lookup cube supports two sets of measures, one for lookups that occur within the fiscal year, and another for lookups to calendar years. The simplest lookup is now to the fiscal year, with no requirement to translate to calendar month and back again to do a simple lookup.

Other Improvements

To reduce the number of measures, we also removed the “Backward” and “Forward” measures. This is now done using the “Offset Month” and “Offset Year” dimensions, which now include negative numbers.

This is more articulate, and simplifies rules which rely on numeric calculations to determine the number of months to search forward or backwards in time.

Example Usage

 

To lookup 3 months in the past from a fiscal-based cube:

# Lookup the year

DB('SYS Fiscal Date Lookup',

	!GBL Year, !GBL Year Period, !GBL Month,

	'0','-3',

	'Target Fiscal Year');

# Lookup the month

DB('SYS Fiscal Date Lookup',

	!GBL Year, !GBL Year Period, !GBL Month,

	'0','-3',

	'Target Fiscal Month');

Conclusion

With the new cube structure, it is much easier to lookup months and years in the past and future in fiscal-based cubes. Concordantly, it is no more difficult to use the cube in a calendar-based design.

Now where we’re going, we… er… don’t need roads… Sorry, I know it doesn’t really fit, I just wanted to say it.

Returning values from Turbo Integrator processes

Introduction

The two cornerstone features of any generic programming platform are encapsulation and reuse. These related ideas are simply the ability to write a piece of code once and then use it many times.

Being able to call processes within other processes is a useful technique for performing a set repeated task, but what if you would like the two processes to interact and communicate?

“Hey”, I hear you say, “TI has that covered, doesn’t it?” Well, sort of!
Yes, the ability to call a process from within another can allow processes to act like functions or procedures, and yes, they do accept parameters, so the calling process can modify the behavior of child process.
But what about return values? What if you want to build a utility function that performs some processing, and returns a value to the calling process?
This post will discuss various ways to tackle this very problem.

Techniques

Global temporary cube

Since cubes are globally available to all TI processes, it’s a natural first choice in storing global variables that can act as return values.

You would probably take the following steps:

  • The calling process would call a initialization function, that would receive a parameter to tell it what particular “global variable” measure to create.
  • The calling process would then call the child process, passing a parameter containing the name of the “global variable” measure.
  • The child process would do it’s processing, and write it’s result to the parameter cube using the appropriate CellPut function.
  • The calling process would read this value and continue.
Immediately, you can probably see issues with this approach. For one, it’s pretty messy, and creates annoying control cubes, and elements with temporary names. Sure, you can hide these with security, but it still clutters up the view for administrators.
Secondly, it’s quite inefficient. You could potentially have predetermined elements for your return values, but then you have to worry about concurrent processing and if a user mistakenly interferes with values in the cube using Perspectives or Excel. The speed of writing to and reading from a cube is probably not optimal for such a core requirement.

User-defined Global Variables

TI supports the idea of user-defined global variables. These are the same as local variables, but are available to all the functions being called within the call stack of the parent process or chore group. This is quite useful for the purposes of return values, and avoids the mess of using cubes.

Here’s an example:

StringGlobalVariable('sReturnValue');
ExecuteProcess('GetReply', 'pStatement', 'Hello');
sReply = sReturnValue;

In the child process, you could do the following:

StringGlobalVariable('sReturnValue');

IF(pStatement @= 'Hello');
    sReturnValue = 'Goodbye';
ENDIF;

ProcessBreak;

The end result of this would be your variable, ‘sReturnValue’ would contain the string ‘Goodbye’.

The limitation of this approach is mostly dictated by the maximum length of string values in Turbo Integrator. In the current version, TM1 10.1, this is 65536 characters, which is ample for many applications. Just be wary of using this technique in earlier versions of TM1, because prior to 9.4 and the Unicode upgrade, the maximum length of a string was 256 characters.

Update: The upper limit of a string passed to ASCIIOUTPUT is 64K. However, a string variable appears to be able to have a larger size, depending on the amount of available RAM. On my laptop with 4GB RAM installed, I was able to create a string variable of around 512K. Anything larger and my entire system would crash!

You’re also well-advised to grab the value of the global variable immediately after the ExecuteProcess call and assign it to a local variable. This is simply so you don’t get confused if making multiple calls to other processes which may use the same global variable name as others called in your parent process.

Conclusion

TI Libraries like Bedrock are great for processes that perform stand-alone operations, but do not support the idea of reusable processing functions that return a value to their caller.

These two a simple techniques can be extremely powerful for creating generic, reusable processes. Start using it in your development, and you’ll begin to see what I mean!

Happy modelling!

TM1 Development Methodologies & Tools

Introduction

The classical way of developing a TM1 Server seems to be to go in person to a customer site and develop on their infrastructure.

There are often many good reasons for this, mainly involving security and availability, but I have not worked this way for a long time.

I thought I’d post about my personal development methodologies and tools, and how they might help other developers in their work.

Local and Off-site Development

It’s first worth mentioning that I come from a software development background and have worked in various projects with different software development life-cycles, including structured Microsoft Consulting approach, “agile” development, and many variants in between.

This heavily affects my view of TM1 development, because I sometimes see practices that terrify me as a disciplined programmer.

I’ve seen teams of TM1 consultants developing individual models on their personal laptops, then trying to merge them together and integrated them in a completely unstructured way.

After seeing this, I certainly understand the appeal of the centralized dev server model.

However, I prefer a localized and often off-site development model for various reasons. It allows me to work on many projects simultaneously, stops frequent travel from interfering with productivity, can keep me free of untimely distractions, and just generally suits my way of working.

I won’t attempt to sell the approach here, as my focus is on tools and methods you can use if you happen to share my view.

My Process

Overview

Usually, the first thing I do when I start the development phase of a project is to find out whether it’s possible to work at least partially off-line.

If I find there’s a problem with sensitive data, I’ll just invent some test data in the initial development stages.

This also helps by providing independent, repeatable test cases that can be utilized for unit testing and possibly later for UAT.

I then work on a local TM1 instance on my laptop, whether on-site or not, which I have set up with all my standard tools and applications.

If data ever gets too big for the RAM on my machine, I’ll just scale back the data volumes.

Merging Changes

When the time comes, I’ll update the development server on the client site. I do this by performing an automated file compare and deciding which objects are new and should be included, and which objects should be left alone.

It’s easy to make this decision, as the file compare tool shows you exactly what has changed and which version is more recent. Most even allow you to do a visual comparison of text files, which is very handy for RUX and PRO files.

The tool I use for this is an open source one called “WinMerge”. You can check it out here.

Working in a Team

So, you might be thinking, this is all well and good if you’re working alone or on a very small team, but what about large collaborative projects?

Certainly, larger teams provide a challenge, but nothing that can’t be overcome.

Often in this case, it’s useful to “stub” the design. This involves creating all the cubes and dimensions first, and leaving out the attributes, rules, and TI processes. That way each team member knows what other cubes will be called and won’t make the mistake of creating any objects with the same name.

Naming conventions often come into play here, too. I will often turn up to a project and make it my first order of business to distribute a standard naming convention document I have been using for years. You might prefer a different convention, or the customer might mandate one, but the important part is everyone understands it and sticks to it.

I’ve attached a sample document I used on a project years ago.

Revision Control

One concept I find useful for projects with larger teams is revision control.

This is a discipline well known to computer programmers that not only allows your team to keep a centralized copy development files (in this case TM1 server files from your data folder), but keeps track of all previous versions, who changed them and why.

The Basics

The idea is to keep a central repository of all files in the project and allow developers to “check out” the files to their local computer and work locally.

Once they have made changes, they can choose to “commit” their files to the repository, or to “revert” the changes they have made. If other developers are also making changes, developers perform an “update” to ensure they have the latest committed files.

It has other benefits, such as allowing the team to “tag” releases, so they can always get back to a particular version of the project, and for an individual developer to “branch” development (split it), for experimentation or creating an alternate version.

There are many other features and benefits to using revision control, which you can find by doing a Google search on the topic.

If a revision control system detects that two users have changed the same file, it does not allow one user to overwrite another’s work. It gives a “conflict” notification and allows the users to deal with that conflict.

For text files, you can often “merge” the changes made to both files, or, since revision control tells you who made the conflicting change, you can simply get in contact with the other developer and negotiate which file is correct.

But for TM1?

It may seem counter-intuitive to use a revision control system for TM1, as many of the files are not text-based, but I have found it very useful. Sure, you lose the merge functionality for non-text files, but you can still often perform a Winmerge “diff” and get enough information to work out what has changed and how to resolve it.

When dealing with TM1, you can exclude the TM1 control files from the revision control repository, as most have an “ignore” feature. This is important, because TM1 updates these files each time the server is started up, so they will always register as modified.

The main drawback I have found is getting team members to adopt it, as it does require some process and training to be used effectively.

Software Options

The tool we also use for Flow development is called Subversion, with the Visual SVN Server front-end. It is an open-source version control system that supports all the features thus described, and a nice MMC snap-in interface for configuring the server and permissions.

There are also various front-ends for Subversion. The one we use is Tortoise SVN, which integrates with the Windows shell and provides icon overlays to indicate if a file is up-to-date or has modifications.

Conclusion

Using some of these techniques, processes and tools provides a more flexible project environment by making it easier for developers to work on local copies of their models and providing a framework to merge the changes back to the central server.

Of course, many of the free tools in the Flow Toolbox Suite have the same goal and can automate some of these concepts even further, to make TM1 development even easier!

If you haven’t checked out the tools yet, you can get them here.

I hope it has been an interesting and useful discussion. If you have any questions, feel free to comment below.

TM1 Naming Convention Sample.pdf (105.55 kb)

An Item-based Approach – Quick Update

Working on the Flow Model Packager, I often have to make random changes to a model so I can test the model compare and deploy functionality.

Yesterday, I was using the item-based approach sample server as my test case, and decided to try to implement a more real-world change than I usually do (most of the time, I just add and delete things randomly for testing purposes).

This led to the idea of creating a new reporting cube that would ignore Category and Subcategory, but organize payments by “Payment Range”.

Using the item-based approach proved very flexible in this case, as I was able to add a quick rule and feeder to the input cube and create an entirely new cube which would report on information that wasn’t previously captured.

It also brought up some issues regarding re-triggering numeric feeders in TM1 10, which I will cover in an upcoming article.

For now, I thought I’d share the results for comparison — the model is attached. Happy modelling!

StringFeederSampleRange.zip (2.92 mb)

An Item-based Approach to TM1 Model Design

Introduction

James and I have been working together for some time now, and we have evolved toward what I would class as a non-typical design approach in TM1. For the sake of simplicity, I refer to this as an “item-based approach”.

It is very prominent in our implementation of Propel Planning, and underpins a lot of the power of that particular product.

I thought it worthy of some discussion, as it has many advantages, and a few gotchas, that are worth considering in any implementation you might be involved with.

The Approach

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.

I call it item-based, because this approach naturally requires an item dimension, with arbitrary names. Most of the time we just call the elements “Item 1”, “Item 2” etc, up to a maximum amount. Because this maximum is imposed, it is important to keep the efficiency of the model from being affected by the number of elements in the item dimension. More about that below.

Advantages

There are many advantages to such an approach.

Data-entry simplicity

New users of TM1 are often uninitiated, and, dare I say it, sometimes under-prepared by standard TM1 training courses, to understand the full advantages of a multi-dimensional database model. It doesn’t matter what you do, some users have spent way too much time in Excel or Access and simply think in terms of tables.

And why should they bother? Many of these users are simply data contributors, and do not have any interest in performing extensive analysis on their data.

The flat input approach allows such users to contribute their data in a way that makes sense to them.

It also allows them to adjust manual inputs and correct errors without cutting the data from one intersection and pasting it in another, an operation which can be error prone and, let’s face it, slightly buggy in the TM1 Perspectives cube viewer, and difficult in the TM1 Contributor front-end.

Maintainability & Agility

TM1 implementations are naturally agile and flexible. Developer with an IT background like myself might fight against it, and try to impose strict, inflexible Business Requirements and a rigid change request process to protect against scope creep, but that really undermines one of TM1’s key advantages in the market place: agility.

Imagine a retail sales model, which has Region, Sales Rep and Distributor as data points of interest. Sales reps and other users contribute data from the field using their laptops.

In a typical TM1 design, you’d create a cube with Region, Distributor and Product as dimensions. The input form would ask the user to select elements from each of those 3 dimensions and would write the sales/inventory data to the intersection of the elements chosen.

All is good, and the managers and finance staff can browse the cube and get the insight they need.

However, imagine, after months of data has been collected, someone in head office decides they would like to also track the data by Customer Type. The data already exists in the point of sale system, as each customer is tracked by credit cards and loyalty cards they use when making a purchase.

With your typical design, you don’t have a lot of choice, but to redesign from scratch and create a new cube with the additional dimension. You might choose to keep the existing cube for backward compatibility, in which case you’d have two sources of the same data, which could lead to synchronization issues since the original data is manually contributed from Sales Reps in the field.

It’s your basic nightmare, and if you were halfway through the implementation, you’d probably tell your customer that it’s a change in scope and that it would have to be left to phase 2.

With an item-based approach, you don’t have these issues. You can take the new data from the POS systems, import the Customer Type field via TI (while creating the new Customer Type dimension on the fly), then update your reporting cube and rules.

Yes, you still have to do some basic redesign, but there is no requirement for a complex and error-prone data migration.

Contributor & Insight-friendly

TM1 Contributor (or “Applications” as it’s now known) and Cognos Insight, are great front end tools for data contribution. They are a little weak, however, when it comes to customizing views to be friendly for the end-user. A highly dimensional input cube forces the view designer to choose between unworkably large grids or many laborious title element selectors which make cutting and pasting data difficult.

A flat, item-based input cube is much simpler to work with, supports multi-level cut and paste, and presents itself in a more logical fashion for quick data input. String values can be typed in as well as selected from a list, then copied down as necessary.

Downsides and Gotchas

Performance

If you’re not careful, this design approach can tempt you into inefficient rules and over-feeding. Performance can suffer with large data volumes.

However, with better design and a clean rule-based approach this can be avoided. Over-feeding is not necessary and rules can be structured logically and efficiently.

As always, TM1 has it’s quirks, but once you understand the gotchas associated with this design approach, they are easy to avoid or work around.

I’m planning several follow-up articles that will go through these issues in detail, and how to make sure they don’t have you pulling your hair out.

Complexity

Rules in this design approach can appear more complex and be harder for another developer to understand. I have first hand experience of handing over such a design to very capable developers and having them screw up their noses and replace my cubes with a more standard TM1 design.

I believe this is partially a cultural issue, as TM1 is taught in a particular way, and that has become accepted as “correct”. Once a developer adjusts to this kind of thinking, it’s actually very difficult to go back!

Obviously well-formatted rules and code comments can go a long way to alleviating this issue also.

Limitations

There is a natural limitation imposed by the item-based approach, and that is the number of elements in the item dimension forms a maximum number of “slots” for data input.

To avoid the situation where a user does not have enough “slots” to input their data, a developer might be tempted to include a large number of elements in their item dimension, and, if the rules and feeders are designed poorly, this could lead to poor performance.

However, a well designed cube won’t need a lot of input slots, as the average person is not able to navigate, or even usefully perceive, thousands of rows of data!

In our retail sales example above, there may be many sales items entered, and at first glance, it may appear to require a form with thousands of visible items shown. With a bit of thought, it usually possible to group input tasks meaningfully so that only a useful number of items need to be shown for the current input task. For instance, the Sales Rep could enter only the data for the particular store they are visiting, as they most likely wouldn’t be entering data for several stores simultaneously.

And, either way, a more dimensional approach does not mitigate this problem either!

Conclusion

With a bit of planning and thought, an item-based approach to TM1 development offers many advantages and rewards.

My follow up articles will be based on a simplified example, which is attached to this post for you to download and examine. The example is built and tested in TM1 v10, with the ForceReevaluationOfFeedersForFedCellsOnDataChange set to “T”.

Feel free to leave comment, criticisms, or unfettered praise in the comments section below!

And yes, that is the name of the property!

StringFeederSample.zip (2.98 mb)