Format string to date in Cube

[pimogo]

Hi guys- i can't believe this problem has consumed me for several hours. i'm new to cubes.. perhaps you can help....

The Calender dimension contains a field that organizes week dates like so, 'yyyymmdd'. i need to convert that to a field so i can evaluate further using the built_in cognos function _week_of_year.  i've tried cast([week date field], date) among others but to no avail... i keep getting errors.

is there a special technique one must use when working with cubes?

[CognosPaul]

Cubes don't have any native support for casting. Using the built in Cognos functions aren't recommended as that would force the resultset to be processed locally.

There's a fairly steep learning curve when it comes to working with multi-dimensional functions. For something like this your best bet is to modify the cube to include the week of year as an attribute of the field.

You might also find another way of accomplishing what you're after. If your hierarchy is Year --> Month --> Week --> Day you could count the number of weeks until the current week member. If it's just to format your report you could use report functions.

So a few questions: What data source are you using (Power Cubes, SSAS, EssBase)? Which version of Cognos? And what are you trying to accomplish?

[pimogo]

Hi Paul. Many thanks for your informative response! I'm using Cognos 10 and Power Cubes as my source.

  WHat i'm trying to do (and have successfully done using a standard relational database) is to create a crosstab that shows growth percentage by week over this week last year. That is why in my earlier approach i used the week_of_year function in cognos and was hoping to carry that same logic over to cubes. Needless to say, given my inexperience, this is causing me quite a bit of grief.

[CognosPaul]

This is one of the things that is extremely easy to do with cubes, and complicated to do with relational. In SQL you'd probably have to use two queries and join the two based on week of year. In MDX you simply divide the tuple of (week,value) by (week last year,value).

To make things simple create the following crosstab:

Rows: Weeks
Columns: Measure

If it's not already, set "Has Fact Cells" to Yes.

Now drag in a query calculation to the right of your measure with the following expression:

Code Select Expand

tuple([Measure],parallelPeriod([Cube].[Time].[Time].[Year],1,currentMember([Cube].[Time].[Time])))
The parallelPeriod will return the member at the same index, and at the same level of the member you're referencing (the currentMember of the hierarchy) against the selected level 1 period back. So if it's Week 5/2005 it will return Week 5/2004.

Now run the report. You should see Weeks in the rows, and two columns worth of data. The first column will show the data for that week, the second will show the data for that week of the previous year. Then it's a very simple matter to show the growth.

[pimogo]

Awesome! It worked--i got the right numbers sequentially for the past year. One question, however, is it possible to shift up or down a week. I'm comparing my numbers by week and its aligning as such... the numbers are in the correct order, but i'm thinking that 2010-01-08 should align up with 2011-01-07. I may be wrong, but the option would be nice. 

   Present Year      Past Year
20100101   6539   20110107   6555
20100108   6838   20110114   6640
20100115   6877   20110121   6600
20100122   7023   20110128   6609
20100129   7241   20110204   6508
20100205   7107   20110211   6449
20100212   6733   20110218   6467

Again many thanks! I'm truly appreciative as i've been spinning my wheels for a while now!

[pimogo]

Also how does one filter crosstab to show only the latest 7 weeks?

[pimogo]

nvm. i got it to work. i changed the parallelperiod to week like such..

Code Select Expand

tuple([# CY Job Orders] , parallelPeriod([Cube].[Calendar].[Calendar].[Week-ending Date (Fri)],52, currentMember([Cube].[Calendar].[Calendar] )))

The last couple of things i need to get to work are as follows:

1. The Subtotaling and Grand Totaling is trying to add my percentages instead of recomputing.

2. I need to limit the final crosstab to only display the most recent 7 weeks.

Again, many thanks!

[CognosPaul]

To fix the subtotaling, simply change the solve order on the calculation to 2.

About the 7 weeks thing. It's important to remember that you're not filtering the entire query. One of the ways that I like to explain multi-dimensional queries is that each data item is it's own mini-query. You write a mini-query to return a set of members, and based on that set of members you can find the values that you need.

The hard part will be determining what the current week member is. Since you're using a powercube you should have a hierarchy in your time dimension called Current Week. If you're using Cognos 8.4 or greater you have a function called linkMember. This will allow you to take the member from the Current Week hierarchy and find the corresponding member in the full time hierarchy. Once you have the correct member in the time hierarchy it's a simple matter of using the lastPeriods function to return a set of 7 members.

The following expression was built on the Sales and Marketing cube:

Code Select Expand

lastPeriods (7,linkMember (item([sales_and_marketing].[Time].[Current Month].[Month],0),[sales_and_marketing].[Time].[Time].[Month]))

Item returns the first (and only) member in the Current Month -> Month level, linkMember returns the member from Time -> Month level, and lastPeriods returns the a set of 7 members ending with the current month.

[CognosPaul]

Another thing. 

When working with cubes the labels needs to come from the members or member attributes. With powercubes you can set the short name and descriptions to whatever you need. With SSAS you can have as many attributes as you need. 


MDX does not have any type casting or string functions. You cannot, for instance, have an attribute that looks like 'ABC - 123' and use string functions to trim out the text and cast it to int. If necessary you can use report functions to do it on the report. If you need to filter on the 123 you'll have to set it as an attribute of the member. Then you could do something like filter([Level],[Attribute]=123)

[pimogo]

Hi Paul. Again many thanks.

I got the subtotaling to work, but when i go to locate the current week hierarchy, I don't find it.  In fact the only hierarchies I have are Calendar, Year, Quarter, Month, Week-ending Date (fri) (that last one is a custom).

Should the cube be rebuilt to include a current week hierarchy?

Another thing i notice is that one can't limit as one would in a relational environment.. Suppose i'm trying to limit on date, ordinarlily i would do this... [Cube].[Calendar].[Calendar].[Date] between ?BeginDate? and ?EndDate?.

[pimogo]

BOth have been resolved. Many thanks PaulM