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

Andrew Wiles Olap Warehousing Blog

Here is an interesting new blog http://andrewwiles.spaces.live.com/
Looks like Andrew is going to be doing a really interesting project and its going to be well worth following.
It will be interesting to see the results of MSAS vs TM1

Chats

One of my favourite sites at the moment is www.meebo.com which allows you to sign into multiple of your messenger chat programs at the same time (MSN, Yahoo, Google Talk).

Gaim is another useful program from www.portableapps.com which you can use to login to multiple messenger programs from your usb.

www.Skype.com is still the most useful piece of software though for calling and chatting.

What does this have to do with TM1?
Not a lot but if anyone wants to chat or ask questions then email me for my username on each of these programs.

TM1 and Text Commentry

There always seems to be a bit of debate about TM1 and text. TM1 is built for number crunching rather than being a text database but companies often need to store variance or budget commentry in their TM1 database.

A TM1 cell can hold 255 characters but editing of that text in Excel can prove frustrating to users as they will need to re-type everything.

To make this easier for users I put together this simple vba form with the code below. Basically if the users clicks on a Light Green coloured cell, a vba form as per above will show. They can then edit existing text or enter new text and upon pressing OK the vba will DB send the text to TM1. I tried to make the code as generic as possible so it will decipher any TM1 formula. At the moment though every reference in the formula will need to be a range rather than a hard coded element name

e.g. $A$1 rather than "Jan".

The code in blue goes as an event to the worksheet. The code in red goes behind the form.

It should be pretty easy to replicate the form or I can email an example through.

Apologies that I can't figure out how to indent my code easily in html.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 35 Then
Load inputWindow
inputWindow.Show
End If
End Sub

Option Explicit
'********************************************************************************
'** J.WAKEFIELD
'** Counter of characters
'********************************************************************************
Sub CountDown(ByVal inCounter As Integer)
' make sure comment is not more then 256 characters long
Dim intCounter As Integer
Application.DisplayStatusBar = True
Application.StatusBar = (255 - inCounter) & " Characters Remaining"
Me.lblCharacters.Caption = (255 - inCounter) & " Characters Remaining"
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** Cancel button
'********************************************************************************
Private Sub btnCancel_Click()
'close window with out sending information
inputWindow.Hide
Unload inputWindow
Application.StatusBar = ""
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** Clear text
'********************************************************************************
Private Sub btnClear_Click()
txtComments.Value = ""
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** Create variables to use to send
'********************************************************************************
Private Sub btnOK_Click()
Dim arrFormula() As String, arrDims() As String
Dim strFormula As String, strCube As String
Dim iCommaPos As Integer, iBracketPos As Integer, iNoDims As Integer
Dim i As Integer
'Find Cube name
strFormula = ActiveCell.Formula
iBracketPos = InStr(strFormula, "(")
iCommaPos = InStr(strFormula, ",")
'Get cube name
strCube = ActiveSheet.Range(Mid(strFormula, iBracketPos + 1, iCommaPos - iBracketPos - 1)).Value
'Split formula by commas
arrFormula = Split(strFormula, ",", -1, vbTextCompare)
ReDim arrDims(UBound(arrFormula()))
' Get range values
For i = 1 To UBound(arrFormula())
If i = UBound(arrFormula()) Then
arrFormula(i) = Left(arrFormula(i), Len(arrFormula(i)) - 1)
End If
arrDims(i) = ActiveSheet.Range(arrFormula(i)).Value
Next i
'Pass array to be evaluated and sent
Call SendComment(strCube, arrDims())
'Clean up
Application.StatusBar = ""
Unload inputWindow
ActiveCell.Calculate
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** TM1 Send comment
'********************************************************************************
Private Sub SendComment(p_strCube As String, p_Formula() As String)
Dim temp As Variant
Dim iMax As Integer
'Find number of dimensions to use correct send formula
iMax = UBound(p_Formula())
Select Case iMax
Case 3
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3))
Case 4
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4))
Case 5
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5))
Case 6
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6))
Case 7
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7))
Case 8
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8))
Case 9
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9))
Case 10
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10))
Case 11
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11))
Case 12
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11), p_Formula(12))
Case 13
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11), p_Formula(12), p_Formula(13))
Case 14
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11), p_Formula(12), p_Formula(13), p_Formula(14))
Case 15
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11), p_Formula(12), p_Formula(13), p_Formula(14), p_Formula(15))
Case 16
temp = Application.Run("DBSS", txtComments.Value, p_strCube, p_Formula(1), p_Formula(2), p_Formula(3), p_Formula(4), p_Formula(5), p_Formula(6), p_Formula(7), p_Formula(8), p_Formula(9), p_Formula(10), p_Formula(11), p_Formula(12), p_Formula(13), p_Formula(14), p_Formula(15), p_Formula(16))
End Select
'Catch Errors
If temp = "KEY_ERR*" Then
MsgBox "An error occurred sending comment, please contact your TM1 administrator", vbCritical, "TM1"
End If
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** Keep track of count
'********************************************************************************
Private Sub txtComments_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
CountDown Len(txtComments.Value)
End Sub
'********************************************************************************
'** J.WAKEFIELD
'** Display original comment
'********************************************************************************
Private Sub UserForm_Activate()
txtComments.Value = ActiveCell.Value
CountDown Len(txtComments.Value)
End Sub

