View Extract with TM1 API
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
View Extract with TM1 API
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
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: View Extract with TM1 API
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: View Extract with TM1 API
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
-
- 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
any resolution for this? mine still return an index
-
- 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
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?
-
- 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
you can post any language, C++ is fine with me.
Thank You
Thank You
Re: View Extract with TM1 API
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?
How can I set an filter? Should I create a new View with subsets?
- 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
I would recommend to achieve this via REST API which is the future.
Re: View Extract with TM1 API
How can I select and insert at subset all elements of a dimension whose names match a regular expression?
It does not work:
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 ...;
}
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: View Extract with TM1 API
I'd second this. As an example, using Marius Wirtz' TM1PY libraries: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.
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)
- 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
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.David Usherwood wrote: ↑Tue Oct 24, 2017 3:13 pmI'd second this. As an example, using Marius Wirtz' TM1PY libraries: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.See more examples at https://github.com/cubewise-code/TM1py-samplesCode: 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)
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: View Extract with TM1 API
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.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.
- 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
I am planning to wrap rest api with JAVA and possibly publish it via github too.
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: View Extract with TM1 API
What a good ideaI am planning to wrap rest api with JAVA and possibly publish it via github too.
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
- 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
Thanks, David. Yes, I know Hubert and I will have a look what he has written to see what I can borrow from there.David Usherwood wrote: ↑Thu Oct 26, 2017 12:52 pmWhat a good ideaI am planning to wrap rest api with JAVA and possibly publish it via github too.
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
Re: View Extract with TM1 API
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:
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:
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?
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]
And I whant use filter like this:
Code: Select all
TM1FILTERBYPATTERN(TM1SUBSETALL([period]),"2017.03.*")
How can I write this filter?
- 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
You need WHERE clause after FROM.
Re: View Extract with TM1 API
This clause is known to me. And I try to use it like this:
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
{ TM1SUBSETALL([}Clients]) } ON COLUMNS,
{ TM1SUBSETALL([}Groups]) } ON ROWS
FROM [}ClientGroups]
where ([}Clients].[Admin], [}Groups].[ADMIN])
TM1V hExtractList = TM1ViewExtractCreate(hPool, hObject)
and hExtract = TM1ViewExtractGetNext(hPool, hExtractList)
I upload all content of cube.
What am I doing wrong?
Re: View Extract with TM1 API
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:
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."
}
}
- 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
ture wrote: ↑Thu Nov 02, 2017 8:51 am This clause is known to me. And I try to use it like this:However when I use:Code: Select all
SELECT { TM1SUBSETALL([}Clients]) } ON COLUMNS, { TM1SUBSETALL([}Groups]) } ON ROWS FROM [}ClientGroups] where ([}Clients].[Admin], [}Groups].[ADMIN])
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]