none
Excel 2007 Translation problem RRS feed

  • Question

  • Hi!

     

    I want to see German translations of the cube elements in Excel.

    But I have a problem with Translations when connecting to a AS 2005 cube from Excel 2007, when the Excel workbook is saved on a Sharepoint Server.

     

    I do the following steps:

    1. create a data connection to a AS 2005 cube in Excel 2007 (Excel is installed in German language / Windows is als installed in German)

    2. save the Excel workbook on Sharepoint Server (Sharepoint ist installed in English with German language pack / Windows Server in English)

    3. Open the workbook from Sharepoint or save it locally and open it

    4. open Data connection properties and try to clear the checkbox "Retrieve data and errors in the Office display language when available "

     

    When I do this steps, the following error is shown: "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server."

     

    After some searching on the Internet, I found out, that this is a known issue by Microsoft.

    See http://support.microsoft.com/kb/931388/en-us

     

    I tried to implement the third workaround on this page ("Add the locale identifier to the .odc file. ").

    But it doesn't work. I tried to add the key "Language Identifier=1031" to the ODC-File, but without success.

     

    The only solution is to create a completely new data connection in Excel for this workbook.

    Then the translation works again.

     

    Does anybody know this issue and can provide some more help / workarounds?

     

    Thanks,

    Helmut

    Monday, August 25, 2008 1:54 PM

All replies

  • After modifying the .odc file, did you try the following:

    • Open the Excel PivotTable from Sharepoint.
    • Select Data|Connections from the menu bar.
    • Highlight the connection that is the problem and click on the Properties button.
    • In the dialog, select the Definition tab and change the connection to the modified .odc file in your DLC.
    • Republish the Excel workbook.

    You may have to approve both the .odc file and the Excel workbook if workflow is enforced to have the changes visible to others. My guess if the reconstructed workbook was working correctly that the steps above would correct existing workbooks.

    Monday, August 25, 2008 5:48 PM
  • I'm afraid, the things are getting more complicated...

     

    If I want to open an .ODC-File with the "Language Identifier=1031" key, I get an error message in Excel: "Cannot initialize datasource."

     

    Here is the code of my two ODC-Files.

     

    1. .ODC-File without Language Identifier

    ==============================

     

    Code Snippet

    <html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns="http://www.w3.org/TR/REC-html40">

    <head>
    <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
    <meta name=ProgId content=ODC.Cube>
    <meta name=SourceType content=OLEDB>
    <meta name=Catalog content="KIWI_Reporting">
    <meta name=Table content="KIWI_Reporting">
    <title>wrzsqlrep01 KIWI_Reporting</title>
    <xml id=docprops><o:DocumentProperties
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns="http://www.w3.org/TR/REC-html40">
      <o:Name>wrzsqlrep01 KIWI_Reporting</o:Name>
     </o:DocumentProperties>
    </xml><xml id=msodc><odc:OfficeDataConnection
      xmlns:odc="urn:schemas-microsoft-com:office:odc"
      xmlns="http://www.w3.org/TR/REC-html40">
      <odc:Connection odc:Type="OLEDB">
       <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=wrzsqlrep01;Initial Catalog=KIWI_Reporting</odc:ConnectionString>
       <odc:CommandType>Cube</odc:CommandType>
       <odc:CommandText>KIWI_Reporting</odc:CommandText>
      </odc:Connection>
     </odc:OfficeDataConnection>
    </xml>
    <style>
    <!--
        .ODCDataSource
        {
        behavior: url(dataconn.htc);
        }
    -->
    </style>
     
    </head>

    <body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
    <table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
      <tr>
        <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
          &nbsp;
        </td>
         <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

          &nbsp;
        </td>
      </tr>
      <tr>
        <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>

          &nbsp;
        </td>
      </tr>
      <tr>
        <td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
          <div id='pt' style='height: 100%' class='ODCDataSource'></div>
        </td>
      </tr>
    </table>


    <script language='javascript'>

    function init() {
      var sName, sDescription;
      var i, j;
     
      try {
        sName = unescape(location.href)
     
        i = sName.lastIndexOf(".")
        if (i>=0) { sName = sName.substring(1, i); }
     
        i = sName.lastIndexOf("/")
        if (i>=0) { sName = sName.substring(i+1, sName.length); }

        document.title = sName;
        document.getElementById("tdName").innerText = sName;

        sDescription = document.getElementById("docprops").innerHTML;
     
        i = sDescription.indexOf("escription>")
        if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }

        if (i>=0 && j >= 0) {
          j = sDescription.lastIndexOf("</", j);

          if (j>=0) {
              sDescription = sDescription.substring(i+11, j);
            if (sDescription != "") {
                document.getElementById("tdDesc").style.fontSize="x-small";
              document.getElementById("tdDesc").innerHTML = sDescription;
              }
            }
          }
        }
      catch(e) {

        }
      }
    </script>

    </body>
     
    </html>

     

     

    2. .ODC-File WITH Language Identifier

    ==============================

     

    Code Snippet

    <html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns="http://www.w3.org/TR/REC-html40">

    <head>
    <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
    <meta name=ProgId content=ODC.Cube>
    <meta name=SourceType content=OLEDB>
    <meta name=Catalog content="KIWI_Reporting">
    <meta name=Table content="KIWI_Reporting">
    <title>wrzsqlrep01 KIWI_Reporting DE</title>
    <xml id=docprops><o:DocumentProperties
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns="http://www.w3.org/TR/REC-html40">
      <o:Name>wrzsqlrep01 KIWI_Reporting DE</o:Name>
     </o:DocumentProperties>
    </xml><xml id=msodc><odc:OfficeDataConnection
      xmlns:odc="urn:schemas-microsoft-com:office:odc"
      xmlns="http://www.w3.org/TR/REC-html40">
      <odc:Connection odc:Type="OLEDB">
       <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=wrzsqlrep01;Initial Catalog=KIWI_Reporting;Language Identifier=1031</odc:ConnectionString>
       <odc:CommandType>Cube</odc:CommandType>
       <odc:CommandText>KIWI_Reporting</odc:CommandText>
      </odc:Connection>
     </odc:OfficeDataConnection>
    </xml>
    <style>
    <!--
        .ODCDataSource
        {
        behavior: url(dataconn.htc);
        }
    -->
    </style>
     
    </head>

    <body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
    <table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
      <tr>
        <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
          &nbsp;
        </td>
         <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>

          &nbsp;
        </td>
      </tr>
      <tr>
        <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>

          &nbsp;
        </td>
      </tr>
      <tr>
        <td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
          <div id='pt' style='height: 100%' class='ODCDataSource'></div>
        </td>
      </tr>
    </table>


    <script language='javascript'>

    function init() {
      var sName, sDescription;
      var i, j;
     
      try {
        sName = unescape(location.href)
     
        i = sName.lastIndexOf(".")
        if (i>=0) { sName = sName.substring(1, i); }
     
        i = sName.lastIndexOf("/")
        if (i>=0) { sName = sName.substring(i+1, sName.length); }

        document.title = sName;
        document.getElementById("tdName").innerText = sName;

        sDescription = document.getElementById("docprops").innerHTML;
     
        i = sDescription.indexOf("escription>")
        if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }

        if (i>=0 && j >= 0) {
          j = sDescription.lastIndexOf("</", j);

          if (j>=0) {
              sDescription = sDescription.substring(i+11, j);
            if (sDescription != "") {
                document.getElementById("tdDesc").style.fontSize="x-small";
              document.getElementById("tdDesc").innerHTML = sDescription;
              }
            }
          }
        }
      catch(e) {

        }
      }
    </script>

    </body>
     
    </html>

     

     

     

     

    I figured out a second problem during my testings:

     

    1. I open the Excel workbook from Sharepoint

    2. I open a Excel workbook, which is locally stored, with the .ODC-File without Language Identifier

    3. ==> I get the error from the Microsoft known issue. (

    "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube." )

     

     

     

     

     

    Tuesday, August 26, 2008 10:23 AM
  • Hi!
    While searching the way how to open AS 2005 cubes from excel with different translations I have found this topic.
    W
    hen tried to add “Language Identifier” to ODC file connection string had the same error - "Cannot initialize datasource.".
    The information about language specification in connection string  provided in “
    Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables” is incorrect. Instead of “Language Identifier” you should use “Locale Identifier”. For more information about format of ODC file and OLEDB connection string see http://msdn.microsoft.com/en-us/library/cc514373.aspx  and  http://msdn.microsoft.com/en-us/library/cc678147(SQL.90).aspx

    Thursday, April 2, 2009 8:29 PM
  • Hi eager man of IcT,

    Symptom:
    while connecting to ssas cube using excel (error):
    "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube. " )

    While browsing the Cube in ssms or BIDS = no problemo.

    Problem:
    In BIDS I had a dimention attribute (Customer Name)   AttributeHierarchyEnabled set to False.
    And before this, I maked some Named Set calculations in de CUBE Calcuation tab where using this  (Customer Name) attribute.

    Solution:
    attribute (Customer Name)   AttributeHierarchyEnabled set to True,
    Reprocess Full.

    Result:
    Happy as can be.

    Time cost:
    +-1h googling


    Friday, December 4, 2009 12:23 PM