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
