Scalability Experts (SE) has the experience to help you develop your data warehousing needs. Data warehousing is the foundation of Business Intelligence where the data is stored as one stop shop, single point of the truth for reporting, trends, and analytics across the business enterprise. Data Warehouses are subject oriented with facts (business processes) surrounded by dimensions (subject areas of the business) that can be hierarchical in nature. Data is derived and transformed from many disparate sources including SAP, Oracle, SQL Server, Access, flat files, spreadsheets, and web services to name a few. Data can be identified from different time periods with the ability to receive answers on business processes by slicing through date ranges that are available. Data Warehouses allow businesses to receive a consistent picture over time, expecting consistent answer, slicing by the same attributes at a later date as one discovers today. Finally, contention against operational databases will be alleviated versus reporting on data in the application database, with less record locking and dirty reads thus enhancing performance.
SE has the expertise to interview the stakeholders, business users, and subject matter experts (SME) for making the discovery needed to satisfy the Business Intelligence needs of your organization. Requirements are gathered based on but not limited to understanding your business, verifying needs, figure out pain points, collecting business rules, incorporate desired historical tracking, and finding data sources. Requirements also include migrating current, capabilities to answer business problems that cannot be solved today, and prioritizing for business value. SE believes that it is critical to have the business users and SME’s involved for they provide valuable insight into data sources and translation for business needs. Data profiling is also conducted at this time in order to further determine the layout of the data model.
The data model is created based one to many data marts (usually coincides with business processes) based on interpretation from the discovery phase and is the blueprint or schema for the data warehouse. The model is dimensional model which is mostly a star schema with centralized fact tables surrounded by relations to subject areas of the business called dimensions. Facts contain quantitative measures of the business processes.
Source to Target Mapping
“Source to Target Mapping” is where a map of the source data and how it is transformed and derived to reach its destination in the data warehouse. It is guideline for ETL (Extract, Transform, and Load) items such as data types, constraints, default values, unknown members, custom ETL rules, historical treatment, foreign key relationships, metadata, etc. are defined.
ETL is a method for automatically extracting data from the data source, transforming that data, and loading into the data warehouse at first historically and then incrementally going forward. The extraction will occur from disparate sources mentioned above into a staging area without transformation. Transformation is where business rules are met and compliance with the data warehouse schema. Transformation could include code translation, derived columns, sorting, joining multiple sources, slowly changing dimension (account for historical changes in a dimension), and data validation. Finally loading to the data warehouse occurs. Best practice ETL will include auditing, notifications, and failure checkpoints.