Leveraging SQL Server Database Schema
Data warehouses typically pull data from various sources and combine the data into a common repository. Keeping up with the source systems and what data is being pulled from each system can be challenging. One method that can be used to easily identify the source and type of data in your data warehouse is to create a database schema for each area.
For instance, I create a reference schema that is used for common data such as a master calendar or business specific code tables. When pulling data from an application, such as SalesForce you can create a SalesForce schema and know that any tables that have this schema originated from within the SalesForce Application. If you do this for all of the data you need from every application whether internal or external. Your warehouse is much easier to understand when looking at the tables. The last database schema that I use is for finished tables that may combine data from multiple sources to create a user friendly view. For example, creating a Customer schema and table that combines data from a CRM system, billing and payment systems. A table created with the schema used as an identifier is easy for BI analysts to consume as well as easy to create a data dictionary they can use to navigate the warehouse.
The post Leveraging SQL Server Database Schema appeared first on BI and Predictive Analytics.