Question related to Data Import

Hi,

I have a scenario where I either have an option of writing a VB based import routine to import "FORMATTED" data in a text file and populate it in my underlying tables (SQL2K). However someone suggested that I should use DTS.

I am not very familiar with DTS and would therefore appreciate:

1. What would be a better solution - using the VB based import routine that I was planning to have in place or use a DTS?

2. What are the benefits of using a DTS?

3. How easy is it to define/develop a DTS?

4. Aee DTSs flexible?

5. Can someone recommend a URL from where I can read more about DTS and ideally with an example which I can follow to define/develop a DTS myself?

I'll appreciate your reply. Thanks.DTS is the way to go. DTS is very functional in transforming data. Read this link for more info:
http://www.databasejournal.com/features/mssql/article.php/3086891

and also look up DTS in Books Online, and the internet

Hope this helps|||You need access to enterprise manager (to make life easier)

sql server's client side tools...

But why not use BULK INSERT with a Format file? Or bcp

I've seen DTS do wierd things...but that was back in 7.

And if your VB routine was going to do singleton inserts, I would recommend against taht...

Holy Tranny log batman...|||Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.|||Originally posted by Joozh
Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.

Once you create the DTS package, make sure it's portable, ie, it's can be used on virtually any computer. Read the Following article portability of DTS packages, it will give you some idea.

http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp

Secondly, once the DTS package is completed you can schedule a job for the package. This job can run on any server. So you can transform/format files or load the data from files to tables etc. So the user would just see the finished product of the DTS packages.

Hope this helps.

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_CurrCurrency_Code,
@.AC_CurrCurrency_Code,
@.In_ValAmount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_ValAmount,
@.Ex_RateEx_Rate,
@.Cal_MethodCal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
Kenneth
Hi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:

> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone who
> can tell me what's wrong with it?
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_CurrCurrency_Code,
> @.AC_CurrCurrency_Code,
> @.In_ValAmount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_ValAmount,
> @.Ex_RateEx_Rate,
> @.Cal_MethodCal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
> @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>
|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:

>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like this
>:
>SELECT
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
>FROM TableX
>WHERE
>ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it?
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
Hugo Kornelis, SQL Server MVP
|||I am using SQL server service pack 2 currently. However, I can still simulate
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:

> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
KennethHi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:

> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like th
is
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone wh
o
> can tell me what's wrong with it'
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency
=
> @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Cur
r
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:

>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like thi
s
>:
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it'
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
Hugo Kornelis, SQL Server MVP|||I am using SQL server service pack 2 currently. However, I can still simulat
e
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:

> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency = @.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
KennethHi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone who
> can tell me what's wrong with it'
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like this
>:
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it'
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
--
Hugo Kornelis, SQL Server MVP|||I am using SQL server service pack 2 currently. However, I can still simulate
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:
> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
> >Hello,
> >
> >I am new to SQL Server. I am writing a stored procedure with a sql like this
> >:
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> >When I run the query, all the five column return same value and I am sure
> >the result is incorrect. Here is the function of fn_currency. It simply
> >convert foreign curreny amount to base currency amount. Is there anyone who
> >can tell me what's wrong with it'
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question on cube write back

Hi, all experts here,

I encountered a problem with cube writing back which contains measures with count aggregation rather than sum which said write back can not be allowed on measure groups with other aggregations rather than sum? Is it saying we can only enable write back on cubes with measure groups all based on sum aggregations?

But in my case, the measures needed to be counted rather than sum. What can I try to enable write back on this cube?

I am looking forward to hearing from you for your advices and thank you very much in advance.

With best regards,

Yours sincerely,

I'm having trouble thinking of a situation where you would need to writeback to a count measure, but that is beside the point.

One work around might be to create a new column in your fact table for this measure and pre-populate it with a value of 1 during ETL. Then you would be able to use a sum aggregation and get the same value as you would with a count measure. It would then also be possible to write back to this measure.

|||

