Sunday, February 25, 2007

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

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.

Monday, February 12, 2007

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.

Thursday, February 08, 2007

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.

Friday, February 02, 2007

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.

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:

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:\00\00\00\14\is_document_1459_ApplixStatementonDaylightSavingsTimeChangesin2007.pdf