SSAS Cube only working in certain legal entity

(0) Share

Report

Posted on by Community Member

Hi all,

we have a problem with our enterprise portal dashboard data, after i deploy and process the analytics cube, only one legal entity shows correct data, other legal entity just shows blank - no data.

For example here I use the Top Customer by YTD Sales, you can see below the comparison of report produced by DAX and report directly pulled from A/R cube using excel. they are show the exact same result.

(customer name are blocked) - company : BM01

1222.Capture.PNG

However when i run the same report on another legal entity, it just says no data while the same excel query from the same parameter as prior legal entity shows some data - customer name blocked, company : ID01

6825.Capture.PNG

So i'm sure that the cube and SSAS working correctly because we can see the data from excel or direct from SQL server management studio, but what happened is when DAX pulled the data from cube, looks like something is missing thus the result is always blank.

We have like 7 legal entities here and the report only work on ONE and left six other blank.

Any thought and suggestion on what is happening or how to solve it will be very appreciated.

thanks

Chris

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (2)

Community Member on at

Like (0)

Report

...

Was this reply helpful? Yes No
Suggested answer

jac_rod 39 Moderator on at

Like (0)

Report

This could be caused by currency conversion. Take a look at my blog post that gives some hints on things to check. There are cases where these reports require a "Analysis currency" selected - if you're not selecting this currency or the currency isn't calculating properly, you might see only results in the "Local" currency in the AX reports.

dynamicconsulting.com/.../currency-conversion-in-standard-ax-2012-analysis-services-cubes

Take a look in your system and let me know what you think.

-Jake

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Jake,

thanks for the reply. Your answer might point at correct issue, but i haven't resolve it yet. One thing i discovered is whenever a legal entity have 'USD" as the accounting currency, the report works. But other than USD, the report shows blank. Our 'anaylisis currency' now only 'USD' and 'local'. So i guess that's why the report is not working, because the 'analysis currency' is not prepared for other than USD (?).

From above query on cube, only company BM, NG and US have the report working, and all have USD as accounting currency. The currency colum is 'analysis currency'. Other company such as ID and KE that have non-USD as accounting currency have the report blank.

note : all the numbers above are on dummy development environment, not reflecting real figures.

I read your blog, do you think we need to add some local currencies to the analysis currency list ? if yes, how do we supposed to do that ?

thanks in advance

Chris

Was this reply helpful? Yes No
Suggested answer

jac_rod 39 Moderator on at

Like (0)

Report

Yeah. That's likely what's causing at least part of this issue.

In the blog post, I tried to indicate the things to do. If you have multiple currency codes being used as accounting currencies in your other companies, they all have to have currency exchange rates to each other. For example if you have USD, GBP, and EUR, you need to have exchange rates between USD-GBP, USD-EUR, and GBP-EUR for all three of them to display.

Try looking at that first, and make sure you have all the accounting currency codes represented in the exchange rates.

-Jake

Was this reply helpful? Yes No
Verified answer

Lance [MSFT] on at

Like (1)

Report

I concur with Jacob that missing exchange rates are likely the reason that some values are not shown.

Here's a bit of background info on the Analysis Currency dimension.

In AX, currency amounts are stored in the ledger currency of the company. But since different companies can use different ledger currencies, if you query across multiple companies, which appears to be the case here, the resulting amounts are not really comparable.

To get past this, the Analysis Currency dimension was added which can be used for reports that need to show data for multiple companies and convert the amounts to some specified currency so that they are comparable. It basically shows you what are these amounts worth in currency X. Whenever using Analysis Currency in a query, the query should be written so that the query is filtered by a single value from the dimension. Otherwise the query is really asking for what is amount X in USD + amount X is EUR + amount X is whatever other currencies are available, and that sum is not particularly useful.

In queries that are filtered to a single company, there is no need to use the Analysis Currency dimension because all currency amounts are already stored in the currency for the company.

The Analysis currency dimension members are a subset of the currency dimension values. For each currency in the currency list, if there exists an exchange rate between that currency and each currency that is used as the ledger currency for a company, then the currency is included in Analysis currency dimension. If any of those exchange rates are missing, then the currency is omitted from the analysis currency dimension. This is because there is at least one company having amounts stored in a ledger currency that cannot be converted to that particular member of the currency dimension due to the lack of an exchange rate.

So if the some ledger currencies are not shown in the Analysis currency dimension, then first get the set of currencies that are used as a ledger currency and then for each pair of currencies from that list, enter an exchange rate. Exchange rates between currencies not used as ledger currencies are not required.

After entering exchange rates, reprocess the database and do a full process rather than an incremental process because incremental process does not update dimension members. That should allow the analysis currency dimension to pick up the other ledger currencies.

Hope this helps.

Was this reply helpful? Yes No
jac_rod 39 Moderator on at

Like (0)

Report

Thanks for the extra info Lance!

Was this reply helpful? Yes No
Verified answer

Lance [MSFT] on at

Like (0)

Report

One more thing ...

If you happen to have an empty dat company, set it's ledger currency. There have been reports in the past of currency amounts not showing up that if there is a company without a ledger currency set.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Hi Jacob, Lance,

you guys are my saviour :)

i solve the problem, thanks to your help.

in summary, thing to remember on multicurrency setup are :

1. all accounting currency from all companies must have exchange rate setup between each other

2. dont forget to include DAT's ledger settings, even it's not being used and empty

3. full process database after the exchange rate setup is done

the result : my analysis currency is now consist of 6 currencies :)

and eventually, the Top Customer by YTD Sales report, is completed perfectly.

thanks Gentlemen !

Cheers

Was this reply helpful? Yes No