I have two tables ( aggregated tables) Client_Aggregate and Market_Volume ( more tables may get added with common dimension names and different measures) with respective columns as
Client_Aggregate :
Dimensions
DATE
CLIENT_NAME
ACRONYM
SALES_TRADER
ACCOUNTING_BOOK
SYMBOL
SIDE
SECTOR
COVERAGE_TRADER
DESK
BUYING_CENTER_ID
BUYING_CENTER_NAME
DISTRIBUTION_FACTOR
Measures:
REVENUE,
VOLUME,
SOFTCOMM,
NOTIONAL
*******************************************
Market_Volume :
Dimensions
DATE
SYMBOL
SECTOR
Measures
MARKET_VOLUME
OBSERVATIONS :
1. Granularity is different for both tables
2. Tried creating a link table but market Volume data gets inflates.
3. Tried below
ClientAggregate:
load
DATE &'|'&
[CLIENT NAME] &'|'&
ACRONYM &'|'&
SALES_TRADER &'|'&
ACCOUNTING_BOOK &'|'&
SYMBOL &'|'&
SIDE &'|'&
SECTOR &'|'&
COVERAGE_TRADER &'|'&
DESK &'|'&
BUYING_CENTER_ID &'|'&
BUYING_CENTER_NAME &'|'&
DISTRIBUTION_FACTOR as %Master_Key ,
REVENUE,
VOLUME,
SOFTCOMM,
NOTIONAL_LOCAL
Market_Volume
load
DATE &'|'& '<ANY>' &'|'& '<ANY>' &'|'& '<ANY>' &'|'& '<ANY>' &'|'& SYMBOL &'|'& '<ANY>' &'|'& SECTOR &'|'& '<ANY>' &'|'& '<ANY>' &'|'& '<ANY>' &'|'& '<ANY>' &'|'& '<ANY>' as %Master_Key ,
MARKET_VOLUME
How should I create dimension tables out of this which could be provided for user selection
