Merge dimension issue

[Former Member]

Hi All,

Need help on this

Issue:

The issue is in using the u2018Merge Dimensionsu2019 utility. After I merge the two results based on the dimension they share in common, I get duplicate rows.

Background:

Universe #1 and Universe #2 have a dimension in common u2013 UCS ID. The user builds a query against Universe #1 and brings back the distinct UCS ID and the UCS Name. He builds a second query against Universe #2. The query brings back UCS ID and passenger count.

The results of query #1 are:

UCS ID UCS NAME

=====================

257 Motorola

5 AON Service Corporation

6 Altria Client Services Inc

The results of Query #2 are:

UCS ID Passenger Count

=========================

257 6892

5 2767

6 382

The user would like to see the results of the merge dimensions utility to show up as one row for each UCS ID, as shown below:

UCS ID UCS NAME Passenger Count

257 Motorola 6892

5 AON Service Corporation 2767

6 Altria Client Services 382

Instead, they get the following (i.e, duplicate row with a null or blank):

UCS ID UCS NAME Passenger Count

257 Motorola

..............................................................................

257 6892

.............................................................................

5 AON Service Corporation

...................................................................................

5 2767

.................................................................................

6 Altria Client Services

...................................................................................

6 382

Environment details:

BOXIR2,SP2.

Database :SQL server

Thnnks in advance,

[Former Member]

Hi Sreeni,

Thanks for your reply

We have already tried by checking the options Avoid duplicate rows aggregation and show rows with empty but no luck.

Thanks in advance,

swapna.

[Former Member]

Hi Jacques,

Thanks for your reply

-We checked that both the merged dimensions are using same data type(varchar).

- Even tried to run both the queries at the database end it retrieves the correct data without any duplicate or null values.

- The issue is mainly when we drag all the dimension and measure objects it either displays a null values for the entire column or displays a combination of duplicate and null values in the table.

Thanks in advance.

[Former Member]

Hi,

Please check Avoid duplicate rows aggregation and show rows with empty and let me know.

Sreeni

[Former Member]

Hi Swapna,

The rules for merged dimesions are that must match exactly i.e Same data type, same syntax etc.

One can avoid some of these issues if you use a common dimension table in the universe and both queries use this same dimesion.

To to a test on where you issue is, drag the dimensions in a report one-by-one and see which one is causing a duplicate. Sometimes dimensions can also have spaces i.e when one is of a data type varchar, and the other of char... here you can use the trim function in the report to eliminate any leading or trailing spaces.

Hope this helps

Jacques