Many of the projects that I’ve been working on over the last 12 months have evolved integrating data into a data warehouse. Specifically, companies are looking to import more data from other systems to do various types of analysis. Data integration is nothing new, companies have been doing it for a long time, but usually with a very manual process that includes vast Microsoft Excel spreadsheets.
What Is a Data Warehouse?
A data warehouse is a database designed to enable reporting and data analysis activities. Data warehouses store current and historical data from one or more disparate sources. Some of these sources may include:
- CRM data
- ERP data
- Supply Chain Management System data
- Human Resource Management System data
- Google Analytics data
- Membership System data
- Loyalty program data
- Point of Sale/Hotel Management System data
- Other Legacy Application System data
Once loaded into the data warehouse, the data is used for creating trending reports, daily operational reports, dashboards, SQL Service Analysis Service data mining models or other reporting internal/external reports. Some of the benefits of a data warehouse include:
- Congregate data from multiple sources into a single database so a single query engine can be used to integrate and present a single view of the data
- Mitigate the problem of database isolation level lock contention in transaction processing systems caused by attempts to run large, long running, analysis queries in transaction processing databases
- Maintain data history, even if the source transaction systems do not
- Integrate data from multiple source systems, enabling a central view across the enterprise
- Improve data quality by providing consistent codes and descriptions and by flagging and fixing bad data.
- Present the organization’s information consistently
- Provide a consistent data model of interest regardless of the data’s source
- Restructure the data so that it optimized for human consumption (instead of optimized for machine processing)
- Restructure the data so that it delivers excellent query performance, even for complex analytic queries, without impacting the operational systems
My Favorite Application to Kick-start Data Warehousing
One of my favorite tools to help with all of this data warehousing is Solver’s BI360 Data Warehouse and Microsoft Excel One Stop Reporting add-in. Solver’s Data Warehouse comes preconfigured with seven ERP modules: General Ledger, Payables, Accounts Receivable, Sales, Human Resources/Payroll, Capital, and Projects. Additionally, with BI360’s data warehouse manager you have the ability to configure additional Dimension and Fact tables to load the additional integration data points discussed above. This all means that your implementation can be completed in days or weeks instead of months or years, and at a fraction of the cost and risk of a traditional “home-grown” data warehouse project. To help with developing reports for your end users Solver has created Several ERP and CRM report templates. These include:
ERP
- AP Aging by Vendor
- AP by Vendor
- AR Aging
- AR Detail Report
- Balance Sheet
- Cash Flow
- Company Performance Dashboard
- Inventory by Location
- Multiple Profit and Loss Statements
- Trial Balance
CRM
- Campaign Efficiency
- Lead Activity
- Opportunity Detail by Sales Person
- Opportunity Performance by Sales Person
- Opportunity Summary by Sales Person
- Top 50 Opportunities
- Campaign ROI
- CRM Activity report
One of the other great things about BI360 is that reports are built with their One Stop Reporting Microsoft Excel add-in. So once you get the data into Microsoft Excel you have all of its formatting and Power BI and data mining capabilities.
Next steps
So I got the disparate data sources loaded into my data warehouse, now what? How to I use it and analyze it? We’ll look at some of these data gathering technics and analyze in my next post.
For help kick starting your data-driven organizational needs, contact KTL Solutions to explore BI360, Power BI, Microsoft Dynamics GP and CRM.
BARRY CROWELL, MBA | Senior Business Software Consultant and GP Lead
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
