View Extract with TM1 API

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

View Extract with TM1 API

Post by rmackenzie »

Hello all,

Did anyone have any success generating a view extract via the TM1 API ? I'm stuck !

I've built a valid handle to the extract object of a pre-existing view, but I can't return any data from TM1ViewExtractGetNext - it only returns a TM1 index type variable (value is 1). I think the sticking point is in the correct use of the TM1ViewExtractComparison property but can't be sure....

Any help appreciated.

Robin
Robin Mackenzie
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: View Extract with TM1 API

Post by rmackenzie »

Code attached ....

Code: Select all

Option Explicit

Dim hUser As Long
Dim pPool As Long
Dim hServer As Long

Sub Test()

    Dim hCube As Long
    Dim hView As Long
    Dim hExtract As Long
    Dim hLines As Long
    
    Call Login
        
    hCube = TM1ObjectListHandleByNameGet(pPool, hServer, TM1ServerCubes, TM1ValString(pPool, "plan_BudgetPlan", 0))
    hView = TM1ObjectListHandleByNameGet(pPool, hCube, TM1CubeViews, TM1ValString(pPool, "High Level Profit and Loss", 0))    
    hExtract = TM1ViewExtractCreate(pPool, hView)
    
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractComparison, TM1ValIndex(pPool, 1)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractRealLimitA, TM1ValReal(pPool, 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractRealLimitB, TM1ValReal(pPool, 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractSkipConsolidatedValues, TM1ValBool(pPool, 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractSkipZeroes, TM1ValBool(pPool, 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractSkipRuleValues, TM1ValBool(pPool, 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractStringLimitA, TM1ValString(pPool, "", 0)
    TM1ObjectPropertySet pPool, hExtract, TM1ViewExtractStringLimitB, TM1ValString(pPool, "", 0)
    
    hLines = TM1ViewExtractGetNext(pPool, hExtract)
    
    Debug.Print TM1ValType(hUser, hLines)

    TM1ViewExtractDestroy pPool, hExtract

    Call Logoff
    
End Sub
Robin Mackenzie
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: View Extract with TM1 API

Post by rmackenzie »

Well, it seems like a good nights sleep will help any API problem...

Code: Select all

Do
    hLines = TM1ViewExtractGetNext(pPool, hExtract)
    ' do stuff with hLines
Loop Until hLines = 0
Robin Mackenzie
olapuser
Posts: 40
Joined: Fri Jan 29, 2010 1:55 am
OLAP Product: Cognos TM1
Version: 9.5
Excel Version: 2007
Contact:

Re: View Extract with TM1 API

Post by olapuser »

any resolution for this? mine still return an index
Kyro
Community Contributor
Posts: 126
Joined: Tue Nov 03, 2009 7:46 pm
OLAP Product: MODLR - The CPM Cloud
Version: Always the latest.
Excel Version: 365
Location: Sydney, Australia
Contact:

Re: View Extract with TM1 API

Post by Kyro »

I have a C++ version of this I wrote to allow batch file cube exports to csv. Would you like me to post it or are you after VBA/6 only?
olapuser
Posts: 40
Joined: Fri Jan 29, 2010 1:55 am
OLAP Product: Cognos TM1
Version: 9.5
Excel Version: 2007
Contact:

Re: View Extract with TM1 API

Post by olapuser »

you can post any language, C++ is fine with me.

Thank You
ture
Posts: 9
Joined: Sat Oct 21, 2017 11:40 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: View Extract with TM1 API

Post by ture »

I need to extract view using filter by an dimension (I have value of an element). I can get/create hView, but TM1ViewExtractCreate(TM1ViewExtractGetNext) extracts all contents of cube.

How can I set an filter? Should I create a new View with subsets?
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

I would recommend to achieve this via REST API which is the future.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
ture
Posts: 9
Joined: Sat Oct 21, 2017 11:40 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: View Extract with TM1 API

Post by ture »

How can I select and insert at subset all elements of a dimension whose names match a regular expression?

It does not work:

Code: Select all

	TM1V vPattern = TM1ValString(hPool, Pattern, PatternLen);
	TM1V vOk = TM1SubsetSelectByPattern(hPool, hNewSubset, vPattern,TM1ValBool(hPool, isSelect));
	if (TM1ValType(hUser, vOk) == TM1ValTypeBool() && !TM1ValBoolGet(hUser,vOk)) {
		//...
		throw ...;
	}
	vOk= TM1SubsetSelectionInsertChildren(hPool, hNewSubset);
	if (TM1ValType(hUser, vOk) == TM1ValTypeBool() && !TM1ValBoolGet(hUser,vOk)) {
				//...
		throw ...;
	}
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: View Extract with TM1 API

Post by David Usherwood »

Re: View Extract with TM1 API

Unread post by macsir » Tue Oct 24, 2017 4:47 am
I would recommend to achieve this via REST API which is the future.
I'd second this. As an example, using Marius Wirtz' TM1PY libraries:

Code: Select all

"""
Create MDX View on }ClientGroups cube and query data through it.
IMPORTANT: MDX Views can not be seen through Architect/Perspectives.
"""
import uuid
from TM1py.Objects import MDXView
from TM1py.Services import TM1Service
with TM1Service(address='localhost', port=12354, user='admin', password='apple', ssl=True) as tm1:
    # Random text
    random_string = str(uuid.uuid4())
    # Create mdx view
    mdx = "SELECT " \
          "NON EMPTY {TM1SUBSETALL( [}Clients] )} on ROWS, " \
          "NON EMPTY {TM1SUBSETALL( [}Groups] )} ON COLUMNS " \
          "FROM [}ClientGroups]"
    mdx_view = MDXView(cube_name='}ClientGroups', view_name='TM1py_' + random_string, MDX=mdx)
    # Create mdx view on TM1 Server
    tm1.cubes.views.create(view=mdx_view)
    # Get view content
    content = tm1.cubes.cells.get_view_content(cube_name=mdx_view.cube, view_name=mdx_view.name)
    # Print content
print(content)
See more examples at https://github.com/cubewise-code/TM1py-samples
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

David Usherwood wrote: Tue Oct 24, 2017 3:13 pm
Re: View Extract with TM1 API

Unread post by macsir » Tue Oct 24, 2017 4:47 am
I would recommend to achieve this via REST API which is the future.
I'd second this. As an example, using Marius Wirtz' TM1PY libraries:

Code: Select all

"""
Create MDX View on }ClientGroups cube and query data through it.
IMPORTANT: MDX Views can not be seen through Architect/Perspectives.
"""
import uuid
from TM1py.Objects import MDXView
from TM1py.Services import TM1Service
with TM1Service(address='localhost', port=12354, user='admin', password='apple', ssl=True) as tm1:
    # Random text
    random_string = str(uuid.uuid4())
    # Create mdx view
    mdx = "SELECT " \
          "NON EMPTY {TM1SUBSETALL( [}Clients] )} on ROWS, " \
          "NON EMPTY {TM1SUBSETALL( [}Groups] )} ON COLUMNS " \
          "FROM [}ClientGroups]"
    mdx_view = MDXView(cube_name='}ClientGroups', view_name='TM1py_' + random_string, MDX=mdx)
    # Create mdx view on TM1 Server
    tm1.cubes.views.create(view=mdx_view)
    # Get view content
    content = tm1.cubes.cells.get_view_content(cube_name=mdx_view.cube, view_name=mdx_view.name)
    # Print content
print(content)
See more examples at https://github.com/cubewise-code/TM1py-samples
Yes, that's good idea but basically TM1py is still built on rest api. If you are experienced with programming and want to learn things, better to start with rest api first. If wanting a quick thing, then TM1py is good choice. :D
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: View Extract with TM1 API

Post by David Usherwood »

Re: View Extract with TM1 API

by macsir » Tue Oct 24, 2017 11:38 pm

David Usherwood wrote: ↑
Tue Oct 24, 2017 3:13 pm

Re: View Extract with TM1 API

Yes, that's good idea but basically TM1py is still built on rest api. If you are experienced with programming and want to learn things, better to start with rest api first. If wanting a quick thing, then TM1py is good choice. :D
Absolutely. But you have to use some language to work with the REST API, and TM1PY does all the heavy lifting - and Python has all those juicy libraries to pull in to do things which you can't do with the native tools eg IRR (in numpy), remi (for web frontends), csv, xlwings (excel)... I have seen a SAP library but can't speak for it since I don't have SAP.
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

I am planning to wrap rest api with JAVA and possibly publish it via github too. :D
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: View Extract with TM1 API

Post by David Usherwood »

I am planning to wrap rest api with JAVA and possibly publish it via github too. :D
What a good idea :)
Hubert Heijkers, who 'owns' the REST API, has some routines for Google's Go language, but when I last looked they weren't as comprehensive as TM1PY:
https://github.com/Hubert-Heijkers
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

David Usherwood wrote: Thu Oct 26, 2017 12:52 pm
I am planning to wrap rest api with JAVA and possibly publish it via github too. :D
What a good idea :)
Hubert Heijkers, who 'owns' the REST API, has some routines for Google's Go language, but when I last looked they weren't as comprehensive as TM1PY:
https://github.com/Hubert-Heijkers
Thanks, David. Yes, I know Hubert and I will have a look what he has written to see what I can borrow from there. :D
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
ture
Posts: 9
Joined: Sat Oct 21, 2017 11:40 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: View Extract with TM1 API

Post by ture »

Thank you. It helps me with mdx-view. I can create a mdx-view, find it and use as regular (native) view. But I can not write mdx-query with restrict by subsets or another condition. For example, I write query:

Code: Select all

select 
   {[dim 1].MEMBERS}
   * {[dim 2].Members} 
   * {[dim 3].Members} 
   * {[period].Members}  on COLUMNS, 

   {[dim 4].MEMBERS } 
   * {[dim 5].MEMBERS}         on ROWS 
from
   [cube A]
The dimensuion [period] has values "2017.01.01", "2017.01.02", "2017.01.03",..,"2017.12.30" and "2017.12.31".
And I whant use filter like this:

Code: Select all

TM1FILTERBYPATTERN(TM1SUBSETALL([period]),"2017.03.*")
to select from my mdx-view only data has [period] from "2017.03.01","2017.03.02","2017.03.03",.."2017.03.31".

How can I write this filter?
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

You need WHERE clause after FROM.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
ture
Posts: 9
Joined: Sat Oct 21, 2017 11:40 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: View Extract with TM1 API

Post by ture »

This clause is known to me. And I try to use it like this:

Code: Select all

SELECT                                          
       { TM1SUBSETALL([}Clients]) }  ON COLUMNS, 
       { TM1SUBSETALL([}Groups])  }  ON ROWS
FROM [}ClientGroups]
where ([}Clients].[Admin], [}Groups].[ADMIN])
However when I use:
TM1V hExtractList = TM1ViewExtractCreate(hPool, hObject)
and hExtract = TM1ViewExtractGetNext(hPool, hExtractList)
I upload all content of cube.

