Can we merge variable dimension?

building reports webintelligence
1

We have two tables. Table_A has dimension (key, date, cost, reference_code). Table_B has field (ref_code, bi_code) where ref_code is the prefix of the reference_code in Table_A

For example,

Table_A
1, 1-1-12, 100, AX-123
2, 1-1-12, 200, TY-234
3, 1-1-12, 150, CY-126
4, 1-1-12, 200, AX-123

Table_B
AX, GDS
TY, INV
CY, TEL

This two tables are incompatible. Our task is to put the fields into single block. As they are incompatible, we should use merge dimension.

However, there is no common dimension in these two queries. Any idea whether we could group 2 tables using variable dimensions? Thank you

summoner :hong_kong: (BOB member since 2008-12-18)

2

If reference code in table A is always having a hyphen (-) then you can create a dimension object that gives you the prefix part from table A, when you create two DP’s in the report, you can merge on this new object from 1st DP and ref_code from 2nd DP !!

Next, create all the other text dimensions to detail objects and try to bring them in one report table…!!

Will that help ?

BO_Chief :us: (BOB member since 2004-06-06)

3

Why not just join the tables at universe level using substring? Then you won’t have any incompatibility issues.

(pseudocode)
tableB.ref_code=substring(tableA.referencecode,1,2)

Debbie

Debbie :uk: (BOB member since 2005-03-01)

4

Both solutions works. However, it requires modification on universe.

We targeted to minimize the changes on universe level. As this issue was raised out by users, we would like to teach them how to use the WEBI for handling the case.

summoner :hong_kong: (BOB member since 2008-12-18)

5

One cannot merge webi variables, period.

Andreas :de: (BOB member since 2002-06-20)

6

Why? Surely the job of the universe designer is to make life easier for the users and the business? It’s a five minute job at universe level versus training users and them having to do it in every report, have the reports take longer to run and a possibility of cartesian errors and wrong results from the query?

Debbie

Debbie :uk: (BOB member since 2005-03-01)

7

Say there is a code ABCDEFGHIJK in table Sales. It have several mapping methods for different business units with different purpose.

One will split it as ABC, DEFG, HIJK where ABC is used to find the principal category. The other one will split it as ABCD, EFGHIJK for customer services.

The split criteria varies frequently. The universe has quite a lot of objects and already confusing our clients. Adding too much dimensions inside universe degrade the satisfication of using BO.

FYI, our clients still prefer vLookup in Excel and resist to move to BO tools. :twisted:

summoner :hong_kong: (BOB member since 2008-12-18)