Upload data into TM1 from an Excel file

No matter which tool your company uses for planning, if finance users have to choose one tool to input lots of data, they will choose Excel.

With IBM TM1/Planning Analytics, you can give your users the freedom to do all their input into Excel and then upload the Excel file into the TM1 cubes. This article explains the different methods that you can implement in your TM1 application to upload data from an Excel file into your IBM TM1/Planning Analytics cubes.

Option 1: DBS or PAx

The most common way to send data from Excel to TM1 is to use the DBS formula. The DBS takes a value from a cell and sends it to the TM1 cube. With PAx, the new Excel Add-in, a simple copy paste can send the value into the TM1 cube. 

The advantage of this method is that it is straight forward, you just need to refresh Excel or press the commit button in PAx to upload the data into the TM1 cube. But the challenging part is that you rely on Excel and this is not the best option to upload lots of data. The other main challenge is that there is no governance or transparency. Any TM1 user could upload any data from any Excel spreadsheets.

Option 2: TM1 processes

The second option is to use a TM1 process. TM1 processes are very powerful, they can load data from many types of data sources, albeit Excel is unfortunately not one of them. In order to load data from an Excel file, you will need first to convert the file into a CSV or text format.

To upload data using this method you will need to follow these steps:

  1. User copy their Excel file into a specific folder.
  2. A script converts Excel file into CSV.
  3. TM1 process uploads the CSV file into TM1.

The advantage of this method is that users do not need to log in to TM1 and they can work offline. But this method is not straight forward, in theory it works, but in real life applications there are lots of moving parts.

Option 3: Canvas

The third option is to use the TM1 REST API, in just two steps you can load lots of data from your web browser into TM1:

1. Upload File into your web browser

Canvas includes the SheetJS library which helps working with spreadsheets on the web. With a simple button you can upload an Excel file and then display the data into your browser.

This step allows you to do validations before sending the data into TM1:

2. Send the data into TM1

Once the data is in the browser and after clicking on the Save button. Canvas will batch the query and send the data into the TM1 cube using the TM1 REST API.

This method gives you an extra step where you can validate automatically your users input and add greater governance, controls and transparency so you know who has updated which cells and when.

Once the Excel file is uploaded, all the work is happening on your web browser. You can then continue working on Excel while your data is uploading.

Do more with TM1 with Python

TM1py (pronounced "TM1-pie") is a Python package that wraps the TM1 REST API in a simple to use library. That makes it easy to build stuff with TM1 and Python.

Python is a widely-used general-purpose high-level programming language that lets you work quickly and integrate systems more effectively. We believe Python can be a valuable extension to the TM1 ecosystem.

Get the best of TM1 and Python

Leverage TM1 with Python

Do statistical analysis and write machine learning on top of your TM1 application, these and many more are now easy to achieve with TM1py:

  • Use Pandas for Data Analysis and Statistics on top of your TM1 model.
  • Build Machine Learning and Forecasting Algorithms with Python and scikit-learn based on your TM1 data.
  • Load data (e.g. FX, stock data) from webservices into TM1.
  • Synchronize cubes throughout TM1 instances.
  • Get insights from your TM1 model. (e.g. Which views use this subset?, which process has an ODBC Datasource?).
  • Export TM1 data to XML, JSON, YAML, XLSX, …
  • Generate MDX Queries from cube views.

Get started

TM1py is licensed through an MIT license and can easily be downloaded though the python packaging system. To get started we recommend downloading the samples from Github and play around with it.
If you have questions or remarks on TM1py it’s best to communicate through Github. 

With a few lines of codes you can achieve a lot. For example the code below reschedule all chores for one instance by -1 hour:

with TM1Service(address='localhost', port=8001, user='admin', password='apple', ssl=True) as tm1:
    for chore in tm1.chores.get_all():
        chore.reschedule(hours=-1)
        tm1.chores.update(chore)

Contribute

TM1py is an open source project, just like Bedrock. It thrives on contribution from the TM1 community.
If you find a bug or feel like you can contribute please fork the repository, update the code and then create a pull request so we can merge in the changes.
 

Understand relationships between your IBM TM1 objects

As your IBM TM1/ Planning Analytics application grows, your number of TM1 objects is likely to increase as well. It is good practice to clean up all objects which are not used anymore. In a complex TM1 model, knowing which TM1 objects can be deleted could be time consuming.

