example FinanceColim1

####################################
# Financial Data Warehousing

typeside Ty = literal {
	external_types
		Integer -> "java.lang.Integer"
		String -> "java.lang.String"
		Double -> "java.lang.Double"
		Boolean -> "java.lang.String"
		Date -> "java.lang.String"
	external_parsers
		Integer -> "parseInt;"
		String -> "x => x"
		Double -> "x => java.lang.Double.parseDouble(x)"
		Boolean -> "x => x"
		Date -> "x => x"
	external_functions
		keygen : Integer,Integer,Integer -> Integer = "(x, y, z) => (2^x * 3^y * 5^z)"
}

####################################

schema Client = literal : Ty {
	entities
		client
	attributes
		id : client -> Integer
		name description : client -> String
}

instance ClientInstance = literal : Client {
     generators
     	cc1 cc2 cc3 : client
     multi_equations
     	id -> {cc1 1, cc2 2, cc3 3}
     	name -> {cc1 Tom, cc2 Dick, cc3 Harry}
     	description -> {cc1 "Tom Client", cc2 "Dick Client", cc3 "Harry Client"}
}

####################################

schema Portfolio = literal : Ty {
	entities
		strategy
		portfolio
	foreign_keys
		strategy : portfolio -> strategy
		parent : portfolio -> portfolio
	path_equations
		portfolio.parent.parent = portfolio.parent
	attributes
		id : strategy -> Integer
		name description : strategy -> String
		id : portfolio -> Integer
		name description : portfolio -> String
		client_id strategy_id parent_id : portfolio -> Integer
	observation_equations
		forall p. p.strategy.id = p.strategy_id
		forall p. p.parent.id = p.parent_id
}

instance PortfolioInstance = literal : Portfolio {
    generators
     	ps1 ps2 ps3 : strategy
     	pp1 pp2 pp3 : portfolio
	multi_equations
		parent_id -> {pp1 1, pp2 2, pp3 2}
		parent -> {pp1 pp1, pp2 pp2, pp3 pp2}
		id -> {ps1 1, ps2 2, ps3 3, pp1 1, pp2 2, pp3 3}
		name -> {ps1 Strat1, ps2 Strat2, ps3 Strat3, pp1 Port1, pp2 Port2, pp3 Port3}
		description -> {ps1 Strategy1, ps2 Strategy2, ps3 Strategy3, pp1 Portfolio1, pp2 Portfolio2, pp3 Portfolio3}
		strategy_id -> {pp1 2, pp2 2, pp3 1}
		client_id -> {pp1 1, pp2 2, pp3 2}
		strategy -> {pp1 ps2, pp2 ps2, pp3 ps1}
}

####################################

schema Ref = literal : Ty {
	entities
		country
		currency
		asset
		strategy
	foreign_keys
		country : currency -> country
		strategy : asset -> strategy
	attributes
		id : country -> Integer
		code name : country -> String
		id strategy_id : asset -> Integer
		name description : asset -> String
		id : strategy -> Integer
		name description : strategy -> String
		id country_id : currency -> Integer
		code name : currency -> String
	observation_equations
		forall c. c.country.id = c.country_id
		forall a. a.strategy.id = a.strategy_id
}

instance RefInstance = literal : Ref {
     generators
     	rcty1 rcty2 rcty3 rcty4 rcty5 rcty6 : country
		rcur1 rcur2 rcur3 : currency
		ra1 ra2 ra3 ra4 ra5 ra6 ra7 ra8 ra9 ra10 : asset
		rs1 rs2 rs3 rs4 rs5 : strategy
	multi_equations
		country -> {rcur1 rcty6, rcur2 rcty4, rcur3 rcty5}
		country_id  -> {rcur1 6, rcur2 4, rcur3 5}
		strategy -> {ra1 rs1, ra2 rs3, ra3 rs2, ra4 rs4, ra5 rs5, ra6 rs4, ra7 rs4, ra8 rs5, ra9 rs3, ra10 rs1}
		strategy_id  -> {ra1 1, ra2 3, ra3 2, ra4 4, ra5 5, ra6 4, ra7 4, ra8 5, ra9 3, ra10 1}
		id  -> {rcty1 1, rcty2 2, rcty3 3, rcty4 4, rcty5 5, rcty6 6, rcur1 1, rcur2 2, rcur3 3, ra1 1, ra2 2, ra3 3, ra4 4, ra5 5, ra6 6, ra7 7, ra8 8, ra9 9, ra10 10, rs1 1, rs2 2, rs3 3, rs4 4, rs5 5}
		code  -> {rcty1 AU, rcty2 CH, rcty3 CN, rcty4 EU, rcty5 JP, rcty6 US, rcur1 USD, rcur2 EUR, rcur3 JPY}
		name  -> {ra1 A1, ra2 A2, ra3 A3, ra4 A4, ra5 A5, ra6 A6, ra7 A7, ra8 A8, ra9 A9, ra10 A10, rs1 Strat1, rs2 Strat2, rs3 Strat3, rs4 Strat4, rs5 Strat5, rcty1 Australia, rcty2 Switzerland, rcty3 China, rcty4 "European Union", rcty5 Japan, rcty6 "United States of America", rcur1 "US Dollar", rcur2 "Euro", rcur3 "Japanese Yen" }
		description  -> {ra1 Asset1, ra2 Asset2, ra3 Asset3, ra4 Asset4, ra5 Asset5, ra6 Asset6, ra7 Asset7, ra8 Asset8, ra9 Asset9, ra10 Asset10, rs1 Strategy1, rs2 Strategy2, rs3 Strategy3, rs4 Strategy4, rs5 Strategy5}
}

