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.