예제 #1
0
파일: etl.py 프로젝트: ddimmich/Cubulus
def runMain():	
	print 'use ' + DATABASE
	
	#if constants.DBLIB == 'mysql':
	#	#this allows portable || instead of concat()
	#	print "set @@sql_mode='ansi';"
		
	#init the start schema
	if CREATESCRIPT != '':
		f = open(CREATESCRIPT, 'r')
		s=f.read()
		f.close()
		print s

	print gen.genMetadata()
	
	#continues at the very end
	print gen.genSecurityTables()
	print 'insert into users_01 values(1, "anonymous");' #hardcoded ID's !!
	print 'insert into users_01 values(45, "a");'
	print 'insert into users_01 values(99, "b");'
	
	#source table called "time" will become dim0
	#source table has PK t_id, level-1 = t_year, level-2 = t_month
	#1=hierarchy root is Selectable
	#1=default node is "all time"
	#
	#normally root would have ID=0, but for some reason 
	#autoincrement generates IDs starting with 1
	print transform2Dim('time', 't_id', 't_year','t_month',1,1,0)
	print transform2Dim('prod', 'p_id', 'p_cat','p_prod',1,1,0)
	print transform2Dim('customer', 'c_id', 'c_type','c_cust',1,1,0)
	print transform2Dim('packaging', 'pck_id', 'pck_type','pck_pack',1,1,0)
	#source table called "measure" will become dim4
	#PK = s_id, level-1 = s_scenario
	#0=hierarchy root not selectable
	#2=default node is not the root
	print transform1Dim('measure', 'm_id', 'm_measure',0,2,0)
	print transform1Dim('scenario', 's_id', 's_scenario',0,2,0)
	
	#remember which columns you set before as partitions
	#and what cardinality you need
	partStmt = gen.genPartitions('fact_cub', \
		'dim_0', (2007-2005)*(13-1)+(2007-2005)+1, 'dim_3, dim_4')
		
	print transformFact('fact', partStmt)
	
	
	#fact table is looked up by mandatory id 'fact'
	#fact table is called 'fact_cub'
	#0,0,0 = ignored for fact (not a dimension table)
	print gen.addMetadata(-1, 'fact', 'fact_cub', 0, 0, 0, 0)

	#simple authorizations
	print "insert into auth_01 select  users_01.id, dim, element " +\
		"from auth_01 a, users_01 where users_01.name in('a','b');"
예제 #2
0
파일: etl.py 프로젝트: ddimmich/Cubulus
def transform1Dim(sourceTable, sourcePK, sourceCol1,
		selRoot, defNode, isTable):
	destTableNr = len(factTableFields)
	factTableFields.append(sourcePK)	
	destTable = 'dim' + str(destTableNr)
	l = [('source_table', sourceTable), 
		('dest_table', destTable),
		('source_pk', sourcePK),
		('table_nr',destTableNr),
		('source_col_1', sourceCol1),
		('autoinc', AUTOINC)]
	res = sqlCommon % dict(l) 
	res += sql1Dim % dict(l)
	gen0 = dict(l + [('gen',0)])
	res += sql2Dim1 % gen0
	res += gen.addMetadata(destTableNr, sourceTable, destTable, selRoot, 0, 
		defNode, isTable)
	return res