January 26, 2010 at 4:24 pm
I have a dimension that has a date field in it. i would like to create a metric for weekend = yes or no.
But I cannot, for the life of me, figure out how to get the date or a datepart from the value.
I'll try to give enough details below.
The CI field looks like this: 2009-01-04 00:00:00.000
When I try something like this:
Set [CI weekday] as (VBA!Weekday([Comparison Group].[CI].[CI]))
I get:
Execution of the managed stored procedure Weekday failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.
Query (44, 21) The function expects a string or numeric expression for the 1 argument. A level expression was used.
When I try something like this:
MEMBER [Comparison Group].[CI].[CI weekday] as (VBA!Weekday([Comparison Group].[CI].[CI].currentmember))
I get the CI weekday as a column value and an error for the metric.
Obviously, I'm a newbie. Any direction, things to try, hints on how to describe better, etc would be greatly appreciated.
Thanks, Megan
January 27, 2010 at 1:07 am
One approach is to add it before you get to the dimension. So, in the Data Source View, right click the table name and select the option to add a new Named Calculation. Assuming a SQL data source, the calc text will be valid TSQL (so, in this case likely the use of Datepart and a case statement - ie use the TSQL you'd use to create a true/false value in a column in a sql query).
Once you have your new named calc, go to edit the dimension, and add this field as a new Attribute Dimension within the dim - the name could be something like 'Is Weekend' and the values would be 'Yes' and 'No'.
HTH,
Steve.
January 27, 2010 at 11:41 am
Thanks Steve. Do you know if you have to reprocess the entire cube to see this new value? Thanks again. -Megan
January 27, 2010 at 11:56 am
You *should* be able to do a process update on the dimension alone, but not being there and seeing what you've got, I could be wrong and it may require a full reprocess 🙁 One thing you may want to try is the Impact Analysis (*prior* to clicking OK/RUn 🙂 ) to see if it can tell what other objects (such the cube/partitons) will be affected.
Steve.
January 27, 2010 at 12:35 pm
cool. thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply