PANTA RHEI

"bubbles" © Danuta Szyszko (www.photocase.com)

HandBook | Module 1

Table of Contents

General Introduction

The structure of TM1

Cubes

Dimensions

Rules

Processes

Chores

Applications

TM1 Perspectives Explorer

TM1 Perspectives Explorer's structure

First steps with TM1 Perspectives Explorer

Start Applix TM1 Perspectives

TM1 Menu in Excel

Menu bar and toolbar of the Server Explorer

The Menu Bar

The Toolbar

Cubes

Cube Viewer

The Menu Bar

The Tool Bar

Working with the Cube Viewer

Creating and Saving Views

Creating a Slice

Creating a Snapshot

Dimensions

Composition of dimensions

Subset Editor

The menu bar

The Tool Bar

General Introduction

The structure of TM1

TM1 provides the opportunity to develop and represent multidimensional data models. You work on cubes, which are composed of several (up to 256) dimensions. These cubes can be related and linked by rules and they can be created and edited by processes.

Now we will have a look at following key words:

Cubes

Multidimensional

Composed of up to 256 dimensions

Contains data

Cube Viewer enables the graphical representation

Dimensions

Represent criteria for the analysis of the model; for example: time, region, scenario, sales, model

Components of the cubes

One dimension can be used in different cubes

Composed of single, consolidated or string elements

Elements in dimensions can be described by attributes

Subset Editor enables the graphical representation

Rules

Calculation rules

Rules for data processing in cubes

Create relations between several cubes; for example: multiplication of two cubes

Processes

Automated workflows for data processing; for example: filling cubes; creating cubes, dimensions, attributes

Tool is the Turbo Integrator

Chores

• Aggregation of several processes in a chronological order

Applications

Applications help you to arrange TM1 objects in logical and work specific groups

Application-objects can be uploaded on the server or added as a reference. If a source object or file is deleted, the reference remains.

A TM1 application can contain references of every type of TM1 objects, exept for rules and replications

Every Excel file, which is referenced in a TM1 application, can be automatically made available in TM1 Web

TM1 Perspectives Explorer

In the following part we will learn more about the TM1 Perspectives Explorer.

TM1 Perspectives Explorer's structure

The window of the Server Explorer is divided into two sections. The section on the left hand side represents a hierarchic structure of all cubes, dimensions and processes, which are available on this server. By pressing the plus or minus button the subordinate elements can be displayed or hidden. In the window on the right (Properties Window) TM1 provides important information, such as the server you are working on, the dimensions that compose the cube and the subsets that are assigned to a dimension.

In the following part you will learn how to work with the TM1 Explorer and how to create views.

First steps with TM1 Perspectives Explorer

Start Applix TM1 Perspectives

Open your Windows Start Menu and expand All Programs.

Choose Applix in your Windows Start menu and afterwards TM1. Then launch Perspectives for MS Excel. Now you should have the following view on your desktop:

As you can see, you have an additional menu item - TM1 - in the Excel menu bar, as well as an additional toolbar. This toolbar includes many useful TM1 tools. But now let's have a look at the TM1 Menu.

TM1 Menu in Excel

• Server Explorer. opens the Perspectives Server Explorer.
• Save workbook on TM1 Server . here you can load new application files on your TM1 Server or update existing files.
• In Spreadsheet Browser. opens the In Spreadsheet Browser
• Insert Action Button. navigation between Excel-sheets / start a Turbo-Integrator process (9.1 or higher)
• Dimension WorkSheets . here you can open, save or create Dimension WorkSheets
• Rule WorkSheets . here you can open, save or create Rule WorkSheets.
• Edit Formula . this point is for editing a formula (more about this later)
• Pick . here you can select an element, a cube or a dimension.
• Process Data . here you can process data, objects or examples.
• Data spread . here you can spread data on several elements.
• Holds . exclude cells from data spreading operations.
• Network . here you can connect and disconnect to the server.
• Options. have a look on following screenshot:

After starting TM1 Perspectives, open the TM1 Options, in order to assure that you are working on the right server. In Local Server Data Directory you can see the directory to which you are having access to right now.

If you want to print PDF Files of your reports you have the possibility to choose PostScript Printer. (9.0 or lower)

During the training the checkmark before Connect to Local Server on Start-up should remain. When you start Perspectives, a connection to the local server is established automatically.