Printing TM1 Excel Reports

Initially when I started creating TM1 reports in Excel I use to always change the page setup for printing to be either Fit to 1 page wide by 1 page tall or adjust the % until it looked right.

Nowadays I find it a lot easier to select fit to 1 page wide but blank out the page tall setting.

This

I find makes things a lot easier and more flexible.

TM1 Application and Architecture Audit

You can download a pdf by the Applix Centre of Excellence here:

https://www.applix.com/includes/dnldtrkr.asp?tbl=content_document&loc=http://ftp.applix.com/Attachments\00\00\00\42\content_document_4286_ApplixAuditChecklist.pdf&id=4286

It gives a checklist of things you should do on your TM1 server to snsure its running at its best.

XRU Files Tip

I find TM1 Rule worksheets (XRUs) as the best place to write my rule files.
They allow for easy formatting and commenting for your rules.
One other advantage is that you can keep backups of your rules by just copying your worksheet. This allows you to change your rules, test them out and if it all turns to custard, revert to the backup.
You can also have multiple worksheets within the workbook for different scenarios e.g. a rule to save for the Forecast and a rule to save for Budget time.

WildcardFileSearch

I have been testing the new Applix recommended practice below:


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

and its been working really well. What I think needs adding though is an indicator of whether there was any errors in the process.

I'm thinking the TI function below may do the trick but will need some testing:

WildcardFileSearch( Pathname, PriorFilename);

I should be able to search for any files with my TI process name and then return that to my zLogging cube. Worth a go I reckon.

MDX, Dynamic Subsets and Server Crashes

I recently got asked to look at a site that was experiencing TM1 server crashes.
I had a look and found that on an MDX report that ran in Excel, it was referencing a dynamic subset. This dynamic subset was producing empty results due to changes in their structure.

Essentially whenever they ran this Excel MDX report it would crash the server instantly. As soon as I fixed the Dynamic Subset, the MDX report worked fine and the server is stable again.

I guess the lesson is to check that your dynamic subsets are never producing empty results.

TM1 Certification???

I notice Microsoft is introducing a BI certification which led me to wonder shouldn't Applix introduce some sort of certification program to prove that TM1 people have proven skills????

Just a random thought....

TM1 8.4.5

It looks like version 845 was released on monday 6/11/06
Two things that stand out for me are the enhanced message logging capabilities and the ability to allow feeders to load using multiple hyper-threads which should result in quicker load times.

I would imagine these will be included in V9 SP3 which will hopefully be out before the end of the year.

Loose File Installs

I was a bit worried that from version 9 it wouldn't be possible to do loose files upgrades but on the Applix documentation website there is now a full guide to doing client upgrades:

http://www.applix.com/support/documentation.asp

Protecting worksheets...

One feature that I find very useful when building applications is the ability to protect only the user interface.

I.e.

Sub Protect_sheet(S As Worksheet)
S.Protect UserInterFaceOnly:=True
End Sub