What am I doing wrong?
ture
Posts: 9
Joined: Sat Oct 21, 2017 11:40 am
OLAP Product: tm1
Version: 10.2
Excel Version: 2013

Re: View Extract with TM1 API

Post by ture »

Rest API:
POST on https://as-msk-a0134:8000/api/v1/Execut ... xpand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells
Headers:
Accept:text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
Content-Type:application/json; charset=utf-8
Accept-Encoding:gzip, deflate, br
Accept-Language:ru-RU,ru;q=0.8,en-US;q=0.6,en;q=0.4
Authorization:Basic YWRtaW46
Cache-Control:max-age=0
Connection:keep-alive
Cookie:TM1SessionId=pwbEILUfA0Q_OoHLmYC89w
DNT:1
Host:as-msk-a0134:8000
Upgrade-Insecure-Requests:1
User-Agent:Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36

Data:
{"MDX":"SELECT {TM1SUBSETALL([}Clients])} ON COLUMNS, {TM1SUBSETALL([}Groups])} ON ROWS FROM [}ClientGroups] where ([}Clients].[Admin],[}Groups].[ADMIN])"}

Response:

Code: Select all

{
    "error": {
        "code": "",
        "message": "MDX parameter is required and cannot be empty."
    }
}
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: View Extract with TM1 API

Post by macsir »

ture wrote: Thu Nov 02, 2017 8:51 am This clause is known to me. And I try to use it like this:

Code: Select all

SELECT                                          
       { TM1SUBSETALL([}Clients]) }  ON COLUMNS, 
       { TM1SUBSETALL([}Groups])  }  ON ROWS
FROM [}ClientGroups]
where ([}Clients].[Admin], [}Groups].[ADMIN])
However when I use:
TM1V hExtractList = TM1ViewExtractCreate(hPool, hObject)
and hExtract = TM1ViewExtractGetNext(hPool, hExtractList)
I upload all content of cube.

What am I doing wrong?

Code: Select all

SELECT                                          
       { [}Clients].[Admin] }  ON COLUMNS, 
       { [}Groups].[ADMIN]  }  ON ROWS
FROM [}ClientGroups]
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply