Hugo Sanguino Home Page

Schema Analyzer for MAS 500 ERP

2008-08-20T09:37:00.001-07:00

Update June 2009.

Hi Dear Friends, I didn't take care of this project for a couple of months. I'm sorry, the tools aren't for free anymore. The time for sharing and asking for feedback is closed now since I shared the software with 20 friends and then I just got feedback from 2 of them (Thanks Mike and Richard). So I'll commerce the tool through www.erptoolbelt.com site. Also, I'm open for selling the full project (source code included) serious offers to hsanguino@erptoolbelt.com.

Thanks,

Hugo.

---------------------

Whether you are a Final User, Consultant, DBA or a Developer, you benefit from using tools that help you work better, no doubt, that’s why I always take Saturdays to create those tools which can help me to go faster when doing my work along the week, sadly I’m always busy and have no time to publish all of them, but talking with some respected friends of mine Mike Harms and John Kirwin, I realized that maybe some tools would help to create a nice community sharing opportunity. And I think the Schema Analyzer for MAS 500 ERP is the simplest and most effective way to start it.

Introducing Schema Analyzer for MAS 500 ERP

Watch it in action!

• Originally Created for a Developer trying to understand the MAS 500 Database Schema
• Now Customized for those who aren’t developers but want explode the most of the Database Schema
• Graphics always help make the things easier.
• Completely intuitive, easy to use, it does what you need to get the knowledge
• Full support for SQL Server 2005*

The Schema Analyzer for MAS 500 ERP allows Developers, Consultants and Advanced users to gain access to ALL the MAS 500 Database Schema in a graphic way including new Tables added by using the SDK Visual Tools. This means that when you are working with MAS 500 databases, repots, APIs, Add-ons, etc, you can finally have the most powerful tool right there to hand to help yourself to solve those doubts regard Database Schema.

Why another Schema Tool?

This one uses a “Graphical Exploring” method which plays several key roles in Analytics:

- Help to finding patterns in raw data which can be a challenge, especially with complex data sets.
- Help to understanding and making sense of a set of data, besides help finding patterns in it.
- Clear presentation of results to others and of course oneself!

VAR Case 1

Let say you added some new tables to the database and the customer wants to see them in the Schema Browser like the rest of the SAGE tables. We know that the Hyper Schema Text/documents were created through the product called "ERWIN". If you purchase this expensive and complex CASE tool, maybe you could then use it to re-generate the hypertext model currently provided by SAGE and update the hyper text to reflect the new tables. But, if you don't plan on creating extensive schema modifications, I would recommend pick Schema Analyzer up which automatically recognize all new tables imported through MAS 500 Data Dictionary Tools.

VAR Case 2

We know the classic scene where Consultants assist customers with modifying or creating crystal reports or otherwise interface with MAS500 and they find the schema browser difficult. Schema Analyzer for MAS 500 would help since it exposes the table structure including definition information and the linking; besides it allows creating Notes, and the purpose of it is that interconnected people could be able to put their own “knowledge” in there, and then make that available to others.

Regards,

Hugo Sanguino

How can ERP improve a company’s business performance?

2007-06-08T14:09:00.001-07:00

ERP’s best hope for demonstrating value is as a sort of battering ram for improving the way your company takes a customer order and processes it into an invoice and revenue—otherwise known as the order fulfillment process. That is why ERP is often referred to as back-office software. It doesn’t handle the up-front selling process (although most ERP vendors have recently developed CRM software to do this); rather, ERP takes a customer order and provides a software road map for automating the different steps along the path to fulfilling it. When a customer service representative enters a customer order into an ERP system, he has all the information necessary to complete the order (the customer’s credit rating and order history from the finance module, the company’s inventory levels from the warehouse module and the shipping dock’s trucking schedule from the logistics module, for example).

People in these different departments all see the same information and can update it. When one department finishes with the order it is automatically routed via the ERP system to the next department. To find out where the order is at any point, you need only log in to the ERP system and track it down. With luck, the order process moves like a bolt of lightning through the organization, and customers get their orders faster and with fewer errors than before. ERP can apply that same magic to the other major business processes, such as employee benefits or financial reporting.

