Abstract:
The invention provides idealized and reusable data source interfaces. The process of idealizing includes reengineering of the original data model using a surrogate key based model. The technique emphasizes readability and performance of the resulting operational data store. 
     The invention provides a unique method for handling changes that allows all types of changes to be automatically implemented in the operational data store by table conversion. 
     Further the invention provides Inline materialization that supports a continuous data flow dependency chain. The continuous dependency chain is used to provide automated documentation as well as dynamic paralleled transformation process. 
     Finally master data integration is provided as a benefit of architecture and the inbuilt surrogate key based data model. The feature implements integrations by specification rather than by programming.

Description:
CROSS-REFERENCE TO RELATED APPLICATIONS 
       [0001]    Not Applicable 
       STATEMENT REGARDING FEDERALLY SPONSORED RESEARCH OR DEVELOPMENT 
       [0002]    Not Applicable 
       REFERENCE TO SEQUENCE LISTING, A TABLE, OR A COMPUTER PROGRAM LISTING COMPACT DISK APPENDIX 
       [0003]    Not Applicable 
       BACKGROUND OF THE INVENTION 
       [0004]    The present invention is in the technical field of information management. More particularly, the present invention is in the technical field of BI. More particularly BI as defined by Forrester Research: 
         [0005]    “BI is a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information that&#39;s used to enable more effective strategic, tactical, and operational insights and decision-making.” 
         [0006]    BI is today broadly recognized as the most vital mechanism for companies to provide strategic and operational meaningful information, reports and business figures from the company&#39;s many data sources. 
         [0007]    The process of constructing an environment for BI is regarded as very complex and time consuming. In most cases it highlights both management and technical issues and can therefore be quite overwhelming, especially for medium and small size businesses. 
         [0008]    The reason for this is that businesses are faced with, on one hand a large set of methodologies, architectures and “best practices” in the market, primarily in form of written books and documents, and on the other hand technologies in the area of ETL (Extract, Transform, Load) and visualization. 
         [0009]    Businesses are left to use the available information and software components to build their own BI environment. This is a challenging task and more often than not leads to project failure in that it exceeds estimated cost, time and complexity. Moreover in these internal built BI solution environments there will normally be no reusability of data sources extractions, ETL processes or solutions across different companies in the market. This is due to the top-down and “silo” focus which makes generalization and reusability difficult. 
         [0010]    ETL tools that exist in the market today are rich on functionality, but made for general purpose. The basic functionality has been around for many years, with limited development and innovation over the last years. Tools today are to a very limited extent supporting methodologies, architectures and today&#39;s “best practices” for BI. 
         [0011]    Visualization tools on the other hand have shown a stunning development and innovation over the last few years, where the latest innovations have brought the market self-service BI, in-memory processing and animated visualizations. 
         [0012]    It is a well-known fact in the industry that the cost and effort spent on ETL activities and visualization activities in a BI project is split near 80 to 20 percent respectfully. This makes it obvious where resources should be spent in order to reduce cost and risk in such projects. 
       SUMMARY OF THE INVENTION 
       [0013]    The present invention is a “methodology supported BI product”. The invention addresses several of the challenges with current technology and methodologies by
       Idealizing the data source interfaces   Implementing Inline materialization services   Automated end-to-end dependency documentation   Master Data integration
 
