Currency or Double preferred?

I'm wondering whether it's better to choose Currency or Double for measure datatypes if the aggregated values will fit inside either datatype. Thoughts? Which would be smaller in terms of storage? If the number gets real big will either Currency or Double lose precision? Are rounding errors more prevalent with one rather than the other?

I have read the following pages but can't seem to make heads or tails of it in terms of best practices:

http://msdn2.microsoft.com/en-us/library/ms129408.aspx

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

http://msdn2.microsoft.com/en-us/library/678hzkk9(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/364x0z75(VS.80).aspx

There is no Double in SQL Server so you have to use either Currency or Decimal or you may have to do conversion before sending the value to SQL Server. Try the link below for the SQL Server Types, ADO.NET types and .NET types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

|||

Caddre, thanks for the reply. That was a good link if you're doing SQLCLR stuff.

The Double SSAS datatype is equivalent to the float datatype in SQL Server. The Currency SSAS datatype is equivalent to the money datatype in SQL Server. Regardless, I'm not interested in SQL Server datatypes, just Analysis Services.

The question still remains, which datatype do you choose in SSAS if either will work: Currency or Double?

|||

Float is not a data type for persistence in any layer of SQL Server because of known precision issues in all programming languages. And my reply was related to your posted links which are clr related.

|||

Hi Furmangg,

If you have to store financial information in your cube and your database stores it as money(currency) you should definitely use currency data type for your mesures. Operations with the currency data type take less CPU power (no FPU calculations) and moreover will be better comressed by the storage engine.

Also if your information could be stored in currency data type (from -922,337,203,685,477.5808 to 922,337,203,685,477.5807) you schould take this type for your measures.

Best regards,

Vladimir Chtepa

|||

Thanks Vladimir. That's the answer I was looking for. I appreciate it.

Ctype from string to double not behaving as expected.