####################################

schema Trans = literal : Ty {
	entities
		asset
		currency
		transaction
	foreign_keys
		asset : transaction -> asset
		currency : transaction -> currency
	attributes
		id : asset -> Integer
		name description : asset -> String
		id : currency -> Integer
		code name : currency -> String
		id asset_id portfolio_id quantity currency_id : transaction -> Integer
		date  : transaction -> Date	# added as a new column
		buy_sell_ind : transaction -> Boolean
		price : transaction -> Double
	observation_equations
		forall t. t.asset.id = t.asset_id
		forall t. t.currency.id = t.currency_id
}

instance TransInstance = literal : Trans {
     generators
		ta1 ta3 ta5 ta7 ta9 : asset
		tc1 tc2 tc3 : currency
		tt1 tt2 tt3 tt4 tt5 tt6 : transaction
	multi_equations
		id -> {ta1 1, ta3 3, ta5 5, ta7 7, ta9 9, tc1 1, tc2 2, tc3 3, tt1 1, tt2 2, tt3 3, tt4 4, tt5 5, tt6 6}
		name -> {ta1 A1, ta3 A3, ta5 A5, ta7 A7, ta9 A9, tc1 "US Dollar", tc2 Euro, tc3 "Japanese Yen"}
		description -> {ta1 Asset1, ta3 Asset3, ta5 Asset5, ta7 Asset7, ta9 Asset9}
		code -> {tc1 USD, tc2 EUR, tc3 JPY}
		asset -> {tt1 ta1, tt2 ta1, tt3 ta3, tt4 ta3, tt5 ta5, tt6 ta5}
		asset_id -> {tt1 1, tt2 1, tt3 3, tt4 3, tt5 5, tt6 5}
		portfolio_id -> {tt1 1, tt2 1, tt3 2, tt4 2, tt5 3, tt6 3}
		buy_sell_ind -> {tt1 buy, tt2 sell, tt3 buy, tt4 sell, tt5 buy, tt6 sell}
		quantity -> {tt1 200, tt2 100, tt3 150, tt4 150, tt5 200, tt6 100}
		price -> {tt1 "5.51", tt2 "5.5", tt3 "2.5", tt4 "3.5", tt5 "1.6", tt6 "1.5"}
		date -> {tt1 "1/1/2011", tt2 "6/1/2011", tt3 "9/1/2013", tt4 "3/1/2014", tt5 "2/1/2013", tt6 "7/1/2013"}
		currency -> {tt1 tc2, tt2 tc2, tt3 tc1, tt4 tc1, tt5 tc3, tt6 tc3}
		currency_id -> {tt1 2, tt2 2, tt3 1, tt4 1, tt5 3, tt6 3}
}

####################################

schema HoldPos = literal : Ty {
	entities
		client
		holding
		position
	foreign_keys
		client : holding -> client
		client : position -> client
	attributes
		no : client -> Integer	# new name
		nm desc : client -> String	# new name
		id client_no portfolio_id asset_id quantity : holding -> Integer
		purchase_date begin_date end_date : holding -> Date
		purchase_price : holding -> Double
		currency_code : holding -> String	# switched from _id to _code
		id client_no asset_id quantity current_value cost_basis : position -> Integer	# new name
		current_value_currency_code cost_basis_currency_code : position -> String	# switched from _id to _code
	observation_equations
		forall h:holding. h.client.no = h.client_no
		forall p:position. p.client.no = p.client_no
}

