I have had a recent email conversation with a gentleman about OpenERP and its readiness for US-based small businesses. It's an excellent question, and one I am currently evaluating, and have been for a month or two.
OpenERP, if you do not know, is a modular-based ERP software solution that is more of a framework than most ERP solutions I have seen. It is built on Postgres and Python, two of my favorite things. The website touts over 300 modules that are available to plug into your OpenERP solution. It's feature list is large and broad, to say the least, with modules for Document Management, Wiki, Portals, HR, Projects, and more.
I have downloaded and installed OpenERP on Windows and on Linux. There is a fat client and also a web client with equal functionality (as far as I have seen so far). There is also a SaaS version called Odoo (http://odoo.com) that is very reasonably priced. Although the SaaS version does not let you install all the available modules, but it does tell you why it is that way.
So, do I think it's ready for primetime in the US for small businesses? For the most part, I would say it is. The price tag of the software itself is unbeatable (free) and the modules and features are solid. Other packages will do other things better, but not at the price and perhaps not as integrated.
More to come as I keep learning it and evaluating.
GP Sales Cube with Pentaho
Tuesday, December 8, 2009
I now have Pentaho talking with my Dynamics GP data in SQL Server 2005. I have since been able to make a Sales Transaction cube using the Pentaho Data Integration tool along with the Schema Workbench.
The Data Integration tool is used for the ETL/Integration portion of the process. It reads data from GP and creates dimensions and the resulting fact table.
The Schema Workbench is used to create the Analysis views that will be available in Pentaho's Mondrian Schema. It's an XML file that lists out my dimensions I define (Time, Sales Territory, Customer Class, Item Class) and some measures. You use this tool to publish the schema file to Pentaho so you can use the User Console to create a new Analysis View and slice and dice your data.
I have also worked a little with the Design Studio and created some xaction files that generate flash-based charts using my GP data. The examples are quite helpful, as well as the Pentaho Wiki on Charting. These xaction files also are accessed via the Pentaho User Console.
Next step - refining my Sales Cube and using the Community Dashboard Framework (CDF)!
More on Accessing Dynamics GP Data using Pentaho CE 3.5
Thursday, November 19, 2009
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:
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:
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!
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
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.DataSourceUse 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.
TWO/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
TWO/url=jdbc:sqlserver://localhost;databaseName=TWO
TWO/user=username
TWO/password=mypassword
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!
Export Dynamics GP Trial Balance to Excel
Friday, October 30, 2009
Do you ever need to export your Financial Trial Balance (or even your AR or AP) to Excel? Tired of printing your GP Trial Balance report to a CSV file and then attempting to open it in Excel, and then delete the report/page headings every 20 or 30 lines?
Well, now you can use the SQL Reporting Services (SRS) report that comes default with Dynamics GP 10! SRS reports natively export nicely to Excel, saving you much time and trouble.
With the release of Dynamics GP 10, several of the most common GP reports were updated (and I do mean that in all possible senses) to SRS. About 75 reports in total, from the major modules. If you are running SQL Reporting Services on your SQL Server (2000, 2005, 2008), you can access these reports through GP or a URL.
Well, now you can use the SQL Reporting Services (SRS) report that comes default with Dynamics GP 10! SRS reports natively export nicely to Excel, saving you much time and trouble.
With the release of Dynamics GP 10, several of the most common GP reports were updated (and I do mean that in all possible senses) to SRS. About 75 reports in total, from the major modules. If you are running SQL Reporting Services on your SQL Server (2000, 2005, 2008), you can access these reports through GP or a URL.
- Make sure you are running IIS somewhere (if your server is not overloaded, you can do this on the SQL Server itself)
- Make sure SRS is installed as part of your SQL Server installation. I will not go into details in this post, but you can read more at MS books online site.
- Configure Reporting Services (either at install time using the defaults, or manually using the config tool)
- Install the Dynamics GP SRS Wizard from the AdProd folder of the GP DVD (or disc 2 of the CD set, or downloaded from Partnersource/Customersource)
- Run the tool, and select one or more companies for which to deploy the reports
- In GP, under Tools - Setup - Reporting Tools Setup, specify the URLs of the SRS reporting site (usually http://servername/ReportServer/reportservice.asmx and http://servername/reports/pages/folder.aspx)
- In GP, under the Administration navigation pane tab, clock on Custom Reports List to view a list of SRS Reports deployed.
So now you can open these reports from within GP, or point a user to them (After giving them appropriate security) using a URL.
The GL Trial Balance report will be in the COMPANY_CODE - Financial folder. There is a Detail and Summary report. Run one, pick your options, and then hit View Report.
Once it is up, you can select Excel as the output option and hit "Export" and you now have a GP Trial Balance in a well-formatted Excel spreadsheet!
Running Pentaho CE 3.5 With Access to MS SQL 2005
Wednesday, October 28, 2009
For those that do not know, Pentaho is a commercial open-source Business Intelligence (here-after referred to as BI) software solution. It produces a Community Edition (CE) which is available free of charge, and an Enterprise Edition (EE) which is around $30,000 for the whole enchilada. EE has some additional features that the CE does not, such as Single Sign On (SSO), some additional Admin Console features, and other whizbang goodies. Also thoroughly QA's code. However, the vast majority of the functionality is the same in both editions.
My goal here is to get the latest 3.5 CE release talking to my Microsoft Dynamics GP 10 Fabrikam company database, running on MS SQL 2005. Why? Because I spend most of my time working with GP (and CRM).
First, I downloaded Pentaho CE 3.5 and started reading the Getting Started Guide. I first created a directory to hold my extracted files, and made the two directories recommended under the main - server and design tools. My path is now:
Now I want to make a new report using my GP data. According the the Guide, a new data source can be created using the Pentaho User Console when you click Add in the Select Data Source step when creating a new Ad Hoc Report. Let's try it.
My goal here is to get the latest 3.5 CE release talking to my Microsoft Dynamics GP 10 Fabrikam company database, running on MS SQL 2005. Why? Because I spend most of my time working with GP (and CRM).
First, I downloaded Pentaho CE 3.5 and started reading the Getting Started Guide. I first created a directory to hold my extracted files, and made the two directories recommended under the main - server and design tools. My path is now:
c:\pentaho\server
c:\pentaho\design toolsI extracted the biserver-ce-3.5.0.stable.zip file to the server directory. That made two new directories - biserver-ce and administration console. I then was able to get the Pentaho User Console up by simply running start-pentaho.bat from within the biserver-ce directory. That worked like a charm. All the sample information is there.
Now I want to make a new report using my GP data. According the the Guide, a new data source can be created using the Pentaho User Console when you click Add in the Select Data Source step when creating a new Ad Hoc Report. Let's try it.
After clicking on the New Report icon, and then clicking on the Add button to add a new datasource, the New Data Source "window" pops up and already has an entry for the SampleData.
I hit the little green + icon to add a new connection. When I select the MS SQL Server option and enter my criteria and test, it fails.
Why does it fail? Well, it turns out it is looking for the JTDS JDBC driver, which is not in my classpath, apparently.
So, next step is to download the JTDS driver, which works with SQL Server and Sybase. Download here, and then extract and copy the jtds-1.2.4.jar file to the biserver-ce/tomcat/common/lib directory. That was my guess as a path that would work. And it did! (One other note - TCP/IP must be enabled for the SQL Server Network Protocol, or you need to pass the JTDS driver a parameter telling it to use Named Pipes. Details here.)
After stopping Tomcat (Ctrl-C in the windows or stop-pentaho.bat) and restarting Tomcat/Pentaho, I go back to add a new report and new datasource. This time it works!
So now I can continue and attempt to make a new Ad Hoc Report using this new connection. I am going to do a simple query to get the Customer List by Customer Class.
I know that all three fields are strings, so I mark them as such. Now I move along to the next step.
Next is to identify the fields, groupings, and filters for the report.
Next. Everything looks good.
Next. I specify a Report Header.
Now I click on the little blue floppy disk icon to Save my report. I saved it in the BI-Developer/Reporting folder, as TWO Customer by Class. Now in the User Console, I can open that location in the tree and see my new report.
But when I double-click the report to view it, I get this error message:
Since I do not know what that means, I'm kinda stuck, and will attempt to watch some demos and see what I can find on the net.
I hit the little green + icon to add a new connection. When I select the MS SQL Server option and enter my criteria and test, it fails.
Why does it fail? Well, it turns out it is looking for the JTDS JDBC driver, which is not in my classpath, apparently.
So, next step is to download the JTDS driver, which works with SQL Server and Sybase. Download here, and then extract and copy the jtds-1.2.4.jar file to the biserver-ce/tomcat/common/lib directory. That was my guess as a path that would work. And it did! (One other note - TCP/IP must be enabled for the SQL Server Network Protocol, or you need to pass the JTDS driver a parameter telling it to use Named Pipes. Details here.)
After stopping Tomcat (Ctrl-C in the windows or stop-pentaho.bat) and restarting Tomcat/Pentaho, I go back to add a new report and new datasource. This time it works!
So now I can continue and attempt to make a new Ad Hoc Report using this new connection. I am going to do a simple query to get the Customer List by Customer Class.
select custnmbr, custname, custclas from rm00101After entering this query into the New Data Source SQL Query field, I hit Preview to verify it works, and it does. Then I hit Apply and am presented with a Metadata specification:
I know that all three fields are strings, so I mark them as such. Now I move along to the next step.
Next is to identify the fields, groupings, and filters for the report.
Next. Everything looks good.
Next. I specify a Report Header.
Now I click on the little blue floppy disk icon to Save my report. I saved it in the BI-Developer/Reporting folder, as TWO Customer by Class. Now in the User Console, I can open that location in the tree and see my new report.
But when I double-click the report to view it, I get this error message:
Since I do not know what that means, I'm kinda stuck, and will attempt to watch some demos and see what I can find on the net.
Subscribe to:
Posts (Atom)
Posts (Atom)
