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.