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.