What is M-Data Analytics and how is it going to change the way you use data?
M-Data Analytics is a Data Warehouse. A Data Warehouse is a database that is specially designed for reporting and data analysis. M2M Data is extracted, checked, cleaned, and transformed to a format which is optimized for reporting. M-Data Analytics is being designed to make reporting simple enough that nearly anyone can effectively pull data from it. How is it going to fix all the problems I mentioned yesterday?
Characteristics of M-Data Analytics – A Quality Reporting System
There will be several phases to the project. In Phase 1, the team transforms most of the modules in M2M into their corresponding structures in M-Data Analytics. Included Modules are Quotes, Orders, Accounts Receivable, and Accounts Payable, Purchasing, Receiving, Shipping, Inventory and Cycle Counting, Labor and Costing, etc.
Easy to Use
M-Data Analytics is easy enough so that everyone will want to use it. The fields and tables have descriptive names which everyone can understand. Any links between tables should be automatic for most modern reporting programs. The system will support not only database views, but also cubes and Report Models (more on these in a later article) to make reporting that much easier.
Accurate and Consistent
The core modules of M-Data Analytics are going to match your Made2Manage system and can be relied upon to be accurate. If you compared invoicing data in M2M for a specific invoice, M-Data Analytics would match that exactly because the same data is there, only packaged in a more useful way. Further, the information will be as complete as possible. Country values will be added where possible even when not available in the M2M Database. State designations will be consistent at either two characters (TX) or full names (Texas), etc.
Performant and Safe to Use
You will be able to run M-Data Analytics on decent hardware without experiencing performance problems. Depending on your needs, you can run M-Data Analytics on it’s own server or on your current Made2Manage server. M-Data Analytics will not require you to violate your user agreement with Consona and is be designed in such as way as to have little to no impact at all on regular M2M operations. Toward that goal, M-Data Analytics will not update the M2M database directly. M-Data Analytics will notify a designated employee at your company when the M2M program has entered records that are likely invalid.
M-Data Analytics will support nearly any reporting tool available such as SQL Server Reporting Services, Crystal Reports, Excel; or even any of Consona’s add on options such as Advanced Reporter and Executive Dashboards. You’re not locked in to some proprietary or ancient technology like Visual FoxPro.
Keep Historical Data
In Phase 2, M-Data Analytics will enable you to keep historical data at periods you specify and makes it easy to spot trends. Wouldn’t you like to track information such as Sales Order Backlog, Inventory Valuation, and Work In Process?
Oh, did I mention that M-Data Analytics is FREE? M-Data Analytics is an open source project that all are free to work on and use. You’re not required to pay anything to use it. It is being developed using Microsoft Business Intelligence Tools which are included with every licensed version of SQL Server. It’s important to note that as of this point, the project is being written in SQL Server 2008. There may be an effort to port that to SQL Server 2005 in the future. However, I still consider that “free” because we will all need to buy SQL 2008 eventually anyway.
As I mentioned previously, I’ve been working on this project a long time and realized early on that it should be a community driven project. It will be developed and maintained by the Made2Manage community. Also, since everyone’s Data Warehouse will look nearly identical, it will be easy for users of M-Data Analytics to share their reports which benefits everyone.
Now I know that what I’ve told you seems like a lot of theory and a very high level overview. This was intentional because I want everyone, from CEO’s to database administrators, to understand it and why M-Data Analytics will be a game changer for M2M companies.
Sales Report Example Revisited
Remember the “simple” sales report I discussed in the previous article? Well, this is how it will look in M-Data Analytics.
If you compare that to yesterday’s pictures you’ll immediately notice:
- The number of tables is less.
- Table and Field Names are easily understood.
- The join lines are blue because the database uses Foreign Keys so that nearly any program will automatically join them. In fact, in Excel, you would click on the Orders table and the “Add Related Tables” button and the query would write itself.
- The added calendar table makes reporting by years, quarters, months or weeks easy.
- The important calculations are already made for you. You don’t have to know how to calculate the shipped quantity or calculate order quantity by unit price to get extended price.
- The descriptions for codes such as Product Class will always be available so you won’t have to memorize them anymore.
In this article I am only hitting the high points. In future articles, I’ll explain how it works, which steps the team is currently working on, and publish educational articles regarding Microsoft Integration, Analysis, and Reporting Services.
So, who’s with me?