Creating a Pick list

There are two ways of creating a Pick list.

1) Creating Pick list with Element Attributes
2) Creating Pick list with Control Cubes

1) Creating Pick list with Element Attributes

a) In the Server Explorer, right-click on the dimension for which the pick lists is required and then click Edit Element Attributes.

b) In the Attributes Editor, click Edit > Add New Attribute.

c) In the New Attribute dialog box, enter Picklist as the attribute name.

d) Select Text as the attribute type.

e) Click ok. The Attributes Editor now contains a new column titled Picklist.

f) For each element for which the pick list is required, enter a valid pick list definition at the intersection of the element name and the Picklist column.

g) Click ok to close the Attributes Editor and save the pick list definitions.

2) Creating Pick list with Control Cubes

a) The pick list can be created with control cubes. This gives control over which cube cells should contain pick list and allows flexibility in defining picklist of individual cells.

b) We can also create rules for the pick list cube, which allows us to define pick lists for any section of cube.

c) A pick list control cube is composed of the same dimensions as the regular cube and in addition it gets one more dimension named }picklist.

d) The }Picklist dimension contains a single string element, named value.

Steps

i) In the ServerExplorer, right-click the cube for which the picklist is required, then Create Pick List Cube.

ii) A new control cube is created, using the naming convention }PickList_cubename. For example, when we create a pick list control cube for the Expenses cube, the control cube is named }Picklist_Expenses.

iv) If the control cube is not displayed in the Server Explorer, click View, Display Control Objects to enable the display of control cubes and other control objects.

v) Defining Pick Lists for Individual Cells in a Control Cube

vi) Below are the steps to define pick lists for individual cells in a control cube. The pick lists defined in the control cube are used to display pick list values in the associated regular cube.

      1.Double-click the control cube in the Server Explorer. The control cube opens in the Cube Viewer.

     2.Configure the view of the control cube as necessary to view the cells for which we want do define pick lists.

     3.In each cell for which we want to create a pick list, enter a pick list definition. We can enter any of the pick list types in the control cube: static, subset, or dimension.

    4.Click File, Recalculate to recalculate the cube view.
 

Picklist

1) Pick list is a list of values for specific element or cube cell. When we set up the Pick list, it'll show a drop down menu containing of the list of defined values.

2) User can select the required value from the Picklist and if user tries to enter an element which is not listed in the pick list, system will throw an error.

Pick List Types

There are three types of pick list.

1) Static Pick list
2) Subset Pick list
3) Dimension Pick list

1) Static Pick list

a) A static pick list is composed of a colon-delimited list of values using the below syntax.

     static:value1:value2:value3

For ex; static:Newyork:Chicago:Washington results in a pick list containing the values Newyork, Chicago, and Washington when browing the cube.

b) To inlcude a NULL value in the pick list, we need to have two consuective colons as shown below.

      static:value1:value2:value3:: . This will result in a pick list with NULL value at the end.

2) Subset Pick list

a) A subset list contains values corresponding to all elements of a named subset. If the elements of the subset change, the values available in the pick list will automatically change.

b) The syntax for subset pick list is as follows:

      subset:dimension_name:subset_name

For ex; subset:Account:IT results in a pick list containing all elements from IT of Account dimension.

3) Dimension Pick list

1) A dimension pick list contains values corresponding to all elements of a dimension. if the elements of the dimension change, the pick will automatically change.

2) The syntax of dimension pick list is as follows:

     dimension:dimension_name

For ex; dimension:months results in a pick list containing all elements of Month dimension.

 

Creating a Cube

A cube stores the data in a Multi-Dimensional fashion which helps the business to analyze the data from multiple perspectives.

1) TM1 cube stores data for reports, analysis, and staging data for further calculations.

2) Data can be come from multiple sources and be arranged in a multi-dimensional format called Cube.

3) Each cube contains data, business logic, measure rules , and calculations.

4) TM1 cube should have alteast one measure dimension and one or more regular dimensions.


Creating a Cube

