Tm1py Expert

TM1py Expert

This skill provides expert guidance for working with TM1py, the Python package that wraps the IBM Planning Analytics (TM1) REST API. TM1py enables programmatic interaction with TM1 servers for data operations, metadata management, and automation.

Prerequisites

Required

• Python 3.7 or higher
• TM1py package: pip install tm1py or pip install "tm1py[pandas]" (recommended)
• Access to a TM1/Planning Analytics server (v11 or higher)
• Valid TM1 credentials (username and password)

Recommended (TM1py 2.1+)

• TM1py 2.1 or higher for advanced features:
  • Auto-reconnect on network failures
  • Hybrid sync/async request modes
  • Built-in retry logic for resilience

Optional

• pandas library (for DataFrame operations)
• networkx library (for hierarchy analysis)

Core Concepts

TM1Service - The Main Entry Point

All TM1py functionality is accessed through the TM1Service class, which establishes and manages your connection to TM1:

from TM1py import TM1Service

# Use context manager (recommended - handles cleanup automatically)
with TM1Service(address='localhost', port=8001, user='admin', password='apple', ssl=True) as tm1:
    # Your TM1 operations here
    print(tm1.server.get_product_version())

Service Architecture

TM1py organizes functionality into specialized services accessible via tm1.<service>:

• tm1.cells: Read/write cube data
• tm1.dimensions: Manage dimensions
• tm1.hierarchies: Manage hierarchies
• tm1.elements: Manage elements and attributes
• tm1.cubes: Manage cubes
• tm1.processes: Execute and manage TI processes
• tm1.chores: Manage chores
• tm1.subsets: Manage subsets
• tm1.views: Manage cube views
• tm1.sandboxes: Manage sandboxes
• tm1.security: Manage users and groups
• tm1.server: Server information and operations
• tm1.monitoring: Monitor threads and sessions

See references/API_REFERENCE.md for comprehensive service documentation.

Step-by-Step Instructions

Task 1: Connect to TM1

TM1 11 On-Premise

from TM1py import TM1Service

with TM1Service(
    address='localhost',
    port=8001,
    user='admin',
    password='apple',
    ssl=True,
    verify=False  # Set to True to verify SSL certificate
) as tm1:
    print(f"Connected to {tm1.server.get_server_name()}")

TM1 11 IBM Cloud

with TM1Service(
    base_url='https://mycompany.planning-analytics.ibmcloud.com/tm1/api/tm1/',
    user='non_interactive_user',
    namespace='LDAP',
    password='your_password',
    ssl=True,
    verify=True,
    async_requests_mode=True
) as tm1:
    print("Connected to IBM Cloud")

TM1 12 PAaaS

params = {
    "base_url": "https://us-east-1.planninganalytics.saas.ibm.com/api/<TenantId>/v0/tm1/<DatabaseName>/",
    "user": "apikey",
    "password": "<TheActualApiKey>",
    "async_requests_mode": True,
    "ssl": True,
    "verify": True
}

with TM1Service(**params) as tm1:
    print("Connected to PAaaS")

See CONNECTION_GUIDE.md for all connection patterns including TM1 12 on-premise and Cloud Pak for Data.

Task 2: Read Data from Cubes

Execute MDX Query

# Simple MDX query
mdx = """
SELECT
  {[Product].[Product].[ProductA], [Product].[Product].[ProductB]} ON ROWS,
  {[Period].[Period].[2024-Q1], [Period].[Period].[2024-Q2]} ON COLUMNS
FROM [SalesCube]
WHERE ([Measure].[Revenue])
"""

# Get data as dictionary
data_dict = tm1.cells.execute_mdx(mdx)
for coordinates, cell_properties in data_dict.items():
    print(f"{coordinates}: {cell_properties['Value']}")

# Get data as pandas DataFrame (requires pandas)
df = tm1.cells.execute_mdx_dataframe(mdx)
print(df)

Read from Cube View

# Read from existing view
df = tm1.cells.execute_view_dataframe(
    cube_name='SalesCube',
    view_name='DefaultView',
    private=False
)
print(df)

# Read with performance optimizations
df = tm1.cells.execute_view_dataframe(
    cube_name='SalesCube',
    view_name='LargeView',
    private=False,
    use_blob=True,  # Faster for large datasets (requires admin)
    skip_zeros=True,  # Exclude zero values
    skip_consolidated_cells=True  # Exclude C-level cells
)

See DATA_OPERATIONS.md for advanced reading patterns including blob operations and performance optimizations.

Task 3: Write Data to Cubes

Write Cell Values

# Write a single value
tm1.cells.write_value(
    value=12345.67,
    cube_name='SalesCube',
    element_tuple=('ProductA', '2024-Q1', 'Revenue')
)

# Write multiple values
cellset = {
    ('ProductA', '2024-Q1', 'Revenue'): 12345,
    ('ProductB', '2024-Q1', 'Revenue'): 23456,
    ('ProductA', '2024-Q2', 'Revenue'): 34567
}

tm1.cells.write(
    cube_name='SalesCube',
    cellset_as_dict=cellset,
    dimensions=['Product', 'Period', 'Measure']  # Optional but improves performance
)

Write from DataFrame

import pandas as pd

# DataFrame with cube structure
df = pd.DataFrame({
    'Product': ['ProductA', 'ProductB', 'ProductA'],
    'Period': ['2024-Q1', '2024-Q1', '2024-Q2'],
    'Measure': ['Revenue', 'Revenue', 'Revenue'],
    'Value': [12345, 23456, 34567]
})

tm1.cells.write_dataframe(
    cube_name='SalesCube',
    data=df
)

High-Performance Writes

# Use TI process for bulk writes (requires admin permissions)
tm1.cells.write(
    cube_name='SalesCube',
    cellset_as_dict=large_cellset,
    use_ti=True  # Or use_blob=True for even better performance
)

# Async write for parallel processing
tm1.cells.write_async(
    cube_name='SalesCube',
    cells=very_large_cellset,
    max_workers=8,  # Number of parallel threads
    slice_size=250000  # Cells per thread
)

See PERFORMANCE.md for optimization strategies including async operations and bulk transfer techniques.

Task 4: Manage Dimensions and Hierarchies

Get Dimension Information

# Check if dimension exists
if tm1.dimensions.exists('Product'):
    print("Product dimension exists")

# Get dimension object
dim = tm1.dimensions.get('Product')
print(f"Dimension: {dim.name}")
print(f"Hierarchies: {dim.hierarchy_names}")

# Get all dimension names
all_dims = tm1.dimensions.get_all_names()

Work with Elements

# Get all elements
elements = tm1.elements.get_element_names(
    dimension_name='Product',
    hierarchy_name='Product'
)

# Get elements as DataFrame
df = tm1.elements.get_elements_dataframe(
    dimension_name='Product',
    hierarchy_name='Product',
    skip_consolidations=False,
    attributes=['Description', 'Category']  # Include attributes
)

# Create a new element
from TM1py import Element

element = Element(name='ProductC', element_type='Numeric')
tm1.elements.create(
    dimension_name='Product',
    hierarchy_name='Product',
    element=element
)

# Add edge (parent-child relationship)
tm1.elements.add_edge(
    dimension_name='Product',
    hierarchy_name='Product',
    parent='All Products',
    component='ProductC',
    weight=1
)

See METADATA_MANAGEMENT.md for comprehensive CRUD operations and METADATA_MANAGEMENT_ADVANCED.md for advanced patterns.

Task 5: Execute TI Processes

Execute Process

# Execute process without parameters
success, status, error_log = tm1.processes.execute_with_return(
    process_name='RefreshData'
)

if success:
    print("Process completed successfully")
else:
    print(f"Process failed: {error_log}")

# Execute with parameters
success, status, error_log = tm1.processes.execute_with_return(
    process_name='LoadData',
    pYear='2024',
    pMonth='01'
)

Execute Loose TI Code

# Execute TI statements directly
prolog = [
    "sYear = '2024';",
    "sMessage = 'Processing year: ' | sYear;",
    "TextOutput('TM1ProcessError.log', sMessage);"
]

epilog = [
    "TextOutput('TM1ProcessError.log', 'Process completed');"
]