Start TM1 as described above. After opening Excel, select Options in the TM1 Menu and make sure that you are working on the right Server Data Directory. During the training the local Server Process Execution Mode should be an independent Process. Accept the settings by pressing OK.

• Print Report . here you can print PDF reports, more about this in Module 2.
• Help . here you are forwarded to the TM1 Help Menu, where you have the possibility to search several keywords and their explanations.

Menu bar and toolbar of the Server Explorer

Now you should open the Server Explorer with TM1 Server Explorer. Then you will see the following window on your screen:

The Menu Bar

Here you have another menu bar.

• File . here you can see the point Options. This is exactly the same as we have seen before in the Excel Menu. Furthermore there are the sub-items Shutdown Local Server, Refresh Available Servers and Exit. Exit closes the Server Explorer.
• TM1 . here you have the sub-item Save Data All.
• Edit . at this point you can copy and delete.
• View . when you open this menu point you can decide - by setting a check mark - what should be displayed (Status bar, Toolbar, Properties Window, Applications, Cubes, Dimensions, Replications, Processes, and Chores). Moreover you can Expand all children. This means that you can see all subordinated items in the server explorer. When you put a checkmark next to Collapse all children the server explorer closes all subordinated elements. Display Control Objects shows you all cubes that are in this directory. The Control Objects are marked by a left curly bracket. You can also Refresh (F5) the view.
• Help . it opens the Help Menu and informs you about TM1 Perspectives.

The Toolbar

On the right hand side you can see the properties window that - in case - can be hidden. You can show/hide it with View Properties Window or by pressing following buttons on the toolbar:

To hide the properties window

To show the properties window

The other icons have following functions:

Copy

Delete

Collapse all Children

Expand all Children

Now please collapse all children and then expand all children again by the two ways shown. Therefore you have to select a cube, where you want to open or close all subordinated elements. Furthermore you have the possibility to show or hide elements by clicking on the Plus or Minus Sign. Then display the control objects and undo it afterwards.

Cubes

Cube Viewer

The Cube Viewer is a tool for open, configure and save views of TM1 data in cubes. The Cube Viewer includes features to print views and create slices and snapshots out of views.

Open the Cube Viewer for the Sales Cube by making a double click on Sales Cube in the left panel and the Cube Viewer window will appear with the cube's system default view.

In the title bar you can see the path, so that you know in which cube you are working. At the time you can see no data. Press F9 or click the following button.

Recalculate (F9)

Now cell values should be displayed. Like in the TM1 Server Explorer, you can enable or suppress the display of children by pressing the plus or the minus sign. Create now following view:

The Menu Bar

The menu bar here is similar to the menu bar in TM1 Server Explorer.

• File
• Open . here you can open a saved view. Click on the button Open. Now a window appears where you can choose different views - in our case Default_Actual. Confirm with OK.

• Reload . click here, if you want to see the original saved view without any changes that you have done. For changing Title Elements, click the down arrow next to the element name - in our case Argentina - and choose Brazil. TM1 clears the Cube Viewer grid. Press F9 or Recalculate. If you click now on File→Reload, the original view with Argentina is displayed.
• Save . choose in the first array, Variance instead of Actual and click File→Save. TM1 opens a window and asks if you want to overwrite the existing view. Click No. Then write in the white array Default_Variance and set a checkmark in front of Private. Now you have saved a private view and if you have a look in the row on the top, there is written:

A Private View is only available for the user, who created it. In contrast, Public Views are available for all users on a remote Server.

• Save as . look at Save.
• Delete Views . you need this point to delete saved views.
• Recalculate . the same as the Icon
• Slice . creates a slice
• Snapshot . creates a snapshot
• Close . closes the Cube Viewer
• Edit . for undoing, cutting, copying, deleting and pasting values, as well as for copying unformatted value and editing cubes attributes you have to press here.
• View . in this menu point you can decide - by setting checkmarks - if the Tool Bar and the Status Bar are displayed. Please hide the Tool Bar and then the Status Bar. Then show the Tool Bar and hereafter the Status Bar. Also set the checkmark next to Right to left and then give it away again.
• Options . in this menu you can suppress the zeroes either on the complete grid, on rows or on columns. Furthermore, you have the option to activate Automatic Recalculate.

