Confused about writeback

[Rosanero4Ever]

Hi all,

I read some documents about writeback functionality provided by TM1.
Can i develop a form-based input using TM1 in order to write data on source database?
Or can I save data edited in Cube Viewer executing a particular process using Odbcoutput?
Can I use Excel to perform wirteback to source database?
Thanks a lot for your help.

[tomok]

Show a little initiative and do some searching on this board before you post a question. There must be literally hundreds of posts about moving data to and from ODBC data sources. Asking specific questions is more likely to be responded to than a request to be spoon fed.

[qml]

TM1 is a database (of the MOLAP variety) that has interfaces that can be used to push/pull data in/out. Among other things, it is possible to extract data out of TM1 and load it to another database. The typical methods of doing this would be pushing data out of TM1 using ODBCOutput or ASCIIOutput or pulling it out of TM1 using OLEDB with MDX or one of the APIs.

However, be aware that TM1 can be often misused in the sense that it becomes a data source for systems it should not really feed in a healthy architecture.

Also, in the context of TM1 the term 'writeback' is usually used to describe the capability of direct user input.

[Rosanero4Ever]

Show a little initiative and do some searching on this board before you post a question. There must be literally hundreds of posts about moving data to and from ODBC data sources. Asking specific questions is more likely to be responded to than a request to be spoon fed.

I'm sorry, but I don't want to be spoon fed.
I'm "googled" for this argument and I asked a clarification.
I'm also reading the book "IBM Tm1 Official Guide" and I don't understand some parts as reguard as the writeback functionality
I don't know the reason of your malice.
Regards

[Rosanero4Ever]

Also, in the context of TM1 the term 'writeback' is usually used to describe the capability of direct user input.

How user can input data and send them to source databases?
Using Excel or Cube Viewer and the executing a process(with an odbcoutput or asciiouput as you suggested)?
Thanks!

[tomok]

How user can input data and send them to source databases?

With a TI process.

[qml]

How user can input data and send them to source databases?

Users can input data in many ways, using one of the many interfaces TM1 offers for data input. When they do so, you have yourself some data in your TM1 database.

Now the data needs to be pushed out using a TI process (this is the most typical solution) or pulled out using an external procedure created and executed outside of TM1.

Let me also reiterate that in the context of TM1 'writeback' typically describes the fact that TM1 can be used to get data from, but also to input data into. So you can have users contributing instead of just consuming.

The concept of writing data from TM1 to other databases can be called 'writeback' as well, but it's something different.

Does this clarify things?

[Rosanero4Ever]

thanks all!

[paulsimon]

Hi

I tried to think of a way to arrange things so that you could send data into a TM1 cube and write it to a relational database at the same time. The Action Button could almost do it, but only if it had the option to go to another workbook and then run a TI process. Unfortunately it only has run TI process then go to a workbook. I had a theory about using the element matching to do the trick of putting a 1 into a cell to allow a DBS to operate. (Std trick in TM1-Excel to have IF( rMySendRange = 1, DBS( Cube,elements ), "NoSend") ). Perhaps something to ask IBM for an enhancement.

The only other way that I can think of doing it would be to use a VBA macro, so you could send the data into TM1 with a click on a normal Excel Forms button, and then read the data from the Excel Cells and write it into the SQL database using a standard ODBC connection in the VBA, or run a TI process to extract the data you just wrote into TM1 and write it in to the SQL database. You could do a similar thing without the need for Excel using the TM1 API.

Unfortunately you can't use VBA if you are using a web front end.

If you are not so bothered about the SQL database being updated as soon as TM1 is updated, then you can schedule a Chore to run a TI process that will run the extract and ODBCOutput.

Another potential alternative is to process the transaction logs that are generated. The upside of that is that you will only need to process what has changed. The downside is that the current log is locked and cannot be read, and secondly that most developers disable transaction logging on bulk loads.

Perhaps the key thing to understand is why are you asking? Most people enter their planning data into TM1, and just use it there. If they do want it in a relational database too then they do a chore scheduled extract. I have not come across a requirement to put data into both a TM1 cube and a relational database at the same time.

Regards

Paul Simon

[Rosanero4Ever]

Perhaps the key thing to understand is why are you asking? Most people enter their planning data into TM1, and just use it there. If they do want it in a relational database too then they do a chore scheduled extract. I have not come across a requirement to put data into both a TM1 cube and a relational database at the same time.

Thank you very much for your post, Paul.
I need to write data directly to TM1(e.g. plan for distribution of cost centers, cards budget, etc.) and I also need to extract this data to databases in order to perform some calculations that will create other data that, then, will be imported in TM1.
I suppose, I could do it directly in TM1 using Rules, but I'm yet in a training phase so, in this moment, I prefer perform calculations in a relational SQL database.
I haven't the requirement to put data into both TM1 cube and a relational database at the same time, so I could use one of solutions you suggested, i.e write data in TM1 and then schedule a TI process to write data into a database using an ODBCoutput.
Thanks a lot having solved many doubts!
Regards

[qml]

I also need to extract this data to databases in order to perform some calculations that will create other data that, then, will be imported in TM1.

I'm sure you realise this doesn't sound like an optimal setup, to put it mildly. One of TM1's strengths is its calculation engine.

I suppose, I could do it directly in TM1 using Rules, but I'm yet in a training phase so, in this moment, I prefer perform calculations in a relational SQL database.

Depending on the nature of these calculations, you could do them using either rules, or TI. Somehow I have a feeling that it would be worthwhile to attempt this exercise in TM1 because you're still learning. The amount of work to create a robust export and import of data will probably be no smaller than in the case of running these calcs in TM1, so my advice is to do it the right way in the first place.

So, what sort of calculations are we talking about?

[Rosanero4Ever]

So, what sort of calculations are we talking about?

One of the calculation is about applying the repartition plan of centers of cost: I have some common centers of cost and I must identify the correct center of cost using the repartition plan as following:
Center of cost | New center of cost | %
common | sales | 10%
common | R&D | 90%
....

Users decide the repartition plan as I wrote before, i.e. using excel to write directly to TM1.
the repartion plan is writed back to source databases using a TI process.
In this moment, i know how do this job using simple SQL statements, but, as you wrote, i could exercising with this scenario to improve my TM1 knowledge.
Thanks

[paulsimon]

Hi

What you call Partitioning is more commonly called Allocations in Finance Depts.

Create a cube that has the Cost Centres and another measures dimension to hold the rates. You might want to add a time dimension as rates may vary by time.

You can use a simple rule list

['Common Centre','Alloc Rate']=N:0 ;

to prevent anyone entering a rate against the Common Centre, which is the Centre whose Total Costs you want to allocate out.

Then in your main cube you need a rule like this

skipcheck ;

['Allocated Cost', 'Common Centre' ] = N: 0 ;

['Allocated Cost'] = N: ['Common Centre','Total Costs' ] *
DB( 'Cost Ratio Cube', !Centre, !Time, 'Alloc Rate' ) ;

and a feeder probably along the lines of

['Common Centre','Total Costs' ] => ['All Centres', 'Allocated Cost' ] ;

Assuming that All Centres is a consolidation at the top of the Centres hierarchy.

However, this can lead to over feeding, unless all Centres have an Alloc Rate in all periods. These days I tend to do allocations in TI, unless users need to see the effect of any allocation change immediately, then it must be a rule.

To use TI, you would create a TI process using a View on the Cost Ratio Cube as the source, with suppression of zero, consol and calc results, then for every Centre and Time period combination that comes from that view, on the Data Tab, get the Total Costs on the Common Centre for that same Period using a CellGetN, and then multiply that by the ratio and write the result into the cube for the appropriate Centre, and Time period and the measure Allocated Costs, using a CellPutN. If you only need to run the allocation for a single period, eg the current period, then you can make it more efficient by getting the Total Costs of the Common Centre on the Prolog and holding it in a variable and then using this on the Data Tab.

As the previous forumer said, you really should learn how to do this in TM1, and not export to SQL do the calcs there and then re-import, which will be much slower.

Search this forum and the web - there are lots of examples - I learnt to write a VB.NET program that automatically generates SSIS packages by cribbing examples from the web and applying a bit of knowledge - that was a lot harder than learning how to do an allocation in TM1.

Regards

Paul Simon

[Rosanero4Ever]

Many thanks, Paul!
I'm yet studying rules and I'll surely apply your advices because I know that export data, perfom calculation and the re-import in TM1 is a bad way.
Best regards