Translated Columns

Topics: Developer Forum
Oct 9, 2015 at 5:31 AM
Can we retrieve translated column names when using Drillthrough functions or ExecuteDrillthroughAndFixColumns function.
Coordinator
Oct 19, 2015 at 7:44 PM
If the built-in drillthrough does not return translated columns then I don't think that the ASSP version will either as we are just manipulating the recordset that is returned by the default action. I don't use translations normally so I'd have to do some research to see if this is possible.
Oct 20, 2015 at 11:09 AM
Thanks Darren. Will wait for your advise.
Coordinator
Oct 29, 2015 at 8:58 AM
OK, so I have an preview version of 1.3.8 up on my onedrive here http://1drv.ms/1SaRacC

I've added a new function called ExecuteDrillthroughAndTranslateColumns - it works like ExecuteDrillthroughAndFixColumns, but it looks up the translated caption from the MDSCHEMA_LEVELS, MDSCHEMA_MEASURES and MDSCHEMA_DIMENSIONS schema rowsets (so it will always be a little slower than ExecuteDrillthroughAndFixColumns. I think I've got it handling most things so if you could give it a test and let me know if that works for you that would be great.
Oct 29, 2015 at 9:24 AM
Thanks Darren. Let me give it a try. Will update you. Thanks again for all your help.
Oct 29, 2015 at 12:47 PM
Edited Oct 29, 2015 at 12:48 PM
Hi Darren,

I tried to test it but when I define action like
'call ASSP.ExecuteDrillthroughAndTranslateColumns("' 
 + ASSP.GetCustomDrillthroughMDX(
    "[Internet Sales].[Internet Sales Amount]
    ,[Internet Sales].[Internet Total Product Cost]
    ,NAME([$Date].[Date])
    ,NAME([$Customer].[Customer])
    ,NAME([$Product].[Product])
    ,NAME([$Promotion].[Promotion])
    ,NAME([$Source Currency].[Source Currency Code])"
   )
+ '")'
excel gets crashed on view underlying data with error
"Data could not be retrieved from the database.
Check the database server or contact your administrator. Make sure the external database is available, and try the operating again."

I am getting this error from default language as well when I use ExecuteDrillthroughAndFixColumns function so I believe this is something with excel not with newly developed function.

Please advise.

Thanks
Ail
Coordinator
Oct 29, 2015 at 7:19 PM
Adventure Works has some ROLAP dimensions (which are mainly used for drillthrough details) and you sometimes get that error if SSAS cannot find the SQL database - is that database online and is the connection string in Adventure Works point to the correct server?
Oct 30, 2015 at 6:58 AM
Edited Oct 30, 2015 at 7:00 AM
Hi Darren,

I tested the functionality on my cube and I am afraid that I am not getting translated value.

Process:

1) Defined an action for my calculated measure.
2) Brows the cube in excel with old ASSP and got error that ExecuteDrillthroughAndTranslateColumns.
3) updated the reference to your ASSP and brows the cube. Result is that I am getting Fixed column but not the translated one.

Please note that I only translated the column being used in action expression (for testing).

Action looks like
'call ASSP.ExecuteDrillthroughAndTranslateColumns("' 
 + ASSP.GetCustomDrillthroughMDX("NAME([$Policy].[Policy Status])", [Measures].[Count of Policies]) 
+ '")'
Resultant column Name in Underlying window is "Policy Status"

P.S. I am getting translated attribute name in right side panel of excel.
-Ali
Coordinator
Oct 30, 2015 at 9:31 PM
So I'm not doing any "Fixing" of names, I'm effectively looking up the caption by doing the equivalent of "SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS" - what do you see in SSMS when you run that command? Do you see the translationed attribute names?

Did you have to change the locale identifier in your odc file to get the translated names in Excel or is that being picked up automatically from the regional settings on your machine?
Oct 31, 2015 at 4:33 PM
Hi Darren,

I am getting "Policy Status" in LEVEL_CAPTION column when i ran above query. :(

I also tried it after changing language as French(France) of analysis server under server properties.

yes, i have to provide locale identifier in my odc file to get translated values. How can I verify that my translation are being save in database correctly?

P.S. same result for Adventure works as well. caption column contains the same value as Level name.
Nov 2, 2015 at 8:52 AM
I found this helping query.
WITH 
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT 
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]
But this return the translations of members of attribute not of attribute itself.
Coordinator
Nov 4, 2015 at 5:11 AM
muhammadali330 wrote:
yes, i have to provide locale identifier in my odc file to get translated values. How can I verify that my translation are being save in database correctly?
Ah ok - that's going to be the issue then. Excel seems to create a new connection when you drillthrough and when it does that it looks like it resets the Locale Identifier using the actual local off the client machine.

You'll either need to create a test translation for the locale of your workstation. Or you'll need to change your locale or setup a VM with another locale in order to test this.
Marked as answer by furmangg on 11/11/2015 at 3:58 AM
Nov 4, 2015 at 5:14 AM
Thanks Darren. Let me try this.. will update you.
Nov 4, 2015 at 5:25 AM
It worked. Many thanks Darren for all your support. Appreciated.