That, at least, is the dream of ERP. The reality is much harsher.

Let’s go back to those inboxes for a minute. That process may not have been efficient, but it was simple. Finance did its job, the warehouse did its job, and if anything went wrong outside of the department’s walls, it was somebody else’s problem. Not anymore. With ERP, the customer service representatives are no longer just typists entering someone’s name into a computer and hitting the return key. The ERP screen makes them businesspeople. It flickers with the customer’s credit rating from the finance department and the product inventory levels from the warehouse. Will the customer pay on time? Will we be able to ship the order on time? These are decisions that customer service representatives have never had to make before, and the answers affect the customer and every other department in the company. But it’s not just the customer service representatives who have to wake up. People in the warehouse who used to keep inventory in their heads or on scraps of paper now need to put that information online. If they don’t, customer service reps will see low inventory levels on their screens and tell customers that their requested item is not in stock. Accountability, responsibility and communication have never been tested like this before.

People don’t like to change, and ERP asks them to change how they do their jobs. That is why the value of ERP is so hard to pin down. The software is less important than the changes companies make in the ways they do business. If you use ERP to improve the ways your people take orders, manufacture goods, ship them and bill for them, you will see value from the software. If you simply install the software without changing the ways people do their jobs, you may not see any value at all—indeed, the new software could slow you down by simply replacing the old software that everyone knew with new software that no one does.

What is a ERP System?

2007-06-08T14:07:00.000-07:00

Well... let me explain.. enterprise resource planning software, or ERP, doesn’t live up to its acronym. Forget about planning—it doesn’t do much of that—and forget about resource, a throwaway term. But remember the enterprise part. This is ERP’s true ambition. It attempts to integrate all departments and functions across a company onto a single computer system that can serve all those different departments’ particular needs.

That is a tall order, building a single software program that serves the needs of people in finance as well as it does the people in human resources and in the warehouse. Each of those departments typically has its own computer system optimized for the particular ways that the department does its work. But ERP combines them all together into a single, integrated software program that runs off a single database so that the various departments can more easily share information and communicate with each other.

That integrated approach can have a tremendous payback if companies install the software correctly.

Take a customer order, for example. Typically, when a customer places an order, that order begins a mostly paper-based journey from in-basket to in-basket around the company, often being keyed and rekeyed into different departments’ computer systems along the way. All that lounging around in in-baskets causes delays and lost orders, and all the keying into different computer systems invites errors. Meanwhile, no one in the company truly knows what the status of the order is at any given point because there is no way for the finance department, for example, to get into the warehouse’s computer system to see whether the item has been shipped. "You’ll have to call the warehouse" is the familiar refrain heard by frustrated customers.

ERP vanquishes the old standalone computer systems in finance, HR, manufacturing and the warehouse, and replaces them with a single unified software program divided into software modules that roughly approximate the old standalone systems. Finance, manufacturing and the warehouse all still get their own software, except now the software is linked together so that someone in finance can look into the warehouse software to see if an order has been shipped. Most vendors’ ERP software is flexible enough that you can install some modules without buying the whole package. Many companies, for example, will just install an ERP finance or HR module and leave the rest of the functions for another day.

A Control Document For ERP Enhancements

2007-06-08T07:37:00.000-07:00

Name:

General Conventions for Fact Sheets, Add-Ons, DB Updates, Reports, Issues Solutions and Queries Control Documents.

Date:

April 17, 2007

Type:

Fact Sheet	X
Add-On
DB Update
Query
Report
Issue Solution

Childs Document IDs:

Team:

Designer:	Hugo Sanguino
Developer:
QA:
Documentation:
Authorization	MyBossName

Revisions:

Date	Revised by	Description

Table of Contents. 2

1 Overview.. 3

2 Documents Layout. 3

2.1.4 Childs Document IDs. 4

2.1.5 Team.. 4

2.1.6 Revisions. 4

2.2 Table of contents. 5

2.3 Overview Section. 5

2.3 Body Structure. 5

2.4 Appendix. 5

2.5 Document Naming Conventions. 5

2.6 Document Description. 6

3. Document Types. 6

