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.