SQL Centre of Excellence

I’ve been having a few problems with the following error message in Dashboard Designer 2010, when refreshing a data connection to SSAS.

“The following lists cannot be accessed from SharePoint server. The lists have been deleted or you no longer have permission to open them. “

image

There was also a note in the application log “Failed to Read the ACLS for an item”

image

The crux of the problem is that Application pool for PerformancePoint is used to invoke SharePoint web services, and it needs at least “Read” access on the data connections folder. You can right click on the data connections library in Dashboard Designer and select “Edit Permissions”. Note that the application log tells you the user for the application pool, but if you miss it you can look in IIS for the applications pools. Be warned though as PerformancePoint defaults to a GUID for the name of the Application Pool.

I set the permissions for the service account and everything is flying along now..happy days.

 

Currency conversion can be a PITA. As you may know there are three standard requirements that pop up:

  1. One to Many. Where the database is in one currency and it needs to be reported in many currencies.
  2. Many to One. Where different rows are in different currencies and need to be converted into a standard currency.
  3. Many to Many currency. Different rows are in different currencies and need to be reported in many currencies.

This is slightly difficult in PowerPivot due to the lack of “Many to Many” support” and also no support for “default members” to have a default currency.

In this example we have the following tables (example PowerPivot Workbook attached). One initial solution is to use a “calculated member in the PowerPivot Window to convert to a standard currency (EUR) and then apply a one to many relationship for the reporting currency. For a lot of customers that won;t work as this results in a double conversion and therefore errors in the maths.

image Fact Table

.image

ExchangeRate Table

My solution is not elegant. In fact, maybe it will appear in the sqlcat site of “worst practices”, but it works like a dream so far…

The Solution
1. First add a sheet for your FromCurrency and your ToCurrency. Its quiet common that your source currency may be in 10-20 currencies but you only want 2-3 reporting currencies

2. Decide on your “default” currency. in my case EUR

3. do NOT add a relationship between the Facts and the “To Currency”, when a To currency is selected we need to use a calculated measure to do the conversion dynamically as many to many relationships are not supported by default in PowerPivot.

4. Add a slicer for the “To Currency”

5. Add a calculated measure for the Amount.

=if(countrows(ToCurrency)=1,sumx(FactTrans,sumx(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),FactTrans[Amount]/ ExchangeRates[MidRate])),sumx(FactTrans,sumx(filter(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),ExchangeRates[ToCurrency]="EUR"),FactTrans[Amount] / ExchangeRates[MidRate])))

 

How does it Work?

The trick is the “sumx” function effectively iterates through every row, finding the related conversion rate and applying the conversion and summing the results. The IF statement detects of we have not selected only one currency and if so, applies a default one.

How does it compare to Analysis Services Currency Conversion?

The formula looks  lot more complex than the SSAS MDX equivalent technique, and SSAS has the benefit of also being able to do formatting with “Locales” to change the currency symbols dynamically too. But its quick and easy-ish and it works…

image

Currency_Conversion_Challenge_2.0.xlsx (274.78 kb)

Page List

Page List