Need to create an MDX or filter results from a cube view using TM1 Rest

Post Reply
shaselai
Posts: 2
Joined: Thu Aug 13, 2020 5:55 pm
OLAP Product: TM1
Version: 10.x
Excel Version: x

Need to create an MDX or filter results from a cube view using TM1 Rest

Post by shaselai »

So i have a cube view "Food" from Cube "Restaurant" and I want the TM1 Rest call to return all the members under that view with Attribute ProductCategory = "what i provide".

I have the following setup:
api/v1/Cubes('Restaurant')/Views('Food')/tm1.Execute?expand=Cells($filter=Value ne null), Axes($expand=Tuples($expand=Members($select=Name, Attributes,Hierarchy)))

This returns 90 Members with Attributes (dozen of them) for this view. However, there are only 2 members with Attribute ProductCategory eq "drink". I just want the result to return 2 records that matches the "drink" category and have all the attributes returned with it.

Anyone can recommend how to do this? I know I can do MDX but thats a different TM1 rest call and i don't know how to combine it with a specific cube view...

Thanks!
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Need to create an MDX or filter results from a cube view using TM1 Rest

Post by paulsimon »

Hi

It might be possible to do this using the Rest API alone by somehow filtering the results returned by the Native View. However, it might take you a while some help from Hubert to do that one.

Alternatives are

a) Execute an MDX Query - more complex MDX queries can use sub queries a bit like in SQL - but I don't know that you would necessarily need this. The DIMENSION PROPERTIES can get attribute values in the ROWS or COLUMNS and can be referenced in the WHERE clause for the selection.
b) Modify your Native TM1 View to use an MDX Subset to do the filtering. However, if you are trying to do some sort of selection where a user can select the attribute value that they want to filter then this approach is not going to work well. Potentially you can make the MDX in the subset and reference a value in a cube. However, that will not work in a multi-user scenario.
c) Use an Active Form / Dynamic Report. That will allow you to use an MDX expression on the Rows to limit things down. That MDX statement can take selections from eg a SUBNM on the }ElementAttributes dimension and you can use normal EXCEL text functions to make the desired statement. I am not sure why you would want to return all attributes though. Surely you would only want a subset of them?

It occurs to me that you seem to be trying to use TM1 like a relational database.

If I wanted to see all combinations with a certain attribute value I would just create consolidations for each attribute value and link the elements to that consolidation. That way you can get to any element that has a given attribute value just by drilling down. I do this sort of thing all the time. Build a dimension and store attributes, then do a second pass to read attributes and build additional hierarchies.

Regards

Paul Simon
shaselai
Posts: 2
Joined: Thu Aug 13, 2020 5:55 pm
OLAP Product: TM1
Version: 10.x
Excel Version: x

Re: Need to create an MDX or filter results from a cube view using TM1 Rest

Post by shaselai »

Thanks for the response!
So I am creating a webpage to filter the result - the user selects a "view" then an ID so I call TM1 Rest to retrieve only that set of information. I already have a dozen or so views created from the main cube. My current alternative is to just retrieve the entire data set from a view then filter the results in Java myself, however, that's obviously not ideal.

I have done MDX before but not on a cube with a view. Can you provide an example on how i can do this on my cube with view? It seems Option A is the best approach.


I also tried to add "$filter=" in there too at the Attribute level but that didnt work either. I thought maybe since the hierarchy is Axes->Tuples->Members->Attributes I could filter that way.. maybe my syntax is off?
Thanks!
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Need to create an MDX or filter results from a cube view using TM1 Rest

Post by paulsimon »

Hi

In this case you don't use a Native View. You use an MDX query which is superficially like a SQL statement but not quite the same

An example on the sdata sample database is

SELECT {[actvsbud].[Actual],[actvsbud].[Budget]} on columns ,
NON EMPTY CROSSJOIN(
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [region] )}, "Europe")}, {[region].[Europe]} ) } ,
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [account2] )} , "Earnings before Taxes")}, {[account2].[Earnings before Taxes]} ) } ) DIMENSION PROPERTIES [account2].[Description] on rows
FROM PNLCube
WHERE month.Feb

This will produce a grid with Actual and Budget on the columns from the actvsbud dimension

On the rows it will have every element under Europe and nested within that every account below Earnings before Taxes. The NON EMPTY bit is counter-intuitive but means zero suppress on rows. The DIMENSION PROPERTIES has to come after all the CROSSJOIN, just before it says 'on rows'. You need to give the name of the dimension dot the attribute name that you want to retrieve.

The WHERE clause means retrieve the data for month February only.

I suggest you do a search for TM1 MDX Primer, which is a good intro. The other great way to learn some MDX is to use PAX which lets you examine the MDX that it generates.

To submit the MDX via the Rest API you will need to use ExecuteMDX

Regards

Paul Simon
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: Need to create an MDX or filter results from a cube view using TM1 Rest

Post by Wim Gielis »

paulsimon wrote: Thu Jan 21, 2021 11:12 pmSELECT {[actvsbud].[Actual],[actvsbud].[Budget]} on columns ,
NON EMPTY CROSSJOIN(
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [region] )}, "Europe")}, {[region].[Europe]} ) } ,
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [account2] )} , "Earnings before Taxes")}, {[account2].[Earnings before Taxes]} ) } ) DIMENSION PROPERTIES [account2].[Description] on rows
FROM PNLCube
WHERE month.Feb
For those who revisit this topic.
The syntax, after copy/paste, is not accepted in PAfE.

It works when I modify:
  • cube name
  • where clause
  • dimension properties

Code: Select all

SELECT {[actvsbud].[Actual],[actvsbud].[Budget]} on columns ,
NON EMPTY CROSSJOIN(
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [region] )}, "Europe")}, {[region].[Europe]} ) } ,
{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [account2] )} , "Earnings before Taxes")}, {[account2].[Earnings before Taxes]} ) } ) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, [account2].[account2].[Description] ON 1
FROM [PNLCube]
WHERE [month].[month].[Feb]
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
Post Reply