Using the DimensionCreate Function to Create a Dimension in TM1

Often we will want to create dimensions in TM1. We might be creating a new model or recreating a dimension during a TI process. To do this we use the DimensionCreate function (not the CreateDimension function that doesn’t exist!).

Syntax of DimensionCreate

The syntax is DimensionCreate ( DimName );

where DimName represents the name of the dimension you want to create.

Example

DimensionCreate ( ‘Customer’ );

This example creates the Customer dimension in our TM1 model.

Usage

This function is often used in conjunction with an IF statement and the DimensionExists function to check if a dimension exists and if it doesn’t, then to create it. This could look like:

sDimName = 'Customer' ;
IF ( DimensionExists ( sDimName ) = 0 ) ;
  DimensionCreate ( sDimName ) ;
ENDIF ;

In this example we are setting the variable sDimName as ‘Customer’, then testing if it exists and if not, then creating it.

You can only use this function in TI processes.

Checking if a DimensionExists in a TM1 Model

Sometimes we need to check if a dimension exists in a Turbo Integrator process. We might want to then build it if it doesn’t exist or unwind the dimension if it does.  To do this is really easy with a simple function – DimensionExists.

Syntax of DimensionExists

DimensionExists ( DimName );

where DimName is the name of the dimension you want to check if it exists.

The function will return a “1” if it does exist or a “0” if it doesn’t.

Example

DimensionExists ( ‘Customer’ ) ;

This example determines if the Customer dimension exists in the current TM1 model and if it does, will return a 1.

Usage

It is often used in conjunction with an IF function and possibly DimensionCreate. This could look like:

sDimName = 'Customer' ;
IF ( DimensionExists ( sDimName ) = 0 ) ;
  DimensionCreate ( sDimName ) ;
ENDIF ;

In this example we are setting the variable sDimName as ‘Customer’, then testing if it exists and if not, then creating it.

You can only use this function in TI processes.

“Joining” Text Files in a TM1 Turbo Integrator Process

I was faced with an interesting situation yesterday. I needed to load information from a header and a detail table into TM1. So, just do a join in the TI, I said to myself. And then in the middle of the night I realised that just isn’t possible to do a join in a TI when the source data is text or csv files. This blog post deals with how we overcame the problem of how to join text files in a Turbo Integrator process.

If we were using a relational database as the source to load data from multiple tables in a single query into TM1 we would have a query on our TI that includes a join in something like this:

select header.invoice_number, header.customer_id, header.invoice_date, detail.sku, detail.invoice_line, detail.quantity, detail.sales, detail.cost 

from header, detail 

where header.invoice_number = detail.invoice_number

Note this has a join on the invoice_number field. With a text or csv file as the source, we just can’t do joins like this.

Solution to “Creating a Join” to Load from Multiple Text Files

So how did we load load data into TM1 from multiple text files as the source as if we were creating a join? It was a two step process:

  1. In the TI reading the header file we created a temporary dimension using invoice number as the element. Then we assigned attributes for customer ID and invoice date.
  2. Then in the TI that reads the detail table we simply read each record, getting the invoice number, invoice line, quantity, sales and cost and then use ATTRS to look up our temporary dimension to get the customer ID and invoice date for the current record’s invoice number. Then, we deleted the temporary dimension created in the first step.

Snappo! We were able to join text files via a Turbo Integrator process.

Data Export Tool Review – Flyspeed

Ever needed to create a text file from a data source and you have no tools available to interrogate it?

We are doing a cloud Planning Analytics project for a client where they are using an ERP called Finance Plus over a database called Visual Foxpro. Mmmm, I hear you read – why is this relevant for me? Well, Visual Foxpro is a very close relative of dBase – a popular database in the 1980s (even more “why is this relevant” sighs – stay with me!). We tried all sorts of methods (including using MS Access over Foxpro, using the ETL tools in SQL server, the ODEDB and ODBC drivers for Foxpro etc) to get data out of Foxpro, all of which failed. Eventually we decided to just do a text file extract and FTP that to the Planning Analytics server, rather than using the gateway tools provided by IBM for connection to a local database.

This then created a wee problem. How to create text files from Foxpro? A bit of digging and I found a tool called “Flyspeed Data Export“.

Flyspeed Data Export

Allows you to:

  • Create multi table exports from a variety of source databases (including ODBC, OLEDB, Oracle, SQL Server, DB2, MS Access, Foxpro, OpenXML, CSV, DBF, BLOB etc).
  • Export to a variety of targets, including CSV, TXT, XLS, XML, MS Access, dBase, Rich Text, MS Word, HTML or BLOB).
  • You can then create complete table exports of select specific fields (important for me because the source data contained multi line text/memo fields).
  • Create “projects” so you can re-open them.
  • Schedule the job to run when required.
  • Name the output files as required.
  • Format the output to use pipes “|”, rather than commas.
  • Format date and time as required.
  • Set performance options
  • Overwrite target or append a date-time stamp

Basically, Flyspeed is a great tool for easily creating text files or databases from almost any data source. No, it doesn’t have ETL or filtering, but it is great at what it does!

Note that to save a project or schedule a job you will need to purchase the professional version (cost in June 2017 was $US95) that also comes with 12 months support.Just in case you’re interested, I purchased the professional version and no, there is no affiliate fee payable for my recommendation!

Managing Applications from Command Line

In TM1 10.2, a new feature has been added to allow users to perform application maintenance using command line, or in a batch file. The main purpose of this utility is to automate some of the application maintenance, using a combination of standard TI processes and TI processes that execute commands available in this utility. Also, chores can be setup to execute these periodically, such as overnight application maintenance. This utility can be found in
the <TM1 installation directory>\webapps\pmpsvc\WEB-INF\tools\, and the main file to use is app_maintenance.bat.

Command Line Commands for TM1

Here are a list of commands that you can perform using this utility: Continue reading