Thus making it possible for users to develop, maintain, and operate comprehensive BI environments “out-of-the-box”.
       
 
         [0018]    Moreover the invention provide users with features to handle all required changeability in their BI environments and to benefit from extensive reusability of idealized data source interfaces as well as ETL processes. 
         [0019]    The invention relies on Microsoft operating systems and Microsoft SQL Server as the basic technology platform. 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWING 
         [0020]    
         FIG. 1 
       
           [0021]    Is an overview over the complete dataflow from source data to star schema construction included relevant data stores. 
           [0022]    
         FIG. 2 
       
           [0023]    Shows the physical database naming structure 
           [0024]    
         FIG. 3 
       
           [0025]    Is the products repository and usage by the product 
           [0026]    
         FIG. 4 
       
           [0027]    Shows the basic structure of handling data sources 
           [0028]    
         FIG. 5 
       
           [0029]    Shows the general database object naming conventions used throughout the product 
           [0030]    
         FIG. 6 
       
           [0031]    Shows the process of idealizing data sources 
           [0032]    
         FIG. 7 
       
           [0033]    Shows the extraction process 
           [0034]    
         FIG. 8 
       
           [0035]    Shows the dataflow from data source to staging area 
           [0036]    
         FIG. 9 
       
           [0037]    Shows the dataflow from staging area to the operational data store 
           [0038]    
         FIG. 10 
       
           [0039]    Shows the process of detecting changes between repository and the operational data store 
           [0040]    
         FIG. 11 
       
           [0041]    Shows the general transformation process with the operational data store as source 
           [0042]    
         FIG. 12 
       
           [0043]    Shows a more detailed transformation process using SQL views 
           [0044]    
         FIG. 13 
       
           [0045]    Shows the principle of inline materialization 
           [0046]    
         FIG. 14 
       
           [0047]    Shows overview of managed integration 
           [0048]    
         FIG. 15 
       
           [0049]    Shows the basic principle of a star schema 
           [0050]    
         FIG. 16 
       
           [0051]    Shows the principle of integrating different data sources against a common master data model 
       
    
    
     DETAILED DESCRIPTION OF THE INVENTION 
       [0052]    Referring now to the invention in more detail,  FIG. 1  shows the general dataflow starting with data sources  10 . It is important to notice that unlike  11 - 16  data sources  10  exists in as many instances as are relevant for a specific product installation. 
         [0053]      11  is a physical database that is used as a data staging area. Full- or incremental loads from data sources  10  are bulk copied into the staging area to ensure maximum performance. 
         [0054]      12  is a logical surrogate data area that physical resides in operational data store  13 . The surrogate data area holds conversion tables that reflect the link between natural- and surrogate keys. For each table, the surrogate key is held in an integer column where the first record starts with the value 1 and is incremented by 1 for each new natural key. All data tables are assigned a surrogate key and surrogate keys are also used in foreign key references. 
         [0055]    The operational data store  13  holds data tables with basically the same structure as the source data, but in a much more user friendly format. 
         [0056]    The overall structure of a data table in operational data store  13  is: 
         [0000]    
       
         
               
               
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 Column 
                 1 
                 Primary key surrogate key 
               
               
                   
                 Column 
                 2 
                 Last Change Date 
               
               
                   
                 Column 
                 3 
                 Instance No 
               
               
                   
                 Column(s) 
                 4-n 
                 Foreign surrogate keys if exists 
               
               
                   
                 Columns 
                 n 
                 Data columns 
               
               
                   
                   
               
             
          
         
       
     
         [0057]    To make sure that all relations are resolved in joins between tables, each data table has its own dummy record with primary key value set to 0. If foreign keys exist in this table, the surrogate foreign key columns value is also set to 0. 
         [0058]    The ETL process is using the operational data store  13  as source and is carried out in the ETL database  14 . The ETL process provides all relevant fact- and dimensions tables for the next level that is the star schema elements database  16 . 
         [0059]    The star schema database  16  is the level that interfaces with the visualization layer, either directly or via the data mart level  17 . The sources tables and views resides in the ETL database  14  and is made available for star schema database  16  either by using views or by using the automated Stars schema modeler  15 . 
         [0060]    The Stars schema modeler  15  is a pure metadata database for defining fact- and dimension content and relationship. The Stars schema modeler  15  is an optional feature that allows for automated advanced configuration, integration and loading of different data sources.  FIG. 16  shows how the stars schema modeler  15  is used conceptually and functionality will be detailed later in this document. 
         [0061]    If the Stars schema modeler  15  is not used the star schema database  16  could be used manually by creating relevant views against the ETL database  14 . 
         [0062]    The data mart level  17  is optional and is used primarily to allow for customizing stars schemas for specific security purposes and/or specific user groups. 
         [0063]    The data mart level  17  can exist in zero, 1 or many instances. 
         [0064]    Referring now to  FIG. 2  this shows the physical database naming conventions. The database name consists of two parts, a customizable prefix  20  and a fixed suffix  21 . During the installation procedure the user is given the opportunity to specify his own prefix  20  or use a default prefix value. 
         [0065]    Referring now to  FIG. 3  this shows how the product  01  is using its own repository database  02  for all persistent management information. 
         [0066]    Referring now to  FIG. 4  this shows the fundamental structure for handling data sources in the product. The data source level  30  serves solely as a logical grouping of data source versions  31 . Data sources versions  31  contains specific database metadata information as well as mapping information that is used to implement friendly names for destination tables in the data warehouse. The data source version  31  can be used in one or more instances  32 . This is to serve situations where a user has more than one instance of a source application installed or that he wish to handle logical different clients in a single application separately. 
         [0067]    Referring now to  FIG. 5  this shows object naming conventions and sequences (for objects on a lower level than database level) used by the product  01 . The naming is divided into required and optional parts. 
         [0068]    Starting with the required parts, the basic object name  42  is a free naming of the object. The instance identification  41  is a combined string that consists of the data source name, version id and instance id to easily identify data source and instance. 
         [0069]    Optionally there might be a prefix  40  and a suffix  43  for allowing variation of basic objects for different purposes. 
         [0070]    Referring now to  FIG. 6  this shows the principle of idealizing data sources. Idealizing data sources are defined as the process of, regardless of the original data source, making the operational data model  13  as complete and understandable as possible. The fundamental requirements in the process are:
       Provide intuitive table name mappings for relevant tables   Provide intuitive column name mappings for relevant columns   Complete the relational model with
           All primary keys   All relevant foreign keys   
               
 
         [0076]    The product  01  supports the process of idealizing data sources by the following step-by-step process chain.
       1. Import the original data source  10  metadata into repository  02 .   2. Provide intuitive table- and column names. The process is the act of mapping friendly names to the original names. This can be done by using internal editors in the product  01  or by allowing export and import of the complete metadata model to and from Excel  52 .   3. In case the relational model is incomplete, the product support export of a complete metadata database  51  for a given data source  10 . This metadata database  51  is then completed with necessary primary- and foreign keys using standard SQL Server Management Studio. After the completion the revised metadata database is imported into the repository  02 .       
 
         [0080]    The product  01  has now all necessary information to provide user friendly names in the operational data store  13 , to create surrogate key and to visualize all relations in an intuitive manner. 
         [0081]    Moreover the idealized data model now represents a reusable object that can be purchased as a commodity in a web shop  50  by users. 
         [0082]    Referring now to  FIG. 7  this shows an overview of the extraction process. It shows that the data flows from the data source  10 , via the staging area  11  to the operational data store  13 . The figure also show the surrogate data store  12  which is a part of the physical operational data store  13 . 
         [0083]    All necessary tables are automatically created after the following rules:
       Staging data store  11 
           Table is dropped and created if it exists, or created if it do not exists   Table is dropped after successful execution or kept if execution was unsuccessful   
           Operational data store  13 
           Surrogate data store  12 
               Table is created if not exists   
               Operational data store table
               Table is created if not exists   
               
               
 
         [0092]    The load process is multithreaded, and the integrity of the process is ensured by making each table load dependent on that the specific table foreign key tables are successfully complete before loading. 
         [0093]    In further detail, referring to  FIG. 8  shows how data flows between the data source  10  and the staging area  11 . 
         [0094]    The staging area  11  is used for full- or incremental load of data source tables  10 . The structure mirrors the selected tables and columns from the data source  10 . The product  01  supports a variety of selection criteria&#39;s:
       Filter on specific column names through the complete data source  10  is used for filtering general codes like Client and language codes.   Specific filter(s) on specific tables   Incremental columns like a number, last update data etc.       
 
         [0098]    The staging area tables are supplied with a primary key that is evaluated during the load process. This prevents duplicate records to be imported. 
         [0099]    Referring now to  FIG. 9 , this shows the data flow between the staging data store  11  to the operational data store  12 . 
         [0100]    In this process several important functions are performed. 
         [0101]    First and foremost the re-engineering of the destination data model takes place by converting all natural keys and foreign natural key references to integer surrogate keys  12 . 
         [0102]    All tables maintain their own surrogate key table  12  where natural keys are mapped to surrogate keys. A surrogate key table&#39;s primary key is the integer key, while the natural (one or more columns) forms a unique index. 
         [0103]    During insert and update operations the surrogate tables are used to create and maintain surrogate keys for all data tables. If a specific natural foreign key do not have a corresponding natural key value in the surrogate table  11 , the integer value zero is used as default value. The zero value will reference the dummy record that is implemented in the operational data store  13 . 
         [0104]    The data processing sequence is determined by the dependencies between tables. Dependency criteria in this setting are determined by foreign keys. This means that no table can be processed unless all tables that are used as foreign keys in the table have successfully updated their respective surrogate key tables first. 
         [0105]    This ensures that all tables extracted from the specific data source  10  are in sync regarding relationships. 
         [0106]    Still referring to  FIG. 8 , the data flow is basically handled in to different streams.
       Update  54  if the record with actual surrogate primary key exists in the operational data store  13 .   Load  53  if the record with actual surrogate primary key does not exists in the operational data store  13 .       
 
         [0109]    An extra feature for the update  54  stream is to optionally avoid updating if no involved column value in the update stream has actually been changed. 
         [0110]    Referring now to  FIG. 10  there is shown the principle for changeability. In a dynamic business world, new and changed requirements frequently occur. The data warehouse and BI solution must be able to comply with and adapt to these changes. Basically the product is designed to cope with all normal changes that occur:
       New table   Dropped table   Renamed table   New column   Dropped column   Renamed column   New foreign key   Dropped foreign key   Altered data definitions       
 
         [0120]    To be able to handle all the mentioned scenarios, it is necessary to implement a static reference model. This is because all normal references basically can be changed. The static reference model is established and maintained by using SQL Server extended properties in the operational data store  13  data tables. 
         [0121]    On table level one extended property contains the data source  10  table names. 
         [0122]    On column level one extended property per column is required, but with a bit more complex naming structure:
       Primary surrogate key column gets the static value ‘PK’   Foreign surrogate key columns get the value of the correspondent external foreign key name   Ordinary columns gets the corresponding data source table  10  column name       
 
         [0126]    The product  01  performs consistency check by comparing the repository  02  definitions with the operational data store  13  definitions. if any discrepancies are found a corrective script is built and optional executed by the product  01 . This functionality enables the possibility of changing the data import dynamically without having a manual DWH overhead of reconstructing the data store. 
         [0127]    In more detail the process consists of the following steps:
       1. The product  01  extracts definitions from repository  02  and produces an intermediate internal table  62     2. The product  01  extracts definitions from the operational data store  60  and produces an intermediate internal table  63     3. The two tables  62  and  63  is then joined by using data source  10  definitions combined with the special case columns that is explained above.   4. Discrepancy script is created if any inconsistencies have been found.   5. The script is optionally executed  61 .       
 
         [0133]    Referring now to  FIG. 11  and  FIG. 12  these shows the principle of transformation. Given the re-engineering of data source  10  in the operational data store  13 , with consistent surrogate key based model, in many cases there will no need for transformations at all. Tables can serve directly as dimension- and fact tables in the star schema database  16 . 
         [0134]    If transformations are needed, the process could be viewed as series of dependent views in the ETL data store  14  that arc continuously refining the data into its ultimate dimension- or fact table in the star schema database  16 . However, view dependency chains clearly have its limitations both when it comes to performance and also when very complex transformations are required. 
         [0135]    The product  01  solves these issues by allowing specification of materialization (example  70 ). The specification requires only basic SQL Views. Moreover, the specification allow for activating Stored Procedures as well as basic SQL. 
         [0136]    This simple mechanism ensures that all ETL processes are included in a manageable global unbroken dependency structure across databases in the BI solution. 
         [0137]    The unbroken dependency chain is used for several purposes in the product  01 :
       1. An automated end-to-end dependency documentation.   2. An interactive visualization of dependencies for developers.   3. Dynamic multitasked, prioritized and parallelized execution of defined ETL tasks.       
 
         [0141]    Referring now to  FIG. 13  there is shown the detailed principle for materialization 
         [0142]    When there is a need for materialization, a pre-defined naming convention and structure is used so that the Product recognizes the need for actions. It is done by providing a suffix that is either “_Inline” for materializing a view, or “_InlineSP” for materialization using a stored procedure. The output from both is a physical table that is named exactly like the “_Inline” view itself, but with the suffix removed. This technique allow for flexible materializations and de-materializations. 
         [0143]    The following detailed rules apply to each type of materialization: 
       “_Inline” 
       [0144]    All materializations require a primary key specification. The specification of primary key is done by using the “order by” statement in the SQL view. All columns that are included in the “order by” statement will be regarded as primary key in the destination table definition. 
         [0145]    Further, generation of a surrogate key can be enabled in 2 variations:
       1. A zero value followed with an alias column name with the suffix “_Id_Ic” signals incremental load, meaning that only record with new primary key will be loaded. The alias column name “_Id_Ic” will be an identity column and renamed to “_Id_I” in the destination table definition.   2. A zero value followed with an alias column name with the suffix “_Id_Ix” signals that the table will be reloaded from scratch during every process. The alias column name “_Id_Ix” will be an identity column and renamed to “_Id_X” in the destination table definition.       
 
         [0148]    The product  01  creates all destination tables in the ETL database  14 . The product  01  also detects all structural changes between the view and the destination table. In such cases an optionally drop and recreate of the associated destination table is offered interactively. 
       “_InlineSP” 
       [0149]    Unlike materialization of a view “_InlineSP” signals a dummy view that only acts as a dependency structure placeholder. 
         [0150]    There are several requirements for a stored procedure that is going to be used in a “_InlineSP” construct. 
         [0151]    The stored procedure should accept and use the following default parameter values:
       Database prefix  20 —for database identification   Instance id  41 —to allow usability across instances  32     Destination table name (automatically derived from view name)       
 
         [0155]    In addition to the default parameters specified above, the store procedure might use its own specific parameters that then should follow the default ones. 
         [0156]    The stored procedure view is constructed as follows: 
         [0000]    
       
         
               
               
               
             
           
               
                   
               
             
             
               
                 Column 
                 1 
                 String containing the name of the stored procedure - any 
               
               
                   
                   
                 alias column name 
               
               
                 Column 
                 2 
                 String containing stored procedure specific parameter 
               
               
                   
                   
                 list with values separated with comma - any alias 
               
               
                   
                   
                 column name 
               
               
                   
               
             
          
         
       
     
       EXAMPLE 
       [0157]      
         [0000]    
       
         
               
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 ‘MyProcedure’ 
                 AS ProcedureName, 
               
               
                   
                 ‘MyParm1=”x”, ‘MyParm2=”y” ‘ 
                 AS SpParm 
               
               
                   
                   
               
             
          
         
       
     
         [0158]    In order to make the dependency chain completed for stored procedures, the product  01  provides a facility to register the object names that the stored procedure is dependent on. 
         [0159]    Referring now to  FIG. 14  managed integrations overview,  FIG. 15  Star Schema Sample and  FIG. 15  a star schema sample. 
         [0160]    Relevant integration scenarios that the product is designed to handle:
       Integrate dimensions from different data sources against specific master data definition   integrate several instances  32  fact tables in a combined model       
 
         [0163]    The Star Schema Database  16  is structured as the sample in FIG.  15 —clusters of fact tables with related dimensions. 
         [0164]    In the basic dataflow dimension identifications are data source instance related  32  up to the ETL database  14 . 
         [0165]    In scenarios where several data sources  10  are activated, there might be a requirement to integrate dimensions against master data definitions of its type. Most often one might find entities like users, vendors, services and products etc. as master data candidates, but also any other entity that are shared among data sources. 
         [0166]    Referring now to  FIG. 16  this shows the detailed principle of managed integrations. 
         [0167]    The Star Schema Metadata database  15  contains a generic metadata definition of all fact- and its related dimension tables  42 . The term generic is used here to point out that the names in this database  15  as well as the star schema database, does not contain an instance prefix  41 . 
         [0168]    The automated mapping between the ETL database  14  and the Star Schema Metadata database  15  is done by mapping names. For tables and views instance prefix  41  is excluded when comparing. 
         [0169]    In this scenario there might be a one-to-many relationship between objects in the star schema metadata database  15  and the ETL database  14 . The product offers a customization tool that allow selecting master data source  10  to integrate other dimensions against as well as combining multiple facts tables if they exist in different instances  32 . 
         [0170]    Based on the rules specified here, the product  01  automatically populates the star schema database  16 . 
         [0171]    The master data integrations are handled in two separate variations:
       1. All sources  10  are already integrated against a master data regime by having the same natural key. The conversion of surrogate key is then performed by using surrogate tables  12  for the dimensions involved.   2. Sources  10  are not integrated on natural key level. Then the conversion needs a manually supplied conversion table that is managed in the product  01 . Manage in this scenario means to be able to supply conversion values and to be notified when there are missing values in the conversion table.