1) Right click on Cubes and click on Create new cube.
2) Creating Cube window opens. Enter the name of the Cube and select the required dimensions from Available Dimensions list.
3) Click > (right arrow) to bring them all into the Cube and set the order for the dimensions by clicking up and down arrows.
4) Click on Create Cube and the Cube will be placed under Cubes.
5) Expand the Cube and see the dimensions that you have selected.
6) Dimensions can be re-order even after creating the Cube. To re-order, right click on the Cube and select Re-Order Dimensions.
7) Double click to open the Cube. All dimensions under the cube will be available to browse through the data.
8) Select a Dimension and pull down to left most to view the data based on that Dimension.
9) The data can be viewed across all dimensions.
10) For any dimension, if you want to see the aggregated data, click on RollUp button. It'll sum up all the values and give us the Total value.
11) In any Dimension, if you want to filter the elements click on Filter by widlcard icon and give the value. It exactly works as a search string.
12) After selecing the dimensions view, click on Recalculate to view the data. By default Cube shows all the data values inlcuding 0s.
13) We can ignore the 0s and pull only non-zero values by clicking x0 icon on the Icon Tool bar.
14) Automatic recalculate icon is used to refresh the data automatically. We won't need to click Recalculate when we change the dimension view.
15) We can download the data into an Excel sheet by clicking on Snapshot icon.
14) After selecting the dimension view, we can save the TM1 view by clicking File->Save.
15) TM1 Save View window opens. We can either choose Private or Default to save the view.
16) Check Private if you want the view to be available for only current user. Uncheck it if you want it to be made Public. Default option will make it default view and it opens that when the Cube is opened.
17) The cube can be loaded with TI Processor.
 

Creating Dimensions using TI Process

The dimension can be created and updated through a Turbo Integrator Process.

1) Create an Excel sheet with the dimension values and save it as .csv file. (For ex. Account Dim)

Account.csv

ID, NAME, CATEGORY
100,ABC, IT
200,XYZ, ADMIN

Using TI Process

1) Right click on Process-> Create New Process.
2) Turbo Integrator dialogue box opens.
3) Choose Text under Data Source and browse to the .csv file that was created.
4) Select Comma Delimiter and put 1 in Number of title records if you have a header information in the sheet.
4) Click on Preview to see the data.
5) Click on Variables tab and choose Other for Contents field.

Ignore - Ignore the contents of the column
Element - Column is converted to leaf level
Consolidation - Column is converted to consolidated element and parent to other elements
Data - Column has data values for the cube
Attribute - Column contains element attributes
Other - Values don't belong to any of the above categories.

6) Click on Advanced tab-> Prolog. Write DimensionCreate('Account'); This will create Account Dimension Under Dimensions.

7) Use Metadata tabe to insert elements in Account dim.

8) The function
DIMENSIONELEMENTINSERT('Account','',ID,'n');
DIMENSIONELEMENTINSERT('Account','',NAME,'n');
DIMENSIONELEMENTINSERT('Account','',CATEGORY,'n');


- These will insert the elements in ID,NAME & CATEGORY fields. Account is the dim name, " indicates the previous field name, 'n' represents the simple type element.

9) Save the Process and click File-> Run to run the process.
10) The Dimension Account will be created with the all values.

Using Named Hierarchy Levels with TM1 Dimensions

We can assign own custom names to the hierarchy levels of TM1 dimension by using
}HierarchyProperties control cube.

1) This is a default control objects creates by TM1. To get this Cube click on View-> Display Control Objects.

2) The hierarchy level names get created as level000, level001, and so on. We can change them to have a proper meaning.

TM1 Dimension Level Example Named Dimesion Level
level000 All
level001 IT Accounts
level003 Admin Accounts

Congifuring Named Levels

1) In TM1 Architect, click the View menu and select Display Control Objects.

2) In the Navigation pane, expand the cubes.

3) Open }HierarchyProperties Cube. Cube opens.

4) Select the dimension for which you want to assign named levels. Click on Recalculate button.

5) In the default member cell, enter an existing element name to set as the default member for this dimension.

6) Enter the name of the top element in the dimension hierarchy to retrieve all the elements by default.

7) Enter own custom name in the level000 to level020 cells.

8) Just restart the TM1 server or Run RefreshMDxHierarchy function in TI process to get the changes.

Display of Elements in Dimension Editor

There are more options which help us manage the way elements display.

Keeping Elements

For ex: The Dimension has more elements and we want to display only few for a particular scenario.

1) Select the elements you want to display.
2) Click Edit-> Keep or Click on Keep icon directly.

Hiding Elements

1) Select the elements you want to hide.
2) Click Edit->Hide or Click Hide icon directly.

Sorting Elements Alphabetically

We can sort the elements in the Dimension Editor in asceding or descending alphabetical order.

Ascending Alphabetical - Click Edit, Sort, Ascending or click Sort Ascending icon
Descending Alphabetical - Click Edit, Sort, Descending or click Sort Descending icon

