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
c# and Tm1
-
- MVP
- Posts: 2831
- 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
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.
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: c# and Tm1
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!
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!
-
- MVP
- Posts: 2831
- 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
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.
You can Google it just as easily as I can.
-
- MVP
- Posts: 3652
- 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
Any TM1 API other than the Ressful API is dead. Simple as that.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!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 32
- Joined: Tue Aug 14, 2012 7:47 am
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2007
Re: c# and Tm1
Here is a quick and dirty console app to convert slice to snapshot.
What you need:
RestSharp
EPPlus
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