Dynamic Subset Expression - EP Access table replication

Post Reply
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Dynamic Subset Expression - EP Access table replication

Post by Mark »

Hi again all,

Facing a challenge re-creating a guide available online for mimicking access tables in EP.

I am wanting to suppress invalid intersections from the display.

Im using the following IBM guide:
http://www-01.ibm.com/support/docview.w ... 0080&aid=1

Specifically im referring to the technique found on the bottom of page 24 through 25.

I think I understand the functions and what they do, essentially im using the filter function to filter the ALL subset of my dimension against my security cube using the current members and its valid combination as a check against 0.

My code placed in the subset editor (right click on dimension/new/subset), tick dynamic and enter expression as follows:
{
FILTER (
{TM1SUBSETALL([2 Active Projects])},
[WIP Access - WIP Active and WIP Inactive].([2 Active Projects].CurrentMember,
[2 WIP Hierarchy].CurrentMember,
[1 Access].[Access_Num] ) > 0)};

'2 Active Projects' is my dimension on the cube im looking to suppress members if the access cube has a value of 0.
'WIP Access - WIP Active and WIP Inactive' is my access cube where I have populated the combinations with a 0 or 1 depending if visibility should be given.
'2 Active Projects' and '2 WIP Hierarchy' are two dimensions of my access cube that are being iterated over using the CurrentMember function.
'1 Access' is my calc dimension where 'Access_Num' is my element with the value (0,1) against it.

Syntactically i seem to be OK. if i purposesly put a extra bracket etc then i get a validation error and cant save the expression.

However, when actually going to apply this subset to the cube (right click the dimension / edit subset / select it from drop down and press OK) i get the following error:
'There was an internal application problem. Try to save your work and exit the application.
Cannot select dynamic subset WIP Projects because of com.ibm.cognos.tm1.TM1Exception: Syntax error at or near: ';}, {}, ALL)} DIMENSION PROPERTIES MEMBE ... ', character position 887'


Note: theres not ~900 odd characters in my expression so ive assumed this error is being thrown from some code behind the scenes.

Server logs dont have a lot more than that:
355296 [63] ERROR 2018-07-06 01:21:42.885 TM1.Mdx.Interface Syntax error at or near: '; DIMENSION PROPERTIES MEMBER_NAME,MEMBE ... ', character position 219
355296 [63] ERROR 2018-07-06 01:21:48.938 TM1.Mdx.Interface Syntax error at or near: '; DIMENSION PROPERTIES MEMBER_NAME,MEMBE ... ', character position 219
355296 [63] ERROR 2018-07-06 01:22:35.442 TM1.Mdx.Interface Syntax error at or near: ';}, {}, ALL)} DIMENSION PROPERTIES MEMBE ... ', character position 887

Details dont mean a lot to me but here is a dump:
com.ibm.mdt.widgets.dialogs.EditSubsetDialog.buttonPressed(EditSubsetDialog.java:156)
com.ibm.cdf.cui.common.CDFDialog.defaultButtonHandler(CDFDialog.java:1023)
com.ibm.cdf.cui.common.CDFDialogAction.run(CDFDialogAction.java:67)
com.ibm.cdf.cui.common.CDFButton.runAction(CDFButton.java:995)
com.ibm.cdf.cui.common.CDFButton.pressUp(CDFButton.java:937)
com.ibm.cdf.cui.common.CDFButton.mouseUp(CDFButton.java:925)
org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:220)
org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)
org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)
org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
org.eclipse.jface.window.Window.open(Window.java:801)
com.ibm.cdf.cui.common.CDFDialog.open(CDFDialog.java:568)
com.ibm.mdt.widgets.cube.MDTCubeGriContextMenuProvider$4.run(MDTCubeGriContextMenuProvider.java:357)
com.ibm.cdf.cui.utilities.popup.CDFBaseMenuItem.runAction(CDFBaseMenuItem.java:661)
com.ibm.cdf.cui.utilities.popup.CDFBaseMenuItem.mouseUp(CDFBaseMenuItem.java:683)
com.ibm.cdf.cui.utilities.popup.CDFBaseMenuItem$2.handleEvent(CDFBaseMenuItem.java:141)
org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
org.eclipse.swt.widgets.Display.filterEvent(Display.java:1262)
org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1052)
org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4169)
org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3758)
org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
com.ibm.mdt.app.Application.start(Application.java:85)
org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:353)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:180)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
java.lang.reflect.Method.invoke(Method.java:620)
org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:629)
org.eclipse.equinox.launcher.Main.basicRun(Main.java:584)
org.eclipse.equinox.launcher.Main.run(Main.java:1438)