instance HoldPosInstance = literal : HoldPos {
    generators
  		hpc1 hpc2 : client
		hph1 hph2 hph3 hph4 hph5 : holding
		hpp1 hpp2 : position
	multi_equations
		no -> {hpc1 1, hpc2 2}
		nm -> {hpc1 Tom, hpc2 Dick}
		desc -> {hpc1 "Tom Client", hpc2 "Dick Client"}
		id -> {hph1 1, hph2 2, hph3 3, hph4 4, hph5 5, hpp1 1, hpp2 2}
		client_no -> {hph1 1, hph2 1, hph3 2, hph4 2, hph5 2, hpp1 1, hpp2 2}
		client -> {hph1 hpc1, hph2 hpc1, hph3 hpc2, hph4 hpc2, hph5 hpc2, hpp1 hpc1, hpp2 hpc2}
		portfolio_id -> {hph1 1, hph2 1, hph3 3, hph4 3, hph5 3}
		asset_id -> {hph1 1, hph2 1, hph3 3, hph4 5, hph5 5}
		quantity -> {hph1 200, hph2 100, hph3 150, hph4 200, hph5 100, hpp1 100, hpp2 100}
		purchase_date -> {hph1 "1/1/2011", hph2 "1/1/2011", hph3 "9/1/2013", hph4 "2/1/2013", hph5 "2/1/2013"}
		begin_date -> {hph1 "1/1/2011", hph2 "6/1/2011", hph3 "9/1/2013", hph4 "2/1/2013", hph5 "7/1/2013"}
		end_date -> {hph1 "6/1/2011", hph3 "3/1/2014", hph4 "7/1/2013"}
		purchase_price -> {hph1 "5.51", hph2 "5.1", hph3 "2.5", hph4 "1.6", hph5 "1.6"}
		currency_code -> {hph1 EUR, hph2 EUR, hph3 JPY, hph4 USD, hph5 USD}
		asset_id -> {hpp1 1, hpp2 5}
		current_value -> {hpp1 550, hpp2 170}
		cost_basis -> {hpp1 551, hpp2 160}
		current_value_currency_code -> {hpp1 EUR, hpp2 JPY}
		cost_basis_currency_code -> {hpp1 EUR, hpp2 JPY}
}

####################################

schema_colimit Colimit = quotient Client + Ref + Trans + Portfolio + HoldPos : Ty {
	 entity_equations
		Client.client = HoldPos.client
		Ref.asset     = Trans.asset
		Ref.currency  = Trans.currency
		Ref.strategy  = Portfolio.strategy
	observation_equations
		forall x. x.Client_client_id          = x.HoldPos_client_no
		forall x. x.Client_client_name        = x.HoldPos_client_nm
		forall x. x.Client_client_description = x.HoldPos_client_desc

		forall x. x.Ref_strategy_id          = x.Portfolio_strategy_id
		forall x. x.Ref_strategy_name        = x.Portfolio_strategy_name
		forall x. x.Ref_strategy_description = x.Portfolio_strategy_description

		forall x. x.Ref_asset_id          = x.Trans_asset_id
		forall x. x.Ref_asset_name        = x.Trans_asset_name
		forall x. x.Ref_asset_description = x.Trans_asset_description

		forall x. x.Ref_currency_id   = x.Trans_currency_id
		forall x. x.Ref_currency_code = x.Trans_currency_code
		forall x. x.Ref_currency_name = x.Trans_currency_name
	options
		simplify_names=false
}

instance ClientFwd = sigma (getMapping Colimit Client) ClientInstance
instance PortfolioFwd = sigma (getMapping Colimit Portfolio) PortfolioInstance
instance RefFwd = sigma (getMapping Colimit Ref) RefInstance
instance TransFwd = sigma (getMapping Colimit Trans) TransInstance
instance HoldPosFwd = sigma (getMapping Colimit HoldPos) HoldPosInstance

instance CoProd = coproduct ClientFwd + PortfolioFwd + RefFwd + TransFwd + HoldPosFwd : getSchema Colimit

####################################

instance E = quotient_query CoProd {
	entity Client_client -> {
		from
			a:Client_client b:Client_client
		where
			a.Client_client_id = b.HoldPos_client_no
	}
	entity Ref_asset -> {
		from
			a:Ref_asset b:Ref_asset
		where
			a.Ref_asset_id = b.Trans_asset_id
	}
	entity Ref_currency -> {
		from
			a:Ref_currency b:Ref_currency
		where
			a.Ref_currency_id = b.Trans_currency_id
	}
	entity Ref_strategy -> {
		from
			a:Ref_strategy b:Ref_strategy
		where
			a.Portfolio_strategy_id = b.Ref_strategy_id
	}

	options
		quotient_use_chase = false
}

####################################