This article shows the different features of Pulse which will help you to understand quickly all relationships between your TM1 objects and to determine which objects are not relevant anymore.

bensemail2 copy.png

Interactive Flow Diagram

The Flow diagram gives you an overview of your application, you can easily identify which cubes or TM1 processes are not linked to the others.

You can then highlight a specific object by clicking on it or you can zoom on a specific area to see the object names:

Relationship Diagram

With the relationship diagram, you can focus on specific TM1 objects by including or excluding some objects you do not want to see. It creates a PDF report which you can then share with others:

Technical Documentation

To get more details about your TM1 objects, you can run the technical documentation. It will create a PDF document with lots of information about each object such as description, statistics and all relations to the other objects:

Explore the Pulse databases

To dig even deeper, you can access the Pulse database and with a simple SQL find all relationships. You can then export the table to Excel for further analysis:

What if Heimdall was part of your IBM TM1 team?

Heimdall is a main character in the Thor comic books and movies. He is the guardian of Asgard which is the home of Thor. More importantly he is the gate keeper, he knows the difference between what is good or bad.

What will Heimdall do in your IBM TM1 team?

Heimdall could be your IBM TM1 gatekeeper. He could block everything which should not go into production. Data reconciliation and user requirements should not be the only two criteria to take into account before pushing a release into production. There are other criteria that you should consider such as:

  • Check Best Practice in TM1 rules and TM1 processes.
  • Check naming convention.
  • Check increase in memory consumption.
  • Check the speed performance.

Checking all these criteria is time consuming even for Heimdall unless you give him the right tool.

'Twilight sword' vs Pulse for TM1

Heimdall is in possession of the 'Twillight Sword', it is a weapon that could destroy the 'known worlds'. It sounds amazing and all powerful but it is not going to help you with your IBM TM1 application.

Pulse has lots of features which could help him to make sure that everything that you migrate into production meets user requirements, SOX compliance and IBM TM1 Best Practices such as:

Every IBM TM1 team needs a Heimdall who should be in charge of quality and consistency.

Find the biggest impact on your budget in an instant

Finding which cost center, product or project has the biggest impact on your budget can be time consuming if you have to drill down to the lowest level of a large dimension.

Have you already thought about using Treemaps?

