HowTo

Run processes in parallel using only one TM1 connection

Being able to run processes in parallel is one of the most powerful features of IBM TM1 and Planning Analytcs. Instead of loading one year of data with one process, you could run in parallel one process per month which will significantly speed up your loading time!

Run processes in parallel with TM1RunTI

Running processes in parallel is quite common now in IBM TM1 and Planning Analytics applications. The most common way to do it is to use TM1RunTI.

TM1RunTI is an exe file which is located in the TM1 bin installation folder. With a simple command line as below you can run multiple processes in parallel:

"C:\Program Files\Cognos\TM1\bin\tm1runti.exe" -process Cub.Flight -adminhost CW111 -server flightstats -user TI01 -pwd "" pYear=2000 pMonth=01
"C:\Program Files\Cognos\TM1\bin\tm1runti.exe" -process Cub.Flight -adminhost CW111 -server flightstats -user TI02 -pwd "" pYear=2000 pMonth=02
"C:\Program Files\Cognos\TM1\bin\tm1runti.exe" -process Cub.Flight -adminhost CW111 -server flightstats -user TI03 -pwd "" pYear=2000 pMonth=03

Manage running processes with Hustle

Hustle was created to manage the threads and making sure that you do not exceed the number of available threads on the server. Hustle uses TM1RunTI, therefore it has the same limitation, for each thread, it will first need to connect to TM1 to be able to execute it.

In certain applications, TM1 developers had to set up one different user per thread to avoid locking.

Run processes in parallel with only one connection

To avoid this situation RushTI was created:

RushTI does the same as Hustle except that it connects to TM1 only once at the beginning and then reuse the same connection to run all threads.

This article will explain you how to install and use RushTI.

Prerequisites:

  • TM1 10.2 FP5 as minimum
  • TM1py v1.0.3 as minimum

1. Install Python

RushTI is a Python script, to run it, you will first need to install Python. Steps to install Python can be found here.