schema Target = literal : Ty {
	entities
		currency #Trans Ref
		strategy #Portfolio Ref
		asset #Trans Ref
		transaction #Trans
		client #HoldPos
		position #HoldPos
		country #Ref
		portfolioholding #NEW
		portfolio #Portfolio
		holding #HoldPos

	foreign_keys
		portfolio_strategy : portfolio -> strategy #Portfolio
		parent : portfolio -> portfolio

		position_client : position -> client #HoldPos
		position_asset : position -> asset #NEW
		position_current_value_currency : position -> currency #NEW
		position_cost_basis_currency : position -> currency #NEW

		asset_strategy : asset -> strategy #Ref

		currency_country : currency -> country #Ref

		transaction_currency : transaction -> currency #Trans
		transaction_asset : transaction -> asset #Trans
		transaction_portfolio : transaction -> portfolio #NEW

		holding_currency : holding -> currency #NEW
		holding_asset : holding -> asset

		portfolioholding_holding : portfolioholding -> holding #NEW
		portfolioholding_client : portfolioholding -> client #NEW
		portfolioholding_portfolio : portfolioholding -> portfolio #NEW

	path_equations
		portfolio.parent.parent = portfolio.parent

	attributes
		code : currency -> String #Trans Ref
		name : currency -> String #Trans Ref
		id : currency -> Integer #Ref
		country_id : currency -> Integer #Ref

		code : country -> String #Ref
		name : country -> String #Ref
		id : country -> Integer #Ref

		id : asset -> Integer #Trans Ref
		description : asset -> String #Trans Ref
		name : asset -> String #Trans Ref
		strategy_id : asset -> Integer #Ref

		name : strategy -> String #Ref Portfolio
		description : strategy -> String #Ref Portfolio
		id : strategy -> Integer #Ref Portfolio

		name : portfolio -> String #Portfolio
		description : portfolio -> String #Portfolio
		id : portfolio -> Integer #Portfolio
		strategy_id : portfolio -> Integer #Portfolio
		parent_portfolio_id : portfolio -> Integer	#Portfolio ADDED

		quantity : position -> Integer #HoldPos
		current_value : position -> Integer #HoldPos
		cost_basis : position -> Integer #HoldPos
		id : position -> Integer #HoldPos
		client_id : position -> Integer #HoldPos
		asset_id : position -> Integer #HoldPos
		current_value_currency_id : position -> Integer # HoldPos
		cost_basis_currency_id : position -> Integer # HoldPos

		id : holding -> Integer #HoldPos
		asset_id : holding -> Integer #HoldPos
		currency_id : holding -> Integer #HoldPos
		purchase_price : holding -> Double #HoldPos
		quantity : holding -> Integer #HoldPos #HoldPos
		purchase_date : holding -> Date #HoldPos
		begin_date : holding -> Date #HoldPos new attribute
		end_date : holding -> Date #HoldPos new attribute

		description : client -> String #HoldPos
		name : client -> String #HoldPos
		id : client -> Integer #HoldPos

		id : transaction -> Integer #Trans
		asset_id : transaction -> Integer #Trans
		buy_sell_ind : transaction -> Boolean #Trans
		quantity : transaction -> Integer #Trans
		price : transaction -> Double #Trans
		currency_id : transaction -> Integer #Trans
		portfolio_id : transaction -> Integer #Trans
		date : transaction -> Date	#added as a new column

		id : portfolioholding -> Integer #NEW todo: talk to Jee about this
		holding_id : portfolioholding -> Integer #NEW
		client_id : portfolioholding -> Integer #NEW
		portfolio_id : portfolioholding -> Integer #NEW

	observation_equations
		forall p. p.parent.id = p.parent_portfolio_id #added
		forall p. p.portfolio_strategy.id = p.strategy_id
		forall a. a.asset_strategy.id = a.strategy_id
		forall p. p.position_client.id = p.client_id
		forall c. c.currency_country.id = c.country_id
		forall t. t.transaction_currency.id = t.currency_id
		forall t. t.transaction_asset.id = t.asset_id
		forall p. p.portfolioholding_holding.id = p.holding_id
		forall p. p.portfolioholding_client.id = p.client_id
		forall p. p.portfolioholding_portfolio.id = p.portfolio_id
		forall p. p.position_asset.id = p.asset_id
		forall h. h.holding_currency.id = h.currency_id
		forall t. t.transaction_portfolio.id = t.portfolio_id
		forall p. p.position_current_value_currency.id = p.current_value_currency_id
		forall p. p.position_cost_basis_currency.id = p.cost_basis_currency_id
}

####################################

