TM1 10 Tip #2

TM1 10 Tip #2

After installation of TM1 10, if you try TM1web and receive an xml error it its likely due to the "IBM Cognos TM1 Excel Service" being set to run under local system and not having the privelages to create documents.
Change this service to run under an administrator account and you should be fine.

TM1 10 Tip #1

I have helped 3 people out so far with exactly the same 3 issues when setting up TM1 10 so I wanted to take time to document them:

Tip #1

You are best adding the following parameters to your TM1 10 cfg file if not there already:


AllowSeparateNandCRules=T
DistributedPlanningOutputDir=tunit  

The first parameters is because of the new wizard functionality in modeller that helps rule creation and it needs this parameter set to help you write C: level rules.
The second parameter is a new one to do with Cognos Insight and the below explains it:

#DistributedPlanningOutputDir
# Type: Optional, Static
# Cognos Insight distributed clients need information called "tunits".
# This data is created when an application is deployed and is updated as the TM1 server runs.
# The location of the directory used for this purpose is set using this parameter.
# In order to deploy Cognos Insight distributed client applications using this database, uncomment or add this parameter as DistributedPlanningOutputDir=<location of the tunit directory>.
# The pathname specified can be absolute, or relative to the TM1 server data directory.
#
# Examples:
# DistributedPlanningOutputDir=tunit   creates a directory "tunit" under the TM1 server data directory
# DistributedPlanningOutputDir=..\tunit   creates a directory "tunit" as a sibling to the TM1 server data directory
# DistributedPlanningOutputDir=C:\Program Files\IBM\cognos\tm1\samples\tm1\GO_New_Stores\tunit   creates a directory "tunit"  at the specified location.
#DistributedPlanningOutputDir=.\tunit

IBM Cognos TM1 10.1.0 and 9.5.2 object names cannot contain the following reserved characters

Goes with the best practice guidelines for using underscores in model names:

http://www-01.ibm.com/support/docview.wss?uid=swg27024713&myns=swgimgmt&mynp=OCSS9RXT&mync=E


Reserved Characters

The following characters are reserved. You cannot use these reserved characters when you name TM1 applications, cubes, views, dimensions, subsets, processes, and chores:

Character​	Description​
’​	apostrophe​
*​	asterisk​
@​	at sign​
\​	back-slash​
:​	colon​
,​	comma​
"​	double-quote​
>​	greater-than​
<​	less-than​
|​	pipe ​
?​	question-mark​
;​	semicolon​
/​	slash​

Note: The right curly brace (}) should not be the first character in any user-created TM1 object name. TM1 and control object names always begin with the right curly brace. If a user-created object name begins with a right curly brace, the object will be hidden to end-users if Display Control Objects is turned off.

The MustGather!

If raising a PMR with IBM the following url outlines the MustGather information that you will need to collect:

https://www-304.ibm.com/support/docview.wss?rs=2307&uid=swg21456392

Cognos Proven Practices on Youtube

Check out the Cognos Proven Practices on Youtube, including some good BI report studio tips:

http://www.youtube.com/user/ibmbusinessanalytics

Active Form Calculation mode

I would recommend reading this article on IBM if you work with active forms on a regular basis:

https://www-304.ibm.com/support/docview.wss?mynp=OCSS9RXT&mync=R&uid=swg21500429&myns=swgimgmt

Controlling the calculation mode will improve performance and prevent some screen flickering.

Some Active Form quirks in Cognos Express (9.0) TM1 (941)

-          In data validations, if you want to display a message make sure you have a title otherwise it will display null at the front of the message

-          If you have action buttons that navigate between sheets and leave the workbook name blank then the navigation will work but the workbook name will be lost from the tab bar. You can set the workbook name to be a named range if this is an issue

-          In the action button process options, don’t delete the messages, only untick them to not display anything

Active Form Top Ten Tips

In no particular order but hopefully a useful list for people to check through to ensure good performance:

1. Delete any Named references with #Ref in them, do an Excel find to see if there are any #Ref in cell formula, check for external links and change/delete
2. Delete any unused columns or rows. Use the slider as an indicator as to how many rows/columns are saved with the sheet
3. Keep SUBNM, ELCOMP formula to a minimum
4. Before publishing the Active Form delete all rows below the tm1rptrow formula
5. Any lookups to cube that are used in other formula’s need to be a DBR rather than DBRW
6. Workbooks perform best when uploaded to TM1 Server (copied to TM1 server through Add File route), subsequent navigation options via action buttons should then have “Look In: TM1 Applications” selected
7. Try to minimise the number of worksheets in a workbook. Definitely less than 8
8. Try not to use Action buttons formatted as hyperlinks
9. Don’t use dynamic named ranges e.g. ranges that are not visible to objects within the workbook
10. Use named ranges in action button properties rather than cell references e.g. =rngTM1Server not =’Sheet1’$A$1

EV Sizing

Proven Practice document for sizing executive viewer deployments:

http://www.ibm.com/developerworks/data/library/cognos/performance/cognos_specific/page560.html?ca=drs-

TM1 and virus scan software

Link to official recommendations:

https://www-304.ibm.com/support/docview.wss?mynp=OCSS9RXT&mync=R&uid=swg21458762&myns=swgimgmt

Virus scan software can negatively impact the performance of TM1 and can cause corruption if it is checks TM1 files while TM1 is running. For that reason, virus scan software should be set to skip checking the following:

• The TM1 data directory
• The TM1 logging directory (if not in the data directory)
• The directory where the tm1s.cfg file is located (if not in the data directory)
• The directory where any Excel sheets (or other docs) are located and uploaded to the TM1 server as a reference
• The directory where any flat files may be written to (output from a TI process, for example) or pulled from (import into a TI process, for example)
• Any other databases being used by a TM1 process (Oracle, SQL Server, MSAS, etc.)
• The TM1Web/TM1WebEx files - by default located at: C:\Inetpub\wwwroot\

Integrating Cognos Express into Sharepoint 2007

Step by step instructions found on proven practices:

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page559.html?ca=drs-

What to do when you have a bug with Cognos/TM1

Use the following link for detailed instructions:

https://www-304.ibm.com/support/docview.wss?mynp=OCSSPURP&mync=R&uid=swg21389775&myns=swgimgmt

TM1 Active Reports - Alternative Row shading

Alternative row shading is a good techniques for visual reporting.
With Active forms you can easily achieve it by using a formula in column A such as:

=IF(TM1RPTELISCONSOLIDATED($B$20,$B20),"C",MOD(ROW(),2))

This formula will then return a result of C, 1 or 0 and your format range just needs to cater for these with 1 and 0 being different shading.

Cognos Express 95 Online Mnauals

Useful to keep in your bookmarks:

http://publib.boulder.ibm.com/infocenter/cx/v9r5m0/index.jsp

TM1 952 Parallel Interaction

TM1 952 introduces and exciting new feature called Parallel Interaction which massively improves the locking around multiple writers to cubes.

There is a good IBM tech note here to read:

https://www-304.ibm.com/support/docview.wss?mynp=OCSS9RXT&mync=R&uid=swg21460595&myns=swgimgmt

TM1 Cube Dependency

Cube dependency has become an important aspect admins should be aware of within their models.
There is a good overview describing what it is and how to cater for it here:

http://publib.boulder.ibm.com/infocenter/ctm1/v9r5m0/index.jsp?topic=/com.ibm.swg.im.cognos.tm1_op.9.5.2.doc/tm1_op_id3833CubeDepenedcy_MN4002.html

Does TM1 support clustering /load balancing?

Interesting link here about whether TM1 supports clustering/load balancing:

https://www-304.ibm.com/support/docview.wss?mynp=OCSS9RXT&mync=R&uid=swg21420705&myns=swgimgmt

Answer

None of the shipped versions of the TM1 software support load balancing or clustering. The TM1 software does not possess any integrated load balancing or cluster functionality. Nor does it support and use load balancing or cluster standards and interfaces used by some external load balancing or clustering software.

For instance:
Cluster-aware software run by WCS (Windows Cluster Services) can execute a hot failover. If the master node of the WCS cluster fails, its slave node becomes the new master node. Cluster-aware software can directly switch from the failed old master node to the new master node. Due to the hot failover, from a user point of view nothing has happened, the user sessions survive the hot failover, the users are not forced to log on again after the switch of the cluster-aware application from the old failed master node to the new running master node.

But the TM1 Server software is not cluster-aware, thus it cannot perform a hot failover. In contrast to a cluster-aware application a non cluster-aware application must be started on the new master node. And because the user sessions have been terminated by the failure of the old master node, after the non cluster-aware application is up and running on the new master node, the users do have to log on again.

TM1 952 - New Rule and TI functions

The following link outlines the new rule and TI functions available in TM1 952.
The ability to do Averages in rules is something very exciting

TM1 Active Forms - hidden sheets

If you do an Active Form from the cube viewer and the row dimension does not contain a subset then it will create a hidden sheet in the workbook that contains a list of the elements you had in the rows.