2. Install TM1py

    RushTI uses the free Python package TM1py, so the next step is to install the TM1py package. Open a command prompt and then type:

    pip install TM1py

    3. Download RushTI folder

    RushTI is an open-source Python script which you can download from its github page:

    In this example, the RushTI folder has been copied to the C:\RushTI folder:

    4. Update config file

    In the config file example, you will find two instances but you can add as many as you want, to add a TM1 instance just add the following information:

    • [instanceName]
    • address: IP address of the physical server where the TM1 instance is located
    • port: HttpPortNumber of the instance (tm1s.cfg)
    • namespace: (only required for CAM Security)
    • user: TM1 user
    • password: TM1 user password (encoded in Base 64)
    • ssl: True/False (depending useSSL setting in tm1s.cfg)

    WARNING: The password needs to be encoded in Base 64.

    5. Update tasks.txt

    The file tasks.txt contains the list of all processes which are going to be executed by RushTI. In this example, the process Cub.GeneralLedger.LoadFromFile will be executed for every months:

    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=01
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=02
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=03
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=04
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=05
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=06
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=07
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=08
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=09
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=10
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=11
    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=12

    It can be noted that in the same list, you can run processes from different TM1 instances by changing the instance parameter:

    instance="tm1srv01" process="Cub.GeneralLedger.LoadFromFile" pMonth=01
    instance="tm1srv02" process="Cub.GeneralLedger.LoadFromFile" pMonth=01

    6. Run RushTI from command line

    To run RushTI as a command line, open a command promtp and then use, the last parameter is the number of threads RushTI will run at the same time:

    python RushTI.py Tasks.txt 4

    If you are not in the RushTI folder, you will have to add the path to the script:

    python C:\RushTI\RushTI.py C:\RushTI\Tasks.txt 4

    The following video shows the Pulse Live Monitor with 4 threads running at the same time:

      Run RushTI from a TM1 process

      To run RushTI from a TM1 process, you just need to use the ExecuteCommand function either in the Prolog or Epilog tab:

      sCommand = 'python C:\RushTI\RushTI.py C:\RushTI\Tasks.txt 4';
      
      ExecuteCommand(sCommand,1);

      The second parameter is either 1 if you want to wait for the command to finish or 0 to termiate the process straight away.

      Troubleshoot RushTI

      If the threads are not running, the best place to start looking at, is the log file. RushTI will create a RushTI.log file in the same folder location as the RushTI.py.

      The most common errors are the following:

      1. RushTI can't connect to TM1 instance so you should check first the settings in the config.ini (Check connectivity with TM1).
      2. TM1py version should be v1.0.3 as minimum (Upgrade TM1py).
      3. Check parameters of the TM1 processes.

      Timeseries Forecasting with Facebook Prophet and TM1/Planning Analytics

      Welcome to the last part of the articles series about Data Science with TM1/Planning Analytics and Python. In Part 1 we loaded weather data from the NOOA web service into our TM1 cubes. In Part 2, by analyzing the data with Pandas and Ploty, we've learned that 

      • There are strong seasonal trends throughout the year
      • Public Holidays and weekends have a negative impact on the bike shares
      • Temperature and Bike shares are strongly correlated in every city. 
      • The intensity with which temperature affects the bike shares varies by city. Washington DC is the city that is least affected by the weather.

      Before going any further, we recommend you to read these two articles:

      Objective

      In this article, we are going to explain how to use Facebook's Prophet to create a two year demand forecast for bike sharing, based on four years of actuals from our TM1 cube.

      Before we start with the implemenation let's quickly discuss what Prophet is.

      Prophet

      The idea behind the prophet package is to decompose a time series of data into the following three components:

      • Trends: these are non-periodic and systematic trends in the data,
      • Seasonal effects: these are modelled as daily or yearly periodicities in the data (optionally also hourly), and
      • Holidays / one-off effects: one-off effects for days like: Black Friday, Christmas, etc.

      Based on our historic data, Prophet fits a model, where each of these components contribute additively to the observed time series. In other words, the number of bike shares on a given day is the sum of the trend component, the seasonal component and the one-off effects.


      Step 1: Data Wranging

      In the Part 2 of this series, we already loaded the actuals from the Bike Sharing cube into Python. We called the variable df_b.

      Before we can use this data to fit our Prophet model, we must make sure we arrange the data in a correct format.

      The dataframe that Prophet needs has two columns:

      • ds: dates
      • y: numeric values

      We execute the following code to arrange our dataframe.

      df_nyc = df_b[city].reset_index()
      df_nyc.rename(columns={'Date': 'ds', city: 'y'}, inplace=True)
      df_nyc.tail()

      We use the tail function on our dataframe (df_nyc) to display the last 5 rows of data:


      Step 2: Fitting the model

      Now that we have the data ready, and a high level understanding of the seasonal trends in our data, we are ready to fit our model!

      First we need to instantiate Prophet. We are passing two arguments to the constructor of the Prophet model:

      • The public holidays that we want Prophet to take into account
        (they come from a TM1 cube through MDX. More details in the Jupyter notebook)
      • Whether or not Prophet should model intraday seasonality
      m = Prophet(holidays = holidays, daily_seasonality=False)

      Now we can fit our model, by executing the fit method on our model and passing the dataframe, that we arranged in step 1.

      m.fit(df_nyc);

      This is where Prophet is actually doing all the hard work, the curve-fitting.
      Under the hood Prophet uses Stan to run the statistical calculations as fast as possible.


      Step 3: Use Facebook's Prophet to forecast the next 2 years

      We can use the fitted Prophet model, to predict values for the future.

      First we need to specify how many days we would like to forecast forward.
      This code block creates a dataframe with the sized window of future dates.

      future = m.make_future_dataframe(periods=365*2)

      Then we use the predict function on our model. As the argument to that function, we pass the dataframe future.

      forecast = m.predict(future)

        Done! 

        The forecast is ready. Let's look at what Prophet predicted !

        We select the following columns on the dataframe and print out the last 5 records::

        • ds (the date) 
        • yhat (the predicted value)
        • yhat_lower (the lower bound of the confidence interval)
        • yhat_upper (the upper bound of the confidence interval)

        The following code is going to print the last 5 records:

        forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']].tail()

        Step 4: Analysing the forecast

        We can interrogate the model a bit to understand what it is doing. The best way to do this is to see how the model fits existing data and what the forecast looks like. This is shown in the plot below.

        The black dots correspond to the historic number of bike shares each day (2014-2018). The dark blue line represents the estimated number of shares, projected with the fitted model.
        The light blue lines correspond to the 80% confidence interval for the models predictions.

        Judging visually, the model has done a good job of picking up the yearly seasonality and the overall trend. The forecast for 2019 and 2020 looks plausible!

        To get an even further understanding of our fitted model, we can plot each of the model components. This is shown in the plot below.

        In the top panel we see the linear growth term. This term contains changepoints (either determined independently by Prophet or preset by the user) so that the rate of growth is allowed to vary over time. The second panel shows the effect that public holidays have on our bike shares. The final two panels show the estimated yearly and weekly trends of the model:

          components_plot.png

          Conclusion on this analysis:

          • An overall global trend of growth from 2015, that slowed down slightly after 2016.
          • Public holidays lead to a fall in the usage of the bikes
          • A strong weekly seasonality: Our bikes are used mostly during the week – presumably for commuting.
          • A strong yearly seasonality with a peak in summer/ automn and a drop in winter.

          Step 5: The last step is to send the data back to TM1

          Before sending the data back to TM1, we need to rearrange the data so it matches the dimensions in our cube:

          • Version: Prophet Forecast
          • Date: date
          • City: city
          • Bike Shares Measures:
            • Count for yhat
            • Count Lower for yhat_lower
            • Count Upper for yhat_upper

          To rearrange the data for TM1 we execute the following code.

          cells = {}
          
          for index, row in forecast.iterrows():
              date = str(row['ds'])[0:10]
              cells['Prophet Forecast', date, city, 'Count'] = round(row['yhat'])
              cells['Prophet Forecast', date, city, 'Count Lower'] = round(row['yhat_lower'])       
              cells['Prophet Forecast', date, city, 'Count Upper'] = round(row['yhat_upper'])

          Once our data set is ready, we use the TM1py function tm1.cubes.cells.write_values to send the data to our cube Bike Shares:

          tm1.cubes.cells.write_values('Bike Shares', cells)

          Let's check in the cubeview, years from 2014 to 2017 are actual data and the forecast starts in 2018:

          See Prophet in action with Jupyter

          Explore your TM1/Planning Analytics data with Pandas and Ploty

          Welcome to the second part of the Data Science with TM1/Planning Analytics article. In the Part 1 article, we uploaded in our TM1 cubes the weather data from a web service. Now that we have all the data we need in TM1, we can start to analyse it. The Python community provides lots of tools which make data science easy and fun.

          Why using Python for Data Science?

          • Top-notch, free data analysis libraries.
          • Free (and good) libraries to access data from systems or web.
          • Lots of research in new disciplines like, Machine Learning (Tensorflow) or Natural Language Processing (NLTK).
          • TM1 (as the Information Warehouse) is the ideal data source for Data Science

          Before going through this article, you should read first, Part 1:

          Objective

          The objective of this article is to explore the impact of seasonal effects, weather and public holidays on our operative business. To do that we are going to follow these steps:

          1. Load and visualize monthly bike shares by city
          2. Explore seasonal and regional trends
          3. Analyze relationship between average temperatures and bike shares by day
          4. Analyze the impact of Non-working days vs. Working days.

          We created a Jupyter notebook to guide you through these steps. The notebook that we are going to use in this article is Data Science with TM1py.ipynb, you can find it in the cubike-sample.zip file which you can download in the Part 1 article.

          Prerequisites

          To run this example on your machine, you will have to make sure first that you have installed the following components:


          Step 1:  Load, visualize 2017 monthly bike shares by city

          The first step is to define the TM1 connection settings which you find at the top of the notebook:

          ADDRESS = "localhost"
          PORT = 8009
          USER = "admin"
          PWD = ""
          SSL = True

          Before we start with the analysis, we first need to bring data from TM1 into our notebook.

          We start with data from the view 2017 Counts by Month of the Bike Shares cube.
          To load data from the cube view into python we execute the following code:

          cube_name = 'Bike Shares'
          view_name = '2017 Counts by Month'
          
          data = tm1.cubes.cells.execute_view(cube_name=cube_name, view_name=view_name, private=False)

          Our cellset, given back by TM1, is stored in the variable data. We use a TM1py function to convert the cellset into a pandas dataframe, which we call df:

          df = Utils.build_pandas_dataframe_from_cellset(data, multiindex=False)

          We now need to rearrange the dataframe. We need to reduce the dimensionality of our data from 4 (Time, Version, City, Measure) to 2 (Time, City). This should make our life easier down the road.

          df['Values'] = df["Values"].replace(np.NaN, 0)
          for city in ('NYC', 'Chicago', 'Washington'):
              df[city] = df.apply(lambda row: row["Values"] if row["City"] == city else None, axis=1)
          df.drop(columns=["Values"], inplace=True)
          df = df.groupby("Date").sum()

          To show the rearranged dataframe, we can just type df into a jupyter cell and execute it.

          Let's plot a barchart from our dataframe, to explore and understand the monthly distributions throughout the cities, visually.

          Using the popular charting library Plotly, we can create an interactive barplot with just 7 lines of python code:

          cities = ('NYC', 'Chicago', 'Washington')
          
          # define Data for plot
          data = [go.Bar(x=df.index, y=df[city].values, name=city) for city in cities]
          
          # define Layout. stack vs. group !
          layout = go.Layout(
              barmode='stack',
              title="Bike Shares 2017"
          )
          
          # plot
          fig = go.Figure(data=data, layout=layout)
          py.iplot(fig)

          As you can see below the Ploty package creates an interactive bar chart, you can untick a city and zoom into a specific chart area:

          Step1 - Video

          Step 1 - Conclusion

          As expected, the seasons have a massive impact on our bike sharing business. In the warmer months we have substantially more usage than in colder months.

          Also interesting is that the seasons seem to impact cities differently. While the relation between Summer and Winter months in NYC and Washington DC is approximately 1/2, in Chicago it is close to 1/5!

          Let's dig deeper into the relationships between the cities and the Temperature!


          Step 2: Explore seasonal and regional trends

          In order to get a clearer picture, instead of just looking at monthly figures of 2017, we are going to bring in the daily data from 2014 to 2017.

          We execute the following code to create a new dataframe df_b based on the data coming from the view 2014 to 2017 Counts by Day:

          cube_name = 'Bike Shares'
          view_name = '2014 to 2017 Counts by Day'
          
          data = tm1.cubes.cells.execute_view(cube_name=cube_name, view_name=view_name, private=False)
          df_b = Utils.build_pandas_dataframe_from_cellset(data, multiindex=False)
          df_b['Values'] = df_b["Values"].replace(np.NaN, 0)
          
          # Rearrange content in DataFrame
          for city in ('NYC', 'Chicago', 'Washington'):
              df_b[city] = df_b.apply(lambda row: row["Values"] if row["City"] == city else None, axis=1)
          df_b.drop(columns=["Values"], inplace=True)
          df_b = df_b.groupby("Date").sum()

          Let's print 5 sample records from the dataframe using df_b.sample(5):

          Pandas dataframes come with very handy and easy to use tools for data analysis. To calculate the correlation between the different columns (cities) in our dataframe, we can just call the corr function on our dataframe. df_b.corr():

          As one would expect, the bike shares by day across our three cities are all strongly correlated.

          To analyze the relationship between the average temperature and the bike shares  by city, we need to query the daily historic average temperatures from our TM1 cube into python.

          We execute the following code to create a dataframe (we call it: df_w) based on the cubeview 2014 to 2017 Average by Day of the Weather Data cube:

          cube_name = 'Weather Data'
          view_name = '2014 to 2017 Average by Day'
          
          data = tm1.cubes.cells.execute_view(cube_name=cube_name, view_name=view_name, private=False)
          df_w = Utils.build_pandas_dataframe_from_cellset(data, multiindex=False)

          To calculate the correlation between the two dataframes (df_w and df_b) we can use the corrwith function

          df_b.corrwith(df_w)

          Step 2 - Video

          Step 2 - Conclusion

          Temperature and Bike shares are strongly correlated in every city. 
          For the forecasting (part 3 of this series) to be effective we will need a model that can take seasonal effects (e.g. temperature) into account.

          The intensity with which temperature affects the bike shares varies by city.
          For the forecasting we will need to create different models by city.


          Step 3: Analyze relationship between average temperature and bike shares by day

          Let's visualize the relationship between temperature and bike shares in a Scatterplot.
          From our two dataframes: df_w (average temperature by day) and df_b (bike shares per day) we can create a scatterplot in just a few lines of code:

          cities = ('NYC', 'Chicago', 'Washington')
          colors = ( 'rgba(222, 167, 14, 0.5)','rgba(31, 156, 157, 0.5)', 'rgba(181, 77, 52, 0.5)')
          
          # Scatterplot per city
          data = [go.Scatter(
              x = df_w[city].values,
              y = df_b[city].values,
              mode = 'markers',
              marker = dict(
                  color = color
                  ),
              text= df_w.index,
              name=city
              )for (city, color) in zip (cities, colors)]
          
          # Plot and embed in jupyter notebook!
          py.iplot(data)

          Step 3 - video

          Step 3 - Conclusion

          Analyzing the plot visually we make a few statements:

          • Among the three cities, the distribution in Chicago is the closest to a linear model .
            Judging visually, we could draw a neat line through that point cloud
             
          • For Washington DC we can recognize an interseting trend, that for temperatures of approx. 25 degrees and higher the bike count stagnates.
             
          • The distribution for NYC is less homogeneous. A simple linear model would not sufficiently explain the bike shares count.

          Let's quantify those finding and take a closer look at the how non-working days impact our operative business.


          Step 4: Analyze the impact of Non-working days vs. Working days.

          To analyze the impact of Public holidays and weekends, we will focus on one city at a time. Let's start with NYC.

          First we want to create a linear regression between the average temperatures and the bike shares for NYC. 

          To calculate the fitted line we use the linregress function from the popular Scipy stats module.

          Note that the function not only returns us the slope and intercept of the fitted line, but also three measures (R squared, P Value and the Standard Error), that quantify how well the fitted line matches the observations.

          x, y = df_w[city].values, df_b[city].values
          slope, intercept, r_value, p_value, std_err = stats.linregress(x, y)

          Now we need to query Public Holidays and Weekends from TM1 through two small MDX Queries, and merge them into a list. This list we call non_work_days.

          mdx = "{ FILTER ( { TM1SubsetAll([Date]) }, [Public Holidays].([City].[NYC]) = 1) }"
          public_holidays = tm1.dimensions.execute_mdx("Date", mdx)
          
          mdx = "{FILTER( {TM1SUBSETALL( [Date] )}, [Date].[Weekday] > '5')}"
          weekends = tm1.dimensions.execute_mdx("Date", mdx)
          
          non_work_days = public_holidays + weekends

          Now we can create a new scatterplot, that includes the fitted line (orange), the working days (blue) and the non-working days (green).

          When we repeat this exercise for Chicago and Washington DC we see a similar picture:
          The fitted line matches the points more (Chicago, Washington DC) or less (NYC) good and the majority of the green points lay underneath the fitted line.

          Step 4 - video

          Step 4 - Conclusion

          In all three cities the vast majority of the green points lay under the fitted line.
          On Non-working days there is generally less usage than our fitted linear regression model (Temperature ~ Bike Count) predicts.

          For the forecasting (part 3 of this series) to be effective we will need to take weekdays and public holidays into account.


          Next step: Timeseries forecasting

          The last part of this series is about timeseries forecasting with TM1py. We are going to use Prophet to forecast the bike shares in the future:

          Upload weather data from web services into TM1/Planning Analytics

          Python is a widely-used general-purpose programming language that lets you work quickly and integrate systems more effectively. TM1py enables you for the first time to get easily data from a multitude of systems and web services such as Trello, Salesforce, financial web services and many more... into your TM1/Planning Analytics application.

            This help article is the first one on the series of articles focus around a fictional Bike Sharing company called Cubike operating in New York City, Washington D.C and Chicago.
            The data for the series is real historic data provided from Bike Sharing companies in North America: DivvyCapital Bikeshare and Citi Bike.

            Objective

            The objective of these articles is to explore and understand the impact of the weather and public holidays on our operative (bike sharing) business and subsequently use modern timeseries forecasting techniques to generate a rough two year forecast.
            In this first article we will setup our working environment and upload weather data from a web service:

            1. Execute a python script that builds all the required TM1 objects for the series
            2. Write a python script to load daily historic weather data (Min, Max and Average by city) from a web service into a TM1 cube

            If you are new to TM1py, we recommend you to start with the following article which is going to guide you in the installation of Python, TM1py and Jupiter:

            Prerequisites

            In order to run this sample you will need to install on your machine:

            • IBM TM1 10.2 FP5 as minimum or Planning Analytics.
            • Python, an extremely useful programming language for everything that is related to data.
            • Pandas, a library which provides data structures and data analysis tools.
            • TM1py, the python package for TM1.
            • Jupyter (How to setup jupyter notebook).
             

            1 - Download the Cubike sample content

            We gathered all the files you need to run this example in the following zip file: 

            After downloading the zip file from above, unzip this file in any folder, you should see two Jupyter notebooks (.ipynb), four csv files and one python script:

            • chicago.csv: Bike Sharing data from Chicago.
            • Data Integration with TM1py.ipynb: Jupyter Notebook to load data into TM1 from a web service.
            • Data Science with TM1py.ipynb: Jupyter Notebook to analyse the data.
            • nyc.csv: Bike Sharing data from New Yord City.
            • public holidays.csv: Public Holidays data.
            • washington_dc.csv: Bike Sharing data from Washington DC.
            • setup.py: Python script to create TM1 objects.
             

            2 - Run the setup.py script

            The setup.py script will create all Cubes, Rules, Dimensions, Subsets, Views, etc. that are required. Open setup.py and then update the TM1 settings which you will find at the top of the script:

            • address: Server name where TM1 is installed
            • port: HttpPortNumber value in tm1s.cfg
            • user: TM1 admin user name
            • password: TM1 password
            • ssl: UseSSL setting in tm1s.cfg
            IMPORTANT: Before running the script you should be aware that the Date, Version and City dimensions will be overwritten, if they already exist in the TM1 model!

            Now you are ready to run the script either with pycharm or from the command line. Open a Windows Command prompt window, navigate using the cd command to the setup.py folder location and then type:

            • python setup.py

            If the script fails, it is likely due to one of following reasons:

            After the script ran, you should now be able to see the following TM1 objects:

              You are now ready to run the first Jupyter notebook!

               

              3 - Run the Data Integration with TM1py Jupyter Notebook

              For demonstration and documentation purposes all the python code in this series is distributed through Jupyter Notebooks. Jupyter Notebook is a web application that allows you to create and share documents that contain live code, equations, visualizations and narrative text.

              To start Jupyter Notebook, you need to type the following command line in the folder where the notebooks are located:

              • jupyter notebook

              Once it has started, you should be able to access the jupyter notebook via the following URL:

              • http://localhost:8888/tree

              You can then click the upload button on the top right corner and select the first notebook that we are going to use: Data Integration with TM1py.ipynb.
              Now click on the newly uploaded notebook and the notebook should be opened in a new tab:


              Step1: Import packages and define constants

              The first step in the Jupyter notebook is to import the packages and define the constants we need:


              Step 2: Build the URL Query

              We are pulling the weather data from the National Oceanic and Atmospheric Administation (NOAA). NOAA has a rich API which allows us to access all kind of environmental data from the US.

              We use the following code to build the URL query:

              url = 'https://www.ncdc.noaa.gov/cdo-web/api/v2/data?' \
                    'datasetid=GHCND&' \
                    'startdate=' + FROM + '&' \
                    'enddate=' + TO + '&' \
                    'limit=1000&' \
                    'datatypeid=TMIN&' \
                    'datatypeid=TAVG&' \
                    'datatypeid=TMAX&' \
                    'stationid=' + STATION
              
              print(url)

              Step2 - video


              Step 3: Query the Weather Data

              Now that our URL is ready, we need to send the request to the API:

              response = requests.get(url, headers=HEADERS).json()
              results = response["results"]   

              From the repsonse, we are storing the results into a new variable results. Then we print the first 3 objects:

              print(json.dumps(results[0:3], indent=2))

              Step 3 - video


              Step 4: Rearrange the data

              Before sending the data into TM1, we now need to rearrange the data so it matches our TM1 cube structure:

              • Version = Actual
              • Date = record['date'][0:10]
              • City = NYC
              • DataType = record['datatype']
              cells = dict()
              
              for record in results:
                  value = record['value'] / 10
                  coordinates = ("Actual", record['date'][0:10], "NYC", record['datatype'])
                  cells[coordinates] = value

              Step 4 - video


              Step 5: Send data into TM1

              Now that the data is ready, we just need to connect to our TM1 instance and finally write the values into the TM1 cube "Weather Data". With TM1py we can do this in two lines of code:

              with TM1Service(address='10.77.19.60', port="12354", user="admin", password="apple", ssl=True) as tm1:
                  tm1.cubes.cells.write_values("Weather Data", cells)

              Step 5 - video

              If you open the Weather Data Cube, you should be able to see some data for the first days of 2017:

              Generate MDX Queries form Cubeview

              MDX is a fast and powerful way to query data from TM1 Cubes. It can play a key role when you combine your TM1 models with other technologies (e.g. Canvas, R, Python).

              Example of MDX Query:

              SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS, 
                NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS  
                FROM [General Ledger] 
              WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

              How do we write MDX Queries? TM1py can do this for you!

              TM1py can generate MDX Queries from existing cube views in your TM1 model. So, you don’t have to write them yourself.

              This can be a great way to get started with MDX or to save time and be more productive.

              This article explains the steps to create MDX query from an existing cubeview. For this example we will use the Gross Margin by periods view of the General Ledger cube:

              The TM1py script which generates the MDX is called 

              If you haven't downloaded the samples, you can download the script here.

              2. Update the script

              You need to update the TM1 connection parameters and then the cube and view name:

              3. Run the script

              Before running the script, you should make sure that TM1py is installed and that TM1py can connect to your TM1 instance.

              To run the script open a command line, go to the script folder, in this example C:\TM1py\TM1py-samples-master\Other and then type:

              • python "generate mdx from native view.py"

              TM1py is going to print in the command line the MDX query:

              SELECT NON EMPTY TM1SubsetToSet([Account],"Gross Margin") on ROWS, 
                NON EMPTY TM1SubsetToSet([Period],"All Periods") on COLUMNS  
                FROM [General Ledger] 
              WHERE ([Version].[1],[Year].[2013],[General Ledger Measure].[Amount],[Currency].[Local],[Region].[1],[Department].[1])

              4. Print the MDX query into a file

              To print the MDX query in a file you can replace the following code:

              print(nv.MDX)

              with

              with open('output.csv','w') as file:
                      print(dim, file=file)

              The script should look like this:

              If you run the script again in the command line:

              printmdx2.png

              A file containing the MDX query will be created in the same folder where the script is located:

              Find unused dimensions

              Ever wondered which of the dimensions in your TM1 Model are not used in cubes?

              TM1py can help to answer this questions with 8 Lines of code!

              If you have previously downloaded all samples folder, the script "find unused dimensions.py" is located in the Other folder:

              If you want to only download this script, you can find the code on github.

              1. Check connection with TM1

              Before running the script you should make sure that the connection with your TM1 instance is working:

              2. Update the script

              Open the python script with a text editor and update the TM1 connection information (address, port, user, password, ssl):

              3. Run the script

              Open a command line from where the script is located and then type:

              • python "find unused dimensions.py"

              By default it is going to print the list of all dimensions in the command line.

              4. Print the list of dimensions into a file

              To print this list of dimension in a file you can replace the following code:

                  print(unused_dimensions)

              with

              with open('output.csv','w') as file:
                  for dim in unused_dimensions:
                      print(dim, file=file)

              Your code should look like this:

              Save the script and if you run the same command line again:

              FindUnusedDimension4.png

              The script will create the file in the same location where the script is located:

              Open the file and you will see the list of all dimensions:

              Be careful before deleting a dimension

              This script will give you the list of all dimensions which are not used in a cube. But unused in a cube does not mean that the dimension is not used by something else. For example the dimension could be used in a TM1 process or TM1 rule.

              To find all unused dimensions using a TM1 process, you can find the steps in this IBM guide:

              Cleanup your TM1 application

              Sometimes our TM1 models (especially non-production environments) become messed up with temporary and unwanted objects, like

              •  subsets and views that were used in TI processes, but not properly deleted
              • temporary processes that were used to test stuff
              • cubes and dimensions for prototyping

              In many cases we do use certain naming conventions when we create temporary objects, like we prefix all the processes with ‘temp_’ or ‘test’. It can be a tedious task though, to walk through your model object by object and delete stuff that matches a certain naming convention.

              Writing a TM1 process which loops through TM1 objects

              Writing a TI process on the other Hand can be cody and might rely on the data directory (which is not a sustainable approach anymore, considering recent developments in the TM1 server, such as Encryption for Data at Rest)

              Neither option is convenient. TM1py can help!

              Through TM1py you can Read, Update, Create and Delete all kind of TM1 objects.
              When we combine this with the power of Regular Expressions we can write scripts that clean our models from all temporary or unwanted objects with the ease of python and regular expressions.

              This article explains how to run the TM1py scrip cleanup model.py. If you have downloaded the TM1py samples, you can find this script in the folder called Other:

              The objective is to delete all TM1 objects starting with TM1py in the Canvas Sample TM1 instance:

              1. Update TM1 settings and the regular expression

              Open the script with any text editor or using PyCharm, update the TM1 connection settings (address, port, user, password, ssl). Before running the script you should make sure that TM1py can connect to TM1 instance.

              To delete all TM1 objects starting with TM1py we are using the following regular expression:

              • regex_list = ['^TM1py*']

              Your script should look like this:

              2. Run the script

              In a command line, navigate to the folder where the script is located and then type:

              • python "cleanup model.py"

              If you check now your TM1 instance, all TM1 objects starting with TM1py should have been deleted.

              Upload Exchange rate from a Webservice

              Your TM1 models can get great benefits from external information, e.g., FX rates, weather forecast, data on commodity prices or consumer confidence. Webservices like Yahoo Finance, Bloomberg or FRED, offer a range of financial and non-financial information. Many of them are Free to use.

              How do we bring this data into our TM1 model? TM1py can help us to do this.

              What TM1py does:

              The load process generally consist of 3 steps

              1. Load raw data from a webservice (e.g. Yahoo Finance, Quandl)
              2. Transform the data
              3. Push the transformed data into TM1

               This article explains the steps to load FX rates from the FRED to a TM1 cube.

              What is the FRED?

              The Federal Reserve Bank of St.Louis is a financial institution which offers lots of financial data such as all exchange rates. In this example we are interested to the exchange rate from USD to JPY:

              Prerequisites

              This sample uses pandas to get the data from the FRED, before running the script you need to install pandas python module. In a command line type:

              • pip install pandas

              Once pandas has been installed, you need to install the pandas_reader module. To install it, run in a command line:

              • pip install pandas_datareader

              1. Update TM1 instance settings

              Before running the Load Data\fx rates to cube daily.py script you need to update the TM1 credentials:

              This script is going to load the echange rates to TM1py FX Rates cube, to create this cube you need to run the sample setup.py script.

              2. Run the script

              After checking the connectivity with your TM1 instance, open a command line inside the Load Data folder and type the following command:

              • python "fx rates to cube daily.py"

              3. Check TM1py FX Rates

              This script will load daily exchange rates from 1990/01/01 to 2041/01/01in the TM1py FX Rates cube:

              4. Load other Exchange rates

              If you are interesting about other exchange rate, you can see all available Exchange rate in this link:

              For example if you want to load the the Exchange rate USD To Euro, you need to get the index DEXUSEU:

              To run this script from a TM1 process you should check the following help article: