DataWareHousing Support

TM1 Perspectives VB Error

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 18 May 2012 11:34:00 +0000

Error: Object library invalid or contains references to object definitions that could not be found

Screenshot:

Solution:

Close TM1 perspective and Excel completely
Open the command prompt
Navigate to Document and Settings
C:\Documents and Settings>del /S /A:H /A:-H *.EXD (and press enter key)
Close command prompt
Now try login back to TM1 perspectives

CoCreateInstance (ControllerXLDisp.MsgPusher) failed with 0x800a01ad

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 17 Feb 2012 07:36:00 +0000

Error: CoCreateInstance (ControllerXLDisp.MsgPusher) failed with 0x800a01ad

Steps:
1. Logon to the client PC as the 'bad' user
2. Open the Microsoft Office folder (where EXCEL.EXE is located). For example, if using Office 2003 then the default path is C:\Program Files\Microsoft Office\OFFICE11
3. Inside this folder, ensure that there is a file called 'EXCEL.EXE.CONFIG'.
4. Check the NTFS file permissions for this file ('Excel.exe.config'). Ensure that the permissions are set so that the group 'Everyone' has read access
5. Launch Internet Explorer
6. Click 'Tools - Options'
7. Click 'Connections'
8. Click 'LAN Settings'
9. Ensure that you do not hve a proxy server configured.
10. Ensure that you are logged onto the client PC as an administrator
11. Start - Run - 'Regedit'
12. Locate the key 'HKEY_CLASSES_ROOT\ControllerXLDisp.MsgPusher'
13. Right-click on 'ControllerXLDisp.MsgPusher' and choose 'permissions'
14. Ensure that the group 'Everyone' has 'Read' permissions
15. Launch 'Control Panel' then 'Add/Remove Programs'
16. Locate the Controller Excel Link entry
17. Uninstall the Controller Excel link client
18. After the process has finished, locate the Controller Excel link software install program(typically "ControllerClient.MSI" which you can download from http:///cognos8/controllerbin/controllerclient.msi or sometimes "CCRLocalClient.MSI") and copy it to the client device's local hard drive(for example C:\TEMP)
19. Re-install the Controller Excel link client. IMPORTANT: If using a modern version of Windows (Vista / Windows 7 / Windows 2008) then you will need to right-click on the MSI file (typically "ControllerClient.MSI") and choose "Run as administrator". If using an older version of Windows (such as Win XP / Windows 2003) then simply double-click on the file 'ControllerClient.MSI',
20. Locate the key 'HKEY_CLASSES_ROOT\ControllerXLDisp.MsgPusher'
21. Right-click on 'ControllerXLDisp.MsgPusher' and choose 'permissions'
22. Double-check to ensure that the group 'Everyone' still has 'Read' permissions
23. Test

Controller Client/Excel Add-in is not installed

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 17 Feb 2012 07:34:00 +0000

Error: Controller Client/Excel Add-in is not installed

Steps:
Excel 2003 or 2005

1. Click on Tools/Add-ins in Microsoft excel
2. Click the check box next to IBM Cognos 8 Controller Link.
a. If you are not able to see the Excel Add-in under Add-ins window, we need to add it manually.
b. Click on Browse in the Add-ins window
c. Browse through the specified path C:\Program Files\Cognos\ccr\ControllerXL.xll
d. Click ok to add the excel add-in and then enable it by clicking on the check box.

Excel 2007
1. Click on Office button in Excel and then click on Excel Options
2. Click on Add-ins
3. In the Manage dropdown box select Disabled Items and then click on Go button
4. Select the IBM Cognos 8 Controller Link and then Enable it by clicking on Enable button
5. Select Excel Add-ins and then click on Go button once again and then click on check box next to IBM Cognos 8 Controller Link and then click on OK.

Interop.Excel: Attempted to read or write protected memory - Cognos Controller

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 17 Feb 2012 07:30:00 +0000

Cause: Printer Issue
Sol: Print Preview before you save the form.
1. Open the form
2. Make the change
3. Click "File - Print Preview"
4. Click the 'close' button to close the "print preview" page
5. Press 'Save'

Renaming Elements in Cognos TM1 Dimension

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 29 Oct 2010 15:54:00 +0000

Reanming Elements in a Dimension:

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

Steps:

Create a new alias "new" in the Dimension, by default the new elements are identical
Change all required elements to their new names in the "new" created alias
Create a new TI process with the following line in the Prolog:

SwapAliasWithPrincipalName(Dimension,'new',0);

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

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 29 Oct 2010 15:46:00 +0000

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:

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

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

Creating a cube through TI process with new dimensions

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 26 Oct 2010 14:15:00 +0000

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

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

Now Turbo Integrator window will be opened
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.
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).
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:

Click on Variables tab and now you can see all the columns which you previewed in the previous tab.
Change the contents for all the variables (Ex: Element, Consolidation, Ignore, Other etc)
Click on ‘Maps’ tab and click on ‘Cube’ tab.
Click on ‘Create Cube’ under ‘Cube Action’ and provide a cube name.
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

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’.
Specify the element type for the dimension.
Click on ‘Consolidation’ tab and assign the N level elements to the consolidation element to create a hierarchy.
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.
Save the process with a name and then execute it to create a new dimension.

Creating a Dimension through TI process

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 26 Oct 2010 14:09:00 +0000

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

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

Now Turbo Integrator window will be opened
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.
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).
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:

Click on Variables tab and now you can see all the columns which you previewed in the previous tab.
Change the contents for all the variables (Ex: Element, Consolidation, Ignore, Other etc)
Click on ‘Maps’ tab and do nothing under ‘Cube’ tab.
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

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)
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.
Save the process with a name and then execute it to create a new dimension.

Cognos Controller certification Q & A (Sample)

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 12 Oct 2010 15:52:00 +0000

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

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 12 Oct 2010 15:48:00 +0000

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

Installing TM1 Web

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 06 Sep 2010 04:59:00 +0000

Installing and Running IBM Cognos TM1 Web:
Before you install the IBM Cognos TM1 Web software, you need to understand about the software requirements:

● Software Requirements
● Installing the IBM Cognos TM1 Web Software

Software Requirements:

Operating System	Microsoft Windows
Web server	Microsoft IIS
Microsoft Excel	Excel 2002 or later
Web browser	Microsoft Internet Explorer
Windows Service Account	Windows services run under a network user name and password. We recommend that you establish a user account and password in your network exclusively for the IBM Cognos TM1 Web services. The account can have any name.

Installing the IBM Cognos TM1 Web Software:
Follow these steps to install IBM Cognos TM1 Web.

Steps:

Insert the TM1 distribution CD in the CD-ROM drive.
The Installation Wizard runs.
Click IBM Cognos TM1 Web, and click Install.
Select a language: English, French or German. Click OK.
The Installation Wizard Welcome screen opens.
Follow the wizard instructions to install IBM Cognos TM1 Web.

TM1 Web Architecture

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 06 Sep 2010 04:47:00 +0000

IBM Cognos TM1 Web Overview:

IBM Cognos TM1 Web extends the analytical power of TM1 by allowing you to complete the following tasks in a Web browser:

● Analyze cube data
● Manipulate formatted Excel reports
● Drill, pivot, select, and filter data
● Build charts
● Perform some server administration tasks

IBM Cognos TM1 Web Architecture:
The IBM Cognos TM1 Web multi-tiered architecture includes an IIS Web server, an Excel Web service for displaying Websheets, a TM1 server, and an Admin server.

Note: IBM Cognos TM1 Web requires the Microsoft .NET Framework.

IIS:

The IIS Web server has a virtual directory that stores TM1 Web ASP scripts.
IIS Web server must have Microsoft Excel 2002 or later versions installed. This software is required to generate TM1 Websheets.
IIS Web server must have Microsoft.NET Framework version 3.0 installed.
Web installs a Windows service – the TM1 Excel Service (TM1ExcelService.exe) on your Web server. This service converts Excel worksheets to TM1 Websheets. It also exports Websheets back to Excel or to PDF.

TM1 Server and TM1 Admin Server:

TM1 Server can be installed on the same computer that hosts your Web server, but installing on a separate computer is more efficient.
TM1 Admin Server can be installed on any computer on your LAN, but it must reside in the same LAN segment as your TM1 Server. Typically the TM1 Server and the TM1 Admin Server are installed on the same computer.

Monitoring TM1 server performance

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 06 Sep 2010 04:16:00 +0000

Monitoring Server Performance:
TM1 includes a performance monitoring feature that lets you record the performance statistics for clients, cubes, and servers. When you enable performance monitoring, TM1 populates several control cubes on a minute-by-minute basis. You can then browse these cubes to analyze the server performance.

The following control cubes are populated during performance monitoring.

}StatsByClient
}StatsByCube
}StatsByCubeByClient
}StatsForServer

}StatsByClient	Tracks the message count, average message size, total elapsed time, and other measures for each client on the server.
}StatsByCube	Tracks the memory used for each cube on the server.
}StatsByCubeByClient	Tracks the number and elapsed time of cell updates, cell retrievals, view calculations, and view retrievals for each client and cube on the server.
}StatsForServer	Tracks the connected clients, active threads, and memory used by the server.

Steps:

Select the server in Server Explorer.
Click Server, Start Performance Monitor.
You have enabled performance monitoring on a per-session basis.
If you want to enable performance monitoring at the start of every server session, you can set the "PerformanceMonitorOn" parameter in the Tm1s.cfg file to automatically begin performance monitoring when a server starts.
To end performance monitoring during a server session:
Select the server in Server Explorer.
Click Server, "Stop Performance Monitor".

Deleting duplicate records in SQL Server

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 16 Jul 2010 15:14:00 +0000

Example Table:

Empid	Empname
1	A
1	A
2	B
2	B
3	C
4	D

Query:
WITH CTE (Empid, Empname, Duplicaterowcount)
AS
(
SELECT Empid, Empname, ROW_NUMBER() OVER (PARTITION BY Empid, Empname ORDER BY Empid) AS Duplicaterowcount
FROM Emp
)
DELETE from CTE
WHERE Duplicaterowcount > 1
GO

Result:

Empid	Empname
1	A
2	B
3	C
4	D

How to setup a TM1 Server to run as a Windows Service

ajai.goura@gmail.com (AjayKumar Gourabathina) — Thu, 15 Jul 2010 15:20:00 +0000

To setup a TM1 Server to run as a service...
1) Open a command prompt: Start > Run > cmd
2) Navigate to the TM1 bin directory using the cd command. Depending on the version of TM1 you have installed, the default location of the bin directory will be either "C:\Program Files\Applix\bin\" or "C:\Program Files\Cognos\TM1\bin". If you installed TM1 to a different directory, navigate to the bin directory appropriately.

tm1sd.exe -install -n"NameOfTM1Server" -z"PathtoTm1s.cfgFile"

NameOfTM1Server (after the -n) is the name you want to give to this TM1 Server service. If the name you are going to use contains any spaces, this value must be specified in double quotes.
The PathtoTm1s.cfgFile (after the -z) is the path to the tm1s.cfg file for this the TM1 Server you are setting up. This parameter value MUST be surrounded by double quotes if there are any spaces in the path name.

For example:
tm1sd.exe -install -n"TM1 Production Server" -z"C:\Program Files\Cognos\TM1\Custom\TM1Data\PData"

If the command is successful, it will return a message indicating so. For example:
TM1 Server / tm1 production server installed.

4) You can then go into the services listing, and you will see the new TM1 Server service you setup. Following the above example, the service would be listed as:
TM1 Server / tm1 production server
You can then configure the service to run automatically, and to run under a specific domain user.

