More on Accessing Dynamics GP Data using Pentaho CE 3.5

My first post on using Pentaho to access Dynamics GP data in SQL Server left off with me being stuck in limbo.  Since then I have downloaded the Pentaho Enterprise Edition trial version, which provided me with access to the very helpful Pentaho Knowledgebase.  The Pentaho KB has very good information about installing, configuring, and using their software.  I also stumbled upon a great little guide for using Pentaho Metadata editor to setup data sources for use in the Pentaho User Console.  Using this guide and some info from the Pentaho KB, I have now setup data sources that access Dynamics GP data residing in SQL Server that can be used in the console for Ad Hoc Report creation.

I extracted the Metadata Editor to my c:\pentaho\design-tools folder.  Inside the newly created folder, metadata-editor, put the sqljdbc.jar file in the libext/JDBC folder.  This is to access MS SQL Server.

Now in the Pentaho Admin console, I setup a new data connection called TWO, and specify my connection information.  I am using the Microsoft SQL Server JDBC driver, which is located in the following folders for the Admin Console and the User Console to access this driver:

• c:\pentaho\server\administration-console\jdbc
• c:\pentaho\server\biserver-ce\tomcat\common\lib

If you have not put the sqljdbc.jar file in these folders, do so, and then restart the Admin Console and the main BI Server.

Now I follow the quick guide mentioned earlier, and start by editing the c:\pentaho\design-tools\metadata-editor\simple-jndi\jdbc.properties file to add my custom datasource.  My section looks like this:

TWO/type=javax.sql.DataSource
TWO/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
TWO/url=jdbc:sqlserver://localhost;databaseName=TWO
TWO/user=username
TWO/password=mypassword

Use the same name as what you called the data connection in the Admin Console.  Next I open the Metadata editor by running metadata-editor.bat.

First step is to add a new connection.  I thought I could use a JNDI connection because I had set it up previously, but that did not work, so I added a new connection of type MS SQL Server and Native (JDBC) as the access method.  Name it the same as what you have used earlier - in my case TWO.  After I confirmed this, a window popped up asking me to import any tables I wanted.  I selected a few needed for accessing Customer information.

Next is to make a new Business Model by right-clicking on Business Models and selecting "New Business Model."  Enter at least an ID and a Name.  This name will show up in the Pentaho User Console as an available Data Source when making an Ad Hoc Report.

In the Metadata editor, you can give pretty names to tables and columns, instead of the sometimes cryptic hard names (especially in GP!).  This can be done at the main Connection level, which would display in the Business Views, or at the Business View layer itself you can override what is already setup.

I opened up some customer tables and edited the names of the tables and the names of the columns to make them more presentable.  These names show up in the Ad Hod Report designer, so make them easy to understand.  I made my changes after dragging the tables into the Business Table area under my new Business Model.

After creating my relationships between the tables and editing my Metadata to override the GP cryptic table and column names, I created some Business View Categories.  These are not so much dimensions in the sense of an OLAP cube, but categories for selecting and organizing data that you want to include as available to add to a report.  These categories will show in the Ad Hoc Report creation tool, and the data will be grouped as you define it here.  I made three categories to test it out - Customer Address Info, Customer Basic Info, Customer Summary Info.  I used the Manage Categories feature to add the tables to the correct categories.

Now our model is done, and is ready to be published to the Pentaho server.  First, save this definition.  Now you can publish to the server.  But before we do that, I created a new folder in the Pentaho Solution repository, because there can be only one metadata.xmi file for each folder.

Using the Pentaho User Console, I created a new folder called gp-two.  I did this because I read there should be no spaces in the folder names.  You can change the display name by going to c:\pentaho\server\biserver-ce\pentaho-solutions\gp-two and editing the index.xml file.

I also needed to setup the publishing password, which is blank by default in the Community Edition.  The file to edit is c:\pentaho\biserver-ce\pentaho-solutions\system\publisher_config.xml.  I did not restart the biserver, and it still worked for me.

So now back in Metadate Editor, File - Publish to Server will open up the dialog box.  Enter the appropriate information, including the newly set publishing password, and a username/password combo, and the folder, in my case gp-two.  This will create the metadata.xmi file that will be used when creating an Ad Hoc Report.  Anytime you make changes to the model, save and publish!

Back in the Pentaho User Console, I want to create a new Ad Hoc Report using my newly created Business Model.  When I open a new Ad Hoc Report, I see my new Data Source, Customer Info, which is the name of my Business Model in Metadata Editor.

I select the new Data Source and hit Next to move to the next step of selecting the fields I want on the report.  In this screen, you see the fields grouped by the categories we defined in Metadata Editor.  Select what you need and move along.

After confirming the selections and giving the report a page header on the last step, save the report and preview it.  It works!  Woohoo!