RestAPI View has less figures Ascii Export

Post Reply
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

RestAPI View has less figures Ascii Export

Post by orlando »

Hi there,

I have a problem with the RestAPI

If I export a view via Ascii Export I get 34960 lines back.

If I query the view via the RestAPI, I get 34013 lines back.

I call the RestAPI via a Post:
https://MyServer.de:8001/api/v1/Cubes(' ... xpand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells

Does anyone have an idea what I am doing wrong?

I work with PA 2.06.

Thanks a lot!
orlando
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: RestAPI View has less figures Ascii Export

Post by Wim Gielis »

Hello,

What kind of cells are contained in the view ? Level 0 / consolidated / string. Rules-calculated or not ? Fed or not ?

Can you make the view smaller to identify the gaps and find a pattern ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: RestAPI View has less figures Ascii Export

Post by orlando »

Hi Wim,

thanks for the reply.
The view contains only Level 0, no strings and the cube has no rules.
I will try to make it smaller and investigate.
This will take a while, a lot of meetings and vacation ahead

Best regards,
orlando
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: RestAPI View has less figures Ascii Export

Post by David Usherwood »

You have a rather good analytics engine available. I suggest you create a new cube with a measure
Ascii
Rest
Difference = Ascii - Rest
and import the two feeds. You should then be able to identify the differences.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: RestAPI View has less figures Ascii Export

Post by Wim Gielis »

Or open up both exports in Excel, one below the other. Make sure the same columns are used in the same order. Then multiply 1 set of values with -1 and create a pivot table. The differences will remain.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: RestAPI View has less figures Ascii Export

Post by kangkc »

Did you re-order the cube dimensions ?
There is a bug in PA (can't recall which release) with Rest API that will cause such issues if the cube has a re-ordered dimensions. Even PAx is facing the same problem.
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: RestAPI View has less figures Ascii Export

Post by orlando »

kangkc wrote: Tue May 21, 2019 1:57 am Did you re-order the cube dimensions ?
There is a bug in PA (can't recall which release) with Rest API that will cause such issues if the cube has a re-ordered dimensions. Even PAx is facing the same problem.
Hi,

i used an VLookUp in Excel to identify the missing rows.
The data doesn't look unusual and yes I re-ordered the dimensions.
We are still in development, so destroy the cube and reorder the dimensions could be an option - maybe

I'm in Ehingen at the "Planning Analytics TM1 Anwendertag" next week - i will ask there and come back with a solution after my vacation.

Thank you all for the ideas!

Best regards
orlando
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: RestAPI View has less figures Ascii Export

Post by kangkc »

orlando wrote: Thu May 23, 2019 12:21 pm Hi,

i used an VLookUp in Excel to identify the missing rows.
The data doesn't look unusual and yes I re-ordered the dimensions.
We are still in development, so destroy the cube and reorder the dimensions could be an option - maybe

I'm in Ehingen at the "Planning Analytics TM1 Anwendertag" next week - i will ask there and come back with a solution after my vacation.

Thank you all for the ideas!

Best regards
orlando
You don't need to destroy the cube to re-order.
Re-ordering the cube back to the original dimension order can be on the fly without re-creating the cube.
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: RestAPI View has less figures Ascii Export

Post by orlando »

kangkc wrote: Fri May 24, 2019 12:56 am
You don't need to destroy the cube to re-order.
Re-ordering the cube back to the original dimension order can be on the fly without re-creating the cube.
Thanks for the tip
I wouldn't have thought that TM1 would recognize this.
Strange bug.

I will talk to them on tuesday - and come back with the answer.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: RestAPI View has less figures Ascii Export

Post by lotsaram »

I will also be at the Anwendertag next week. If you are interested in re-ordering (aka "cube optimization") and what it does I recommend you read these posts.
what is cube optimization?
how to find optimal dimension order
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: RestAPI View has less figures Ascii Export

Post by orlando »

lotsaram wrote: Fri May 24, 2019 7:40 am I will also be at the Anwendertag next week. If you are interested in re-ordering (aka "cube optimization") and what it does I recommend you read these posts.
what is cube optimization?
how to find optimal dimension order
hi,
thanks for the links.

See you in Ehningen
Post Reply