Current Month in ProClarity

I can′t get the current month,I have tried the following mdx without any result:

Code Snippet

Extract(Filter([Period].[Monthly].[Month].Members, [Period].[Monthly].CurrentMember.Properties("Current Period") ="-1"), [Period].[Monthly]).Item(0)

Code Snippet

Tail( Filter( [Date].[Date].Levels(3).Members, Not IsEmpty([Date].CurrentMember)), 1).item(1)

Here is an example that will give you the most current calendar month in Adventure Works where the month has "reseller sales":

Tail(Exists([Date].[Calendar].[Month].Members,,"Reseller Sales"),1)(0)

The general form of the calculation is:

Tail(Exists(<<date dimension>>.<<date hierarchy>>.<<date level>>.Members,, "<<measure group>>"),1)(0)

HTH,

Steve

sql

Currency formatting: how to change to use another locale... or is US format hard coded?

How to I change the formatting of my currency based members according to the actual currency that they are defined in rather than the default US format? (in my case GBP).

Answers to this can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=887100&SiteID=1

I got sorta the same problem..

|||

FYI: Direct link to answer:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

Am I right in thinking that I should create a Script Command in the Calculuations tab to set the appropriate languages for my measures?

eg:

Language([Measures].[GBPEQUIV-SENSITIVIES]) = 2057;

Language([Measures].[GBPEQUIV-STRESS]) = 2057;

BTW: I also changed the server locale to English-UK to get a default setting.

|||

Yeah I think you could do that, I have a script command like that with: Language(this) = 1053

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.

Cube members not accurate

What could gone wrong when the memebers of a dimension after deployment is not accurrate as the actual records in the Dim table? Initially i hit an error when changing the actual Dim table and Fact table ( by the way i am changing the primary foreign key values.) Then i ecounter errors when trying to process the cube again. Seeing this:http://support.microsoft.com/kb/922673 , I recreate the data source and no errors after the deployment 9 hurray). But the new records added in the Dim is not reflected in the memebers of the cube :(

I guess the initial error that I get is becausethe Dim table is not updated in thefirst place causing the Fact table to have keys not found in Dim. But my question is why is it theDim not updated?

Thanks.

Regards

Alu

Go back try processing your dimension using standard processing options.

If you instruct Analysis Server to hide problems it finds during dimension processing, you never going to find problems you have in your data. The instructions in the KB article only let you get by if you are just in the hurry to get your cube somehow processed. You should really try and resolve all the problems with relational data to see the correct dimension structure and correct measure totals. Hiding processing errors is quite dangerous.

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