def get_tm1_service(settings):
address = settings['ServerAddress']
port = settings['PortNumber']
user = settings['UserName']
ssl = True if settings['UseSSL'] == 'T' else False
password = decode("1234567890", settings['Password'])
namespace = settings['CAMNamespaceID']
session_name = '{}_{}_{}_{}'.format(address, port, user, namespace)
cookie_path = tempfile.gettempdir()
cookie_file = os.path.join(cookie_path, urlsafe_b64encode(session_name.encode()).decode() + ".tm1_session")
namespace = None if len(namespace) == 0 else namespace
connected = False
if os.path.isfile(cookie_file):
try:
service = TM1Service.restore_from_file(cookie_file)
if service._tm1_rest.is_connected():
connected = True
except TM1pyException as e:
pass
if not connected:
try:
service = TM1Service(address=address, port=port, user=user, password=password, namespace=namespace, ssl=ssl)
connected = True
except TM1pyException as e:
raise
service.save_to_file(cookie_file)
return service
def setup_tm1_services(max_workers: int, tasks_file_path: str,
execution_mode: ExecutionMode) -> dict:
""" Return Dictionary with TM1ServerName (as in config.ini) : Instantiated TM1Service
:return: Dictionary server_names and TM1py.TM1Service instances pairs
"""
if not os.path.isfile(CONFIG):
raise ValueError("{config} does not exist".format(config=CONFIG))
tm1_instances_in_tasks = get_instances_from_tasks_file(
execution_mode, max_workers, tasks_file_path)
tm1_services = dict()
# parse .ini
config = configparser.ConfigParser()
config.read(CONFIG, encoding='utf-8')
# build tm1_services dictionary
for tm1_server_name, params in config.items():
if tm1_server_name not in tm1_instances_in_tasks:
continue
# handle default values from configparser
if tm1_server_name != config.default_section:
try:
tm1_services[tm1_server_name] = TM1Service(
**params,
session_context=APP_NAME,
connection_pool_size=max_workers)
# Instance not running, Firewall or wrong connection parameters
except Exception as e:
logger.error(
"TM1 instance {} not accessible. Error: {}".format(
tm1_server_name, str(e)))
return tm1_services
def setup_tm1_services(maximum_workers):
""" Return Dictionary with TM1ServerName (as in config.ini) : Instantiated TM1Service
:return: Dictionary server_names and TM1py.TM1Service instances pairs
"""
if not os.path.isfile(CONFIG):
raise ValueError("{config} does not exist.".format(config=CONFIG))
tm1_services = dict()
# parse .ini
config = configparser.ConfigParser()
config.read(CONFIG)
# build tm1_services dictionary
for tm1_server_name, params in config.items():
# handle default values from configparser
if tm1_server_name != config.default_section:
try:
tm1_services[tm1_server_name] = TM1Service(
**params,
session_context=APP_NAME,
connection_pool_size=maximum_workers
)
# Instance not running, Firewall or wrong connection parameters
except Exception as e:
logging.error(
"TM1 instance {} not accessible. Error: {}".format(
tm1_server_name, str(e)
)
)
return tm1_services
def job():
entries = tm1_source.server.execute_transaction_log_delta_request()
if len(entries) > 0:
cellset = dict()
for entry in entries:
cellset[tuple(entry["Tuple"])] = entry["NewValue"]
with TM1Service(**config['tm1srv02']) as tm1_target:
tm1_target.cubes.cells.write_values(cube_target, cellset)
def setUpClass(cls):
"""
Establishes a connection to TM1 and creates TM! objects to use across all tests
"""
# Connection to TM1
cls.config = configparser.ConfigParser()
cls.config.read(Path(__file__).parent.joinpath('config.ini'))
cls.tm1 = TM1Service(**cls.config['tm1srv01'])
def collect_mdx_of_views_and_subsets():
lines = []
with TM1Service(address=ADDRESS,
port=PORT,
user=USER,
password=PWD,
namespace='',
gateway='',
ssl=SSL) as tm1:
parse_views(tm1, lines)
parse_subsets(tm1, lines)
# specify the dimension and hierarchy
with open(OUTPUT_FILE, "w") as file:
file.writelines(lines)
def setUpClass(cls) -> None:
"""
Establishes a connection to TM1 and creates TM1 objects to use across all tests
"""
# Connection to TM1
cls.config = configparser.ConfigParser()
cls.config.read(Path(__file__).parent.joinpath('config.ini'))
cls.tm1 = TM1Service(**cls.config['tm1srv01'])
# Build Dimensions
for dimension_name in DIMENSION_NAMES:
elements = [Element('Element {}'.format(str(j)), 'Numeric') for j in range(1, 1001)]
element_attributes = [ElementAttribute("Attr1", "String"),
ElementAttribute("Attr2", "Numeric"),
ElementAttribute("Attr3", "Numeric")]
hierarchy = Hierarchy(dimension_name=dimension_name,
name=dimension_name,
elements=elements,
element_attributes=element_attributes)
dimension = Dimension(dimension_name, [hierarchy])
if cls.tm1.dimensions.exists(dimension.name):
cls.tm1.dimensions.update(dimension)
else:
cls.tm1.dimensions.create(dimension)
# Build Cube
cube = Cube(CUBE_NAME, DIMENSION_NAMES)
if not cls.tm1.cubes.exists(CUBE_NAME):
cls.tm1.cubes.create(cube)
# Build cube view
view = NativeView(
cube_name=CUBE_NAME,
view_name=VIEW_NAME,
suppress_empty_columns=True,
suppress_empty_rows=True)
view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(
dimension_name=DIMENSION_NAMES[0],
expression='{[' + DIMENSION_NAMES[0] + '].Members}'))
view.add_row(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(
dimension_name=DIMENSION_NAMES[1],
expression='{[' + DIMENSION_NAMES[1] + '].Members}'))
view.add_column(
dimension_name=DIMENSION_NAMES[2],
subset=AnonymousSubset(
dimension_name=DIMENSION_NAMES[2],
expression='{[' + DIMENSION_NAMES[2] + '].Members}'))
if not cls.tm1.cubes.views.exists(CUBE_NAME, view.name, private=False):
cls.tm1.cubes.views.create(
view=view,
private=False)
# Build subset
subset = Subset(SUBSET_NAME, DIMENSION_NAMES[0], DIMENSION_NAMES[0], None, None, ["Element 1"])
if cls.tm1.dimensions.hierarchies.subsets.exists(
subset.name,
subset.dimension_name,
subset.hierarchy_name,
False):
cls.tm1.dimensions.hierarchies.subsets.delete(
subset.name,
subset.dimension_name,
subset.hierarchy_name,
False)
cls.tm1.dimensions.hierarchies.subsets.create(subset, False)
# Build process
p1 = Process(name=PROCESS_NAME)
p1.add_parameter('pRegion', 'pRegion (String)', value='US')
if cls.tm1.processes.exists(p1.name):
cls.tm1.processes.delete(p1.name)
cls.tm1.processes.create(p1)
# Build chore
c1 = Chore(
name=CHORE_NAME,
start_time=ChoreStartTime(datetime.now().year, datetime.now().month, datetime.now().day,
datetime.now().hour, datetime.now().minute, datetime.now().second),
dst_sensitivity=False,
active=True,
execution_mode=Chore.MULTIPLE_COMMIT,
frequency=ChoreFrequency(
days=int(random.uniform(0, 355)),
hours=int(random.uniform(0, 23)),
minutes=int(random.uniform(0, 59)),
seconds=int(random.uniform(0, 59))),
tasks=[ChoreTask(0, PROCESS_NAME, parameters=[{'Name': 'pRegion', 'Value': 'UK'}])])
cls.tm1.chores.create(c1)
# create Folder
app = FolderApplication("", TM1PY_APP_FOLDER)
cls.tm1.applications.create(application=app, private=False)
import configparser
from getpass import getpass
import keyring
from TM1py import TM1Service
INSTANCE = "tm1srv01"
config = configparser.ConfigParser()
config.read(r'..\config.ini')
address = config[INSTANCE]["address"]
port = config[INSTANCE]["port"]
ssl = config[INSTANCE]["ssl"]
user = config[INSTANCE]["user"]
# interact with Windows Credential Manager through the keyring library
password = keyring.get_password(INSTANCE, user)
if not password:
password = getpass(
f"Please insert password for user '{user}' and instance '{INSTANCE}':")
keyring.set_password(INSTANCE, user, password)
with TM1Service(**config[INSTANCE]) as tm1:
tm1_version = tm1.server.get_product_version()
print(tm1_version)
def main(instance_name: str, cube_name: str, view_names: List[str],
measure_dimension_only_numeric: bool, max_permutations: int,
executions: int, mode: ExecutionMode):
from executors import OriginalOrderExecutor, OneShotExecutor, GreedyExecutor, BruteForceExecutor, BestExecutor
with TM1Service(**config[instance_name], session_context=APP_NAME) as tm1:
original_dimension_order = tm1.cubes.get_storage_dimension_order(
cube_name=cube_name)
displayed_dimension_order = tm1.cubes.get_dimension_names(
cube_name=cube_name)
try:
permutation_results = list()
original_order = OriginalOrderExecutor(
tm1, cube_name, view_names, displayed_dimension_order,
executions, measure_dimension_only_numeric,
original_dimension_order)
permutation_results += original_order.execute()
# One Shot goes first, as it may blow up the overall RAM for the cube
if mode == ExecutionMode.ALL or mode == ExecutionMode.ONE_SHOT:
one_shot = OneShotExecutor(tm1, cube_name, view_names,
displayed_dimension_order,
executions,
measure_dimension_only_numeric)
permutation_results += one_shot.execute()
if mode == ExecutionMode.ALL or mode == ExecutionMode.BRUTE_FORCE:
brute_force = BruteForceExecutor(
tm1, cube_name, view_names, displayed_dimension_order,
executions, measure_dimension_only_numeric,
max_permutations)
permutation_results += brute_force.execute()
if mode == ExecutionMode.ALL or mode == ExecutionMode.GREEDY:
greedy = GreedyExecutor(tm1, cube_name, view_names,
displayed_dimension_order, executions,
measure_dimension_only_numeric,
max_permutations)
permutation_results += greedy.execute()
if mode == ExecutionMode.ALL or mode == ExecutionMode.BEST:
best = BestExecutor(tm1, cube_name, view_names,
displayed_dimension_order, executions,
measure_dimension_only_numeric)
permutation_results += best.execute()
optimus_result = OptimusResult(cube_name, permutation_results)
logging.info("Analysis Completed")
logging.info("More details in csv and png files in results folder")
for view_name in view_names:
optimus_result.to_csv(
view_name,
RESULT_CSV.format(cube_name, view_name, TIME_STAMP))
optimus_result.to_png(
view_name,
RESULT_PNG.format(cube_name, view_name, TIME_STAMP))
return True
except:
logging.error("Fatal error", exc_info=True)
return False
finally:
logging.info("Reestablishing original dimension order")
tm1.cubes.update_storage_dimension_order(cube_name,
original_dimension_order)
def setUpClass(cls):
cls.tm1 = TM1Service(**config['tm1srv01'])
from TM1py import TM1Service, NativeView, AnonymousSubset
mdx = """
SELECT
NON EMPTY {[Date].[2017-11-26], [Date].[2017-11-27]} * {[Bike Shares Measure].[Count]} ON ROWS,
NON EMPTY {[City].[NYC], [City].[Chicago]} ON COLUMNS
FROM [Bike Shares]
WHERE ([Version].[Actual])
"""
with TM1Service(address='10.77.19.60', port=12354, user='******', password='******', ssl=True) as tm1:
pivot = tm1.cubes.cells.execute_mdx_dataframe_pivot(mdx=mdx)
print(pivot)
view = NativeView(
cube_name="Bike Shares",
view_name="Bike Shares By City",
suppress_empty_columns=True,
suppress_empty_rows=True)
view.add_row(
dimension_name="Date",
subset=AnonymousSubset(
dimension_name="Date",
expression="{Tm1SubsetAll([Date])}"))
view.add_row(
dimension_name="Bike Shares Measure",
subset=AnonymousSubset(
dimension_name="Bike Shares Measure",
elements=["Count"]))
view.add_column(
dimension_name="City",
import tkinter
from GUI import Application
from TM1py import TM1Service
app = Application()
app.master.title=('Sample Application')
app.mainloop()
with TM1Service(address=app.TM1AddressEntry.get(), port=app.TM1PortEntry.get(), user=app.TM1UsernameEntry.get()
, password=app.TM1PasswordEntry.get(), ssl=app.sslvar.get()) as tm1:
cubelist=tm1.cubes.get_all_names()
for cube in cubelist:
print ("Cube name: ", cube)
### Simple cube list print. Parameters being used are:
### address = localhost, port = 8002, user = admin, password = '', ssl = True
import configparser
config = configparser.ConfigParser()
config.read('..\config.ini')
import time
from TM1py import TM1Service
cube_source = "Retail"
cube_target = "Retail"
# Establish connection to TM1 Source
with TM1Service(**config['tm1srv01']) as tm1_source:
# Start Change Tracking
tm1_source.server.initialize_transaction_log_delta_requests("Cube eq '" +
cube_source +
"'")
# Continuous checks
def job():
entries = tm1_source.server.execute_transaction_log_delta_request()
if len(entries) > 0:
cellset = dict()
for entry in entries:
cellset[tuple(entry["Tuple"])] = entry["NewValue"]
with TM1Service(**config['tm1srv02']) as tm1_target:
tm1_target.cubes.cells.write_values(cube_target, cellset)
while True:
job()
def main(instance_name: str,
view_name: str,
process_name: str,
executions: int,
fast: bool,
output: str,
update: bool,
password: str = None):
config = get_tm1_config()
tm1_args = dict(config[instance_name])
tm1_args['session_context'] = APP_NAME
if password:
tm1_args['password'] = password
tm1_args['decode_b64'] = False
with TM1Service(**tm1_args) as tm1:
original_performance_monitor_state = retrieve_performance_monitor_state(
tm1)
activate_performance_monitor(tm1)
model_cubes = filter(lambda c: not c.startswith("}"),
tm1.cubes.get_all_names())
for cube_name in model_cubes:
if not tm1.cubes.views.exists(cube_name, view_name, private=False):
logging.info(
f"Skipping cube '{cube_name}' since view '{view_name}' does not exist"
)
continue
original_vmm, original_vmt = retrieve_vmm_vmt(tm1, cube_name)
write_vmm_vmt(tm1, cube_name, "1000000", "1000000")
logging.info(f"Starting analysis for cube '{cube_name}'")
original_dimension_order = tm1.cubes.get_storage_dimension_order(
cube_name=cube_name)
logging.info(
f"Original dimension order for cube '{cube_name}' is: '{original_dimension_order}'"
)
displayed_dimension_order = tm1.cubes.get_dimension_names(
cube_name=cube_name)
measure_dimension_only_numeric = is_dimension_only_numeric(
tm1, original_dimension_order[-1])
permutation_results = list()
try:
original_order = OriginalOrderExecutor(
tm1, cube_name, [view_name], process_name,
displayed_dimension_order, executions,
measure_dimension_only_numeric, original_dimension_order)
permutation_results += original_order.execute(
reset_counter=True)
main_executor = MainExecutor(tm1, cube_name, [view_name],
process_name,
displayed_dimension_order,
executions,
measure_dimension_only_numeric,
fast)
permutation_results += main_executor.execute()
optimus_result = OptimusResult(cube_name, permutation_results)
best_permutation = optimus_result.best_result
logging.info(f"Completed analysis for cube '{cube_name}'")
if not best_permutation:
logging.info(
f"No ideal dimension order found for cube '{cube_name}'."
f"Please pick manually based on csv and png results.")
else:
best_order = best_permutation.dimension_order
if update:
tm1.cubes.update_storage_dimension_order(
cube_name, best_order)
logging.info(
f"Updated dimension order for cube '{cube_name}' to {best_order}"
)
else:
logging.info(
f"Best order for cube '{cube_name}': {best_order}")
tm1.cubes.update_storage_dimension_order(
cube_name, original_dimension_order)
logging.info(
f"Restored original dimension order for cube '{cube_name}' to {original_dimension_order}"
)
except:
logging.error("Fatal error", exc_info=True)
return False
finally:
with suppress(Exception):
write_vmm_vmt(tm1, cube_name, original_vmm, original_vmt)
with suppress(Exception):
if original_performance_monitor_state:
activate_performance_monitor(tm1)
else:
deactivate_performance_monitor(tm1)
if len(permutation_results) > 0:
optimus_result = OptimusResult(cube_name,
permutation_results)
optimus_result.to_png(
view_name, process_name,
RESULT_PATH / RESULT_PNG.format(
cube_name, view_name, process_name, TIME_STAMP))
if output.upper() == "XLSX":
optimus_result.to_xlsx(
view_name, process_name, RESULT_PATH /
RESULT_XLSX.format(cube_name, view_name,
process_name, TIME_STAMP))
else:
if not output.upper() == "CSV":
logging.warning(
"Value for -o / --output must be 'CSV' or 'XLSX'. Default is CSV"
)
optimus_result.to_csv(
view_name, process_name, RESULT_PATH /
RESULT_CSV.format(cube_name, view_name,
process_name, TIME_STAMP))
return True
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
class TestDecorators(unittest.TestCase):
tm1 = TM1Service(**config["tm1srv01"])
@classmethod
def setUpClass(cls):
cls.dimension1 = Dimension(
name=DIMENSION_NAMES[0],
hierarchies=[
Hierarchy(name=DIMENSION_NAMES[0],
dimension_name=DIMENSION_NAMES[0],
elements=[
Element(name="Element_{}".format(i),
element_type="Numeric")
for i in range(1, 101)
])
])
cls.dimension2 = Dimension(
name=DIMENSION_NAMES[1],
hierarchies=[
Hierarchy(name=DIMENSION_NAMES[1],
dimension_name=DIMENSION_NAMES[1],
elements=[
Element(name="Element_{}".format(i),
element_type="Numeric")
for i in range(1, 101)
])
])
cls.cube_source = Cube(name=CUBE_NAME_SOURCE,
dimensions=DIMENSION_NAMES)
cls.cube_target = Cube(name=CUBE_NAME_TARGET,
dimensions=DIMENSION_NAMES)
@classmethod
def tearDownClass(cls):
cls.tm1.logout()
def setUp(self):
if not self.tm1.dimensions.exists(dimension_name=self.dimension1.name):
self.tm1.dimensions.create(dimension=self.dimension1)
if not self.tm1.dimensions.exists(dimension_name=self.dimension2.name):
self.tm1.dimensions.create(dimension=self.dimension2)
if not self.tm1.cubes.exists(cube_name=self.cube_source.name):
self.tm1.cubes.create(cube=self.cube_source)
if not self.tm1.cubes.exists(cube_name=self.cube_target.name):
self.tm1.cubes.create(cube=self.cube_target)
def tearDown(self):
self.tm1.cubes.delete(cube_name=self.cube_source.name)
self.tm1.cubes.delete(cube_name=self.cube_target.name)
self.tm1.dimensions.delete(dimension_name=self.dimension1.name)
self.tm1.dimensions.delete(dimension_name=self.dimension2.name)
def test_tm1io_input_nativeview_output_nativeview(self):
# create input view
view_input = NativeView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
suppress_empty_columns=False,
suppress_empty_rows=False)
view_input.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression="{ HEAD ( { [" +
DIMENSION_NAMES[0] + "].Members}," +
str(len(IRR_INPUT_VALUES)) + ") }"))
view_input.add_column(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression="{[" + DIMENSION_NAMES[1] +
"].[Element_1]}"))
self.tm1.cubes.views.create(view=view_input, private=False)
# create output view
view_output = NativeView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
suppress_empty_columns=False,
suppress_empty_rows=False)
view_output.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[0],
expression="{[" + DIMENSION_NAMES[0] +
"].[Element_1]}"))
view_output.add_column(
dimension_name=DIMENSION_NAMES[1],
subset=AnonymousSubset(dimension_name=DIMENSION_NAMES[1],
expression="{[" + DIMENSION_NAMES[1] +
"].[Element_1]}"))
self.tm1.cubes.views.create(view=view_output, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, IRR_INPUT_VALUES)
# execute method
result = irr(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
tm1_target="tm1srv02",
cube_source=CUBE_NAME_SOURCE,
cube_target=CUBE_NAME_TARGET,
view_source=VIEW_NAME_SOURCE,
view_target=VIEW_NAME_TARGET)
self.assertAlmostEqual(IRR_EXPECTED_RESULT,
result,
delta=IRR_TOLERANCE)
# check output view
cell_value = next(
self.tm1.cubes.cells.execute_view_values(
cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
self.assertAlmostEqual(cell_value,
IRR_EXPECTED_RESULT,
delta=IRR_TOLERANCE)
def test_tm1io_input_mdx_view_output_mdx_view(self):
# create input view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# create output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
result = stdev(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
tm1_target="tm1srv02",
cube_source=CUBE_NAME_SOURCE,
cube_target=CUBE_NAME_TARGET,
view_source=VIEW_NAME_SOURCE,
view_target=VIEW_NAME_TARGET)
self.assertAlmostEqual(STDEV_EXPECTED_RESULT,
result,
delta=STDEV_TOLERANCE)
# check output view
cell_value = next(
self.tm1.cubes.cells.execute_view_values(
cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
self.assertAlmostEqual(cell_value,
STDEV_EXPECTED_RESULT,
delta=STDEV_TOLERANCE)
def test_tm1io_input_view(self):
# define input view and output view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(IRR_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, IRR_INPUT_VALUES)
# execute method
result = irr(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE)
self.assertAlmostEqual(IRR_EXPECTED_RESULT,
result,
delta=IRR_TOLERANCE)
def test_tm1io_input_values_output_view(self):
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# execute method
stdev_p(tm1_services={"tm1srv01": self.tm1},
tm1_target="tm1srv01",
cube_target=CUBE_NAME_TARGET,
view_target=VIEW_NAME_TARGET,
values=STDEV_INPUT_VALUES,
tidy=False)
# do check
result = next(
self.tm1.cubes.cells.execute_view_values(
cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
self.assertAlmostEqual(result,
STDEV_P_EXPECTED_RESULT,
delta=STDEV_TOLERANCE)
def test_tm1tidy_true_input_view_output_view(self):
# create source_view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# create target_view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
stdev(tm1_services={
"tm1srv01": self.tm1,
"tm1srv02": self.tm1
},
tm1_source="tm1srv01",
tm1_target="tm1srv02",
cube_source=CUBE_NAME_SOURCE,
cube_target=CUBE_NAME_TARGET,
view_source=VIEW_NAME_SOURCE,
view_target=VIEW_NAME_TARGET,
tidy=True)
# check existence
self.assertFalse(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
private=False))
self.assertFalse(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
def test_tm1tidy_false_input_view_output_view(self):
# define input view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
stdev(tm1_services={"tm1srv01": self.tm1},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE,
tidy=False)
# check existence
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
private=False))
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
def test_tm1tidy_true_input_view(self):
# define input view and output view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
stdev(tm1_services={"tm1srv01": self.tm1},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE,
tidy=True)
# check existence
self.assertFalse(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
private=False))
def test_tm1tidy_false_input_view(self):
# define input view and output view
mdx_input = MDX_TEMPLATE_SHORT.format(
rows="{ HEAD ( { [" + DIMENSION_NAMES[0] + "].Members}," +
str(len(STDEV_INPUT_VALUES)) + ") }",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_SOURCE)
view_input = MDXView(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
MDX=mdx_input)
self.tm1.cubes.views.create(view=view_input, private=False)
# write values into input view
mdx = view_input.MDX
self.tm1.cubes.cells.write_values_through_cellset(
mdx, STDEV_INPUT_VALUES)
# execute method
stdev_p(tm1_services={"tm1srv01": self.tm1},
tm1_source="tm1srv01",
cube_source=CUBE_NAME_SOURCE,
view_source=VIEW_NAME_SOURCE,
tidy=False)
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_SOURCE,
view_name=VIEW_NAME_SOURCE,
private=False))
def test_tm1tidy_true_input_values_output_view(self):
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# execute method
stdev(tm1_services={"tm1srv01": self.tm1},
tm1_target="tm1srv01",
cube_target=CUBE_NAME_TARGET,
view_target=VIEW_NAME_TARGET,
values=STDEV_INPUT_VALUES,
tidy=True)
# check view existence
self.assertFalse(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
def test_tm1tidy_false_input_values_output_view(self):
# define output view
mdx_output = MDX_TEMPLATE_SHORT.format(
rows="{[" + DIMENSION_NAMES[0] + "].[Element_1]}",
columns="{[" + DIMENSION_NAMES[1] + "].[Element_1]}",
cube=CUBE_NAME_TARGET)
view_output = MDXView(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
MDX=mdx_output)
self.tm1.cubes.views.create(view=view_output, private=False)
# execute method
irr(tm1_services={"tm1srv01": self.tm1},
tm1_target="tm1srv01",
cube_target=CUBE_NAME_TARGET,
view_target=VIEW_NAME_TARGET,
values=IRR_INPUT_VALUES,
tidy=False)
# check view existence
self.assertTrue(
self.tm1.cubes.views.exists(cube_name=CUBE_NAME_TARGET,
view_name=VIEW_NAME_TARGET,
private=False))
import time
from TM1py import TM1Service
ADDRESS = "localhost"
SSL = True
USER = "******"
PASSWORD = "******"
tm1_master = TM1Service(address=ADDRESS,
port=12354,
ssl=SSL,
user=USER,
password=PASSWORD)
tm1_other = TM1Service(address=ADDRESS,
port=12297,
ssl=SSL,
user=USER,
password=PASSWORD)
while True:
dimension_master = tm1_master.dimensions.get(dimension_name="Region")
dimension_other = tm1_other.dimensions.get(dimension_name="Region")
if dimension_master != dimension_other:
print(
f"Recognized changes. Updating dimension: '{dimension_master.name}'"
)
tm1_other.dimensions.update(dimension_master)
subsets_names = tm1_master.subsets.get_all_names("Region")
config = configparser.ConfigParser()
# storing the credentials in a file is not recommended for purposes other than testing.
# it's better to setup CAM with SSO or use keyring to store credentials in the windows credential manager. Sample:
# Samples/credentials_best_practice.py
config.read(r'..\config.ini')
mdx = """
SELECT
NON EMPTY {[Date].[2017-11-26], [Date].[2017-11-27]} * {[Bike Shares Measure].[Count]} ON ROWS,
NON EMPTY {[City].[NYC], [City].[Chicago]} ON COLUMNS
FROM [Bike Shares]
WHERE ([Version].[Actual])
"""
with TM1Service(**config['tm1srv01']) as tm1:
pivot = tm1.cubes.cells.execute_mdx_dataframe_pivot(mdx=mdx)
print(pivot)
view = NativeView(cube_name="Bike Shares",
view_name="Bike Shares By City",
suppress_empty_columns=True,
suppress_empty_rows=True)
view.add_row(dimension_name="Date",
subset=AnonymousSubset(dimension_name="Date",
expression="{Tm1SubsetAll([Date])}"))
view.add_row(dimension_name="Bike Shares Measure",
subset=AnonymousSubset(dimension_name="Bike Shares Measure",
elements=["Count"]))
view.add_column(dimension_name="City",
subset=AnonymousSubset(dimension_name="City",
def main(instance_name: str, view_name: str, executions: int, fast: bool):
config = get_tm1_config()
with TM1Service(**config[instance_name], session_context=APP_NAME) as tm1:
model_cubes = filter(lambda c: not c.startswith("}"),
tm1.cubes.get_all_names())
for cube_name in model_cubes:
if not tm1.cubes.views.exists(cube_name, view_name, private=False):
logging.info(
f"Skipping cube '{cube_name}' since view '{view_name}' does not exist"
)
continue
original_vmm, original_vmt = retrieve_vmm_vmt(tm1, cube_name)
write_vmm_vmt(tm1, cube_name, "1000000", "1000000")
logging.info(f"Starting analysis for cube '{cube_name}'")
original_dimension_order = tm1.cubes.get_storage_dimension_order(
cube_name=cube_name)
logging.info(
f"Original dimension order for cube '{cube_name}' is '{original_dimension_order}'"
)
displayed_dimension_order = tm1.cubes.get_dimension_names(
cube_name=cube_name)
measure_dimension_only_numeric = is_dimension_only_numeric(
tm1, original_dimension_order[-1])
permutation_results = list()
try:
original_order = OriginalOrderExecutor(
tm1, cube_name, [view_name], displayed_dimension_order,
executions, measure_dimension_only_numeric,
original_dimension_order)
permutation_results += original_order.execute(
reset_counter=True)
main_executor = MainExecutor(tm1, cube_name, [view_name],
displayed_dimension_order,
executions,
measure_dimension_only_numeric,
fast)
permutation_results += main_executor.execute()
optimus_result = OptimusResult(cube_name, permutation_results)
best_permutation = optimus_result.best_result
logging.info(f"Completed analysis for cube '{cube_name}'")
if not best_permutation:
logging.info(
f"No ideal dimension order found for cube '{cube_name}'."
f"Please pick manually based on csv and png results.")
return True
best_order = best_permutation.dimension_order
tm1.cubes.update_storage_dimension_order(cube_name, best_order)
logging.info(
f"Updated dimension order for cube '{cube_name}' to {best_order}"
)
except:
logging.error("Fatal error", exc_info=True)
return False
finally:
with suppress(Exception):
write_vmm_vmt(tm1, cube_name, original_vmm, original_vmt)
if len(permutation_results) > 0:
optimus_result = OptimusResult(cube_name,
permutation_results)
optimus_result.to_csv(
view_name,
RESULT_CSV.format(cube_name, view_name, TIME_STAMP))
optimus_result.to_png(
view_name,
RESULT_PNG.format(cube_name, view_name, TIME_STAMP))
return True
