Creating TM1 Objects in C# and ASP.NET

[KevinB]

Hello all,

I have been working with the TM1 .NET API and was wondering if anyone would be interested if I posted a "How To" guide to create a dynamic Data Table in C# (which you can then import into a GridView in a ASP.NET page).

Let me know if anyone is interested and I will put something together.
~Kevin

[Olivier]

Hi Kevin,

I guess you are using this method in order to optimise web reports ?
If yes could you please give us an overview of the architecture around it ?


Thanks,

Kind regards,

Olivier

[KevinB]

I will post more as I devlop further. This example is how to get a View in TM1 from a Cube and turn it into a DataTable in C#. Once you have a Data Table as part of a DataSet. You can then do a lot in ASP.NET. You might ask "Why bother?". Well because you can programically Create a TM1 View in TI or in the .NET Framework, you could have your custom Web Page return the view without having to go through TM1 Web all the time. You could also create your own web application which uses drop down menus to select title columns, row subsets, etc... and then have the Data Table created and displayed on the fly.

Here is a simple Function in C# to create a DataSet, retrieve a TM1 Data Table from a view and return it as a DataTable.

public DataTable GetTM1Table()
{
DataSet ds = new DataSet();
ds.Tables.Add("TM1DataTable");
TM1AdminServer admin = new TM1AdminServer("adminHostName", "tm1adminserver");
TM1Server myServer = admin.Servers["TM1ServerName"].Login("admin", "apple");
TM1CubeCollection myCubes = myServer.Cubes;
TM1Cube myCube = myCubes["CubeName"];
TM1View view = myCube.PublicViews["Default"];
TM1DataTable data = view.DataTable;
// Get the Column Names in the View and bind them to the table
// The line below I am creating the First Column Header named "Row ColumnHeader" and then will add the Row elements to it later
ds.Tables["TM1DataTable"].Columns.Add("RowHeaderColumn");
// Count the number of rows and columns in the view. We are not concerned with the Title elements because you should already know what they are based on the view.
int ColumnCount = view.ColumnTable.Rows[0].Cells.Count;
int RowCount = view.RowTable.Rows.Count;
// Loop through the columns and add the names of the column elements to the Table.
for (int i = 0; i < ColumnCount; i++)
{
// Create a new Column in the DataTable by getting the Column header from the TM1 View.
ds.Tables["TM1DataTable"].Columns.Add(view.ColumnTable.Rows[0].Cells.Value);
}
// Columnm Headers are created. Now get the rows and thier data and bind them to the columns.
for(int y=0; y < RowCount; y++)
{
DataRow rowNew = ds.Tables["TM1DataTable"].NewRow();
// You need to name the Column that contains the row elements. If your row elements are GL Accounts.. Name RowHeaderColumn "GL Accounts"
rowNew["RowHeaderColumn"] = view.RowTable.Rows[y].Cells[0].Value;
// Get the first row in the TM1DataTable
TM1DataRow Row = data.Rows[y];
// Here is the tricky part... The columns are zero based indexed. The first Column [0] we already created which consists of the GL Accounts.
// So I make a new counter and initialize it to 1 instead of 0.
int a = 1;
// Now I loop through the TM1Rows and retrive their values 1 row at a time.
foreach (TM1DataCell cell in Row.Cells)
{
// bind the cell value to the first column, second column, etc...
rowNew[a] = cell.Value;
a++;
}
// The row is now has values... Add it to the Table.
ds.Tables["TM1DataTable"].Rows.Add(rowNew);
// Go back and add all the rows until you hit the last row.
}
// Dispose of the objects in memory... This doesn't delete them it just clears out the garbage.
admin.Dispose();
data.Dispose();
view.Dispose();
admin.LogoutAll();
// Return the Table... Now you can call this funcition and bind the table to a GridView, a Chart or whatever you need.
return ds.Tables["TM1DataTable"];

}

// Happy Coding!

~Kevin

[brandonchua]

If you guys do not mind my two cent worth,

In order for TM1 view or MDX issued view information to be really retrieved and display on the fly, it should really be able to translate any TM1view regardless of the number of tuples and dimensions involved.

Personally i prefer encapsulate the View Details, Axes Details and Dimensions information into individual objects.
Thereafter, classify these objects into one object and return it via any web service to any page which itself is linking to TM1 API.
This web service can then feed this result into any new age presentation layer such as SliverLight.

Depending on your architectural design then splitting the arithmetic into frontoffice (Client) or backoffice (Server), calculations on what to display can hence be allocated there.

I am more than happy to post a series of self written tutorials on TM1 API.NET, Sliverlight, AJAX, ASP.NET and the web services involved if anyone is interested.

[KevinB]

Oliver,

I am most interested. I have come accross some issues trying to Bind subsets to drop downs. It seems when I changed and element in a Column Subset header or Row header, the calulated value was one off the selected element. I thought this was because the elements in the API are indexed 0 vs on the TM1 server by 1. I contacted IBM and they said that this was an Known Issue which should be resolved in FP3. If you have a better design I am all eyes and ears..

