Using Dimensional Functions and Attributes on Relative Time Members

[gpollock]

I've been working on a complicated report and have spent some time trying to figure out how to use some functions/attributes on the relative time dimensions.  Since I didn't see anything, I thought I'd add my findings in the hope it saves someone else the trouble.  This is for a dynamic cube on 10.2.2.  As always, if someone has something to add, please do.

The gist is if you're ever working on a report that uses relative members, and you need to use dimensional functions, or hierarchy level attributes, you may be unable to accomplish your task with whatever the cube design gives you.  child( and currentMember( functions can get you pretty far, but if you ever need to use more on the cube, you can filter a hierarchy level by the business key.

For this example, we have a cube.dimension.hierarchy called [Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day], and a level called [Week].  The current week member is week 35.  In the proper hierarchy, the member of the [Week] level is [Week-35].  Also, there is a relative member [Current Week (Week-35)] that is updated every time the cube is refreshed.  User wants to see some measures for the prior 8 weeks inclusive.  User also wants to nest the days within those weeks.

Grabbing the last 8 weeks is simple if you drag the hierarchy member, but then you'd have to update the report to grab the new week.

Code Select Expand

lastPeriods(8,[Week-35])

But if you run the same on the relative member, it doesn't return anything.  I found out that these relative members aren't considered a part of the hierarchy, so any level-related functions, including lastPeriods, won't work properly.  Further, if you try to nest members of the day level, or any attributes, the report won't display, for the same reasons above.

What I found after trial-and-error is that the business key is the only value in common between relative members and their corresponding hierarchy-based members.  In this case, both the above members have a business key of (35).  Using that, I can use a filter function to grab members of the hierarchy level, and then use the rest of the cube's built-in features to do the rest.

So in my query, I created a Set Expression, and used the following text:

Code Select Expand


filter(
    members([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day].[Week])
    ,roleValue('_businessKey'
        ,currentMember([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day]))
    =roleValue('_businessKey',[Current Week (Week-35)])
)


Then, wrap the text in an item function and you can use whatever dimensional functions you want.

Code Select Expand


lastPeriods(8,
    item(
        filter(
            members([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day].[Week])
            ,roleValue('_businessKey'
                ,currentMember([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day]))
            =roleValue('_businessKey',[Current Week (Week-35)])
        )
    ,0)
)


Add that to your crosstab, and you can nest any lower levels, add attributes, etc.  You can also use parallelPeriod to compare the members to last year.

One thing that bothered me was that I wasn't able to make this work with multiple calculated measures--I had to put all the code in a set expression.  I wanted to have a calculated member to just grab the current week, and then reuse it for a set expression, but couldn't figure out why it wasn't working.  I didn't notice any decrease in performance, but the [Week] level only has 52 members (years are compressed).  I imagine if you were working on something with thousands of members, an alternate hierarchy may be necessary.

[tjohnson3050]

Usually dimensional models will include relative time.  Recently I faced a requirement to efficiently calculate the mun for the prior month using a dimensional model with no relative time.  The model was a Cognos DMR model.

The following function uses a prompt macro to query the current date from the report server, then constructs a member unique name (MUN) for the current month.  This assumes a calendar hierarchy with levels for Year, Quarter and Month.  It also assumes the quarter and month business keys are constructed a certain way (YYYYQ) and (YYYYMM).  The macro function timestampMask can return the year and the month from the current timestamp, and a case statement derives the quarter number. Then the dimensional function for previous member (prevMember) returns the prior month.  The good thing about this method is that the function (including the case statement) current month is resolved quickly before the query is generated.

In the function below, the [Namespace].[CalendarDimension].[CalendarHierarchy].[Month]-> portion would obviously need to be replaced by the appropriate names in your model.

prevMember ([Namespace].[CalendarDimension].[CalendarHierarchy].[Month]->[all].
#sb(timestampMask($current_timestamp,'yyyy'))#.
#sb(timestampMask($current_timestamp,'yyyy')+
case timestampMask($current_timestamp,'mm')
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
end)#.
#sb(timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
)

[cognostechie]

I've been working on a complicated report and have spent some time trying to figure out how to use some functions/attributes on the relative time dimensions.  Since I didn't see anything, I thought I'd add my findings in the hope it saves someone else the trouble.  This is for a dynamic cube on 10.2.2.  As always, if someone has something to add, please do.

The gist is if you're ever working on a report that uses relative members, and you need to use dimensional functions, or hierarchy level attributes, you may be unable to accomplish your task with whatever the cube design gives you.  child( and currentMember( functions can get you pretty far, but if you ever need to use more on the cube, you can filter a hierarchy level by the business key.

For this example, we have a cube.dimension.hierarchy called [Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day], and a level called [Week].  The current week member is week 35.  In the proper hierarchy, the member of the [Week] level is [Week-35].  Also, there is a relative member [Current Week (Week-35)] that is updated every time the cube is refreshed.  User wants to see some measures for the prior 8 weeks inclusive.  User also wants to nest the days within those weeks.

Grabbing the last 8 weeks is simple if you drag the hierarchy member, but then you'd have to update the report to grab the new week.

Code Select Expand

lastPeriods(8,[Week-35])

But if you run the same on the relative member, it doesn't return anything.  I found out that these relative members aren't considered a part of the hierarchy, so any level-related functions, including lastPeriods, won't work properly.  Further, if you try to nest members of the day level, or any attributes, the report won't display, for the same reasons above.

What I found after trial-and-error is that the business key is the only value in common between relative members and their corresponding hierarchy-based members.  In this case, both the above members have a business key of (35).  Using that, I can use a filter function to grab members of the hierarchy level, and then use the rest of the cube's built-in features to do the rest.

So in my query, I created a Set Expression, and used the following text:

Code Select Expand


filter(
    members([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day].[Week])
    ,roleValue('_businessKey'
        ,currentMember([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day]))
    =roleValue('_businessKey',[Current Week (Week-35)])
)


Then, wrap the text in an item function and you can use whatever dimensional functions you want.

Code Select Expand


lastPeriods(8,
    item(
        filter(
            members([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day].[Week])
            ,roleValue('_businessKey'
                ,currentMember([Sales and Operation Metrics].[Calendar YOY].[Quarter > Month> Week > Day]))
            =roleValue('_businessKey',[Current Week (Week-35)])
        )
    ,0)
)


Add that to your crosstab, and you can nest any lower levels, add attributes, etc.  You can also use parallelPeriod to compare the members to last year.

One thing that bothered me was that I wasn't able to make this work with multiple calculated measures--I had to put all the code in a set expression.  I wanted to have a calculated member to just grab the current week, and then reuse it for a set expression, but couldn't figure out why it wasn't working.  I didn't notice any decrease in performance, but the [Week] level only has 52 members (years are compressed).  I imagine if you were working on something with thousands of members, an alternate hierarchy may be necessary.

Interesting way to do something in a complicated manner instead of doing it in a cube in a simple manner !

[gpollock]

Thanks for your feedback, TJ.  The issue I was working through is that relative time is present, but that Cognos wasn't considering our relative time members as part of the hierarchy proper.  I don't have access to the cube design so I don't know if the designer missed something, but I found our relative time (e.g. current month) had children for week and days, lastPeriods(, parallelPeriods(, etc. weren't returning the expected members.