Crossing two types of data will speed-up your analysis, for example while you analyse the % of margin, the Treemap can help you to analyse the impact as well. A product could have a high % of a margin but a low impact or a low margin but high impact.

    Why you should use it?

    A Treemap is a great way to display hierarchical data. It simultaneously shows the big picture, comparisons of related items, and allows easy navigation.

    In a Treemap, you analyse two types of data with one visualization. A quantity measure such as revenue which will determine the size of the boxes and a measure of performance such as % revenue compared to last year which will be represented by the colors.

    Dynamic Treemap with Canvas

    The Treemap in Canvas is dynamic, you can navigate intuitively through the hierarchy, starting from the top consolidation and then drilling down quickly to the lowest levels:

    Customize it the way you like

    With Canvas, you are not limited, you can customize colors, fonts of the Treemap the way you like:

    Meet the Cubewise Code team in Sydney

    The 2017 Sydney TM1 & Planning Analytics User Conference is coming very soon, the 15th of August. The Cubewise Code team will be there.

    For the first time this year, there will be two hands-on sessions where you will be able to try Pulse and Canvas.

    There will be plenty of opportunities to meet us:

    Pulse user group

    On Day 1, It is a customer feedback sessions where you will get the latest news on Pulse, learn a new way to access Pulse databases and Customer stories.

    Pulse Hands-On

    On Day 3, Try Pulse and learn in this session how to improve your Change Management process by using the change tracking, migration and user analytics features.

    Canvas Hands-On

    On Day 3, Try Canvas and learn how to build a TM1 data entry template using the modern web technology. Take the opportunity to learn the basics of HTML, Bootstrap and Angular.

    Booth for demo

    You can meet us at our demo booth where we can show you all our products.

    Book your ticket

    If you haven't booked your ticket yet, you can see the full agenda and book your ticket on the cubewise website: cubewise.com/conference/.

    5 simple ways to be more productive as a TM1 Administrator

    The following article describes how TM1 Administrators using Pulse for TM1, can be more productive and deliver better outcomes for their business users.

    1. Be Pro-Active

    Do no wait for an issue to happen. Pulse for TM1 can send you email alerts before an issue happen. For instance if a TM1 process or chore is taking longer than usual or if the data do not reconcile and many more...

    Do not wait that your TM1 users complain to take action, with Pulse you will be the first to know when and where there is an issue.

    2. Know all possible impacts

    Always keep an overview of even the most complex TM1 models with Pulse’s magic documentation feature including all dependencies. Having current documentation and the ability to view a TM1 model visually via relationship diagrams also means your team can feel confident changing the system knowing all possible impacts.

    3. Be confident before a new release

    Never forget a TM1 object. When migrating a TM1 object, Pulse finds all dependencies which should be included during the migration.

    View changes before migration. It tells you exactly what is going to be updated in the target instance. You can view in details the exact lines of code which are going to be updated.

    4. Easily roll-back changes

    Pulse gives you total transparency about what changed. Every time an object is changed by either a user or system process, it is tracked and logged by Pulse. There is no need to keep lists of what has been changed in your development environments and in production Pulse can provide the needed governance and transparency.  

    Quickly rollback changes, Use the rollback feature to retrieve a previous version of TM1 process or rule file.

    5. Improve testing procedures

    It is fair to say that testing phases are painful for business users. In addition to their daily tasks they have to find some times to test new features before a new release. Unfortunately it often happens that they do not have time to do proper testing. Pulse helps you to ensure that testing procedures have been strictly adhered to by monitoring and tracking the test user’s activity.

    Sending Email Alerts from TM1

    There are many situations where you would like IBM TM1/Planning Analytics to send an email to the TM1 administrators or to TM1 users.

    For example if a chore runs longer than expected, if some users are waiting or if the data does not reconciled.

    IBM TM1/Planning Analytics does not include any tools to help you to send emails to your users. There are some workarounds but they are not very easy to set up.

    You can overcome this by using Pulse for TM1, which is full of features that can help you in your day to day TM1 jobs, one of them is pro-active alerting. Pulse has lots of alert types that you can set-up such as memory alert, if the instance is offline and many more.

    Use case: In most TM1 applications, chores run every night to load data, a chore can run successfully but it does not mean the data will reconcile. This article describes how Pulse can send you an email when the data between two cubes do not reconcile.

    1. Write into the TM1 message log

    From TM1 v10.2.2, a new function LogOutput has been included, it enables you to write to the tm1server.log from a TM1 process:

    • LogOutput('SeverityLevel', 'MessageString');

    In the example below if the value is not the same between the General Ledger and Retail cubes, a message will be written the message log:

    # Check Reconciliation
    #### 
    IF( nValueGL <> nValueRetail );
     message = 'Cost of Sales reconciliation failed between GL ( '| sValueGL | ' ) and Retail ( '| sValueRetail | ' ) for ' | cVersion | ' - ' | cYear ;
     LogOutput ('INFO', message);
    ENDIF;
    

    2. Define an Alert in Pulse

    In Pulse we would need to define a "Message in Log" alert. This will generate an alert every time a parameterized filter pattern is found in the tm1server.log.

    For this example, we will set the filter  "*failed*" as the pattern to be detected by Pulse and a Notification Group (TM1Admin), in order to email those users that could be interested in receiving this notification.

    You can also set this alert to be triggered by one instance or for all (leave instance blank), in this case, the alert will only applied for cxmd. 

     

     

     

     

    Email Alert Sent

    As a result, every time the TM1 process writes the “failure” message in the tm1server.log, Pulse will alert via email:

    The email alert will report that the text pattern “failed”  has been found within the message log and will also include a snapshot of the instance’s TM1Top data:

    Analyse your data with Google Map and IBM TM1/Planning Analytics

    Canvas for TM1 gives your company the freedom to easily create modern, sophisticated IBM TM1/Planning Analytics applications. There is almost no limit with the things you can do now, such as displaying your business data by locations with Google Map. For example we have seen sales field rep benefiting from this new type of visualization:

    Integrate Google Map into your application

    With Canvas for TM1, it is really easy to link Google Map with your IBM TM1/Planning Analytics data. All you need to do is query your cube using MDX Query and then use Canvas for TM1 to display it in Google Map:

    Create your own Google Map

    Even better, you can customize Google Map to the way you want it. Show/Hide roads, parks or monuments. There are lots of settings that you can play with! Google Map has a Google Maps APIs Styling Wizard which will help you create your own style:

    Canvas for TM1 at IBM Vision

    This year at IBM Vision in Orlando, there will be two great examples of Canvas applications presented by Jabil Circuit and Apliqo C3.

    Jabil Circuit

    Jabil Circuit is building a refreshed web-based user interface for its 2,500+ Cognos TM1 users leveraging Canvas for TM1. Jabil will be presenting on stage, the session code is FPM-1205.

    Apliqo C3

    Apliqo C3 provides a pre-built Performance Management Solution with unified reporting, analysis and planning in ten different modules. They have chosen Canvas to rebuild their existing Cognos TM1 Web application into a whole new modern interface. The Apliqo team will be presenting on stage, the session code is FPM-1281.

    Meet us at our booths 13 & 14

    You can meet the Cubewise and the Apliqo team at their booths 13 & 14. Swing past our booths and get a selfie with Manny Perez, Cubewise CTO and the inventor of TM1!

    Canvas examples

    TM1 migration has never been so convenient

    At first sight, the TM1's file-based architectural makes TM1 migration from one instance to another quite straight forward: 

    Even if it looks easy, you have to make sure that you picked all the files from the source instance. For example if you are migrating a cube, all dimensions have to be included and to migrate a dimension with its attributes you have to include all control objects.

    If you miss one TM1 object, you will have to restart the target instance again. It might not be a big deal if you are working on development but if you are in production it might be difficult to find a new time-slot.

    The Pulse migration feature overcomes TM1's file-based architectural limitations with a centralized model that reduces risk of mistakes.

    Advantages of using Pulse for TM1 migration

    • Never forget a TM1 object: When migrating a TM1 object, Pulse finds all dependencies which should be included during the migration.
    • View changes before migration: Pulse tells you exactly what is going to be updated in the target instance. You can view in details the exact lines of code which are going to be updated.
    • Automatically back-up the database folder.
    • Very robust: It is used by many clients for the past years. With Pulse v5.5, we optimized the migration to make it even faster.

    How it works?

    Pulse provides a structured approach to migration, which follows two important steps: 

    1.  Create migration package: Gather all TM1 objects you want to migrate into one package from one source instance.
    2. Execute migration package: Import all TM1 objects from the package to the target instance.

    Each package contains all the information and files required to update a target instance.

    Migration methods


    Pulse has several methods for creating a migration package. The two main methods are source control and manual migration:

    • The Source Control option allows you to query for a list of changes based on a date range and also by developer/user (leave this blank for all users) and then select the items you want to migrate.
    • The Manual option allows you to pick the objects you want to migrate manually. It is not restricted by changes that have been made, any object can be migrated (except views and subsets). Pulse will then find all dependencies of the TM1 objects selected:

    Types of packages

    Once you picked which methods you want to use to create the package, you will then have to decide the package type:

    • Live Update: The execution of packages can be performed while the server is still running.
    • Offline Update: Pulse will create a folder with the TM1 object files. You can then copy the files manually into the target instance.

    Pulse provides enterprise grade migration features allowing you to easily move changes in your TM1 models between servers.

    Read more:

    Responsive design with Canvas and Bootstrap

    Excel vs HTML

    The first thing you notice when you start building a web page is that the layout is not defined. You literally start from a blank page. First you have to create the containers before adding some contents. 

    In Excel, when you create a new sheet, you can easily add some contents into the cells. The advantage is that you don't have to worry about defining the containers but the downside is that the layout is fixed. The size of the cells will always be the same no matter the screen-size. It is not responsive, on a small screen it is likely that you will have to use the scroll bar to see all the contents.

    Building a webpage takes a bit more time at the beginning because you have to define the layout. The advantage is that it offers you the freedom to set your layout as you want. To help you to define a responsive design for your TM1 applications, Canvas includes the Bootstrap framework.

    What is Bootstrap?

    Bootstrap is a pretty nice HTML and CSS framework for developing responsive web applications. In other words it helps to create a layout which will adjust depending on the screen size.

    The bootstrap grid system allows for up to 12 columns on the page. You can group multiple columns to create bigger containers:

    For example if you want to show 4 columns of the same size in the same row, you will need to create 4 containers with a width of "col-md-3", you define a container by using div tag:

      <div class="row">
        <div class="col-md-3">
             Column 1
        </div>
        <div class="col-md-3">
             Column 2
        </div>
        <div class="col-md-3">
            Column 3
        </div>
        <div class="col-md-3">
            Column 4
        </div>
      </div>

    This is what your page will look like, 4 columns with the same width:

    If you work on a smaller device where the 12 columns cannot fit, instead of staying on the same row and use the scroll bar to see them, the columns will be pushed to the next rows:

    Bootstrap allows you to manage the look and feel of your TM1 applications as you resize your web browser or switch from a tablet to a smart phone.

    Dashboard with responsive design in Canvas

    In the following video, you can see an example of dashboard with a responsive design:

    With Canvas and Bootstrap you have the freedom to define the look and feel of your TM1 applications. You do not need to know Bootstrap or HTML before starting to build Canvas pages. Canvas comes with a complete set of samples which will give you plenty of ideas on how to build out your own TM1 applications, and if you want to go even further there are plenty of contents online such as forum and training courses.

    10 Tips to improve your TM1 application

    Over the years your TM1 application will grow, you might create new cubes, create/update rules or TM1 processes, create new elements.... All these changes will have an impact on your application performance. 

    If you want to minimize this impact, here are 10 tips that you should look at. When you design an application, it is always difficult to find the right balance between users requirements, design standards and performance that is why these tips might not all be relevant to your application.

    1. Improve TM1 rules

    As the size and the complexity of systems grow, your TM1 rule files become more complex so if you do not want to lose calculation time, here are some best practices that you can follow:

    • Use skipcheck algorithm.
    • Use TM1 attributes instead of text functions (SUBST, SCAN): Text comparison in TM1 are slower than operating on attributes.
    • Try to create consolidations instead of rules: Consolidation is the fastest way to calculate values:

    Use FTE as a consolidation of Hours with a weight of (1/8)

    instead of

    ['FTE']=['Hours']/8;

    • Area statements are faster than IF statements:
    ['A'] = N: 1;
    ['B'] = N: 0;
    

    instead of

    [{'A','B'}] = N: IF( !Column @= 'A' , 1 , 0 ) ;

    Pulse for TM1 can help you to follow these best practices. The validation report can help you to validate your best practice rules against your model.

    2. MTQ (Multi-Threaded Query)

    Even if TM1 was already fast, IBM made TM1 even faster with the introduction of MTQ with v10.2. Instead of doing the calculation on one CPU, TM1 can now use several CPU at the same time. As IBM recommendations, the best practice is to set the MTQ value such that the maximum available processor cores are used.

    3. Run TM1 processes in parallel

    Parallel processing is a TM1 feature which unfortunately is not enough used. Imagine for instance that instead of running one TM1 process which copy data for one year, you could run one TM1 process which then launches one TM1 process per months (12 processes running at the same time) using TM1RUNTI.

     

    Instead of

    If you want to do parallel processing, I highly recommend you to use Hustle. It is a free tool which will help you to handle the number of threads you want to run at the same time.

    4. Avoid User Locking

    Having users locked and not able to access their data is one of the worst situation for a TM1 Administrator, hopefully it does not happen often in the TM1 world. Here some tips to avoid user locking:

    • Turn off cube logging by using CellPutS( 'NO', '}CubeProperties', 'cubename', 'LOGGING' ) instead of CubeSetLogChanges( 'cubename', 0 ).
    • Avoid dimensions update during working hours ( Split metadata update and data update in 2 different TI)
    • Use CellGetN with cautiou, it can create locking.
    • Security Refresh (use processes over rules to update TM1 security).
    • Set up Alerts to be the first to know when there is an issue.

    5. Train your users

    After implementation it is usual for users to develop their own TM1 spreadsheets that may or may not be designed according to best practices. Depending on what they build (Large cube view or do spreading on a high consolidation), it might slow down or even lock TM1 during working hours. In order to avoid these issues you should make sure that they know the TM1 basics:

    • Use VIEW function in a slice.
    • Use DBRW instead of DBR.

    Pulse analyses all Excel workbooks linked to your TM1 application and can help you to identify users who need training.

    6. Restart your TM1 server on a weekly basis

    TM1 elements that are deleted aren't removed from the TM1 indexes until a server restart. So if you are frequently removing and adding elements your memory will grow overtime. Restarting the TM1 instance will help removing temporary files.

    7. Clean dimensions

    The number of elements in dimensions increase over time. Even if it is not an issue for a cube to have lots of "0" cells, having lots of elements in your dimension will slow down all your MDX query or dynamic subsets.

    This is relevant only for Large dimensions (>100,000 elements) such as Product or Customer dimensions. Be very careful during this step because if you delete an element, you will loose the data attached to it.

    8. Snapshot old data

    Rules should be applied only on a specific cube area where data changes. You do not need rules if your data is static such as last year data for example. What you could do is export cube data for the specific year, remove the rules and then load the data back, the data for this year will then be static and it will be much faster to query them.

    9. Tune VMM/VMT

    TM1 keeps the calculation in memory, the first time you open a cubeview, if it takes more time than the VMT value (default is 5 sec), TM1 will keep this view in memory (TM1 creates a stargate view), next time you open the cubeview, it will be much faster because TM1 will open the stargate view instead of recreating the view from scratch.

    VMM is the amount of RAM reserved on the server for the storage of stargate views. Increasing this value will allowed TM1 to store more stargate views which means that TM1 will be much faster but it will consume more memory.

    10. Disable anti-virus on the TM1 data folder

    Virus scan software can negatively impact your TM1 application performance. You should set up your anti-virus to skip TM1 folders.

    Read more:

    Monitor and manage your TM1 environment in one place

    Managing the performance of your company’s IBM TM1 applications has never been an easy task with the limited built in tools. You and your company can appreciate that as the size and complexity of systems grow you need the right tools to understand your model, track what is happening and then diagnose issues as they arise. Pulse for TM1 has been developed for this very purpose; it is the one and only all-encompassing management tool for TM1.
    With Pulse for TM1 you and your company can take the management of your system to the next level: be the first to know when there is an issue, deliver performance reports to your users and understand the design of your system more than you ever have before.

    What's the magic behind Pulse:

    Pulse constantly tracks your TM1 services. It is composed of two services which update two internal databases. Pulse must be installed on each server where TM1 is installed.

    The Excel Addins:

    Pulse comes with the Extend Excel add-in. Extend monitors how Excel is used by the TM1 users. It communicates back to the Pulse server each time a user creates, saves, opens or closes a TM1 enabled Excel workbook. A TM1 enabled workbook is one that contains at least one of the following formulas: TM1RPTVIEW, VIEW, SUBNM, DBRW, DBR or DBS.

    Key features:

    Read more:

     

     

    Happy TM1 users by Pro-Active support

    Over the years your TM1 application is growing, as your TM1 application grows, your TM1 user base is likely to grow as well. Supporting a large number of users can be time consuming, especially during month end closes and budgeting/forecasting periods, I can bet that these questions have been asked of you many times before:

    • Where is this data coming from?
    • Why can't I access my reports?
    • My Excel is freezing, is there something running on the TM1 application?

    Typically we see that by giving the appropriate tools to your TM1 users to better understand the TM1 application, you'll reduce the number of support calls and increase the user adoption. Thanks to different assets that I will refer to in this article, I'm going to show you that Pulse for TM1 can be the answer.

    Give User's transparency

    With Pulse's Live Monitor capability users can view exactly what's happening on the TM1 application. If the TM1 users think there is something wrong with the TM1 application, they can go and investigate the issue by themselves instead of calling the TM1 support team.

    One of our clients reduced their number of support calls by 50% by exposing the Pulse Live Monitor to all its TM1 users.

    bring confidence to TM1 users

    To truly trust the data, TM1 users need to understand the TM1 application. Explaining how the application works or where the data is coming from, is time consuming, especially if you have to explain the same things to different users.

    To overcome this challenge we recommend using Pulse's dynamic reports and documentation which can help the users to understand how the TM1 application works at the click of a button:

    Having current documentation and the ability to view a TM1 model visually via relationship diagrams also means your users will feel confident about the application knowing all source and relationship between cubes.

    Train your users

    Users building large views or doing data spreading on large dimensions can slow down the TM1 application. Pulse can help you to identify which users are making these requests and might require TM1 training. With Pulse you can for example analyze all workbooks in your application, the number of sheets, number of rows and if they are using VBA:

    Conclusion:

    You can improve your TM1 application as much as you want but if your TM1 users do not know how to use it, or do not understand how it works, you will likely continue to receive a disproportionate number of low level support calls. The best way to reduce the number of support calls is to give your users the tools to understand the TM1 application themselves.

    What is the TM1 REST API?

    The TM1 REST API is a new way of accessing data and everything else in TM1. Rather than being a proprietary API like old TM1 interfaces it is based on web standards making it accessible to a wide range of developers.  

    REST stands for REpresentational State Transfer and is an architectural style (not a standard) that has become the common way of designing API for applications on the internet. The TM1 REST API does however support the OData standard (v4) which provides a common way for accessing data through queries and also updating data. OData is used by multiple vendors and is supported by companies like Microsoft, SAP and of course IBM

    If you want to become a true TM1 REST API guru you should read up the documentation on the odata.org website. TM1 supports v4 of the standard, you may find examples out on the web that are an earlier version of the standard that won't work with TM1.

    An important distinction to make about REST and OData is that neither of these are programming languages. The REST API is language agnostic, you can use the language of your choice: Java, JavaScript, Python, C#, Visual Basic, etc. All of these languages have HTTP libraries that will allow you to access the REST API.

    What is great about the REST API?

    • It is fast! The REST API is built in to the TM1 server, there is no external web servers or parts you need to install. Because of this the REST API has direct access to all of that data stored in memory in your TM1 server.
    • It is fast over slow networks! The REST API is optimized for access over the internet, it takes advantage of all of the goodness that is built into the HTTP protocol such as compression, streaming, etc.
    • Did I mention it is fast? In our applications, we are noticing no discernible difference in speed when accessing applications from a local server to one that is hosted on the other side of the world. Our application framework Canvas optimizes this further by batching requests so they are returned in milliseconds rather than seconds.
    • You can use any programming language. Whatever your favorite programming language is the chances are you can use it with the REST API. As long as there is a HTTP library for your language, you can use it. Examples of these are:
      • Java: Apache HTTP Client, okHttp, etc.
      • C# and VB.NET: Built into the .NET Framework: System.Net.WebClient and System.Net.HttpWebRequest
      • JavaScript: jquery, Angular $http.
      • Python: httpie.
      • Others: Search github.
    • It is very easy to get most information out of TM1, there is even a built-in document that describes the API called $metadata (more on that later).
    • It is cloud and big data enabled! I just added that to make the article sounds cooler.

    What ARE the hard bits ABOUT the REST API?

    If we are going to make an objective assessment of the REST API we also need to understand that it isn't all smooth sailing with the REST API. In fact, because of the points below Cubewise decided to build a framework on top of the REST API called Canvas to help TM1 developers build beautiful user interfaces without all of the scary bits.

    • It is low level. The REST API is built to access TM1 objects directly, this is easy for many queries but is more difficult for updating data, accessing application entries and a number of other things.
    • Difficult for the traditional Accountant who recently became a TM1 Developer. As stated before, the REST API can be complicated for people without an IT background.
    • Little cross-over from existing TM1 concepts and terminology. If you are a long time TM1 person you will be very familiar with functions such as DBRW and SUBNM as well as Active Forms and Slices. These concepts don't exist in the REST API but do in Canvas.

    What can you use the REST API for?

    Anything, the REST API is NOT a user interface however, it is a way to access objects/data in TM1. It could be used for pretty much anything:

    • As an ETL tool to move data into and out of TM1.
    • As a data source for a user interface.
    • To sync data between TM1 servers.
    • To backup data to another source or file. 
    • Create, update and delete TM1 objects.

    Where to next?

    Over the coming weeks we will be posting regular articles on how to use the REST API including examples. We will also provide a comparison and reasons why we developed Canvas to leverage the goodness of the REST API and provide something that is easy to work with for regular TM1 developers. 

    Stay tuned!

    How to measure TM1 user adoption

    User adoption is good way to evaluate the success of any application. You can build the most efficient application ever created but at the end of the day if users are not using it, it's ROI is questionable.
    Typically, in most projects, only key users are involved during the design phase. While most business users will only ever see the application at the end of the project… which might be too late to get their buy in.

    Knowing who is using your application, when are they using it and how they are using it, is very important if you want to improve your application, increase its user adoption and measure the applications ROI.

     

    How to measure it ?

    Without Pulse, it is very hard to know who is really using the TM1 application and what are they consuming.

    Pulse keeps track of user sessions. You know who is using your TM1 application, when are they using it, what are they consuming and lots of other statistics like number of sessions, session times...:

    With this data, you will be able to see who is not using your TM1 application and ask them why. Maybe you just need to make a few changes to get them back on board.

    And there is more, Pulse tells you exactly how the users are using your TM1 application, you can see if users are consuming the TM1 reports you built or if they prefer to build their own:

    Pulse keeps track of TM1 spreadsheets in both Excel and TM1 Web. If you want to know more about User Analytics or Excel Analytics with Pulse just click on the links.