query ETL = literal : getSchema Colimit -> Target {
 entity currency -> {
 		from
 			c:Ref_currency
		attributes
			code -> c.Ref_currency_code
			name -> c.Ref_currency_name
			id -> c.Ref_currency_id
			country_id -> c.Ref_currency_country_id
		foreign_keys
		 	currency_country -> {c -> c.Ref_currency_country}
}

entity strategy -> {
		from
			s:Ref_strategy
		attributes
			name -> s.Ref_strategy_name
			description -> s.Ref_strategy_description
			id -> s.Ref_strategy_id
	}

entity	asset -> {
		from
			a:Ref_asset
		attributes
			id -> a.Ref_asset_id
			description -> a.Ref_asset_description
			name -> a.Ref_asset_name
			strategy_id -> a.Ref_asset_strategy_id
		foreign_keys
		 	asset_strategy -> {s -> a.Ref_asset_strategy}
	}

entity	client -> {
		from
			c:Client_client
		attributes
			description -> c.Client_client_description
			name -> c.Client_client_name
			id -> c.Client_client_id
	}

entity	country -> {
		from
			c:Ref_country
		attributes
			code -> c.Ref_country_code
			name -> c.Ref_country_name
			id -> c.Ref_country_id
	}

entity	transaction -> {
		from
			t:Trans_transaction p:Portfolio_portfolio c:Client_client c1:Client_client
		where
			t.Trans_transaction_portfolio_id = p.Portfolio_portfolio_id
		attributes
			id -> t.Trans_transaction_id
			asset_id -> t.Trans_transaction_asset_id
			buy_sell_ind -> t.Trans_transaction_buy_sell_ind
			quantity -> t.Trans_transaction_quantity
			price -> t.Trans_transaction_price
			date -> t.Trans_transaction_date
			currency_id -> t.Trans_transaction_currency_id
			portfolio_id -> t.Trans_transaction_portfolio_id
		foreign_keys
			transaction_currency -> {c -> t.Trans_transaction_currency}
			transaction_asset -> {a -> t.Trans_transaction_asset}
			transaction_portfolio -> {p -> p} 	#NEW
	}

entity	position -> {
		from
			p:HoldPos_position a:Ref_asset cv:Ref_currency cb:Ref_currency
		where
			p.HoldPos_position_asset_id = a.Ref_asset_id
			p.HoldPos_position_current_value_currency_code = cv.Ref_currency_code
			p.HoldPos_position_cost_basis_currency_code = cb.Ref_currency_code
		attributes
			quantity -> p.HoldPos_position_quantity
			current_value -> p.HoldPos_position_current_value
			cost_basis -> p.HoldPos_position_cost_basis
			id -> p.HoldPos_position_id
			client_id -> p.HoldPos_position_client_no
			asset_id -> p.HoldPos_position_asset_id
			current_value_currency_id -> cv.Ref_currency_id
			cost_basis_currency_id -> cb.Ref_currency_id
 		foreign_keys
		 	position_client -> {c -> p.HoldPos_position_client}
			position_asset -> {a -> a}  #NEW
			position_current_value_currency -> {c -> cv}  #NEW
			position_cost_basis_currency -> {c -> cb}  #NEW
	}

	entity portfolio -> {
		from
			p:Portfolio_portfolio
		attributes
			parent_portfolio_id -> p.Portfolio_portfolio_parent_id #added
			name -> p.Portfolio_portfolio_name
			description -> p.Portfolio_portfolio_description
			id -> p.Portfolio_portfolio_id
			strategy_id -> p.Portfolio_portfolio_strategy_id
		foreign_keys
 			parent -> {p -> p.Portfolio_portfolio_parent}  #added
			portfolio_strategy -> {s -> p.Portfolio_portfolio_strategy}
	}

	entity holding -> {
		from
			h:HoldPos_holding c:Ref_currency a:Ref_asset
		where
			a.Ref_asset_id = h.HoldPos_holding_asset_id
			h.HoldPos_holding_currency_code = c.Ref_currency_code
		attributes
			id -> h.HoldPos_holding_id
			asset_id -> h.HoldPos_holding_asset_id
			currency_id -> c.Ref_currency_id
			purchase_price -> h.HoldPos_holding_purchase_price
			quantity -> h.HoldPos_holding_quantity
			purchase_date -> h.HoldPos_holding_purchase_date
			begin_date -> h.HoldPos_holding_begin_date
			end_date -> h.HoldPos_holding_end_date
		foreign_keys
 			holding_asset -> {a -> a}
			holding_currency -> {c -> c}
	}

entity	portfolioholding -> {
		from
			h:HoldPos_holding p:Portfolio_portfolio cur: Ref_currency c1:Client_client a:Ref_asset
		where
			a.Ref_asset_id = h.HoldPos_holding_asset_id
			p.Portfolio_portfolio_parent.Portfolio_portfolio_client_id = c1.Client_client_id
			c1.Client_client_id = h.HoldPos_holding_client.Client_client_id
			h.HoldPos_holding_currency_code = cur.Ref_currency_code
			p.Portfolio_portfolio_client_id = h.HoldPos_holding_client.Client_client_id
			h.HoldPos_holding_portfolio_id = p.Portfolio_portfolio_id
		attributes
			id -> keygen(h.HoldPos_holding_id, h.HoldPos_holding_client.Client_client_id, p.Portfolio_portfolio_id) #added by ryan
			holding_id -> h.HoldPos_holding_id
			client_id -> c1.Client_client_id
			portfolio_id -> p.Portfolio_portfolio_id
		foreign_keys
			portfolioholding_holding -> {h -> h c -> cur a -> a}
			portfolioholding_client -> {c -> h.HoldPos_holding_client}
			portfolioholding_portfolio -> {p -> p}
	}
}

instance Output = distinct eval ETL E
Keywords:

typeside_literal
sigma
quotient_query
schema_literal
quotient
query_literal
instance_literal
distinct
coproduct

