Classifications

• • G—PHYSICS
  • G06—COMPUTING OR CALCULATING; COUNTING
  • G06F—ELECTRIC DIGITAL DATA PROCESSING
  • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
  • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
  • G06F16/25—Integrating or interfacing systems involving database management systems
  • G06F16/252—Integrating or interfacing systems involving database management systems between a Database Management System and a front-end application
• • G—PHYSICS
  • G06—COMPUTING OR CALCULATING; COUNTING
  • G06F—ELECTRIC DIGITAL DATA PROCESSING
  • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
  • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
  • G06F16/25—Integrating or interfacing systems involving database management systems
  • G06F16/258—Data format conversion from or to a database

Definitions

• the present disclosure relates to database systems. More particularly, the present disclosure pertains to an apparatus and method for transporting data for a data warehousing application. Even more specifically, this disclosure teaches both a method and apparatus for transporting data for data warehousing applications that incorporates analytic data interface.
• Extracting raw data from one or more operational databases and transforming it into useful information is the function of data "warehouses" and data "marts.”
• data warehouses and data marts the data is structured to satisfy decision support roles rather than operational needs.
• the corresponding source data from an operational database is filtered to remove extraneous and erroneous records; cryptic and conflicting codes are resolved; raw data is translated into something more meaningful; and summary data that is useful for decision support, trend analysis or other end-user needs is pre-calculated.
• the data warehouse is comprised of an analytical database containing data useful for decision support.
• a data mart is similar to a data warehouse, except that it contains a subset of corporate data for a single aspect of business, such as finance, sales, inventory, or human resources. With data warehouses and data marts, useful information is retained at the disposal of the decision-makers.
• the time and cost for defining and programming such that the data is suitable for use as input to a data warehousing application is particularly problematic for companies that use multiple different operational databases. More particularly, the process of defining and programming for data transport must be repeated for each different operational database. That is, for example, if a company has both a SAP database and an Oracle database, the process of defining and programming for data transport must be performed for both databases and the process is unique to each database.
• What is needed is a method and apparatus that allows for transporting data such that the data can be used in data warehousing applications.
• a method and apparatus is needed that meets that above need and that takes advantage of the standardization of database components.
• a method and apparatus is needed that reduces the time required to define and program data transport for data warehousing applications.
• the present invention provides a method and apparatus that meets the above needs.
• the present invention includes a method and apparatus for transporting data for a data warehousing application. More particularly, the present invention introduces a method and a data transport process architecture that uses standardized structures of different types of source databases to achieve source-specific configuration for extraction, transformation, and loading in a data warehousing application.
• a system includes an analytic business component that translates operational data from data source having a standardized data structure.
• the system also includes a staging area for storing the translated data.
• the system includes a source adapter that is coupled to the staging area.
• An analytic data interface couples to the source adapter and receives the data having a common structure and transforms the data for loading into a data warehouse.
• data is extracted from one or more source containing data having a standard data structure and is translated into data that contains meaningful business terms.
• the translated data is then stored.
• the analytic business component is operable for extracting data from the source, translating the extracted data and for storing the translated data into a staging area.
• the translated data is then processed to obtain data having a common structure.
• a source adapter processes the translated data to obtain data having a common structure.
• an analytic data interface receives the data having a common structure and transforms the data for loading into a data warehouse.
• the data can then be loaded into a data warehouse.
• the analytic data interface includes a graphical user interface that makes it easy to configure and customize how business data is loaded into an analytic applications system such as a data warehouse.
• the analytic data interface includes a simplified abstraction layer for the data warehouse administrator, allowing the warehouse administrator to configure how data is loaded into the analytic applications in a fraction of the time it takes to configure these capabilities programmatically as occurs in prior art systems.
• most of the complex technical problems are solved prior.to data entering the analytic data interface. In many instances, these technical problems are solved without any required configuration or analysis by the warehouse administrator. This greatly simplifies the task of loading data into a data warehouse, saving significant expense and time.
• the benefits are particularly apparent for companies that use multiple different operational databases. More particularly, there is no need to define and program for data transport for each different operational database. The warehouse administrator needs only define and program for data transport a single time using the graphical user interface of the analytic data interface.
• the present invention provides a method and apparatus that allows for transporting data such that the data can be used in data warehousing applications.
• the present invention provides a method and apparatus that takes advantage of the standardization of database components.
• the present invention provides a method and apparatus that reduces the time required to define and program data transport for data warehousing applications.
• Figure 1 illustrates an exemplary computer system used as part of a data warehousing system in accordance with one embodiment of the present invention.
• Figure 2 illustrates an exemplary architecture that includes a server in accordance with one embodiment of the present invention.
• Figure 3 illustrates an exemplary architecture that includes an analytic data interface in accordance with one embodiment of the present invention.
• Figure 4 shows a method for transporting data to a data warehousing application in accordance with one embodiment of the present invention.
• portions of the present invention are comprised of the computer-readable and computer executable instructions which reside, for example, in computer system 10 used as a part of a data warehousing system in accordance with one embodiment of the present invention. It is appreciated that system 10 of Figure 1 is exemplary only and that the present invention can operate within a number of different computer systems including general-purpose computer systems, embedded computer systems, and stand-alone computer systems specially adapted for data warehousing applications.
• Computer system 10 includes an address/data bus 12 for conveying digital information between the various components, a central processor unit (CPU) 14 for processing the digital information and instructions, a main memory 16 comprised of volatile random access memory (RAM) for storing the digital information and instructions, a nonvolatile read only memory (ROM) 18 for storing information and instructions of a more permanent nature.
• computer system 10 may also include a data storage unit 20 (e.g., a magnetic, optical, floppy, or tape drive) for storing vast amounts of data, and an I/O interface 22 for interfacing with peripheral devices (e.g., computer network, modem, mass storage devices, etc.).
• a data storage unit 20 e.g., a magnetic, optical, floppy, or tape drive
• I/O interface 22 for interfacing with peripheral devices (e.g., computer network, modem, mass storage devices, etc.).
• the software program for performing the transport process can be stored either in main memory 16, data storage unit 20, or in an external storage device.
• Devices which may be coupled to computer system 10 include a display device 28 for displaying information to a computer user, an alphanumeric input device 30 (e.g., a keyboard), and a cursor control device 26 (e.g., mouse, trackball, light pen, etc.) for inputting data, selections, updates, etc.
• a display device 28 for displaying information to a computer user
• an alphanumeric input device 30 e.g., a keyboard
• a cursor control device 26 e.g., mouse, trackball, light pen, etc.
• computer system 10 may be coupled in a network, such as in a client/server environment, whereby a number of clients (e.g., personal computers, workstations, portable computers, minicomputers, terminals, etc.), are used to run processes for performing desired tasks (e.g., inventory control, payroll, billing, etc.).
• Figure 2 illustrates an exemplary computer network upon which an embodiment of the present invention may be practiced.
• Operational databases 210, 220, and 230 store data resulting from business and financial transactions, and/or from equipment performance logs.
• Databases 250 and 260 are the data warehouses or data marts that are the targets of the data transportation process.
• server 240 performs data transport operations. That is, in the present embodiment, data from databases 210, 220, and 230 is extracted, transformed, and loaded by server 240 into databases 250 and 260.
• server 240 includes multiple microprocessors and data warehousing related software that operates in conjunction with an installed operating program such as, for example, Windows, NT, UNIX, etc.
• FIG. 3 shows an embodiment of the present invention that includes
• Analytic Business Components 12-14 that extract data from sources of data (e.g. databases 2-3 and web logs 4). Typically, sources contain data in the form of relational tables, Enterprise Resource Planning (ERP) objects, or flat files. Analytic business components 12-14 ' also translate operational data from each source of data into meaningful business terms. These business terms are then stored in staging areas 22-24.
• sources of data e.g. databases 2-3 and web logs 4
• ERP Enterprise Resource Planning
• Analytic business components 12-14 also translate operational data from each source of data into meaningful business terms. These business terms are then stored in staging areas 22-24.
• Staging areas 22-24 hold data received from analytic business components 12-14.
• staging areas 22-24 consolidate data from disparate systems.
• the staging area denormalizes data where necessary, preparing it for storing in a data warehouse. More particularly, data is cleansed and remains formalized, tables from different databases are joined, and a refresh policy is carried out.
• staging areas 22-24 provide a structure that is flexible to allow for user configuration.
• the only structures that are not configurable are the primary key columns.
• staging areas 22-24 are not organized by subject area, and are not customized for viewing or reporting by end users.
• the staging area tables consist of all fields required by the analytic data interface 5, including all extension fields.
• the default database for the staging area is oracle. However, at implementation, the database type can be changed.
• staging areas 22-24 provide for quick and efficient data extract. This minimizes the time required for loading the database, allowing for a smaller operational window.
• staging area prepares the data consistently for loading into the analytic data interface from various sources.
• source adapters 32-33 convert source- specific terminology into analytic data interface terminology.
• Source adapters are provided for each source with the exception of web logs, which do not require any further source-specific conversion.
• source adapters 32-33 combine e tract-specific staging area objects into the analytic data interface 5.
• source adapters 32-33 combine staging area objects to match up the inputs and their interrelationship as required by the analytic data interface 5.
• Analytic data interface 5 transforms data for loading into data warehouse 6 for use in applications 8.
• the analytic data interface cleans data by enforcing commonalties in dates, names and other data types that appear across multiple systems and prepares it for the source-independent data warehouse.
• analytic data interface 5 includes a graphical user interface that makes it easy to configure and customize how business data is loaded into an analytic applications system such as a data warehouse 6.
• Analytic data interface 5 includes a simplified abstraction layer for the data warehouse administrator, allowing the warehouse administrator to configure how data is loaded into the analytic applications in a fraction of the time it takes to configure these capabilities programmatically as occurs in prior art systems.
• most of the complex technical problems are solved prior to data entering the analytic data interface. Often these technical problems are solved automatically by the preconfigured programming of the analytic business components without any required configuration or analysis by the warehouse administrator. This greatly simplifies the task of loading data into a data warehouse, saving significant expense and time.
• analytic business components 12-14, source adapters 32-33, and analytic data interface 5 are implemented as maplets within the warehouse designer.
• staging areas 42-43 exists as targets in the warehouse designer.
• An analytic applications system is illustrated that includes data warehouse 6, operational data store 7 and applications 8. It is appreciated that the method and apparatus of the present invention is well adapted for transporting data to any of a number of different types of analytic applications systems.
• data warehouse 6 includes a bus architecture and dimensional data models with conformed dimensions and facts for star schema analysis of data.
• data warehouse 6 includes support for slowly changing dimensions with effective dates for type II slowly changing dimensions.
• data warehouse 6 includes pre-packaged dimensions (e.g. time, time of day, IP addresses, etc.), and provision for intelligent extension fields, normalized measures, aggregate tables, and analytic fields with additional complex measures.
• data warehouse 6 is packaged as targets in a warehouse designer.
• Operational data store 7 consolidates and stores references data for loading into data warehouse 6.
• operational data store 7 retains customized, source-specific fields that will not exist in data warehouse 6 such as reference data to help standardize other formats (e.g. zip codes, currency conversion rates, and product-code to product-name translations).
• applications 8 include data warehousing applications.
• applications 8 include pre-packaged analytic tools that provide queries and reports for evaluating business performance such as, for example, applications built on the Power CenterTM data integration platform made by Informatica Corporation of Palo Alto, California.
• applications 8 include programs that allow for drill-down capability, drill-up capability and drill-across capability.
• applications 8 allow for the performance of more detailed analysis using a multi-dimensional approach (slice-and -dice capability).
• applications 8 include a user interface with web analytic capabilities that allows for generating standard reports and that includes ad-hoc query tools (that allow for drag and drop of measurements and dimensions to create custom reports).
• applications 8 are pre-configured with some aggregate tables on commonly used dimensions and facts. However, these default aggregate tables can be changed based on specific business needs. Aggregate tables contain pre-calculated pre-stored summaries that are stored in the data warehouse to improve query performance.
• Figure 4 shows a method 400 for transporting data for a data warehousing application.
• method 400 is performed by server 240 of Figure 2 which includes some or all of the components of computer 1 of Figure 1.
• the structure of Figure 3 is used to perform method 400.
• data is extracted from one or more source containing data having a standard data structure.
• data is extracted from databases 210, 220, and 230 by server 240.
• analytic business components 12-14 are operable to extract data from data sources 2-4. More particularly, analytic business component 12 extracts data from database 2.
• analytic business component 13 extracts data from database 3 and analytic business component 14 extracts data from web logs 4.
• step 402 is performed by server 240 of Figure 2.
• analytic business components 12-14 of Figure 3 translate the data extracted in step 401 in order to produce translated data that contains meaningful business terms. More particularly, analytic business component 12 translates data from database 2. Similarly, analytic business component 13 translates data from database 3 while analytic business component 14 translates data from web logs 4.
• analytic business components .12-14 are source-specific. That is, each analytic business component is adapted to extract and translate data from a specific type of database standard data structure.
• database 2 is an Ariba database
• analytic business component 12 is an analytic business component for an Ariba database.
• database 3 is a SAP database
• analytic business component 3 is an analytic business component for a SAP database.
• database 4 includes web logs
• analytic business component 14 is an analytic business component adapted to extract and translate web logs.
• the translation process hides the complexity of the source systems (e.g. databases 2-3 and web logs 4).
• analytic business components 12-14 perform joins in the data source that help to present the data in simple business terms.
• the analytic business components 12-14 present the source fields in form that is understandable to the user. For example, for SAP, business components 12-14 provide English descriptions.
• analytic business components 12-14 can be customized at implementation to provide additional business abstractions over and above those business abstractions preprogrammed into analytic business components 12-14. Moreover, analytic business components 12-14 encapsulate extraction logic as they move data from its source.
• the translated data is then loaded into staging areas as shown by step 403.
• the translated data is loaded into staging areas stored on server 240.
• the translated data is loaded into staging areas 22-24-. More particularly, analytic business component 12 loads translated data into staging area 22. Similarly, analytic business component 13 loads translated data into staging area 23 and analytic business component 14 loads translated data into staging area 24.
• the translated data is processed to obtain data having a common structure.
• server 240 is operable to process the translated data to obtain data having a common structure.
• step 404 converts source-specific terminology into analytic data interface terminology.
• staging area objects are combined to match up the inputs and their interrelationship as required by the analytic data interface 5.
• Data indicators and data indicator flags are configured based on the data. Physical deletions are performed and a delete flag is set to provide a common way to flag a record to be deleted. In addition, data type conversion and source-related clean up are performed. Also, unique key identification is configured based on the source and its data to take care of problems arising from the fact that the number of keys differ in each source. In the present embodiment, the set of rows that will be put in the data warehouse is also determined.
• source adapters 32-33 are operable for processing the translated data within staging areas 22-23 to obtain data having a common structure. More particularly, source adapters 32-34 convert source-specific terminology into analytic data interface terminology. Source adapters are provided for each source with the exception of web logs, which do not require any further source-specific conversion. In the present embodiment, source adapters 32-33 combine extract-specific staging area objects into the analytic data interface 5. In the present embodiment, source adapters 32-33 combine staging area objects to match up the inputs and their interrelationship as required by the analytic data interface 5.
• source adapters 32-33 set the delete flag. More particularly, because each source handles deletes differently, there is a need to provide a uniform delete flag to the analytic data interface. To meet this need, source adapters 32-33 handle physical deletions and delete indicators and provide a common way to flag a record to be deleted.
• source adapters 32-33 handle data type conversions. More particularly, because the same concepts are represented differently in each source, there is a need to provide data type conversion.
• source adapters 32-33 publish the structure of each field and convert the data type using a consistent approach.
• source adapters 32-33 handle any source-related clean up.
• source adapters 32-33 configure unique key identification based on the source and its data. This takes care of problems arising from the fact that the number of keys differ in each source.
• the data having a common structure is transformed into a format suitable for loading into an analytic applications system such as a data warehousing application.
• the data having a common structure is transformed into a format suitable for loading into a data warehouse as shown by step 405.
• server 240 is operable to transform the data having a common structure into a format suitable for loading into a data warehouse.
• step 405 includes consolidation of business concepts into integrated structures that are suitable for querying and reporting.
• source definitions differences are normalized into a single, common definition.
• step 405 includes, for example, code lookup (e.g.
• currency conversion unit of measure conversion and code to description field resolution
• data-driven updates intelligent expansion fields
• dimension table specific features dimension table specific features
• fact table specific features dimension table specific features
• key resolution key generation
• "bad" data flagging the user can also insert, update, or reject a determination.
• analytic data interface 5 is operable to transform data received from source adapters 32-33 and staging area 24 into a format suitable for loading into a data mart.
• analytic data interface 5 provides complex transformation logic that integrates data in two ways. First, business concepts are consolidated across an entire value chain into integrated structures that are suitable for querying and reporting.
• the analytic data interface 5 provides complex transformation logic that normalizes source definitions differences into a single,, common definition.
• Some of the transformation logic performed in the analytic data interface 5 include codes lookup (e.g. currency conversion, unit of measure conversion and code to description field resolution) and data-driven updates.
• the analytic data interface includes dimension table specific features (e.g. surrogate key generation, slowly changing dimension support, and support for effective dates), fact table specific features (e.g. support for exchange rate lookup and support for dimension key lookup), key resolution, key generation, "bad" data flagging.
• dimension table specific features e.g. surrogate key generation, slowly changing dimension support, and support for effective dates
• fact table specific features e.g. support for exchange rate lookup and support for dimension key lookup
• key resolution e.g. support for exchange rate lookup and support for dimension key lookup
• key generation e.g., "bad" data flagging.
• the analytic data interface allows a user to insert, update, or reject a determination.
• analytic data interface 5 includes slowly changing dimension logic for tracking historically important data.
• a historically significant attribute is one that you want to retain for your records, even if subsequent records show that a change has been made.
• records within analytic data interface 5 can be configured using two different types of slowly changing dimension categories: historically insignificant attributes (Type 1 slowly changing dimensions) and historically significant attributes (Type 2 slowly changing dimensions).
• Type 1 slowly changing dimensions the data field is simply overwritten.
• type 1 slowly changing dimensions does not maintain history, it is the simplest and fastest slowly changing dimension.
• Type 1 slowly changing dimension is used when the old value of the changed dimension is not deemed important or of interest to track, or is a historically insignificant attribute. For example, a user may want to use type 1 when changing incorrect values in a field.
• Type 2 slowly changing dimensions create a new record. This is the most common slowly changing dimension because it allows the user to track history. The old record allows for pointing to all history prior to the change, and the new record points to all history after the change. Because each change generates a new record, old and new records allow for partition history exactly.
• state name in a supplier table is a type 2 slowly changing dimension
• a new, current record is generated upon changes to the state in which a supplier is located in. The previous value remains a record, and the new current record is a separate record.
• the slowly changing dimension logic of the present invention gives four types of records that are stored in the staging area, new records, changed records with data that is not historically tracked, changed records having historical significance, and changed records having historical significance, and changed records whose changes have no significance of any kind.
• a new customer key is used for the old sales - record while the old customer key continues to be used for the new record.
• a simple overwrite of the record showing the new combination suffices.
• the dimension table key is resolved only when both of the following facts are true: the key does not already exist in the data mart, and the key resolution attributes of the fact change.
• a predetermined alphanumeric character is used to indicate a need for data cleansing. That is, because most analytic data interface fields are mapped to fields in the transaction system, be it Ariba, ORMS or SAP, some fields may not be populated with values. For instance, a row in the supplier table may have information on a supplier's address, but may have no value in the supplier's region field. If a report is run on supplier prices by region, the suppliers for whom region information is missing would normally be excluded. However, analytic data interface 5 provides a feature to identify all occurrences of missing values. In the present embodiment.the identifier for missing values is a question mark ("?").
• the question mark is a sign that the organization's data needs to be "cleansed.”
• Cleansing the data in this case involves drilling into the category marked as "?" to learn, perhaps the supplier names or numbers within that group.
• the data warehouse administrator can then correct each of those suppliers by entering in the regional information on the back end.
• the logic for populating null fields is in the analytic data interface 5. More particularly, the analytic data interface looks for columns that are both linked to a character data type and that are null, and populates them with a "?.” It is appreciated that the use of a character such as a question mark is simply the default setting to represent missing data.
• the present invention is well adapted for using a different character or multiple characters.
• analytic data interface 5 Because the data input into analytic data interface 5 has a common structure, there is no need to process data from each data source independently. More particularly, the data received at analytic data interface 5 has already been translated to obtain meaningful business terms (step 402) and has been processed to obtain data having a common structure (step 404). Therefore, the data from different sources (e.g. databases 2-3 and web logs 4) can be treated as a single data source for the purpose of transformation (step 405).
• sources e.g. databases 2-3 and web logs
• the analytic data interface includes a graphical user interface
• it is easy to configure and customize how business data is loaded into an analytic applications system such as a data warehouse.
• the analytic data interface includes a simplified abstraction layer for the data warehouse administrator
• the warehouse administrator can configure how data is loaded into the analytic applications in a fraction of the time it takes to configure these capabilities programmatically.
• the task of configuring data is greatly simplified.
• the present invention greatly simplifies the process of loading data into a data warehouse, saving significant expense and time.
• maplets reusable objects that represent a set of transformations
• maplets are used for code lookup, for address lookup, and for extraction.
• maplets are used to identify all business locations and identify all business hierarchical structures.
• the transformed data is loaded into an analytic applications system such as a data warehousing application.
• the data is loaded into a data warehouse as shown by step 406.
• the data is loaded into target databases 250- 260 which can be databases of a data warehouse.
• the data is loaded into data warehouse 6.
• data is also loaded into operational data store 7.
• Applications 8 then extract data from data warehouse 6 and operational data store 7 for performing analysis.
• staging area 32 will contain data from an Ariba database that has been translated in order to include meaningful business terms
• staging area 33 will contain data from an SAP database that has been translated in order to include meaningful business terms
• staging area 34 will contain- data from web logs that has been translated in order to include meaningful business terms.
• source adapter 32 will be an Ariba source adapter and source adapter 33 will be a SAP source adapter while no source adapter is required for data from web logs 4. Because the data from each of sources 2-4 is provided to analytic data interface 5, the data can be treated as a common data source for the purpose of transforming the data into a format suitable for loading into a data warehousing application (step 405 of Figure 4). Therefore, the warehouse administrator needs only define and program for data transport a single time using the graphical user interface of the analytic data interface. Not three separate times as would be required by prior art systems.
• the present invention provides a method and apparatus that allows for transporting data such that the data can be used in data warehousing applications.
• the present invention provides a method and apparatus that takes advantage of the standardization of database components.
• the present invention provides a method and apparatus that reduces the time required to define and program data transport for data warehousing applications. While the present invention has been described in particular embodiments, it should be appreciated that the present invention should not be construed as limited by such embodiments, but rather construed according to the below claims.

Landscapes

• Engineering & Computer Science (AREA)
• Databases & Information Systems (AREA)
• Theoretical Computer Science (AREA)
• Data Mining & Analysis (AREA)
• Physics & Mathematics (AREA)
• General Engineering & Computer Science (AREA)
• General Physics & Mathematics (AREA)
• Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
• Warehouses Or Storage Devices (AREA)

Applications Claiming Priority (5)

Publications (1)

Family

ID=26908858

Family Applications (1)

Country Status (4)

Families Citing this family (9)

Family Cites Families (1)

• 2001
  • 2001-06-21 WO PCT/US2001/019768 patent/WO2002001415A2/en not_active Ceased
  • 2001-06-21 AU AU2001270013A patent/AU2001270013A1/en not_active Abandoned
  • 2001-06-21 CA CA2414230A patent/CA2414230C/en not_active Expired - Fee Related
  • 2001-06-21 EP EP01948540A patent/EP1374090A2/de not_active Ceased

Non-Patent Citations (2)

Also Published As

Similar Documents

Legal Events