Renaming Elements in Cognos TM1 Dimension

Reanming Elements in a Dimension:

We can rename an element in a TM1 dimension by using a function called SwapAliasWithPrincipalName

Steps:

1. Create a new alias "new" in the Dimension, by default the new elements are identical
2. Change all required elements to their new names in the "new" created alias
3. Create a new TI process with the following line in the Prolog:
• SwapAliasWithPrincipalName(Dimension,'new',0);
4. The third parameter needs to be zero to execute the swap. If it has any other purpose, please edit/leave a comment

Backing out changes in Cognos TM1

Backing out Changes (Where Possible)
Under specific circumstances data changes made to a cube can be ‘Backed out’ via the TM1 Transaction Log. In order for this to be possible the cube must have logging enabled (“}cube_properties” control cube).

Steps to back out data changes:

1. Right Click on the Server within “Server Explorer”
2. Select “View Transaction Log”

3. Select the Date you wish to search from
4. Select the Cube and/or User (who’s actions you wish to reverse)
5. Execute the Search
6. Highlight the items you wish to “back out”
7. Under “View” click “Select” and click "Back out"

Creating a cube through TI process with new dimensions

Please follow the below steps to create a new cube through TI process with new dimensions:

1. Select process and then select ‘Process\Create New Process’ as shown in the below screenshot

2. Now Turbo Integrator window will be opened
3. If your source is an external database (For Ex: SQL SERVER), click on ‘ODBC’ as your datasource type under ‘Data Source’ tab inside the TI process.
4. Click on Browse and select the ‘Data Source Name’ (which should be created before selecting the datasource name, create a DSN for the database which you are going to use).
5. Write the query to fetch the data from the database inside ‘Query’ and click on ‘Preview’ button. If everything works fine, you can see the top 10 records under preview section.
Note: If you feel that all the columns are there under preview section, then no need to do anything. If not, please modify your query to get your columns.
6. Click on Variables tab and now you can see all the columns which you previewed in the previous tab.
7. Change the contents for all the variables (Ex: Element, Consolidation, Ignore, Other etc)
8. Click on ‘Maps’ tab and click on ‘Cube’ tab.
9. Click on ‘Create Cube’ under ‘Cube Action’ and provide a cube name.
10. Click on ‘Accumulate/Store’ values based upon your requirement under ‘Data Action’ for storing the data inside the cube.
• Accumulate: Aggregates the values with existing data
• Store: replaces the data
11. Click on ‘Dimensions’ tab and provide the new dimension name under ‘Dimension’ and also specify the order of the dimension in the cube. If you are creating new dimensions, please double check whether the ‘Action’ contains ‘Create’.
12. Specify the element type for the dimension.
13. Click on ‘Consolidation’ tab and assign the N level elements to the consolidation element to create a hierarchy.
14. Click on ‘Advanced’ Tab and then click on ‘Prolog’, ‘Meta Data’, ‘Data’ and “Epilog’. The TI process will automatically writes the script inside the relevant tabs.
15. Save the process with a name and then execute it to create a new dimension.

Creating a Dimension through TI process

Please follow the below steps to create a new dimension through TI process:

1. Select process and then select ‘Process\Create New Process’ as shown in the below screenshot

2. Now Turbo Integrator window will be opened
3. If your source is an external database (For Ex: SQL SERVER), click on ‘ODBC’ as your datasource type under ‘Data Source’ tab inside the TI process.
4. Click on Browse and select the ‘Data Source Name’ (which should be created before selecting the datasource name, create a DSN for the database which you are going to use).
5. Write the query to fetch the data from the database inside ‘Query’ and click on ‘Preview’ button. If everything works fine, you can see the top 10 records under preview section.
Note: If you feel that all the columns are there under preview section, then no need to do anything. If not, please modify your query to get your columns.
6. Click on Variables tab and now you can see all the columns which you previewed in the previous tab.
7. Change the contents for all the variables (Ex: Element, Consolidation, Ignore, Other etc)
8. Click on ‘Maps’ tab and do nothing under ‘Cube’ tab.
9. Click on ‘Dimensions’ tab and assign the following parameters
• Dimension: give a new name (since it is a new dimension)
• Action: Create
• Element Type: either Numeric or String
10. Click on ‘Consolidation’ tab and assign the n level elements if you want the elements to be under a particular consolidation element (If you didn’t mention an consolidation element under variables tab, this Tab will be deactivated)
11. Click on ‘Advanced’ Tab and then click on ‘Prolog’, ‘Meta Data’, ‘Data’ and “Epilog’. The TI process will automatically writes the script inside the relevant tabs.
12. Save the process with a name and then execute it to create a new dimension.

Cognos Controller certification Q & A (Sample)

Sample test:
The sample test is designed to give the candidate an idea of the content and format of the questions that will be on the certification exam. Performance on the sample test is NOT an indicator of performance on the certification exam. This should not be considered an assessment tool.

(Answer key at the end)
------------------------------------------------------------------------
1. Where would the administrator define a circular ownership (company A owns company B, and company B owns company A)?
A. In the Define Company Structure window.
B. In the Data Entry - Shareholding and Investments in Group Companies window.
C. In the Define Account Structure window.
D. In the Data Entry - Shareholdings and Investments in External Companies window.

