Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: Generate XMI file with metadata API

  1. #1
    Join Date
    Apr 2013
    Posts
    8

    Default Generate XMI file with metadata API

    Morning all,
    I am trying to generate an XMI file automatically by the API. At moment I can read an XMI file that already exists and save it in other file.
    I can't modify it ... I don't understand the hierarchy behind the domains, logical tables, logical column.
    Someone can halp me? I would know what classes I must use to modify, add or delete a table (and columns) in a schema.

    Thanks to all,

    Christian

  2. #2

    Default

    I am having the same issue, PLEASE can someone actually answer this question!

  3. #3

    Default

    I finally got an answer, got it to work, now I am working through some of the issues and trying to make sure I am doing this right, will repost an example once I confirm that it works. In the mean time you may check out http://stackoverflow.com/questions/1...ad-hoc-reports

  4. #4

    Default

    Here is the code that I have used to generate the XMI File in Java, If anyone has any code review suggestions on how to do this better, let me know. Note that for my application I am generating the XMI for a datasource. It appears that the XMI I have generated is missing a few key things when compared to that which is generated in the PUC. If anyone knows how to configure a datasource and add it to the domain that would be very helpful.

    Database db = dbCon.getDb();
    File xmiFile = new File(xmiLocation);

    //Initialize the Kettle Environment, if this was not done the dbMeta will fail
    KettleEnvironment.init();

    //Initialize the Props. Still a bit confused as to what the integeter indicates, but this must be initialized as well
    Props.init(0);

    //create the string of they type of sql. Rediculous that Pentaho uses Strings for this...
    String type ="";
    switch(db.getFlavor()){
    case MY_SQL:
    type = "Mysql";
    break;
    case ORACLE:
    type = "Oracle";
    break;
    case SQL_SERVER:
    type = "Mssql";
    break;
    case HSQL:
    type = "Hsql";
    break;
    case VERTICA:
    type = "Vertica";
    break;
    }

    //Used to connect to the database and retreive all the information from it.
    DatabaseMeta dbMeta = new DatabaseMeta(db.getDbName(), type, "JDBC", db.getHost(), db.getDbName(), db.getPort(), db.getUser(), db.getPassword());

    //Domains can hold physical and logical table structures, this will be populated in order to generate XMI (and mondrian,
    //but didn't want to include that dependency if I could help it)
    Domain domain = null;

    //Creating an AutoModeler, Mimicked the ModelerService class in constructing with all null values.
    AutoModeler generator = new AutoModeler(null, null, null, null);

    //Configure the generator
    String locale = LocalizedString.DEFAULT_LOCALE;
    generator.setLocale(locale);
    generator.setDatabaseMeta(dbMeta);
    generator.setModelName(this.dsName);

    //Get the schema namse from the dbtable
    SchemaTable tableNames[] = new SchemaTable[1];
    tableNames[0] = new SchemaTable(db.getDbName(), this.tableName);

    //generate domain and set id and name attributes
    generator.setTableNames(tableNames);
    domain = generator.generateDomain();
    domain.setId(this.dsName);
    LocalizedString string = new LocalizedString();
    string.setString(locale, this.dsName);
    domain.setName(string);

    //Create the logicalconfiguration inside the domain.
    LogicalModel businessModel = domain.getLogicalModels().get(0);
    businessModel.setProperty("AGILE_BI_GENERATED_SCHEMA", "TRUE");
    businessModel.setProperty("DUAL_MODELING_SCHEMA", ""+true);

    businessModel.setName(new LocalizedString(locale, this.dsName));
    businessModel.setDescription(new LocalizedString(locale, "This is the data model for "+this.dsName));

    LogicalTable businessTable = businessModel.getLogicalTables().get(0);
    businessTable.setName(new LocalizedString(locale, businessTable.getPhysicalTable().getName(locale)));

    //XMI parser will parse the domain into an XMI File
    XmiParser parser = new XmiParser();

    new FileOutputStream(xmiLocation).write(parser.generateXmi(domain).getBytes("UTF-8"));

    return xmiFile;


    P.S. All I needed to add all the needed dependencies for this was add the following dependency to my Maven POM file

    <dependency>
    <groupId>pentaho</groupId>
    <artifactId>pentaho-metadata</artifactId>
    <version>3.4.5</version>
    </dependency>
    Last edited by and.decarlo; 08-05-2013 at 02:55 PM.

  5. #5
    Join Date
    Apr 2013
    Posts
    8

    Default

    Quote Originally Posted by benslinkard View Post
    Hi,

    Even I am Facing the same problem plz let me know the correct answer If any one knows the answer.

    Regards,
    Benslinkard
    Hi,
    in this days I will try to generate XMI using API and I will post my code and I hope to get results.
    Keep in touch.
    Best regards,

    Christian

  6. #6

    Default

    Seriously, did my reply not show up? I have code below?

  7. #7
    Join Date
    Apr 2013
    Posts
    8

    Default

    Quote Originally Posted by and.decarlo View Post
    Seriously, did my reply not show up? I have code below?
    I can't see nothing below... :/

  8. #8

    Default

    Here is the code that I have used to generate the XMI File in Java, If anyone has any code review suggestions on how to do this better, let me know. Note that for my application I am generating the XMI for a datasource. It appears that the XMI I have generated is missing a few key things when compared to that which is generated in the PUC. If anyone knows how to configure a datasource and add it to the domain that would be very helpful.

    Database db = dbCon.getDb();
    File xmiFile = new File(xmiLocation);

    //Initialize the Kettle Environment, if this was not done the dbMeta will fail
    KettleEnvironment.init();

    //Initialize the Props. Still a bit confused as to what the integeter indicates, but this must be initialized as well
    Props.init(0);

    //create the string of they type of sql. Rediculous that Pentaho uses Strings for this...
    String type ="";
    switch(db.getFlavor()){
    case MY_SQL:
    type = "Mysql";
    break;
    case ORACLE:
    type = "Oracle";
    break;
    case SQL_SERVER:
    type = "Mssql";
    break;
    case HSQL:
    type = "Hsql";
    break;
    case VERTICA:
    type = "Vertica";
    break;
    }

    //Used to connect to the database and retreive all the information from it.
    DatabaseMeta dbMeta = new DatabaseMeta(db.getDbName(), type, "JDBC", db.getHost(), db.getDbName(), db.getPort(), db.getUser(), db.getPassword());

    //Domains can hold physical and logical table structures, this will be populated in order to generate XMI (and mondrian,
    //but didn't want to include that dependency if I could help it)
    Domain domain = null;

    //Creating an AutoModeler, Mimicked the ModelerService class in constructing with all null values.
    AutoModeler generator = new AutoModeler(null, null, null, null);

    //Configure the generator
    String locale = LocalizedString.DEFAULT_LOCALE;
    generator.setLocale(locale);
    generator.setDatabaseMeta(dbMeta);
    generator.setModelName(this.dsName);

    //Get the schema namse from the dbtable
    SchemaTable tableNames[] = new SchemaTable[1];
    tableNames[0] = new SchemaTable(db.getDbName(), this.tableName);

    //generate domain and set id and name attributes
    generator.setTableNames(tableNames);
    domain = generator.generateDomain();
    domain.setId(this.dsName);
    LocalizedString string = new LocalizedString();
    string.setString(locale, this.dsName);
    domain.setName(string);

    //Create the logicalconfiguration inside the domain.
    LogicalModel businessModel = domain.getLogicalModels().get(0);
    businessModel.setProperty("AGILE_BI_GENERATED_SCHEMA", "TRUE");
    businessModel.setProperty("DUAL_MODELING_SCHEMA", ""+true);

    businessModel.setName(new LocalizedString(locale, this.dsName));
    businessModel.setDescription(new LocalizedString(locale, "This is the data model for "+this.dsName));

    LogicalTable businessTable = businessModel.getLogicalTables().get(0);
    businessTable.setName(new LocalizedString(locale, businessTable.getPhysicalTable().getName(locale)));

    //XMI parser will parse the domain into an XMI File
    XmiParser parser = new XmiParser();

    new FileOutputStream(xmiLocation).write(parser.generateXmi(domain).getBytes("UTF-8"));

    return xmiFile;


    P.S. All I needed to add all the needed dependencies for this was add the following dependency to my Maven POM file

    <dependency>
    <groupId>pentaho</groupId>
    <artifactId>pentaho-metadata</artifactId>
    <version>3.4.5</version>
    </dependency>

    Please tell me you see this.

  9. #9
    Join Date
    Apr 2013
    Posts
    8

    Default

    Quote Originally Posted by and.decarlo View Post
    Here is the code that I have used to generate the XMI File in Java, If anyone has any code review suggestions on how to do this better, let me know. Note that for my application I am generating the XMI for a datasource. It appears that the XMI I have generated is missing a few key things when compared to that which is generated in the PUC. If anyone knows how to configure a datasource and add it to the domain that would be very helpful.

    Database db = dbCon.getDb();
    File xmiFile = new File(xmiLocation);

    //Initialize the Kettle Environment, if this was not done the dbMeta will fail
    KettleEnvironment.init();

    //Initialize the Props. Still a bit confused as to what the integeter indicates, but this must be initialized as well
    Props.init(0);

    //create the string of they type of sql. Rediculous that Pentaho uses Strings for this...
    String type ="";
    switch(db.getFlavor()){
    case MY_SQL:
    type = "Mysql";
    break;
    case ORACLE:
    type = "Oracle";
    break;
    case SQL_SERVER:
    type = "Mssql";
    break;
    case HSQL:
    type = "Hsql";
    break;
    case VERTICA:
    type = "Vertica";
    break;
    }

    //Used to connect to the database and retreive all the information from it.
    DatabaseMeta dbMeta = new DatabaseMeta(db.getDbName(), type, "JDBC", db.getHost(), db.getDbName(), db.getPort(), db.getUser(), db.getPassword());

    //Domains can hold physical and logical table structures, this will be populated in order to generate XMI (and mondrian,
    //but didn't want to include that dependency if I could help it)
    Domain domain = null;

    //Creating an AutoModeler, Mimicked the ModelerService class in constructing with all null values.
    AutoModeler generator = new AutoModeler(null, null, null, null);

    //Configure the generator
    String locale = LocalizedString.DEFAULT_LOCALE;
    generator.setLocale(locale);
    generator.setDatabaseMeta(dbMeta);
    generator.setModelName(this.dsName);

    //Get the schema namse from the dbtable
    SchemaTable tableNames[] = new SchemaTable[1];
    tableNames[0] = new SchemaTable(db.getDbName(), this.tableName);

    //generate domain and set id and name attributes
    generator.setTableNames(tableNames);
    domain = generator.generateDomain();
    domain.setId(this.dsName);
    LocalizedString string = new LocalizedString();
    string.setString(locale, this.dsName);
    domain.setName(string);

    //Create the logicalconfiguration inside the domain.
    LogicalModel businessModel = domain.getLogicalModels().get(0);
    businessModel.setProperty("AGILE_BI_GENERATED_SCHEMA", "TRUE");
    businessModel.setProperty("DUAL_MODELING_SCHEMA", ""+true);

    businessModel.setName(new LocalizedString(locale, this.dsName));
    businessModel.setDescription(new LocalizedString(locale, "This is the data model for "+this.dsName));

    LogicalTable businessTable = businessModel.getLogicalTables().get(0);
    businessTable.setName(new LocalizedString(locale, businessTable.getPhysicalTable().getName(locale)));

    //XMI parser will parse the domain into an XMI File
    XmiParser parser = new XmiParser();

    new FileOutputStream(xmiLocation).write(parser.generateXmi(domain).getBytes("UTF-8"));

    return xmiFile;


    P.S. All I needed to add all the needed dependencies for this was add the following dependency to my Maven POM file

    <dependency>
    <groupId>pentaho</groupId>
    <artifactId>pentaho-metadata</artifactId>
    <version>3.4.5</version>
    </dependency>

    Please tell me you see this.
    Perfect! Now I can see it! )
    And is it good code? Does it work well?

    Ciao

  10. #10

    Default

    I mean it works getting an XMI document created, for my purposes I am still at a bit of a loss, I need to create a XMI for a datasource that will be used for reporting and analysis. This seems to produce a good XMI but is missing a couple key things that the XMI produced from the BI Server has, so I am still looking for that.

  11. #11
    Join Date
    Apr 2013
    Posts
    8

    Default

    Quote Originally Posted by and.decarlo View Post
    I mean it works getting an XMI document created, for my purposes I am still at a bit of a loss, I need to create a XMI for a datasource that will be used for reporting and analysis. This seems to produce a good XMI but is missing a couple key things that the XMI produced from the BI Server has, so I am still looking for that.
    I need to create an tool that generate XMI file, the table names and relation it be stored in a database. So at first I get information from database and than I will create XMI file. Do you think it is possible to create same of this?

    Ciao

  12. #12

    Default

    I am not really sure, I think this would work, the only advice I can give is give it a try and look at the output, I am pretty new to the Pentaho world, so I am not too sure. Sorry, hope it works for you.

  13. #13
    Join Date
    Apr 2013
    Posts
    8

    Default

    Perfect! In the next days I work on it and I give you news (I hope good news). I am new in pentaho world too... so don't worry
    Thank you a lot for the help.

  14. #14

    Default

    Sure thing, I know it can be frustrating when no one answers a questions around here.... Found this in the java docs for the AutoModeler (the class responsible for generating the domains) "The model WILL NOT CONTAIN RELATIONSHIPS / JOINS!! Those need to be added later." I am looking for where the "Later" is as this would be helpful for my specific scenario.

  15. #15

    Default

    Found another bit of code that could be helpful to someone. This seems to be more in line with what I am trying to accomplish, as I can now create JOINS and set a FACT TABLE. However the XMI is not picked up by the biserver even after I have it produced. If anyone knows why PLEASE HELP.

    My Code is as follows. A little simpler this time.

    KettleEnvironment.init();
    Props.init(Props.TYPE_PROPERTIES_EMPTY);

    if(ModelerMessagesHolder.getMessages() == null){
    ModelerMessagesHolder.setMessages(new SpoonModelerMessages());
    }

    workspace = new ModelerWorkspace(new ModelerWorkspaceHelper(LOCALE), null);
    databaseMeta = new DatabaseMeta("SPSSTest","Mysql","JDBC","localhost","SPSSTest","3306","root","password");

    List<JoinRelationshipModel> joins = new ArrayList<JoinRelationshipModel>();

    JoinTableModel joinTable1 = new JoinTableModel();
    joinTable1.setName("SPSSTest");

    JoinTableModel joinTable2 = new JoinTableModel();
    joinTable2.setName("SPSSTest_NUMER16");

    JoinRelationshipModel join1 = new JoinRelationshipModel();
    JoinFieldModel lField1 = new JoinFieldModel();
    lField1.setName("numer16");
    lField1.setParentTable(joinTable1);
    join1.setLeftKeyFieldModel(lField1);

    JoinFieldModel rField1 = new JoinFieldModel();
    rField1.setName("code");
    rField1.setParentTable(joinTable2);
    join1.setRightKeyFieldModel(rField1);

    joins.add(join1);
    SchemaModel model = new SchemaModel();
    model.setJoins(joins);
    model.setFactTable(joinTable1);

    MultiTableModelerSource modelerSource = new MultiTableModelerSource(databaseMeta, model, "SPSSTest", Arrays.asList("SPSSTest", "SPSSTest_NUMER16"));
    Domain domain = modelerSource.generateDomain();

    XmiParser parser = new XmiParser();
    new FileOutputStream("src/test/resources/test2.xmi").write(parser.generateXmi(domain).getBytes("UTF-8"));
    Last edited by and.decarlo; 08-20-2013 at 06:33 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.