Currency Conversions

Hi!

I am having trouble wrapping my brain around currency conversions.

Here is my situation:

My fact table contains values in local currency with a reference to the currency dimension.

These local currencies need to be converted to two reporting currencies: Actual Rates and Budget Rates.

I have rates on a daily basis for both reporting currencies.

How should my table structure look, and how do i set it up in AS so this works? Ive been fiddeling with this for AGES, getting M:1 conversions to work but not this.

Any help appreciated.

Maybe this thread on currency conversions can help you?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=151917&SiteID=1

>>

11-29-2005, 8:29 PM UTC

Michael Barrett Jensen

Posts 41

Measure expressions and currency conversion
Was this post helpful ?

OK - here is my scenario... I have a fact table containing one measure: Sales. The values of the measure are stored in the same currency (DKK) for all records. In another fact table I have exchange rates for four different currencies per day for a 15 year period (approx. 22000 records). I have built a cube around these fact tables containing two measure groups.

Measure Group 1
Measure: Sales
Dimensions: Time, Product, Company

Measure Group 2
Measure: Exchange rate
Dimensions: Time, Currency

The only shared dimension between the two measure group is thus Time... The currency dimension contains one attribute hierarchy (CurrencyCode) with IsAggregatable set to False and DefaultMember set to DKK.

For the measure Sales, I have created a measure expression like this:

[Measures].[Sales] / [Measures].[Currency]

I thought this should work - i.e. give me the opportunity to select any of the four available currencies and have the value of Sales displayed accordingly. However - it does not. Instead my Sales measures is multiplied by 4, which corresponds to the number of currencies for which I have exchange rates in my exchange rate fact table. Why is this? Am I not modelling the scenario correctly?

I hope someone can help, since this really puzzles me!

---

Michael

11-30-2005, 12:06 PM UTC

Chris Webb

Posts 31

Answer Re: Measure expressions and currency conversion
Was this post helpful ?

Hi Michael,

I think I might know what's going on here: you need to give your Currency dimension a many-to-many relationship with Measure Group 1 using Measure Group 2 as the intermediate measure group. Does this work?

Regards,

Chris

---

Blog at http://spaces.msn.com/members/cwebbbi/

>>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.
You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>
|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>