Sorting Elements by Index Value

We can sort the elements in the Dimension Order in ascending or descending according to the index value.

Ascending Index Value - Click Edit, Sort, Index Ascending or click Sort By Index, Ascending icon
Descending Index Value - Click Edit, Sort, Index Descending or click Sort By Index, Descending icon

Sort Elements by Hierarchy

We can also sort elements as they appear in the dimension hierarchy.

As they appear in the dimension hierarchy - Click Edit, Sort, Hierarchy or click Hierarchy Sort

Viewing Elements by Alias

Setting up the Alias Attribute

1) Right click on the Dimension in Server Explorer and select Edit Element Attributes.
2) Attributes Editor dialogue box opens. Create an Attribute by clicking Edit-> Add new attribute.
3) Give a name for the attribute and choose the attribute type Alias.
4) Click Ok.

All the elements will get the alias name which can be viewed in Dimension Editor. Click on Use Aliases to get the Alias Names.

Aliases names can be loaded manually or through a Turbo Integrator.

Modifying Dimensions

After creating a dimension, you can make the following modifications.

a) Add more elements.
b) Add siblings to existing elements.
c) Add children to existing elements.
d) Rearrange the hierarchy structure, such as repositioning elements within consolidations.
e) Delete elements from the dimension.
f) Delete elements from consolidations.
g) Edit element properties, such as changing the weight of an element within a consolidation.
h) Rearrange the order of elements in the dimension.

a) Adding more Elements

1) Right click on the Dimension and select Edit Dimension Structure.
2) Click Edit->Insert Element.
3) Dimension Element Insert window opens. Enter the name of the element in Insert Element Name.
4) Choose Simple in Element Type and click Add.
5) Repeat 2,3 & 4 to add more elements. After adding all the elements click Ok.

b)Adding Siblings to Existing Elements

1) Right click on the Dimension and select Edit Dimension Structure.
2) Select the element, click Edit and select Insert Sibling.
3) Dimension Element Insert window opens. Enter the name of the sibling in Insert Element Name.
4) Choose Simple in Element Type and click Add.
5) Repeat 2,3 & 4 to add more siblings. After adding all the elements click Ok.

c) Adding Children to Existing Elements

1) Right click on the Dimension and select Edit Dimension Structure.
2) Select the element for which you want to have the child, click Edit->Insert Child.
3) Dimension Element Insert window opens. Enter the name of the first child in the Insert Element Name.
4) Choose Simple in Element Type and click Add.
5) Repeat 2,3 & 4 to add more siblings. After adding all the elements click Ok.

d) Rearranging the Dimension Hierarchy

1) In the Dimension Editor, select the elements you want to move.
2) Drag and Drop the elements to their new location in the dimension hierarchy.

e) Deleting Elements from a Dimension

1) Select the elements you want to delete.
2) Click Edit, Delete Element from Consolidation or click Delete button.

f) Editing Element Properties

We can edit the element properties to assign a new weight or element type of any child.

1) Select the element in Dimension Editor.
2) Click Edit, Element Properties.
3) Dimension Element Property window opens.
4) Change the Element weight or Element type if necessary.
5) Click Ok.

g) Rearranging the order of Elements in a dimension

TM1 lets us set the order of the elements in a dimension to determine the index value for each element. The first element has an index value of 1, the second element has an index value of 2, and so on.

1) Change the order of the elements as you want them to appear in the dimension.
2) Select the elements that you want to put them in order and click on Keep. Click the Set Dimension Order button.
3) Click Dimension -> Save.
4) Click Yes to save the new dimension order.

Rearranging the order of Elements from the Server Explorer

1) Right click the dimension in the Server Explorer.
2) Click Set Elements Order.
3) The Dimension Element Ordering dialog box opens.
4) Select a Sort Type.

Automatic - Enables the Automatic Sort by options : Name, Level, and Hierarchy.
Manual - Orders elements as they currently exist in the dimension structure and sets the dimension sorting property to Manual.

5) In case of Automatic Sort -

Name - Sorts elements alphabetically.
Level - Sorts elements by hierarchy level.
Hierarchy - Sorts elements according to the dimension hierarchy.

Creating Dimension

Dimension consists of the elements and every element has a type associated with it.

The Element Types are -

Simple - Element at lowest level of heirarchy.
Example - Date

String - Elements have string data
Example - "Asset Category"

Consolidated - Element that define aggregated data in a cube.
Example - Year

Attributes are used to provide supporting data to an element.

Types of attributes are -