This is incredibly handy as it enables us to make modifications to worksheets via vba (without having to toggle sheet protection off and on all of the time).

Workbook Events VBA

There are 2 events at least people need to capture in TM1 frontends:
1) Stop the delete key so people don't delete the DBRW formula
2) Stop an F9 and instead do a shift plus f9

The code below in the workbook events module will help with this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnKey "{DELETE}"
Application.OnKey "{F9}"

End Sub

Private Sub Workbook_Open()

Application.OnKey "{DELETE}", "delKey"
Application.OnKey "{F9}", "+{F9}"

End Sub

Sub delKey()

MsgBox "The delete key has been disabled. Please use the space bar instead", vbExclamation

End Sub

Excel Hidden Sheets

If you want to hide worksheets from users (maybe some lookup ranges for use in drop downs) then you can set the visible property in the VBE (Alt+F11).

TM1 and Excel Insert Function

I quite often use the Excel Insert Function (fx button) to quickly create TM1 Excel formulas.
Normally I just select the TM1 category from the drop down list but you may notice that if you have All selected and scroll down the list of available functions you will find a whole load of other TM1 functions.
They all seem to be something to do with the API but you never know there may be something useful in there.....

TM1 Naming Standards

Everyone had their own naming standards for cubes, dimensions etc but I have a few that I try to stick to when building applications

1) Don't use Month and Year as dimension names because these are reserved words in TI and it gets frustrating having to rename your variables each time. I tend to use Period and FiscalYear for GL type data.

2) Try not to have spaces in your names. Instead use underscores or capitalisation.

3) Don't name your dimensions the same as your cube e.g. a cube called Programmes with a dimension called Programmes

4) Make the names of the dimensions meaningful to users. Do not use Relational table column names.

5) Almost every cube you create will have a Measures dimension. Try to have the dimensions with a naming standard e.g. GL_Values, HR_Values

It would be useful if other people comment their ideas for naming standards as well.

TM1 .blb files

Within your TM1 data directory you may find some files with the extension .blb
These files actually store the formatting of your .rux files.

e.g.
If you maintain your rules in backend TM1 (rather than Excel worksheets) then you may have GL.cub and a GL.rux
If you format the text in the .rux to be font size 12 and blue than a GL.blb will appear storing that formatting. Sometimes you may create one without realising it though.

I actually find blb files to be a bit annoying and I have had issues with them, so I tend to delete when I see them.

Personally I think rules are easier to maintain in Excel worksheets but I occasionally use backend rule files on my control cube rules.

Transaction Log

Something that may not be obvious when you want to back out some transactions is that you have to select the transactions first.

Select the transaction you want to back out and go Edit > Select. The transaction will then have a tick against it. Once a transaction has a tick against it the Back Out option on the edit menu will become available.

Alternative to SUBNM

If you want a user to select an element from a TM1 dimension from your Excel front end then you can use a SUBNM and get a user to double click the cell or....

put a transparent text box over the subnm cell and assign a macro like this

Sub Dept()

Sheets("Menu").Range("Dept").Select
Application.Run Range("[tm1.xla]tm1!ENTER")

End Sub

This allows a user to only single click to get to the subset editor.

New Recommend Practice Article

There is an interesting new post on the recommended practices website about logging info on TI processes:

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

I like it but would prefer it if more of this stuff was inbuilt in the application.
For example on error, email the dba.
I realise Paul Simon's API can program this but its so much neater and easier if its inbuilt.

Free Reporting Services Control - Update

Well I have got it working after a bit of investigating round the web.
The trick was to issue an MDX statement and return it to a Dataset which was then used in the report.
So you need a bit of techie knowledge but I think it will achieve what i was after, in terms of a free web reporting capability for TM1 based off MDX.
MDX gives you so much control over the data that its great for reporting.
Hopefully I'll get round to documenting the whole process this week and post on the Applix forum.

Free Reporting Services Control

On the Visual Web Developer website you can now download a control that effectively gives you reporting services capability for free:

http://msdn.microsoft.com/vstudio/express/vwd/download/