Your TM1RptRow formula will then reference this hidden sheet with a formula similar to ({AR}01'!$B$17:$B$22)

These references to hidden objects perform slower on tm1web then references to subsets or mdx. For performance I would recommend replacing these hard coded references by changing the TM1RptRow formula to use the subset or mdx parameters and deleting the hidden sheet within the workbook

TM1 Active Forms

TM1 Active Forms are very powerful but sometimes a few simple tips can improve performance dramatically.
The next few post will outline a few of these simple techniques.

The first tip is that first row of the ‘active’ section is the basis of the entire report … all formulas are copied down from there. Find the row with the TM1RptRow formula and this is the row that will be copied.

You can actually delete all rows below row with the TM1RptRow formula. All rows beneath are calculated at run time.

For example I took a client's active form report, that was a simple active form straight from the cube viewer but contained nearly 1000 rows and took almost 5 minutes to appear on tm1web, deleted all the rows beneath the TM1RptRow then saved and uploaded. The result was the form then appeared in tm1web in seconds

Excel export not working in TM1 Contributor on Windows 2008

http://publib.boulder.ibm.com/infocenter/ctm1/v9r5m0/index.jsp?topic=/com.ibm.swg.im.cognos.tm1_rdm.9.5.1.doc/tm1_rdm_id121InstallationandConfiguration.html

If exports are not working on your TM1 contributor deployment and you have installed on windows 2008 then its probably an issue outlined in the online manual about a chnage in dll locations in Win2008:

Due to a change in location of a Windows 2008 .dll file, when running TM1® Contributor on Microsoft® Windows 2008, you must either copy the .dll to the c:\windows\system32 location or add the following symbolic link to permit Export to Excel or Export to PDF to work.


Steps


Login to the system as an administrator. It is not sufficient to be a user who is part of the administrator group.


In a command prompt cd to


c:\windows\system32


Enter the following command:


mklink iprop.dll c:\windows\syswow64\iprop.dll


This command creates a pointer to the original file required by TM1 Contributor.


If this link file is ever deleted, you must re-create the link to allow exports to work again

Single Sign On for Cognos Express on Windows 2008

With the release of Cognos Express 95, windows 2008 64 bit is now a supported OS.

The following url contains notes to setup single sign on:

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page555.html?ca=drs-

TM1web export to pdf not working

Sometimes you may receive an error when installing TM1 that the pdfcamp did not install correctly or on an existing TM1web or contributor application you may find the "Export to PDF" button is not working.

You should then test this by going into the TM1\Bin\PDFCamp directory and running the appropriate setup.exe (32 bit or 64 bit depending on your OS).

If you receive a message such as:

"enummonitors_failed error (1722)"

then it's likely the your print spooler service has not been started and you need to manually start it as per the instructions here:

http://www-01.ibm.com/support/docview.wss?uid=swg21439516&myns=swgimgmt&mynp=OCSS9RXT&mync=R

I think this same issue can happen when installing Cognos Express too

Cognos Express and VMware

Some useful tips on Cognos Express within a vmware environment here:

http://www-01.ibm.com/support/docview.wss?uid=swg21413456

Rename TM1 Elements

One of the biggest time savers I have used recently is the SwapAliasWithPrincipalName Turbo Integrator command.


It isn't well documented but allows you to swap an alias with the element name, thus allowing you to rename TM1 elements.


binhints.com has an excellent tutorial here:


http://www.bihints.com/renaming_elements

Automaticalling Deleting Log Files

2 great posts here on olapforums for how to automatically delete old log files in TM1 or Cognos Express:

http://forums.olapforums.com/viewtopic.php?f=3&t=4108

Cognos Express 95

With the launch of Cognos Express 95 last month there are a number of new features such as the new Planner module, new backup and restore capability etc.

The one I look forward to most though is the support for Windows 2008 64 bit

Cognos 10 Redbook

A good resource for anyone considering Cognos 10 is the redbook available here:

http://www.redbooks.ibm.com/redbooks/pdfs/sg247912.pdf

Scrolling through dimensions in TM1 Web / Contributor

Useful tip here on the IBM website about changing the default number of rows and columns that appear before paging takes affect:

http://www-01.ibm.com/support/docview.wss?uid=swg21461148&myns=swgimgmt&mynp=OCSS9RXT&mync=R

Test COG-385: IBM Cognos TM1 Master Exam

There is a new TM1 exam available called COG-385: IBM Cognos TM1 Master Exam:

http://www-03.ibm.com/certify/tests/objCOG-385.shtml

TM1Web Timeouts

The defaults timeouts on tm1web normally need extending for deployments.
The best guide I have seen is here:

http://www-01.ibm.com/support/docview.wss?uid=swg21408905

and extract below:

Resolving the problem

To prevent a TM1Web session from timing out configure your Timeout Parameter according to business requirements and “precedence of timeout settings” (best practice is not to change the default parameters). Check web.config and tm1s.cfg file timeout parameter information. Check if you have a setting in your tm1s.cfg as it could be impacting your timeout if you do (note that no entry = never timeout). IdleConnectionTimeOut Seconds specifies a timeout limit for idle client connections, in seconds.For example, if you include the line IdleConnectionTimeOutSeconds=900 in tm1s.cfg, the server disconnects idle client connections after 900 seconds. Timeout Parameter in TM1 & Microsoft IIS & TM1Web 0. Increase timeout IdleConnectionTimeOutSeconds (tm1s.cfg) from 900 sec to 7200 sec (= 120 min) 1. Increase timeout of IIS worker process from sec (=20 min) to 7200 sec (= 120 min) cscript %SystemDrive%\Inetpub\AdminScripts\adsutil.vbs set W3SVC/AppPools/ApplicationPoolName n 2. Increase timeout DefaultAppPool from default 20 min to 120 min 3. Increase timeout TM1 Web Session (web.config) from default 20 min to 120 min 4. httpRuntime executionTimeout="7200" (web.config) 6. restart IIS after changing timeout settings Another category of Session Timeouts, the ones where immediately after logon (or a short time later) the Session Timeout hits the user, is due to the default setting in IIS Version 6 webserver of MS Windows Server 2003: Go to Control Panel -> Administrative Tools ->IIS Manager -> Application Pool -> Default Application Pool (DefaultAppPool) -> right mouse click to open context menu, selectProperties -> tab Performance -> Web Garden: Maximum number of worker processes -> Change from default value of 4 to 1.

IBM Cognos Solution Implementation Methodology

If you are about to implement a new cognos product or a new project within an existing install then its worth checking out the CSIM roadmaps available:

 http://www-01.ibm.com/software/data/cognos/services/implementation-roadmaps.html


There are roadmaps for Cognos BI and TM1 available 

TM1 Contributor not liking an alias named Description

I quite commonly create a default alias on my dimensions with the name Description.

It would seem though that TM1 Contributor does not like this as a name for the alias on a nominated workflow hierarchy.

I could not understand why my alias was not displaying in Contributor but upon using a different name for my alias on the workflow dimension, suddenly everything came right again.

My suspicion is that there is some MDX call within Contributor that becomes invalid for some reason.

TM1 TI process SQL preview killing

If your TI process datasource consists of a sql statement that takes a long time to run then it will become annoying when you want to make changes to that TI and the sql executes every time you open the TI process.

There is though a simple workaround.

At the end of your sql add the to your WHERE condition “1” =’?pCondition?’

 Then in your Prolog tab at the very start add a line  pCondition = '1';

 By having the sql where clause not being populated until run time (execution of the TI) it means your TI will now open without running the SQL statement

Running multiple instances of Cognos BI on a machine

Having recently installed Cognos10 alongside Cognos8 on my laptop I found the following post by Ahmed Lashin to be vary helpful:

http://www.alashin.net/cognos-8-bi/multiple-instances-on-the-same-machine/

The main trick was ensuring unique ports for dispatcher, logging and shutdown in cognos configuration

How to enable browsing for Images in IBM Cognos Express Report Studio

By default you will not be able to browse for images in Cognos Express report studio.
To enable it you need to follow the instructions found here:

http://www-01.ibm.com/support/docview.wss?uid=swg21421987

Running TM1 as Desktop application in Windows 2008

Obviously best practice would be to setup TM1 to run as a service but sometimes you need to setup a TM1 server to run as a desktop shortcut you can double click on to start.
In windows 2008 though the security setup is a bit more strict than previous versions. If you just double click the shortcut you will likely get errors about logging or missing files and the server won't start even though everything looks fine in the cfg file etc.
The trick is to right click the shortcut and select to "Run as Administrator" and everything will start as normal.

Ending of support for TM1 on IE6

TM1 9.5.x releases will be the last to support IE6 as a browser:

http://www-01.ibm.com/support/docview.wss?uid=swg27020555

TM1 on Linux

There looks to be a new release of TM1 951 available for deployment of Red Hat Linux Enterprise.

See updated supported environments page:

http://www-01.ibm.com/support/docview.wss?uid=swg27018895

Set sort order in tree prompts based on TM1 datasource

Nice little article available here on how to set a customized sort order in hierarchy prompts based off a TM1 datasource in report studio:

https://www-304.ibm.com/support/docview.wss?mynp=OCSS9RTN&mync=R&uid=swg21457443&myns=swgimgmt

Using SQL Express as Content Store for Cognos BI

First thing to point out is SQL Express is NOT  supported environment but for testing purposes on my own laptop the following steps were needed:

- Start SQL Server Configuration Manager
- Go to Client Protocols and enable TCP/IP and Named Pipes
- Go to Protocols for SQLEXPRESS and enable TCP/IP and Named Pipes
- Then double click TCP/IP to go into its properties, click IP adresses tab, scroll to botton for IPAll group
- Make TCP dynamic ports setting to be blank
- Make TCP port to be 1433
- In cognos configuration you can then select SQL Server and localhost:1433 for database settings

Integrating TM1web with Cognos BI

When you have a distributed architecture for your Cognos BI install and then you want to integrate TM1web into the environment with CAM authentication, you may find you get an error message about a CAF rejection and you cannot access tm1web.

This is due to the internal cognos firewall rules and you need to edit the CAF settings within Cognos Configuration. Normally this just involves adding the tm1 adminhost name into the CAF exception rule.

e.g. If your TM1 server is running on a computer called "TM1WebAppServ", then you would add this to the CAF exception rule

I found these sites helpful in resolving this issue:

http://www.cogknowhow.com/index.php/knowledge-articles/30-cognos-8/305-can-not-add-a-url-in-cognos-connection-to-direct-users-to-other-server-or-website

http://www-01.ibm.com/support/docview.wss?uid=swg21410316

Moving IBM Cognos Express Data to a New Drive

Sometimes you may need to install Cognos Express on one drive e.g C:\ but need to store all the data to a dedicated drive for backup purposes e.g. D:\
An IBM technote exists which outlines the steps to achieve this:

http://www-01.ibm.com/support/docview.wss?uid=swg21413500

TM1 install on Windows 2008

As of TM1 951 you can install on Windows 2008.
The key thing to remember during the install is to right click everything and "Run as Administrator".
This includes cmd prompt when you want to setup TM1 to run as a service and even IE when you want to install the TM1 rich client administration for Contributor.

Back Open for Business

Its been a while and the landscape has changed a lot, so I have decided to carry on where I left off and continue to update the site with some tips and tricks that I have found helpful.
The site will expand to include Cognos BI, Cognos Business Viewpoint and over time other products with the Cognos brand.

Emailing TM1 Reports

If you want to automatically email month end reports to managers then SPF Batch reporting is still the best way to automate the process. You need to add in some vba code to automate the emailing and I have found that when using Outlook 2003 you can get annoying security messages that pop up each time.

The best way of coding the email messages I have found is via cdo as outlined here:

http://www.rondebruin.nl/cdo.htm

By using cdo, it bypasses any outlook security questions, in fact you don't even need outlook installed to send emails thus you can run things off the TM1 server if you wanted.

TM1 Help Files

Applix will be publishing the latest help files for 9.1 inc hotfixes etc to this link:

http://www.applix.com/support/on-line-documentation.asp

Definately worth bookmarking.

Applix Podcasts

Check out the Applix podcasts available here:

http://www.applix.com/podcasts/

Drill Through Efficiency

TM1 users love drill through to transactions capabilty.
The success of its deployment though will depend upon how fast a drill through will return transactions.
Developers should look to maximise the efficiency of their sql statements and the best way to do this is to utilise the indexes of the tables you are querying.
For example I changed a SQL drill through statement that took 8 minutes sometimes and got it down to 8 seconds, just by making sure the sql queried the indexes of the tables.
Usually transactional journal tables will be indexed on their journal dates, so if you include this in your where clause the sql will work a lot faster.
Normally your TM1 GL cube will only contain balances per month and your transactions will need to be queried by their transaction date. Within your TI drill process you can easily take your month and convert it into a sql date between criteria.

Within the prolog of your TI drill process you will need code similar to this that will take the month, and depending on your financial year, convert to a calendar date string.

IF(NUMBR(AttrS('Period', Period, 'Period No')) <= 6);
vStartDate = '01-' | Period | '-' | STR(NUMBR(Fiscal_Year) - 1,4,0);
ELSE;
vStartDate = '01-' | Period | '-' | Fiscal_Year;
ENDIF;

In the sql of your TI process you can then include this vStartDate parameter within your where clause. Use add_months to add a day and then -1 off it to get the last day of the month.

WHERE a.Journal_Date between to_date('?vStartDate?','DD-MON-YYYY') and add_months(to_date('?vStartDate?','DD-MON-YYYY'), 1) - 1

This means that if a user selected drill on a May 2007 cell, the TI would create a variable vStartDate of '01-May-2007' and then create a sql that includes the where clause of
WHERE a.Journal_Date between to_date('01-May-2007','DD-MON-YYYY') and add_months('01-May-2007','DD-MON-YYYY'), 1) - 1

