Here is some information for you
http://msdn2.microsoft.com/en-us/library/ms180174.aspx
http://msdn2.microsoft.com/en-us/library/ms175660.aspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Here is some information for you
http://msdn2.microsoft.com/en-us/library/ms180174.aspx
http://msdn2.microsoft.com/en-us/library/ms175660.aspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
All our reports gives the revenues in $ now we are planning to get the same result in various currencys.Can any one tell me How to use One-to-many & Many-to-many options?
Thanks in Advance.
Some information for you to implement currency conversion in Analysis Services.
http://msdn2.microsoft.com/en-us/library/ms175660.aspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
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
>>
Michael Barrett Jensen
Posts 41
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
Chris Webb
Posts 31
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/
>>
I am using the cube intelligence wizard to add currency conversions. I have defined everything properly. At a certain moment, a query is launched against my Oracle backend. This is the query:
SELECT "Reporting Currency".*
FROM
(
SELECT "CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_CODE","CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_NAME"
FROM "CCDM_IL"."CCDM_IL_CRRN_T"
WHERE [CURRENCY_NAME] IN ('Euro') UNION
SELECT
DISTINCT
'Local' "Local",'Local' "Local 2"
FROM "CCDM_IL"."CCDM_IL_CRRN_T"
)
"Reporting Currency"
Now, this query returns the error "ORA-00936: Missing Expression". This error is caused by the square brackets around currency_name in the where clause. Once I remove these the query runs fine.
How can I make this query work?
Thanks
Joos
By the look of it this is coming from a named query in the DSV. Check if the wizard has actually inserted the square brackets into the named query in the DSV. If so, you should be able to edit the definition to remove the square brackets.|||The query is generated by the BI wizard itself. In my DSV, I do not have a named query for the currency dimension. It is a real table...
The currency dimension has one attribute for which the key is set to CCDM_IL_CRRN_T.CURRENCY_CODE and the name is set to CCDM_IL_CRRN_T.CURRENCY_NAME.
|||Can you double check? Right click on the object in the DSV and tell me if the third menu item you see says either "Edit Named Query..." or "Replace Table with...". I would not normally ask, but the reason I am is becuase the BI wizard actually generates objects in your DSV and cubes, but it does not actually send any queries to the relational source.|||For the table CCDM_IL_CRRN_T, it definitely says "Replace Table".
Joos
|||Have you been able to isolate if this error is coming from a dimension or from a partition? (by processing objects associated with currencies one at a time)
Try going into SSMS, right clicking on the database and generating an alter script. This will generate a heap of xmla, search through this for the table name in square brackets or some other identifiable portion of this problem query. You can then either run the alter script and then import the database back into BIDS or hopefully this will help identify where exactly the problem query is coming from.
|||In the script, the square brackets do not occur. Extracts where currency_name occurs:
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>CCDM_CCDM_IL_CRRN_T</TableID>
<ColumnID>CURRENCY_NAME</ColumnID>
</Source>
</NameColumn>
<xs:element name="CURRENCY_NAME" msprop:FriendlyName="CURRENCY_NAME" mspropbColumnName="CURRENCY_NAME" minOccurs="0">
<xsimpleType>
<xs:restriction base="xstring">
<xs:maxLength value="255" />
</xs:restriction>
</xsimpleType>
</xs:element>
Joos
Cursors vs. Queries,database,sql server