TM1 Forum

Posted: **Fri Jun 20, 2008 6:51 am**

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

Posted: **Fri Jun 20, 2008 7:08 am**

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

Posted: **Sat Jun 21, 2008 12:02 am**

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

Posted: **Tue Jul 12, 2011 10:47 am**

any resolution for this? mine still return an index

Posted: **Mon Jul 18, 2011 6:14 am**

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?

Posted: **Mon Jul 18, 2011 5:34 pm**

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

Thank You

Posted: **Mon Oct 23, 2017 9:28 am**

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?

Posted: **Tue Oct 24, 2017 4:47 am**

I would recommend to achieve this via REST API which is the future.

Posted: **Tue Oct 24, 2017 2:48 pm**

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 ...;
	}

Posted: **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

Posted: **Tue Oct 24, 2017 11:38 pm**

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.

Posted: **Wed Oct 25, 2017 8:39 am**

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.

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.

Posted: **Thu Oct 26, 2017 12:45 am**

I am planning to wrap rest api with JAVA and possibly publish it via github too.

Posted: **Thu Oct 26, 2017 12:52 pm**

I am planning to wrap rest api with JAVA and possibly publish it via github too.

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

Posted: **Fri Oct 27, 2017 1:49 am**

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.
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.

Posted: **Wed Nov 01, 2017 7:17 am**

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?

Posted: **Thu Nov 02, 2017 4:42 am**

You need WHERE clause after FROM.

Posted: **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?

Posted: **Thu Nov 02, 2017 9:12 am**

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."
    }
}

Posted: **Fri Nov 03, 2017 1:32 am**

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]