A static subset seems to work ok. Creating the subset in Architect yields the same result.

Any pointers would be most helpful as im stuck.
Cheers,
Mark.
Wim Gielis
MVP
Posts: 3103
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: Dynamic Subset Expression - EP Access table replication

Post by Wim Gielis »

Hi Mark,

Is the dimension ‘2 WIP Hierarchy’ part of the cube where you test it ?
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
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Dynamic Subset Expression - EP Access table replication

Post by Mark »

Hi Wim!

Yes, the cube I am applying the subset on contains the following dimensions:
'2 WIP Hierarchy'
'2 Active Projects'
'1 WIP Active Calc'
'4 Period'

Mark.
Wim Gielis
MVP
Posts: 3103
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: Dynamic Subset Expression - EP Access table replication

Post by Wim Gielis »

Try hardcoding:

[2 WIP Hierarchy].CurrentMember

to a valid element and see what happens.
Also, hardcode the [2 Active Projects].CurrentMember to a valid element and test again.
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
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Dynamic Subset Expression - EP Access table replication

Post by Mark »

Wim Gielis wrote: Fri Jul 06, 2018 8:03 am Try hardcoding:

[2 WIP Hierarchy].CurrentMember

to a valid element and see what happens.
Also, hardcode the [2 Active Projects].CurrentMember to a valid element and test again.
Hi Wim, have given the following code blocks a shot.
{
FILTER (
{TM1SUBSETALL([2 Active Projects])},
[WIP Access - WIP Active and WIP Inactive].([2 Active Projects].['0264845-00278933-C010002'],
[2 WIP Hierarchy].CurrentMember,
[1 Access].[Access_Num] ) > 0)};
{
FILTER (
{TM1SUBSETALL([2 Active Projects])},
[WIP Access - WIP Active and WIP Inactive].([2 Active Projects].[0264845-00278933-C010002],
[2 WIP Hierarchy].[KAL - COM - Construction & Maintenance],
[1 Access].[Access_Num] ) > 0)};
{
FILTER (
{TM1SUBSETALL([2 Active Projects])},
[WIP Access - WIP Active and WIP Inactive].([2 Active Projects].CurrentMember,
[2 WIP Hierarchy].[KAL - COM - Construction & Maintenance],
[1 Access].[Access_Num] ) > 0)};
Unfortunately still the same error/result - albeit with a different cursor positioning.

I also tried to wrap the member in quotes e.g. ['KAL - COM - Construction & Maintenance']

Still no difference.

To note, ive created an isolated set of dimensions and access cube and target cube and im still getting the same issue. In my sample ive made sure to have:
- more dimensions in my target cube than my original access lookup cube.
- calculations in my target cube
- mimic the dimension types, e.g. generic/hierarchy/calc dimensions

Im really puzzled now.
Last edited by Mark on Sun Jul 08, 2018 11:23 pm, edited 2 times in total.
Wim Gielis
MVP
Posts: 3103
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: Dynamic Subset Expression - EP Access table replication

Post by Wim Gielis »

Why do you have a semicolon at the end ?
Last edited by Wim Gielis on Mon Jul 09, 2018 5:29 am, edited 1 time in total.
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
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: Dynamic Subset Expression - EP Access table replication

Post by Mark »

Wim Gielis wrote: Sun Jul 08, 2018 11:22 pm Whumy do you have a semicolon at the end ?
Fantastic question, took it out and it applied the subset no problems! Guess I just assumed I had to terminate it like the other statements found on dimensional calcs and the like.

Now to work out why I don't get my expected members to display. Im sure ill be back with more questions shortly :)
Wim Gielis
MVP
Posts: 3103
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: Dynamic Subset Expression - EP Access table replication

Post by Wim Gielis »

Glad it's solved!

You can check out the syntax for MDX statements here:
http://www.wimgielis.com/tm1_mdxstatements_EN.htm
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