c# and Tm1

Post Reply
HighKeys
Posts: 39
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

c# and Tm1

Post by HighKeys » Mon Nov 18, 2019 12:31 pm

Hello friends,

i'm searching for a .NET Wrapper for the TM1 API (or is there a better way), i want to create some programs around TM1 but i really can't find any actual things at google.

Maybe some user here have something to share?


Thanks and BR
HighKeys

tomok
MVP
Posts: 2647
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: c# and Tm1

Post by tomok » Mon Nov 18, 2019 4:56 pm

HighKeys wrote:
Mon Nov 18, 2019 12:31 pm
i'm searching for a .NET Wrapper for the TM1 API (or is there a better way), i want to create some programs around TM1 but i really can't find any actual things at google.
There is a better way. It's called the REST API. You would be crazy to create programs around a dying API, which the legacy TM1 API definitely is. It's only a matter of time before they pull the plug on it. They would have done it already if it weren't for everyone's dependency on Architect. Once PAW get's better at admin/developer functions they'll pull Architect and once that's gone there is no need for the legacy API.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

HighKeys
Posts: 39
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: c# and Tm1

Post by HighKeys » Wed Nov 20, 2019 10:04 am

Hello tomok!

i thought the c++ api is still active in development, i tried to activate the Web API (it works when i get the metadata but i cant list cubes or something..)


Is there any good documentation on this? (WEB APi, c++ Api etc) or do you know this just from experience? Its hard to find good information about the API stuff.


Thanks for your help!

tomok
MVP
Posts: 2647
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: c# and Tm1

Post by tomok » Wed Nov 20, 2019 12:19 pm

HighKeys wrote:
Wed Nov 20, 2019 10:04 am
i thought the c++ api is still active in development
It most definitely isn't and hasn't been updated in quite a few years. The only reason it is still a part of Planning Analytics is because Architect and Perspectives are built on top of it. Perspectives already has a replacement in PAFE. Architect has a replacement in PAW but it isn't there yet in my opinion (and a lot of others too). IBM is dying to kill it. Once they do the C++ API will be killed.
HighKeys wrote:
Wed Nov 20, 2019 10:04 am
Is there any good documentation on this? (WEB APi, c++ Api etc)
You can Google it just as easily as I can.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lotsaram
MVP
Posts: 3329
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: c# and Tm1

Post by lotsaram » Wed Nov 20, 2019 1:58 pm

HighKeys wrote:
Wed Nov 20, 2019 10:04 am
Hello tomok!

i thought the c++ api is still active in development, i tried to activate the Web API (it works when i get the metadata but i cant list cubes or something..)


Is there any good documentation on this? (WEB APi, c++ Api etc) or do you know this just from experience? Its hard to find good information about the API stuff.


Thanks for your help!
Any TM1 API other than the Ressful API is dead. Simple as that.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Ricky Marwan
Posts: 32
Joined: Tue Aug 14, 2012 7:47 am
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: c# and Tm1

Post by Ricky Marwan » Wed Nov 20, 2019 9:39 pm

Here is a quick and dirty console app to convert slice to snapshot.

What you need:
RestSharp
EPPlus

Code: Select all

using System;
using System.IO;
using OfficeOpenXml;
using System.Collections.Generic;
using RestSharp;
using RestSharp.Authenticators;
using System.Text.RegularExpressions;

namespace ConsoleApp1
{
    
    public class Cell
    {        
        public double Value { get; set; }
    }
    public class Attributes
    {
        public string Caption { get; set; }
    }

    public class Value
    {
        public string Name { get; set; }
        public string UniqueName { get; set; }
        public string AllLeavesHierarchyName { get; set; }
        public Attributes Attributes { get; set; }
    }

    public class RootObject
    {
        public string ID { get; set; }
        public List<Cell> Cells { get; set; }
        public List<Value> value { get; set; }
    }
    