I tried posting this earlier, but it never showed up. Hopefully I don't
double post.
I was writing code for a custom dll earlier this morning where I ran into
some strange behavior for the IsNumeric function.
IsNumeric("$1,0562.00") was returning false
I thought that maybe this behavior was being cause by one of the "features"
of the IsNumeric function so I decided to do some more testing.
I created a web app on the same machine and ran the same code there
IsNumeric("$1,0562.00") was returning true with the web app.
I delved a little deeper and decided to research just what all IsNumeric
does to check for a numeric value. I didn't have to go far.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
states
"It also returns True if Expression is a String that can be successfully
converted to a Double"
So I decided to test the following code in each environment.
ctype("$1,0562.00",double)
Sure enough within the web app it worked fine, but within a reporting
services custom dll it threw an exception.
Both the instance of reporting services and the webapp are being run and
compiled on the same machine. They are both running under the 1.1 framework.
It would make more sense to me that the code didn't behave the way I thought
it should if it was consistently misbehaving. :-)
It's quite possible there is something I'm missing here, but I'm just trying
to figure out the reason for the inconsistency.
I'm running Reporting Services 2003 with sp2 installed.
Anyone have any ideas?
Thanks,
NathanThat is odd. I tried in SSRS 2005 preview window just for giggles, returned
true as expected.
=IsNumeric("$1,0562.00")
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>I tried posting this earlier, but it never showed up. Hopefully I don't
> double post.
> I was writing code for a custom dll earlier this morning where I ran into
> some strange behavior for the IsNumeric function.
> IsNumeric("$1,0562.00") was returning false
> I thought that maybe this behavior was being cause by one of the
> "features"
> of the IsNumeric function so I decided to do some more testing.
> I created a web app on the same machine and ran the same code there
> IsNumeric("$1,0562.00") was returning true with the web app.
> I delved a little deeper and decided to research just what all IsNumeric
> does to check for a numeric value. I didn't have to go far.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> states
> "It also returns True if Expression is a String that can be successfully
> converted to a Double"
> So I decided to test the following code in each environment.
> ctype("$1,0562.00",double)
> Sure enough within the web app it worked fine, but within a reporting
> services custom dll it threw an exception.
> Both the instance of reporting services and the webapp are being run and
> compiled on the same machine. They are both running under the 1.1
> framework.
> It would make more sense to me that the code didn't behave the way I
> thought
> it should if it was consistently misbehaving. :-)
> It's quite possible there is something I'm missing here, but I'm just
> trying
> to figure out the reason for the inconsistency.
> I'm running Reporting Services 2003 with sp2 installed.
> Anyone have any ideas?
> Thanks,
> Nathan
>|||Perhaps this was something that was fixed with 2005? Although I'm still not
ruling out that I'm missing something. When you say in the preview window do
you mean you used â'ctype("$1,0562.00",double)â' in the custom code window
there? Iâ'll have to give that a try and see if it has the same behavior as
calling it within a custom dll.
"Steve MunLeeuw" wrote:
> That is odd. I tried in SSRS 2005 preview window just for giggles, returned
> true as expected.
> =IsNumeric("$1,0562.00")
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
> >I tried posting this earlier, but it never showed up. Hopefully I don't
> > double post.
> >
> > I was writing code for a custom dll earlier this morning where I ran into
> > some strange behavior for the IsNumeric function.
> > IsNumeric("$1,0562.00") was returning false
> > I thought that maybe this behavior was being cause by one of the
> > "features"
> > of the IsNumeric function so I decided to do some more testing.
> > I created a web app on the same machine and ran the same code there
> > IsNumeric("$1,0562.00") was returning true with the web app.
> >
> > I delved a little deeper and decided to research just what all IsNumeric
> > does to check for a numeric value. I didn't have to go far.
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> > states
> > "It also returns True if Expression is a String that can be successfully
> > converted to a Double"
> >
> > So I decided to test the following code in each environment.
> > ctype("$1,0562.00",double)
> > Sure enough within the web app it worked fine, but within a reporting
> > services custom dll it threw an exception.
> >
> > Both the instance of reporting services and the webapp are being run and
> > compiled on the same machine. They are both running under the 1.1
> > framework.
> > It would make more sense to me that the code didn't behave the way I
> > thought
> > it should if it was consistently misbehaving. :-)
> > It's quite possible there is something I'm missing here, but I'm just
> > trying
> > to figure out the reason for the inconsistency.
> >
> > I'm running Reporting Services 2003 with sp2 installed.
> >
> > Anyone have any ideas?
> >
> > Thanks,
> >
> > Nathan
> >
>
>|||I just entered it as an expression in a textbox and clicked the Preview tab
in the Report Designer (VS IDE)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
> Perhaps this was something that was fixed with 2005? Although I'm still
> not
> ruling out that I'm missing something. When you say in the preview window
> do
> you mean you used "ctype("$1,0562.00",double)" in the custom code window
> there? I'll have to give that a try and see if it has the same behavior
> as
> calling it within a custom dll.
> "Steve MunLeeuw" wrote:
>> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> returned
>> true as expected.
>> =IsNumeric("$1,0562.00")
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >I tried posting this earlier, but it never showed up. Hopefully I don't
>> > double post.
>> >
>> > I was writing code for a custom dll earlier this morning where I ran
>> > into
>> > some strange behavior for the IsNumeric function.
>> > IsNumeric("$1,0562.00") was returning false
>> > I thought that maybe this behavior was being cause by one of the
>> > "features"
>> > of the IsNumeric function so I decided to do some more testing.
>> > I created a web app on the same machine and ran the same code there
>> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >
>> > I delved a little deeper and decided to research just what all
>> > IsNumeric
>> > does to check for a numeric value. I didn't have to go far.
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> > states
>> > "It also returns True if Expression is a String that can be
>> > successfully
>> > converted to a Double"
>> >
>> > So I decided to test the following code in each environment.
>> > ctype("$1,0562.00",double)
>> > Sure enough within the web app it worked fine, but within a reporting
>> > services custom dll it threw an exception.
>> >
>> > Both the instance of reporting services and the webapp are being run
>> > and
>> > compiled on the same machine. They are both running under the 1.1
>> > framework.
>> > It would make more sense to me that the code didn't behave the way I
>> > thought
>> > it should if it was consistently misbehaving. :-)
>> > It's quite possible there is something I'm missing here, but I'm just
>> > trying
>> > to figure out the reason for the inconsistency.
>> >
>> > I'm running Reporting Services 2003 with sp2 installed.
>> >
>> > Anyone have any ideas?
>> >
>> > Thanks,
>> >
>> > Nathan
>> >
>>|||Putting the "ctype("$1,0562.00",double)" directly in the cell on a page seems
to work fine. It seems to be when the code is run in a custom dll that the
problem occurs.
Can anyone else reproduce this?
Thanks,
-Nathan
"Steve MunLeeuw" wrote:
> I just entered it as an expression in a textbox and clicked the Preview tab
> in the Report Designer (VS IDE)
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
> > Perhaps this was something that was fixed with 2005? Although I'm still
> > not
> > ruling out that I'm missing something. When you say in the preview window
> > do
> > you mean you used "ctype("$1,0562.00",double)" in the custom code window
> > there? I'll have to give that a try and see if it has the same behavior
> > as
> > calling it within a custom dll.
> > "Steve MunLeeuw" wrote:
> >
> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
> >> returned
> >> true as expected.
> >> =IsNumeric("$1,0562.00")
> >>
> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
> >> >I tried posting this earlier, but it never showed up. Hopefully I don't
> >> > double post.
> >> >
> >> > I was writing code for a custom dll earlier this morning where I ran
> >> > into
> >> > some strange behavior for the IsNumeric function.
> >> > IsNumeric("$1,0562.00") was returning false
> >> > I thought that maybe this behavior was being cause by one of the
> >> > "features"
> >> > of the IsNumeric function so I decided to do some more testing.
> >> > I created a web app on the same machine and ran the same code there
> >> > IsNumeric("$1,0562.00") was returning true with the web app.
> >> >
> >> > I delved a little deeper and decided to research just what all
> >> > IsNumeric
> >> > does to check for a numeric value. I didn't have to go far.
> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
> >> > states
> >> > "It also returns True if Expression is a String that can be
> >> > successfully
> >> > converted to a Double"
> >> >
> >> > So I decided to test the following code in each environment.
> >> > ctype("$1,0562.00",double)
> >> > Sure enough within the web app it worked fine, but within a reporting
> >> > services custom dll it threw an exception.
> >> >
> >> > Both the instance of reporting services and the webapp are being run
> >> > and
> >> > compiled on the same machine. They are both running under the 1.1
> >> > framework.
> >> > It would make more sense to me that the code didn't behave the way I
> >> > thought
> >> > it should if it was consistently misbehaving. :-)
> >> > It's quite possible there is something I'm missing here, but I'm just
> >> > trying
> >> > to figure out the reason for the inconsistency.
> >> >
> >> > I'm running Reporting Services 2003 with sp2 installed.
> >> >
> >> > Anyone have any ideas?
> >> >
> >> > Thanks,
> >> >
> >> > Nathan
> >> >
> >>
> >>
> >>
>
>|||Sorry about that, I missed the whole point.
I added the following method to a custom assembly and referenced this in a
2005 report and used the preview window. I was going to try the same in VS
2003, but my 2.0 assembly doesn't appear that it will work with the 1.1
framework in 2003. My custom assembly is in C#, referenced the VB.Net dll
to get the IsNumeric function.
public bool NewsGroup_CtypeFromString()
{
return Microsoft.VisualBasic.Information.IsNumeric("$1,0562.00");
}
Expression:
=Code.loc.NewsGroup_CtypeFromString
Returns True
Steve MunLeeuw
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:AC81C8D6-6E64-4867-A15E-72C034F3F511@.microsoft.com...
> Putting the "ctype("$1,0562.00",double)" directly in the cell on a page
> seems
> to work fine. It seems to be when the code is run in a custom dll that
> the
> problem occurs.
> Can anyone else reproduce this?
> Thanks,
> -Nathan
> "Steve MunLeeuw" wrote:
>> I just entered it as an expression in a textbox and clicked the Preview
>> tab
>> in the Report Designer (VS IDE)
>>
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
>> > Perhaps this was something that was fixed with 2005? Although I'm
>> > still
>> > not
>> > ruling out that I'm missing something. When you say in the preview
>> > window
>> > do
>> > you mean you used "ctype("$1,0562.00",double)" in the custom code
>> > window
>> > there? I'll have to give that a try and see if it has the same
>> > behavior
>> > as
>> > calling it within a custom dll.
>> > "Steve MunLeeuw" wrote:
>> >
>> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> >> returned
>> >> true as expected.
>> >> =IsNumeric("$1,0562.00")
>> >>
>> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >> >I tried posting this earlier, but it never showed up. Hopefully I
>> >> >don't
>> >> > double post.
>> >> >
>> >> > I was writing code for a custom dll earlier this morning where I ran
>> >> > into
>> >> > some strange behavior for the IsNumeric function.
>> >> > IsNumeric("$1,0562.00") was returning false
>> >> > I thought that maybe this behavior was being cause by one of the
>> >> > "features"
>> >> > of the IsNumeric function so I decided to do some more testing.
>> >> > I created a web app on the same machine and ran the same code there
>> >> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >> >
>> >> > I delved a little deeper and decided to research just what all
>> >> > IsNumeric
>> >> > does to check for a numeric value. I didn't have to go far.
>> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> >> > states
>> >> > "It also returns True if Expression is a String that can be
>> >> > successfully
>> >> > converted to a Double"
>> >> >
>> >> > So I decided to test the following code in each environment.
>> >> > ctype("$1,0562.00",double)
>> >> > Sure enough within the web app it worked fine, but within a
>> >> > reporting
>> >> > services custom dll it threw an exception.
>> >> >
>> >> > Both the instance of reporting services and the webapp are being run
>> >> > and
>> >> > compiled on the same machine. They are both running under the 1.1
>> >> > framework.
>> >> > It would make more sense to me that the code didn't behave the way I
>> >> > thought
>> >> > it should if it was consistently misbehaving. :-)
>> >> > It's quite possible there is something I'm missing here, but I'm
>> >> > just
>> >> > trying
>> >> > to figure out the reason for the inconsistency.
>> >> >
>> >> > I'm running Reporting Services 2003 with sp2 installed.
>> >> >
>> >> > Anyone have any ideas?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Nathan
>> >> >
>> >>
>> >>
>> >>
>>|||Got off my lazy horse and tried in VS 2003, I don't have VB installed, I
think using regular expressions would defeat the whole purpose of your case.
Maybe using RegExpr instead of IsNumeric would be a valid workaround?
sorry, C#
deonvs said:
Even easier:
static bool IsNumeric(string inputString)
{
return Regex.IsMatch(inputString, "^[0-9]+$");
}
Reference: http://dotnet.org.za/deonvs/archive/2004/07/06/2579.aspx
Steve MunLeeuw
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:AC81C8D6-6E64-4867-A15E-72C034F3F511@.microsoft.com...
> Putting the "ctype("$1,0562.00",double)" directly in the cell on a page
> seems
> to work fine. It seems to be when the code is run in a custom dll that
> the
> problem occurs.
> Can anyone else reproduce this?
> Thanks,
> -Nathan
> "Steve MunLeeuw" wrote:
>> I just entered it as an expression in a textbox and clicked the Preview
>> tab
>> in the Report Designer (VS IDE)
>>
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:E814C5A0-E41D-4261-8A37-7ECB30C98D68@.microsoft.com...
>> > Perhaps this was something that was fixed with 2005? Although I'm
>> > still
>> > not
>> > ruling out that I'm missing something. When you say in the preview
>> > window
>> > do
>> > you mean you used "ctype("$1,0562.00",double)" in the custom code
>> > window
>> > there? I'll have to give that a try and see if it has the same
>> > behavior
>> > as
>> > calling it within a custom dll.
>> > "Steve MunLeeuw" wrote:
>> >
>> >> That is odd. I tried in SSRS 2005 preview window just for giggles,
>> >> returned
>> >> true as expected.
>> >> =IsNumeric("$1,0562.00")
>> >>
>> >> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> >> news:4B24FE69-BCA8-48CF-8ECC-40F8FCCC5448@.microsoft.com...
>> >> >I tried posting this earlier, but it never showed up. Hopefully I
>> >> >don't
>> >> > double post.
>> >> >
>> >> > I was writing code for a custom dll earlier this morning where I ran
>> >> > into
>> >> > some strange behavior for the IsNumeric function.
>> >> > IsNumeric("$1,0562.00") was returning false
>> >> > I thought that maybe this behavior was being cause by one of the
>> >> > "features"
>> >> > of the IsNumeric function so I decided to do some more testing.
>> >> > I created a web app on the same machine and ran the same code there
>> >> > IsNumeric("$1,0562.00") was returning true with the web app.
>> >> >
>> >> > I delved a little deeper and decided to research just what all
>> >> > IsNumeric
>> >> > does to check for a numeric value. I didn't have to go far.
>> >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctisnumeric.asp
>> >> > states
>> >> > "It also returns True if Expression is a String that can be
>> >> > successfully
>> >> > converted to a Double"
>> >> >
>> >> > So I decided to test the following code in each environment.
>> >> > ctype("$1,0562.00",double)
>> >> > Sure enough within the web app it worked fine, but within a
>> >> > reporting
>> >> > services custom dll it threw an exception.
>> >> >
>> >> > Both the instance of reporting services and the webapp are being run
>> >> > and
>> >> > compiled on the same machine. They are both running under the 1.1
>> >> > framework.
>> >> > It would make more sense to me that the code didn't behave the way I
>> >> > thought
>> >> > it should if it was consistently misbehaving. :-)
>> >> > It's quite possible there is something I'm missing here, but I'm
>> >> > just
>> >> > trying
>> >> > to figure out the reason for the inconsistency.
>> >> >
>> >> > I'm running Reporting Services 2003 with sp2 installed.
>> >> >
>> >> > Anyone have any ideas?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Nathan
>> >> >
>> >>
>> >>
>> >>
>>