This sql is based on pl/sql so for Microsoft sql it may be slightly different syntax.

TM1 9.1 SP2

The software downloads section of the Applix website has been updated with the latest release of TM1 9.1 SP2 for the 32bit and x64 installs, as of 17/7/2007.

TM1 Copy Pate DBRW

If you want to copy and paste values into TM1 DBRW cells then historically you needed to use SPF (Beachware).

In recent versions of TM1 you are able to copy and paste a single value into a DBRW cell but not a range of cells.

Ben Hill recently posted his vba module that replicates the SPF functionality and I can vouch for its use having used it myself for a good while now.

Check out his download from the TM1 forum:

http://forums.applix.com/forum/tm.asp?m=12606

Zero Out and load last 2 months of data

I recently needed to dynamically load the last 2 months worth of data into TM1.

If anyone was interested I could go into detail of the sql but within TI I had to make sure I deleted the current month and the previous month. The best way to do this was to define to seperate areas of the cube to zero out, the code is shown below. This code is based on a fiscal year of Jul to Jun and will need adjusting to your own fiscal year.

#***********************************************
# Zero out first month
#***********************************************
pYear1 = CAPIT(LOWER(TIMST(Now, '\Y')));
pMonthNo = StringToNumber(TIMST(Now, '\m'));
IF(pMonthNo > 6);
pYear1 = NumberToString(StringToNumber(pYear1) + 1);
ENDIF;
pMonth1 = TIMST(Now, '\M');

vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear1, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth1, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);

#***********************************************
# Zero out second month
#***********************************************
IF(CAPIT(pMonth1) @= 'JUL');
pYear2 = DIMNM('Fiscal_Year', DIMIX('Fiscal_Year', pYear1) - 1);
pMonth2 = 'JUN';
ELSE;
pYear2 = pYear1;
pMonth2 = DIMNM('Period', DIMIX('Period', pMonth1) - 1);
ENDIF;

vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear2, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth2, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);

2007 Budgeting and forecasting study

Interesting link here from Adrian Downes to the 2007 Budgeting and forecasting study from PriceWaterhouseCoopers:

http://adriandownes.blogspot.com/2007/06/on-pricewaterhousecoopers-2007.html

Ben Hill - New TM1 Resource Website

My friend Ben Hill has started his own website for TM1 help.

On there he has some great tips for anyone using or thinking about TM1 on Unix and other good stuff.

SUBNM V8 to V9

One thing general users of TM1 should be aware of, if they have been upgraded from V8 to V9 of TM1, is the change to the SUBNM worksheet formula.

In V8 a TM1 slice would result in the paging dimensions having a formula like so:

SUBNM(Dimension, Subset, Index, [Alias])

The problem with this was that because the formula was based of an index, then if the indexing of that dimension changed then users would get unexpected results from their reports.

In V9 the SUBNM formula improved so that it accepted either an Index or a Name yet still kept the same double click functionality:

SUBNM(Dimension, Subset, IndexOrName, [Alias])

When you do a slice the default behaviour is for the Element name to be used as the parameter to the formula.

My suggestion is that if you have been upgraded from V8 to V9, then go back and revisit any old worksheets/reports that you have, double click the subnm formulas, select the element again and the subnm will update with the SUBNM using the Name as the paramter, thus future proofing your Excel reports.

Treemaps Excel Addin

There has been a bit of talk on the Applix forum about treemaps recently.
I think this type of graph would be very useful to analyse certain data types
and until Applix release their built in version, I have been using this excel addin from Microsoft Research:

http://research.microsoft.com/research/downloads/Details/3f3ed95e-26d8-4616-a06c-b609df29756f/Details.aspx

I pretty sure I can output the required csv file and then execute the command line version of the treemapper to auatmate production of graphs.

Find data in Oracle databases

If you ever need to find that elusive information in an oracle database to load into TM1 via TI, then the following sql is very useful:

SELECT * FROM ALL_TAB_COLUMNS
WHERE OWNER = ''

SELECT * FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '% %'

SELECT * FROM ALL_TAB_COLUMNS
WHERE table_name= ''