I have used the full reporting services successfully against TM1 so I wonder whether I can use this free control as well.

I'll give it a test and let you know.......

More useful free software

If you need to edit screenshots or pictures for use in your Excel templates or TM1Web than the best thing to use is Irfanview.

Download here http://www.irfanview.com/

Traffic Lighting

Sometimes people like to see traffic lighting as indictors of performance on reports. One easy way to do this in Excel is using Webdings and conditional formatting.
1. Type n into the cell you wish to have traffic lights on
2. format the cell to be Webdings (this will show a circle)
3. Apply conditional formatting using the picture as a guide.

Subset Cut and Paste

In the subset editor you can change the order of elements by dragging and dropping the elements.
Sometimes this can be a little awkward though and I actually find it easier to right click the element, select cut, and then use the Paste above (or below) option to get the elements in the order that I require.

TM1 LDAP

I find LDAP authentication quite easy to setup on TM1 with just the following cfg parameters:

IntegratedSecurityMode=2
PasswordSource=LDAP
LDAPPort=
LDAPHost=
LDAPSearchBase=
LDAPSearchField=
LDAPWellKnownUserName=
LDAPWellKnownPassword=

I find that if I have this setup though I can't use the admin account because it's not an LDAP account. Unless I'm missing something, its a shame that the TM1 admin account is not excluded from this rule.

What Web product do you use with TM1?

TM1 Version Poll

Useful software when working with TM1

These useful (and most importantly free) pieces of software below are really useful when working on a TM1 project.

http://www.primopdf.com/

Allows you to write to pdf. Great for User guides or using in the TM1 report manager.

http://www.foxitsoftware.com/pdf/rd_intro.php

Allows you to read pdf files and no install required. Means you don't need adobe with the annoying popups. Need it to read the TM1 manuals

http://www.toadsoft.com/

Database querying tool. A must for when you hook up TM1 to oracle and is better than SQL enterprise manager when viewing sql databases.

TM1Web & Drawing Objects

Be careful with your use of the drawing toolbar if you are publishing to TM1Web.
Not all of the components will be supported.
One that is definately not supported at the moment is the shading option, these objects will just disappear on the web.

TM1 Indents

One thing I keep hoping Applix will do is to putting hierarchy indenting into the cube viewer. It should also be automatic within dynamic slices.

If you look at xlcubed or Temtec (which Applix just purchased) they always show the hierarchy as indented when you drill down on consolidations.

The InSpreadsheetBrowser has been doing it for years and I'm sure every user would like to see it in the cube browser and when they slice a view out to excel

TM1 and Dates

I find the best way to store dates in TM1 is by using numeric elements rather than string.
If you use numeric elements then the figure will be stored the way excel stores dates e.g. 38991
You can then format this figure so that it appears like 01/10/2006 in Excel through cell formatting and backend in TM1 through element formatting.
Storing them this way makes it a lot easier to minus or add dates to each other.

VBA - Modules

You can actually drag and drop modules, forms and class modules between your excel workbooks in the Visual Basic Editor.
Lot easier than exporting and importing.

MDX Usage Poll

Referencing Subsets in TM1 formulas...

One handy feature of TM1 is the ability to reference not only elements but also subset names in TM1 formulas.

For example, if we have the subset called "Fast Cars" (on a cars dimension), which contains the elements "Porsche" and "Ferrari," we can pass our DBRW formula the actual name of the subset (instead of the individual element names).

That is:
=DBRW("tm1server:Cars", "2007","Fast Cars", "New Zealand","Car Numbers")

Instead of:
=DBRW("tm1server:Cars", "2007","Porche", "New Zealand","Car Numbers")

This will give us a rollup of the 2007 Car numbers for both Porsche and Ferrari in New Zealand.

I have found this feature to be incredibly powerful, especially when filtering is applied to the subset based on an attribute value.

Notes:
- Be weary of referencing subsets containing both consolidated elements and their children, as values will be counted multiple times
- Subsets cannot be referenced as elements in MDX queries

Expand Above

If you want your hierarchies to operate downwards (consolidations at the bottom like the way you would layout an Excel spreadsheet) then use the Expand Above option.

