PDA

View Full Version : Search only match first element when dimension nameColumn use the same description.



reynunez
04-05-2016, 08:13 AM
I have the following level declaration at a Payers dimension hierarchy:

<Level name="payer" visible="true" column="payor_id" nameColumn="payor_name" ordinalColumn="payor_id" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Payer">
</Level>


Here is the problem. In some cases we are transferring data from the Master Data to the Warehouse were we have records with unique column (Key element) , but the same description at the nameColumn, as you can see with the following sample:

column= 1234 nameColumn=John Smith
column= 1235 nameColumn=John Smith
column= 1236 nameColumn=John Smith
column= 1237 nameColumn=John Smith

When performing a search, the MDX will look like this:

select NON EMPTY {[Measures].[total_payments]} ON COLUMNS,
NonEmptyCrossJoin([d_organization.h_organization].[business_entity].Members, {[d_security.h_security].[1]}) ON ROWS
from [payments_cube]
where {[d_payers.h_payer_group_type].[GROUP1].[GROUP_TYPE_1].John Smith],
[d_payers.h_payer_group_type].[GROUP1].[GROUP_TYPE_1].[John Smith],
[d_payers.h_payer_group_type].[GROUP1].[GROUP_TYPE_1].[John Smith],
[d_payers.h_payer_group_type].[GROUP1].[GROUP_TYPE_1].[John Smith]}

The payer (last level in hierarchy) is belong to the same GROUP1 and GROUP_TYPE but they are different at the payer level, per column id. Mondrian will only match the first record total and not the rest. Is there a way to configure the schema to use the column identifier to properly aggregate those records in the results set?

reynunez
04-08-2016, 06:26 AM
As a possible solution to this problem, I also try using captionColumn instead of name column. This solve the problem with the filtering, however it brings the key "column" ids on the results set rather than the descriptive text.
There should be a flag or configuration at the hierarchy level to designate the filter field, as well as the results field. It should be part of the Mondrian logic to utilize the column as a key for filtering and the nameColumn for display purposes or MDX query.