Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

get weekday in mdx Expand / Collapse
Author
Message
Posted Tuesday, January 26, 2010 4:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:41 AM
Points: 953, Visits: 417
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



Post #854034
Posted Wednesday, January 27, 2010 1:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Monday, July 14, 2014 10:41 PM
Points: 1,814, Visits: 3,453
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.
Post #854218
Posted Wednesday, January 27, 2010 11:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:41 AM
Points: 953, Visits: 417
Thanks Steve. Do you know if you have to reprocess the entire cube to see this new value? Thanks again. -Megan


Post #854613
Posted Wednesday, January 27, 2010 11:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Monday, July 14, 2014 10:41 PM
Points: 1,814, Visits: 3,453
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.
Post #854625
Posted Wednesday, January 27, 2010 12:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:41 AM
Points: 953, Visits: 417
cool. thanks!


Post #854656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse