def test_model(self):
"""
Making sure the model can be loaded and is valid
"""
filename = 'datawarehouse/fixtures/datawarehouse.json'
model = cubes.load_model(filename)
self.assertIsInstance(model,cubes.model.Model)
def initialize_model():
global model
global workspace
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql", model, url=DB_URL,
fact_prefix="ft_",
dimension_prefix="dm_")
def setUp(self):
self.view_name = "test_view"
engine = create_engine('sqlite://')
self.connection = engine.connect()
self.metadata = MetaData()
self.metadata.bind = engine
years = [2010, 2011]
path = os.path.join(DATA_PATH, 'aggregation.json')
a_file = open(path)
data_dict = json.load(a_file)
self.dimension_data = data_dict["data"]
self.dimensions = data_dict["dimensions"]
self.dimension_keys = {}
a_file.close()
self.dimension_names = [name for name in self.dimensions.keys()]
self.create_dimension_tables()
self.create_fact()
# Load the model
model_path = os.path.join(DATA_PATH, 'fixtures_model.json')
self.model = cubes.load_model(model_path)
self.cube = self.model.cube("test")
def setUp(self):
self.view_name = "test_view"
engine = create_engine('sqlite://')
self.connection = engine.connect()
self.metadata = MetaData()
self.metadata.bind = engine
years = [2010,2011]
path = os.path.join(DATA_PATH, 'aggregation.json')
a_file = open(path)
data_dict = json.load(a_file)
self.dimension_data = data_dict["data"]
self.dimensions = data_dict["dimensions"]
self.dimension_keys = {}
a_file.close()
self.dimension_names = [name for name in self.dimensions.keys()]
self.create_dimension_tables()
self.create_fact()
# Load the model
model_path = os.path.join(DATA_PATH, 'fixtures_model.json')
self.model = cubes.load_model(model_path)
self.cube = self.model.cube("test")
def initialize_model():
global workspace
global model
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql.star", model, url=DB_URL,
fact_prefix="ft_")
def __init__(self, config = None):
"""Create a WSGI server for providing OLAP web service. You might provide ``config``
as ``ConfigParser`` object.
"""
self.config = config
#
# Configure logger
#
self.logger = logging.getLogger(cubes.common.logger_name)
if self.config.has_option("server", "log"):
formatter = logging.Formatter(fmt='%(asctime)s %(levelname)s %(message)s')
handler = logging.FileHandler(self.config.get("server", "log"))
handler.setFormatter(formatter)
self.logger.addHandler(handler)
if self.config.has_option("server", "log_level"):
level_str = self.config.get("server", "log_level").lower()
levels = { "info": logging.INFO,
"debug": logging.DEBUG,
"warn":logging.WARN,
"error": logging.ERROR}
if level_str not in levels:
self.logger.warn("Unknown logging level '%s', keeping default" % level_str)
else:
self.logger.setLevel(levels[level_str])
self.logger.debug("loading model")
#
# Load model
#
model_path = config.get("model", "path")
try:
self.model = cubes.load_model(model_path)
except:
if not model_path:
model_path = 'unknown path'
raise common.ServerError("Unable to load model from %s" % model_path)
self.model_localizations = {}
if config.has_option("model", "locales"):
self.locales = config.get("model", "locales").split(",")
self.logger.info("model locales: %s" % self.locales)
elif self.model.locale:
self.locales = [self.model.locale]
else:
self.locales = []
if config.has_option("server","backend"):
backend = config.get("server","backend")
else:
backend = "cubes.backends.sql.browser"
self.create_workspace(backend, config)
def initialize_model():
global workspace
global model
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql.browser",
model,
url=DB_URL,
view_prefix="vft_")
def initialize_model():
global workspace
global model
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql.star",
model,
url=DB_URL,
fact_prefix="ft_")
def generate_ddl(args):
raise NotImplementedError("Temporarily disabled.")
model = cubes.load_model(args.model)
if args.backend:
backend = cubes.workspace.get_backend(args.backend)
else:
backend = cubes.backends.sql.browser
ddl = backend.ddl_for_model(args.url, model, fact_prefix=args.fact_prefix,
dimension_prefix=args.dimension_prefix,
fact_suffix=args.fact_suffix,
dimension_suffix=args.dimension_suffix)
print(ddl)
def generate_ddl(args):
raise NotImplementedError("Temporarily disabled.")
model = cubes.load_model(args.model)
if args.backend:
backend = cubes.workspace.get_backend(args.backend)
else:
backend = cubes.backends.sql.browser
ddl = backend.ddl_for_model(args.url, model, fact_prefix=args.fact_prefix,
dimension_prefix=args.dimension_prefix,
fact_suffix=args.fact_suffix,
dimension_suffix=args.dimension_suffix)
print(ddl)
def from_cubes(project_id):
cnt = Counter()
print "-------------- FROM CUBE ------------- " + project_id
model = cubes.load_model("../../cida_model.json")
ws = cubes.create_workspace("sql", model, url="postgres://localhost/crs")
cube = model.cube("cida")
browser = ws.browser(cube)
cell = cubes.Cell(cube)
cut = cubes.PointCut("project_number", [project_id])
cut = cubes.PointCut("project_number", [project_id])
cell2 = cell.slice(cut)
result = browser.aggregate(cell2, drilldown=["fiscal_year", "country_region_name", "id"])
for i, c in enumerate(result.cells):
cnt[sum] += c["amount_spent_sum"]
# print i,c['id'],project_id, c['fiscal_year'],c['country_region_name'],c['amount_spent_sum']
print cnt[sum]
def initialize_model():
global workspace
global model
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql", model, url=DB_URL)
engine = sqlalchemy.create_engine('sqlite:///:memory:')
tutorial.create_table_from_csv(engine,
"data/IBRD_Balance_Sheet__FY2010-t02.csv",
table_name=FACT_TABLE,
fields=[
("category", "string"),
("subcategory", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True
)
# 2. Load model and get cube of our interest
model = cubes.load_model("models/model_02.json")
cube = model.cube("irbd_balance")
# 3. Create a browser
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
# 4. get a cell representing the whole cube (all data)
cell = browser.full_cube()
result = browser.aggregate(cell)
print "Record count: %d" % result.summary["record_count"]
print "Total amount: %d" % result.summary["amount_sum"]
def setUp(self):
self.model_path = os.path.join(DATA_PATH, 'model.json')
self.model = cubes.load_model(self.model_path)
FACT_TABLE = "irbd_balance"
engine = sqlalchemy.create_engine('sqlite:///:memory:')
tutorial.create_table_from_csv(engine,
"data/IBRD_Balance_Sheet__FY2010-t02.csv",
table_name=FACT_TABLE,
fields=[("category", "string"),
("subcategory", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True)
# 2. Load model and get cube of our interest
model = cubes.load_model("models/model_02.json")
cube = model.cube("irbd_balance")
# 3. Create a browser
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
# 4. get a cell representing the whole cube (all data)
cell = browser.full_cube()
result = browser.aggregate(cell)
print "Record count: %d" % result.summary["record_count"]
print "Total amount: %d" % result.summary["amount_sum"]
A face is something that is measurable, like a contract.
A dimension is the context for the fact, location, time, type
Who signed the contract, how much was spent on the construction work, and where the transaction happen.
Also, cubes supports hierchies. So you can define levels for each dimension, eg. year month, day, or continent, continent, city
Levels and attributes
Hierarchy
Key Attributes
Label Attributes
"Multi-dimensional breadcrumbs"
'''
import cubes
model = cubes.load_model("cida_model.json")
ws = cubes.create_workspace("sql",model,url="sqlite:///cida_norm.db")
cube = model.cube("projects")
browser = ws.browser(cube)
result = browser.aggregate(drilldown=["project_number"])
print result.summary
print "---------------------"
for c in result.cells:
print c
def setUp(self):
path = os.path.join(DATA_PATH, 'model2.json')
self.model = cubes.load_model(path)
self.cube = self.model.cube("transactions")
self.dim_date = self.cube.dimension("date")
import cubes
model = cubes.load_model("vvo_model.json")
cube = model.cube("contracts")
workspace = cubes.create_workspace("sql", model, url="sqlite:///vvo_data.sqlite",
dimension_prefix="dm_",
fact_prefix="ft_")
# workspace = cubes.create_workspace("sql", model, url="postgres://localhost/ep2012",
# schema="vvo",
# dimension_prefix="dm_",
# fact_prefix="ft_",
# denormalized_view_schema="views",
# use_denormalization=False,
# denormalized_view_prefix="mft_")
def drilldown(cell, dimension):
"""Drill-down and aggregate recursively through als levels of `dimension`.
This function is like recursively traversing directories on a file system
and aggregating the file sizes, for example.
* `cell` - cube cell to drill-down
* `dimension` - dimension to be traversed through all levels
"""
if cell.is_base(dimension):
return
result = browser.aggregate(cell, drilldown=[dimension])
def initialize_model():
global workspace
global model
model = cubes.load_model(MODEL_PATH)
workspace = cubes.create_workspace("sql.browser", model, url=DB_URL, view_prefix="vft_")
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from cubes import load_model
from django.db import connections
from django.conf import settings
## Setup the connections to the DB for cubes/sqlalchemy
# engine = connection requirement for cubes/sqlalchemy
engine = create_engine('postgresql://%(USER)s:%(PASSWORD)s@%(HOST)s:%(PORT)s/%(NAME)s' % connections['default'].settings_dict)
# engine = create_engine('postgresql://*****:*****@vecnet-db.crc.nd.edu:5432/dw')
# engine = create_engine('postgresql://*****:*****@127.0.0.1:5432/dw2')
# Session = required when querying the database from sqlalchemy
Session = scoped_session(sessionmaker(bind=engine))
path = os.path.abspath(os.path.dirname(__file__))
parent_path = os.path.sep.join(path.split(os.path.sep)[:-2]) + os.path.sep
# Load the warehouse model from datawarehouse.json
#dwmodel = load_model(os.path.join(settings.PROJECT_ROOT, 'datawarehouse/fixtures/datawarehouse_mk2.json'))
if os.name == "nt":
#load_model won't accept full path on Windows, as it interprets c:// as protocol (like http:// etc)
dwmodel = load_model('datawarehouse/fixtures/datawarehouse_mk2.json')
else:
dwmodel = load_model(os.path.join(settings.PROJECT_ROOT, 'datawarehouse/fixtures/datawarehouse_mk2.json'))
def __init__(self, config = None):
"""Create a WSGI server for providing OLAP web service.
API:
* ``/model`` - get model metadata
* ``/model/dimension/dimension_name`` - get dimension metadata
* ``/model/dimension/dimension_name/levels`` - get levels of default dimension hierarchy
* ``/model/dimension/dimension_name/level_names`` - get just names of levels
* ``/aggregate`` - return aggregation result
"""
local.application = self
self.config = config
# Configure logger
self.logger = logging.getLogger(cubes.common.logger_name)
if self.config.has_option("server", "log"):
formatter = logging.Formatter(fmt='%(asctime)s %(levelname)s %(message)s')
handler = logging.FileHandler(self.config.get("server", "log"))
handler.setFormatter(formatter)
self.logger.addHandler(handler)
if self.config.has_option("server", "log_level"):
level_str = self.config.get("server", "log_level").lower()
levels = { "info": logging.INFO,
"debug": logging.DEBUG,
"warn":logging.WARN,
"error": logging.ERROR}
if level_str not in levels:
self.logger.warn("Unknown logging level '%s', keeping default" % level_str)
else:
self.logger.setLevel(levels[level_str])
db_defaults = {
"schema": None,
"view_prefix": None,
"view_suffix": None
}
self.dburl = config.get("db", "url")
self.schema = None
if config.has_option("db","schema"):
self.schema = config.get("db","schema")
self.view_prefix = None
if config.has_option("db","view_prefix"):
self.view_prefix = config.get("db", "view_prefix")
self.view_suffix = None
if config.has_option("db","view_suffix"):
self.view_suffix = config.get("db", "view_suffix")
self.engine = sqlalchemy.create_engine(self.dburl)
self.logger.info("creatign new database engine")
model_path = config.get("model", "path")
try:
self.model = cubes.load_model(model_path)
except:
if not model_path:
model_path = 'unknown path'
raise common.ServerError("Unable to load model from %s" % model_path)
self.model_localizations = {}
if config.has_option("model", "locales"):
self.locales = config.get("model", "locales").split(",")
self.logger.info("model locales: %s" % self.locales)
elif self.model.locale:
self.locales = [self.model.locale]
else:
self.locales = []
self.workspace = cubes.backends.sql.SQLWorkspace(self.model, self.engine, self.schema,
name_prefix = self.view_prefix,
name_suffix = self.view_suffix)
row_label = "".join(["<th>%s</th>" % h for h in tuple(row_hdr)])
values_str = "".join(
["<td>%s</td>" % (value[0] if value else 0) for value in row])
row_string = "<tr>%s%s</tr>" % (row_label, values_str)
out += row_string + "\n"
out += "</table></body></html>"
with open("table.html", "w") as f:
f.write(out)
########################################################################
# Create browsing context
model = cubes.load_model("vvo_model.json")
cube = model.cube("contracts")
workspace = cubes.create_workspace("sql",
model,
url="sqlite:///vvo_data.sqlite",
dimension_prefix="dm_",
fact_prefix="ft_")
browser = workspace.browser(cube)
cell = cubes.Cell(cube)
########################################################################
# Create cross-table (written to table.html in current directory)
result = browser.aggregate(cell, drilldown=["process_type", "date"])
rows = ["process_type.description"]
tutorial.create_table_from_csv(engine,
"data.csv",
table_name=FACT_TABLE,
fields=[
("category", "string"),
("category_label", "string"),
("subcategory", "string"),
("subcategory_label", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True
)
model = cubes.load_model("model.json")
cube = model.cube("irbd_balance")
cube.fact = FACT_TABLE
# 2. Create the view (required for the default backend)
print "creating view '%s'..." % FACT_VIEW
connection = engine.connect()
dn = cubes.backends.sql.SQLDenormalizer(cube, connection)
dn.create_view(FACT_VIEW)
print "done"
def setUp(self):
path = os.path.join(DATA_PATH, 'model2.json')
self.model = cubes.load_model(path)
self.cube = self.model.cube("transactions")
self.dim_date = self.cube.dimension("date")