Basically you can use the ALL_TAB_COLUMNS table to find info on all the tables in an oracle database. i.e. you know there is a table that holds GST info but you are not sure where, then execute the sql:

SELECT * FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%GST%'

I would also recommend that anyone who needs to get data out of Oracle uses the tool Toad, which I find invaluable.

TM1 Upgrades

There is a new document on the applix website about the install/upgrade process for TM1. This is important because from 9.1 it is possible to apply upgrades, without having to remove the old version.

http://www.applix.com/support/documentation.asp?docid=1557

MDX Tools

it-workplace have released an mdx query building tool:

http://www.it-workplace.co.uk/IOCOverview.aspx

Being built on adomd.net it won't work with TM1 but i think it shows the increasing demand for mdx tools in the market place.

I built a tool for showing the mdx equivilant statement for tm1 views in my open source toolkit found here:

http://forums.applix.com/forum/tm.asp?m=10617

or

http://www.bihints.com/tm1_sdk

and I was wondering if people found these mdx tools useful and whether further tools would benefit people.

SAP Acquires Outlooksoft

Another aquisition, another surprise.

http://www.sap.com/usa/company/press/press.epx?pressid=7671

Considering TM1's recent extended ability in the SAP area via the SAP RFC connector, I would have thought TM1 to be a better acqusition.

TM1TOP Logs

TM1Top has been a really useful tool to me in the last few months.
It allows me to keep check on who's logged in and if anyone starts hogging too much server resources.
Currently I have tm1top set for logging every 5 seconds but the this does mean the log file builds up pretty quickly over the course of a week or so; therefore making it important that you archive or reset the log file every so often.

TM1 Subsets Copy Paste

I'm sure a lot of people are familiar with the copy and paste facility in the subset editor. It allows you to build a list of elements that may come from anywhere within the dimension using the find facility or reorder existing subsets.
One trick that I don't think people are aware of is the ability to copy from Excel into the subset editor.
e.g. someone sends you a list of costcentres in Excel they need a report on, you can go into your cube, open the costcentre dimension subset, delete any costcentres showing, flick into Excel and copy the costcentres, flick back into TM1 and paste into the subset editor.
This certainly is quicker to build a list of elements than going through and selecting them one by one with the find facility.
The other cool thing is that it will display any alias you have switched on too.

Time Dimension Creation

One of the cool new things about Analysis Services in SQL 2005 is the ability to define your time dimension from a wizard.
In BIDS you just enter what dates from and to, if you want a Fiscal Calendar and then what levels
e.g. Days > Weeks > Months > Years or Months > Quarters > Half Year > Years
It will create the hierarchy for you in AS but the really cool thing is that you can then push that information back to your SQL database. The Schema update wizard will create and populate a table in SQL for you containing all the information from the wizard.
Nothing to stop you then using this new tabe to create your time hierarchies in TM1 using Turbo Integrator......

TM1 9.1 SP2

This has been released this week and contains 2 important things I think.

1. The feeder issue in 9.1 sp1 where if you used curly brackets {} in your feeders then only the first element would feed; has been fixed.

2. There is a new function available called TM1User
e.g. =TM1User("sdata")
Should be cool for all sorts of things for tracking and helping users.

Watch out for this tech bulletin though explaining how any computer you try installing V9.1 SP2 will need to have Adobe acrobat on it in order to install successfully

http://www.applix.com/support/documentation.asp?docid=1542

Gannt Charts off TM1

I recently got asked to do some projects reporting in TM1.
One report they needed was a Gannt chart of the various stages each Project was at
e.g.
Start, Design, Implement, Finsh
So what I did was store the dates in TM1 so that the PMs could update them, do a DBRW in Excel to fetch the dates, then a days360() excel formula to calculate the number of days between each phase.
Once I had this data I then used the methods outlined in this article to produce a bar chart showing each of the 4 stages.
Who needs microsoft project ;-)

http://peltiertech.com/Excel/Charts/GanttChart.html

Business Objects Labs

Business Objects has a cool site where they introduce potential future products/add-ons

http://labs.businessobjects.com/

I'd love to see something similar from Applix. Allow the engineers to think out the box, post it for people to try and get feedback from them.

online file storage

Sometimes I find myself in the position where I have forgotten my usb drive but really need to get a copy of the TM1 database to review later.
In this situation I find http://www.omnidrive.com/ a great alternative.
You can get a free account with 2GB of storage, more than enough space to store a zipped copy of your TM1 database to access from another location i.e. when you get home.

HR Cubes

When doing detailed bottom up budgeting, people like to be able to budget for staff individually per department. There is often a difference between the level of departments that a person can see for GL purposes and what they can see for HR budgeting.

e.g. an accountant may be able to see the whole department structure for GL but should only budget for one department when it comes to budgeting salary costs per person.

I find the best architecture is to build the HR cube with a separate dimension called HR_Department. This dimension is a direct replica of the normal Department dimension but it can have entirely separate security applied to it

Now its just a case of creating some extra groups to handle clients access to the HR_Departments.

TM1 9.1 SP1

Version 9.1 is fully released now and available for general download.
Note that this is an SP1 release already so any major bugs should all be sorted.

My favourite features are:

- The new locking model
- Indentation on the cube viewer
- New rules editor

and the new dotnet api which contains functions for doing mdx results without using adomd. Something I've already utilised and think will be really useful for other people

Microcharts on TM1 Data

