...

Turbo Integrator Data Processing Tips and Techniques Tip or Technique

by user

on
Category: Documents
6

views

Report

Comments

Transcript

Turbo Integrator Data Processing Tips and Techniques Tip or Technique
Tip or Technique
Turbo Integrator Data Processing
Tips and Techniques
Product(s): TM1 Turbo Integrator
Area of Interest: Modeling
Turbo Integrator Data Processing Tips and Techniques
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
WHAT A TURBO INTEGRATOR (TI) LICENSE INCLUDES .............................. 4
3
HOW TO PROCESS DATA USING AN ELEMENT THROUGH TURBO
INTEGRATOR................................................................................................. 5
4
PROCESSING ATTRIBUTES USING TURBO INTEGRATOR........................... 13
5
HOW TO PROCESS NUMERIC CODES TO STRING CODES WITH TURBO
INTEGRATOR............................................................................................... 16
6
DECIMAL PLACEMENTS AND TURBO INTEGRATOR .................................... 22
7
CONCATENATE TWO STRING ELEMENTS USING TURBO INTEGRATOR ..... 25
8
USING THE WHERE CLAUSE IN TI WITH STRING VARIABLES ................... 26
9
ADDING CLIENTS TO GROUPS USING TURBO INTEGRATOR ..................... 33
10
TURBO INTEGRATOR TIPS.......................................................................... 38
11
TI TIDBITS .................................................................................................. 39
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
4
1 Introduction
1.1
Purpose
This document is a compilation of various tips and techniques on using Turbo
Integrator to accomplish various data movement and transformations into
TM1.
1.2
Applicability
This content is applicable to TM1 Turbo Integrator version 9.1 and prior.
1.3
Exclusions and Exceptions
No exclusions have been identified.
2 What a Turbo Integrator (TI) License Includes
Without a TI license the following functionality exists:
1. Can only choose from 2 data source types to process with
- ASCII
- ODBC
2. Will NOT have option to add new variables once data source is chosen, thus
limiting ways to manipulate your data.
3. Will have Cube Action that will allow you to:
-
Create
Recreate
Update
4. Will be able to update dimensions with N level elements and Consolidations.
5. Will NOT have Chores functionality to schedule TI processes at certain times.
With a TI license you will get the following functionality:
1.
Can choose from multiple data source types to process with
-
ASCII
ODBC
Cube View
Dimension Subset
2. Will have the option to add new variables once data source is chosen.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
3. Will have Cube Action that will allow you to:
-
Create
Recreate
Update
4. Will be able to update dimensions with N level elements and Consolidations.
5. Write business rule functions in the Advance area, enhancing the data
processing.
6. Will have Chores functionality to schedule TI processes at certain times.
3 How to Process Data Using an Element through Turbo
Integrator
First the facts about this TI process
The cube is 4 dimensions
The .csv file contains information for 3 of the dimension
So this means a variable needs to be set for the forth dimension
1. See how the .csv file is setup to understand this process
- Column A will be ignored
IBM Cognos Confidential Information
5
Turbo Integrator Data Processing Tips and Techniques
6
- Column B is an element for dimension
- Column C is an element for a dimension
- Columns D-O are data that actually represents months for a dimension.
What I mean is that the value 0 in D1 will get pushed to the month January.
The 0 in E1 is for February and so on. This just happens to be the structure
of this .csv file.
SO we have established 3 dimensions but what about the 4th. We need to set add a
new variable and set it to equal and element of the 4th dimension.
This is what is placed in the formula
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
Now you need to point to the right cube and update it
You need to update your dimensions as well
IBM Cognos Confidential Information
7
Turbo Integrator Data Processing Tips and Techniques
8
You now need to set the data variable to which element they represent - see picture
of .csv file to understand this
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
Prolog Tab should look like this:
IBM Cognos Confidential Information
9
Turbo Integrator Data Processing Tips and Techniques
Metadata Tab should look like this:
Data Tab should look like this – once in this Tab save process and execute it.
IBM Cognos Confidential Information
10
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
11
Turbo Integrator Data Processing Tips and Techniques
Final result should look like this (match first line of .csv file to this view)
IBM Cognos Confidential Information
12
Turbo Integrator Data Processing Tips and Techniques
13
4 Processing Attributes using Turbo Integrator
Steps:
1. First create attribute name and type
2. Go into Attribute Editor and select “add new attribute” My example is “company
name” as alias
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
14
3. Here is how my ASCII file looks
I have my element names first then my attributes. Why does it need to be this way?
Well for now in Turbo Intergrator you need to specify where the elements go and
once that is done then attributes can be mapped to the elements – you will see in
the next pictures
4. So once you choose your ASCII file enter the information like the following
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
5. No need to fill the cube tab, so go to the Dimension tab and fill in like this
6. Next fill in the Attributes tab
IBM Cognos Confidential Information
15
Turbo Integrator Data Processing Tips and Techniques
16
7. Once all this is done execute your TI Process and the following should appear:
5 How to Process Numeric Codes to String Codes with
Turbo Integrator
Here is a simple DB that will show you how to process numeric codes to string codes
through Turbo Integrator.
1. First I have a .csv file that looks like the following – however following your
scenario
1 = labor, 2=hour, 3=a, 4=b, 5=c, 6=d
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
17
Turbo Integrator Data Processing Tips and Techniques
18
2. With this in mind I need to create a lookup cube which looks like the following:
3. Once this is done I can process the information into a cube which I called “Mark”
4. To start bring up a process for TI and select the codes.txt file for the ASCII file.
5. Once that is done you will see the information entered as follows:
•
•
•
•
Make sure sample value 1 is set to string and not numeric
E1 will be string as well
And 25 is set to data
The key here is to enter a New Variable for your conversion process – name it V3
set it as string and as and element.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
6. Double click the Formula cell and enter the following formula:
IBM Cognos Confidential Information
19
Turbo Integrator Data Processing Tips and Techniques
7. In this example I created a cube and stored the values in it:
8. I also created 2 new dimensions:
IBM Cognos Confidential Information
20
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
21
Turbo Integrator Data Processing Tips and Techniques
22
9. When it is all done save the process and execute and your cube should look like
the following:
6 Decimal Placements and Turbo Integrator
Just to get a better understanding. TI functions:
(ie. DATASOURCEASCIITHOUSANDSEPARATOR and
DATASOURCEASCIIDCIMALSEPARATOR)
Located in the Advance Tabs will overwrite the Number Delimiters on the Data
Source Tab.
Situation 1:
If you use an ASCII file and you set all variables to “other” and this is placed in the
prolog:
dVal = 123456.789;
dVal = NUMBR(TRIM(STR(dVal, 6, 0)));
asciioutput('vals.log', STR(dVal, 6, 2));
It will take on the defaults on Data Source tab of the TI process.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
Here are my results
Situation 2:
Take the above TI Process and switch the Number Delimiters
Here are my results
Situation 3:
Change the TI process by adding to the Prolog tab
DATASOURCEASCIITHOUSANDSEPARATOR = '';
dVal = 123456.789;
dVal = NUMBR(TRIM(STR(dVal, 6, 0)));
asciioutput('vals.log', STR(dVal, 6, 2));
Results:
IBM Cognos Confidential Information
23
Turbo Integrator Data Processing Tips and Techniques
Now change:
DATASOURCEASCIITHOUSANDSEPARATOR = ',';
Results:
Situation 4:
Add this function to prolog tab
DATASOURCEASCIIDECIMALSEPARATOR= '';
dVal = 123456.789;
dVal = NUMBR(TRIM(STR(dVal, 6, 0)));
asciioutput('vals.log', STR(dVal, 6, 2));
Results:
Now Change
DATASOURCEASCIIDECIMALSEPARATOR= '.';
Results:
IBM Cognos Confidential Information
24
Turbo Integrator Data Processing Tips and Techniques
25
Here is a different view
DATASOURCEASCIIDECIMALSEPARATOR= '.';
dVal = 123456.789;
#dVal = NUMBR(TRIM(STR(dVal, 6, 0)));
asciioutput('vals.log', STR(dVal, 6, 2));
7 Concatenate Two String Elements using Turbo
Integrator
011563625003808005435003701LAWRENCEVIGA01006221B7GL2AN41S1618
73001001 721870000750100T02000000045027O005435002 04040401001
You can take this data and place it into a simple Notepad file or in Excel and save it
as .TXT or .CSV
You point your TI process to this file and that data will come in like this:
Add a new variable and set it up like this
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
26
Enter this in for the formula
v1=subst(v0,1,2)|subst(v0,56,8);
Run the TI process and the end result is this
8 Using the WHERE Clause in TI with String Variables
The following steps will illustrate using the WHERE clause in TI with string variables.
1. Create TI process with just the select statement
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
27
Turbo Integrator Data Processing Tips and Techniques
2. Choose the variable you want to work with and set it to ‘other”
IBM Cognos Confidential Information
28
Turbo Integrator Data Processing Tips and Techniques
3. Create a parameter and set it to “String”
IBM Cognos Confidential Information
29
Turbo Integrator Data Processing Tips and Techniques
4. Go to the Prolog tab and set your parameter to something specific
IBM Cognos Confidential Information
30
Turbo Integrator Data Processing Tips and Techniques
5. Just for testing purposes you can create and ASCIIout statement to make
sure you getting the right information back
6. Save TI process
IBM Cognos Confidential Information
31
Turbo Integrator Data Processing Tips and Techniques
32
6. Now here is where the TI quirks come in. Open up the TI process again and
add the Where statement. DO NOT HIT THE VARIABLES TAB
7. Just save the TI process and run it. Check the ASCIIoutput file to see if the
data returned is valid.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
9 Adding Clients to Groups using Turbo Integrator
I want to add new clients and add them to the 3 groups (group1, 2 and 3)
I will use a simple ASCII file that looks like this:
IBM Cognos Confidential Information
33
Turbo Integrator Data Processing Tips and Techniques
Using this ASCII file I set up my TI process
IBM Cognos Confidential Information
34
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
35
Turbo Integrator Data Processing Tips and Techniques
IBM Cognos Confidential Information
36
Turbo Integrator Data Processing Tips and Techniques
37
Important that you update the client dimension because you are adding new clients.
The group dimension will remain as is because they are already set the DB.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
38
Execute the process and it should run no problem the TI process works like a charm
Here are your new users
Here are the group settings
10 Turbo Integrator Tips
"@=" is needed when using string variables NOT just "=" when using the IF
statement
"|" is used for concatenation or joining two text or string variables INSTEAD of "&"
Remember to add the ";" after each new variable formula and for those using the
script remember it after each TI statement line
To improve the speed of updating cubes turn OFF cube logging BUT you lose an
audit trail of what was loaded. True, but I want speed!
When using the ASCIIOutput function, all variables must be a variable type of
STRING. Also remember to SKIP ZEROS unless absolutely needed for Cube Views for
TI.
Use the TI UI to create the base of the script and learn how to create and modify the
TI script in the advanced tab. You will save time and frustration.
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
39
11 TI Tidbits
Why can't I view/edit a chore when it is active? In order to even view it,
I have to deactivate it before I can see when it executes.
***
You need to de-activate a chore because it is a server process. If the chore is not
de-activated
it may have a TI process kick off before you have time to edit it.
To pass a string to a blank sample value using an IF statement
Ex: v1=IF([email protected]='','foo',v1);
When I go to process this data to create dimensions variable v1 will have element
“foo” in the dimension.
Any errors occurring in TI should appear in the a file in the server's data
directory whose name is TM1ProcesError"ProcessName".log
IBM Cognos Confidential Information
Turbo Integrator Data Processing Tips and Techniques
40
You must be part of the ADMIN group to be able to use Turbo Integrator (TI).
Being part of a group that has ADMIN privileges is different and does not
recognize TI. This was done by design because TI is a separate product from
Tm1 client/server.
IBM Cognos Confidential Information
Fly UP