Automated refinement and validation of data warehouse star schemas

One or more non-transitory computer readable storage mediums storing one or more sequences of instructions, which when executed by one or more processors, causes automatic refinement of a template warehouse star schema, by performing the steps of:

• automatically comparing a customized source database with a baseline template warehouse to gather a custom information selected from i) a data type or a length of custom columns, ii) frequently used expressions on custom columns, iii) custom entities or information on constituent columns of the custom entities, iv) custom entity keys, v) relationships of custom entities with other entities, vi) relationships within custom entities, vii) a contribution of custom entities to specialized entities, viii) an update frequency of custom entities and columns, ix) a grouping of custom entity and column updates, and x) a contribution of custom entities and columns to workflows and correlated attributes, wherein the custom information is gathered based on a combination of a usage analysis technique by automatically analyzing queries on the custom source database, a data profiling technique by automatically analyzing data in the custom source database and an operational reporting environment analysis technique by automatically analyzing an underlying model and queries in operational reports;
• automatically determining facts by determining frequently updated numeric non key custom columns and the custom entities;
• automatically determining dimensions by determining non-numeric custom columns and the custom entities that are infrequently updated and that are linked to the facts by foreign keys;
• automatically determining dimension hierarchies based on hierarchical relationships within custom entities determined to be dimensions;
• and
• automatically suggesting at least one of (i) subsumption of a custom entity into an entity of the template warehouse star schema based on a type of mapped target of related entities, a column type of the custom entity or an update frequency of the custom entity if the custom entity is related to the entity of the template warehouse star schema, (ii) creation of new facts or dimensions for the custom entity based on a column type of the custom entity or an update frequency of the custom entity if the custom entity is a standalone entity, or (iii) creation of specialized entities or augmentation of specialized entities in the template warehouse star schema if the custom entity is related to a currency or a unit of measure.

One or more non-transitory computer readable storage mediums storing one or more sequences of instructions, which when executed by one or more processors, causes automatic validation of a data warehouse, by performing the steps of:

• automatically validating i) a data type or a length of existing columns, ii) existing entities, iii) existing entity keys, iv) relationships of existing entities with other entities, v) relationships within existing entities, vi) a contribution of existing entities to specialized entities, and vii) a contribution of existing entities and columns to workflow and correlated attributes in the data warehouse based on a combination of:
• (i) a usage analysis technique by automatically querying on a source database to determine if existing warehouse entities, columns and workflow attributes are being used and if assumed relationships are being exercised,
• (ii) a data profiling technique by automatically analyzing data in the source database to determine changes due to customer specific customizations, source schema updates, or data quality issues, and
• (iii) an operational reporting environment analysis technique by automatically analyzing an underlying model and queries in operational reports to identify which of the existing entities, relationships, and measures are relevant and if specialized entities or standard units assumed are valid;
• automatically validating facts by determining frequently updated numeric non key custom columns and the custom entities;
• automatically validating dimensions by determining non-numeric custom columns and the custom entities that are infrequently updated and that are linked to the facts by foreign keys;
• and
• automatically validating dimension hierarchies based on hierarchical relationships within custom entities determined to be dimensions.