Dynamic subset based on a cube value, with another dimension

Post Reply
User avatar
Elessar
Posts: 103
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Dynamic subset based on a cube value, with another dimension

Post by Elessar » Mon Sep 18, 2017 10:28 am

Hello all,

I have 2 dimensions with N*N mapping: "Department 1" and "Product".
Mapping.png
Mapping.png (18.65 KiB) Viewed 572 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?
Sorry for my English ;)

Drg
Posts: 64
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 » Mon Sep 18, 2017 10:37 am

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

User avatar
Elessar
Posts: 103
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

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

Post by Elessar » 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.
Sorry for my English ;)

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 » Mon Sep 18, 2017 5:51 pm

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
Posts: 64
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 » Wed Sep 20, 2017 7:15 am

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
Posts: 103
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

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

Post by Elessar » Fri Sep 22, 2017 8:57 am

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.
Sorry for my English ;)

st2000
Posts: 58
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 » Fri Sep 22, 2017 9:36 am

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
Posts: 103
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

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

Post by Elessar » Wed Sep 27, 2017 10:19 am

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.
Sorry for my English ;)

Drg
Posts: 64
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 » Fri Sep 29, 2017 7:25 am

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: 58
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 » Mon Oct 09, 2017 1:58 pm

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 290 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 » Mon Oct 09, 2017 7:00 pm

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 279 times

st2000
Posts: 58
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 » Wed Oct 11, 2017 12:29 pm

I meant this style (amended assigments for demo):
N2N-SlicerDim-MDX.png
N2N-SlicerDim-MDX.png (70.2 KiB) Viewed 235 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