Kick-Start Your Data Warehousing

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 custom 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 analyzing in my next post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s