Chart navigation issue

If you want to navigate to another request or dashboard from one chart but it would not work, please check it by the following steps: here we use Gauge as one case.

1) Check the Additional Gauge Properties to ensure you added the related navigation

2) If you added the link, please check the other dimension columns, make sure that the length of this values less than 13 Chinese words. Such as if the values is ‘上海现代建筑设计（集团）有限公司’ , it will fails, but if it is ‘上海现代建筑设计（集团）有’, it works.

One solution on CHM issue.

if your CHM files could not be opened when your system is XP+SP2, please try the following way,

create one TXT file, and copy the below script into it,

REGEDIT4 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp] [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\ItssRestrictions] "MaxAllowedZone"=dword:00000003

save it as *.REG file and run it. then you can read your chm file freely.

Try it.

ETL - Tips on Transformations - Joiner Transformation

The following tips come from bookshelf, it is not my idea yet, once I complete the case i will change that with the example.

Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. You can also join data from the same source. The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources.The two input pipelines include a master pipeline and a detail pipeline or a master and a detail branch. The master pipeline ends at the Joiner transformation, while the detail pipeline continues to the target.

To join more than two sources in a mapping, join the output from the Joiner transformation with another source pipeline. Add Joiner transformations to the mapping until you have joined all the source pipelines.The Joiner transformation accepts input from most transformations. However, consider the following limitations on the pipelines you connect to the Joiner transformation:♦You cannot use a Joiner transformation when either input pipeline contains an Update Strategy transformation.♦You cannot use a Joiner transformation if you connect a Sequence Generator transformation directly before the Joiner transformation.

when you use joiner transformation what you should do as below,

1) Defining a Join Condition;

such as EMP_ID1 = EMP_ID2;

2)Defining the Join Type;

a) Normal Join

With a normal join, the Integration Service discards all rows of data from the master and detail source that do not match, based on the condition.For example, you might have two sources of data for auto parts called PARTS_SIZE and PARTS_COLOR with the following data:

To join the two tables by matching the PART_IDs in both sources, you set the condition as follows:

PART_ID1 = PART_ID2

When you join these tables with a normal join, the result set includes the following data:

The following example shows the equivalent SQL statement:SELECT * FROM PARTS_SIZE, PARTS_COLOR WHERE PARTS_SIZE.PART_ID1 = PARTS_COLOR.PART_ID2;

b) Master Outer Join

A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.When you join the sample tables with a master outer join and the same condition, the result set includes the following data:

Because no size is specified for the Fuzzy Dice, the Integration Service populates the field with a NULL.The following example shows the equivalent SQL statement:SELECT * FROM PARTS_SIZE RIGHT OUTER JOIN PARTS_COLOR ON (PARTS_SIZE.PART_ID1 = PARTS_COLOR.PART_ID2);

c)Detail Outer Join

A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.When you join the sample tables with a detail outer join and the same condition, the result set includes the following data:

Because no color is specified for the Ash Tray, the Integration Service populates the field with a NULL.

The following example shows the equivalent SQL statement:SELECT * FROM PARTS_SIZE LEFT OUTER JOIN PARTS_COLOR ON (PARTS_COLOR.PART_ID2 = PARTS_SIZE.PART_ID1);

d) Full Outer Join

A full outer join keeps all rows of data from both the master and detail sources.When you join the sample tables with a full outer join and the same condition, the result set includes:

Because no color is specified for the Ash Tray and no size is specified for the Fuzzy Dice, the Integration Service populates the fields with NULL.The following example shows the equivalent SQL statement:SELECT * FROM PARTS_SIZE FULL OUTER JOIN PARTS_COLOR ON (PARTS_SIZE.PART_ID1 = PARTS_COLOR.PART_ID2)

ETL - Tips on Transformations - Lookup procedure

Transformations can be active or passive. if the number of rows is changed that pass through one transformation we call it an active transaction, such as Filter transformation; otherwise, if the number of rows is not changed, we call passive transformation, such as Expression transformation.
Farther more, one transformation can be unconnected or connected to the data flow, an unconnected one can be called within another transformation(a value will be retured).

Here I will give a explaination to the difference between unconnected Lookup procedure and connected Lookup procedure.

Like we said as above, for the unconnected Lookup procedure, it is separate from the data flow, and for the connected Lookup procedure, it is joined into the mapping pipeline, and has the input and then output ports.
1) unconnected Lookup procedure.





From the above screenshot, there is one unconnected Lookup procedure named LKP_W_EXCH_RATE_G, it is called by one procedure named EXP_PLP_AP_INCR_ACTIVITY_LOAD in pipeline, one expression is wroted for one port as following in EXP_PLP_AP_INCR_ACTIVITY_LOAD ,




IIF(LOC_CURR_CODE=CURRENCY_CODE,VAR_ONE_AMT,:LKP.LKP_W_EXCH_RATE_G(DATASOURCE_NUM_ID,'M',LOC_CURR_CODE,CURRENCY_CODE,POSTED_ON_DT))

for the above :LKP expression, there are 5 parameters, which are the input of the unconnected Lookup procedure. and one the lookup result will be used by the expression.

Note:

a) If you call a connected Lookup transformation in a :LKP expression, the Designer marks the mapping invalid;

b) Best practice: adding index to every columns of the lookup table in database will be very helpfull for the performance if the lookup table is a large one.

c) If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

2) Connected Lookup procedure

ETL - Tips on ETL develoment

When we do the development of mappings, workflows, there are some good practice, I will continuely list them in the following days,

Tips on mapping development
1) We can set the type the transformation to reusable, if we want reuse it. set the checkbox named Make reusable checked status.




2)Mapplet is one objects group with a standardized set of transformation logic. if some are used in your mapping, you can expand them in the Mapping Designer, so you can see all the objects(transformations) here, but need not open the mapplet in the mapplet designer.Click Mappings > Expand from the main menu. This expands the mapplet within the mapping for view. NOTE: but you cannot edit any of the properties.


3)one property of target table: Reject truncated and overflow data. (need to test to track it);
4)one property of target table: Table name prefix. (Need to test if it wants to format the table name like cmst.tablename).

ETL - Tips on propagating port attributes

It is a good way for us to propagate the other transformations' related ports when we give some changes to one transformation's port(such as the port name, datatype, precision, scale, and description...).

But please note, The Designer does not propagate changes to the following mapping objects:
1)Unconnected transformations;
2)Reusable transformations;
3)Mapplets;
4)Source and target instances;
5)SDK Source Qualifier;

and if you want to get more inforamtion about propagating port attributes, please refer to the bookshelf.

ETL - Tips on Linking Ports

When you develop the mapping, if you want link the ports of two transformations, You can manually link ports, or you can automatically link ports between transformations. When you link ports automatically, you can link by position or by name. I think it is easy to understand the manual way; here I just list the difference between automatica linking by position and name;

1) Automatic Linking by Position
When you link by position, the Designer links the first output port to the first input port of the another transformation, the second output port to the second input port, and so on. so the ports will be kept in the same order for the two transformations.
2) Automatic Linking by Name
Another way, You can link ports by name in the Designer. The Designer adds links between input and output ports that have the same name. Linking by name is not case-sensitive. Link by name when you use the same port names across transformations. The Designer can link ports based on prefixes and suffixes you define. Link by name and prefix or suffix when you use prefixes or suffixes in port names to distinguish where they occur in the mapping or mapplet.

you can Click Layout > Autolink in mapping or mapplet window to get more infomation.