Thanks!

Kevin

[brandonchua]

...

[Alan Kirk]

This is Brandon Chua not Olivier.

I meant to post earlier that I too think that your documents would make for most interesting reading. Even though I don't use .Net all that much myself (or the .Net API at all at present, even though IBM support seem convinced otherwise (don't ask)), it would be interesting to see the approaches involved.

And hey, if Olivier has any as well, I'm sure that no-one would object if he brought them to the party.

[brandonchua]

Kelvin you should try using TM1MdxView instead of TM1View.
Since you are already in API, why not go the extra mile.

But to be proficient in that, MDX queries have to be issued.
And if you already understand the power of MDX, you will realised that you are not far away from creating the standard Cognos BI Query Studio solution. (Click and Drag capabilities)

Understanding MDX is another chapter all together but you can easily manipulate and beat the data to form any result you want.
As a result, you can create dynamic view from result sets deprived from first MDX query.

In brief, issue an MDX query into TM1 API and from there create the Axis, Dimensions and CellValue Objects.
Some TM1 APIs you have to use are GetAxisValues, GetCellValues etc. From there create your babies objects.

Tutorials coming soon! - BRANDON CHUA

[Olivier]

Hi all,

Brandon is the man for interfacing tm1 to the web !
I am afraid my inputs in this area are very limited...except to say that i am not a fan of tm1 web...

These days, my technical skills are more around the mastering of the supernatural features of tm1 such as views corruptions, sleeping cells, and other Iboglix original features...

But still i will read this post to the end for sure

Olivier

[olapuser]

yes brandon TM1MdxView is good and simple, just an addition to brandon post if you do not know anything about MDX you may install MS SQL Analysis Service 2000 there is an MDX query studio to generate MDX statemens.

[iansdigby]

I am just now getting in to the TM1 .NET API and would deeply appreciate any tutorials Brandon or anyone else is generous enough to post here. Actually quivering with anticipation at the possibilities of the (TM1 API + .NET + MDX) package.
Regards
Ian Digby

[paulfelce]

TM1 heavy lifting, with silverlight front end sexiness. A match made in heaven.

[brandonchua]

