shvuuuu/dal

The Data Access Layer (DAL) provides an interface based on TM1py. Using the DAL, efficient communication between the IBM Planning Analytics/TM1 REST API and Python can be realized.

Data Access Layer

Data-driven innovation through the use of the Data Access Layer based on TM1py ❤️

Install

copy the following files to your python development environment

• DEV.yml
• DAL.py

Configure the DEV.yml

planning_analytics:
    address: <url to your ibm planning analytics instance>
    port: <http-port>
    user: <username>
    password: <password>
    ssl: <boolean>

Use

import

from DAL import DataAccessLayer

instantiate the Data Access Layer with the DEV environment

DAL = DataAccessLayer(path='DEV.yml')

build example mdx-statement

mdx = {
    'columns':'{TM1FILTERBYLEVEL(TM1SUBSETALL([col.dim].[col.dim]) , 0)}',
    'rows':'{TM1FILTERBYLEVEL(TM1SUBSETALL([row.dim].[row.dim]) , 0)}',
    'where':[
        '[where.dim].[where.dim].[member]'
    ]
}

query = f"SELECT NON EMPTY {mdx['columns']} ON 0, NON EMPTY {mdx['rows']} ON 1 FROM [cube] WHERE ({','.join(mdx['where'])})"

Call the DAL request by MDX-Statement

data_raw = DAL.load_data_by_mdx(query)

Helper

convert pandas multi-index to standard pandas.DataFrame

data = data_raw.dropna()
data = data.unstack()
data = data.reset_index()
data.columns = data.columns.map(''.join)

Call the DAL request by View

data_raw = DAL.load_data_by_view(query)

Write dataframe

DAL.write_dataframe(cube="cube",dataframe=data)

Write cellset

DAL.write_cellset(cube="cube",cellset=data)

Run TI

DAL.run_ti(name="test",parameters={'pnValue':1234})

Features

DAL has more tricks up its sleeve than a magician's hat when it comes to getting cozy with TM1py, such as

• Read data from TM1 via MDX and views as easy as possible
• Write/Push data from outside into TM1
• Easily configurable via YAML files
• Significantly more interfaces of source/target systems can be connected (compared to TurboIntegrator) - e.g. OData
• Core technology extending application
• Secure and encrypted connection via SSL; data is not transferred in plain text
• Data transfer from cloud to cloud using the authorization schema specified by the source and target system in each case

Requirements

• 
• 
• 
• 
• 

Optional Requirements

• 
• 

Issues

If you find issues, sign up in Github and open an Issue in this repository

Contribution

DAL is like a magical show, where everyone in the TM1 community can become a wizard! 🧙‍♂️

If you happen to spot a pesky bug or have a spellbinding idea to enhance it, simply take your magical wand (fork the repository), weave your code incantations, and then conjure up a pull request. We'll be ready to merge in your enchanting contributions! ✨🪄