Methodology supported business intelligence (BI) software and system

• US 20150006469A1
• Filed: 09/16/2014
• Published: 01/01/2015
• Est. Priority Date: 05/23/2012
• Status: Abandoned Application

• Current Assignee: Bi-Builders As (Timextender A/S)
• Original Assignee: Bi-Builders As (Timextender A/S)
• Inventors: Frafjord, Erik
• Application Number:
  US14/488,076
• Publication Number:
  US 20150006469A1
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/282 Hierarchical databases, e.g. IMS, LDAP data stores or Lotus Notes
• CPC Class Codes: G06Q 30/0601 Electronic shopping [e-shopping]

Abstract

The disclosed device provides idealized and reusable data source interfaces. The process of idealizing includes reengineering of an original data model using a surrogate key based model. The technique emphasizes readability and performance of the resulting operational data store. In, addition, the disclosed device provides a unique method for handling changes which allows for all types of changes to be automatically implemented in the operational data store by table conversion. Further the disclosed device provides inline materialization which supports a continuous data flow dependency chain. A continuous dependency chain is used to provide automated documentation as well as a dynamic paralleled transformation process.

Claims

1. A method of ensuring consistency between a configured product repository and a destination operational data store when changes to one or more configurations occurs, said method comprising the steps of:

creating and maintaining a static reference model further comprising a storing of object information in one or more object extended properties in said operational data store;

on a table level, at least one extended property containing a data source table;

on a column level, at least one extended property per column created using a primary surrogate key having a static standardized value, a foreign surrogate key having a value of a corresponding external foreign key name, and ordinary columns having a corresponding data source column name; and

comparing one or more repository configurations and definitions with one or more extended properties in said static reference model.

2. The method of claim 1, wherein said comparing of one or more repository configurations and definitions further comprises extracting definitions from said repository and producing a first intermediate internal table, extracting definitions from said operational data store and producing a second intermediate internal table, comparing said first and said second intermediate internal tables, creating a discrepancy script if inconsistencies are found, and displaying said discrepancies to a user along with a repair script that optionally can be executed.

3. A method of constructing an unbroken dependency chain for all data transformation tasks in a data warehouse, information management and/or business intelligence (hereinafter “

a solution”

) environment, said method comprising the steps of;

(i) establishing a naming format for database objects comprising one or more tables or views for a data transformation process, each of said tables or views includable in said unbroken dependency chain via naming and format standardization which can be specified in a product;

(ii) standardizing said solution environment by incorporating at least three standardized databases, a first database holding an idealized data source, a second database holding one or more transformation processes, a third database holding a multidimensional star diagram structure to be accessed by an end user visualization application

(iii) creating said unbroken dependency chain by structuring and storing information in said standardized databases, wherein one or more physical dependencies are extracted from at least one DBMS system table into a dependency structure within said product, one or more dependencies that are derived from said standardized naming convention promoted by said product includable in said dependency structure within said product, said product enabling a defining of logical dependencies or relationships in said product and storage of said dependency structure within said product; and

(iv) defining and scheduling flexible update processes in said product by using a dynamic unbroken dependency chain by defining logical dependencies on one or more top level objects within said multidimensional structure, defining processing groups by using one or more fact table objects as input, dynamically creating and maintaining a complete list of objects to be automatically included in an update process via said dependency structure, and loading data by parallel processing of all objects on the same level in said dependency structure to automatically maximize performance efficiency.

4. The method of claim 3, wherein said step of establishing a naming format for database objects further comprises a deriving of a destination table name from said view name, a specifying a primary key column and an optional surrogate key column through said product or by a standardized format in database view, and an optional loading of full data or incremental data through said product or by a standardized format in database view.

5. The method of claim 3, wherein said database objects in said name establishing step further comprise one or more stored procedures, said one or more stored procedures having a view format comprising a destination table name and an associated view parameter, said one or more stored procedures being dynamically referable to said destination table and said associated view parameter.

6. The method of claim 3, wherein said one or more stored procedures is capable of being automatically loaded into said one or more tables.

7. A method to transform raw electronic data into meaningful and useful information, comprising:

idealizing metadata from at least one data source into a relational model, comprising, importing metadata into a repository connected to a product, generating intuitive table and column names by mapping a friendly name to an original name by the product, refining the metadata to include table keys and relationships even if this information may not be accessible in the data source;

importing data from the at least one data source to a staging data store for temporary storage;

importing table(s) primary key(s) from the staging data store to a surrogate data store creating a surrogate key table, wherein the surrogate data store converts all original keys and foreign key references to surrogate keys, an original key being mapped to a surrogate key, the surrogate key table reflecting the link between the original and surrogate keys, wherein during insert and update operations the surrogate key tables are used to create and maintain surrogate keys;

processing the table for extraction to an operational data store, wherein the table can successfully update the surrogate key table before processing, the table being updated during processing if a record with an actual surrogate primary key exists in the operational data store, the table being loaded if a record with the actual surrogate primary key does not exist in the operational data store;

importing data to said operational data store, wherein the table has to successfully update the corresponding surrogate key table and the surrogate key table(s) of any related tables before processing; and

performing a consistency check on meta data level by comparing the repository with the operational data store.

8. The method of claim 7, wherein the idealizing step further comprises exporting a metadata database to provide primary and foreign keys using standard DBMS functionality, and wherein a revised metadata database is imported back into said repository where it can be iteratively refined one or more times, the relational model being a reusable object that can be purchased as a commodity.

9. The method of claim 7, wherein the idealizing step further comprises establishing user-crafted user-selected table name mappings and user-crafted user-selected column name mappings which can be set forth in an external spreadsheet exported by the system, the system disposed to read the spreadsheet and to bring about the associations with respect to the tables in response to the content of the spreadsheet.