Build your own AI infused Natural Language Query for tool TM1

This post demonstrates how relatively simple it is to enhance the capabilities of TM1 with AI infused natural language query (NLQ) to assist with interpreting your data using Python with TM1.

Examples like this using NLQ support a broader (non technical) user base and help to transform the activity focus from lower value information collation to higher value data analysis.

Using the tm1py and sketch libraries, this sample also relies on the original TM1 SData database as the TM1 source. The script connects to the database and then runs an MDX query against TM1 that extracts the data ready for the user to query via plain language.

Please note that sketch is a publicly accessible service so all usual considerations around data sensitivity, privacy, etc should apply here.

# import for sketch
import sketch

# Create the TM1 service using tm1py
from TM1py.Services import TM1Service

# Connect to TM1 via tm1py
tm1 = TM1Service(address='localhost', port=8010, user='admin', password='apple', ssl=True)

# This mdx will be used as data in this example
mdx = """
SELECT 
NON EMPTY {{TM1DRILLDOWNMEMBER( {[model].[Total]} , ALL, RECURSIVE )}} ON ROWS,
{[month].[Year],[month].[Jan],[month].[Feb],[month].[Mar],[month].[Apr],[month].[May],[month].[Jun],[month].[Jul],[month].[Aug],[month].[Sep],[month].[Oct],[month].[Nov],[month].[Dec]} ON COLUMNS
FROM [SalesCube]
WHERE ([account1].[Sales], [actvsbud].[Actual], [region].[World])
"""

# retrieve a dictionary through TM1py and build a DataFrame from it
from TM1py.Utils.Utils import build_pandas_dataframe_from_cellset

data = tm1.cubes.cells.execute_mdx(mdx)

# could store view into pandas data frame via TM1py ie
# data = tm1.cubes.cells.execute_view(cube_name=cube_name, view_name=view_name, private=False)

# basic dataframe
tm1_data = build_pandas_dataframe_from_cellset(data, multiindex=False)
tm1_data['Values'] = tm1_data['Values'].round(2)

# Prompt user for questions
question = ''

# If user types in ciao then stop otherwise, keep running (and replying)
while question != 'ciao':
    # get question
    question = input("Ask TM1 something: ")
    
    if question != 'ciao':
        # ask question
        answer = tm1_data.sketch.ask(question, False)

        # print the response
        print ( answer, end='\n' )

print ( "Thanks for chatting...")