Dynamic subset based on a cube value, with another dimension

[Elessar]

Hello all,

I have 2 dimensions with N*N mapping: "Department 1" and "Product".

Mapping.png (18.65 KiB) Viewed 13309 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?

[Drg]

Create new element [Measures].[Value2] solve your problem?

[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.

[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]

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

[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.

[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.

[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.

[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]

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 (9.18 KiB) Viewed 13027 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'...

[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

[st2000]

I meant this style (amended assigments for demo):

N2N-SlicerDim-MDX.png (70.2 KiB) Viewed 12972 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