3.1 Recommended Structures. 7

3.1.6 Issue Solution. 7

4. References. 8

1 Overview

So far the CompanyName MAS 500 Team members has been developing several Enhancements individually through adding different kinds of objects to MAS 500 ERP Database without a lead guidance, even without a control which allow the rest of team be notified and get the general knowledge in order to reuse the objects mentioned above.

The goal of this document is to define the obligated Tasks-To-Do, in order to document each Enhancement as objects creations inside SQL Server, Queries to Share, Fact Sheets regarding System Workflow, Report Designs Specification and the most important: The Knowledge Base around MAS 500 Background operation.

2 Documents Layout

The Document must be a Microsoft Word File (*.doc), using for his edition the Template File Format Styles named MAS500-TEMPLATE.DOC shared at \\myserver\mas500\Enhancements.

For new whole document creation you must copy an actual document (preferably the Template one) and rename to new document's desired name. For more information see Document Naming Conventions.

2.1 Cover format

The document cover it must be limited to only one page, this is the most important section inside document since it contains all the information about the document objectives. The cover have three areas, there are a Header, a Body and Footer. The Writer only needs to modify the Body, in the following sections its covers how must be the body subsections modified.

2.1.1 Name

The name of the Enhancement must represent the whole objective of the enhancement. When the writers compose the name, they must denote three parts: The name of the Enhancement, the general objective, and the Module(s) of MAS 500 that affects if were the case.

2.1.2 Date

This part represent the date when the Enhancement was authorized for its publication. Only one person can authorize any enhancement, the Director of IT Department. The date must comply with the Long Date format without the name of the day.

2.1.3 Type

There are several types of Enhancements, some pretty much identified with the real concept of “enhancement”, nevertheless the goal of this Control is to catch any kind of contribution to the Knowledge Base of the Company and since the Knowledge must be classified for its future use and application, we try to include any kind of topic at 6 categories: Fact Sheet, Add-On, DB Update, Query, Report and Issue Solution. The writer must mark only one category. Could be confusing select just one cat when the Enhancement can cover more than one aspect.

Example

Suppose we are writing a Control Document for our Enhancement which is a kind of Add-On although its own implementation on MAS 500 Database requires creation of several Stored Procedures, Tables, Views and a couple of Crystal Reports, we can’t include all the Components Documentation at just one ID. We need a separate Control Document ID for each Store Procedure, Table, View and CR Report Component.

This can signify a lot of work, but in the other hand, this practice will give us the handle for reuse of each component of the mentioned add-on in future enhancements.

For more information about each Enhancement Category see the Document Types Section.

2.1.4 Childs Document IDs

In this part we need to refer all the Document IDs which is directly linked with our Enhancement. Looking the example at the previous section we can populate the grid with each Store Procedure, Table, View and CR Report Components Control Document ID. So this way every member of the CompanyName MAS 500 Team can ofbtain easy reference to Child Related Enhancements in order to give it more value when reusing objects, avoiding the creations of duplicates and adopting them like Parent-child Relation.

2.1.5 Team

When we need deeper information about an "enhancement" the most powerful source of information are the members of team which develop the whole thing.

Designer: Crystal Reports Designer.

Developer: Who wrote the code at VB6, VB.NET, VBS, VBA, VC++, T-SQL, etc.

QA: Quality Assurance.

Documentation: Who is writing the Document?

Authorization: IT Director.

2.1.6 Revisions

Any team member can do revisions over any Enhancement, just as long as it does it using the Microsoft Word Reviewing Function.

This is necessary to track modifications to the original document. The revisions are mandatory when a member modify any existing enhancement. Each revision must be registered with the Date, Member Name and Description of the Revision.

Example:

Date	Revised by	Description
12/05/07	Hugo Sanguino	Add the SessionID input parameter to spsoGetSalesOrderLLL Stored Procedure Reference.

Obviously the real and explained Reference is going to be detailed in the respective section inside Body Document. Something like this:

Stored Procedure “spsoGetSalesOrderLLL” Input Parameters

TranNo: Existing Description

SOKey: Another Existing Description

SessionID: Here goes the new description.

…

