Many - Many Currency Conversion

I ran the "Currency conversion" BI wizard to implement Many to Many currency conversion but get the following error when I click on Finish in the wizard. Looks like the wizard is trying to generate some thing wrong. Any clue ?

The 'DimensionAttribute' with 'Name' = 'Reporting Currency' doesn't exist in the collection

Cheers,

Arun

Got this to working by creating a "Role Playing dimension" for currency that is used by the FX rates measure groups. Now, I have a bigger problem.

1. er the wizard generates the dimensions, calculation etc., we need to define the relationship b/w the generated "Reporting Currency" dimension and the measure groups. (Else the new dimension will not be displayed in the client). But, logically there is no relationship. How do we overcome this ?

measure group1 : Balance

Dimension : Calendar, Currency, ......

measure group2 : Rates (The fact table contains rates for all currencies from EUR for all days. cols - Date, CurrencyCode, Rate)

Dimension : Calendar, ToCurrency, ....

Newly generated dimension : Reporting Currency

2. Also a member called "Local" is used in calcualtion. Can some one explain the need for this ?

Any help is appreciated.

|||

Could you clarify your goal? Are you saying you want to see the "To Currency" (cube) dimension against the Balance measure group? If so, could you confirm a (many to many) relationship exists between the "To Currency" dimension and the Balance measure group after the wizard is finished?

Thanks,

Bryan

|||

Well, my goal is to get the many to many currency conversion working.

One of the problems is that once the wizard is finished, I can see a new "Reporting Currency" dimension created and no relationship exists in Dimension Usage and hence I'm not able to see this dimension through Excel or from any other client.

Thanks,

Arun

|||

I am familiar with currency conversion in theory, but I have not used the wizards. I suspect all you need to do is set a many-to-many relationship between the "To Currency" (cube) dimension and the Balance measure group.

To do this, open the cube editor and select the dimension usage tab. Click on the grey-box at the intersection of the Balance measure group and the "To Currency" dimension. This should give you a button in the right-hand side of this box. Click that button to get the Define Relationship dialog.

In the Define Relationship dialog, set relationship type to many-to-many and set the Rates measure group as the intermediate measure group. If the Rates measure group is not available in the drop down, SSAS can't identify a set of shared dimensions between these two measure groups. This indicates a deeper problem and I suspect you might need to revisit how you've used the Currency Conversion wizard.

BTW, the SSAS 2005 Step-by-Step book covers currency conversion with reasonable detail. That could be a good resource for getting started.

Bryan

|||

I have a ToCurreny dimension (I created this myself. Has relationship only to Rates) + another "Reporting Currency" dimension generated by the wizard.

In all the examples given in the book, they have the above 2 dimensions as the same but in real-world scenario it cant be the same. ToCurrency - Will contain all currencies for which rates are available. They will have to include rates for all transaction currencies in the fact table. (in my case 100 currencies)

Reporting Currency - should include only the set of currencies for which we need the conversion (in my case only some 6 currencies)

But the problem is that, there is no logical relationship b/w Reporting Currency and Rates or Balance measure group as that dimension will only be used for on-the-fly calculation. So, I'm in a fix now to set the dimension usage for this dimension whithout which , this cannot be viewed from the client.

thanks,

Arun

|||

Problem solved.

Solution:

I had created 2 diff measures (so 2 diff measure groups)

1) Rates_ToEUR ==> Will contain rates for all currencies to EUR for all days

2) Rates_FromEUR ==> Will contain rates from EUR to only the reporting currencies

Dimension Usage :

Measure group - Rates_ToEUR -- Currency (same one as used by balance measure group) and Calendar

Measure group - Rates_FromEUR -- Calendar and ReportingCurrency. Also, Balance measure group has many to many relation ship with ReportingCurrency dim through this measure group.

Also, I have simplified the calculation to below :