    public class TM1Session
    {
        public string Value { get; set; }
    }
    class Program
    {
        static void ReportBurst(string apiurl, string username, string password, string template, string output)
        {
            var client = new RestClient(apiurl);
            client.Authenticator = new HttpBasicAuthenticator(username, password);
            var request = new RestRequest("api/v1/Configuration");
            var response = client.Get(request);
            if (response.Cookies.Count == 0)
            {
                throw new Exception("Invalid username or password");
            }
            var cookies = response.Cookies;
            string sessionId = cookies[0].Value;
            Regex regex = new Regex("SUBNM|VIEW");
            FileInfo existingFile = new FileInfo(template);
            FileInfo outputFile = new FileInfo(output);
            using (var package = new ExcelPackage(existingFile))
            {
                foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
                {
                    var lastRow = worksheet.Dimension.End.Row;
                    var lastColumn = worksheet.Dimension.End.Column;
                    for (int r = 1; r <= lastRow; r++)
                    {
                        for (int c = 1; c <= lastColumn; c++)
                        {
                            ExcelRange cell = worksheet.Cells[r, c];
                            if (cell != null)
                            {
                                string formula = cell.Formula;
                                if (formula.Contains("DBRW"))
                                {
                                    string[] dbrw = ParseDBRW(formula);
                                    string servercube = worksheet.Cells[dbrw[0]].Value.ToString();
                                    string server = servercube.Split(':')[0];
                                    string cube = servercube.Split(':')[1];
                                    string[] elements = new string[dbrw.Length - 1];
                                    for (int e = 1; e < dbrw.Length; e++)
                                    {
                                        string element = worksheet.Cells[dbrw[e]].Value.ToString();
                                        elements[e - 1] = element;
                                    }
                                    var dimensions = GetDimensions(client, sessionId, cube);
                                    double newValue = GetCubeValue(client, sessionId, cube, dimensions, elements);
                                    cell.Value = newValue;

                                }
                                if (regex.IsMatch(formula))
                                {
                                    cell.Value = cell.Value;
                                }
                            }
                        }
                        package.SaveAs(outputFile);
                    }
                }                
            }
            var logout = new RestRequest("api/logout");
            logout.AddCookie("TM1SessionId", sessionId);
            client.Get(logout);
        }

        static void Main(string[] args)
        {
            try
            {
                if (args.Length < 5)
                {
                    throw new Exception("Invalid arguments. example: \"http://host:port/\" \"admin\" \"apple\" \"C:\\Temp\\Template.xlsx\" \"C:\\Temp\\Output.xlsx\" ");                    
                    //Console.Read();
                }
                Console.WriteLine(args[0] + " " + args[1] + " " + args[2] + " " + args[3] + " " + args[4]);
                ReportBurst(args[0], args[1], args[2], args[3], args[4]);
                Console.WriteLine("Done");
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public static double GetCubeValue(RestClient client, string sessionId, string cube, List<Value> dimensions, string[] dbrw)
        {
            var request = new RestRequest("api/v1/ExecuteMDX");
            request.AddHeader("Content-Type", "application/json");
            request.AddQueryParameter("$expand", "Cells($select=Value)");
            request.AddCookie("TM1SessionId", sessionId);            
            var sBody = BuildMDX(cube, dimensions, dbrw);
            request.AddJsonBody(sBody);
            var response = client.Post<RootObject>(request);
            return response.Data.Cells[0].Value;
        }

        public static List<Value> GetDimensions(RestClient client, string sessionId, string cube)
        {
            var request = new RestRequest("api/v1/Cubes('" + cube+"')/Dimensions");
            request.AddHeader("Content-Type", "application/json");
            request.AddHeader("Accept", "application/json;odata.metadata=none");
            request.AddCookie("TM1SessionId", sessionId);
            var response = client.Get<RootObject>(request);
            var content = response.Content;
            return response.Data.value;
        }

        public static string BuildMDX(string cube, List<Value> dimensions, string[] dbrw)
        {
            string mdx = "{\"MDX\":\"SELECT{(";
            for (int i=0;i<dimensions.Count;i++)
            {
                mdx = mdx + "[" + dimensions[i].Name + "].[" + dbrw[i] + "],";
            }
            mdx = mdx.Substring(0, mdx.Length - 1);
            mdx = mdx + ")} on 0 FROM ["+cube+"]\"}";
            return mdx;
        }

        static string[] ParseDBRW(string formula)
        {
            List<string> list = new List<string>();
            string strStart = "(";
            int startIndex = formula.IndexOf(strStart) + 1;
            int endIndex = formula.IndexOf(")", startIndex);
            string str = formula.Substring(startIndex, endIndex - startIndex);            
            return str.Split(','); 
        }
    }
}

Ricky Marwan

Post Reply