[jira] [Created] (LENS-752) Support flattening of columns selected through bridge-tables(many-to-many relationships)

["Amareshwari Sriramadasu (JIRA)" <ji...@apache.org>]

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)