Please choose the region United States. Press Recalculate. Now only zeroes are displayed. If you click now on Suppress Zeroes, the following window will appear.

Aside from the zeroes you also have these options: Format, Column Width, Expand Row header Width, Slice to new WorkBook and Classic Slice Mode.

• Help . identical with Help in the Server Explorer.

The Tool Bar

or Save Private or Public View

or Reload Private or Public View

Choice of Views

Create a Slice

Create a Snapshot

Suppress zeroes

Layout right to the left

Layout left to the right

Switch on automatic recalculate

Switch off automatic recalculate

Working with the Cube Viewer

The Cube Viewer has two important functions: Slices and Snapshots. They convey loaded data from a Cube into an Excel Sheet. But before you can create Slices and Snapshots you have to understand how to save and create views in the Cube Viewer.

Creating and Saving Views

If you open the Cube Viewer, you receive a specific View.

At this point we will explain how Views can be changed and saved by stacking dimensions and by choosing other elements. Close the Cube Viewer for the SalesCube and open the Cube Viewer for the SalesByQuarterCube by double-clicking on this Cube in the TM1 Server Explorer. After that the following window appears:

In this view you have following dimensions:

• Title dimensions: actvsbud, region; model
• Row dimension: account1
• Column dimension: quarter

Dimensions are stackable in the Cube Viewer. Press now F9. Now values should be written in the grid. Arrange the dimensions in the following order:

• Title dimensions: account1, model

• Row dimension: region
• Column dimension:  quarter, actvsbud

and choose following elements:

• account1: Units
• model: S Series
• region: World, Europe, Americas
• Quarter: Year, 1 Quarter, 2 Quarter, 3 Quarter, 4 Quarter
• actvsbud: Variance

If you move the mouse pointer over a particular element you can see the name of the dimension. (see below).

In the CubeViewer Dimensions can be moved and interchanged by Drag and Drop. Below you can see an example for moving the dimension "region" from the title dimension to the row dimension:

For interchanging dimensions, move the dimension over the dimension to replace and release:

Now build with your new abilities a view which is identical to this one.

Save the view as a private view under the name: Units_Variance_SSeries. (Private views are available only to the user who created them. In contrast public views are available to all users on a remote server who have at least Read access to the associated dimensions. Only a TM1 administrator can create public views.)

For practice create another view - open the Cube Viewer for the Price Cube

Try to create following view by yourself and save it as Actual_SSeries_Americas.

You can save this view as private or public view , whereas private view implies that only you can see and load this view.

or Save Private or Public View

Creating a Slice

You can use the Slice option in the Cube Viewer to copy TM1 cube views into an Excel worksheet. Create following view:

Close the Cube Viewer and open it one more time for the PriceCube The PriceCube has only four dimensions: actvsbud, region, model and month.

Press F9. The title elements are actvsbud and region, model is in the column and month in the row. Click on actvsbud and drag it to the left in the column, drag month to the right in the row and model to the top behind region. Afterwards choose Europe for the region and press Recalculate. Press the plus sign next to Year.

Save this view as Price_actvsbud_Europe

To create a slice from the Cube Viewer, choose File→Slice or press the Slice Icon. The sample view from above looks like this when it is conveyed to an Excel worksheet.

Save this Excel worksheet as Slice_ Price_actvsbud_Europe into the data directory \xls.

With TM1 slices you have the possibility to modify the Excel worksheets without changing the values and the functions behind the values. Select the cells A6 - A11 and cut them. Select the range E6 - E11, choose Edit→Paste. The elements that you have selected are pasted into this range. Now cut the cells B6 - C11 and paste them into A6 - B11 Afterwards cut E6 - E11 and paste this range into C6 - C11. When you now press F9, you can see that the values haven't changed. You can modify the format of the generated Excel Sheet but you will see that the functions and values stay the same. In the end you should have a similar sheet on your screen. Save the slice and close the file.

Creating a Snapshot

We return to the TM1 Server Explorer and open the Cube Viewer for the SalesCube. Here open the saved view Default_Variance. Then click on the icon for snapshot or File→Snapshot. The following Excel worksheet appears:

In a snapshot the data cells do not contain formulas. A snapshot differs from a slice in that it contains actual values, not functions that deliver values. Accordingly the values in a snapshot do not update when cube data changes. Save this worksheet as Snapshot_Sales_Default_Variance.