2. A new account has been set up in Controller by the administrator, but it is not visible in the Define Account Structure window of the users. How can this be fixed?
A. Generate the account, activate the advanced account view settings, and then restart Controller.
B. Activate the advanced account view settings, restart Controller, and then re-create the account.
C. Activate the advanced account view settings, generate the account, and then restart Controller.
D. Activate the advanced account view settings, restart Controller, and then generate the account.

3. How are adjusting entries for group journals different from adjusting entries for company journals? Adjusting entries for group journals are:
A. Posted in a group adjustment company.
B. Entered as adjustment columns.
C. Posted on the parent company's books.
D. Done through adjustment column or manual entry.

4. A Controller administrator is about to convert period data into different currencies for the first time. What should the administrator do?
A. Enter currency rates.
B. Use the "According to Company Structure Definition" conversion method.
C. Specify the same period for the "From Period" and the "OB Period".
D. Leave the "OB Period" blank.

5. The administrator has converted all P&L accounts at the average rate and all Balance Sheet accounts (except equity) at the closing rate. What is the conversion method used?
A. Method 1- MNM method
B. Method 2- MNM method
C. Method 1- Current method
D. Method 2- Current method

6. An account called "Research and Development - P001St2" was setup for the profit and loss statement. The account type code used is T. What could be the reason?
A. The account is a statistical, debit, balance sheet account.
B. The account is a statistical, credit, balance sheet account.
C. The account is a statistical, debit, profit and loss account.
D. The account is a statistical, credit, profit and loss account.

7. How can the administrator view intercompany balances for a particular intercompany pairing within a company?
A. Run the journals across report for an intercompany account.
B. Run the Trial Balance with DrillDown report for a company.
C. Run the Reconcile Intercompany Balances Report for a company.
D. Run the Ledger Report for an intercompany account.

8. A client has entered the following information in their investment elimination template: CR - Investment in Subsidiaries 1,000,000 and DR - Goodwill 500,000 The client has no more information available, and they want to use the investment register and eliminations template to take advantage of the automated amortization functionality. What should they do?
A. Enter the difference to an offset account, and then use the investment elimination template.
B. Enter the difference to an offset account, and then create an automatic journal to eliminate this value.
C. Disable the no balance control in the general configuration.
D. Enable the no balance control in the general configuration.

ANSWERS: 1-B; 2-D; 3-A; 4-A; 5-C; 6-C; 7-C; 8-D

Sample TM1 certification Q & A

Sample test:
The sample test is designed to give the candidate an idea of the content and format of the questions that will be on the certification exam. Performance on the sample test is NOT an indicator of performance on the certification exam. This should not be considered an assessment tool.

(Answer key at the end)
-----------------------------------------------------------------------

1. An application developer needs to provide a unique name for the server containing the TM1 application. Which of the following tm1s.cfg parameters should be used?
A. DataBaseDirectory
B. AdminHost
C. ServerName
D. PortNumber

2. Why does an application developer create multiple hierarchies in a dimension?
A. To increase the number of dimensions and empty cells in a cube.
B. To increase the sparsity of the dimensions and empty cells in a cube.
C. To reduce the number of dimensions and empty cells in a cube.
D. To reduce the density of the dimensions and string cells in a cube.

3. When loading data into a cube, which of the following must be specified for each of the dimensions?
A. Consolidation
B. Simple element
C. Data variable
D. Attribute

4. Why would an application developer precede a formula with S:?
A. To create a rule stored in a string cell.
B. To create a rule stored in a simple cell.
C. To create a rule to calculate all simple cells.
D. To create a rule to calculate all leaf cells.

5. Which of the following indicates a rule has been under-fed?
A. Consolidated cells are zero.
B. Leaf level cells are zero.
C. Calculation time is slower.
D. Calculation results are incorrect.

6. How many dimensions must a reporting cube and look-up cube have in common in order to share data?
A. 1
B. 3
C. 2
D. 0

7. Which of the following must an application developer do to distribute formatted reports to users through a web browser?
A. Save the report to a reporting folder.
B. Save the report to the data directory.
C. Save the report on the TM1 server.
D. Save the report to an application folder.

8. When a cell has a marker indicating it has been held, which of the following options can be used to update the cell?
A. Data spreading
B. Hold consolidate
C. Direct editing
D. Proportional spreading

9. What can an analyst create to save only the elements deemed necessary for their own analysis?
A. A public view
B. A public subset
C. A private view
D. A private subset

10. Sales needs to insert projections into the forecasting cube and analyze the results. Which of the following options should they use?
A. A snapshot
B. A subset
C. A slice
D. A form

11. Why would an analyst use a DBSS function?
A. To send data to the cube in a single package.
B. To retrieve a consolidation from the cube.
C. To send a string value to the cube.
D. To retrieve an attribute from the cube.

12. Which of the following enables drill-down in a websheet?
A. Data validation
B. Active Form
C. Data spreading
D. Active report

Answers: 1-C; 2-C; 3-B; 4-A; 5-A; 6-D; 7-D; 8-C; 9-D; 10-C; 11-C; 12-B