"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()