get a value from a measure at a given time | SQL Server Performance Forums

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=’:)‘ />]<br /><br /><br /><br /> <br /><br /><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 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=’:)‘ />]<br /><br /><br /> No, i don’t have the measure stored in the time dimension.<br /> <br /> I have a dimension called "Time" and a measure called "ValSal". My fact table has two column time and valsal.For column 31/05/2004 from fact table corresponds a value for valsal. I want to create a calculated member to display only this value on all levels of dimension "Time".<br /><br /><br /> <br /><br /><br /> <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
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.
]]>