Automating the Consolidation Process in Controller

[Conoscenti]

Does anyone know if you can automate the consolidation of data within Controller from SQL similar to the functions we use to load and publish data?

It would be nice if we could load data, consolidate it, and publish it to the data-mart in one automated process.

Thanks in advance.

Andrew

[Kognize]

You can use any standard ETL tool to load data from GLs directly into Controller, and trigger the Controller stored procedures to transfer the data from the staging cubes to the main cubes, and another stored procedure to consolidate the data. The consolidated data can then be trickle fed into a TM1 cube using the FAP, or alternatively reported on using Cognos BI once an appropriate FM model is has been built and published (its extremely annoying that IBM doesn't have an out of the box FM model for Controller nor the TM1 FAP cubes. >:()

[IBMrc]

If the question was "Can you schedule the consolidations" then yes you can. When you create a new consolidation, use the GUI to schedule it to run at the day/time that you want it to.
:) Good luck :)

[yurykon]

...and another stored procedure to consolidate the data.

- What is the name of the stored procedure to consolidate data please?
any specific syntax, parameters?

Many thanks

[IBMrc]

Unfortunately there is no stored procedure to generate/trigger a consolidation  :(

   
• However, you should be able to manually create consolidation batch jobs by creating an entry inside the relevant SQL table ("xbatchqueue").

TIP: Look at previous consolidation jobs (which already existing in there) and you will soon work our which parameters correspond to which entry etc.
=> Your SQL guru should be able to create some kind of method to manually inject consolidation jobs into that table as you require it.

Naturally this bespoke solution would be officially unsupported, but it should work :-)

Good luck!

[yurykon]

Cool!  :D

I'll look into it.

Thank you!