The slice and the snapshot can - in contrast to the In-Spreadsheet Browser - be printed or saved as a PDF File.

Dimensions

For representation of data in a multidimensional cube we first need the structure of the data model. That's why we will now explain the composition of dimensions.

Composition of dimensions

Dimensions are composed of elements.

Types of Elements:

• N - elements: simple numeric elements
• C - elements: consolidated elements
• S - elements: text elements (string element)

Subset Editor

The Subset Editor is one of the most important tools for working with TM1. Here you can create, modify and save subsets.

Open the TM1 Server Explorer by making a double-click on the dimension region. Then TM1 opens the Subset Editor.

The Subset Editor is divided in two sections. The window on the left represents a hierarchic order of all elements belonging to the dimension. The window on the right hand side is a properties window. It gives you more information about the properties of each selected element - for example: is it a consolidated element or not; the weight, the index number.

The menu bar

The menu bar is similar to the menu bar of the Cube Viewer.

• Subset
• Open . here you can open a saved subset. Click Open. Now a small window appears on the display where you can choose the subset that you want to open. Choose Europe and press OK.
• Reload . this point reloads the saved subset. Select the plus sign next to Central Europe on the left hand side. Two N-elements appear: France and Germany When clicking Subset → Reload, the originally saved subset with Central Europe and Southern Europe is displayed again.
• Save . press the plus signs next to the C-elements on the left hand side. Afterwards click Subset Save. The following window appears:

TM1 asks if you want to overwrite the existing subset definition. Please press No. Then write in the white array Europe_Detail and set a checkmark in front of private. Confirm with OK.

Now the title bar looks like this:

• Save as .see above.
• Close . closes the Subset Editor.

• Edit . this function is specified later in this manual (see tool bar)
• View . by setting checkmarks next to these menu elements you can decide whether or not the menu element is shown. Here are the Status Bar and Properties Window displayed. In a next step you should hide the Status Bar and the Properties Window. Then set a checkmark in front of Expand Above. As you can see, the subordinated elements are now displayed above the consolidated elements. You also have the function Refresh (F9).
• Tools
• Record Expression . here you can define a dynamic subset. Therefore, you open the Subset Editor for the dimension region. Choose the subset Total Region. Now the Subset Editor displays all elements of this dimension. Click on Tools → Record Expression and press this icon:

Select by levels

Now TM1 opens this window:

Choose 0 and confirm with OK. Now only the N-elements are displayed. These are the elements that have no subordinated elements. Click on the following icon:

sort descending

Now the following should be displayed on your screen:

o       Stop recording . choose Tools → Stop recording. This window appears:

Press Yes and save the subset. Now the dynamic subset is ready. When opening the subset you can see a sign for a filter next to the name.

In the expression window, that can be displayed with View → Expression Window, the following command is written:

This means, that each time you open this subset a process starts. This is useful because changes in the database are considered in the subset.

o       Clear Expression . here you can delete the expression that we have generated. This entails that the process does not start and so the dynamic subset becomes static.

If you want to delete the expression press Yes and the expression window is blank. Now you can overwrite the existing subset.

If you confirm with Yes, the filter-symbol next to Total Region disappears and you have transformed the dynamic subset into a static one.

o       Filter. this function will be explained in Module 2.

Help . same as the Help menu in the Cube Viewer

The Tool Bar

Open the subset Europe_Detail that we have created previously. Click on Central Europe.

In the tool bar you find the same functions as in the menu point Edit.

Show all elements

or Save private or public subset

or Reload public or private subset

Selection of subsets

Cut

Copy

Insert

Undo

Redo

Delete element

Keep element

Sort elements ascending

Sort elements descending

Sort elements hierarchic

Sort elements by index, ascending

Sort elements by index, descending

Sort elements by levels

Select by regular expression

Select by attribute

Select by view extract

Drill Down

Roll Up

Collapse

Expand

Display properties Window

Expand all elements

Switch on/off Aliases

Edit element formats

Create now this subset with the above mentioned tools:

As you can see, when the aliases are switched on, the names of the states are displayed in German. Save this subset as Europe_Detail01.

Note:

In the Subset-Editor you can also choose the elements on the basis of existing lists. If you have, for example, an existing list of the countries to be selected in the form of a saved text or excel file, you can copy this list and paste it in the Subset-Editor.