Check out this picture of an Excel dashboard, created using Microcharts, running off the sdata TM1 server.

You can download this file and examples from other olap servers here:

http://www.bonavistasystems.com/Download2/Dashboards%20for%20OLAP.zip

Security Rules

If you write rules on your TM1 security cubes be careful they don't become invalid.
e.g. You write a rule for a specific security group to have read permissions but 6 months later you decide to delete that group.

If you don't delete that rule the next time your server re-starts, it will find that rule file to be invalid and not apply any rules, thus you risk security profiles not working until the rule file is updated.

Excel Iterations problem

I recently had an issue where a TM1 report would not work.
After looking closer I found it would not run the vba code Activecell.Calculate
After a bit of playing around I found that if I turned off
Tools > Options > Calculation > Iterations
everything would work as normal.
Personally I have never found a use for iterations in TM1 reporting and I think its worth turning offon any TM1 excel report.

Oracle buys Hyperion

Well this is going to shake things up!
Microsoft has Performancepoint coming out soon so it seems Oracle has responded by buying big.
I presume Essbase will become the MOLAP server of Oracle/Peoplesoft databases but it surely will take some time to integrate it all together.
TM1 may very well benefit from all this.

http://www.reuters.com/article/ousiv/idUSN2824699920070301

TM1 9.1

There seems to be some confusion about the release of version 9.1
To my knowledge documentation is available for download but the official software will not be available until the end of March

Automatically load TM1

If you want to ensure that when a user loads an Excel report that TM1 is loaded, you can add the tm1p.xla as a reference to that file.
That way Excel will always check TM1 is loaded.
In the VBA go Tools > References > Browse
and browse to where tm1p.xla is installed.
This will then appear as a reference in your project.
Save the file, close down excel and open that file.
The file will load tm1 automatically.

Setting up TM1 Services

I am always creating and deleting TM1 servers as windows services.
You have to do a bit of reading to find out how to do this in the manuals but the basics are really easy.
Open a command prompt and change directory to the bin folder of where TM1 is installed

e.g.
CD C:\Program Files\Applix\Bin

to create a service type:
tm1sd.exe -install -n"ServiceName" -z"DataDirectory"

to delete a service type:
tm1sd.exe -remove "ServiceName"

TM1 & Backup Zips

I use zips of the data directory to make sure I always have a recent copy of my TM1 databases as backups.
Its just a really quick and convenient way if you want to be extra careful.
IT departments should be making backups of the entire hard disks but there's no harm in having backup copies in zips.

I have used Winzip and Winrar but yio really need to pay to get the full license.
I have been using Alzip recently which is free and does the job, plus the funny little characters are amusing.

http://www.altools.net/

Rediscover QUDEFINE

I haven't used the TM1 macro QUDEFINE in years.
This is mainly because I normally use MDX to execute the type of logic that Qudefine gives me.
Recently though at a site I was unable to use MDX due to the oledb provider not being installed, so instead I used QUDEFINE and QUSUBSET together.
QUDEFINE allows me to create a view based off a range of parameters provided in the Excel workbook and which can be formulas (thus allowing me to populate them via drop downs).
QUSUBSET allows me to create a subset on a dimension based off the results of a QUDEFINE.

The report I needed to create was a list of all the product sales against a selected customer for the last 12 months. There are something like 30,000 customers to choose from and 15,000 products that could have data.
Normally an MDX statement would do this in flash but thats not an option. A vba routine to loop round all these combinations would take ages.
Instead I put in a drop down of available customers, the result of which populates the customer option in the QUDEFINE Excel range. A refresh button then runs the following code:

Sub GetTM1Data()
Dim vTemp As Variant
vTemp = Application.Run("QUDEFINE", "Server:Revenue", "PricingTemplateQuery", Sheets("TM1 View").Range("rngPricingTemplateQuery"), , , True, False)
vTemp = Application.Run("QUSUBSET", "Server:Revenue", "PricingTemplateQuery", "Products", "PricingTemplateQuery")
Application.Run "TM1RECALC"
End Sub

QUDEFINE creates the view for me and QUSUBSET gives me a list of all the products that have values for the last 12 months. I can then use a report based off SUBNMs to that subset created from the QUSUBSET.

The finished report actually runs at a good speed and there is hardly any VBA used.

I'll do another post soon on how the 12 months rolling total was derived, when the year and month were seperate dimensions.

Excel Toolbars

Good article here about how you can add a toolbar to your Excel workbook.
I use this myself to create a floating toolbar of worksheets available for selection within Excel TM1 reports.

http://www.contextures.com/xlToolbar02.html

BIHints - Documenting TM1

BIHints has a very interesting article about using Perl and the graphviz graphing tool to create visual diagrams to document TM1 systems.

http://www.bihints.com/node/49

ExecuteProcess TurboIntegrator Function - Applix TM1 Technical Bulletin

It would seem in 845 and 9 SP3 you can no longer use the TI function Execute process in the data or metadaat tabs.
For more details see:

http://ftp.applix.com/Attachments%5C00%5C00%5C00%5C14%5Cis_document_1461_ChangestoExecuteProcessTurboIntegratorFunction.pdf

Daylight Savings - Applix TM1 Technical Bulletin

A new technical bulletin from Applix about Daylight Savings Time Changes in 2007 and how it affects States in the US and Canada can be found here:

http://ftp.applix.com/Attachments\00\00\00\14\is_document_1459_ApplixStatementonDaylightSavingsTimeChangesin2007.pdf