NOTE: If you are setting up a completely new TM1 Server for which you don't already have a tm1s.cfg file, you can copy the tm1s.cfg from the Planning Sample TM1 Server and modify it accordingly. At a minimum, you will need to modify the ServerName, DataBaseDirectory, and PortNumber parameter values.

If the service does not start (error message stating the service cannot be started), starting the same TM1 Logical server as a desktop application will usually provide more detailed information on the error.

If you wish to remove a TM1 Server service (again, following the example above), follow steps 1 and 2, then type the following command (substituting appropriate values where needed):

tm1sd.exe -remove -n"NameOfTM1Server"

The NameOfTM1Server (after the -n) is the name of the TM1 Server service you wish to remove. If the name you are going to use contains any spaces, this value must be specified in double quotes.

For example:
tm1sd.exe -remove -n"TM1 Production Server"

Overview of TM1 Servers

ajai.goura@gmail.com (AjayKumar Gourabathina) — Thu, 15 Jul 2010 15:17:00 +0000

We have to concentrate mainly on 3 components:
1) TM1 Admin Server
2) TM1 Server/Servers
3) TM1 Clients

There are 3 steps mainly takes place in the TM1 servers.

1) TM1 servers register with TM1 Admin server
2) TM1 clients contact the TM1 admin server and receive information about the TM1 servers that are available.
3) TM1 clients establish connections with TM1 servers.

Datawarehousing FAQs

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 02 Jul 2010 08:31:00 +0000

What is Data Warehousing?
A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for managements decision support system. The critical factor leading to the use of a datawarehouse is that a data analyst can perform complex queries and analysis, such as data mining, on the information without slowing down the oprational systems. Datawarehousing collection of data designed to support management decision making. Data warehouses contain a variety of data that present a coherent picture of business conditions at a single point in time. It is a repository of integrated information, avaliable for queries and analysis.

What is Business Intelligence (BI)?
Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis and presentation of business information and sometimes to the information itself. The purpose of business intelligence is to support better business decision making. Thus, BI is also described as a decision support system (DSS).

BI systems provide historical, current and predictive views of business operation, most often using data that has been gathered into a data warehouse or a datamart and occassionally working from operational data.

What is Dimension table?
Dimension table contains textual attributes of measurements stored in the facts tables. Dimension table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchy nodes.

What is Dimensional Modeling?
Dimensional data model concept involves two types of tables and it is different from 3rd normal form. This concept uses Facts table which contains the measurements of the business and Dimension table which contains the context (dimension of calculation) of the measurements.

What is Fact table?
Fact table contains measurements of business process. Fact table contains the foreign keys of the dimension tables.
Example, If your business process is "paper production", "average production of paper by one machine" or "weekly production of paper" will be considered as measurements of business process.

What are fundamental stages of data warehousing?
There are four different fundamental stages of data warehousing.

Offline Operational Databases
Offline Datawarehouse
Real Time Datawarehouse
Integrated Datawarehouse

Offline Operational Databases:
Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance.

Offline Datawarehouse:
Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.

Real Time Datawarehouse:
Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (Ex: an order or a delivery or a booking etc.)

Integrated Datawarehouse:
Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

What are different methods of loading Dimension tables:
There are two different ways to load data in Dimension tables.

Conventional (Slow)
Direct (Fast)

Conventional (Slow):
All the constraints and keys are validated against the data before it is loaded and this way data integrity is maintained.

Direct (Fast):
All the constraints and keys are disabled before the data is loaded. Once the data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it will not included in index and all future processes are skipped on this data.

Describe the foreign key columns in fact table and dimension table?
Foreign keys of dimension tables are primary keys of entity tables whereas Foreign keys of facts tables are primary keys of dimension tables.

What is Data Mining?
Data Mining is the process of analyzing data from different perspectives and summarizing it into useful information.

What is the difference between a View and a Materialized View?
A View takes the output of a query and makes it appear like a virtual table and it can be used in place of tables.

