def setUp(self): location = "http://somewhere/over/the/rainbow" p = xmla.XMLAProvider() c = p.connect(location=location) with requests_mock.mock() as m: m.post(location, text=mdx1.xml_response) self.fr = c.Execute(mdx1.__doc__) self.res = utils.PropDict(mdx1.result) self.cm = self.fr.cellmap self.ordinal1 = list( filter(lambda cell: cell._CellOrdinal == "1", self.res.CellData.Cell))[0] self.ax_tupel0 = [ tup.Member for tup in self.res.Axes.Axis[0].Tuples.Tuple ] with requests_mock.mock() as m: m.post(location, text=mdx_noaxistuple.xml_response) self.fr_noaxistuple = c.Execute(mdx_noaxistuple.__doc__) with requests_mock.mock() as m: m.post(location, text=mdx_columns_but_no_rows.xml_response) self.fr_cbnr = c.Execute(mdx_columns_but_no_rows.__doc__) with requests_mock.mock() as m: m.post(location, text=mdx_rows_but_no_columns_no_cells.xml_response) self.fr_rbncnc = c.Execute( mdx_rows_but_no_columns_no_cells.__doc__)
def main(mdxfile, location, catalog, krb, username, password, spn, sslverify): p = os.path.dirname(os.path.realpath(__file__)) pyfile = os.path.join(p, os.path.splitext(mdxfile)[0] + os.path.extsep + "py") cmd = open(os.path.join(p, mdxfile)).read() p = xmla.XMLAProvider() auth = None if krb: kw = {} if spn: service, host = spn.split("@", 1) kw["service"] = service kw["hostname_override"] = host if username: kw["principal"] = username auth = HTTPKerberosAuth(**kw) elif username: auth = HTTPBasicAuth(username, password) kwargs = {} log = LogRequest() kwargs["log"] = log c = p.connect(location=location, sslverify=sslverify, auth=auth, **kwargs) res = c.Execute(cmd, Catalog=catalog) x = utils.dictify(res.root, keep_none_text=True) erg = pprint.pformat(x) encodinghint = "# -*- coding: utf-8" open(pyfile, "w+").write('%s\n"""\n%s\n"""\n\nresult=%s\n\nxml_response="""%s"""' % (encodinghint, cmd, erg, log.res))
def conn(): engine = sqlalchemy.create_engine("sqlite://") create_insert(engine) executor = MdxEngine(sqla_engine=engine, source_type="db") executor.load_cube(cube_name="main", fact_table_name="facts") discover_request_hanlder = XmlaDiscoverReqHandler(executor) execute_request_hanlder = XmlaExecuteReqHandler(executor) print("spawning server") application = Application( [XmlaProviderService], "urn:schemas-microsoft-com:xml-analysis", in_protocol=Soap11(validator="soft"), out_protocol=Soap11(validator="soft"), config={ "discover_request_hanlder": discover_request_hanlder, "execute_request_hanlder": execute_request_hanlder, }, ) wsgi_application = WsgiApplication(application) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() provider = xmla.XMLAProvider() yield provider.connect(location=server.url) print("stopping server") drop_tables(engine) server.stop()
def main(): file = open( "DATABASES_bench_result" + str(datetime.datetime.now().strftime("%Y-%m-%d-%H-%M")), "w", ) file.write("Benchmarks are made with cpu :\n") file.write(cpuinfo.get_cpu_info()["brand"] + "\n\n") for idx, query in enumerate([query1, query6, query7, query9]): file.write( "Query {0} :\n".format(str(idx + 1)) + query + "\n----------------------------------------------------------\n\n") sqlalchemy_uri = os.environ.get("SQLALCHEMY_DATABASE_URI", "sqlite://") db = urlparse(sqlalchemy_uri).path.replace("/", "") engine = sqlalchemy.create_engine(sqlalchemy_uri) mdx_engine = MdxEngine(sqla_engine=engine, source_type="db") mdx_engine.load_cube(cube_name=db if db else "main", fact_table_name="facts") wsgi_application = get_wsgi_application(mdx_engine) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() for config_file in os.listdir(os.path.join(os.getcwd(), "db_config_files")): dbms = str(config_file.split("_")[0]) try: copy_2_olapy_dir(config_file) # to refresh cubes from database provider = xmla.XMLAProvider() conn = provider.connect(location=server.url) mbench = MicBench() t = PrettyTable( ["Query", "{0} - olapy execution time".format(dbms)]) for idx, query in enumerate([query1, query6, query7, query9]): if dbms.upper() in ["POSTGRES", "ORACLE"]: query = fix_query_lowercase_db(query) t.add_row([ "Query" + str(idx + 1), str(mbench.bench(conn, query, CUBE_NAME)) ]) file.write(str(t) + "\n\n") except XMLAException: type, value, traceback = sys.exc_info() print("Error opening %s" % (value)) print("Can't connect to {0} database".format(dbms)) server.stop()
def olapy_xmla_benchmark(queries): provider = xmla.XMLAProvider() connection = provider.connect(location="http://{0}:{1}".format(HOST, PORT)) return [ Timer(lambda: connection.Execute(query, Catalog=BENCH_CUBE)).timeit(number=1) for query in queries ]
def Get_Measure_Value_From_Cube(olapServerURL, userName, pd, siloID, query): provider = xmla.XMLAProvider() con = provider.connect(location=olapServerURL, username=userName, password=pd) source = con.getOLAPSource() res = source.Execute(query, Catalog=siloID) return res.getSlice(properties="FmtValue")
def setUp(self): self.p = xmla.XMLAProvider() self.c = self.p.connect(location=self.be["location"], username=self.be["username"], password=self.be["password"], spn=self.be["spn"]) self.c.BeginSession() self.getSchemaRowsetSupport()
def setUp(self): self.cube = self.be["cube"] self.set1 = self.be["set1"] self.set2 = self.be["set2"] self.set3 = self.be["set3"] self.catalog = self.be["catalog"] self.p = xmla.XMLAProvider() self.c = self.p.connect(location=self.be["location"], username=self.be["username"], password=self.be["password"], spn=self.be["spn"])
def __init__(self, cude_id): """ Args: cube_id(int): id куба """ self.cube_id = cude_id self.connect = xmla.XMLAProvider().connect(location=XMLA_URL) self.webdav = easywebdav.connect(host=settings.OLAP_SERVER_HOST, port=settings.OLAP_SERVER_PORT, path=REPOSITORY_PATH, username=settings.OLAP_SERVER_USER, password=settings.OLAP_SERVER_PASS)
def olapy_xmla_benchmark(queries): provider = xmla.XMLAProvider() connection = provider.connect(location=f"http://{HOST}:{PORT}") execution_time_results = [] for query in queries: execution_time_results.append( Timer(lambda: connection.Execute(query, Catalog=BENCH_CUBE)).timeit( number=1 ) ) return execution_time_results
def setUp(self): testname = self.id().split(".")[-1] session = mockhelper.mockedsession(self.be["conversation"], testname) self.p = xmla.XMLAProvider() if self.logreq: self.logreq.prefix = testname kw = {"log": self.logreq, "session": session} self.c = self.p.connect(location=self.be["location"], auth=self.be["auth"], **kw) #self.c.BeginSession() self.getSchemaRowsetSupport()
def main(mdxfile, location, catalog, username, password, spn, sslverify): pyfile = os.path.splitext(mdxfile)[0] + os.path.extsep + "py" cmd = file(mdxfile).read() p = xmla.XMLAProvider() c=p.connect(location=location, username=username, password=password, spn=spn, sslverify=sslverify) res=c.Execute(cmd, Catalog=catalog) x=utils.dictify(res.root) erg=pprint.pformat(x) file(pyfile, "wb+").write('"""\n%s\n"""\n\nresult=%s\n' % (cmd, erg))
def conn(): print("spawning server") application = Application([XmlaProviderService], 'urn:schemas-microsoft-com:xml-analysis', in_protocol=Soap11(validator='soft'), out_protocol=Soap11(validator='soft')) wsgi_application = WsgiApplication(application) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() provider = xmla.XMLAProvider() conn = provider.connect(location=server.url) yield conn print("stopping server") server.stop()
def setUp(self): testname = self.id().split(".")[-1] session = mockhelper.mockedsession(self.be["conversation"], testname) self.cube = self.be["cube"] self.set1 = self.be["set1"] self.set2 = self.be["set2"] self.set3 = self.be["set3"] self.catalog = self.be["catalog"] self.p = xmla.XMLAProvider() if self.logreq: self.logreq.prefix=testname kw = { "log":self.logreq, "session":session } self.c = self.p.connect(location=self.be["location"], auth=self.be["auth"], **kw)
def data_cube(statement, catalog): provider = xmla.XMLAProvider() connect = provider.connect( location='http://10.88.10.163/OLAP/msmdpump.dll', username='******', password='******') # source = connect.getOLAPSource() resource = connect.Execute(statement, Catalog=catalog) getSlice = resource.getSlice(properties='Value') getAxisTuple = resource.getAxisTuple(1) if len(getSlice) == len(getAxisTuple): data = [] for val1, val2 in iter(zip(getAxisTuple, getSlice)): data.append([val1['Caption'], val2[0]]) return data else: return
def main(): file = open( "bench_result" + str(datetime.datetime.now().strftime("%Y-%m-%d-%H-%M")), "w") gen = CubeGen(number_dimensions=3, rows_length=1000, columns_length=5) gen.generate_csv(gen.generate_cube(3, 1000)) mbench = MicBench() file.write("Benchmarks are made with cpu :\n") file.write(cpuinfo.get_cpu_info()["brand"] + "\n\n") xmla_tools = _get_xmla_tools() application = get_spyne_app(xmla_tools) wsgi_application = WsgiApplication(application) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() provider = xmla.XMLAProvider() conn = provider.connect(location=server.url) olapy_query_execution_bench(file, mbench, conn, xmla_tools) olapy_vs_mondrian(file, mbench, conn) olapy_vs_iccube(file, mbench, conn) olapy_profile(file) gen.remove_temp_cube() file.close() server.stop()
import requests import numpy as np import pandas as pd import time import urllib3 #urllib3.disable_warnings() print('checkpoint 1') username = '' #please add a username here or get it from a configuration file password = '' #please add a password here or get it from a configuration file endpoint = "https://iccube.regentmarkets.com/xmla" print('checkpoint 2') p = xmla.XMLAProvider() print('p:',p) print('checkpoint 3') # mondrian c = p.connect(location=endpoint, username=username, password=password, sslverify=False) print('checkpoint 4') print('now sleeping...') time.sleep(3) print('done sleeping!') print('c_dict:',c.__dict__)
def main(): file = open( "DATABASES_bench_result" + str(datetime.datetime.now().strftime("%Y-%m-%d-%H-%M")), "w", ) file.write("Benchmarks are made with cpu :\n") file.write(cpuinfo.get_cpu_info()["brand"] + "\n\n") for idx, query in enumerate([query1, query6, query7, query9]): file.write( "Query {0} :\n".format(str(idx + 1)) + query + "\n----------------------------------------------------------\n\n") olapy_data = os.path.join(expanduser("~"), "olapy-data") db_config = DbConfigParser() # todo conn string db_conf = db_config.get_db_credentials( os.path.join(olapy_data, "olapy-config.yml")) xmla_tools = XmlaDiscoverReqHandler(olapy_data=olapy_data, source_type="db", db_config=db_conf, cubes_config=None) application = get_spyne_app(xmla_tools) wsgi_application = WsgiApplication(application) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() for config_file in os.listdir(os.path.join(os.getcwd(), "db_config_files")): dbms = str(config_file.split("_")[0]) try: copy_2_olapy_dir(config_file) # to refresh cubes from database provider = xmla.XMLAProvider() conn = provider.connect(location=server.url) mbench = MicBench() t = PrettyTable( ["Query", "{0} - olapy execution time".format(dbms)]) for idx, query in enumerate([query1, query6, query7, query9]): if dbms.upper() in ["POSTGRES", "ORACLE"]: query = fix_query_lowercase_db(query) t.add_row([ "Query" + str(idx + 1), str(mbench.bench(conn, query, CUBE_NAME)) ]) file.write(str(t) + "\n\n") except: type, value, traceback = sys.exc_info() print("Error opening %s" % (value)) print("Can't connect to {0} database".format(dbms)) pass server.stop()
def olapy_vs_iccube(file, mbench, conn): try: file.write("******************************************\n") file.write('* iCcube v4.8.2 with "sales Excel" Cube *\n') file.write("******************************************\n\n") t = PrettyTable(["Query", "olapy", "icCube"]) p2 = xmla.XMLAProvider() c2 = p2.connect( location="http://localhost:8282/icCube/xmla", username="******", password="******", ) cmd = """SELECT FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 1 :\n" + cmd + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 1", mbench.bench(conn, cmd, "sales"), mbench.bench(c2, cmd, "Sales (Excel)"), ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({ [Geography].[Geo].[All Regions]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({ [Geography].[Geo].[All Continent]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 2 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 2", mbench.bench(conn, cmd2, "sales"), mbench.bench(c2, cmd, "Sales (Excel)"), ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 3 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 3", mbench.bench(conn, cmd2, "sales"), mbench.bench(c2, cmd, "Sales (Excel)"), ]) cmd = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember ({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))), Hierarchize(AddCalculatedMembers({ [Product].[Prod].[Company].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))), Hierarchize(AddCalculatedMembers({ [Product].[Product].[Company].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 4 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 4", mbench.bench(conn, cmd2, "sales"), mbench.bench(c2, cmd, "Sales (Excel)"), ]) cmd = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers (DrilldownMember({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{ [Product].[Prod].[Company].Members}}, { [Product].[Prod].[Company].&[Crazy Development ]})}}, { [Product].[Prod].[Company].&[Crazy Development ].&[icCube]})))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownMember({{ [Time].[Calendar].[Year].Members}}, { [Time].[Calendar].[Year].&[2010]})}}, { [Time].[Calendar].[Year].&[2010].&[Q2 2010]})}}, { [Time].[Calendar].[Year].&[2010].&[Q2 2010].&[May 2010]})))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{ [Product].[Product].[Company].Members}}, { [Product].[Product].[Company].[Crazy Development]})}}, { [Product].[Product].[Company].[Crazy Development].[olapy]})))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownMember({{ [Time].[Time].[Year].Members}}, { [Time].[Time].[Year].[2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010].[May 2010]})))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 5 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 5", mbench.bench(conn, cmd2, "sales"), mbench.bench(c2, cmd, "Sales (Excel)"), ]) file.write(str(t) + "\n\n") except: print("Make sure icCube is running and containing sales Excel cube") pass
def main(): file = open( 'bench_result' + str(datetime.datetime.now().strftime("%Y-%m-%d-%H-%M")), 'w') gen = CubeGen(number_dimensions=3, rows_length=1000, columns_length=5) gen.generate_csv(gen.generate_cube(3, 1000)) XmlaProviderService.discover_tools.change_catalogue(CUBE_NAME) mbench = MicBench() file.write("Benchmarks are made with cpu :\n") file.write(cpuinfo.get_cpu_info()['brand'] + "\n\n") application = Application([XmlaProviderService], 'urn:schemas-microsoft-com:xml-analysis', in_protocol=Soap11(validator='soft'), out_protocol=Soap11(validator='soft')) wsgi_application = WsgiApplication(application) server = WSGIServer(application=wsgi_application, host=HOST, port=PORT) server.start() provider = xmla.XMLAProvider() conn = provider.connect(location=server.url) t = PrettyTable(['Query', 'olapy execution time']) cmd = """ SELECT FROM [""" + CUBE_NAME + """] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 1 :\n" + cmd + "\n----------------------------------------------------------\n\n") t.add_row(['Query 1', mbench.bench(conn, cmd, CUBE_NAME)]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{{ [table0].[table0].[All table0A].Members}}}, { [table0].[table0].[table0A].[""" + str( XmlaProviderService.discover_tools.star_schema_dataframe.table0A[1] ) + """]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [""" + CUBE_NAME + """] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS """ file.write( "Query 2 :\n" + cmd + "\n----------------------------------------------------------\n\n") t.add_row(['Query 2', mbench.bench(conn, cmd, CUBE_NAME)]) tup = "[table0].[table0].[table0A].[" + str( XmlaProviderService.discover_tools.star_schema_dataframe.table0A[0] ) + "]" for d in range(REFINEMENT_LVL): tup += ",\n[table0].[table0].[table0A].[" + str( XmlaProviderService.discover_tools.star_schema_dataframe.table0A[ d + 1]) + "]" cmd = """ SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{{ [table0].[table0].[All table0A].Members}}}, { """ + tup + """ }))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [""" + CUBE_NAME + """] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS """ file.write( "Query 3 :\n" + cmd + "\n----------------------------------------------------------\n\n") t.add_row(['Query 3', mbench.bench(conn, cmd, CUBE_NAME)]) file.write(str(t) + "\n\n") try: file.write( '******************************************************************************\n' ) file.write( '* mondrian with "warehouse" Cube (note the same as olapy but resemble to it) *\n' ) file.write( '* (olapy warehouse"s cube has more rows) *\n' ) file.write( '******************************************************************************\n\n' ) t = PrettyTable(['Query', 'mondrian', 'olapy']) p2 = xmla.XMLAProvider() c2 = p2.connect(location="http://localhost:8080/xmondrian/xmla") cmd = """SELECT FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY {[Measures].[Supply Time]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 1 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 1', mbench.bench(c2, cmd2, 'FoodMart'), mbench.bench(conn, cmd, 'foodmart') ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{{ [Product].[Product].[All brand_name].Members}}}, { [Product].[Product].[brand_name].[Pearl]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(Hierarchize({ [Product].[Brand Name].Members, [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].Children}), { [Measures].[Supply Time]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 2 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 2', mbench.bench(c2, cmd2, 'FoodMart'), mbench.bench(conn, cmd, 'foodmart') ]) cmd = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({ [Product].[Product].[All brand_name].Members})), Hierarchize(AddCalculatedMembers({ [Store].[Store].[All store_type].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize({ [Product].[Brand Name].Members}),Hierarchize({ [Store Type].[All Store Types], [Store Type].[All Store Types].Children})), {[Measures].[Supply Time]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 3 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 3', mbench.bench(c2, cmd2, 'FoodMart'), mbench.bench(conn, cmd, 'foodmart') ]) file.write(str(t) + "\n\n") except: print('Make sure mondrian is running and containing Warehouse cube') pass try: file.write('******************************************\n') file.write('* iCcube v4.8.2 with "sales Excel" Cube *\n') file.write('******************************************\n\n') t = PrettyTable(['Query', 'olapy', 'icCube']) p2 = xmla.XMLAProvider() c2 = p2.connect(location="http://localhost:8282/icCube/xmla", username="******", password="******") cmd = """SELECT FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 1 :\n" + cmd + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 1', mbench.bench(conn, cmd, 'sales'), mbench.bench(c2, cmd, 'Sales (Excel)') ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({ [Geography].[Geo].[All Regions]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({ [Geography].[Geo].[All Continent]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 2 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 2', mbench.bench(conn, cmd2, 'sales'), mbench.bench(c2, cmd, 'Sales (Excel)') ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 3 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 3', mbench.bench(conn, cmd2, 'sales'), mbench.bench(c2, cmd, 'Sales (Excel)') ]) cmd = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember ({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))), Hierarchize(AddCalculatedMembers({ [Product].[Prod].[Company].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))), Hierarchize(AddCalculatedMembers({ [Product].[Product].[Company].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 4 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 4', mbench.bench(conn, cmd2, 'sales'), mbench.bench(c2, cmd, 'Sales (Excel)') ]) cmd = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers (DrilldownMember({{DrilldownMember({{DrilldownLevel({ [Geography].[Geo].[All Regions]})}}, { [Geography].[Geo].[All Regions].&[America], [Geography].[Geo].[All Regions].&[Europe]})}}, { [Geography].[Geo].[All Regions].&[America].&[US], [Geography].[Geo].[All Regions].&[Europe].&[FR], [Geography].[Geo].[All Regions].&[Europe].&[ES]}))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{ [Product].[Prod].[Company].Members}}, { [Product].[Prod].[Company].&[Crazy Development ]})}}, { [Product].[Prod].[Company].&[Crazy Development ].&[icCube]})))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownMember({{ [Time].[Calendar].[Year].Members}}, { [Time].[Calendar].[Year].&[2010]})}}, { [Time].[Calendar].[Year].&[2010].&[Q2 2010]})}}, { [Time].[Calendar].[Year].&[2010].&[Q2 2010].&[May 2010]})))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{ [Product].[Product].[Company].Members}}, { [Product].[Product].[Company].[Crazy Development]})}}, { [Product].[Product].[Company].[Crazy Development].[olapy]})))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownMember({{ [Time].[Time].[Year].Members}}, { [Time].[Time].[Year].[2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010].[May 2010]})))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" file.write( "Query 5 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ 'Query 5', mbench.bench(conn, cmd2, 'sales'), mbench.bench(c2, cmd, 'Sales (Excel)') ]) file.write(str(t) + "\n\n") except: print('Make sure icCube is running and containing sales Excel cube') pass file.write( '---------------- Profiling olapy Query 5 ------------------ \n\n') cProfile.run( """cmd = ''' SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{{ [Geography].[Geography].[All Continent].Members}}}, { [Geography].[Geography].[Continent].[America], [Geography].[Geography].[Continent].[Europe]})}}, { [Geography].[Geography].[Continent].[America].[United States], [Geography].[Geography].[Continent].[Europe].[France], [Geography].[Geography].[Continent].[Europe].[Spain]}))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{ [Product].[Product].[Company].Members}}, { [Product].[Product].[Company].[Crazy Development]})}}, { [Product].[Product].[Company].[Crazy Development].[olapy]})))), Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownMember({{DrilldownMember({{ [Time].[Time].[Year].Members}}, { [Time].[Time].[Year].[2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010]})}}, { [Time].[Time].[Year].[2010].[Q2 2010].[May 2010]})))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [sales] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS''' request = ExecuteRequest() request.Command = Command(Statement = cmd) request.Properties = Propertielist(PropertyList = Property(Catalog='sales')) XmlaProviderService().Execute(XmlaProviderService(),request)""", "{}.profile".format(__file__)) s = pstats.Stats("{}.profile".format(__file__), stream=file) s.strip_dirs() s.sort_stats("time").print_stats(PROFILING_LINES) try: os.system( 'gprof2dot -f pstats main.py.profile | dot -Tpng -o profile.png') except: print('make sure gprof2dot and graphviz are installed') os.remove('main.py.profile ') gen.remove_temp_cube() file.close() server.stop()
def olapy_vs_mondrian(file, mbench, conn): try: file.write( "******************************************************************************\n" ) file.write( '* mondrian with "warehouse" Cube (note the same as olapy but resemble to it) *\n' ) file.write( '* (olapy warehouse"s cube has more rows) *\n' ) file.write( "******************************************************************************\n\n" ) t = PrettyTable(["Query", "mondrian", "olapy"]) p2 = xmla.XMLAProvider() c2 = p2.connect(location="http://localhost:8080/xmondrian/xmla") cmd = """SELECT FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY {[Measures].[Supply Time]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 1 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 1", mbench.bench(c2, cmd2, "FoodMart"), mbench.bench(conn, cmd, "foodmart"), ]) cmd = """SELECT NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{{ [Product].[Product].[All brand_name].Members}}}, { [Product].[Product].[brand_name].[Pearl]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(Hierarchize({ [Product].[Brand Name].Members, [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].Children}), { [Measures].[Supply Time]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 2 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 2", mbench.bench(c2, cmd2, "FoodMart"), mbench.bench(conn, cmd, "foodmart"), ]) cmd = """SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({ [Product].[Product].[All brand_name].Members})), Hierarchize(AddCalculatedMembers({ [Store].[Store].[All store_type].Members}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [foodmart] WHERE ([Measures].[supply_time]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS""" cmd2 = """SELECT NON EMPTY CrossJoin(CrossJoin(Hierarchize({ [Product].[Brand Name].Members}),Hierarchize({ [Store Type].[All Store Types], [Store Type].[All Store Types].Children})), {[Measures].[Supply Time]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON 0 FROM [Warehouse]""" file.write( "Query 3 :\n" + cmd2 + "\n----------------------------------------------------------\n\n") t.add_row([ "Query 3", mbench.bench(c2, cmd2, "FoodMart"), mbench.bench(conn, cmd, "foodmart"), ]) file.write(str(t) + "\n\n") except: print("Make sure mondrian is running and containing Warehouse cube") pass