Calculate Options

Here's some tips for VBA calculating options that I posted on the forum

In the TM1 documentation you will find W_CALC which calculates a TM1 worksheet.

If you record a macro and do a F9 it will record it as "TM1RECALC" (bear in ming F9 calculates all worksheets in the workbook)

If you record a macro and do a SHIFT F9 it will record it as "TM1RECALC1" (SHIFT F9 calculates the active worksheet).

If your worksheet has TM1 formulas you are best using one of the above methods.

If you just want to calculate a lookup worksheet then you can use:

Thisworkbook.Sheets("SheetName").Calculate

If you just want to calculate a named range:

Range("MyRange").Calculate

Just the first 3 rows in the worksheet would be:

Activesheet.Range("1:3").Calculate

microcharts

With the popularity of the last sparklines post I will also mention Microcharts.
They also do an easy to use Excel addin to produce cool looking micro charts in Excel including sparklines, bullet graphs etc.

http://www.microcharts.net/

I have fun playing with these tools, they look excellent and work well of TM1 reports.

More Aquisitions in the OLAP world

It would seem the big OLAP providers are trying to consolidate their positions by buying new products from smaller companies to expand their offerings.
Applix recently bought Temtec to expand their web and dashboarding capabilities and in the last week Cognos has bought Celequest and Hyperion has bought Crystal Ball.

http://www.decisioneering.com/hyperion/index.html

http://www.cognos.com/news/releases/2007/0117.html

bissantz sparklines

I really like the look of Bissantz's Sparkmaker and Sparkticker.
I wonder if the Sparkticker will work with TM1Web......

http://www.bissantz.de/sparklines/sparkmaker.asp

Excel Application Events

Sometimes in your Excel front-ends for TM1 you may want to capture events at an Application level

e.g. For every excel workbook open, you may want to capture the double click event and run a certain set of code.

The best way to do this I have found is using the following link:

http://www.cpearson.com/excel/AppEvent.htm

TM1 Open Source Toolkit v1.1

I have published version 1.1 of the Toolkit on the Applix forum.
It contains example vba code for running TI processes/chores and vba for running MDX reports.

TM1 Open Source Toolkit Published

I published the 1st version on the Applix forum here:

http://forums.applix.com/forum/tm.asp?m=10617

Anyone can download play and improve.

http://www.bihints.com/ is also hosting it and will probably be the the hosting site for future releases to enable version control.

Visio 2007 to Document TM1 Maybe?

Interesting post here about using Visio 2007 to document a MSAS 2005 database. Looks to me like it supports OLEDB and could therefore maybe work against TM1. If I get a copy of office 2007 I might give it a go.

http://www.databasejournal.com/features/mssql/article.php/3652456

Reporting Services White Papers

Microsoft has 2 white papers showing how to connect Reporting Services to SAP BW and Essbase here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/UsingRSwithSAPNetWeaver.mspx

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/UsingSSRSandESSbase.doc

I've done quite a bit of work with Reporting Services and TM1 and posted some examples to the Applix forum; do you think Microsoft would publish a whitepaper for TM1 if one was written.....

MDX, Dynamic Subsets and Server Crashes - Update

This issue has been fixed in V9 SP3 and so definately worth upgrading if you use MDX and V9 SP2.

Free Reporting Services Control - Update

I had this all working and had started to document everything . I ran into an issue though when testing which I think is either a network issue or a Windows 2003 issue.
If anyone else has some spare time and would like to help finish off this please drop me a email.
Thanks

TM1 V9 SP3

I see that v9 SP3 has been released on 4th Jan 2007 and is available for download.
What I'm not sure about though is whether V9 SP3 contains the new functionality that came in 845 e.g. enhanced message logging capabilities and the ability to allow feeders to load using multiple hyper-threads which should result in quicker load times.
I can't quite tell from the notes.

Update - TM1 Open Source Tool Kit

I managed to put in a bit of time on this over christmas and I'm not far off a version 1 to release.
There is just a couple of bugs in the xml and mdx exports to fix and its ready.
Hopefully I'll get chance to release it next week on the applix forum.

New Applix Recommended Practices

2 new articles worth reading here:

http://www.applix.com/rp/Using_and_Managing_TM1_Attributes.htm

http://www.applix.com/rp/Documenting_Dimension_Usage_by_Cube.htm

TM1 Open Source Tool Kit

Over the quiet Christmas period I am going to do play around with developing a workbook full of useful vba code for anyone to use. I aim for this to be an open source collaboration that anyone can contribute to.

I will be including hopefully:

• Examples of working with the TM1 API
• Treeviews of TM1 data
• Select a TM1 view and see the equivilant MDX statement
• Select a view and export as xml
• Dynamic form for text entry
• Example MDX reporting
• Robert Gardiner's TM1 kill module
• Anything else that people would like to include

Send me an email if anyone would like to help with the first version

vba progress bars

If you have a vba report that takes a bit of time to work out the results then you should display progress to the user so that they know the report is working and hasn't crashed.

Andy Pope's website has some fantatic progress bars that you can download and use in your applications:

http://www.andypope.info/vba/pmeter.htm

Version 8 and 9 updates

TM1 9.0 SP2 U4 and TM1 8.4.5 U1 have been released and are available on the Applix website.

Olapobjects Newsletter

Olapobjects have a new newsletter out available on their website.
Check it out here:

http://www.olapobjects.com/Documents/Newsletters/OONewsletter-Nov2006.htm