A Materialized View provides indirect access to table data by storing the results of a query in a seperate scheme object.

What is OLTP?
OLTP (On-Line Transaction Processing) is an application that modifies data instance it receives and has a large number of concurrent users.

What is OLAP?
OLAP (On-Line Analytical Processing) is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is factless fact table?
A fact table which doesnot contain numeric fact columns is called factless fact table.

What are slowly changing dimensions (SCD)?
SCD applies to cases where the attribute for a record varies over time. There are different types of SCD.

SCD1: The new record replaces the original record. Only one record exist in database - current data.
SCD2: A new record is added into the customer dimension table. Two records exist in database - current data and previous history data.
SCD3: The original data is modified to include new data. One record exists in database - new information is attached with old information in same row.

What is hybrid slowly changing dimension?
Hybrid SCD's are combination of both SCD1 and SCD2. It may happen in a table, some columns are important and we need to track changes for them i.e., capture the historical data for them whereas in some columns even if the data changes, we dont care.

What is BUS schema?
BUS scheme is composed of a master suite of conformed dimension and standardized definition of facts.

What is Star scheme?
Star scheme is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What is Snow Flake scheme?
Snowflake scheme is a scheme in which each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Difference between star and snowflake scheme?
Star Scheme: A single fact table with N number of Dimensions, all dimensions will be linked directly with a fact table. This scheme is normalized and results in simple join and less complex query as well as faster results.
Snowflake Scheme: Any dimensions with extended dimensions are known as snowflake scheme, dimensions may be interlinked or may have one to many relationship with other tables. This scheme is normalized and results in complex join and very complex query as well as slower results.

What is the difference between ER modeling and Dimensional modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is degenerate dimension table?
If a table contains the values, which is neither dimension nor measures is called degenerate dimensions.

What is junk dimension?
A number of very small dimensions might be lumped together to form a single dimension, a junk dimension - the attributes are not closely related. Grouping of Random flags and text attributes in a dimension and moving them to a seperate sub dimension is known as junk dimension.

Still to come...

ExecuteCommand function in Turbo Integrator process

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 29 Jun 2010 09:10:00 +0000

ExecuteCommand:
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.

This function executes a command line during a process. You can use ExecuteCommand to run a desktop application, but not a service.If you use ExecuteCommand to run an executable, the following conditions apply:

If the CommandLine argument specifies only the name of a file to be executed, a Windows TM1 server looks for the file in both the server database directory and in the directory where Tm1s.exe resides. A UNIX TM1 server looks for the file only in the server database directory.

If the CommandLine argument uses a relative path prefix, both the Windows and UNIX TM1 server attempt to locate the file in the server database directory only.

On either the Windows or UNIX TM1 server, you can pass an absolute path to the CommandLine argument to execute a file in any location..

Syntax:
ExecuteCommand(CommandLine, Wait);

Arguments:

Argument	Description
CommandLine	The command line you want to execute.
Wait	Indicates if the process should wait for the command to complete execution before continuing to the next process statement. An argument value of 0 causes the process to proceed to the next statement without waiting for the command line to execute. An argument value of 1 causes the process to wait for the command line to successfully execute before proceeding to the next statement.

Moving a file from one folder to other and Renaming the file with date through Turbo Integrator Process in Cognos TM1

ajai.goura@gmail.com (AjayKumar Gourabathina) — Tue, 29 Jun 2010 08:54:00 +0000

In Turbo Integrator process there is no such method/function to move a file from one folder to other folder and also renaming. But it can be achieved through a batch file.

Scenario:
You have a file "Example.txt" to move from "D:\Temp" folder to "D:\Archive" folder and rename the file from "Example.txt" to "Example_DDMMYYYY.txt".

Solution:
Create a batch file which consists of source code to move a file from one folder to other and rename the same file appending with date.

Steps:
Batch File:
@MOVE "D:\Temp\Example.txt" "D:\Archive"
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do rename "D:\Temp\Example.txt" Example_%%e%%d%%f.cma

Copy and paste the above source code into a notepad and save the file with your desired filename with an extension ".bat", so that it creates a batch file.

Finally you need to call the batch file through ExecuteCommand (Turbo Integrator process function), so that it executes the batch file and moves the file from one folder to another and then rename the file.

ExecuteCommand('your batch filename with complete path', 1);

Backing Out Records from the Transaction Log in Cognos TM1

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 21 Jun 2010 16:53:00 +0000

After you query the transaction log, you can use the Transaction Log Query Results dialog box to back out the transactions. When you back out a transaction, the value in the OLDVALUE column replaces the value in the NEWVALUE column.

Steps:
1. Highlight the record(s) you want to back out.
● To highlight an individual record, click the record.
● To highlight multiple adjacent records, click the first record and SHIFT+click the last record.
● To highlight multiple non-adjacent records, CTRL+click each record.
2. Click Edit, Select.
All the highlighted records now display a check mark in the box adjacent to the first column. The check marks indicate that the record is selected to be backed out.
To select all the records without first highlighting them, click Edit, Select All.
3. Click Edit, Back Out.
TM1 backs out the records in reverse chronological order as identified by the LOGTIME column.

Enabling and Disabling Logging in Cognos TM1

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 21 Jun 2010 16:47:00 +0000

By default, TM1 logs transactions to all cubes loaded on the server. As the system administrator, you have the option to turn off logging for particular cubes.

When you disable logging, TM1 accelerates updating the data but you cannot recover the updates in the event of a system failure.

Steps to enable logging for individual cubes:
1. Open Server Explorer.
2. Select the Cubes icon for the server you are working with.
3. Select Cubes, Security Assignments.
The TM1 Security Assignments dialog box opens.
4. Click the cell at the intersection of the Logging column and the cube name. TM1 enables logging when a check box contains an X, and disables logging when the check box is empty.
5. Click OK.

Data Backup and Recovery in Cognos TM1

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 21 Jun 2010 16:39:00 +0000

The Tm1s.log file remains open while a TM1 server is running. When you bring down the server normally, TM1 renames the log file by appending a time stamp to it, and with the following naming convention:
Tm1syyyymmddhhmmss.log

The time stamp, yyyymmddhhmmss, represents the current Greenwich Mean Time at the time the server was brought down. For example, if the server came down on January 2, 2002, at 2:30 PM, the name of the log file is Tm1s20020102143000.log.

TM1 recovers the data automatically in the event that a server comes down abnormally, and leaves the Tm1s.log file on the disk. The next time you bring up the server, TM1 recovers the changes in either of two ways:

● Automatically recovers the changes -- when you are running the TM1 server as a Microsoft Windows service
● Prompts you to recover the changes -- when you are running the TM1 server as an application If you intentionally shut down a TM1 server without saving the data, TM1 saves the transaction log with a time stamp and changes the file extension to .rej.

For example, Tm1s20020102143000.rej
The .rej log file ensures that you always have a record of the data transactions, even if you shut down the server without saving the data. If you accidentally shut down the server without saving the changes, you can process the .rej file through TurboIntegrator to recover the data.

Overview of TM1 System and Performance Monitoring

ajai.goura@gmail.com (AjayKumar Gourabathina) — Mon, 21 Jun 2010 16:29:00 +0000

TM1 includes a collection of tools that provide logging and real-time performance monitoring of the TM1 servers in your organization. You can monitor TM1 performance and activity using the following tools.

