Amareshwari Sriramadasu created LENS-752:
--------------------------------------------
Summary: Support flattening of columns selected through
bridge-tables(many-to-many relationships)
Key: LENS-752
URL: https://issues.apache.org/jira/browse/LENS-752
Project: Apache Lens
Issue Type: New Feature
Components: cube
Reporter: Amareshwari Sriramadasu
Fix For: 2.4
Data warehousing concepts suggests to use bridge-tables to implement
many-to-many relationships between cube & dimension or between two dimensions.
Here are few links explaining the bridge-tables
http://www.pythian.com/blog/implementing-many-to-many-relationships-in-data-warehousing/
http://www.kimballgroup.com/2012/02/design-tip-142-building-bridges/
http://stackoverflow.com/questions/2785158/star-schema-fact-1n-dimension-how
If we look at the following schema :
User :
|ID|Name|Gender|
|1|A|M|
|2|B|M|
|3|C|F|
User interests :
|UserID| Sports ID|
|1|1|
|1|2|
|2|1|
|2|2|
|2|3|
Sports :
|SportsID| Description|
|1|Football|
|2|Cricket|
|3|Basketball|
User Interests is the bridge table which is capturing the many-to-many
relationship between Users and Sports.
If we have a fact as follows :
|UserId| Revenue|
|1|100|
|2|50|
If analyst is interested in analyzing with respect to user's interest, then the
report would the following :
|User Interest|Revenue|
|Football|150|
|Cricket|150|
|BasketBall|50|
Though the individual rows are correct and the overall revenue is actually 150,
looking at above report makes people assume that overall revenue is 350.
The feature ask here is to optionally flatten the selected fields, if fields
involved are coming from bridge tables in join path. If flattening is enabled,
the report would be the following :
|User Interest|Revenue|
|Football, Cricket|100|
|Football, Cricket, BasketBall|50|
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