Worth mentioning that in your subsets it will remember the options you choose.
e.g. turn on the alias, TM1 will always show the alias, turn on Expand Above the subset will keep the epand above.

TM1 Pick

If you have a template for your reports with Corporate colours and layouts etc then instead of starting with a slice and formatting it, you probably start with a worksheet and add TM1 formulas to it.
In this case the TM1 Pick function becomes very useful .
By right clicking on a cube or dimension it copies the server:cubename or server:dimension to the clipboard. This can then be pasted into your excel worksheet and you then create your DBRW formulas.
Its especially useful if your typing/spelling isn't too good!

Yet Another Excel Tip

Press "ctrl + ~" to toogle between formulas and cell values in Excel... I find this really useful when working with a large worksheet full of TM1 formulas.

TM1 Recommended Practices Repository

If you haven't seen the TM1 Recommended Practices Repository then do have a look as there are some really useful articles there.

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

I would recommend:

• Protecting TM1 Excel Data Entry Templates and Reports
• Using Conditional Formatting to Identify Editable Cells in TM1 Spreadsheets
• Using MDX Parameters in Dynamic Subsets
  

TM1 Bug Tracking

No TM1 project is without a few bugs.

I recommed using one of the free bug tracking websites avilable to track them between your team.

http://www.basecamphq.com/

http://getbugjuice.com/

http://16bugs.com/

TM1, OLAPOBJECTS & XCELSIUS

Great to see OlapObjects have announced integration of Xcelsiusinto their web product offering.
Version 5 was looking cool when I played with the demo.

www.olapobjects.com/

I wrote an article on the xcelsius website showing how anyone can hook up Xcelsius to live TM1 data here:

http://www.bobjects.com/email_imp/partners/nl_xcelsius/article5.html

You can also do extracts from TI to xml and then show in Xcelsius for live dashboarding.

TM1 & TEMTEC Initial Thoughts

I was really surprised and excited when Applix bought Temtec.
It follows the consolidation of the BI market trend e.g. Microsoft buying Proclarity

The thing I always loved about Temtec was the speed. Its the fastest web product that I've seen.
It also had the best printing capability I've seen of an OLAP web product.
It relies on a small client install through the web browser though.

Be interesting to see how they integrate TM1Web with Temtec.

Temtec is great for adhoc querying but relies on ODBO for writeback so I expect them to put some heavy work into making Temtec integrate with the TM1 API.

The History of TM1

Check out this webpage for an interesting histroy of TM1

http://www.vectorspace.com/2006/08/08/tm1-origins-i

Excel Dashboard

If your looking for some nice ways to do dashboaring in Excel or maybe TM1Web then this website gives some ideas:

http://www.excelling.it/excel/dashboard_en.asp

Bullets and Sparklines

Ther's a few BI tools saying they can do fancy graphs like Sparkline sand Bullet Graphs but ExcelUser shows you how you can do them in Excel and therefore dynamically off TM1 data.

http://www.exceluser.com/explore/bullet.htm

http://www.exceluser.com/dash/sparkintro.htm

There may be a small fee involved in some of them.

Citrix cfg parameter

In verision 9 SP2 you can use a new parameter ReceiveProgressResponseTimeoutSecs=20.
As I understand it Citrix can time out a TM1 session unless you put this parameter in so I would rather have it than not.

Default Subsets

I prefer not to have dynamic subsets as my default subset due to the annoying alerts that pop up to users.
To get round this I create a dynamic subset and then base my default subset off it in my load process.
So in my TI process that loads the departments I place this code in the epilog:

SubsetDeleteAllElements('Product', 'Default');
x = 1;
While(x <= SubsetGetSize('Product', 'CORPPRODUCT'));
vElement = SubsetGetElementName('Product', 'CORPPRODUCT', x);
SubsetElementInsert('Product', 'Default', vElement, x);
x = x + 1;
END;

It loops round the dynamic subset called CORPRODUCT and recreates the default subset from it.
The code to produce the dymanic subset is:
{TM1DRILLDOWNMEMBER( {[PRODUCT].[CORPPRODUCT]}, ALL, RECURSIVE )}