Options:

quotient_use_chase
simplify_names



instance ClientInstance

client
IDidnamedescription
01TomTom Client
12DickDick Client
23HarryHarry Client


instance PortfolioInstance

portfolio
IDidnamedescriptionclient_idstrategy_idparent_idparentstrategy
01Port1Portfolio112104
12Port2Portfolio222214
23Port3Portfolio321213
strategy
IDidnamedescription
31Strat1Strategy1
42Strat2Strategy2
53Strat3Strategy3


instance RefInstance

asset
IDidstrategy_idnamedescriptionstrategy
011A1Asset119
123A2Asset220
232A3Asset321
344A4Asset422
455A5Asset523
564A6Asset622
674A7Asset722
785A8Asset823
893A9Asset920
9101A10Asset1019
country
IDidcodename
101AUAustralia
112CHSwitzerland
123CNChina
134EUEuropean Union
145JPJapan
156USUnited States of America
currency
IDidcountry_idcodenamecountry
1616USDUS Dollar15
1724EUREuro13
1835JPYJapanese Yen14
strategy
IDidnamedescription
191Strat1Strategy1
203Strat3Strategy3
212Strat2Strategy2
224Strat4Strategy4
235Strat5Strategy5


instance ClientFwd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
0TomTom1Tom Client1Tom Client
1DickDick2Dick Client2Dick Client
2HarryHarry3Harry Client3Harry Client
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
Ref_country
IDRef_country_nameRef_country_idRef_country_code
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency


instance HoldPosInstance

client
IDnonmdesc
01TomTom Client
12DickDick Client
holding
IDidclient_noportfolio_idasset_idquantitypurchase_datebegin_dateend_datepurchase_pricecurrency_codeclient
211112001/1/20111/1/20116/1/20115.51EUR0
321111001/1/20116/1/2011?05.1EUR0
432331509/1/20139/1/20133/1/20142.5JPY1
542352002/1/20132/1/20137/1/20131.6USD1
652351002/1/20137/1/2013?11.6USD1
position
IDidclient_noasset_idquantitycurrent_valuecost_basiscurrent_value_currency_codecost_basis_currency_codeclient
7111100550551EUREUR0
8225100170160JPYJPY1


instance PortfolioFwd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
011Portfolio112Port104
122Portfolio222Port214
232Portfolio321Port313
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
Ref_country
IDRef_country_nameRef_country_idRef_country_code
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
311Strategy1Strategy1Strat1Strat1
422Strategy2Strategy2Strat2Strat2
533Strategy3Strategy3Strat3Strat3
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency


instance TransInstance

asset
IDidnamedescription
01A1Asset1
13A3Asset3
25A5Asset5
37A7Asset7
49A9Asset9
currency
IDidcodename
51USDUS Dollar
62EUREuro
73JPYJapanese Yen
transaction
IDidasset_idportfolio_idquantitycurrency_iddatebuy_sell_indpriceassetcurrency
811120021/1/2011buy5.5106
921110026/1/2011sell5.506
1033215019/1/2013buy2.515
1143215013/1/2014sell3.515
1255320032/1/2013buy1.627
1365310037/1/2013sell1.527


instance HoldPosFwd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
0TomTom1Tom Client1Tom Client
1DickDick2Dick Client2Dick Client
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
215.512001/1/2011116/1/2011EUR1/1/201110
315.11006/1/201111?0EUR1/1/201120
432.51509/1/2013233/1/2014JPY9/1/201331
531.62002/1/2013257/1/2013USD2/1/201341
631.61007/1/201325?1USD2/1/201351
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
711005505511EUREUR10
821001701602JPYJPY51
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
Ref_country
IDRef_country_nameRef_country_idRef_country_code
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency


instance TransFwd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
01?0A1A1Asset1Asset1111
13?1A3A3Asset3Asset3312
25?2A5A5Asset5Asset5513
37?3A7A7Asset7Asset7714
49?4A9A9Asset9Asset9915
Ref_country
IDRef_country_nameRef_country_idRef_country_code
5?5?6?7
6?8?9?10
7?11?12?13
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
8US DollarUSD11US DollarUSD?65
9EuroEUR22EuroEUR?96
10Japanese YenJPY33Japanese YenJPY?127
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
11?0?0?14?14?15?15
12?1?1?16?16?17?17
13?2?2?18?18?19?19
14?3?3?20?20?21?21
15?4?4?22?22?23?23
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency
161/1/2011buy5.51111220009
176/1/2011sell5.5112210009
189/1/2013buy2.5323115018
193/1/2014sell3.5324115018
202/1/2013buy1.65353200210
217/1/2013sell1.55363100210


