New Topic

Community Topic Question Data Sources

Category Data Sources

Product Version 7.4

EAV Support

Alex shared this question ago

Answered

Sorry, if it's duplicate post, could not find anything similar.

So the question is:

Is there any chance to implement key-value support for views ?

In very rough implementation it's:

entity_id  | key | value |
1             | k1    | v1    |
1             | k2    | v2    |
1             | k3    | v3    |
2             | k1    | z1    |
2             | k2    | z2    |
2             | k3    | z3    |

So I want to get report like that:

id | k1 | k2 | k3
1 | v1 | v2 | v3
2 | z1 | z2 | z3

It's not possible to create another table with flat architecture such as in reports.

Thank you in advance for any suggestions/insight

Best regards,

Alex

The same question

Replies (5)

Paul Thornton ●

Hi Alex,

From what I can understand, you want to concatenate values together from different columns from your DB / Table?

If this is correct, then this ticket may be of assistance or do what you need.

https://community.yellowfinbi.com/topic/how-do-i-concatenate-more-than-two-columns

If not, can you please provide a little more information, so I can better understand the question.

Regards,

Paul

Reply URL

Alex ●

Hello Paul!

Thank you for answer!

No, it's not concatenate, in case of concatenate we would have only one column, but in this case I would like to have all columns available.

I'll try to explain this into details.

Some systems allow to build entity by customers due to their needing. Lets imagine situation where we need to describe a "Person" any person has attributes:

name
age

So with standard flat architecture we would have 3 columns (id, name, age).

Bu so if customer wants to add address ? We would need to alter table and add column which is not really good approach, especially if customer is changing these columns, furthermore there is limitation in column name.

In this situation Entity Attribute Value pattern helps us.

Idea is in storing key-value rather than alter table.

Structure of the table in the simplest implementation will be:

entity_id
key
value

And example content of that record for 2 persons

| 1 | name     | Peter    |
| 1 | age        | 18         |
| 1 | address | NSW...  |
| 2 | name     | Stuart   |
| 2 | age        | 22         |
| 2 | address | VIC...  |

The question is:

how to build view that columns

id
name
age
address

would be available for building report ?

Hello Paul!

Thank you for answer!

No, it's not concatenate, in case of concatenate we would have only one column, but in this case I would like to have all columns available.

I'll try to explain this into details.

Some systems allow to build entity by customers due to their needing. Lets imagine situation where we need to describe a "Person" any person has attributes:

name
age

So with standard flat architecture we would have 3 columns (id, name, age).

In this situation Entity Attribute Value pattern helps us.

Idea is in storing key-value rather than alter table.

Structure of the table in the simplest implementation will be:

entity_id
key
value

And example content of that record for 2 persons

| 1 | name     | Peter    |
| 1 | age        | 18         |
| 1 | address | NSW...  |
| 2 | name     | Stuart   |
| 2 | age        | 22         |
| 2 | address | VIC...  |

The question is:

how to build view that columns

id
name
age
address

would be available for building report ?

URL

Paul Thornton ●

Hi Alex,

Thank you for the clarification on what you are trying to achieve. I have been liaising with our Development team on this and as it stands, Yellowfin does not natively support key-value architecture. There is a possible solution to this for which you could write an SQL query, however this could have an impact on the time it takes to generate the report. Please see below for an example on an SQL query.

---

KeyValueTable is of the form:

SELECT a.VALUE, b.VALUE, c.VALUE, d.VALUE

FROM KeyValueTable a

INNER JOIN KeyValueTable b ON (a.ID = b.ID)

INNER JOIN KeyValueTable c ON (b.ID = c.ID)

INNER JOIN KeyValueTable d ON (c.ID = d.ID)

WHERE a.KEY = 'COLUMN_KEY1'

AND b.KEY = 'COLUMN_KEY2'

AND c.KEY = 'COLUMN_KEY3'

AND d.KEY = 'COLUMN_KEY4'

---

In the mean time, I will put this forward to the dev team as an enhancement but cannot guarantee this would be done, however if this is implemented, I will advise you accordingly.

Thanks,

Paul

Reply URL

Alex ●

Thank you, Paul for your response!

Is there any chance to support key-value architecture ?

As I remember there was no way to use filters/parameters and SQL based view. How it's now ? Is it possible to do so ?

Look forward to hear from you.

Thank you in advance,

Best regards,

Alex

URL

Paul Thornton ●

Hi Alex,

I have not heard anything back from in regards to this so just seeing how it is going. If you could get back to me and let me know if you need further help or whether this has been resolved, that would be great.

Regards,

Paul

Reply URL

Paul Thornton ●

Hi Alex,

Just touching base to see how you went with this or would you like me to close off this ticket?

Regards,

Paul

Reply URL

Alex ●

Hi Paul,

So far I see that problem unsolvable.

But you can close it as I do not think it will be resolved in the nearest feature

Reply URL