Note: to know more about Microsoft Word Reviewing Function ask the writer of this Fact Sheet.

2.2 Table of contents

For new whole document creation you must copy an actual document (preferably the Template one) and rename it to new document's desired name. The Table of Contents don’t need to be modified, just Updated. When the writer applies the Microsoft Word Styles and formatting Function over the Recommended Structure of the document the Table of Contents can be updated.

Every document MUST have a Table of contents.

2.3 Overview Section

This is a brief description of the real reasons that give rise to the creation of “enhancement”.

2.3 Body Structure

The Body Structure contains all the topics needed to learn everything about the Enhancement, when is done, must looks like an Installation Guide or User Guide Classic Structure, depending the Type of Enhancement. The Writer must do an effort to feed this section and incorporate notes and tips in order to give more information to the reader.

The IT Director bases his criteria in this area to authorize the publication of the Enhancement; otherwise he must claim for more level detail.

2.4 Appendix

This part must contain the comments, routines or extra information for the reader, the information in this area is not important for the enhancement’s workflow but it is important to get notion of third-party conventions or additional resources to do easiest the understanding of the complete document.

2.5 Document Naming Conventions

The Control Document name must follow the structure:

KDOC - [The first letter of the 8 initials first words of the Enhancement’s Name] - [incremental number 01-06 depending type of Document] to prevent potential conflicts.

When the Name of Control Document has less than 8 words, the “X” must replace the lacks letters to complete always 8 chars.

Examples

KDOC-ABCDEFGH-05

KDOC-DFGDTEXX-06

2.6 Document Description

Is very important the writer take the time to paste to the File/Properties/Summary/Comments window the name of the Control Document otherwise is going to be hard to find any document just for the name (ID).

3. Document Types

So Far there are 6 enhancements categories identified therefore we have created 6 documents types.

Fact Sheet
Add-On
DB Update
Query
Report
Issue Solution

Fact Sheet - Any help to understanding the concepts involved with the System, its structure may be like this Document you are reading right now or may looks like a white paper too.

Add-On - This is the most hard to write document since by its own nature, we need to write and deploy every child documents to complete the whole documentation for our enhancement.

DB Update - Any object added to mas500_app Database or another database directly related with MAS 500 these may be Databases, Stored Procedures, Views, Tables, Functions, Triggers, Scripts, Jobs, DTS, etc.

Query - Like name it say.

Report - Crystal Reports Reports.

Issue Solution – We need document every solution to our UNEXPECTED issues, this is very important for the rest of team members.

In the follow section we can look some examples and get more information about each particular category structure.

3.1 Recommended Structures

Is time to define the Structure for each Control Document type, note that the writer can change the structure at its own style. Just as IT Director gives his authorization.

3.1.1 Fact Sheets

Overview

Topic 1

Topic 2

Topic n

References

3.1.2 Enhancement

Overview

Description

Installation

Use guide

Workflow

Functionality aspects

Screenshots

References

3.1.3 DB Update

Overview

Type of object

Permissions

Attributes

Script

References

3.1.4 Query

Overview

Description

Path

Source Code

References

3.1.5 Report

Overview

Description

Path

References

3.1.6 Issue Solution

Overview

Issue

Symptoms

Cause

Solutions

Applies to

References

4. References

http://www.hugosanguino.com

Welcome!

2007-06-08T06:24:00.001-07:00

Hi, welcome to my new official website! I'm so excited to finally have a place to communicate with everyone. Finally... the real facts, my latest news, projects, and upcoming developments. Also, be sure to check out my exclusive Tools and Applications For Sale, I hope you enjoy learning a little bit more about ERPs. If you have any suggestions about the site or just want to say hi, I would love to hear from you.

MAS 500 DataFinder 2.0

2007-06-07T14:29:00.001-07:00

Well, here is the new version of my awarded Finder Tool. I hope you people saves time using this little vb program.

' Program : MAS500 Data Finder 2.0
' Usage : Search selected tables in MAS500 database for a given value.
' For example: You know that there's a field somewhere
' containing the text "disk" but you don't know where!!
' Now you can find it easily.

If you want the app, mail me.