instance RefFwd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
011A1A1Asset1Asset1119
123A2A2Asset2Asset2220
232A3A3Asset3Asset3321
344A4A4Asset4Asset4422
455A5A5Asset5Asset5523
564A6A6Asset6Asset6622
674A7A7Asset7Asset7722
785A8A8Asset8Asset8823
893A9A9Asset9Asset9920
9101A10A10Asset10Asset101019
Ref_country
IDRef_country_nameRef_country_idRef_country_code
10Australia1AU
11Switzerland2CH
12China3CN
13European Union4EU
14Japan5JP
15United States of America6US
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
16US DollarUSD11US DollarUSD615
17EuroEUR22EuroEUR413
18Japanese YenJPY33Japanese YenJPY514
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
1911Strategy1Strategy1Strat1Strat1
2033Strategy3Strategy3Strat3Strat3
2122Strategy2Strategy2Strat2Strat2
2244Strategy4Strategy4Strat4Strat4
2355Strategy5Strategy5Strat5Strat5
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency


instance CoProd

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
0TomTom1Tom Client1Tom Client
1DickDick2Dick Client2Dick Client
2HarryHarry3Harry Client3Harry Client
3TomTom1Tom Client1Tom Client
4DickDick2Dick Client2Dick Client
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
515.512001/1/2011116/1/2011EUR1/1/201113
615.11006/1/201111?0EUR1/1/201123
732.51509/1/2013233/1/2014JPY9/1/201334
831.62002/1/2013257/1/2013USD2/1/201344
931.61007/1/201325?1USD2/1/201354
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
1011005505511EUREUR13
1121001701602JPYJPY54
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
1211Portfolio112Port11246
1322Portfolio222Port21346
1432Portfolio321Port31345
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
1511A1A1Asset1Asset1148
1623A2A2Asset2Asset2249
1732A3A3Asset3Asset3350
1844A4A4Asset4Asset4451
1955A5A5Asset5Asset5552
2064A6A6Asset6Asset6651
2174A7A7Asset7Asset7751
2285A8A8Asset8Asset8852
2393A9A9Asset9Asset9949
24101A10A10Asset10Asset101048
251?2A1A1Asset1Asset1153
263?3A3A3Asset3Asset3354
275?4A5A5Asset5Asset5555
287?5A7A7Asset7Asset7756
299?6A9A9Asset9Asset9957
Ref_country
IDRef_country_nameRef_country_idRef_country_code
30Australia1AU
31Switzerland2CH
32China3CN
33European Union4EU
34Japan5JP
35United States of America6US
36?7?8?9
37?10?11?12
38?13?14?15
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
39US DollarUSD11US DollarUSD635
40EuroEUR22EuroEUR433
41Japanese YenJPY33Japanese YenJPY534
42US DollarUSD11US DollarUSD?836
43EuroEUR22EuroEUR?1137
44Japanese YenJPY33Japanese YenJPY?1438
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
4511Strategy1Strategy1Strat1Strat1
4622Strategy2Strategy2Strat2Strat2
4733Strategy3Strategy3Strat3Strat3
4811Strategy1Strategy1Strat1Strat1
4933Strategy3Strategy3Strat3Strat3
5022Strategy2Strategy2Strat2Strat2
5144Strategy4Strategy4Strat4Strat4
5255Strategy5Strategy5Strat5Strat5
53?2?2?16?16?17?17
54?3?3?18?18?19?19
55?4?4?20?20?21?21
56?5?5?22?22?23?23
57?6?6?24?24?25?25
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency
581/1/2011buy5.5111122002543
596/1/2011sell5.511221002543
609/1/2013buy2.532311502642
613/1/2014sell3.532411502642
622/1/2013buy1.653532002744
637/1/2013sell1.553631002744


instance E

