The Data Discovery Hub: a platform for insights

At two conferences in the same month, I overheard CTO's express, that enterprise resource planning (ERP) systems like Dynamics NAV, GP or Sage could only capture what happened inside a company, and to uncover the 'why', organizations would have to consider data collected outside their ERP (Google Analytics, CRM, webstore, Mailchimp, Twitter etc.)  

Ex. Data analysts can build sales or marketing spend report based on data stored in NAV with aplomb.  They can even ascertain product and customer trends, but without external data, it'd be difficult to ascertain why organizations are seeing the behavior or more importantly, how they can influence behavior.

This blog post will review

• the use of TimeXtender's Data Warehouse Automation platform to create a proof-of-concept data warehouse which integrates Dynamics CRM in the cloud and Dynamics NAV on premise. 
• the adoption of a data insights model (the Data Discovery Hub) which facilitates data mashup, manipulation and exploration for data and business analysts.  
• for classically trained Kimball dimensional modelers, this project demonstrates a workflow for developing conformed dimension sourced from multiple data sources.

The Data Discovery Hub facilitates data integration by separating ETL (extract, transform, load) into three databases:  ODS, DSA, MDW

• Operational Data Store (ODS):  Extract and store raw source data from web-based (CRM) and on premise (NAV) systems.  Ideally the ODS will be configured to touch the source systems as infrequently as functionally possible and extract the least amount of data necessary to meet reporting requirements (incremental load).
• Data Staging Area (DSA): data analysts use this area to forge the raw data from the ODS into a presentable format.  Business analysts will ensure the application of business rules and implement transformations required to calculate and feed business metrics while data analysts will find relationships between entities from disparate data sources. (How do you relate an account from CRM to a customer in NAV?)
• Master Data Warehouse (MDW): This final data store is the source for all reporting and analytics.  This database will feed Qlik visualizations, Jet Reports cubes and reports, PowerBI dashboards etc.  To ensure consistency, users will forgo data transformations and implementing complex logic in their reporting tools, and instead rely on a governed data source that has been cleaned up and prepared for presentation.