"address": "localhost",
"port": "12354",
"user": "******",
"password": "******",
"ssl": True
}
tm1_target_parameters = {
"address": "localhost",
"port": "9123",
"user": "******",
"password": "******",
"ssl": True
}
# Setup Connections
tm1_source = TM1Service(**tm1_source_parameters)
tm1_target = TM1Service(**tm1_target_parameters)
# Query data from source TM1 model through MDX
mdx = "SELECT " \
"{[plan_chart_of_accounts].[41101],[plan_chart_of_accounts].[42201]} on ROWS, " \
"{[plan_time].[Oct-2004],[plan_time].[Nov-2004],[plan_time].[Dec-2004]} on COLUMNS " \
"FROM [Plan_BudgetPlan] " \
"WHERE " \
"([plan_version].[FY 2004 Budget],[plan_business_unit].[10110],[plan_department].[410]," \
"[plan_exchange_rates].[local],[plan_source].[input])"
data = tm1_source.data.execute_mdx(mdx)
# Rearrange data
values = [cell['Value'] for cell in data.values()]
# Send data to target TM1 instance
tm1_target.data.write_values_through_cellset(mdx, values)
'EXINUS': {'From': 'USD', 'To': 'INR', 'Invert': 'Y'},
'EXJPUS': {'From': 'USD', 'To': 'JPY', 'Invert': 'Y'}
}
for currency_ticker, currency_details in currency_pairs.items():
# Load FX Rates from FRED through pandas datareader
start = datetime(year=2016, month=1, day=1)
raw_data = web.get_data_fred(currency_ticker, start)
# Remove NaN
data = raw_data.dropna()
# Create cellset and push it to FX Cube
cellset = collections.OrderedDict()
for tmstp, data in data.iterrows():
date = tmstp.date()
my_year, my_month = date.year, date.month
# Handle Australian Financial Year
if my_month > 6:
my_year += 1
# Handle Inverted FX Rates
if currency_details["Invert"] == 'Y':
value = 1 / data.values[0]
else:
value = data.values[0]
coordinates = (currency_details["From"], currency_details["To"], my_year, my_month, 'Month Close')
cellset[coordinates] = value
with TM1Service(address=address, port=port, user=user, password=password, ssl=ssl) as tm1:
tm1.cubes.cells.write_values(cube_name, cellset)
"""
Get a dimension. Update it and push it back to TM1.
"""
import configparser
config = configparser.ConfigParser()
config.read('..\config.ini')
import uuid
from TM1py.Services import TM1Service
# Connection to TM1. Needs Address, Port, Credentials, and SSL
with TM1Service(**config['tm1srv01']) as tm1:
# get dimension
dimension = tm1.dimensions.get('plan_department')
# get the default hierarchy of the dimension
h = dimension.hierarchies[0]
# create new random element name
parent = str(uuid.uuid4())
child = str(uuid.uuid4())
# add elements to hierarchy
h.add_element(element_name=parent, element_type='Numeric')
h.add_element(element_name=child, element_type='Numeric')
# add edge to hierarchy
h.add_edge(parent, child, 1000)
def setUpClass(cls):
cls.tm1 = TM1Service(**config['tm1srv01'])
def setUpClass(cls):
cls.tm1 = TM1Service(**test_config)
def test_impersonate(self):
tm1 = TM1Service(**self.config['tm1srv01'])
self.assertNotEqual(self.user_name, tm1.whoami.name)
tm1 = TM1Service(**self.config['tm1srv01'], impersonate=self.user_name)
self.assertEqual(self.user_name, tm1.whoami.name)
def setUpClass(cls):
cls.tm1 = TM1Service(**test_config)
cls.cube_name = 'TM1py_unittest_cube_{}'.format(str(uuid.uuid4()))
def setUpClass(cls):
cls.tm1 = TM1Service(**config['tm1srv01'])
cls.dimension_name = 'TM1py_unittest_dimension_{}'.format(
int(uuid.uuid4()))
cls.hierarchy_name = cls.dimension_name
"""
Instantiate TM1py with a custom session_context.
Session_context is displayed in column "session" in Arc
"""
import configparser
from TM1py.Services import TM1Service
config = configparser.ConfigParser()
config.read('..\config.ini')
APP_NAME = "My TM1py Application"
with TM1Service(**config['tm1srv01'], session_context=APP_NAME) as tm1:
dimensions = tm1.dimensions.get_all_names()
for dim in dimensions:
print(dim)
Read a csv file with ~ 1000000 lines and write the data to a cube
Takes about 24 seconds.
"""
from TM1py.Services import TM1Service
# Build cellset from file
cube = ''
cellset = {}
# Line 1: "Planning Sample:plan_BudgetPlan";"FY 2003 Budget";"10110";"105";"41101";"local";"input";"Jan-2003";315512.69
with open("plan_BudgetPlan.csv", "r") as file:
# Read coordinates and values from each line
for line in file:
entries = line.split(";")
coordinates = tuple([coordinate[1:-1] for coordinate in entries[1:-1]])
value = entries[-1]
cellset[coordinates] = value
# Read cube name from the last line in the file
server_and_cube = line.split(";")[0]
cube = server_and_cube.split(":")[1][0:-1]
# Push cellset to TM1
with TM1Service(address="localhost",
port=12354,
user="******",
password="******",
ssl=True) as tm1:
tm1.cubes.cells.write_values(cube, cellset)
def setup_class(cls):
cls.tm1 = TM1Service(**test_config)
cls.dimension_name = dimension_prefix.format(uuid.uuid4())
cls.subset_name = dimension_prefix.format(uuid.uuid4())
class TestProcessMethods(unittest.TestCase):
tm1 = TM1Service(address=address,
port=port,
user=user,
password=pwd,
ssl=ssl)
random_string = str(uuid.uuid4())
# None process
p_none = Process(name=process_prefix + '_none_' + random_string,
datasource_type='None')
# ACII process
p_ascii = Process(
name=process_prefix + '_ascii_' + random_string,
datasource_type='ASCII',
datasource_ascii_delimiter_type='Character',
datasource_ascii_delimiter_char=',',
datasource_ascii_header_records=2,
datasource_ascii_quote_character='^',
datasource_ascii_thousand_separator='~',
prolog_procedure="sTestProlog = 'test prolog procedure'",
metadata_procedure="sTestMeta = 'test metadata procedure'",
data_procedure="sTestData = 'test data procedure'",
epilog_procedure="sTestEpilog = 'test epilog procedure'",
datasource_data_source_name_for_server=r'C:\Data\file.csv',
datasource_data_source_name_for_client=r'C:\Data\file.csv')
# Variables
p_ascii.add_variable('v_1', 'Numeric')
p_ascii.add_variable('v_2', 'Numeric')
p_ascii.add_variable('v_3', 'Numeric')
p_ascii.add_variable('v_4', 'Numeric')
# Parameters
p_ascii.add_parameter('p_Year', 'year?', '2016')
# View process
p_view = Process(name=process_prefix + '_view_' + random_string,
datasource_type='TM1CubeView',
datasource_view='view1',
datasource_data_source_name_for_client='Plan_BudgetPlan',
datasource_data_source_name_for_server='Plan_BudgetPlan')
# ODBC process
p_odbc = Process(name=process_prefix + '_odbc_' + random_string,
datasource_type='ODBC',
datasource_password='******',
datasource_user_name='user')
# Subset process
subset_name = process_prefix + '_subset_' + random_string
subset = Subset(dimension_name='plan_business_unit',
subset_name=subset_name,
elements=['10110', '10120', '10200', '10210'])
tm1.subsets.create(subset, False)
p_subset = Process(
name=process_prefix + '_subset_' + random_string,
datasource_type='TM1DimensionSubset',
datasource_data_source_name_for_server=subset.dimension_name,
datasource_subset=subset.name,
metadata_procedure="sTest = 'abc';")
# Create Process
def test1_create_process(self):
self.tm1.processes.create(self.p_none)
self.tm1.processes.create(self.p_ascii)
self.tm1.processes.create(self.p_view)
self.tm1.processes.create(self.p_odbc)
self.tm1.processes.create(self.p_subset)
# Get Process
def test2_get_process(self):
p1 = self.tm1.processes.get(self.p_ascii.name)
self.assertEqual(p1.body, self.p_ascii.body)
p2 = self.tm1.processes.get(self.p_none.name)
self.assertEqual(p2.body, self.p_none.body)
p3 = self.tm1.processes.get(self.p_view.name)
self.assertEqual(p3.body, self.p_view.body)
p4 = self.tm1.processes.get(self.p_odbc.name)
p4.datasource_password = None
self.p_odbc.datasource_password = None
self.assertEqual(p4.body, self.p_odbc.body)
p5 = self.tm1.processes.get(self.p_subset.name)
self.assertEqual(p5.body, self.p_subset.body)
# Update process
def test3_update_process(self):
# get
p = self.tm1.processes.get(self.p_ascii.name)
# modify
p.data_procedure = "SaveDataAll;"
# update on Server
self.tm1.processes.update(p)
# get again
p_ascii_updated = self.tm1.processes.get(p.name)
# assert
self.assertNotEqual(p_ascii_updated.data_procedure,
self.p_ascii.data_procedure)
# Delete process
def test4_delete_process(self):
self.tm1.processes.delete(self.p_none.name)
self.tm1.processes.delete(self.p_ascii.name)
self.tm1.processes.delete(self.p_view.name)
self.tm1.processes.delete(self.p_odbc.name)
self.tm1.processes.delete(self.p_subset.name)
self.tm1.subsets.delete(self.subset.dimension_name, self.subset_name,
False)
def test5_logout(self):
self.tm1.logout()
def count_public_views_and_subsets():
####################################################
# Author: Wim Gielis
# Date: 17-10-2020
# Purpose:
# - list the number of public cube views and dimension subsets
# - for clean up purposes, of objects lurking around in the TM1 data directory
# - there is a similar coding in Turbo Integrator available at GitHub. All in all, I prefer the solution in TI.
####################################################
# =============================================================================================================
# START of parameters and settings
# =============================================================================================================
# TM1 connection settings (IntegratedSecurityMode = 1 for now)
ADDRESS = 'localhost'
USER = '******'
PWD = ''
PORT = 8001
SSL = False
RESULT_FILE = r'D:\count public views and subsets.txt'
# list a cube or dimension only if the count of views, dimensions, resp. exceeds the threshold
threshold_views = 8
threshold_subsets = 8
# =============================================================================================================
# END of parameters and settings
# =============================================================================================================
log_lines = []
public_views = {}
public_subsets = {}
tm1 = TM1Service(address=ADDRESS,
port=PORT,
user=USER,
password=PWD,
namespace='',
gateway='',
ssl=SSL)
# iterate through cubes and dimensions to count the public views, subsets, resp.
cube_names = tm1.cubes.get_all_names()
for cube_name in cube_names:
private_view_names, public_views_names = tm1.cubes.views.get_all_names(
cube_name=cube_name)
public_views[cube_name] = len(public_views_names)
# - remove dictionary items if the count does not exceed the threshold
# - sort the dictionary
if public_views:
public_views = dict(
filter(lambda elem: elem[1] >= threshold_views,
public_views.items()))
if public_views:
public_views = sorted(public_views.items(),
key=lambda x: x[1],
reverse=True)
# now similar coding for subsets, but we will increment dictionary values
dimension_names = tm1.dimensions.get_all_names()
for dimension_name in dimension_names:
subsets = tm1.dimensions.subsets.get_all_names(
dimension_name=dimension_name,
hierarchy_name=dimension_name,
private=False)
# increment instead of write, because PAW alternate hierarchies will be counted with the main (container) dimension
if dimension_name in public_subsets:
public_subsets[dimension_name] += len(subsets)
else:
public_subsets[dimension_name] = len(subsets)
if public_subsets:
public_subsets = dict(
filter(lambda elem: elem[1] >= threshold_subsets,
public_subsets.items()))
if public_subsets:
public_subsets = sorted(public_subsets.items(),
key=lambda x: x[1],
reverse=True)
# output to a text file
log_lines.append("Public cube views: (at least " + str(threshold_views) +
")")
log_lines.append("-" * 35 + "\n")
for k, value in public_views:
log_lines.append("\t{}\t\t{}".format(value, k))
log_lines.append("\n\nPublic dimension subsets: (at least " +
str(threshold_subsets) + ")")
log_lines.append("-" * 35 + "\n")
for k, value in public_subsets:
log_lines.append("\t{}\t\t{}".format(value, k))
with open(RESULT_FILE, 'w', encoding='utf-8') as file:
file.write("\n".join(log_lines))
file.close()
class TestChoreMethods(unittest.TestCase):
tm1 = TM1Service(address=address,
port=port,
user=user,
password=pwd,
ssl=ssl)
# chore properties
chore_name = 'TM1py_unittest_chore_' + str(uuid.uuid4())
start_time = datetime.now()
frequency_days = int(random.uniform(0, 355))
frequency_hours = int(random.uniform(0, 23))
frequency_minutes = int(random.uniform(0, 59))
frequency_seconds = int(random.uniform(0, 59))
frequency = ChoreFrequency(days=frequency_days,
hours=frequency_hours,
minutes=frequency_minutes,
seconds=frequency_seconds)
tasks = [
ChoreTask(0,
process_name1,
parameters=[{
'Name': 'pRegion',
'Value': 'UK'
}]),
ChoreTask(1,
process_name1,
parameters=[{
'Name': 'pRegion',
'Value': 'FR'
}]),
ChoreTask(2,
process_name1,
parameters=[{
'Name': 'pRegion',
'Value': 'CH'
}])
]
# Check if process exists. If not create it
@classmethod
def setup_class(cls):
p1 = Process(name=process_name1)
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)
p2 = Process(name=process_name2)
p2.add_parameter('pRegion', 'pRegion (String)', value='UK')
if cls.tm1.processes.exists(p2.name):
cls.tm1.processes.delete(p2.name)
cls.tm1.processes.create(p2)
# 1. Create chore
def test_1create_chore(self):
c = Chore(name=self.chore_name,
start_time=ChoreStartTime(self.start_time.year,
self.start_time.month,
self.start_time.day,
self.start_time.hour,
self.start_time.minute,
self.start_time.second),
dst_sensitivity=False,
active=True,
execution_mode='MultipleCommit',
frequency=self.frequency,
tasks=self.tasks)
# No exceptions -> means test passed
self.tm1.chores.create(c)
# 2. Get chore
def test_2get_chore(self):
c = self.tm1.chores.get(self.chore_name)
# check all properties
self.assertEqual(c._start_time._datetime,
self.start_time.replace(microsecond=0))
self.assertEqual(c._name, self.chore_name)
self.assertEqual(c._dst_sensitivity, False)
self.assertEqual(c._active, True)
self.assertEqual(c._execution_mode, 'MultipleCommit')
self.assertEqual(c._frequency._days, str(self.frequency_days))
self.assertEqual(c._frequency._hours,
str(self.frequency_hours).zfill(2))
self.assertEqual(c._frequency._minutes,
str(self.frequency_minutes).zfill(2))
self.assertEqual(c._frequency._seconds,
str(self.frequency_seconds).zfill(2))
for task1, task2 in zip(self.tasks, c._tasks):
self.assertEqual(task1, task2)
# 3. Update chore
def test_3update_chore(self):
# get chore
c = self.tm1.chores.get(self.chore_name)
# update all properties
# update start time
start_time = datetime.now()
c._start_time = ChoreStartTime(start_time.year, start_time.month,
start_time.day, start_time.hour,
start_time.minute, start_time.second)
# update frequency
frequency_days = int(random.uniform(0, 355))
frequency_hours = int(random.uniform(0, 23))
frequency_minutes = int(random.uniform(0, 59))
frequency_seconds = int(random.uniform(0, 59))
c._frequency = ChoreFrequency(days=frequency_days,
hours=frequency_hours,
minutes=frequency_minutes,
seconds=frequency_seconds)
# update tasks
tasks = [
ChoreTask(0,
process_name2,
parameters=[{
'Name': 'pRegion',
'Value': 'DE'
}]),
ChoreTask(1,
process_name2,
parameters=[{
'Name': 'pRegion',
'Value': 'ES'
}]),
ChoreTask(2,
process_name2,
parameters=[{
'Name': 'pRegion',
'Value': 'US'
}])
]
c._tasks = tasks
# execution mode
c._execution_mode = "SingleCommit"
# activate
c.deactivate()
# update chore in TM1
self.tm1.chores.update(c)
# get chore and check all properties
c = self.tm1.chores.get(chore_name=self.chore_name)
self.assertEqual(c._start_time._datetime.replace(microsecond=0),
start_time.replace(microsecond=0))
self.assertEqual(c._name, self.chore_name)
self.assertEqual(c._dst_sensitivity, False)
self.assertEqual(c._active, False)
self.assertEqual(c._execution_mode, 'SingleCommit')
self.assertEqual(c._frequency._days, str(frequency_days))
self.assertEqual(c._frequency._hours, str(frequency_hours).zfill(2))
self.assertEqual(c._frequency._minutes,
str(frequency_minutes).zfill(2))
self.assertEqual(c._frequency._seconds,
str(frequency_seconds).zfill(2))
for task1, task2 in zip(tasks, c._tasks):
self.assertEqual(task1, task2)
# 4. Delete chore
def test_4delete_chore(self):
self.tm1.chores.delete(self.chore_name)
@classmethod
def teardown_class(cls):
cls.tm1.processes.delete(process_name1)
cls.tm1.processes.delete(process_name2)
cls.tm1.logout()
"""
Reuse session id to avoid (unreasonably slow) CAM Authentication
"""
from TM1py.Services import TM1Service
import configparser
config = configparser.ConfigParser()
config.read('..\config.ini')
# instantiate session
tm1_a = TM1Service(**config['tm1srv01'], logging=True)
# get session_id from tm1 connection
session_id = tm1_a.connection.session_id
# create a new TM1 connection (tm1_b) with same session as tm1_a
tm1_b = TM1Service(**config['tm1srv01'], session_id=session_id, logging=True)
# Do something...
print(tm1_b.server.get_server_name())
# End session
tm1_b.logout()
class TestAnnotationMethods(unittest.TestCase):
tm1 = TM1Service(address='', port=port, user=user, password=pwd, ssl=False)
# Get Random Cube + Intersection
random_intersection = tm1.cubes.get_random_intersection(cube_name, False)
random_text = "".join(
[random.choice(string.printable) for i in range(100)])
def test1_create_annotation(self):
annotation = Annotation(comment_value=self.random_text,
object_name=cube_name,
dimensional_context=self.random_intersection)
response = self.tm1.cubes.annotations.create(annotation)
annotation_id = json.loads(response)['ID']
# test, if it exists
all_annotations = self.tm1.cubes.annotations.get_all(cube_name)
if len(all_annotations) > 0:
annotation = self.tm1.cubes.annotations.get(annotation_id)
self.assertEqual(self.random_text, annotation.comment_value)
def test2_get_all_annotations_from_cube(self):
annotations = self.tm1.cubes.annotations.get_all(cube_name)
for a in annotations:
b = self.tm1.cubes.annotations.get(a.id)
self.assertEqual(a.body, b.body)
def test3_update_annotation(self):
annotations = self.tm1.cubes.annotations.get_all(cube_name)
for a in annotations:
# Get the anntoation that was created in test1
if a.dimensional_context == self.random_intersection and a.comment_value == self.random_text:
# Update Value and Coordinates
new_random_text = "".join(
[random.choice(string.printable) for _ in range(100)])
a.comment_value = new_random_text
response = self.tm1.cubes.annotations.update(a)
annotation_id = json.loads(response)['ID']
a_updated = self.tm1.cubes.annotations.get(annotation_id)
self.assertEqual(a_updated.comment_value, new_random_text)
def test4_delete_annotation(self):
# get Annotations
annotations = self.tm1.cubes.annotations.get_all(cube_name)
# sort Them
annotations = sorted(
annotations,
key=lambda a: str(a.last_updated if a.last_updated else a.created))
# First count
number_annotations_at_start = len(annotations)
last_annotation = annotations[-1]
self.tm1.cubes.annotations.delete(last_annotation._id)
# get Annotations again
annotations = self.tm1.cubes.annotations.get_all(cube_name)
# Second count
number_annotations_at_end = len(annotations)
self.assertEqual(number_annotations_at_start,
number_annotations_at_end + 1)
self.tm1.logout()
class TestViewMethods(unittest.TestCase):
tm1 = TM1Service(**config['tm1srv01'])
native_view_name = 'TM1py_Tests_Native_View'
mdx_view_name = 'TM1py_Tests_Mdx_View'
# Setup Cubes, Dimensions and Subsets
@classmethod
def setup_class(cls):
# Build Dimensions
for i in range(3):
elements = [Element('Element {}'.format(str(j)), 'Numeric') for j in range(1, 1001)]
hierarchy = Hierarchy(
dimension_name=DIMENSION_NAMES[i],
name=DIMENSION_NAMES[i],
elements=elements)
dimension = Dimension(
name=DIMENSION_NAMES[i],
hierarchies=[hierarchy])
if not cls.tm1.dimensions.exists(dimension_name=dimension.name):
cls.tm1.dimensions.create(dimension=dimension)
# Build Cube
cube = Cube(CUBE_NAME, DIMENSION_NAMES)
if not cls.tm1.cubes.exists(CUBE_NAME):
cls.tm1.cubes.create(cube)
# Write data into cube
cellset = {}
for i in range(20000):
element1 = 'Element ' + str(random.randint(1, 1000))
element2 = 'Element ' + str(random.randint(1, 1000))
element3 = 'Element ' + str(random.randint(1, 1000))
cellset[(element1, element2, element3)] = random.randint(1, 1000)
cls.tm1.data.write_values(CUBE_NAME, cellset)
def setUp(self):
for private in (True, False):
# create instance of native View
native_view = NativeView(
cube_name=CUBE_NAME,
view_name=self.native_view_name)
# Set up native view - put subsets on Rows, Columns and Titles
subset = Subset(
dimension_name=DIMENSION_NAMES[0],
hierarchy_name=DIMENSION_NAMES[0],
subset_name=SUBSET_NAME,
expression='{{[{}].Members}}'.format(DIMENSION_NAMES[0]))
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_row(
dimension_name=DIMENSION_NAMES[0],
subset=subset)
subset = AnonymousSubset(
dimension_name=DIMENSION_NAMES[1],
hierarchy_name=DIMENSION_NAMES[1],
elements=['element1', 'element123', 'element432'])
native_view.add_title(
dimension_name=DIMENSION_NAMES[1],
subset=subset,
selection='element123')
elements = ['Element{}'.format(str(i)) for i in range(1, 201)]
subset = Subset(
dimension_name=DIMENSION_NAMES[2],
hierarchy_name=DIMENSION_NAMES[2],
subset_name=SUBSET_NAME,
elements=elements)
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_column(
dimension_name=DIMENSION_NAMES[2],
subset=subset)
# Suppress Null Values
native_view.suppress_empty_cells = True
# create native view on Server
self.tm1.cubes.views.create(
view=native_view,
private=private)
# create instance of MDXView
nv_view = self.tm1.cubes.views.get_native_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
mdx = nv_view.MDX
mdx_view = MDXView(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.cubes.views.create(
view=mdx_view,
private=private)
def test_view_exists(self):
for private in (True, False):
self.assertTrue(self.tm1.cubes.views.exists(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private))
self.assertTrue(self.tm1.cubes.views.exists(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private))
exists_as_private, exists_as_public = self.tm1.cubes.views.exists(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name)
self.assertTrue(exists_as_private)
self.assertTrue(exists_as_public)
exists_as_private, exists_as_public = self.tm1.cubes.views.exists(
cube_name=CUBE_NAME,
view_name=self.native_view_name)
self.assertTrue(exists_as_private)
self.assertTrue(exists_as_public)
def test_get_all_views(self):
private_views, public_views = self.tm1.cubes.views.get_all(CUBE_NAME)
self.assertGreater(len(public_views + private_views), 0)
private_view_names, public_view_names = self.tm1.cubes.views.get_all_names(CUBE_NAME)
self.assertEqual(len(public_views), len(public_view_names))
self.assertEqual(len(private_views), len(private_view_names))
def test_get_native_view(self):
for private in (True, False):
# generic get
view = self.tm1.cubes.views.get(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
# get native view
native_view = self.tm1.cubes.views.get_native_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
self.assertIsInstance(view, NativeView)
self.assertEqual(view.name, self.native_view_name)
self.assertIsInstance(native_view, NativeView)
self.assertEqual(view, native_view)
def test_get_mdx_view(self):
for private in (True, False):
# generic get
view = self.tm1.cubes.views.get(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private)
# get mdx view
mdx_view = self.tm1.cubes.views.get_mdx_view(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private)
self.assertIsInstance(view, MDXView)
self.assertEqual(view.name, self.mdx_view_name)
self.assertIsInstance(mdx_view, MDXView)
self.assertEqual(view, mdx_view)
def test_execute_view(self):
for private in (True, False):
data_nv = self.tm1.cubes.cells.execute_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
data_mdx = self.tm1.cubes.cells.execute_view(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private)
# Sum up all the values from the views
sum_nv = sum([value['Value'] for value in data_nv.values() if value['Value']])
sum_mdx = sum([value['Value'] for value in data_mdx.values() if value['Value']])
self.assertEqual(sum_nv, sum_mdx)
# fails sometimes because PrivateMDXViews cant be updated in FP < 5.
def test_update_nativeview(self):
for private in (True, False):
# get native view
native_view_original = self.tm1.cubes.views.get_native_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
# Sum up all the values from the views
data_original = self.tm1.data.execute_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
sum_original = sum(value['Value']
for value
in data_original.values() if value['Value'])
# modify it
native_view_original.remove_row(
dimension_name=DIMENSION_NAMES[0])
subset = AnonymousSubset(
dimension_name=DIMENSION_NAMES[0],
elements=["Element 1", "Element 2", "Element 3", "Element 4", "Element 5"])
native_view_original.add_column(
dimension_name=DIMENSION_NAMES[0],
subset=subset)
# update it on Server
self.tm1.cubes.views.update(
view=native_view_original,
private=private)
# Get it and check if its different
data_updated = self.tm1.data.execute_view(
cube_name=CUBE_NAME,
view_name=self.native_view_name,
private=private)
sum_updated = sum(value['Value']
for value
in data_updated.values() if value['Value'])
self.assertNotEqual(sum_original, sum_updated)
def test_update_mdxview(self):
for private in (True, False):
# Get mdx view
mdx_view_original = self.tm1.cubes.views.get_mdx_view(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private)
# Get data from mdx view
data_mdx_original = self.tm1.data.execute_view(
cube_name=CUBE_NAME,
view_name=mdx_view_original.name,
private=private)
mdx = "SELECT " \
"NON EMPTY{{ [{}].Members }} ON 0," \
"NON EMPTY {{ [{}].Members }} ON 1 " \
"FROM [{}] " \
"WHERE ([{}].[Element172])".format(DIMENSION_NAMES[0], DIMENSION_NAMES[1],
CUBE_NAME, DIMENSION_NAMES[2])
mdx_view_original.MDX = mdx
# Update mdx view on Server
self.tm1.cubes.views.update(mdx_view_original, private=private)
# Get it and check if its different
mdx_view_updated = self.tm1.cubes.views.get_mdx_view(
cube_name=CUBE_NAME,
view_name=self.mdx_view_name,
private=private)
data_mdx_updated = self.tm1.data.execute_view(
cube_name=CUBE_NAME,
view_name=mdx_view_updated.name,
private=private)
# Sum up all the values from the views
sum_mdx_original = sum([value['Value'] for value in data_mdx_original.values() if value['Value']])
sum_mdx_updated = sum([value['Value'] for value in data_mdx_updated.values() if value['Value']])
self.assertNotEqual(sum_mdx_original, sum_mdx_updated)
def tearDown(self):
for private in (True, False):
self.tm1.cubes.views.delete(cube_name=CUBE_NAME, view_name=self.native_view_name, private=private)
self.tm1.cubes.views.delete(cube_name=CUBE_NAME, view_name=self.mdx_view_name, private=private)
@classmethod
def teardown_class(cls):
cls.tm1.cubes.delete(CUBE_NAME)
for dimension_name in DIMENSION_NAMES:
cls.tm1.dimensions.delete(dimension_name)
cls.tm1.logout()
class TestSubsetMethods(unittest.TestCase):
tm1 = TM1Service(address=address,
port=port,
user=user,
password=pwd,
ssl=ssl)
# Do random stuff
random_string = str(uuid.uuid4())
private = bool(random.getrandbits(1))
# Define Names
dimension_name = 'TM1py_unittest_dimension_' + random_string
subset_name_static = 'TM1py_unittest_static_subset_' + random_string
subset_name_dynamic = 'TM1py_unittest_dynamic_subset_' + random_string
# Instantiate Subsets
static_subset = Subset(dimension_name=dimension_name,
subset_name=subset_name_static,
elements=['USD', 'EUR', 'NZD'])
dynamic_subset = Subset(dimension_name=dimension_name,
subset_name=subset_name_dynamic,
expression='{ HIERARCHIZE( {TM1SUBSETALL( [' +
dimension_name + '] )} ) }')
# Check if Dimensions exists. If not create it
@classmethod
def setup_class(cls):
elements = [
Element('USD', 'Numeric'),
Element('EUR', 'Numeric'),
Element('JPY', 'Numeric'),
Element('CNY', 'Numeric'),
Element('GBP', 'Numeric'),
Element('NZD', 'Numeric')
]
element_attributes = [ElementAttribute('Currency Name', 'String')]
h = Hierarchy(cls.dimension_name, cls.dimension_name, elements,
element_attributes)
d = Dimension(cls.dimension_name, hierarchies=[h])
cls.tm1.dimensions.create(d)
# 1. Create subset
def test_1create_subset(self):
self.tm1.subsets.create(self.static_subset, private=self.private)
self.tm1.dimensions.hierarchies.subsets.create(self.dynamic_subset,
private=self.private)
# 2. Get subset
def test_2get_subset(self):
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_static,
private=self.private)
self.assertEqual(self.static_subset.body, s.body)
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_dynamic,
private=self.private)
self.assertEqual(self.dynamic_subset.body, s.body)
# 3. Update subset
def test_3update_subset(self):
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_static,
private=self.private)
s.add_elements(['NZD'])
# Update it
self.tm1.dimensions.hierarchies.subsets.update(s, private=self.private)
# Get it again
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_static,
private=self.private)
# Test it !
self.assertEquals(len(s.elements), 4)
# Get subset
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_dynamic,
private=self.private)
s.expression = '{{ [{}].[EUR], [{}].[USD] }})'.format(
self.dimension_name, self.dimension_name)
# Update it
self.tm1.dimensions.hierarchies.subsets.update(subset=s,
private=self.private)
# Get it again
s = self.tm1.dimensions.hierarchies.subsets.get(
dimension_name=self.dimension_name,
subset_name=self.subset_name_dynamic,
private=self.private)
# Test it !
self.assertEquals(
s.expression,
'{{ [{}].[EUR], [{}].[USD] }})'.format(self.dimension_name,
self.dimension_name))
# 4. Delete subsets
def test_4delete_subset(self):
self.tm1.dimensions.hierarchies.subsets.delete(
dimension_name=self.dimension_name,
subset_name=self.subset_name_static,
private=self.private)
self.tm1.dimensions.hierarchies.subsets.delete(
dimension_name=self.dimension_name,
subset_name=self.subset_name_dynamic,
private=self.private)
@classmethod
def teardown_class(cls):
cls.tm1.dimensions.delete(cls.dimension_name)
cls.tm1.logout()
def setUpClass(cls):
# Namings
cls.expand_process_name = str(uuid.uuid4())
cls.expand_process_name_obf = str(uuid.uuid4())
cls.process_name = str(uuid.uuid4())
cls.process_name_obf = str(uuid.uuid4())
cls.dimension_name = str(uuid.uuid4())
cls.dimension_name_cloned = str(uuid.uuid4())
cls.cube_name = str(uuid.uuid4())
cls.cube_name_cloned = str(uuid.uuid4())
# Connect to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
# create process
prolog = "\r\nSaveDataAll;\r\nsText='abcABC';\r\n"
epilog = "SaveDataAll;"
cls.process = Process(name=cls.process_name,
prolog_procedure=prolog,
epilog_procedure=epilog)
# create process with expand in TM1
if cls.tm1.processes.exists(cls.process.name):
cls.tm1.processes.delete(cls.process.name)
cls.tm1.processes.create(cls.process)
# create process with expand
prolog = "\r\nnRevenue = 20;\r\nsRevenue = EXPAND('%nrevenue%');\r\nIF(sRevenue @ <> '20.000');\r\n" \
"ProcessBreak;\r\nENDIF;"
cls.expand_process = Process(name=cls.expand_process_name,
prolog_procedure=prolog)
# create process with expand in TM1
if cls.tm1.processes.exists(cls.expand_process.name):
cls.tm1.processes.delete(cls.expand_process.name)
cls.tm1.processes.create(cls.expand_process)
# create dimension that we clone through obfuscated bedrock as part of the test
if not cls.tm1.dimensions.exists(cls.dimension_name):
d = Dimension(cls.dimension_name)
h = Hierarchy(cls.dimension_name, cls.dimension_name)
h.add_element('Total Years', 'Consolidated')
h.add_element('No Year', 'Numeric')
for year in range(1989, 2040, 1):
h.add_element(str(year), 'Numeric')
h.add_edge('Total Years', str(year), 1)
d.add_hierarchy(h)
cls.tm1.dimensions.create(d)
# Create 2 Attributes through TI
ti_statements = [
"AttrInsert('{}','','Previous Year', 'S')".format(
cls.dimension_name),
"AttrInsert('{}','','Next Year', 'S');".format(
cls.dimension_name)
]
ti = ';'.join(ti_statements)
cls.tm1.processes.execute_ti_code(lines_prolog=ti)
# create }ElementAttribute values
cellset = {}
for year in range(1989, 2040, 1):
cellset[(str(year), 'Previous Year')] = year - 1
cellset[(str(year), 'Next Year')] = year + 1
cls.tm1.cubes.cells.write_values(
"}ElementAttributes_" + cls.dimension_name, cellset)
# create a simple cube to be cloned through bedrock
if not cls.tm1.cubes.exists(cls.cube_name):
cube = Cube(cls.cube_name, ["}Dimensions", "}Cubes"],
"[]=S:'TM1py';")
cls.tm1.cubes.create(cube)
# create bedrocks if they doesn't exist
for bedrock in ("Bedrock.Dim.Clone", "Bedrock.Cube.Clone"):
if not cls.tm1.processes.exists(bedrock):
with open(
Path(__file__).parent.joinpath("resources",
bedrock + ".json"),
"r") as file:
process = Process.from_json(file.read())
cls.tm1.processes.create(process)
"""
Persist TM1Service in a file.
Helps to avoid (unreasonably slow) CAM Authentication
"""
import time
from TM1py.Services import TM1Service
import configparser
config = configparser.ConfigParser()
config.read(r'..\config.ini')
# Connect to TM1
tm1 = TM1Service(**config['tm1srv01'])
print(tm1.server.get_server_name())
# Save TM1Service instance to file
tm1.save_to_file('tm1_connection')
# Wait...
time.sleep(7)
# Restore TM1Service instance from file
tm1 = TM1Service.restore_from_file('tm1_connection')
print(tm1.server.get_server_name())
# Logout
tm1.logout()
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
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'])
# generate random coordinates
cls.target_coordinates = list(
zip(('Element ' + str(e) for e in range(1, 100)),
('Element ' + str(e) for e in range(1, 100)),
('Element ' + str(e) for e in range(1, 100))))
# 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)
attribute_cube = "}ElementAttributes_" + dimension_name
attribute_values = dict()
for element in elements:
attribute_values[(element.name, "Attr1")] = "TM1py"
attribute_values[(element.name, "Attr2")] = "2"
attribute_values[(element.name, "Attr3")] = "3"
cls.tm1.cubes.cells.write_values(attribute_cube, attribute_values)
# Build Cube
cube = Cube(CUBE_NAME, DIMENSION_NAMES)
if not cls.tm1.cubes.exists(CUBE_NAME):
cls.tm1.cubes.create(cube)
# Sum of all the values that we write in the cube. serves as a checksum.
cls.total_value = 0
# cellset of data that shall be written
cls.cellset = {}
for element1, element2, element3 in cls.target_coordinates:
value = 1
cls.cellset[(element1, element2, element3)] = value
# update the checksum
cls.total_value += value
# Fill cube with values
cls.tm1.cubes.cells.write_values(CUBE_NAME, cls.cellset)
# Elements
cls.years = ("No Year", "1989", "1990", "1991", "1992")
cls.extra_year = "4321"
# Element Attributes
cls.attributes = ('Previous Year', 'Next Year')
cls.alias_attributes = ("Financial Year", )
# create dimension with a default hierarchy
d = Dimension(DIMENSION_NAME)
h = Hierarchy(DIMENSION_NAME, DIMENSION_NAME)
h.add_element('Total Years', 'Consolidated')
h.add_element('All Consolidations', 'Consolidated')
h.add_edge("All Consolidations", "Total Years", 1)
for year in cls.years:
h.add_element(year, 'Numeric')
h.add_edge('Total Years', year, 1)
for attribute in cls.attributes:
h.add_element_attribute(attribute, "String")
for attribute in cls.alias_attributes:
h.add_element_attribute(attribute, "Alias")
d.add_hierarchy(h)
cls.tm1.dimensions.update_or_create(d)
# write attribute values
cls.tm1.cubes.cells.write_value('1988',
'}ElementAttributes_' + DIMENSION_NAME,
('1989', 'Previous Year'))
cls.tm1.cubes.cells.write_value('1989',
'}ElementAttributes_' + DIMENSION_NAME,
('1990', 'Previous Year'))
cls.tm1.cubes.cells.write_value('1990',
'}ElementAttributes_' + DIMENSION_NAME,
('1991', 'Previous Year'))
cls.tm1.cubes.cells.write_value('1991',
'}ElementAttributes_' + DIMENSION_NAME,
('1992', 'Previous Year'))
cls.tm1.cubes.cells.write_value('1988/89',
'}ElementAttributes_' + DIMENSION_NAME,
('1989', 'Financial Year'))
cls.tm1.cubes.cells.write_value('1989/90',
'}ElementAttributes_' + DIMENSION_NAME,
('1990', 'Financial Year'))
cls.tm1.cubes.cells.write_value('1990/91',
'}ElementAttributes_' + DIMENSION_NAME,
('1991', 'Financial Year'))
cls.tm1.cubes.cells.write_value('1991/92',
'}ElementAttributes_' + DIMENSION_NAME,
('1992', 'Financial Year'))
def setUpClass(cls):
cls.tm1 = TM1Service(**config['tm1srv01'])
cls.random_string = str(uuid.uuid4())
cls.all_dimension_names = cls.tm1.dimensions.get_all_names()
cls.random_dimension = cls.tm1.dimensions.get(
random.choice(cls.all_dimension_names))
cls.random_dimension_all_elements = cls.random_dimension.default_hierarchy.elements
cls.random_dimension_elements = [
element for element in cls.random_dimension_all_elements
][0:2]
# None process
cls.p_none = Process(name=process_prefix + '_none_' +
cls.random_string,
datasource_type='None')
# ACII process
cls.p_ascii = Process(
name=process_prefix + '_ascii_' + cls.random_string,
datasource_type='ASCII',
datasource_ascii_delimiter_type='Character',
datasource_ascii_delimiter_char=',',
datasource_ascii_header_records=2,
datasource_ascii_quote_character='^',
datasource_ascii_thousand_separator='~',
prolog_procedure="sTestProlog = 'test prolog procedure'",
metadata_procedure="sTestMeta = 'test metadata procedure'",
data_procedure="sTestData = 'test data procedure'",
epilog_procedure="sTestEpilog = 'test epilog procedure'",
datasource_data_source_name_for_server=r'C:\Data\file.csv',
datasource_data_source_name_for_client=r'C:\Data\file.csv')
# Variables
cls.p_ascii.add_variable('v_1', 'Numeric')
cls.p_ascii.add_variable('v_2', 'Numeric')
cls.p_ascii.add_variable('v_3', 'Numeric')
cls.p_ascii.add_variable('v_4', 'Numeric')
# Parameters
cls.p_ascii.add_parameter('p_Year', 'year?', '2016')
cls.p_ascii.add_parameter('p_Number', 'number?', 2)
# View process
cls.p_view = Process(
name=process_prefix + '_view_' + cls.random_string,
datasource_type='TM1CubeView',
datasource_view='view1',
datasource_data_source_name_for_client='Plan_BudgetPlan',
datasource_data_source_name_for_server='Plan_BudgetPlan')
# ODBC process
cls.p_odbc = Process(name=process_prefix + '_odbc_' +
cls.random_string,
datasource_type='ODBC',
datasource_password='******',
datasource_user_name='user')
# Subset process
cls.subset_name = process_prefix + '_subset_' + cls.random_string
cls.subset = Subset(dimension_name=cls.random_dimension.name,
subset_name=cls.subset_name,
elements=cls.random_dimension_elements)
cls.tm1.dimensions.subsets.create(cls.subset, False)
cls.p_subset = Process(
name=process_prefix + '_subset_' + cls.random_string,
datasource_type='TM1DimensionSubset',
datasource_data_source_name_for_server=cls.subset.dimension_name,
datasource_subset=cls.subset.name,
metadata_procedure="sTest = 'abc';")
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'])
cls.some_name = "some_name"
cls.all_dimension_names = cls.tm1.dimensions.get_all_names()
cls.random_dimension = cls.tm1.dimensions.get(
random.choice(cls.all_dimension_names))
cls.random_dimension_all_elements = cls.random_dimension.default_hierarchy.elements
cls.random_dimension_elements = [
element for element in cls.random_dimension_all_elements
][0:2]
# None process
cls.p_none = Process(name=PROCESS_PREFIX + '_none_' + cls.some_name,
datasource_type='None')
# ACII process
cls.p_ascii = Process(
name=PROCESS_PREFIX + '_ascii_' + cls.some_name,
datasource_type='ASCII',
datasource_ascii_delimiter_type='Character',
datasource_ascii_delimiter_char=',',
datasource_ascii_header_records=2,
datasource_ascii_quote_character='^',
datasource_ascii_thousand_separator='~',
prolog_procedure="sTestProlog = 'test prolog procedure'",
metadata_procedure="sTestMeta = 'test metadata procedure'",
data_procedure="sTestData = 'test data procedure'",
epilog_procedure="sTestEpilog = 'test epilog procedure'",
datasource_data_source_name_for_server=r'C:\Data\file.csv',
datasource_data_source_name_for_client=r'C:\Data\file.csv')
# Variables
cls.p_ascii.add_variable('v_1', 'Numeric')
cls.p_ascii.add_variable('v_2', 'Numeric')
cls.p_ascii.add_variable('v_3', 'Numeric')
cls.p_ascii.add_variable('v_4', 'Numeric')
# Parameters
cls.p_ascii.add_parameter('p_Year', 'year?', '2016')
cls.p_ascii.add_parameter('p_Number', 'number?', 2)
# View process
cls.p_view = Process(
name=PROCESS_PREFIX + '_view_' + cls.some_name,
datasource_type='TM1CubeView',
datasource_view='view1',
datasource_data_source_name_for_client='Plan_BudgetPlan',
datasource_data_source_name_for_server='Plan_BudgetPlan')
# ODBC process
cls.p_odbc = Process(name=PROCESS_PREFIX + '_odbc_' + cls.some_name,
datasource_type='ODBC',
datasource_password='******',
datasource_user_name='user')
# Subset process
cls.subset_name = PROCESS_PREFIX + '_subset_' + cls.some_name
cls.subset = Subset(dimension_name=cls.random_dimension.name,
subset_name=cls.subset_name,
elements=cls.random_dimension_elements)
cls.tm1.dimensions.subsets.create(cls.subset, False)
cls.p_subset = Process(
name=PROCESS_PREFIX + '_subset_' + cls.some_name,
datasource_type='TM1DimensionSubset',
datasource_data_source_name_for_server=cls.subset.dimension_name,
datasource_subset=cls.subset.name,
metadata_procedure="sTest = 'abc';")
with open(
Path(__file__).parent.joinpath('resources',
'Bedrock.Server.Wait.json'),
'r') as file:
cls.p_bedrock_server_wait = Process.from_json(file.read())
Assumption: Metadata (Cube and Dimensions) are in sync.
"""
import configparser
from TM1py.Services import TM1Service
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')
# Setup Connections
tm1_source = TM1Service(**config['tm1srv01'])
tm1_target = TM1Service(**config['tm1srv02'])
# Query data from source TM1 model through MDX
mdx = "SELECT " \
"{[plan_chart_of_accounts].[41101],[plan_chart_of_accounts].[42201]} on ROWS, " \
"{[plan_time].[Oct-2004],[plan_time].[Nov-2004],[plan_time].[Dec-2004]} on COLUMNS " \
"FROM [Plan_BudgetPlan] " \
"WHERE " \
"([plan_version].[FY 2004 Budget],[plan_business_unit].[10110],[plan_department].[410]," \
"[plan_exchange_rates].[local],[plan_source].[input])"
data = tm1_source.data.execute_mdx(mdx)
# Rearrange data
values = [cell['Value'] for cell in data.values()]
# Send data to target TM1 instance
tm1_target.data.write_values_through_cellset(mdx, values)
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
# generate random coordinates
cls.target_coordinates = list(
zip(('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100)),
('Element ' + str(random.randint(1, 1000))
for _ in range(100))))
# 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 not cls.tm1.dimensions.exists(dimension.name):
cls.tm1.dimensions.create(dimension)
attribute_cube = "}ElementAttributes_" + dimension_name
attribute_values = dict()
for element in elements:
attribute_values[(element.name, "Attr1")] = "TM1py"
attribute_values[(element.name, "Attr2")] = "2"
attribute_values[(element.name, "Attr3")] = "3"
cls.tm1.cubes.cells.write_values(attribute_cube, attribute_values)
# 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)
subset = AnonymousSubset(dimension_name=dimension_names[0],
expression='{[' + dimension_names[0] +
'].Members}')
view.add_row(dimension_name=dimension_names[0], subset=subset)
subset = AnonymousSubset(dimension_name=dimension_names[1],
expression='{[' + dimension_names[1] +
'].Members}')
view.add_row(dimension_name=dimension_names[1], subset=subset)
subset = AnonymousSubset(dimension_name=dimension_names[2],
expression='{[' + dimension_names[2] +
'].Members}')
view.add_column(dimension_name=dimension_names[2], subset=subset)
cls.tm1.cubes.views.create(view, private=False)
# Sum of all the values that we write in the cube. serves as a checksum
cls.total_value = 0
# cellset of data that shall be written
cls.cellset = {}
for element1, element2, element3 in cls.target_coordinates:
value = random.randint(1, 1000)
cls.cellset[(element1, element2, element3)] = value
# update the checksum
cls.total_value += value
"""
Get private and public Subsets from TM1
"""
from TM1py.Services import TM1Service
with TM1Service(address='localhost', port=12354, user='******', password='******', ssl=True) as tm1:
private_subsets = tm1.dimensions.subsets.get_all_names(dimension_name='plan_department',
hierarchy_name='plan_department',
private=True)
print('private subsets: ')
for subset_name in private_subsets:
subset = tm1.dimensions.subsets.get(dimension_name='plan_department',
subset_name=subset_name,
private=True)
print(subset.name)
public_subsets = tm1.dimensions.subsets.get_all_names(dimension_name='plan_department',
hierarchy_name='plan_department',
private=False)
print('public subsets: ')
for subset_name in public_subsets:
subset = tm1.dimensions.subsets.get(dimension_name='plan_department',
subset_name=subset_name,
private=False)
print(subset.name)
#print("Lat Value: " + str(v["lat"]) )
valuelat = str(v["lat"])
coordinates = ("Admin", "latitude", "String")
cells[coordinates] = valuelat
elif k == city:
print("City Value: " + str(v))
valuelcity = str(v)
coordinates = ("Admin", "city", "String")
cells[coordinates] = valuelcity
elif k == syst:
valuelstate = str(v["country"])
coordinates = ("Admin", "location", "String")
cells[coordinates] = valuelstate
tm1.cubes.cells.write_values("User Weather", cells)
if __name__ == "__main__":
with TM1Service(address=address,
port=port,
user="******",
password=password,
namespace=namespace,
ssl=ssl) as tm1:
server_name = tm1.server.get_server_name()
main(tm1)
#print(unused_dimensions)
#print("Connection to TM1 established!! your Servername is: {}".format(server_name))
class TestAnnotationMethods(unittest.TestCase):
tm1 = TM1Service(**config['tm1srv01'])
# create cube
cube_name = "TM1py_tests_annotations"
dimension_names = ("TM1py_tests_annotations_dimension1",
"TM1py_tests_annotations_dimension2",
"TM1py_tests_annotations_dimension3")
@classmethod
def setup_class(cls):
# Connection to TM1
cls.tm1 = TM1Service(**config['tm1srv01'])
# Build Dimensions
for dimension_name in cls.dimension_names:
elements = [
Element('Element {}'.format(str(j)), 'Numeric')
for j in range(1, 1001)
]
hierarchy = Hierarchy(dimension_name=dimension_name,
name=dimension_name,
elements=elements)
dimension = Dimension(dimension_name, [hierarchy])
if not cls.tm1.dimensions.exists(dimension.name):
cls.tm1.dimensions.create(dimension)
# Build Cube
cube = Cube(cls.cube_name, cls.dimension_names)
if not cls.tm1.cubes.exists(cls.cube_name):
cls.tm1.cubes.create(cube)
cls.create_annotation()
@classmethod
def create_annotation(cls):
# create annotations
random_intersection = cls.tm1.cubes.get_random_intersection(
cls.cube_name, False)
random_text = "".join(
[random.choice(string.printable) for _ in range(100)])
annotation = Annotation(comment_value=random_text,
object_name=cls.cube_name,
dimensional_context=random_intersection)
response = cls.tm1.cubes.annotations.create(annotation)
annotation_id = response.json()['ID']
return annotation_id
@classmethod
def delete_latest_annotation(cls):
# get Annotations
annotations = cls.tm1.cubes.annotations.get_all(cls.cube_name)
# sort Them
annotations = sorted(
annotations,
key=lambda a: str(a.last_updated if a.last_updated else a.created))
cls.tm1.cubes.annotations.delete(annotations[-1].id)
def test_create_and_delete_annotation(self):
# create a random annotation
annotation_id = self.create_annotation()
# count existing annotation
annotations = self.tm1.cubes.annotations.get_all(self.cube_name)
number_annotations_at_start = len(annotations)
# delete
self.tm1.cubes.annotations.delete(annotation_id)
# count existing annotations
annotations = self.tm1.cubes.annotations.get_all(self.cube_name)
number_annotations_at_end = len(annotations)
# assert
self.assertEqual(number_annotations_at_start,
number_annotations_at_end + 1)
def test_get_all_annotations_from_cube(self):
annotations = self.tm1.cubes.annotations.get_all(self.cube_name)
self.assertGreater(len(annotations), 0)
for a in annotations:
b = self.tm1.cubes.annotations.get(a.id)
self.assertEqual(a.body, b.body)
def test_update_annotation(self):
annotations = self.tm1.cubes.annotations.get_all(self.cube_name)
annotation = annotations[-1]
# Update Value
new_random_text = "".join(
[random.choice(string.printable) for _ in range(100)])
annotation.comment_value = new_random_text
response = self.tm1.cubes.annotations.update(annotation)
annotation_id = json.loads(response.text)['ID']
a_updated = self.tm1.cubes.annotations.get(annotation_id)
self.assertEqual(a_updated.comment_value, new_random_text)
@classmethod
def tearDownClass(cls):
cls.tm1.cubes.delete(cube_name=cls.cube_name)
for dimension_name in cls.dimension_names:
cls.tm1.dimensions.delete(dimension_name=dimension_name)
cls.tm1.logout()
class TestViewMethods(unittest.TestCase):
tm1 = TM1Service(**test_config)
random_string = str(uuid.uuid4())
# Fails when random boolean is True. Private NativeViews cant be updated with TM1 10.2.2 FP 6
random_boolean = bool(random.getrandbits(1)) \
if int(tm1.server.get_product_version().split('.')[2]) > 20602 \
else False
native_view_name = 'TM1py_unittest_native_view_' + random_string
mdx_view_name = 'TM1py_unittest_mdx_view_' + random_string
# Setup Cubes, Dimensions and Subsets
@classmethod
def setup_class(cls):
# Build Dimensions
for i in range(3):
elements = [
Element('Element {}'.format(str(j)), 'Numeric')
for j in range(1, 1001)
]
hierarchy = Hierarchy(dimension_names[i], dimension_names[i],
elements)
dimension = Dimension(dimension_names[i], [hierarchy])
if not cls.tm1.dimensions.exists(dimension.name):
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)
# Write data into cube
cellset = {}
for i in range(20000):
element1 = 'Element ' + str(random.randint(1, 1000))
element2 = 'Element ' + str(random.randint(1, 1000))
element3 = 'Element ' + str(random.randint(1, 1000))
cellset[(element1, element2, element3)] = random.randint(1, 1000)
cls.tm1.data.write_values(cube_name, cellset)
def test1_create_view(self):
# create instance of native View
native_view = NativeView(cube_name=cube_name,
view_name=self.native_view_name)
# Set up native view - put subsets on Rows, Columns and Titles
subset = Subset(dimension_name=dimension_names[0],
hierarchy_name=dimension_names[0],
subset_name=str(uuid.uuid4()),
expression='{{[{}].Members}}'.format(
dimension_names[0]))
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_row(dimension_name=dimension_names[0], subset=subset)
subset = AnonymousSubset(
dimension_name=dimension_names[1],
hierarchy_name=dimension_names[1],
elements=['element1', 'element123', 'element432'])
native_view.add_title(dimension_name=dimension_names[1],
subset=subset,
selection='element123')
elements = ['Element{}'.format(str(i)) for i in range(1, 201)]
subset = Subset(dimension_name=dimension_names[2],
hierarchy_name=dimension_names[2],
subset_name=str(uuid.uuid4()),
elements=elements)
self.tm1.dimensions.subsets.create(subset, private=False)
native_view.add_column(dimension_name=dimension_names[2],
subset=subset)
# Suppress Null Values
native_view.suppress_empty_cells = True
# create native view on Server
self.tm1.cubes.views.create(view=native_view,
private=self.random_boolean)
# create instance of MDXView
nv_view = self.tm1.cubes.views.get_native_view(
cube_name=cube_name,
view_name=self.native_view_name,
private=self.random_boolean)
mdx = nv_view.MDX
mdx_view = MDXView(cube_name=cube_name,
view_name=self.mdx_view_name,
MDX=mdx)
# create mdx view on Server
self.tm1.cubes.views.create(view=mdx_view, private=self.random_boolean)
def test2_get_all_views(self):
private_views, public_views = self.tm1.cubes.views.get_all(cube_name)
self.assertGreater(len(public_views), 0)
private_view_names, public_view_names = self.tm1.cubes.views.get_all_names(
cube_name)
self.assertEqual(len(public_views), len(public_view_names))
self.assertEqual(len(private_views), len(private_view_names))
def test3_get_view(self):
# get native view
native_view = self.tm1.cubes.views.get_native_view(
cube_name=cube_name,
view_name=self.native_view_name,
private=self.random_boolean)
# check if instance
self.assertIsInstance(native_view, NativeView)
# get mdx view
mdx_view = self.tm1.cubes.views.get_mdx_view(
cube_name=cube_name,
view_name=self.mdx_view_name,
private=self.random_boolean)
# check if instance
self.assertIsInstance(mdx_view, MDXView)
def test4_compare_data(self):
data_nv = self.tm1.data.get_view_content(cube_name,
self.native_view_name,
private=self.random_boolean)
data_mdx = self.tm1.data.get_view_content(cube_name,
self.mdx_view_name,
private=self.random_boolean)
# Sum up all the values from the views
sum_nv = sum(
[value['Value'] for value in data_nv.values() if value['Value']])
sum_mdx = sum(
[value['Value'] for value in data_mdx.values() if value['Value']])
self.assertEqual(sum_nv, sum_mdx)
# fails sometimes because PrivateMDXViews cant be updated in FP < 5.
def test5_update_nativeview(self):
# get native view
native_view_original = self.tm1.cubes.views.get_native_view(
cube_name=cube_name,
view_name=self.native_view_name,
private=self.random_boolean)
# Sum up all the values from the views
data_original = self.tm1.data.execute_view(cube_name,
self.native_view_name,
private=self.random_boolean)
sum_original = sum([
value['Value'] for value in data_original.values()
if value['Value']
])
# modify it
native_view_original.remove_row(dimension_name=dimension_names[0])
subset = AnonymousSubset(dimension_name=dimension_names[0],
elements=[
"Element 1", "Element 2", "Element 3",
"Element 4", "Element 5"
])
native_view_original.add_column(dimension_name=dimension_names[0],
subset=subset)
# update it on Server
self.tm1.cubes.views.update(native_view_original,
private=self.random_boolean)
# Get it and check if its different
data_updated = self.tm1.data.execute_view(cube_name,
self.native_view_name,
private=self.random_boolean)
sum_updated = sum([
value['Value'] for value in data_updated.values() if value['Value']
])
self.assertNotEqual(sum_original, sum_updated)
def test6_update_mdxview(self):
# Get mdx view
mdx_view_original = self.tm1.cubes.views.get_mdx_view(
cube_name=cube_name,
view_name=self.mdx_view_name,
private=self.random_boolean)
# Get data from mdx view
data_mdx_original = self.tm1.data.get_view_content(
cube_name=cube_name,
view_name=mdx_view_original.name,
private=self.random_boolean)
mdx = "SELECT " \
"NON EMPTY{{ [{}].Members }} ON 0," \
"NON EMPTY {{ [{}].Members }} ON 1 " \
"FROM [{}] " \
"WHERE ([{}].[Element172])".format(dimension_names[0], dimension_names[1], cube_name, dimension_names[2])
mdx_view_original.MDX = mdx
# Update mdx view on Server
self.tm1.cubes.views.update(mdx_view_original,
private=self.random_boolean)
# Get it and check if its different
mdx_view_updated = self.tm1.cubes.views.get_mdx_view(
cube_name=cube_name,
view_name=self.mdx_view_name,
private=self.random_boolean)
data_mdx_updated = self.tm1.data.get_view_content(
cube_name, mdx_view_updated.name, private=self.random_boolean)
# Sum up all the values from the views
sum_mdx_original = sum([
value['Value'] for value in data_mdx_original.values()
if value['Value']
])
sum_mdx_updated = sum([
value['Value'] for value in data_mdx_updated.values()
if value['Value']
])
self.assertNotEqual(sum_mdx_original, sum_mdx_updated)
def test7_delete_view(self):
self.tm1.cubes.views.delete(cube_name=cube_name,
view_name=self.native_view_name,
private=self.random_boolean)
self.tm1.cubes.views.delete(cube_name=cube_name,
view_name=self.mdx_view_name,
private=self.random_boolean)
@classmethod
def teardown_class(cls):
cls.tm1.cubes.delete(cube_name)
for dimension_name in dimension_names:
cls.tm1.dimensions.delete(dimension_name)
cls.tm1.logout()
