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.