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 easily get 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 article is the first one in a series focused 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: Divvy, Capital 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 set up our working environment and upload weather data from a web service:
Execute a python script that builds all the required TM1 objects for the series
Write a python script to load daily historic weather data (Min, Max and Average by city) from a web service into a TM1 cube
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
Pandas, a library which provides data structures and data analysis tools
TM1py, the python package for TM1
Jupyter Notebook
1 – Download the Cubike sample content
After downloading the zip file, unzip it 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 York 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 update the TM1 settings 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!
Run the script from the command line:
After the script ran, you should now be able to see the TM1 objects:
3 – Run the Data Integration Jupyter Notebook
To start Jupyter Notebook, type the following command in the folder where the notebooks are located:
Access the notebook via http://localhost:8888/tree, click the upload button and select Data Integration with TM1py.ipynb.
Step 1: 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 Administration (NOAA). NOAA has a rich API which allows us to access all kind of environmental data from the US.
Step 3: Query the Weather Data
Now that our URL is ready, we send the request to the API:
Step 4: Rearrange the data
Before sending the data into TM1, we rearrange the data so it matches our TM1 cube structure:
Step 5: Send data into TM1
Now that the data is ready, we connect to our TM1 instance and write the values into the TM1 cube "Weather Data". With TM1py we can do this in two lines of code:
If you open the Weather Data Cube, you should be able to see data for the first days of 2017.