Hi,

In my data, the facts are bit data, which only with values of 1 and 0 meaning different things respectively. Therefore I need to count these different situations happened in the fact table across different levels. There are no numeric data types at the moment which are for sum.

Thanks for your advices and I am looking forward to hearing from any of you here more ideas in this situation. Maybe you would give me some better ideas on how to deal with it.

With best regards,

Yours sincerely,

|||

Helen,

Explain me better what is the goal, I didnt understood... Explain me as I a dummy!

:-)

Regards!

|||

HI, PedroCGD,

The facts in the facts tables are with 0 or 1 values only. (0 and 1 therefore represent different meanings), therefore I dont see any point to sum these facts in any cube. Instead, count aggregation makes more sense which can count the numbers for the fact being 0 or 1.

Hope my explanation is clear for your help.

With best regards,

Yours sincerely,

|||It sounds like you have some flags which should modelled as separate attributes, not as measures. You would have one count measure and set either a single dimension or a combined dimension (also known as a 'junk' dimension). In this way you should be able to get the count by any combination of attributes.|||

Hi, Darren,

Thanks for your kind advices.

As you kindly advised, I will then need to update the underlying relational database (as the underlying data warehouse is designed by others)?Since at the moment, the data residing in the data warehouse does not have any junk dimensions at all. All those flags attributes are residing in the fact tables.

I would need to create single dimension for each flag attribute or combine some of the flag attributes together into one single dimension? But only one count measure for all these dimension attributes?

Will it be any good to complete these in data source view of the cube? Thanks again. And hope you can give me more further advices on it.

With best regards,

Yours sincerely,

Question of SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Is there anyone who can tell me what's wrong with
it'
Many thanks!
KennethKenneth wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Is there anyone who can tell me what's wrong with
> it'
> Many thanks!
> Kenneth
Please post enough code to reproduce the problem and show us what is
wrong with it. Without even seeing what fn_currency does it's hard to
help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the function of fn_currency. It simply convert foreign curreny amount
to base currency amount.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
"David Portas" wrote:
> Kenneth wrote:
> > Hello,
> >
> > I am new to SQL Server. I am writing a stored procedure with a sql like this
> > :
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> > When I run the query, all the five column return same value and I am sure
> > the result is incorrect. Is there anyone who can tell me what's wrong with
> > it'
> >
> > Many thanks!
> > Kenneth
> Please post enough code to reproduce the problem and show us what is
> wrong with it. Without even seeing what fn_currency does it's hard to
> help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||This will not solve your former problem, but IMHO this is not a good
approach.
You should use a simple joined table and avoid the choice of the method at
the execution (multiply or divide) in the function.
Have a column with a coef and always multiply. This coef can be a computed
column or a column maintained by a trigger. ;-)
This could look like this :
select t1.amount, t1.amount * er.rate as converted_amount
from table t1
inner join exrate er
on t1.currency = er.source_currency
and er.destination_currency = 'USD'
This is a *much* faster.
"Kenneth" <Kenneth@.discussions.microsoft.com> a écrit dans le message de
news: B8BD30D8-1100-45A7-8615-8EDFCB7A494E@.microsoft.com...
> Here is the function of fn_currency. It simply convert foreign curreny
> amount
> to base currency amount.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency
> => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> "David Portas" wrote:
>> Kenneth wrote:
>> > Hello,
>> >
>> > I am new to SQL Server. I am writing a stored procedure with a sql like
>> > this
>> > :
>> >
>> > SELECT
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
>> > FROM TableX
>> > WHERE
>> > ID = '1234'
>> >
>> > When I run the query, all the five column return same value and I am
>> > sure
>> > the result is incorrect. Is there anyone who can tell me what's wrong
>> > with
>> > it'
>> >
>> > Many thanks!
>> > Kenneth
>> Please post enough code to reproduce the problem and show us what is
>> wrong with it. Without even seeing what fn_currency does it's hard to
>> help you.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>