● Admin Server log - A log file containing messages about communication between TM1 clients, the Admin Server and individual TM1 servers.
● Transactions Log - A log file that tracks the data transactions made by the clients logged into a TM1 server. Recorded information includes the date and time the change was made, name of the client who made the change, before and after value, name of the cube in which the change was made, and the elements that identify the cell that changed.
● Server Message Log - A log file containing details on the activity of the TM1 server, such as an executed processes, chores, loaded cubes and dimensions, and a synchronized replication.
● Audit Log - A log file that monitors changes to TM1 objects and system-wide events, such as modifications to dimensions, views and subsets and successful/ unsuccessful login activity.
● Monitoring Server Performance Using Control Cubes - Minute-by-minute performance statistics for clients, cubes, and servers. This performance data is tracked and stored in the following TM1 control cubes; }StatsByClient, }StatsByCube, }StatsByCubeByClient, and }StatsForServer.
● TM1 Top - A utility that dynamically monitors the threads and processes running in an instance of a TM1 server. TM1 Top is a stand-alone utility that runs within a console (command) window on a Microsoft Windows computer.
● Performance Counters - A collection of continuously updated values that provide real-time monitoring of specific TM1 server properties and activities such as usage of cubes, views, subsets, dimensions, and read/write activity. Performance counters are viewable in graphical
format using the Microsoft Windows Performance Monitor, or in a text-only display using the TM1 PerfMon utility.

Synchronization Process

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 18 Jun 2010 14:55:00 +0000

You always initiate the synchronization process from the target server, which is the server that contains the mirror cubes. You must have administrator authority for this server to initiate the
synchronization process.

You can synchronize on demand or schedule the synchronization process to be automatic.

❑ TM1 updates the data in all the mirror cubes that are part of the same replication connection.
You cannot specify individual cubes to update.
❑ TM1 also updates the replicated dimensions if you made changes to their definitions and rules.
❑ TM1 always writes updates from the source cubes to the mirror cubes.
❑ TM1 does the following with updates you make to the mirror cube:

● Writes the updates back tothe source cube, if the updates were made by users with Reserve access to the source cube.

● Does not write the updates back tothe source cube, if the updates were made by users with Read orWrite access to the source cube.

❑ TM1 uses log files to keep track of the changes made to the source and mirror cubes. During the synchronization process, TM1 uses the log files to determine which updates to make. If there is an update conflict for a particular cell, TM1 uses the most recent update, no matter where this update was made (that is, in the source cube or the mirror cube).

Synchronizing on Demand:
To synchronize updates on demand, complete the following steps.
Steps
1. In the Server Explorer, access the server that contains the mirror cubes.
2. Double-click the Replications icon.
3. Right-click the icon for the replication connection for whose cubes you want to synchronize, and click Synchronize Data.

Scheduling Synchronization
You can create a chore to automatically synchronize data at a regular interval using Turbo Integrator.
For details on Turbo Integrator, see the IBM Cognos TM1 TurboIntegrator Guide.
Steps
1. In the Server Explorer, select the Chores icon beneath the target server for which you want to automate synchronization.
2. Right-click the Chores icon and click Create New Chore. The Chore Setup Wizard opens.
3. Select the replication for which you want to automate synchronization.
4. Click the arrow button to move the replication from the Available list to the Selected List.
5. Click Next.
The second screen of the Chore Setup Wizard displays.
6. Use the calendar and Time field to set a start date and time for the initial synchronization.
7. Use the fields in the Chore Execution Frequency box to set the interval at which the synchronization should be executed.
8. Click Finish to complete the scheduling.

What Happens when TM1 Replicates a Cube

ajai.goura@gmail.com (AjayKumar Gourabathina) — Fri, 18 Jun 2010 14:50:00 +0000

When TM1 replicates a cube, the following action occurs:

● Optionally, TM1 replicates the cube dimensions. When you initiate the replication process, you decide which dimensions you want to replicate, and of those, which dimensions you want to synchronize when you synchronize the cubes. You can also choose to use one or more local
dimensions with a replicated cube.

● Optionally, TM1 replicates the cube rules. When you initiate the replication process, you decide which rules you want to replicate, and of those, which rules you want to synchronize when you synchronize the cubes.

● Represents the cube as an icon under the Cubes icon on the target server.

● Names the source server in the Replication Server field on the attributes bar for each replicated cube.

● Names the source server in the Replication Server field on the attributes bar for each replicated dimension.