tm1.processes.execute_ti_code(lines_prolog=prolog, lines_epilog=epilog)

Task 6: Work with Cubes

Get Cube Information

# Get cube
cube = tm1.cubes.get('SalesCube')
print(f"Dimensions: {cube.dimensions}")
print(f"Has Rules: {cube.has_rules}")

# Get all cube names
all_cubes = tm1.cubes.get_all_names(skip_control_cubes=True)

Create a Cube

from TM1py import Cube

# Create new cube
cube = Cube(
    name='NewSalesCube',
    dimensions=['Product', 'Period', 'Measure', 'Version']
)

tm1.cubes.create(cube)

Examples

Example 1: Export Cube Data to CSV

# Read data and export to CSV
df = tm1.cells.execute_view_dataframe(
    cube_name='SalesCube',
    view_name='ExportView',
    private=False,
    skip_zeros=True
)

df.to_csv('sales_data.csv', index=False)
print(f"Exported {len(df)} rows to sales_data.csv")

Example 2: Bulk Update with Error Handling

# Safe bulk write with validation
try:
    success, status, error_log = tm1.processes.execute_with_return(
        process_name='LoadSalesData',
        pYear='2024',
        pRegion='EMEA'
    )
    
    if success:
        print(f"Process completed: {status}")
    else:
        print(f"Process failed, check: {error_log}")
        
except Exception as e:
    print(f"Error executing process: {str(e)}")

See EXAMPLES.md for real-world patterns including dimension sync, data loading, reporting automation, and multi-environment workflows.

Troubleshooting

Connection Failed

• Check: Server address, port, SSL settings (ssl=True/False), credentials
• IBM Cloud: Use async_requests_mode=True to avoid 60s timeout
• SSL Issues: Set verify=False for self-signed certificates (not production)

MDX Query Returns No Data

• Verify: MDX syntax, element names, user READ permissions
• Try: skip_zeros=False, test query in PA Workspace first

Write Operation Fails

• Check: WRITE permissions, cells not rule-derived, elements exist
• Use: skip_non_updateable=True to skip rule cells
• Note: Consolidated cells spread data proportionally

Performance Issues

• Use: use_blob=True (requires admin), async operations
• Enable: skip_zeros=True, skip_consolidated_cells=True
• Optimize: Specify dimensions explicitly, use views over MDX

Import Errors

• Install: pip install "tm1py[pandas]" (Python 3.7+)
• Fix: Check virtual environment, reinstall if needed

Best Practices

1. Always use context managers (with statement) for automatic cleanup
2. Specify dimensions explicitly in cell operations for better performance
3. Use DataFrames for bulk operations when pandas is available
4. Leverage async operations for parallel processing of large datasets
5. Handle errors with try-except blocks and check return values
6. Close connections explicitly if not using context managers
7. Use service accounts for automation (not personal accounts)
8. Log operations for audit trails and debugging
9. Test in development before deploying to production
10. Keep TM1py updated for latest features and bug fixes

Reference Files

For detailed information, consult these reference documents in references/:

• API_REFERENCE.md: Complete service-by-service API documentation with all methods and parameters
• CONNECTION_GUIDE.md: Connection patterns for all TM1 environments (on-prem, cloud, TM1 12)
• DATA_OPERATIONS.md: Reading and writing data with options, optimizations, and validation patterns
• METADATA_MANAGEMENT.md: Comprehensive CRUD operations for dimensions, hierarchies, cubes, processes, views, and more
• METADATA_MANAGEMENT_ADVANCED.md: Advanced workflows for cube cloning, complete dimension setup, and complex patterns
• PERFORMANCE.md: Optimization for reads/writes, connection resilience, MDX queries, and benchmarking
• EXAMPLES.md: Real-world patterns including dimension sync, data loading, reporting, multi-environment sync, and error handling

Additional Resources

• Official Documentation: https://tm1py.org/
• GitHub Repository: https://github.com/cubewise-code/tm1py
• Sample Scripts: https://github.com/cubewise-code/tm1py-samples
• TM1 REST API Docs: IBM Planning Analytics REST API
• PyPI Package: https://pypi.org/project/TM1py/