Descriptive Attributes - Element may have descriptive attribute.
Example - January may have Jan as a short name

Alias Attributes - Element have an alias for every attribute.
Example - Account ID 100023 may have Travel

Display Format Attributes - Numeric element may have format to display the numbers
Example - Number with the formatica 2 decimal places

a) Dimensions can be created either manually using a Dimension Editor or through a Process.

Creating a Dimension Manaully (using Dimension Editor)

1. In the tree pane of the Server Explorer, select Dimensions under the server name.

2. Click Dimensions and select Create New Dimension.

3. Dimension Editor -> Click Edit and select Insert Element.

4. A window will be opened Dimension Element Insert ->

Insert Element Name : Give the element name (Ex. India if the dimension is Country)
Element Type : Select Consolidated as if you want to have the group by values or Simple if it's a numeric value or String if it's a character.

Click Add and the element will be inserted in Dimension. Add all the elements in the same fashion to have a complete Dimension.

5. Click Save and name the Dimension.

b) The dimension gets created under the Dimensions Tree. Double click the dimension to see the values that you have entered.

c) In case you have many values in Dimension, this would be a difficult task to add all the elements one by one. Instead you can copy the values from outside of TM1 say from Notepad and Paste in the Dimension Editor.

d) In this case all the elements will be with Simple type, you can right on the element and say Edit Properties if you want to change.

TM1 Insight

TM1 - Table Manager One

      TM1 is an IBM product which helps people to forecast/budget the data such as company plans for labor, projects, travel and other expenses etc.

1) TM1 helps the business to create and maintain the Multi Dimensional database.

2) TM1 helps the business to store both actuals and forecasting data and reports to the users.

3) TM1 clients interfaces are -

* Excel add-on (TM1 Perspectives)
* Web browser (TM1 Web)
* TM1 Architect (Server Explorer)

4) TM1 has built-in ETL (Extraction, Transformation & Loading) to perform data movement from source to multi dimensional cubes.

5) TM1 can pull the data from ODBC, Text, Excel and TM1 own Cubes and it also have the capability to write back the source databases.

TM1 consists of the following objects

 I)  Dimension
II)  Cube
III) Applications
IV) Processes
V)  Chores


I) Dimension - Dimension holds the description data. Ex. What is the product code, name, category etc.,

2) The Dimension can be loaded either manually or through a process within TM1. Once the dimensions get loaded the Cubes can be loaded and the data will be shown across available dimensions.

3) Individual Applications can be created by selecting the required Dimensions & Cubes from the list and users will be able to access them.

For Example: If we want to have R&D application related dimensions & cubes in one place, we'll create R&D Application under Applications and choose the required objects under this.

4) Processes are the the ETLs(Extractions, Transformation & Loading) in TM1.

5) Chores hold the processes and schedule to run at a particular date & time.

Introduction to Multi Dimension

Multi Dimensional

Multi Dimensional is the concept of analysing the across many dimensions.

 When the data is stored in a multi dimensional database it means that the data is stored in a cube data structure. The data can be viewed and analyzed from different perspectives at the same time.

Multi Dimensional Data Structures

Dimensions - Various ways through which users want to slice and dice data
Example - Country
Hierarchies -  Similar descriptive data arranged in levels
Example - Districts, State
Levels         -  Individual level of a hierarchy
Example - Districts
Measures    - Values which can be measured for various dimensions
Example - Revenue
Cubes          - Collection of dimensions and measures
Example - Sales Cube
Members     - Individual data members
Secretariat
Attributes    - Provide additional detail for member values
Address

For example

i)  If a CEO of the company wants to analyze the product sales across a retail chain in United States. Each retail store records the unit sales and discounts for privileged customers or some products.
ii) The sales are analysed by Product, Scenario i.e. Actuals Vs Budget, Region, Measures (Metrics such as units, discounts), and Time.
iii) This is a 5 dimensional model. The dimension identify how the data is organised or types of data are tracked.

How Business Run

Every business budgets or forecasts for the upcoming year. Business Users analyze the actual numbers and come up with the forecast numbers to allocated the budget to different departments/functions with in the organization.

For example..

1) How much should we allocate to the projects next year?
2) How much labor % is going to be increased to handle the projects?
3) How many people would be required to travel to different locations to support the projects?

Once the budget is over for the selected period, the actual data start flowing into the system. The actual data may come from any transactional source such as Oracle ERP, SAP, Files etc.

At the end of day/week or month, business users compare the actuals & forecast to check how the business is doing.