Hello, I want to write MDX query to create a dynamic subset on time dimension
Time dimension element could be a Date or Week number
According to me, one of the way is to create an attribute for each of these elements and filter elements based on attribute value.
Is there any other way to create this subset where I can provide two dates or two week numbers as parameters and all the elements in this range provided will be part of the subset ?
Thanks
Rtel
Dynamic subsets on Time dimension
-
- MVP
- Posts: 3120
- 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 subsets on Time dimension
Typically, you would have a (small) input cube where the parameter values are entered (week numbers).
Then, an MDX query would filter on the elements. Yes, an attribute would be a good idea. If you take up the week number, formatted ww, in the attribute, this would not be terribly difficult. Getting the values from the cubes might not be easy though.
Maybe you would be better off by using a TI process, to either set up the MDX query (and use a syntax that is simpler to retrieve the parameter values), or loop through the dimension and fill the subset in a static way. If you are new to all this, use the last approach and gradully step up to the other more dynamic methods.
Then, an MDX query would filter on the elements. Yes, an attribute would be a good idea. If you take up the week number, formatted ww, in the attribute, this would not be terribly difficult. Getting the values from the cubes might not be easy though.
Maybe you would be better off by using a TI process, to either set up the MDX query (and use a syntax that is simpler to retrieve the parameter values), or loop through the dimension and fill the subset in a static way. If you are new to all this, use the last approach and gradully step up to the other more dynamic methods.
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
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
-
- Posts: 12
- Joined: Tue Oct 02, 2018 5:35 am
- OLAP Product: PAL
- Version: PAL 2.0.4
- Excel Version: excel 2010
- Contact:
Re: Dynamic subsets on Time dimension
1.Create Parameter Cube(Dim:Parameter{Timestart,TimeEnd},Measure:Measure)
2.Fill in Timestart and TimeEnd
3.Use this MDXQuery to create MDXSubset
4.Finally , you can use cell to control this Dynamic subsets
MDXQuery:
{
StrToMember("[Time].[" +[Parameter].([Parameter].[Timestart],[Measure].[String])+"]")
:
StrToMember("[Time].[" +[Parameter].([Parameter].[TimeEnd],[Measure].[String])+"]")
}
2.Fill in Timestart and TimeEnd
3.Use this MDXQuery to create MDXSubset
4.Finally , you can use cell to control this Dynamic subsets
MDXQuery:
{
StrToMember("[Time].[" +[Parameter].([Parameter].[Timestart],[Measure].[String])+"]")
:
StrToMember("[Time].[" +[Parameter].([Parameter].[TimeEnd],[Measure].[String])+"]")
}
-
- Posts: 59
- Joined: Tue Nov 13, 2018 10:15 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Dynamic subsets on Time dimension
Thank you Win for the suggestions. It helps me improve of solution designs. I will try some of those methods
Thank you Howard, I have generalized your MDX statement. I hope i understood them correctly.
------------------------------------------------------------------------------------------
Hardcoding element name
{
strToMember('[Dimension1].[element_1]')
:
strToMember('[Dimension1].[element_N]')
}
-------------------------------------------------------------------------------------------
Reading values from a cube
{
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_1]
+]')
:
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_2]
+]')
}
---------------------------------------------------------------------------------------------
Question: If I want to use a variable (named svElement_1 and svElement_N) in Ti process, can I write as follows ?
subsetcreatebyMDX ( Mysubset,
{
strToMember('[Dimension1].[' +
svElement1
+]')
:
strToMember('[Dimension1].[' +
svElement_N
+ ]')
}
)
---------------------------------------------------------------------------------------------
Thank you !
Thank you Howard, I have generalized your MDX statement. I hope i understood them correctly.
------------------------------------------------------------------------------------------
Hardcoding element name
{
strToMember('[Dimension1].[element_1]')
:
strToMember('[Dimension1].[element_N]')
}
-------------------------------------------------------------------------------------------
Reading values from a cube
{
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_1]
+]')
:
strToMember('[Dimension1].[' +
[Cube_A].[Dimension_A].[element_1] , [Cube_A].[Dimension_B].[element_2]
+]')
}
---------------------------------------------------------------------------------------------
Question: If I want to use a variable (named svElement_1 and svElement_N) in Ti process, can I write as follows ?
subsetcreatebyMDX ( Mysubset,
{
strToMember('[Dimension1].[' +
svElement1
+]')
:
strToMember('[Dimension1].[' +
svElement_N
+ ]')
}
)
---------------------------------------------------------------------------------------------
Thank you !
-
- MVP
- Posts: 3120
- 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 subsets on Time dimension
subsetcreatebyMDX ( Mysubset,
‘{
strToMember([Dimension1].[' |
svElement1
| ‘])
:
strToMember([Dimension1].[' |
svElement_N
| ‘])
}’
);
‘{
strToMember([Dimension1].[' |
svElement1
| ‘])
:
strToMember([Dimension1].[' |
svElement_N
| ‘])
}’
);
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
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
-
- Posts: 59
- Joined: Tue Nov 13, 2018 10:15 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Dynamic subsets on Time dimension
Thank you Wim !