You can exchange the name CORPPRODUCT for your top level element you want to drill down on.

PrivilegeGenerationOptimization=T

I had a lot of success recently putting this paramter into the TM1 cfg file.
A security refresh used to take 20mins but by adding this parameter and feeding everything correctly it reduced to 3minutes. Well worth it.

Drill Thru Tip

When creating a drill through process you will most likely need to do some coding in the Prolog section of the TI process.
If a user does a drill on an element which shows an alias you will probably need to convert this name back into a code which the underlying database system recognises to use in a SQL statement.
I therefore use a DIMIX and DIMNM formula to return the element.

e.g. Costcentre = DIMNM('Costcentre', DIMIX('Costcentre', Costcentre));

} Cubes

If you want to hide a TM1 cube from a normal user you can easily just name it with a } at the front.
It will then be treated as a control object and only viewable if you select the option to see them.

TM1Web Recalculate

Something that may not be obviuos but I think very important is that the recalculate button in TM1Web does the equivilant of F9, so if you have a large spredsheet you may get slow performance. Would seem to me to be best to split everything into seperate workbook with hyperlinks.

Dimension Editor Wierd Behaviour

I recently saw this happen in v9 SP2.

Go into the dimension editor, change a consolidation weighting from 1 to -1 and press save.

I have seen this fail if you have an alias on and also if you highlight 1 press delete and then type -1.

MDX Intro PDF

The link below is for a chapter sampler of Chriss Webb's new MDX book. The sampler is a really useful intro for people interested in learning mdx.

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

http://media.wiley.com/product_data/excerpt/80/04717480/0471748080.pdf

Data Visualisation Podcast

This link is a podcast of Nigel Pendse (author of OlapReport) talking about best techniques for Data visualisation.
Worth listening to if you are designing graphs off your TM1 data.

http://www.olapreport.com/podcast/Visualization.mp3

843 Rule Worksheets

There is a bug in 843 where if you have an error in your rule worksheet then the meesage box tells you the wrong line number for the error.
This is fixed in 9.0

More Excel Shortcut keys

ctrl + 1 = show format cells dialog box

crtl + 9 = hide row (good for quickly hiding paging dimenions on TM1 slices)

Excel F9

Did you know that you can highlight a portion of an Excel formula, press F9 and it will show you the result of the calculation.
I find it really useful when dealing with lots of nested IFs and Indexs etc

Data Spreading Quick Keys

One of the features I use more and more nowadays is the Data Spreading quick keys.
For example I want to put the figure of 50 in 12 months on my budget template. I just type
r>50 into the cell and it repeat the figure 50 to the right until it finds a non DBRW cell.

You can then use other quick keys like P%>110, which is percentage increase right of 10%.

Check out the client guide pdf for all the quick keys and explanations.

M_Clear

If you are using VBA with Excel to do any looping round dimensions or subsets then its a good idea to use the M_Clear macro to clear the memory on the client side.
For example if I execute a TI process through VBA and the TI process updates a subset, if I then try to loop round that subset in VBA code I might get wierd results.
This is where I would use Application.Run("M_Clear") to clear the memory on the client side.

Excel 12

Have a look at this link to get an idea of how Office 2006 will look:

http://channel9.msdn.com/showpost.aspx?postid=114720

It will be strange to see the Excel TM1 menu in a "Gallery".

Data Validation Lists

If you use Data Validation Lists in your TM1 Excel reports you may have tried setting the list to refer to cells that are not contained in the worksheet that you want the validation list to appear in. In fact when you try to navigate to a different worksheet Excel just refuses to do anything.

You can though get the Data Validation List to use cells on a different worksheet by giving them a Range name and then setting the source to be equal to that Range name. This way you can just have one worksheet to contain all of your lookup values.

MDX

MDX combined with TM1 is a very powerful solution.
MDX may seem complicated to begin with but in fact the basics are pretty easy to get your head around. The best places to start are:

http://www.mosha.com/msolap/mdx.htm

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxbasics_04qg.asp
If anyone wants to learn more about how to use MDX with TM1 then drop me an email at james.wakefield@cortellgroup.com