Dynamic subset based on a cube value, with another dimension

Post Reply
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Dynamic subset based on a cube value, with another dimension

Post by Elessar »

Hello all,

I have 2 dimensions with N*N mapping: "Department 1" and "Product".
Mapping.png
Mapping.png (18.65 KiB) Viewed 10459 times
For the views with "Department 1" in context and "Products" in rows, I can use the following MDX query for "Products" dynamic subset:
{FILTER({TM1SUBSETALL( [Product] )}, [Mapping Product - Department].([Measures].[Value]) > 0)}


Now I need to create another application with the same approval hierarchy ("Department 2").
Is there a way to write an MDX query for the cubes with "Department 2" dimension based on the Dep1*Product mapping? Or I should create a copy of the mapping cube with "Department 2" dimension?
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Dynamic subset based on a cube value, with another dimension

Post by Drg »

Create new element [Measures].[Value2] solve your problem?
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by Elessar »

Thanks Drg,

This will not help. The problem is because "dimension in a mapping cube" and "dimension in context of budget cube view" are equal, but different (Dep1 and Dep2). When you filter by a cube value in MDX, you do not specify the elements of coincident dimensions (like !Dim in DB). But the dim1 and dim2 are not coincident.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
User avatar
rafaewolie
Posts: 22
Joined: Wed May 11, 2016 2:16 pm
OLAP Product: IBM Cognos TM1
Version: TM1 PA 2.0
Excel Version: Excel 2013
Location: Anywhere, BR
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by rafaewolie »

Hi Elessar,

Where Do You want to put that MDX?

I'm trying to understand Your need just to help You with Your Issue!

For example, If You are creating a dinamic subset, and it's not in any cube, this Will never work, because of this " [Mapping Product - Department].([Measures].[Value]) > 0"

If You create a Department2 dimension, trying to put this MDX inside the subset to turn it dinamic, You will have to create a Mapping Product - Department with that Dimension inside It.

Because of It, I need some more information!

tks
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Dynamic subset based on a cube value, with another dimension

Post by Drg »

Elessar wrote: Mon Sep 18, 2017 1:53 pm Thanks Drg,

This will not help. The problem is because "dimension in a mapping cube" and "dimension in context of budget cube view" are equal, but different (Dep1 and Dep2). When you filter by a cube value in MDX, you do not specify the elements of coincident dimensions (like !Dim in DB). But the dim1 and dim2 are not coincident.
this MDX uses in subset for department1 Right?

Code: Select all

{FILTER({TM1SUBSETALL( [Product] )}, [Mapping Product - Department].([Measures].[Value]) > 0)}
this MDX uses in subset for department2 Right?

Code: Select all

{FILTER({TM1SUBSETALL( [Product] )}, [Mapping Product - Department].([Measures].[Value],[Department2].CurrentMember, [Product].CurrentMember ) > 0)}
But if Department2 element's not in Department1 you need more powerful logic(mapping cube) and your query sames lik this(maybe error in syntaxis):

Code: Select all

{FILTER({TM1SUBSETALL( [Product] )}, [Mapping Product - Department].([Measures].[Value],
	{TM1Member(
		FILTER( [Department2].members , [Mapping Department2 to Department1].( [Measure].[value] , [Department2].CurrentMember)>0).Item(0)
		, 0)
	}
, [Product].CurrentMember ) > 0)}
 
I think it will be very buggy logic, enjoy
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by Elessar »

rafaewolie,
I want to use MDX in Product dimension's subset

In details:
1. I have a mapping cube with dimensions: Department1, Product, Measures, where I can set NxN mapping for departments and products.
I can use this mapping for creating a dynamic subset in Product dimension in any cube view with Department1 in context and Product in rows (cube1):

Code: Select all

 {FILTER({TM1SUBSETALL( [Product] )}, [Mapping Product - Department].([Measures].[Value]) > 0)}

This works fine

2. Now I need to create a cube2 with product and department2 (department1's clone) dimensions.
Product's dynamic subset with the provided MDX obviously does not work in cube2, so I need to clone the mapping cube (with Department2, Product, Measures dimensions) and use it in Product's subset. Is there a way to avoid this clone-cube creation (a proper MDX which will work in Cube2 and will use mapping cube with Dep1)?

Drg,
Thanks, but this won't work because I cannot refer to Department2 dimension from Product dimension's MDX.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by st2000 »

If you need these Dept-2-Products mappings per department, couldn't you create an additional dimension "DeptSlicer" (consisting of elements Dept1, Dept2,...,DeptN)?
Then you just need to incorporate this dimension into your dynamic subset MDX to slice and get the particular mapping that adresses the current department (respectively create the MDX via TI before referencing further on it). SubsetCreatebyMDX combined with 'Expand'-function for the Deptname to refine the MDX-code dynamically could help you doing that dynamic creation of the dept's handcrafted subset.
-----------------------------------
Best regards,
Stefan
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by Elessar »

st2000,

Do you mean that "DeptSlicer"'s elements will be department dimension's names?
If so, I don't understand how this can help me, could you please provide more details? I still can not refer to Department dimension from Product's MDX.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Dynamic subset based on a cube value, with another dimension

Post by Drg »

You can't do this.
If you need filter dimension differently in different cube and use Only one MDX Subset? you need in each cube have system measure element containing dimension for filter.
also need store(maybe as attributes dimension Product) filter mask for each Department(attr1...attrN).


I'm still not sure that this will work stably, if at all
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by st2000 »

Elessar,

my point was to add an additional dim which has the only function to distinguish the particular versions of dept/product-mappings:
Dept_2_Prod_Mapping.png
Dept_2_Prod_Mapping.png (9.18 KiB) Viewed 10177 times
-All_products: the products dim
-All_selling_depts: dim of departments, which are those you want to map to a product
-Contexts_of_depts: Filterdim to distinguish the different mappings
We have something similar: n eCommerce brands are selling their own products which they create and produce or import, but also subsets of the products of their peer brands; changing frequently, but need to be retrievable at a particular point in time. For us, this point of time follows the calendar due to defined seasons, but as I don't know your business, I call this criteria more generic 'context-of-dept'...
-----------------------------------
Best regards,
Stefan
User avatar
tiagoblauth79
Posts: 25
Joined: Fri Aug 26, 2016 1:42 pm
OLAP Product: Cognos BI and TM1
Version: 10.2.2
Excel Version: 10
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by tiagoblauth79 »

Hi Elessar,

Unfortunately, I don't know if it is possible to create a dynamic subset to present the products per selling department because it is a "many to many" combination. I had to do something similar to that. It is a hard and ugly solution but it will work.

1) Create a Department | Product (concatenating the department and product names) dimension based on your "setup" cube (Dept 1-Product 11; Dept 1-Product 12; Dept 1-Product 13; Dept 2-Product 11; and so on). Try to make less ugly by creating an alias;
2) Create the mdx as the attached image
Attachments
DynamicSubset.jpg
DynamicSubset.jpg (123.51 KiB) Viewed 10166 times
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: Dynamic subset based on a cube value, with another dimension

Post by st2000 »

I meant this style (amended assigments for demo):
N2N-SlicerDim-MDX.png
N2N-SlicerDim-MDX.png (70.2 KiB) Viewed 10122 times
But I think, the solution from tiagoblauth79 is much more elegant using attributes. I will keep this in mind for my own purposes too.
Thanks, Tiago :-)
-----------------------------------
Best regards,
Stefan
Post Reply