Client_client
IDClient_client_nameHoldPos_client_nmHoldPos_client_noHoldPos_client_descClient_client_idClient_client_description
0TomTom1Tom Client1Tom Client
1DickDick2Dick Client2Dick Client
2HarryHarry3Harry Client3Harry Client
HoldPos_holding
IDHoldPos_holding_portfolio_idHoldPos_holding_purchase_priceHoldPos_holding_quantityHoldPos_holding_begin_dateHoldPos_holding_client_noHoldPos_holding_asset_idHoldPos_holding_end_dateHoldPos_holding_currency_codeHoldPos_holding_purchase_dateHoldPos_holding_idHoldPos_holding_client
315.512001/1/2011116/1/2011EUR1/1/201110
415.11006/1/201111?0EUR1/1/201120
532.51509/1/2013233/1/2014JPY9/1/201331
631.62002/1/2013257/1/2013USD2/1/201341
731.61007/1/201325?1USD2/1/201351
HoldPos_position
IDHoldPos_position_idHoldPos_position_quantityHoldPos_position_current_valueHoldPos_position_cost_basisHoldPos_position_client_noHoldPos_position_current_value_currency_codeHoldPos_position_cost_basis_currency_codeHoldPos_position_asset_idHoldPos_position_client
811005505511EUREUR10
921001701602JPYJPY51
Portfolio_portfolio
IDPortfolio_portfolio_idPortfolio_portfolio_client_idPortfolio_portfolio_descriptionPortfolio_portfolio_parent_idPortfolio_portfolio_strategy_idPortfolio_portfolio_namePortfolio_portfolio_parentPortfolio_portfolio_strategy
1011Portfolio112Port11033
1122Portfolio222Port21133
1232Portfolio321Port31132
Ref_asset
IDTrans_asset_idRef_asset_strategy_idRef_asset_nameTrans_asset_nameRef_asset_descriptionTrans_asset_descriptionRef_asset_idRef_asset_strategy
1311A1A1Asset1Asset1132
1432A3A3Asset3Asset3333
1555A5A5Asset5Asset5535
1674A7A7Asset7Asset7736
1793A9A9Asset9Asset9934
1823A2A2Asset2Asset2234
1944A4A4Asset4Asset4436
2064A6A6Asset6Asset6636
2185A8A8Asset8Asset8835
22101A10A10Asset10Asset101032
Ref_country
IDRef_country_nameRef_country_idRef_country_code
23United States of America6US
24European Union4EU
25Japan5JP
26Australia1AU
27Switzerland2CH
28China3CN
Ref_currency
IDTrans_currency_nameRef_currency_codeTrans_currency_idRef_currency_idRef_currency_nameTrans_currency_codeRef_currency_country_idRef_currency_country
29US DollarUSD11US DollarUSD623
30EuroEUR22EuroEUR424
31Japanese YenJPY33Japanese YenJPY525
Ref_strategy
IDPortfolio_strategy_idRef_strategy_idRef_strategy_descriptionPortfolio_strategy_descriptionRef_strategy_namePortfolio_strategy_name
3211Strategy1Strategy1Strat1Strat1
3322Strategy2Strategy2Strat2Strat2
3433Strategy3Strategy3Strat3Strat3
3555Strategy5Strategy5Strat5Strat5
3644Strategy4Strategy4Strat4Strat4
Trans_transaction
IDTrans_transaction_dateTrans_transaction_buy_sell_indTrans_transaction_priceTrans_transaction_asset_idTrans_transaction_portfolio_idTrans_transaction_idTrans_transaction_currency_idTrans_transaction_quantityTrans_transaction_assetTrans_transaction_currency
371/1/2011buy5.5111122001330
386/1/2011sell5.511221001330
399/1/2013buy2.532311501429
403/1/2014sell3.532411501429
412/1/2013buy1.653532001531
427/1/2013sell1.553631001531


instance Output

asset
IDiddescriptionnamestrategy_idasset_strategy
01Asset1A1137
13Asset3A3238
25Asset5A5540
37Asset7A7441
49Asset9A9339
52Asset2A2339
64Asset4A4441
76Asset6A6441
88Asset8A8540
910Asset10A10137
client
IDdescriptionnameid
10Tom ClientTom1
11Dick ClientDick2
12Harry ClientHarry3
country
IDcodenameid
13USUnited States of America6
14EUEuropean Union4
15JPJapan5
16AUAustralia1
17CHSwitzerland2
18CNChina3
currency
IDcodenameidcountry_idcurrency_country
19USDUS Dollar1613
20EUREuro2414
21JPYJapanese Yen3515
holding
IDidasset_idcurrency_idpurchase_pricequantitypurchase_datebegin_dateend_dateholding_assetholding_currency
221125.512001/1/20111/1/20116/1/2011020
232125.11001/1/20116/1/2011?0020
243332.51509/1/20139/1/20133/1/2014121
254511.62002/1/20132/1/20137/1/2013219
265511.61002/1/20137/1/2013?1219
portfolio
IDnamedescriptionidstrategy_idparent_portfolio_idparentportfolio_strategy
27Port1Portfolio11212738
28Port2Portfolio22222838
29Port3Portfolio33122837
portfolioholding
IDidholding_idclient_idportfolio_idportfolioholding_clientportfolioholding_holdingportfolioholding_portfolio
305111102227
310211102327
322323112429
337423112529
344523112629
position
IDquantitycurrent_valuecost_basisidclient_idasset_idcurrent_value_currency_idcost_basis_currency_idposition_assetposition_clientposition_cost_basis_currencyposition_current_value_currency
35100550551111220102020
36100170160225332112121
strategy
IDnamedescriptionid
37Strat1Strategy11
38Strat2Strategy22
39Strat3Strategy33
40Strat5Strategy55
41Strat4Strategy44
transaction
IDidasset_idbuy_sell_indquantitypricecurrency_idportfolio_iddatetransaction_assettransaction_currencytransaction_portfolio
4211buy2005.51211/1/201102027
4321sell1005.5216/1/201102027
4433buy1502.5129/1/201311928
4543sell1503.5123/1/201411928
4655buy2001.6332/1/201322129
4765sell1001.5337/1/201322129