none
XMLA script to create or rename SSAS Translations

    Вопрос

  •  

    Hello there,

    I would like to create or rename SSAS translations by XMLA script. The script should generate from a SQL table where I will have the SQL column name for respective SSAS database objects and the related Translation caption. The idea is to create all the translations for all the cubes/dimensions/measures by one XMLA script in a simple way.

     Any suggestions / XMLA script will be helpful.

    Regards,

    Palash

    • Изменен тип Palash Aich 2 марта 2012 г. 11:48 Not getting the help
    • Изменен тип Palash Aich 2 марта 2012 г. 11:48 Not getting the help
    28 февраля 2012 г. 15:29

Все ответы

  • Try this list:  http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    29 февраля 2012 г. 1:14
  •  

    Hello Russ,

    The link is not helpful.

    Does anyone have any suggestions on this?

    Thanks,

    Palash

    29 февраля 2012 г. 14:31
  • My suggestion is that you are almost certain to find someone to answer your question on that list.  This is XML and only a few SSAS people monitor it.  However, your question is not really XML it is SSAS.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    29 февраля 2012 г. 14:36
  • Sorry I reread your post.

    What I need from you is the exact XMLA that you want (with namespace declarations).  Then indicate which pieces you want to generate from a table.  I or someone else should be able to give you some ideas.


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    29 февраля 2012 г. 14:48
  • Thank You Russ!

     

    First step by XMLA I will deploy the cube which is fine. Next step I would like to alter the cube by another XMLA script. This script will contain only translation related information. This is nothing but creating translation against required attributes in all those required dimensions. If we can achieve till this than I will keep all those Dimension, Dimension Attributes and Translation caption in a table from where I should be able to generate the XMLA. The Idea is keep the required Translation caption in a table and execute one XMLA to create or rename all of those without going to BIDS.

    Thanks,

    Palash

    29 февраля 2012 г. 15:07
  • I can't do this easily without a sample XMLA for what you want to do.  You can post it here, indicating in xml comments what you want to be dynamic.


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    29 февраля 2012 г. 15:11
  • Hello Russel,
    As a first step I am having a XML file to rename some translations. I would like to make this file to be executable against the SSAS database by alter tag. Can you give me some suggestions to modify the below code which will be executable as XMLA to alter the database.

    <?xml version="1.0" encoding="utf-8"?>
    <XmlNormalisator xmlns="http://www.shmondja.net/Yury">

        <!--
        Namespace    - MinOccurs=0, MaxOccurs=unbounded
            @Prefix    - MinOccurs=1, MaxOccurs=1, Value:<Non Empty!!!>
            @URI    - MinOccurs=1, MaxOccurs=1, Value:<Non Empty!!!>
        -->
        <Namespace Prefix="x" URI="http://schemas.microsoft.com/analysisservices/2003/engine" />
        <Namespace Prefix="xsd" URI="http://www.w3.org/2001/XMLSchema" />
        <Namespace Prefix="xsi" URI="http://www.w3.org/2001/XMLSchema-instance" />
        <Namespace Prefix="ddl2" URI="http://schemas.microsoft.com/analysisservices/2003/engine/2" />
        <Namespace Prefix="ddl2_2" URI="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" />
        <Namespace Prefix="dwd" URI="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" />

        <!--
        SetValue                - MinOccurs=0, MaxOccurs=1
            @Enabled            - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="False"
            Item                - MinOccurs=1, MaxOccurs=unbounded
                @Enabled        - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="False"
                @CreateIfAbsend    - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="False"
                @Description    - MinOccurs=0, MaxOccurs=1
                Path            - MinOccurs=1, MaxOccurs=1
                TargetChild        - MinOccurs=1, MaxOccurs=unbounded
                Value            - MinOccurs=1, MaxOccurs=1
        -->
        <SetValue Enabled="True">

            <Item Enabled="True" CreateIfAbsend="True" Description="Translation Dimension [Customer] Deutsch (1031)">
                <Path>/x:Dimension/x:Translations/x:Translation[../../x:Name/text()="Customer" and ./x:Language/text()="1031"]</Path>
                <TargetChild>x:Caption</TargetChild>
                <Value>Kunde</Value>
            </Item>

            <Item Enabled="True" CreateIfAbsend="True" Description="Translation Dimension.Attribut [Customer].[Gender] Deutsch (1031)">
                <Path>/x:Dimension/x:Attributes/x:Attribute/x:Translations/x:Translation[../../../../x:Name/text()="Customer" and ../../x:Name/text()="Gender" and ./x:Language/text()="1031"]</Path>
                <TargetChild>x:Caption</TargetChild>
                <Value>Geschlecht</Value>
            </Item>
           
            <Item Enabled="True" CreateIfAbsend="True" Description="Translation Dimension.Hierarchie [Customer].[Customer Geography] Deutsch (1031)">
                <Path>/x:Dimension/x:Hierarchies/x:Hierarchy/x:Translations/x:Translation[../../../../x:Name/text()="Customer" and ../../x:Name/text()="Customer Geography" and ./x:Language/text()="1031"]</Path>
                <TargetChild>x:Caption</TargetChild>
                <Value>Kunden-Geographie</Value>
            </Item>
           
            <Item Enabled="True" CreateIfAbsend="True" Description="Translation Dimension.Hierarchie.Level [Customer].[Customer Geography].[Country] Deutsch (1031)">
                <Path>/x:Dimension/x:Hierarchies/x:Hierarchy/x:Levels/x:Level/x:Translations/x:Translation[../../../../../../x:Name/text()="Customer" and ../../../../x:Name/text()="Customer Geography" and ../../x:Name/text()="Country" and ./x:Language/text()="1031"]</Path>
                <TargetChild>x:Caption</TargetChild>
                <Value>Land</Value>
            </Item>
        </SetValue>

        <!--
        FormatXml                    - MinOccurs=0, MaxOccurs=1
            @Enabled                - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="False"
            @OmitXmlDeclaration        - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="False"
            @NewLineOnAttributes    - MinOccurs=0, MaxOccurs=1, TrueValue="True", DefaultValue="True"
        -->
        <FormatXml Enabled="True" OmitXmlDeclaration="False" NewLineOnAttributes="True" />  
    </XmlNormalisator>

    Regards,
    Palash

    29 февраля 2012 г. 16:29
  • When I try to execute the above code I get the below error. Any idea why this error?

    XML parsing failed at line 7, column 53: A text/xml declaration may occur only at the very beginning of input.

    2 марта 2012 г. 11:45
  • Which application is giving you the error? 

    I am still very, very curious:  what does the XMLA look like that you want to generate? Your XML does not look like XMLA (I have limited XMLA experience, so I may have missed something). If you were to hard code the XMLA to achieve what you want, what is the exact XMLA?  My understanding (based on a glance of google results) is that you need more than one script to achieve what you want.  If you do not know and do not know how to generate the XMLA, I refer you first to the SSAS list that I gave you in the first place.  Once you have sample XMLA, post it with indications in comments which pieces of information are variable.


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    2 марта 2012 г. 13:17
  •  

    Hello Russel,

    XMLA is nothing but XML for analysis services. If we want to alter / create anything we can execute XMLA in SSAS. It is just the XML. When we right click in any Analysis services database for a new query we get the 3 options. MDX, DMX and the XMLA. We can execute XML code in XMLA query window over there. We create SSAS database by XMLA. I need a script that will alter the SSAS database after the creation of it. Alter to add the translations in it. The below link will give you the code to alter translations. The problem is we can’t create all the translations of database by a single script as per the below post. I need a simple way to create translations instead of writing lots of lines of code as this file will be modified by end user based on their needs frequently.

    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/1073bafb-ba90-4161-bc21-0477217d5647

    Regards,

    Palash

    2 марта 2012 г. 17:19
  •   Hello Russel,

    XMLA is nothing but XML for analysis services.

    That is just plain silly.  You know that a docx file nothing but XML.  An SSIS package, an SSRS report is nothing but XML.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    2 марта 2012 г. 17:29
  • My point above ("Your XML does not look like XMLA (I have limited XMLA experience, so I may have missed something).") still stands.  The XMLA in the link you provided looks totally unlike the XML in the link you provided.


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    2 марта 2012 г. 17:35
  • Thank you Russel for the reply and your efforts!
    I am trying to achieve the same thing mentioned in the link. The link tells that we cannot create all the translations by a single XMLA. But I need to create all the translations by a single XMLA script. I am open with XML or any other script to achieve the goal. Ultimately need to create all the translations of the cube by simple way. Argument is not XML or XMLA but solution of this scenario.

    Regards,

    Palash

    3 марта 2012 г. 17:54
  • Check this it might help you for the solution.

    http://www.ssas-info.com/analysis-services-faq/29-mgmt/103-how-build-xmla-script-from-solution-project-interactively

  • Hi,

    I've had a similar problem, where a script fails down to the exact same line and column number, so I suspect it's a bug somewhere in the software.

    The upside of it is, it will run if you remove the <?xml version="1.0" encoding="utf-8"?>.

    You can use notepad++ to convert your xmla files to utf-8 if you need to.

    HTH

    Peter 

    24 сентября 2012 г. 14:54