Getting started on a Data Mart -Denormalize your ERP
In other articles, I have talked about how your Accounting System or ERP (Enterprise Resource Planning) System is highly normalized. What that means is that the ERP’s data integrity is guaranteed by the database rules. These rules are composed of foreign keys, triggers, and stored procedures. All these items add overhead to the database by increasing its size and how/where information is stored in it. All this is BAD for reporting from the ERP database; not to mention that ERP’s are highly transactional are never in a steady state. (Just wait a minute and a query will return a different number.)
The easy answer is to remove the ERP data to a denormalized Data Mart or Data Warehouse. A quick easy method of doing this exist in MS SSIS. By using the Microsoft SSIS tools, you can make a copy of the ERP tables and load only their data into a new database. You can then use SSIS to keep the data tables current. What this immediately gives you is a smaller and faster database from which to do your reporting. (This is the shortest path to a Data Mart.)
If time allows, you can enhance your Data Mart by reducing the number of tables and flatting the data structure. (One table for Customer, One table for Sales Transactions, One table for Customer Interactions, …) You will read a lot about Facts and Dimensions; think Action Verbs and Descriptive Adjectives. Terms like Star and Snowflake schemas have to do with the way you structure your Data Mart.
Another typical Data Mart task would be to add data columns that allow data integration with other Data Marts (which comprise your Data Warehouse). These data columns are known as Dimensions and Foreign Keys and allow tables to be linked consistently. Sales data is notorious for changing Regions, Product Groups, and Sales Persons. Maintaining these Dimensions is a common Data Mart task; don’t worry, Data Marts are copies of historic information, so change it to meet your company’s reporting needs.
I hope this has been helpful. Please consider including me in your next ETL or ERP project.
Randall Moore https://MooreRC.azurewebpages.net/ 615.415.3573