ERP-data into Excel from Movex

Summary.

The demand for current information is increasing and the technical development allows more applications to be profitable by direct extraction of data from your ERP-system

Background.

Extracting data directly from the ERP-system is today in many cases a realistic approach. Some years ago it was a dream. Often you needed to extract the data into a data warehouse and then transfer it into a cube. In many cases these intermediate steps made the needed fast implementation unrealistic and expensive, killing the idea behind the application. Most BI-vendors have understood this, like Microsoft’s announcement of PowerPivot for Excel 2010. MS claims  it will make it possible for advanced Excel-users to extract data direct from their ERP-system, making people like me unemployed. Having been in the arena for some decades I am not too worried, but the elimination of unnecessary steps is good, very good. The real problem behind this is still unsolved and will probably be for a long time. When I first heard about the collaboration between SAP and MS I was naïve to believe they together would fix it. Still after many years we have seen nothing. What the user needs when interacting with his ERP is a view of the data of his interest not a collection of some thousands of tables with many mysterious rules hidden in relations and triggers. To build these views you need to understand a lot behind the scene, so at the end it is all bark no bite.

Trivial example.

Let us assume you are a product-specialist in a company, responsible for the introduction of new product into the market. When introducing new products you need to follow sales, often daily in the beginning to make the correct decision about if is is a go or a no go. First problem is to define “what is a new product”. In this case it is easily solved by collecting a list of product numbers, probably this is done in Excel. The next step is to answer: “Where are our sales stored?”. Asking his IT department he is directed to the data warehouse (if he is lucky), which is updated once a week. This will kill his application, because when he tells them his needs they will tell him – summarizing data daily will cost a fortune.

Solving this problem has technically been possible for decades, but in the past cost to much in resource usage of computers to be profitable. So let us go ahead and solve it if your ERP-system is Movex (Lawson/M3). The database with my customers is in DB2 in AS400 and have been accessible through ODBC for a long time, so that is really no news. Daily I meet people struggling with MS Query digging into the tables of Movex using a language they call “Moviska”. The real challenge is finding the right tables and how to join them, then you have to find the columns to use which could be solved by recognizing figures from your daily work when browsing though the details. We try Google for this and enters “Movex sales stats” and in 2nd document we find the keyword “OSBSTD”. This is actually the table where sales stats are stored!!

How to connect to DB2 i AS400 with ODBC i standard techniques, you could use MS Query, MS Access pass thru or linked tables. I prefer linked tables in Access which makes it very convenient to construct the SQL you need, and it is also very simple to join it to information of your own needs, so I put up a table ProdList which contains my list of interest. Then I start the query designer:

As you see, you need to know the names of the columns in one way or another (Moviska), but then it easy simple and straight forward. So we move the data to Excel, either by copy/paste or with a query in Excel, where we put it into a feasible graph and after some clicks we have:

So we are all done with the prototype and wants to put it into production. Problem is the execution of the query takes 260 sec and a lot of resources making the IT-department worried. With a minor change in the SQL you will have an execution time of 60 seconds. Making it a passthru query and making it use a feasible index reduces the execution time down a factor of 20 giving 3 sec – for the same query. Why?

The SQL used if you would like to copy:
SELECT UCIVDT AS Invdate, Sum(UCSAAM) AS Sales, Sum(UCSAAM-UCUCOS) AS Revenue 
FROM OSBSTD
WHERE UCITNO In (‘121881′,’121891′,’121901′,’121911′,’121921′,’121931’)
AND UCCONO = 1
GROUP BY UCIVDT
ORDER BY UCIVDT

Knowing the methods and where and when to use them, that is what craftsmanship is all about in IT.