Sorry brudders,
I am in a middle of a change right now. Kinda tied up but i will be back here soon.
Meanwhile i can do my best to answer any technical queries into TM1 API (c, vb or .NET) or (C#) sliverlight or ASP.NET.

[shockwave]

Hi All,

Inspired by KevinB I have a produced a working mdx view using the tm1.api. The code may be a little sloppy though. If other people have other examples can they please post them. There is just not enough information out there when it comes to the tm1.api and its a shame to see. Here it is:


Imports Applix.TM1.API
Imports System
Imports System.Data
Imports System.Web.UI.WebControls
Partial Class Development_Workings_dataBindingGrids
Inherits System.Web.UI.Page

Public Function GetTm1Table() As DataTable


Dim ds As New DataSet()
ds.Tables.Add("TM1DataTable")
ds.Tables("TM1DataTable").Columns.Add("RowCollumnHeader")

Dim admin As New TM1AdminServer("server", "tm1adminserver")
Dim server As TM1Server = admin.Servers("server name").Login("admin", "adminPassword")

Dim mdxExpression As String = "SELECT{[month].[jul],[month].[aug]} ON COLUMNS," & _
"{[accounts].[COMMS],[accounts].[OFFICE],[accounts].[TRAVEL],[accounts].[TRNING],[accounts].[AUDIT_FEES]} ON ROWS " & _
"FROM [reportincube]" & _
"WHERE ([company].[TAIL_GRP],[costcentre].[division1],[tradingindicator].[All Trading Partners],[product].[TOT_PRODUCT]," & _
"[transindicator].[All Indicators],[measures].[Financial],[accessmethod].[All Access Methods],[year].[2010],[version].[Reported])"
Dim MyTm1DataView As New TM1MdxView(server, mdxExpression)



'################ LOAD DATA ####################



'#### CREATE COLUMN HEADERS #####
Dim MyMdxColAxis As TM1MdxAxis = MyTm1DataView.GetAxis(0) '(0) indicates x axis ie the columns dimensions ie months
Dim MyMdxColTupleCount As Integer
MyMdxColTupleCount = MyMdxColAxis.TupleCount 'return the number of tuples with the set ie [accounts].[comms]...[accounts].[audit fees] = 5
Dim colCount As Integer

'create column headings use GetAxixValue fucntion and set property index to 1 to return element names
colCount = 0
While colCount < MyMdxColTupleCount
ds.Tables("TM1DataTable").Columns.Add(MyMdxColAxis.GetAxisValue(colCount, 0, 1))
colCount += 1
End While

'#### CREATE ROW HEADERS and ADD DATA TO TABLE #####
Dim MyMdxRowAxis As TM1MdxAxis = MyTm1DataView.GetAxis(1) '(1) indicates y axis ie the rows dimensions ie accounts
Dim MyMdxRowTupleCount As Integer
MyMdxRowTupleCount = MyMdxRowAxis.TupleCount 'return the number of tuples with the set ie [accounts].[comms]...[accounts].[audit fees] = 5

Dim rowCount As Integer
rowCount = 0
While rowCount < MyMdxRowTupleCount
Dim rowNew As DataRow = ds.Tables("TM1DataTable").NewRow()
rowNew("RowCollumnHeader") = MyMdxRowAxis.GetAxisValue(rowCount, 0, 1)
'create a new row in the datatable
ds.Tables("TM1DataTable").Rows.Add(rowNew)

'get cellvalue and bind it to dataset row
Dim cellValue As Object
Dim colCoord As Integer 'column coordinates of mdx view for table starts at 0
Dim colIndex = 1 'in datatable data starts at 1 not index of 0. index of 0 is for the row headers
While colIndex <= MyMdxColTupleCount
colCoord = colIndex - 1
'add value - to table at current row position and col position to mdx table
cellValue = MyTm1DataView.GetCellValue(rowCount, colCoord)
'add row headers - row 0 indicates the row headers. a rows(1) will create an an out of range error item(1) moves across x plain 1 cell
ds.Tables("Tm1DataTable").Rows(rowCount).Item(colIndex) = cellValue
colIndex += 1
End While

'loop to next row
rowCount += 1

End While

MyMdxColAxis.Dispose()
MyMdxRowAxis.Dispose()
admin.LogoutAll()
MyTm1DataView.Dispose()
admin.Dispose()

Return ds.Tables("TM1DataTable")



End Function

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

GridView1.DataSource = GetTm1Table()
GridView1.DataBind()
End Sub
End Class

[winsonlee]

Code: Select all

Dim mdxExpression As String = "SELECT{[month].[jul],[month].[aug]} ON COLUMNS," & _
"{[accounts].[COMMS],[accounts].[OFFICE],[accounts].[TRAVEL],[accounts].[TRNING],[accounts].[AUDIT_FEES]} ON ROWS " & _
"FROM [reportincube]" & _
"WHERE ([company].[TAIL_GRP],[costcentre].[division1],[tradingindicator].[All Trading Partners],[product].[TOT_PRODUCT]," & _
"[transindicator].[All Indicators],[measures].[Financial],[accessmethod].[All Access Methods],[year].[2010],[version].[Reported])"

I notice in MDX query you will have to specify the element you are looking for. Instead of parcing in individual element name, how can i parse in a subset into the query ?

I have a dimension name call Products.

In the Products dimension i created a subset with AAA, BBB, CCC , DDD element in it. Every now and then i will be adding more elements into the subset.

SELECT{[Products].[AAA],[Products].[BBB],[Products].[CCC],[Products].[DDD]} ON COLUMNS

If there is new element added onto the dimension, does it mean that i will have to change the MDX query as well or there is an easier way to maintain the query ? eg SELECT{[Products].[subset name]} ON COLUMNS so that if there is a new element adds onto the dimension, i do not need to alter the statement.

thanks.

[yyi]

I'm having some trouble retrieving elements from a subset;
it seem to work okay until a subset has aliases or attributes for the elements and i get this error:-
"Cannot create attribute value for "Alias1" attribute, parameter is ERROR."

Code: Select all

TM1SubsetCollection sSubsets = server.Dimensions["Entity"].PrivateSubsets;
                TM1Subset sSubset = sSubsets["Default"];
                TM1SubsetElementCollection sSubsetElemets = sSubset.AllElements;
                    foreach (TM1SubsetElement sSElement in sSubsetElemets)
                    {
                        DropDownList1.Items.Add(sSElement.Name.ToString());
                    }

any help or references will be great, thanks.

[yyi]

i had put in "Alias1" in the example as the attribute to highlight that it was an alias and not a text or string attribute.
the error i found was actually around the alias name because it contained a character that the code could not resolve,
ie: had an ampersand like "code & description". once it was removed, the element names were able to be 'read'

there were no references to be found anywhere, even on google so if anyone knows how to code the .net api and
get objects with names containing characters other than numbers and letters [/@$#!&"] would be excellent

[tomok]

[quote="yyi"there were no references to be found anywhere, even on google so if anyone knows how to code the .net api and
get objects with names containing characters other than numbers and letters [/@$#!&"] would be excellent [/quote]

Just another example of why I NEVER use anything other than letters, numbers, and the underscore character when naming a TM1 object.

[sivan307]

Hi Brandon Chua:

I am interested too. Never coded anything in web. Definitely would be a good experience learning.

Thanks,
Kal

[winsonlee]

hi,

recently I have coded my report in asp.net as well to generate report in table and graph form. I am from programming background so pretty much programming gives me the flexibility to do what i wanted to do.
As cognos express now comes with report studio, i would like to find out what asp.net able to achieve but not report studio ?

thanks