SQL Server Performance Forum – Threads Archive
get a value from a measure at a given time
I have a dimension Time with YearMonthDay and a measure ValSal.I want to retrive a value from measure ValSal at 31/05/2005 and use it in a calculated member, but i don’t know how?
Any help is appreciated.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vulcanoro</i><br /><br /><br /><br /> I have a dimension Time with YearMonthDay and a measure ValSal.<br /> I want to retrive a value from measure ValSal at 31/05/2005 and use it in a calculated member, but i don’t know how?<br /> <br /><br /> Any help is appreciated.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br /> <br />I don’t really follow you are you saying you have the measure stored in the time Dimension??<br /><br />WITH<br />MEMBER [MEASURE].[NEW_CALCMEMBER] AS<br />’create your calculated member here'<br /><br />SELECT [DATE_LEVEL].MEMBERS ON COLUMNS<br /><br />[MEASURES].ALLMEMBERS ON ROWS<br /><br />FROM CUBE_NAME<br /><br />[MEASURES].ALLMEMBERS Will return all your measures included the new calculated measure.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Lazy_DBA</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vulcanoro</i><br /><br /><br /><br /> I have a dimension Time with YearMonthDay and a measure ValSal.<br /> I want to retrive a value from measure ValSal at 31/05/2005 and use it in a calculated member, but i don’t know how?<br /> <br /><br /> Any help is appreciated.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br /> <br />I don’t really follow you are you saying you have the measure stored in the time Dimension??<br /><br />WITH<br />MEMBER [MEASURE].[NEW_CALCMEMBER] AS<br />’create your calculated member here'<br /><br />SELECT [DATE_LEVEL].MEMBERS ON COLUMNS<br /><br />[MEASURES].ALLMEMBERS ON ROWS<br /><br />FROM CUBE_NAME<br /><br />[MEASURES].ALLMEMBERS Will return all your measures included the new calculated measure.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
What function exactly do you want the calculated member to perform?
quote:Originally posted by Lazy_DBA
What function exactly do you want the calculated member to perform?
I want to display,in the new calculated memeber, only the value on 31/05/2004, i don’t want a function.
Hello vulcanoro, Imagine: do you want the value of "ValSal", at 31/05/2005, ok? You must want use it as a constant value (i think). This is provide by other LEVEL of DW: data stage area. Mr. Kimball, in your book "The Data Warehouse Toolkit" wrotes it. So, hand on: create a nem column in your fact table. Put the value from 31/05/2005 (in all table, the same value). Rebuild the cube. Remember: you must use the function AVG when you need request this value.
]]>