Scope ( { Measures.[DLY Value Reporting CCY], Measures.[MTD Value Reporting CCY], Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope( Leaves([Calendar]) ,Leaves([Currency]), [Reporting Currency].[Currency].Members);

This = Measures.CurrentMember * Measures.[Rate_ToEUR] * (Measures.[Rate_FromEUR], [Reporting Currency].[Currency].CurrentMember) ;

end scope;

End Scope; // Measures

The above solution works perfectly till now. Any one can see any issues?

So, I would suggest implementing currency conversion by yourself without using the wizard (BI) which does not help much.

Cheers,

Arun

sql

Manually defining aggregations

The Aggregation Wizard designed the following aggregation, on 2 dimensions.
I understand it is aggregating at the Country level in the StoreGeography dimension. However, in the Time dimension, since it didn't list any attributes, I am wondering where it is aggregating.

Is it at the All level, or is it at the leaf level ?

Thanks for any help.

<Aggregation>
<ID>Aggregation 7</ID>
<Name>Aggregation 7</Name>
<Dimensions>
<Dimension>
<CubeDimensionID>Store Geography</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Country</AttributeID>
</Attribute>
</Attributes>
</Dimension>
<Dimension>
<CubeDimensionID>Time</CubeDimensionID>
</Dimension>
</Dimensions>
</Aggregation>

That's right: In case and if no attribute specified for a dimension in the definition of the aggregation, the aggregation contains data for dimension on level "All" for the dimension.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!

Management Studio Scripting Bug?

In my install of management studio when I use the scripting wizard and get to the Choose Script Options dialog The Script Behavior drop down has only two options (Generate CREATE statements only, or Generate DROP statements only). It doesn't have the option to generate a drop followed by a create. If you read the description of the Script Behavior that is below the options you'll see that it advertises a Drop and create option. Why doesn't the dropdown have all three options? Bug or not?

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply Venkat. I think I will miss the ability to do the drop and create in the same script generation. Enterprise Manager's script wizard could do this. Hopefully some update in the future will put this ability back into the product.

Mikewo|||Yes, thank you for the reply.

I too would enjoy seeing that functionality back, 2 passes to accomplish 1 thing is monotonous.|||

Venkat,

And how long will we have to wait for this simplest and most useful option? I hope it will be available as a Global option, not on a database by database basis as it is now.

|||

In my opinion this is a bug. Obviously, based on the description the functionality was expected, and it is not provided.

Terry

|||

I've reported it as a bug since IMHO it is one (not working as documented). Title is:

Script Behavior in Script Wizard does not allow 3rd option

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=605eb110-2335-4275-b440-5d299c484ebf

Add your vote on it if you think it is important to you.

|||Does that mean you will be bringing back the Crop followed by Create, or leaving it out? This is a very useful feature!|||

We consider it a bug, and we would appreciate the ability to create a drop and create statement in one pass. Additionally, it would be very useful to script ALL databases, logins, users and roles in one pass.

Kind regards

Titus

|||It seems to make perfect sense that the wizard SHOULD support the Drop/Create scenario. It's even a bit ridiculous that it doesn't! 2005 seems to be a step backward as far as the scripting wizard goes.|||

VenkatKrish wrote:

Hello Mikewo

Thanks for posting this question.

We are aware of the misleading description, which is an error. The descriptiong will be fixed in our future release.

Unfortunately, the ability to generate drop followed by create is not supported by the Wizard. The work around is to first generated drop scripts & then generate create scripts.

Thank you,
[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Dear Venkat:

Thanks for the clarification. THE BUG IS NOT IN THE DESCRIPTION, THE BUG IS IN THE FUNCTIONALITY! When I push a revised batch of stored procedures to the production server, I can do it with the server on-line with scripts done via drop/create. I then have a small window of time that the system might claim a stored procedure is not present. This window is small enough to risk doing while users are in the database. This is not true if I have to do every updated SP as a mass of drops followed by a mass of creates. Then I have to take the applications off-line.

Please give us back the functionality we had soon!