WO2007095959A1 - Method for generating data warehouses and olap cubes - Google Patents

Method for generating data warehouses and olap cubes Download PDF

Info

Publication number
WO2007095959A1
WO2007095959A1 PCT/DK2007/050024 DK2007050024W WO2007095959A1 WO 2007095959 A1 WO2007095959 A1 WO 2007095959A1 DK 2007050024 W DK2007050024 W DK 2007050024W WO 2007095959 A1 WO2007095959 A1 WO 2007095959A1
Authority
WO
WIPO (PCT)
Prior art keywords
specific
sales
view
data
execution
Prior art date
Application number
PCT/DK2007/050024
Other languages
French (fr)
Inventor
Heine Krog Iversen
Thomas Christiansen
Original Assignee
Timextender A/S
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Priority claimed from US11/361,512 external-priority patent/US20070203933A1/en
Application filed by Timextender A/S filed Critical Timextender A/S
Publication of WO2007095959A1 publication Critical patent/WO2007095959A1/en

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR 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/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Definitions

  • the present invention relates to a method for automatically generating data warehouses and OLAP cubes.
  • Business intelligence systems are crucial tools in today's hugely complex data environment.
  • Business Intelligence is formed by collecting, storing and analyzing data as support in more or less critical decision-making processes.
  • Example usage includes market segmentation, product profitability, inventory and distribution analysis.
  • SQL and other query languages are essentially a specialists' language, generally not accessible to the average person skilled in making business decisions based on business intelligence.
  • Business intelligence staff specifies but does not implement data warehousing, and IT personnel implements, but does not specify data warehousing.
  • business intelligence staff specifies new business intelligence solutions, however small the changes may be compared to previous solutions, they require that a new data warehouse is built from the ground and up. The reason is that specification changes are so pervasive in the building process, a fact that is mirrored in the pervasiveness of the changes that IT personnel must make in existing query code is order to meet the modified specifications. Again, errors are likely during the implementation and are typically discovered only through rigorous testing procedures.
  • a field named "DW_SourceCode” is added in the "Source”. It is defined to contain the name of the data source, "Sales” in this example. That is, the "Sales” data source contains the table "Customer”. In the example, the "Destination” also has an extra added field, "DW_TimeStamp”.
  • the present invention solves the problems described above by providing an automated data warehousing and OLAP cube building process.
  • the invention allows a person who is not a database query language expert to build validated data warehouses, and OLAP cubes based on such data warehouses.
  • a method that allows a user to generate an OLAP cube based on one or more data sources comprising a set of source tables having a set of source fields, the method comprising the steps of:
  • execution script • initializing an execution script to be adapted to generate, when executed, a data warehouse holding validated data based on said one or more data sources, the execution script also being adapted to comprise execution stubs, execution stubs being predefined database query language segments consisting of one or more database manipulation commands that may depend on parameters and selections provided with relation to said data sources;
  • each data warehouse view being formed by carrying out at least the steps of: o the user selecting from the set of tables in the data selection a view-specific first view table to be used in said view; o the user selecting one or more fields from said view-specific first view table; o optionally: ⁇ the user selecting, from the set of tables in the data selection, a view-specific second view table to be used in said view;
  • the user providing a join criterion for joining said view-specific first and second view tables; o providing a name for said data warehouse view; o adding to the execution script a view execution stub representing a creation of said data warehouse view having said name and containing said selected fields from said view-specific first view table, said view execution stub further comprising, on the condition that said view-specific second view table has been selected, statements representing:
  • a cube generation instruction set being a set of instructions that can interact with an Application Programming Interface (API) of an OLAP cube generation software application and cause said software application to generate an OLAP cube; • the user selecting a fact table for said cube, the fact table being either:
  • each dimension-specific table in said dimension-specific set of dimension tables forming a basis for one or more table-specific levels belonging to said dimension, the dimension-specific set of dimension tables being selected from one or both of:
  • each measure being provided with a measure-specific aggregation type, each measure being one of: o a standard measure based on a measure-specific field in the fact table; o a derived measure based on one or more fields in the fact table, said derived measure being calculated based on a measure-specific formula adapted to operate on said one or more fields, derived measures being calculated before aggregation is performed on said measure in accordance with said measure-specific aggregation type; o a calculated measure based on:
  • An OLAP cube is a database model that treats data not as relational tables and columns, but as categorized and summarized information stored in cells.
  • a cube comprises dimensions, measures and a fact table.
  • a measure is a set of values that are based on a field in the cube's fact table. Measures are usually based on numeric fields, such as an item quantity or a currency amount, but could also be a count of character-based fields. Measures are calculated for all possible combinations of dimension values and are stored in the cube cells. Each cell in a cube is addressed by a set of coordinates specifying a "position" in the cube.
  • a fact table is a table that contains numerical measures and keys (fields) relating facts to dimension tables.
  • the execution script is a cornerstone in the present invention.
  • the execution script is built as the steps above are carried out.
  • the steps add predefined execution stubs to the execution script.
  • the most widely used database manipulation language is SQL, "Structured Query Language", and in this case the execution stubs will be SQL language stubs. We point out that the principles of the invention do not rely on a specific query language.
  • the initialization of the execution script can mean to simply provide an empty text file.
  • Execution stubs are added to the execution script by amending them to the file.
  • Another example involves building the execution script in a computer memory.
  • Execution stubs are partly predefined, which is another cornerstone in the invention.
  • a user needs not have any knowledge of SQL or other query language to use the method according to the first aspect to build a data warehouse.
  • the predefined SQL segments (execution stubs) are adjusted to reflect the selections and the parameters he provides.
  • data sources form the basis for OLAP cubes.
  • a data source holds one or more tables, and a table holds a number of rows, each row consisting of at least one field.
  • table and field information for each of the data sources may be extracted for instance using appropriate data dictionaries.
  • This information is then presented to the user, which may be done in several ways.
  • information is presented to the user via a user-friendly interface, such as a graphical user interface (GUI).
  • GUI graphical user interface
  • a GUI also provides a practical way of allowing the user to provide the required input.
  • Fig. 4 illustrates an example where a data source "Sales” has been provided and translated using a data dictionary, and finally is presented to the user by way of a graphical user interface.
  • Tables “Country”, “Customer” and “CustomerGroup” are visible in the illustration.
  • the figure also illustrates that the table “Customer” contains the fields “Customerld”, “CustomerName”, “Country” and “Groupld”.
  • the checkboxes in the graphical user interface allow the user to easily select or deselect specific tables and table fields.
  • Fig. 4 illustrates a specific data selection.
  • all three tables and their fields have arbitrarily been selected. It is of course possible to select fewer tables, and fields may also be left out (not be selected) if they are not needed in the OLAP cube. Using a GUI, making or changing the data selection is easily done.
  • a new table a "valid table" is created for each table in the data selection.
  • the definition of the table can constrain the contents of the fields, for instance using the NULL or NOT NULL statements in a CREATE TABLE function (using SQL terminology). It may be desirable to add extra fields when the valid tables are created. Extra fields can be used for comments. In any automated process, it is desirable to carry along documentation information. In the automated data warehousing process according the first aspect of the present invention, it may for instance be useful to store, in the valid table, information about the data source on which it is based. The time of row insertions into the tables is another useful parameter that one might include.
  • the execution stub is structurally predefined, but also adaptable to the data source and to the user's data selection. This will be further illustrated in "Detailed description of selected embodiments”.
  • OLAP cubes are built from the valid tables in a data warehouse, valid tables comprising validated (“scrubbed") data from tables in the data sources.
  • Data from the data selection must be validated before it is entered into the data warehouse.
  • Data scrubbing is the process by which data from a data source is validated and invalid data from the data source is identified, removed or corrected.
  • Data is validated on a table-by-table, row-by-row basis. For each table in the data selection (the tables and fields selected by the user), a set of validation rules are provided. These rules are responsible for the validation of the data.
  • a useful rule might be that a field may not be NULL (or empty, if the field is a text field). An empty "Customerld" is likely an error in the data provided to the database, and it is desirable to be aware of this error before data is entered into the data warehouse. Another useful rule might be that a field may not exceed a certain value.
  • the validation rules are enforced by adding to the execution script execution stubs representing the desired validation criteria. Statements taking care of insertion of rows that comply with the table- specific set of validation rules are also added.
  • Selecting a specific validation rule type causes a specifically designed execution stub to be employed.
  • a code example illustrates what a validation execution stub may look like.
  • the user may want to create a number data warehouse views. Views combine data from multiple tables into one logical object, which can be used as fact table for cubes. This solves a problem on SQL Server 2000, namely there can be only one fact table per cube.
  • a view is created by selecting a table on which the view shall be based and then selecting a number of view fields from the table which shall be used in the view. After forming the selection and providing a name for the view, an execution stub representing the creation of the view having the provided name and containing the selected fields is added to the execution script. The user may also selected more than one table for use in the view. Fields from across tables are logically combined using a join statement in the execution stub.
  • the OLAP cube building has a natural outset. Rather than representing all information from the data sources to the user, only the data warehouse data is represented. Thus, the user needs not select a fact table for the cube from all tables in the data sources, many of which are likely completely unrelated and not even useful. Instead, he chooses from only the valid tables in the data warehouse or from the created views, if any. Clearly this is highly advantageous compared to separately building a data warehouse, and then subsequently providing the data selection information once again during the subsequent cube generation.
  • the cube's dimensions are provided. Each dimension is based on a set of dimension tables, and thus for each dimension, a set of dimension tables must be provided. Again, not only tables from the data warehouse but also created views may be selected, and thus both valid tables and views can serve as dimension tables.
  • Levels are chosen for each dimension based on the selected dimension tables. For each level added to the dimension, a key field and a name field must be indicated.
  • measures can be added.
  • the measures are based on the fact table. There are three types of measures:
  • a standard measure is simply a field the fact table.
  • a derived measure can be based on more than one field, and it may involve a calculation using the fields on which it is based. However, a derived measure is calculated before aggregation in the cube.
  • a calculated measure may depend on one or more fields in the fact table, on standard measures, and on other calculated measures.
  • the calculated measure is characterized by a multidimensional expression.
  • the actual cube generation is performed via an Application Programming Interface (API) of a cube generation software application, for instance Analysis Management Objects (AMO); Decision Support Objects (DSO); or Oracle OLAP API.
  • API Application Programming Interface
  • AMO Analysis Management Objects
  • DSO Decision Support Objects
  • Oracle OLAP API Oracle OLAP API
  • execution stub representing the creation of the error tables are added to the execution script. Furthermore, execution stubs representing insertion of non-complying rows into the error tables are also added. A table row that does not comply with the table-specific set of validation rules is inserted into the table-specific error table, and erroneous rows may then easily be reviewed along with the validation rules that they did not comply with. An execution stub that handles this is added to the execution script.
  • a staging database may advantageously be used for holding relevant data from data sources.
  • the staging database may then act as a source for cubes.
  • Using a staging database further has the advantage that data can be extracted selectively from the data sources using a set of selection rules. As an example, it may be desirable to extract only data that corresponding to sales postdating 2004, rather than extracting all data dating back to the earliest recorded data in the data source table.
  • methods of building the data warehouse further comprise the steps of:
  • staging data being data that correspond to said data selection
  • table-specific raw tables, valid tables, and error tables are created in the staging database.
  • Selection rules are table-by-table, field-by-field specific.
  • the table-specific selection rules are automatically built into table-specific DTS packages used for copying data from the data source to the corresponding raw tables in the staging database. The selection would then be realized by adding WHERE statements (in SQL terminology) representing the table-specific selection rules.
  • WHERE statements in SQL terminology
  • the user preferably provides the set of selection rules via a user-friendly interface rather than by providing SQL code (such as actual WHERE statements) representing the table-specific selection rules.
  • All transformation, validation etc. may then be done on the staging database after the source data has been successfully copied to it.
  • the user may provide, for each field of each table in the data selection, a set of transformation rules to be applied to the data as it is transferred from the data sources to the data warehouse.
  • the user may for instance have asked that all customer names in a specific table in the data selection be transferred in all upper case.
  • the set of transformation rules are preferably provided via a user-friendly interface, such as a graphical user interface.
  • Employing a set of default rules may also be desirable, for instance for correcting systematic errors or for providing some sense to an empty field.
  • An empty field may for instance be given a default value during the transfer. The user may indicate that if the field "CustomerName" in the "Customer” table is empty, it shall be given the value "(name not provided)". This provides more useful information than does an empty field.
  • relevant personnel will be notified in case a warning or error is identified.
  • An email detailing the problem or an SMS (short message system) message, a fax, a page, an entry in an operating system event log; or some other type of notification with a more or less detailed description of the warning or error may be dispatched.
  • the project file is human readable. This makes it easier for a user to study the data warehouse structure.
  • a project file may easily be distributed to users elsewhere.
  • Company business staff may for instance design business intelligence processes centrally, for instance by creating execution scripts relating to local business conditions.
  • Regional offices can then directly implement the scripts and thereby handle local business intelligence themselves, but in a company-consistent fashion, what the company would consider "best practices".
  • a method for allowing a user to generate a data warehouse holding validated information based on one or more data sources comprising a set of source tables having a set of source fields.
  • software that implements the methods according to the invention.
  • Such software is capable of dispensing the execution stubs described above.
  • the software will provide appropriately adapted execution stubs fitting the specific tasks.
  • choosing a table and one or more of its fields causes a set of execution stubs to be added to the execution scripts, such as a valid table creation stub and a validation execution stub.
  • the software may be capable of providing execution stubs in more than one query language. It may be capable of loading data from several types of data sources, such as vendor-specific customer relationship management systems; human resource management systems; enterprise resource planning systems; database systems, such as Oracle, SQL Server, and Microsoft Excel. Information about for instance tables, views, fields, data types and constraints is extracted from data sources using vendor specific schema information ("data dictionaries"). This information may for instance be extracted by connecting directly to the data sources using either managed .Net providers or legacy providers.
  • Methods according to the invention may also or alternatively be implemented directly in processing hardware, such as an application-specific integrated circuit, or some parts may be provided by software running on a hardware processor, and other parts may be implemented on an application-specific integrated circuit.
  • tables are located in more than on data source.
  • a company's product management division (handling for instance tables “Products”, “ProductCategory” and “ProductGroup”) may for instance operate two SQL servers and an Oracle database.
  • the logistics division (handling for instance tables “OrderLines”, “Orders” and “Customers”) may operate three Oracle servers, and the occasional Microsoft Excel database.
  • Figs. 5 and 6 illustrate interfaces for providing an Oracle source and an Excel source, respectively.
  • Fig. 7 illustrates a typical system process of forming a data warehouse and OLAP cubes.
  • a number of data sources such as an Enterprise Resource System source (“ERP” in Fig. 7), A Microsoft Excel source (“Excel” in Fig. 7), a Customer Relationship Management source (“CRM” in Fig. 7) and a Human Resource source (“HR” in Fig. 7) may form the basis for the data warehouse ("Data Warehouse” in Fig. 7) and OLAP cubes also illustrated in Fig. 7.
  • ERP Enterprise Resource System source
  • CRM Customer Relationship Management source
  • HR Human Resource source
  • Some methods according to the present invention allow a user to extract and validate data from the data sources and generate a data warehouse based thereon.
  • Other methods according to the invention furthermore allow a user to first generate the data warehouse and then generate OLAP cubes based on the data warehouse.
  • the invention is implemented to use a graphical user interface.
  • Fig. 9 illustrates the structure of the sample data source "Sales”, which may for instance be an Oracle database.
  • Fig. 10 to Fig. 12 illustrate the definition of the tables "Customer”, “OrderLines” and "History_OrderLines”.
  • the sample database contains a simple implementation of a sales order application.
  • a project may for instance be a file.
  • a specific project is capable of holding all relevant information that relate to execution of a corresponding specific method in accordance with the invention.
  • a project is defined by providing a "Project name” and a "File path”.
  • the "File path” describes the desired location of the project.
  • We name the project "Sample. dmp”. dmp" might stand for "data manager project", Data Manager being the name of a software package implementing one or more methods according to the invention.
  • the file is located in the root folder C: ⁇ .
  • Fig. 8 illustrates a typical flow diagram for a method in accordance with the invention.
  • the process spans from initializing the execution script, extracting data from data sources ("DS" in Fig. 8), generating a data warehouse ("DW” in Fig. 8), all the way to the generation of an OLAP cube using an OLAP cube generation software.
  • a method according to the invention for generating a data warehouse of course comprises only a subset of the illustrated steps.
  • the method creates not only a valid table and an error table for each source table, but also a "raw" table.
  • the raw table is a table on a staging database to which data is transferred from the data source. Moving data from the data source to a staging database reduces the load on the data source servers.
  • Fig. 14 illustrates the creation of a staging database.
  • Fig. 15 illustrates the tables and fields of a data source named "Sales". The user has selected all fields and tables from the data source. Having selected these fields, execution stubs are added to the project's execution script.
  • Fig. 16 illustrates the raw table "Sales_OrderLines_R" and its fields created on the staging database resulting from the user having selected the corresponding fields under "OrderLines” in the data source representation in Fig. 15.
  • the raw table simply holds all data from the "OrderLines” table on the data source, whether they are valid or not. Data is simply transferred from the data source ("Sales") to this table on the staging database. The same process takes place for the other tables in the data selection in Fig. 15.
  • the execution stub added to the execution script to create the raw table "Sales_OrderLines_R" in Fig. 16 is:
  • the execution stub represents the selections made by the user. However, its structure is predefined, in accordance with the invention. This is one of very important advantageous of the present invention.
  • DW_SourceCode may contain the name of the data source, "Sales” in this example (the “Sales” data source contains the "Customer” table).
  • DW_TimeStamp may contain the time a row is inserted into the raw table.
  • the field “DW_TimeStamp” is given a default value of getdate().
  • DW_TimeStamp smalldatetime NOT NULL default(getdate ⁇ ) ) takes care of the creation of the error table "Sales_Orderl_ines_E" in Fig. 18.
  • error table four extra fields are created: "DW_Severity”, “DW_ErrorMessage”, “DW_SourceCode”, and "DW_TimeStamp”.
  • the field "DW_Severity” may be useful for indicating whether insertion of a specific row into the error table is triggered by for instance a "warning" or an "error". Different actions may be taken depending on a severity parameter. Errors and warnings might be defined as:
  • DW_ErrorMessage might contain a message relating to the specific warning or error that caused a specific row to be inserted into the error table.
  • "DW_SourceCode” and "DW_TimeStamp” have the same meaning described for the valid table.
  • the valid table in Fig. 17 and the error table in Fig. 18 are created to having the same fields as the raw table, which in turn contains the fields selected by the user, as illustrated in Fig. 15. Again, a valid table and an error table are generated for each of the tables selected by the user. This means that a raw table, a valid table and an error table are created for all the tables in Fig. 9.
  • This execution script also generated using only the data selection information already provided.
  • the execution stub represents the selections made by the user, but is structurally predefined. Manual labor is optional, not mandatory.
  • An execution stub for inserting a row into the error table may look similar, for instance like this:
  • An execution stub for determining whether a row is valid may have the following structure (sometimes referred to as "data scrubbing"):
  • the structure of the execution stub is once again predefined, adjusted according to the data selection made by the user.
  • the set of validation rules provided for the "OrderLines" table from the "Sales” data source are incorporated automatically in accordance with the method.
  • the above execution stub also illustrates a use of the severity rating described previously.
  • execution stubs above are provided only for exemplification.
  • the structure of execution stubs is defined by the aspect formulation, not by the examples above.
  • the functions may be literally different from those above, but still implement the same functions.
  • the extra fields illustrated above may be left out, others may be inserted; tables can be defined with other NULL/NOT NULL constraints than those shown in the examples; further SELECT statements and/or WHERE statements can be inserted; and so on.
  • NotEmpty Field value can not be NULL and for text data types, can not be blank/empty
  • Fig. 19 illustrates a view used for generating a cube name "Sales". The user is allowed to choose only among the valid tables corresponding to the original data selection in Fig. 15.
  • This view is taken care of by an appropriately adapted execution stub added to the execution script.
  • the execution stub will be based on the SQL statement CREATE VIEW. Since we have selected fields from different tables, they must be joined. We have declared that the key “Orderld” is common for the tables “Sales_Order_V” and “Sales_OrderLines_V” (see Fig. 9), and thus the view statement will contain an ON statement that selects for instance "Customer” from "Sales_Order_V” when "Orderld” - key for both tables - coincide.
  • the following execution stub creates the view:
  • Sales_ ⁇ rderLines_V.SalesAmount AS [SalesAmount]
  • Sales_ ⁇ rders_V ON Sales_ ⁇ rders_V. ⁇ rderId Sales_OrderLines_V.OrderId GO
  • Fig. 20 shows an overview of the DTS packages that copy the selected table tables from the data sources to the staging database.
  • Fig. 21 illustrates a "Sales" cube having dimensions "Product”, “Customer”, and “Region”. The cube is based on the view in Fig. 19.
  • the dimension "Product” has levels “Productgroup”, “Category”, and “Product”.
  • the dimension "Customer” has levels “Group” and “Customer”.
  • the dimension "Region” has levels “Region” and "Country”.
  • the cube is furthermore defined by measure “Quantity sold”, “Net amount”, “Costs”, “Contribution”, and “Contribution margin”.
  • Fig. 21 illustrates, all cubes, dimensions, levels and measures are defined via the interface without need for providing SQL code.
  • only information in the data selection is available for selection (see Fig. 15), removing the redundancy known from conventional approaches to building OLAP cubes, where the data warehousing and cube building are separated.
  • Fig. 22 illustrates the definition of a measure, in this case "Quantity sold”.
  • the “Field” for this measure is “Quantity” from the fact table (Fig. 19).
  • Fig. 23 illustrates a calculated measure, "Contribution”, based on a difference between the measures “Net amount” and “Costs”.
  • Fig. 24 illustrates a calculated measure, "Contribution margin”, which is defined as the ratio between the measure "Contribution” from Fig. 23 and the measure "Net amount”.
  • Fig. 26 illustrates what the final cube looks like when presented in an analysis tool such as Microsoft's Analysis Manager from Microsoft Analysis Service.
  • a notification is presented when an error occurs during validation.
  • error tables for storing row that do not comply with the validation rules.
  • Fig. 9 After the list of tables, we address how such a notification may appear.
  • Fig. 25 illustrates an example of how such a notification may be presented to the user.
  • another error indication may also be dispatched, for instance in the form of an email to relevant personnel.
  • the data warehousing is performed completely automatically once the execution script has been generated.
  • the data warehousing takes place automatically, but in case a data source contains a field that violates the validation rules, this error must be communicated to the relevant personnel.
  • This section illustrates the complete execution script generated for the example described above for generating a data warehouse.
  • N'lsUserTable 1 1) drop table [dbo].[Sales_Customer_V] CREATE TABLE Sales_Customer_V( Customerld int NOT NULL, CustomerName varchar(50) NOT NULL, Country varchar(50) NOT NULL, Groupld int NOT NULL, DW_SourceCode varchar(15) NULL,
  • N'lsUserTable 1 1) drop table [dbo].[Sales_CustomerGroup_R] CREATE TABLE Sales_CustomerGroup_R(
  • N'IsUserTable 1 1) drop table [dbo].[Sales_CustomerGroup_V] CREATE TABLE Sales_CustomerGroup_V( Groupld int NOT NULL, Name varchar(50) NOT NULL, DW_SourceCode varchar(15) NULL,
  • N'lsUserTable 1 1) drop table [dbo].[Sales_ProductCategory_E] CREATE TABLE Sales_ProductCategory_E( Categoryld int NULL, Name varchar(50) NULL, DW_Severity varchar(l) NOT NULL, DW_ErrorMessage varchar(lOOO) NOT NULL, DW_SourceCode varchar(15) NULL,
  • N'IsUserTable 1 1) drop table [dbo].[Sales_Region_R] CREATE TABLE Sales_Region_R(
  • N'IsUserTable 1 1) drop table [dbo].[Sales_Region_E] CREATE TABLE Sales_Region_E(

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)

Abstract

Presently, the process of building business intelligence solutions is rather cumbersome. It typically requires either access to business intelligence staff with significant IT competencies or collaboration between specialized IT personnel providing technical implementation and business intelligence staff providing business intelligence specifications. The business intelligence staff will furthermore verify data produced by the implementation. In turn, any errors must be communicated back to the IT personnel, with a specification of how the errors are to be corrected. The present invention provides a novel method for generating data warehouses and OLAP cubes, the methods potentially requiring little or no knowledge of database query language programming.

Description

Method for generating data warehouses and OLAP cubes
FIELD OF THE INVENTION
The present invention relates to a method for automatically generating data warehouses and OLAP cubes.
BACKGROUND OF THE INVENTION
Business intelligence systems are crucial tools in today's hugely complex data environment. Business Intelligence is formed by collecting, storing and analyzing data as support in more or less critical decision-making processes. Example usage includes market segmentation, product profitability, inventory and distribution analysis.
Companies collect large amounts of data in their business operations utilizing a wide range of software programs, such as ERP and CRM systems, spreadsheets, and various more or less custom-tailored data handling systems. Different information systems use different data structures and information fields. Retrieving and analyzing information from a range of unaligned systems is presently a tedious and resource-demanding process that requires expert assistance. Like most "programming languages", SQL and other query languages (such as Business System 12, Hibernate Query Language (HQL) and Object-oriented SQL), are essentially a specialists' language, generally not accessible to the average person skilled in making business decisions based on business intelligence.
In reality, the technical formation of business intelligence in an organization often relies on highly trained IT personnel with extensive programming knowledge. Unfortunately, this training is rarely paired with business intelligence skills, and thus the technical implementers typically can not add much perspective on the business intelligence side.
The process of actually building business intelligence solutions is often iterative. Given that highly trained IT personnel provide the technical support, the process requires business intelligence staff to verify data from the IT personnel and report back with errors, if identified. Business intelligence staff will then return with a specification of how the errors are to be corrected.
In the end, no one person can claim ownership to the solution. Business intelligence staff specifies but does not implement data warehousing, and IT personnel implements, but does not specify data warehousing. When business intelligence staff specifies new business intelligence solutions, however small the changes may be compared to previous solutions, they require that a new data warehouse is built from the ground and up. The reason is that specification changes are so pervasive in the building process, a fact that is mirrored in the pervasiveness of the changes that IT personnel must make in existing query code is order to meet the modified specifications. Again, errors are likely during the implementation and are typically discovered only through rigorous testing procedures.
To illustrate the tediousness of existing methods of building business intelligence solutions, we will briefly discuss the following "Customer" table:
Customerld CustomerName Country Group
1 John Doe DK 1
2 Jane Doe DE 1
3 Large Corp Inc NZ 2
4 Small Corp Inc IT 2
5 International Traders us 3
6 First Class Imports AU 3
In a conventional setting, using for instance SQL Server, "Customer" data may be transferred from a data "Source" to a data "Destination", as illustrated in Fig. 1, using the illustrated manually coded SQL query in Fig. 1. Fig. 2 illustrates the field mapping definition used when forming the "Destination". In the present example, a field named "DW_SourceCode" is added in the "Source". It is defined to contain the name of the data source, "Sales" in this example. That is, the "Sales" data source contains the table "Customer". In the example, the "Destination" also has an extra added field, "DW_TimeStamp".
In SQL Server, viewing this "Destination" table might look like Fig. 3, where we have named the destination table "Sales_Customer_R".
In the case described above, we selected all fields in the "Customer" table. A change in our selection, such as by removing a field, would require that we modify the code accordingly. Had we made a smaller selection, such as "CustomerId"+"CustomerName", and wanted to change it to "CustomerId"+"Country"+"GroupId", we would need to implement this change in the code accordingly. In case we need data from several tables, for instance 5 different tables, changes must be made in a number of SQL segments, depending on the relationship between the fields in the different tables. This is time consuming, and changing specifications back and forth, or incorporating a "Customer"-like table with another format (the fields may have different names, for instance), requires pervasive modification of SQL segments. When moving on in the business intelligence building process, to tasks such data transfer, validation and viewing etc., a change in the business intelligence specification will involve changes in yet another set of SQL segments. Changes in table and field selections will carry through from the table definition in the beginning, to the creation of views or building of OLAP cubes at the other end of the process. This propagation of small changes far into the SQL code clearly illustrates the large degree of code redundancy that manual data warehouse and OLAP cube building involves.
The processes above being both error-prone and tremendously time-consuming, it is clear that there is a need for a simplified method of building data warehouses and OLAP cubes. Such a method must provide mechanisms that reduce the possibility of introducing coding errors during the data warehouse and OLAP cube building, and it must reduce the redundancy of information provided when building the data warehouse and OLAP cube.
SUMMARY OF THE INVENTION
The present invention solves the problems described above by providing an automated data warehousing and OLAP cube building process. The invention allows a person who is not a database query language expert to build validated data warehouses, and OLAP cubes based on such data warehouses.
In a first aspect, a method is provided that allows a user to generate an OLAP cube based on one or more data sources comprising a set of source tables having a set of source fields, the method comprising the steps of:
• initializing an execution script to be adapted to generate, when executed, a data warehouse holding validated data based on said one or more data sources, the execution script also being adapted to comprise execution stubs, execution stubs being predefined database query language segments consisting of one or more database manipulation commands that may depend on parameters and selections provided with relation to said data sources;
• the user forming a data selection consisting of a set of tables and a set of fields selected from the source table and source fields; • for each table in said data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific valid table, said valid table being a table adapted to hold validated rows, validated rows being rows that belong to said each table and comply with a table-specific set of validation rules provided for said each table, a validation rule being a field-specific condition that said specific field must fulfill; o adding to the execution script a table-specific execution stub adapted to determine whether each row in said each table complies with the table- specific set of validation rules and if so, to insert said row into the table- specific valid table;
• optionally creating a set of data warehouse views based on one or more of the valid tables that are to form part of said data warehouse, each data warehouse view being formed by carrying out at least the steps of: o the user selecting from the set of tables in the data selection a view-specific first view table to be used in said view; o the user selecting one or more fields from said view-specific first view table; o optionally: ■ the user selecting, from the set of tables in the data selection, a view-specific second view table to be used in said view;
■ the user selecting one or more fields from said view-specific second view table;
■ the user providing a join criterion for joining said view-specific first and second view tables; o providing a name for said data warehouse view; o adding to the execution script a view execution stub representing a creation of said data warehouse view having said name and containing said selected fields from said view-specific first view table, said view execution stub further comprising, on the condition that said view-specific second view table has been selected, statements representing:
■ an inclusion of said selected fields from said view-specific second view table into said data warehouse view;
■ the provided join criterion; • executing the execution script to form said data warehouse;
• initializing a cube generation instruction set for generating said cube, a cube generation instruction set being a set of instructions that can interact with an Application Programming Interface (API) of an OLAP cube generation software application and cause said software application to generate an OLAP cube; • the user selecting a fact table for said cube, the fact table being either:
■ a table selected from the data warehouse; or
■ a view selected from the set data warehouse views, if created;
• the user providing one or more cube dimensions that are to form part of said cube;
• for each of said dimensions: o the user selecting a dimension-specific set of dimension tables on which said dimension shall be based, each dimension-specific table in said dimension-specific set of dimension tables forming a basis for one or more table-specific levels belonging to said dimension, the dimension-specific set of dimension tables being selected from one or both of:
■ the data warehouse;
■ the set of data warehouse views, if created; o for each dimension table in said dimension-specific set of dimension tables:
■ the user selecting, from said dimension table, a level-specific key field and a level-specific name field to form a level;
• the user providing a set of measures that are to form part of said cube, each measure being provided with a measure-specific aggregation type, each measure being one of: o a standard measure based on a measure-specific field in the fact table; o a derived measure based on one or more fields in the fact table, said derived measure being calculated based on a measure-specific formula adapted to operate on said one or more fields, derived measures being calculated before aggregation is performed on said measure in accordance with said measure-specific aggregation type; o a calculated measure based on:
■ one or more fields in the fact table; and/or
■ another standard measure or measures from the set of measures, if applicable; and/or
■ another calculated measure or measures from the set of measures, if applicable;
a calculation of a calculated measure being characterized by a multidimensional expression specific to said calculated measure;
• adding to the cube generation instruction set a set of API instructions representing at least: o said provision of said dimensions; o said provision of said fact table; o said selection of said dimension-specific sets of dimension tables; o said selection of said level-specific key fields and name fields; o said provision of said measures;
• providing said instruction set to said software application and executing said software application, thereby generating said cube. An OLAP cube is a database model that treats data not as relational tables and columns, but as categorized and summarized information stored in cells. A cube comprises dimensions, measures and a fact table.
A measure is a set of values that are based on a field in the cube's fact table. Measures are usually based on numeric fields, such as an item quantity or a currency amount, but could also be a count of character-based fields. Measures are calculated for all possible combinations of dimension values and are stored in the cube cells. Each cell in a cube is addressed by a set of coordinates specifying a "position" in the cube.
A fact table is a table that contains numerical measures and keys (fields) relating facts to dimension tables.
The execution script is a cornerstone in the present invention. The execution script is built as the steps above are carried out. The steps add predefined execution stubs to the execution script. The most widely used database manipulation language is SQL, "Structured Query Language", and in this case the execution stubs will be SQL language stubs. We point out that the principles of the invention do not rely on a specific query language.
The initialization of the execution script can mean to simply provide an empty text file. Execution stubs are added to the execution script by amending them to the file. Another example involves building the execution script in a computer memory.
Execution stubs are partly predefined, which is another cornerstone in the invention. A user needs not have any knowledge of SQL or other query language to use the method according to the first aspect to build a data warehouse. When he makes selections and provides parameters, the predefined SQL segments (execution stubs) are adjusted to reflect the selections and the parameters he provides.
Examples will be provided below. These examples are further described in "Detailed description of selected embodiments".
According to the invention, data sources form the basis for OLAP cubes. A data source holds one or more tables, and a table holds a number of rows, each row consisting of at least one field. Once the data sources have been provided, table and field information for each of the data sources may be extracted for instance using appropriate data dictionaries. This information is then presented to the user, which may be done in several ways. Preferably, information is presented to the user via a user-friendly interface, such as a graphical user interface (GUI). A GUI also provides a practical way of allowing the user to provide the required input.
Fig. 4 illustrates an example where a data source "Sales" has been provided and translated using a data dictionary, and finally is presented to the user by way of a graphical user interface. Tables "Country", "Customer" and "CustomerGroup" are visible in the illustration. The figure also illustrates that the table "Customer" contains the fields "Customerld", "CustomerName", "Country" and "Groupld". The checkboxes in the graphical user interface allow the user to easily select or deselect specific tables and table fields.
Fig. 4 illustrates a specific data selection. In Fig. 4, all three tables and their fields have arbitrarily been selected. It is of course possible to select fewer tables, and fields may also be left out (not be selected) if they are not needed in the OLAP cube. Using a GUI, making or changing the data selection is easily done.
Having provided the data selection, a new table, a "valid table", is created for each table in the data selection. Optionally, the definition of the table can constrain the contents of the fields, for instance using the NULL or NOT NULL statements in a CREATE TABLE function (using SQL terminology). It may be desirable to add extra fields when the valid tables are created. Extra fields can be used for comments. In any automated process, it is desirable to carry along documentation information. In the automated data warehousing process according the first aspect of the present invention, it may for instance be useful to store, in the valid table, information about the data source on which it is based. The time of row insertions into the tables is another useful parameter that one might include.
According to the invention, the execution stub is structurally predefined, but also adaptable to the data source and to the user's data selection. This will be further illustrated in "Detailed description of selected embodiments".
In the present invention, OLAP cubes are built from the valid tables in a data warehouse, valid tables comprising validated ("scrubbed") data from tables in the data sources. Data from the data selection must be validated before it is entered into the data warehouse. "Data scrubbing" is the process by which data from a data source is validated and invalid data from the data source is identified, removed or corrected. Despite the efforts made to ensure the data quality in the data sources, these efforts are not always adequate for meeting the requirements for building a valid business intelligence solution. Data is validated on a table-by-table, row-by-row basis. For each table in the data selection (the tables and fields selected by the user), a set of validation rules are provided. These rules are responsible for the validation of the data. A useful rule might be that a field may not be NULL (or empty, if the field is a text field). An empty "Customerld" is likely an error in the data provided to the database, and it is desirable to be aware of this error before data is entered into the data warehouse. Another useful rule might be that a field may not exceed a certain value. According to the invention, the validation rules are enforced by adding to the execution script execution stubs representing the desired validation criteria. Statements taking care of insertion of rows that comply with the table- specific set of validation rules are also added.
Selecting a specific validation rule type causes a specifically designed execution stub to be employed. In "Detailed description of selected embodiments", a code example illustrates what a validation execution stub may look like.
The user may want to create a number data warehouse views. Views combine data from multiple tables into one logical object, which can be used as fact table for cubes. This solves a problem on SQL Server 2000, namely there can be only one fact table per cube.
A view is created by selecting a table on which the view shall be based and then selecting a number of view fields from the table which shall be used in the view. After forming the selection and providing a name for the view, an execution stub representing the creation of the view having the provided name and containing the selected fields is added to the execution script. The user may also selected more than one table for use in the view. Fields from across tables are logically combined using a join statement in the execution stub.
After forming the execution script as described, that execution script is executed, whereby the data warehouse is created.
The selections made thus far by the user continue to be used during the OLAP cube generation. Since the data warehouse is custom-built, the OLAP cube building has a natural outset. Rather than representing all information from the data sources to the user, only the data warehouse data is represented. Thus, the user needs not select a fact table for the cube from all tables in the data sources, many of which are likely completely unrelated and not even useful. Instead, he chooses from only the valid tables in the data warehouse or from the created views, if any. Clearly this is highly advantageous compared to separately building a data warehouse, and then subsequently providing the data selection information once again during the subsequent cube generation. Next, the cube's dimensions are provided. Each dimension is based on a set of dimension tables, and thus for each dimension, a set of dimension tables must be provided. Again, not only tables from the data warehouse but also created views may be selected, and thus both valid tables and views can serve as dimension tables.
Levels are chosen for each dimension based on the selected dimension tables. For each level added to the dimension, a key field and a name field must be indicated.
Having completely defined the cube's dimensions, measures can be added. The measures are based on the fact table. There are three types of measures:
• standard measures;
• derived measured; and • calculated measures.
A standard measure is simply a field the fact table. A derived measure can be based on more than one field, and it may involve a calculation using the fields on which it is based. However, a derived measure is calculated before aggregation in the cube. A calculated measure may depend on one or more fields in the fact table, on standard measures, and on other calculated measures. The calculated measure is characterized by a multidimensional expression.
According to the method, the actual cube generation is performed via an Application Programming Interface (API) of a cube generation software application, for instance Analysis Management Objects (AMO); Decision Support Objects (DSO); or Oracle OLAP API. The selections made previously by the user are translated to "API" language, thereby obtaining an instruction set. The instruction may then be provided to the software application, which may then proceed to generate the cube. Depending on the application, it might be desirable to leave out the step of providing said instruction set to said software application and executing said software application.
It may be advantageous to also create an error table for each table in the data selection. Rows that do not comply with the validation rules may be inserted into the error table for easy overview by the data warehouse builder. In embodiment using error tables, execution stub representing the creation of the error tables are added to the execution script. Furthermore, execution stubs representing insertion of non-complying rows into the error tables are also added. A table row that does not comply with the table-specific set of validation rules is inserted into the table-specific error table, and erroneous rows may then easily be reviewed along with the validation rules that they did not comply with. An execution stub that handles this is added to the execution script.
It should be obvious that the physical location of the data sources is not important in terms of carrying out the building of the data warehouse. It should also be obvious that the data processing may take place anywhere, as long as the data to be processed is accessible.
Data source servers are often running mission critical applications. Extraction of data from data sources servers should leave as small a footprint as possible in order to minimize the load on those servers. A staging database may advantageously be used for holding relevant data from data sources. The staging database may then act as a source for cubes. Using a staging database further has the advantage that data can be extracted selectively from the data sources using a set of selection rules. As an example, it may be desirable to extract only data that corresponding to sales postdating 2004, rather than extracting all data dating back to the earliest recorded data in the data source table.
In embodiments that employ a staging database, methods of building the data warehouse further comprise the steps of:
• generating a staging database for holding staging data from said data sources, staging data being data that correspond to said data selection;
• for each specific table in the data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific raw table in the staging database, said raw table being a table adapted to hold rows from said specific table;
• copying, after the step of executing the execution script, each table in the data selection from its data source to said table's corresponding raw table in the staging database, optionally applying a set of table-specific selection rules during said copying of said each table, the table-specific selection rules causing said copying to transfer only a part of said each table that complies with said table-specific set of selection rules.
In this case the table-specific raw tables, valid tables, and error tables (if used) are created in the staging database.
Selection rules are table-by-table, field-by-field specific. In a typical implementation, the table-specific selection rules are automatically built into table-specific DTS packages used for copying data from the data source to the corresponding raw tables in the staging database. The selection would then be realized by adding WHERE statements (in SQL terminology) representing the table-specific selection rules. For automation purposes, the user preferably provides the set of selection rules via a user-friendly interface rather than by providing SQL code (such as actual WHERE statements) representing the table-specific selection rules.
All transformation, validation etc. may then be done on the staging database after the source data has been successfully copied to it.
It may be desirable to provide further processing rules, again in an automated fashion. For instance, the user may provide, for each field of each table in the data selection, a set of transformation rules to be applied to the data as it is transferred from the data sources to the data warehouse. The user may for instance have asked that all customer names in a specific table in the data selection be transferred in all upper case. The set of transformation rules are preferably provided via a user-friendly interface, such as a graphical user interface.
Employing a set of default rules may also be desirable, for instance for correcting systematic errors or for providing some sense to an empty field. An empty field may for instance be given a default value during the transfer. The user may indicate that if the field "CustomerName" in the "Customer" table is empty, it shall be given the value "(name not provided)". This provides more useful information than does an empty field.
In some embodiments of the method according to the first aspect, relevant personnel will be notified in case a warning or error is identified. An email detailing the problem or an SMS (short message system) message, a fax, a page, an entry in an operating system event log; or some other type of notification with a more or less detailed description of the warning or error may be dispatched.
It is desirable to save all information relevant information pertaining to the data warehouse in a project file. The user can then return to the project at a later point and determine the structure of the data warehouse. This information is essentially a documentation of the data warehouse. Preferably, the project file is human readable. This makes it easier for a user to study the data warehouse structure.
A project file may easily be distributed to users elsewhere. Company business staff may for instance design business intelligence processes centrally, for instance by creating execution scripts relating to local business conditions. Regional offices can then directly implement the scripts and thereby handle local business intelligence themselves, but in a company-consistent fashion, what the company would consider "best practices".
In a second aspect of the invention, a method is provided for allowing a user to generate a data warehouse holding validated information based on one or more data sources comprising a set of source tables having a set of source fields. The circumstances and variations described in relation to the first aspect above apply equally to the second aspect.
In a third aspect of the invention, software that implements the methods according to the invention is provided. Such software is capable of dispensing the execution stubs described above. Depending on a user's choices, the software will provide appropriately adapted execution stubs fitting the specific tasks. As described above, choosing a table and one or more of its fields causes a set of execution stubs to be added to the execution scripts, such as a valid table creation stub and a validation execution stub. The software may be capable of providing execution stubs in more than one query language. It may be capable of loading data from several types of data sources, such as vendor-specific customer relationship management systems; human resource management systems; enterprise resource planning systems; database systems, such as Oracle, SQL Server, and Microsoft Excel. Information about for instance tables, views, fields, data types and constraints is extracted from data sources using vendor specific schema information ("data dictionaries"). This information may for instance be extracted by connecting directly to the data sources using either managed .Net providers or legacy providers.
Methods according to the invention may also or alternatively be implemented directly in processing hardware, such as an application-specific integrated circuit, or some parts may be provided by software running on a hardware processor, and other parts may be implemented on an application-specific integrated circuit.
DETAILED DESCRIPTION OF SELECTED EMBODIMENTS
The following example illustrates certain aspects of the invention.
In a typical practical scenario, tables are located in more than on data source. For example, a company's product management division (handling for instance tables "Products", "ProductCategory" and "ProductGroup") may for instance operate two SQL servers and an Oracle database. On the other hand, the logistics division (handling for instance tables "OrderLines", "Orders" and "Customers") may operate three Oracle servers, and the occasional Microsoft Excel database. Figs. 5 and 6 illustrate interfaces for providing an Oracle source and an Excel source, respectively.
Fig. 7 illustrates a typical system process of forming a data warehouse and OLAP cubes. A number of data sources, such as an Enterprise Resource System source ("ERP" in Fig. 7), A Microsoft Excel source ("Excel" in Fig. 7), a Customer Relationship Management source ("CRM" in Fig. 7) and a Human Resource source ("HR" in Fig. 7) may form the basis for the data warehouse ("Data Warehouse" in Fig. 7) and OLAP cubes also illustrated in Fig. 7. Some methods according to the present invention allow a user to extract and validate data from the data sources and generate a data warehouse based thereon. Other methods according to the invention furthermore allow a user to first generate the data warehouse and then generate OLAP cubes based on the data warehouse.
BUILDING A DATA WAREHOUSE AND AN OLAP CUBE
In this example, the invention is implemented to use a graphical user interface.
In the present example, all tables come from one data source, "Sales". Fig. 9 illustrates the structure of the sample data source "Sales", which may for instance be an Oracle database. Fig. 10 to Fig. 12 illustrate the definition of the tables "Customer", "OrderLines" and "History_OrderLines". The sample database contains a simple implementation of a sales order application.
It might be useful to work inside the framework of a "project". A project may for instance be a file. A specific project is capable of holding all relevant information that relate to execution of a corresponding specific method in accordance with the invention. We might therefore created a project first. A project is defined by providing a "Project name" and a "File path". We choose "Sample project" as project name as illustrated in Fig. 13. The "File path" describes the desired location of the project. We name the project "Sample. dmp". dmp" might stand for "data manager project", Data Manager being the name of a software package implementing one or more methods according to the invention. The file is located in the root folder C:\.
Fig. 8 illustrates a typical flow diagram for a method in accordance with the invention. The process spans from initializing the execution script, extracting data from data sources ("DS" in Fig. 8), generating a data warehouse ("DW" in Fig. 8), all the way to the generation of an OLAP cube using an OLAP cube generation software. A method according to the invention for generating a data warehouse of course comprises only a subset of the illustrated steps.
In the present implementation, the method creates not only a valid table and an error table for each source table, but also a "raw" table. The raw table is a table on a staging database to which data is transferred from the data source. Moving data from the data source to a staging database reduces the load on the data source servers. Fig. 14 illustrates the creation of a staging database.
Fig. 15 illustrates the tables and fields of a data source named "Sales". The user has selected all fields and tables from the data source. Having selected these fields, execution stubs are added to the project's execution script.
Fig. 16 illustrates the raw table "Sales_OrderLines_R" and its fields created on the staging database resulting from the user having selected the corresponding fields under "OrderLines" in the data source representation in Fig. 15. The raw table simply holds all data from the "OrderLines" table on the data source, whether they are valid or not. Data is simply transferred from the data source ("Sales") to this table on the staging database. The same process takes place for the other tables in the data selection in Fig. 15.
The execution stub added to the execution script to create the raw table "Sales_OrderLines_R" in Fig. 16 is:
CREATE TABLE Sales_OrderLines_R(
Orderld int NULL,
LineNum int NULL, Product int NULL,
Quantity decimal(18,3) NULL,
SalesAmount decimal(18,3) NULL,
CostAmount decimal(18,3) NULL,
Date datetime NULL, DW_SourceCode varchar(15) NULL,
DWJlϊmeStamp smalldatetime NOT NULL default(getdateQ) Clearly, the execution stub represents the selections made by the user. However, its structure is predefined, in accordance with the invention. This is one of very important advantageous of the present invention.
As described previously, it may for instance be useful to store extra information in the created tables. Two fields, "DW_SourceCode" and "DW_TimeStamp" have been added in the creation of the raw table above for practical purposes. DW_SourceCode" may contain the name of the data source, "Sales" in this example (the "Sales" data source contains the "Customer" table). The field "DW_TimeStamp" may contain the time a row is inserted into the raw table. The field "DW_TimeStamp" is given a default value of getdate().
The execution stub added to the execution script to create the valid table "Sales_Orderl_ines_V" in Fig. 17 is:
CREATE TABLE Sales_OrderLines_V(
Orderld int NOT NULL, LineNum int NOT NULL,
Product int NOT NULL,
Quantity decimal(18,3) NOT NULL,
SalesAmount decimal(18,3) NOT NULL,
CostAmount decimal(18,3) NOT NULL, Date datetime NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) )
Finally, the execution stub
CREATE TABLE Sales_θrderLines_E(
Orderld int NULL, LineNum int NULL,
Product int NULL,
Quantity decimal(18,3) NULL,
SalesAmount decimal(18,3) NULL,
CostAmount decimal(18,3) NULL, Date datetime NULL,
DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) takes care of the creation of the error table "Sales_Orderl_ines_E" in Fig. 18. In the error table above, four extra fields are created: "DW_Severity", "DW_ErrorMessage", "DW_SourceCode", and "DW_TimeStamp". The field "DW_Severity" may be useful for indicating whether insertion of a specific row into the error table is triggered by for instance a "warning" or an "error". Different actions may be taken depending on a severity parameter. Errors and warnings might be defined as:
Error Used for restrictions on the source data which is critical for the data quality and subsequently the quality of the decisions made based on the information in the business intelligence solution.
If the data does not comply with the validation rule, an error flag is raised and an error message generated. The entire row will then only be inserted into the error table.
Warning Used for restrictions on a source data which is potentially erroneous, but not critical for the data quality.
If the data does not comply with the validation rule, a warning flag is raised and a warning message generated. The entire row will be inserted into both the error table and the valid table.
"DW_ErrorMessage" might contain a message relating to the specific warning or error that caused a specific row to be inserted into the error table. In the present example, "DW_SourceCode" and "DW_TimeStamp" have the same meaning described for the valid table.
The valid table in Fig. 17 and the error table in Fig. 18 are created to having the same fields as the raw table, which in turn contains the fields selected by the user, as illustrated in Fig. 15. Again, a valid table and an error table are generated for each of the tables selected by the user. This means that a raw table, a valid table and an error table are created for all the tables in Fig. 9.
To insert rows from the raw table "Sales_Orderl_ines_R" into the corresponding valid table "Sales_Orderlines_V", the following execution stub is added to the execution script: CREATE PROC dbo.usp_Sales_OrderLines_V_Insert
©Orderld int,
@LineNum int, ©Product int,
©Quantity decimal(18,3),
©SalesAmount decimal(18,3),
©CostAmount decimal(18,3),
©Date datetime, @DW_SourceCode varchar(15)
AS INSERT INTO dbo.Sales_OrderLines_V( Orderld,
LineNum,
Product, Quantity,
SalesAmount,
CostAmount,
Date,
DW_SourceCode) VALUESC
©Orderld,
@LineNum,
©Product,
©Quantity, ©SalesAmount,
©CostAmount,
©Date,
@DW_SourceCode)
This execution script also generated using only the data selection information already provided. Thus, again the execution stub represents the selections made by the user, but is structurally predefined. Manual labor is optional, not mandatory.
An execution stub for inserting a row into the error table may look similar, for instance like this:
CREATE PROC dbo.usp_Sales_OrderLines_E_Insert ©Orderld int, ©LineNum int,
©Product int, ©Quantity decimal(18,3), ©SalesAmount decimal(18,3), ©CostAmount decimal(18,3), ©Date datetime,
@DW_SourceCode varchar(15), @DW_Severity varchar(l), @DW_ErrorMessage varchar(lOOO) AS INSERT INTO dbo.Sales_OrderLines_E( Orderld,
LineNum,
Product,
Quantity, SalesAmount,
CostAmount,
Date,
DW_SourceCode,
DW_Severity, DW_ErrorMessage)
VALUES(
©Orderld,
@LineNum,
©Product, ©Quantity,
©SalesAmount,
©CostAmount,
©Date,
@DW_SourceCode, @DW_Severity,
@DW_ErrorMessage)
An execution stub for determining whether a row is valid may have the following structure (sometimes referred to as "data scrubbing"):
CREATE PROCEDURE dbo.usp_Sales_OrderLines_Clean ©Debug bit AS
SET NOCOUNT ON
DECLARE ©Orderld int
DECLARE ©LineNum int
DECLARE ©Product int
DECLARE ©Quantity decimal(18,3) DECLARE ©SalesAmount decimal(18,3)
DECLARE ©CostAmount decimal(18,3)
DECLARE ©Date datetime
DECLARE ©Counter int
DECLARE ©Error int DECLARE ©Warning int
DECLARE @DW_ErrorMessage varchar(lOOO)
DECLARE @DW_WarningMessage varchar(lOOO)
DECLARE @DW_SourceCode varchar(15)
DECLARE @DW_TimeStamp smalldatetime SET ©Counter = 0
DECLARE Sales_OrderLines_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Orderld,
LineNum,
Product, Quantity,
SalesAmount, CostAmount, Date,
DW_SourceCode FROM Sales_OrderLines_R BEGIN TRANSACTION
OPEN Sales_OrderLines_Cursor FETCH NEXT FROM Sales_OrderLines_Cursor INTO ©Orderld, @LineNum, ©Product,
©Quantity, ©SalesAmount, ©CostAmount, ©Date, @DW_SourceCode
WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0
SET @DW_WarningMessage = " SET @DW_ErrorMessage = " IF(@OrderId IS NULL) BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Orderld does not comply with validation rule Not empty' END IF(@LineNum IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', LineNum does not comply with validation rule Not empty' END
IF(@Product IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Product does not comply with validation rule Not empty' END
IF(@Quantity IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Quantity does not comply with validation rule Not empty'
END
IF(@SalesAmount IS NULL) BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', SalesAmount does not comply with validation rule Not empty' END
IF(@CostAmount IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', CostAmount does not comply with validation rule Not empty' END
IF(@Date IS NULL OR ©Date = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Date does not comply with validation rule Not empty' END IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_OrderLines_E_Insert ©Orderld,
@LineNum,
©Product,
©Quantity,
©SalesAmount, ©CostAmount,
©Date,
@DW_SourceCode,
•W,
@DW_WarningMessage lF(@Debug = 1)
BEGIN
PRINT @DW_WarningMessage
END
END
IF(@Error = 1)
BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_OrderLines_E_Insert
©Orderld,
@LineNum,
©Product,
©Quantity, ©SalesAmount,
©CostAmount,
©Date, @DW_SourceCode, 1E1,
@DW_ErrorMessage IF(@Debug = 1) BEGIN
PRINT @DW_ErrorMessage
END
END IF(@Error = 0)
BEGIN
/* Insert into validated table */ exec usp_Sales_OrderLines_V_Insert
©Orderld, @LineNum,
©Product,
©Quantity,
@SalesAmount,
©CostAmount, ©Date,
@DW_SourceCode
END
IF((@Counter % 1000) = 0)
BEGIN SAVE TRANSACTION Sales_OrderLines
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END END
FETCH NEXT FROM Sales_OrderLines_Cursor
INTO ©Orderld,
@LineNum,
©Product, ©Quantity,
©SalesAmount,
©CostAmount,
©Date,
@DW_SourceCode END
CLOSE Sales_OrderLines_Cursor
DEALLOCATE Sales_OrderLines_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_OrderLines_Clean 0
The structure of the execution stub is once again predefined, adjusted according to the data selection made by the user. The set of validation rules provided for the "OrderLines" table from the "Sales" data source are incorporated automatically in accordance with the method. In the example above, for simplicity, we apply the same validation rule for all fields in all tables, namely that they cannot be NULL or empty. In the present example, we do not employ transformation rules or default rules.
If the user has asked that all Product names be transferred in all uppercase, an execution stub such as
SET ©Product = UPPER(@Product)
might be incorporated into the execution stub "CREATE PROCEDURE dbo.usp_saies_orderunes_ciean" illustrated previously. Again, this will be performed automatically, based on the user's indication.
The execution stub
IF(@Product IS NULL OR ©Product = ") BEGIN
SET ©Product = '0' END
might be incorporated into the execution stub "CREATE PROCEDURE dbo.usp_saies_customer_ciean" illustrated previously.
The above execution stub also illustrates a use of the severity rating described previously.
The execution stubs above are provided only for exemplification. The structure of execution stubs is defined by the aspect formulation, not by the examples above. Clearly, the functions may be literally different from those above, but still implement the same functions. The extra fields illustrated above may be left out, others may be inserted; tables can be defined with other NULL/NOT NULL constraints than those shown in the examples; further SELECT statements and/or WHERE statements can be inserted; and so on.
The following table illustrates various validation rules that a user may find useful :
Ty1WS Description
NotEmpty Field value can not be NULL and for text data types, can not be blank/empty
Figure imgf000024_0001
Fig. 19 illustrates a view used for generating a cube name "Sales". The user is allowed to choose only among the valid tables corresponding to the original data selection in Fig. 15.
The view uses o "Sales_OrderLines_V.Product for "Product"; o "Sales_OrderLines_V.Quantity" for "Quantity"; o "Sales_OrderLines_V.SalesAmount" for "SalesAmount"; o "Sales_OrderLines_V.CostAmount" for "CostAmount"; o "Sales_OrderLines_V.Date" for "Date"; o "Sales_Order_V.Costumer" for "Customer"; o "Sales_Order_V.DeliverCountry" for "DeliverCountry"; and o "Sales Order V.Invoiced" for "Invoiced".
The creation of this view is taken care of by an appropriately adapted execution stub added to the execution script. The execution stub will be based on the SQL statement CREATE VIEW. Since we have selected fields from different tables, they must be joined. We have declared that the key "Orderld" is common for the tables "Sales_Order_V" and "Sales_OrderLines_V" (see Fig. 9), and thus the view statement will contain an ON statement that selects for instance "Customer" from "Sales_Order_V" when "Orderld" - key for both tables - coincide. The following execution stub creates the view:
CREATE VIEW dbo.view_Orderlines AS SELECT TOP 100 PERCENT
Sales_θrderLines_V. Product AS [Product],
Sales_θrderLines_V.Quantity AS [Quantity],
Sales_θrderLines_V.SalesAmount AS [SalesAmount],
Sales_θrderLines_V.CostAmount AS [CostAmount], Sales_θrderLines_V.Date AS [Date],
Sales_θrders_V.Customer AS [Customer],
Sales_θrders_V.DeliverCountry AS [DeliverCountry],
Sales_θrders_V.Invoiced AS [Invoiced] FROM Sales_θrderLines_V
INNER JOIN
Sales_θrders_V ON Sales_θrders_V.θrderId = Sales_OrderLines_V.OrderId GO
At the end of this specification, in the section "COMPLETE STAGING SCRIPT", we illustrate an execution script generated according to the method and adapted to carry out the actions described above. There are 9 tables in Fig. 9, and with table definitions, view definition and scrubbing we end up with more than 2000 code lines generated with very little input from the user, and because the execution stubs are predefined, the execution script will always be free of programming errors. 9 tables as in Fig. 9 is not necessarily a large project, and it is clear that the use of predefined query segments is highly efficient in forming both data warehouses comprising validated data and in the process that runs from extraction data from data sources to building OL-AP cubes based on that data.
For completeness, Fig. 20 shows an overview of the DTS packages that copy the selected table tables from the data sources to the staging database.
Fig. 21 illustrates a "Sales" cube having dimensions "Product", "Customer", and "Region". The cube is based on the view in Fig. 19.
The dimension "Product" has levels "Productgroup", "Category", and "Product". The dimension "Customer" has levels "Group" and "Customer". The dimension "Region" has levels "Region" and "Country". The cube is furthermore defined by measure "Quantity sold", "Net amount", "Costs", "Contribution", and "Contribution margin". As Fig. 21 illustrates, all cubes, dimensions, levels and measures are defined via the interface without need for providing SQL code. As elsewhere in the method according to the first aspect, only information in the data selection is available for selection (see Fig. 15), removing the redundancy known from conventional approaches to building OLAP cubes, where the data warehousing and cube building are separated.
Fig. 22 illustrates the definition of a measure, in this case "Quantity sold". The "Field" for this measure is "Quantity" from the fact table (Fig. 19). Furthermore, we have chosen aggregation type "Sum". Fig. 23 illustrates a calculated measure, "Contribution", based on a difference between the measures "Net amount" and "Costs". Fig. 24 illustrates a calculated measure, "Contribution margin", which is defined as the ratio between the measure "Contribution" from Fig. 23 and the measure "Net amount".
When all the cube information has been provided, it is passed on to the API of a cube generation software application and the application has been executed, whereby the cube is generated.
Fig. 26 illustrates what the final cube looks like when presented in an analysis tool such as Microsoft's Analysis Manager from Microsoft Analysis Service.
In some embodiments of the invention, a notification is presented when an error occurs during validation. In the present invention, we use error tables for storing row that do not comply with the validation rules. Below is a definition of the tables from Fig. 9. After the list of tables, we address how such a notification may appear.
Table "Orderlines":
Orderld LineNum Product Quantity SalesAmount CostAmount Date
1000 1 10 1 6784 9045,333 12-01-2006
1000 2 20 1 6179 8238,666 12-01-2006
1000 3 30 1 9846 13128 12-01-2006
1001 1 40 1 7975 10633,333 20-01-2006
1001 2 240 1 299 398,666 20-01-2006
1002 1 240 1 299 398,666 20-01-2006
1002 2 220 1 4599 6132 20-01-2006
1003 1 80 1 28950 38600 20-01-2006
1003 2 90 1 5275 7033,333 20-01-2006
1004 1 100 1 4275 5700 20-01-2006
1005 1 120 1 895,25 1193,666 01-02-2006
1005 2 200 1 5495 7326,666 01-02-2006
1005 3 210 1 1995 2660 01-02-2006 1006 1 220 3 11900 15866,666 01-02-2006
1006 2 230 2 9000 12000 01-02-2006
1007 1 240 20 50890 67853,333 02-02-2006
1008 1 250 1 35000 46666,666 02-02-2006
1008 2 10 6784 9045,333 02-02-2006
1009 1 20 1 6179 8238,666 02-02-2006
1009 2 30 2 19692 26256 02-02-2006
1009 3 50 1 4623 6164 02-02-2006
1010 1 60 1 9865 13153,333 24-02-2006
1011 1 100 2 9000 12000 24-02-2006
1011 2 210 1 2995 3993,333 24-02-2006
1012 1 240 10 25445 33926,666 27-02-2006
1012 2 250 1 30000 40000 27-02-2006
1012 3 10 1 6784 9045,333 27-02-2006
Table "History_Orderlines": lerld LineNum Product Quantity SalesAmount CostAmount Date
0900 1 10 1 6784 9045,333 12-01-2005
0900 2 20 1 6179 8238,666 12-01-2005
0900 3 30 1 9846 13128 12-01-2005
0901 1 40 1 7975 10633,333 20-01-2005
0901 2 240 1 299 398,666 20-01-2005
0902 1 240 1 299 398,666 20-01-2005
0902 2 220 1 4599 6132 20-01-2005
0903 1 80 1 28950 38600 20-01-2005
0903 2 90 1 5275 7033,333 20-01-2005
0904 1 100 1 4275 5700 20-01-2005
0905 1 120 1 895,25 1193,666 01-02-2005
0905 2 200 1 5495 7326,666 01-02-2005
0905 3 210 1 1995 2660 01-02-2005
0906 1 220 3 11900 15866,666 01-02-2005
0906 2 230 2 9000 12000 01-02-2005
0907 1 240 20 50890 67853,333 02-02-2005
0908 1 250 1 35000 46666,666 02-02-2005
0908 2 10 6784 9045,333 02-02-2005
0909 1 20 1 6179 8238,666 02-02-2005
0909 2 30 2 19692 26256 02-02-2005
0909 3 50 1 4623 6164 02-02-2005 0910 1 60 1 9865 13153,333 24-02-2005
0911 1 100 2 9000 12000 24-02-2005
0911 2 210 1 2995 3993,333 24-02-2005
0912 1 240 10 25445 33926,666 27-02-2005
0912 2 250 1 30000 40000 27-02-2005
0912 3 10 1 6784 9045,333 27-02-2005
Table "Orders": Orderld Customer Invoiced DeliverCountry
1000 1 1 DK
1001 1 1 DK
1002 2 1 DE
1003 2 1 DE
1004 3 1 NZ
1005 3 1 NZ
1006 4 1 IT
1007 4 1 IT
1008 5 1 US
1009 5 1 US
1010 6 0 AU
1011 6 0 AU
0900 1 1 DK
0901 1 1 DK
0902 2 1 DE
0903 2 1 DE
0904 3 1 NZ
0905 3 1 NZ
0906 4 1 IT
0907 4 1 IT
0908 5 1 US
0909 5 1 US
0910 6 0 AU
0911 6 0 AU
Table "Product":
Productld ProductName ProductGroup Category
10 Lenovo Thinkpad R50e 2 20
20 Lenovo Thinkpad T43 2 20 30 HP Business Notebook Nx6110 1 20
40 HP Compaq Business Notebook Nx6125 1 20
50 Thinkcentre A50 2 30
60 Lenovo Thinkcentre S51 2 30
70 HP Compaq Business Desktop Dc5100 1 30
80 HP Workstation Xw8200 1 30
90 Lenovo Thinkvision Ll 51 (Business black) 2 10
100 Lenovo Thinkvision Ll 51 (Silver) 2 10
110 Compaq TFT 5600 RKM 1 10
120 HP 90 (White) 1 10
130 IBM 670 watt 2 40
140 SDRAM PC133 1x256 2 40
150 SDRAM PC133 1x512 2 40
200 Microsoft Windows 2003 3 100
210 Microsoft Windows XP Pro 3 100
220 Microsoft Office XP Pro 3 200
230 Microsoft Visio 2003 Pro 3 200
240 Trend Micro Officescan 3 300
250 Symantec Antivirus Enterprise Edition 3 300
Table "ProductCategory":
Categoryld Name
10 Monitors
20 Laptops
30 Desktops
40 Parts
100 Operation Systems
200 Office applications
300 Antivirus
Table "ProductGroup": Groupld Name
1 Hewlett Packard
2 IBM
3 Software
Table "Customer": Customerld CustomerName Country Group
1 John Doe DK 1
2 Jane Doe DE 1
3 Large Corp Inc NZ 2
4 Small Corp Inc IT 2
5 International Traders us 3
6 First Class Imports AU 3
Table "CustomerGroup":
Groupld Name
1 Web
2 Retail
3 Distributers
Table "Country":
Countryld CountryName Region
AU Australia Pacific
BR Brazil Americas
CA Canada Americas
DE Germany EMEA
DK Denmark EMEA
HK Hong Kong EMEA
IT Italy EMEA
NZ New Zealand Pacific
US United States of America Americas
ZA South Africa EMEA
Table "Region": Region Id
Americas
EMEA
Pacific
As mentioned in the beginning of the example in this section, we apply, for simplicity, the same validation rule for all fields in all tables, namely that they cannot be NULL or empty. The table "OrderLines" has an empty field, which violates the validation rule for that field in the "OrderLines" table. Hence, in accordance with methods of the invention, the row having the empty field is inserted into the error table. Fig. 25 illustrates an example of how such a notification may be presented to the user.
Along with the insertion of the row into the error table, another error indication may also be dispatched, for instance in the form of an email to relevant personnel. Preferably, the data warehousing is performed completely automatically once the execution script has been generated. Usually, the data warehousing takes place automatically, but in case a data source contains a field that violates the validation rules, this error must be communicated to the relevant personnel.
The examples provide illustrations of the principles behind the invention. The illustrations shall not be construed as limiting the scope defined by the claims.
COMPLETE STAGING SCRIPT
This section illustrates the complete execution script generated for the example described above for generating a data warehouse.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Country_R]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Country_R]
CREATE TABLE Sales_Country_R(
Countryld varchar(50) NULL,
CountryName varchar(50) NULL,
Region varchar(50) NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Country_V]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Country_V]
CREATE TABLE Sales_Country_V( Countryld varchar(50) NOT NULL,
CountryName varchar(50) NOT NULL, Region varchar(50) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Country_E]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Country_E]
CREATE TABLE Sales_Country_E(
Countryld varchar(50) NULL,
CountryName varchar(50) NULL, Region varchar(50) NULL,
DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Customer_R]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Customer_R] CREATE TABLE Sales_Customer_R( Customerld int NULL, CustomerName varchar(50) NULL, Country varchar(50) NULL, Groupld int NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Customer_V]') and OBJECTPROPERTY(id,
N'lsUserTable1) = 1) drop table [dbo].[Sales_Customer_V] CREATE TABLE Sales_Customer_V( Customerld int NOT NULL, CustomerName varchar(50) NOT NULL, Country varchar(50) NOT NULL, Groupld int NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Customer_E]') and OBJECTPROPERTY(id, N'lsUserTable1) = 1) drop table [dbo].[Sales_Customer_E]
CREATE TABLE Sales_Customer_E( Customerld int NULL,
CustomerName varchar(50) NULL,
Country varchar(50) NULL,
Groupld int NULL,
DW_Severity varchar(l) NOT NULL, DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) )
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_CustomerGroup_R]') and OBJECTPROPERTY(id,
N'lsUserTable1) = 1) drop table [dbo].[Sales_CustomerGroup_R] CREATE TABLE Sales_CustomerGroup_R(
Groupld int NULL,
Name varchar(50) NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_CustomerGroup_V]') and OBJECTPROPERTY(id,
N'IsUserTable1) = 1) drop table [dbo].[Sales_CustomerGroup_V] CREATE TABLE Sales_CustomerGroup_V( Groupld int NOT NULL, Name varchar(50) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_CustomerGroup_E]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_CustomerGroup_E]
CREATE TABLE Sales_CustomerGroup_E( Groupld int NULL, Name varchar(50) NULL, DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_OrderLines_R]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_OrderLines_R]
CREATE TABLE Sales_OrderLines_R(
Orderld int NULL,
LineNum int NULL,
Product int NULL, Quantity decimal(18,3) NULL,
SalesAmount decimal(18,3) NULL,
CostAmount decimal(18,3) NULL,
Date datetime NULL,
DW_SourceCode varchar(15) NULL, DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_OrderLines_V]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_OrderLines_V]
CREATE TABLE Sales_OrderLines_V(
Orderld int NOT NULL, LineNum int NOT NULL,
Product int NOT NULL, Quantity decimal(18,3) NOT NULL, SalesAmount decimal(18,3) NOT NULL,
CostAmount decimal(18,3) NOT NULL,
Date datetime NOT NULL,
DW_SourceCode varchar(15) NULL, DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_OrderLines_E]1) and OBJECTPROPERTYfld, N'IsUserTable1) = 1) drop table [dbo].[Sales_OrderLines_E]
CREATE TABLE Sales_OrderLines_E(
Orderld int NULL, LineNum int NULL,
Product int NULL,
Quantity decimal(18,3) NULL,
SalesAmount decimal(18,3) NULL,
CostAmount decimal(18,3) NULL, Date datetime NULL,
DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Orders_R]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Orders_R]
CREATE TABLE Sales_Orders_R( Orderld int NULL, Customer int NULL, Invoiced bit NULL,
DeliverCountry varchar(50) NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Orders_V]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Orders_V]
CREATE TABLE Sales_Orders_V(
Orderld int NOT NULL,
Customer int NOT NULL,
Invoiced bit NOT NULL, DeliverCountry varchar(50) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateQ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Orders_E]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Orders_E]
CREATE TABLE Sales_Orders_E(
Orderld int NULL, Customer int NULL,
Invoiced bit NULL,
DeliverCountry varchar(50) NULL,
DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Product_R]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Product_R]
CREATE TABLE Sales_Product_R( Productld int NULL,
ProductName varchar(50) NULL, ProductGroup int NULL, Category int NULL, DW_SourceCode varchar(15) NULL, DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Product_V]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Product_V]
CREATE TABLE Sales_Product_V(
Productld int NOT NULL, ProductName varchar(50) NOT NULL,
ProductGroup int NOT NULL, Category int NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Product_E]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Product_E]
CREATE TABLE Sales_Product_E( Productld int NULL, ProductName varchar(50) NULL, ProductGroup int NULL, Category int NULL, DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_ProductCategory_R]') and OBJECTPROPERTY(id, N'lsUserTable1) = 1) drop table [dbo].[Sales_ProductCategory_R]
CREATE TABLE Sales_ProductCategory_R(
Categoryld int NULL,
Name varchar(50) NULL,
DW_SourceCode varchar(15) NULL, DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_ProductCategory_V]1) and OBJECTPROPERTY(id, N'lsUserTable1) = 1) drop table [dbo].[Sales_ProductCategory_V]
CREATE TABLE Sales_ProductCategory_V(
Categoryld int NOT NULL, Name varchar(50) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) )
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_ProductCategory_E]') and OBJECTPROPERTY(id,
N'lsUserTable1) = 1) drop table [dbo].[Sales_ProductCategory_E] CREATE TABLE Sales_ProductCategory_E( Categoryld int NULL, Name varchar(50) NULL, DW_Severity varchar(l) NOT NULL, DW_ErrorMessage varchar(lOOO) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_ProductGroup_R]') and OBJECTPROPERTY(id, N'lsUserTable1) = 1) drop table [dbo].[Sales_ProductGroup_R] CREATE TABLE Sales_ProductGroup_R(
Groupld int NULL,
Name varchar(50) NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = ObJeCtJd(N1CdOo]-[SaIeS-PrOdUCtGrOUp-V]1) and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_ProductGroup_V]
CREATE TABLE Sales_ProductGroup_V( Groupld int NOT NULL,
Name varchar(50) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_ProductGroup_E]1) and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_ProductGroup_E]
CREATE TABLE Sales_ProductGroup_E(
Groupld int NULL,
Name varchar(50) NULL,
DW_Severity varchar(l) NOT NULL, DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) )
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Region_R]') and OBJECTPROPERTY(id,
N'IsUserTable1) = 1) drop table [dbo].[Sales_Region_R] CREATE TABLE Sales_Region_R(
Regionld varchar(50) NULL,
DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdateθ) ) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Region_V]') and OBJECTPROPERTY(id, N'IsUserTable1) = 1) drop table [dbo].[Sales_Region_V]
CREATE TABLE Sales_Region_V(
Regionld varchar(50) NOT NULL, DW_SourceCode varchar(15) NULL,
DW_TimeStamp smalldatetime NOT NULL default(getdate()) )
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sales_Region_E]') and OBJECTPROPERTY(id,
N'IsUserTable1) = 1) drop table [dbo].[Sales_Region_E] CREATE TABLE Sales_Region_E(
Regionld varchar(50) NULL,
DW_Severity varchar(l) NOT NULL,
DW_ErrorMessage varchar(lOOO) NOT NULL,
DW_SourceCode varchar(15) NULL, DW_TimeStamp smalldatetime NOT NULL default(getdateθ)
) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Country_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Country_V_Insert]
GO
CREATE PROC dbo.usp_Sales_Country_V_Insert ©Countryld varchar(50), @CountryName varchar(50), ©Region varchar(50), @DW_SourceCode varchar(15)
AS
INSERT INTO dbo.Sales_Country_V( Countryld, CountryName, Region, DW_SourceCode)
VALUES(
©Countryld, ©CountryName, ©Region, @DW_SourceCode)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Country_E_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Country_E_Insert]
GO CREATE PROC dbo.usp_Sales_Country_E_Insert ©Countryld varchar(50), ©CountryName varchar(50), ©Region varchar(50),
@DW_SourceCode varchar(15),
@DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS
INSERT INTO dbo.Sales_Country_E( Countryld,
CountryName,
Region,
DW_SourceCode, DW_Severity,
DW_ErrorMessage) VALUES(
©Countryld,
©CountryName, ©Region,
@DW_SourceCode,
@DW_Severity,
@DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Country_Clean]') and
OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Country_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_Country_Clean ©Debug bit AS
SET NOCOUNT ON
DECLARE ©Countryld varchar(50)
DECLARE ©CountryName varchar(50)
DECLARE ©Region varchar(50) DECLARE ©Counter int
DECLARE ©Error int
DECLARE ©Warning int
DECLARE @DW_ErrorMessage varchar(lOOO)
DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15)
DECLARE @DW_TimeStamp smalldatetime SET ©Counter = 0
DECLARE Sales_Country_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Countryld, CountryName,
Region,
DW_SourceCode
FROM Sales_Country_R BEGIN TRANSACTION OPEN Sales_Country_Cursor
FETCH NEXT FROM Sales_Country_Cursor INTO ©Countryld, @CountrγName, ©Region, @DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = "
IF(@CountryId IS NULL OR ©Countryld = ") BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Countryld does not comply with validation rule Not empty' END
IF(@CountryName IS NULL OR ©CountryName = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', CountryName does not comply with validation rule Not empty' END IF(@Region IS NULL OR ©Region = ")
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Region does not comply with validation rule Not empty' END
IF(@Warning = 1)
BEGIN
/* Insert into error table */ SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_Country_E_Insert ©Countryld,
©CountryName,
©Region, @DW_SourceCode,
•W,
@DW_WarningMessage
IF(@Debug = 1)
BEGIN PRINT @DW_WarningMessage
END
END
IF(@Error = 1) BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_Country_E_Insert
@CountrγId, @CountrγName, ©Region, @DW_SourceCode,
1E',
@DW_ErrorMessage IF(@Debug = 1) BEGIN PRINT @DW_ErrorMessage
END END
IF(@Error = 0) BEGIN
/* Insert into validated table */ exec usp_Sales_Country_V_Insert @CountrγId,
©CountryName, ©Region,
@DW_SourceCode
END
IF((@Counter % 1000) = 0)
BEGIN SAVE TRANSACTION Sales_Country
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END END
FETCH NEXT FROM Sales_Country_Cursor
INTO @CountrγId,
@CountrγName,
©Region, @DW_SourceCode
END
CLOSE Sales_Country_Cursor
DEALLOCATE Sales_Country_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_Country_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Customer_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Sales_Customer_V_Insert]
GO
CREATE PROC dbo.usp_Sales_Customer_V_Insert @CustomerId int,
@CustomerName varchar(50),
©Country varchar(50),
@GroupId int, @DW_SourceCode varchar(15)
AS INSERT INTO dbo.Sales_Customer_V( Customerld,
CustomerName,
Country, Groupld,
DW_SourceCode) VALUESC
©Customerld,
©CustomerName, ©Country,
©Groupld,
@DW_SourceCode) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Customer_E_Insert]') and OBJECTPROPERTYfld, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Customer_E_Insert]
GO
CREATE PROC dbo.usp_Sales_Customer_E_Insert
©Customerld int,
©CustomerName varchar(50), ©Country varchar(50),
©Groupld int,
@DW_SourceCode varchar(15),
@DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS
INSERT INTO dbo.Sales_Customer_E( Customerld,
CustomerName,
Country,
Groupld, DW_SourceCode,
DW_Severity,
DW_ErrorMessage) VALUESC
©Customerld, ©CustomerName,
©Country,
©Groupld,
@DW_SourceCode,
@DW_Severity, @DW_ErrorMessage)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Customer_Clean]1) and
OBJECTPROPERTYfld, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Customer_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_Customer_Clean ©Debug bit AS
SET NOCOUNT ON
DECLARE @CustomerId int DECLARE ©CustomerName varchar(50) DECLARE ©Country varchar(50) DECLARE ©Groupld int
DECLARE ©Counter int DECLARE ©Error int DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO)
DECLARE @DW_SourceCode varchar(15) DECLARE @DW_TimeStamp smalldatetime SET ©Counter = O
DECLARE Sales_Customer_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Customerld,
CustomerName, Country, Groupld, DW_SourceCode FROM Sales_Customer_R
BEGIN TRANSACTION OPEN Sales_Customer_Cursor FETCH NEXT FROM Sales_Customer_Cursor INTO ©Customerld, ©CustomerName,
©Country, ©Groupld, @DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = "
IF(@CustomerId IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Customerld does not comply with validation rule Not empty' END IF(@CustomerName IS NULL OR ©CustomerName = ") BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', CustomerName does not comply with validation rule Not empty' END
IF(@Country IS NULL OR ©Country = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Country does not comply with validation rule Not empty' END IF(@GroupId IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Groupld does not comply with validation rule Not empty' END
IF(@Warning = 1)
BEGIN
/* Insert into error table */ SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_Customer_E_Insert ©Customerld,
©CustomerName,
©Country, ©Groupld,
@DW_SourceCode,
•W,
@DW_WarningMessage
IF(@Debug = 1) BEGIN
PRINT @DW_WarningMessage
END
END IF(@Error = 1)
BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_Customer_E_Insert ©Customerld,
©CustomerName,
©Country,
©Groupld,
@DW_SourceCode, Ε',
@DW_ErrorMessage
IF(@Debug = 1) BEGIN
PRINT @DW_ErrorMessage
END
END
IF(@Error = 0)
BEGIN
/* Insert into validated table */ exec usp_Sales_Customer_V_Insert ©Customerld,
@CustomerName,
©Country,
@GroupId,
@DW_SourceCode END
IF((@Counter % 1000) = 0)
BEGIN
SAVE TRANSACTION Sales_Customer
IF(@Debug = 1) BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END
END
FETCH NEXT FROM Sales_Customer_Cursor INTO ©Customerld,
@CustomerName,
©Country,
@GroupId,
@DW_SourceCode END
CLOSE Sales_Customer_Cursor
DEALLOCATE Sales_Customer_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_Customer_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_CustomerGroup_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_CustomerGroup_V_Insert]
GO
CREATE PROC dbo.usp_Sales_CustomerGroup_V_Insert @GroupId int, ©Name varchar(50), @DW_SourceCode varchar(15) AS
INSERT INTO dbo.Sales_CustomerGroup_V( Groupld,
Name, DW_SourceCode) VALUES(
©Groupld,
@Name, @DW_SourceCode)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_CustomerGroup_E_Insert]') and OBJECTPROPERTYfld, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_CustomerGroup_E_Insert]
GO CREATE PROC dbo.usp_Sales_CustomerGroup_E_Insert @GroupId int, ©Name varchar(50), @DW_SourceCode varchar(15), @DW_Severity varchar(l), @DW_ErrorMessage varchar(lOOO)
AS
INSERT INTO dbo.Sales_CustomerGroup_E( Groupld,
Name,
DW_SourceCode, DW_Severity,
DW_ErrorMessage) VALUES(
©Groupld, ©Name, @DW_SourceCode,
@DW_Severity, @DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_CustomerGroup_Clean]') and OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_CustomerGroup_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_CustomerGroup_Clean ©Debug bit AS
SET NOCOUNT ON
DECLARE ©Groupld int
DECLARE ©Name varchar(50)
DECLARE ©Counter int
DECLARE ©Error int DECLARE ©Warning int
DECLARE @DW_ErrorMessage varchar(lOOO)
DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15) DECLARE @DW_TimeStamp smalldatetime SET ©Counter = 0
DECLARE Sales_CustomerGroup_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Groupld, Name,
DW_SourceCode
FROM Sales_CustomerGroup_R BEGIN TRANSACTION OPEN Sales_CustomerGroup_Cursor
FETCH NEXT FROM Sales_CustomerGroup_Cursor INTO ©Groupld, @Name,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = "
SET @DW_ErrorMessage = " IF(@GroupId IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Groupld does not comply with validation rule Not empty' END
IF(@Name IS NULL OR ©Name = ") BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Name does not comply with validation rule Not empty' END
IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_CustomerGroup_E_Insert
©Groupld,
@Name,
@DW_SourceCode,
•W, @DW_WarningMessage
IF(@Debug = 1)
BEGIN
PRINT @DW_WarningMessage
END END
IF(@Error = 1) BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_CustomerGroup_E_Insert ©Groupld,
@Name,
@DW_SourceCode, 1E1,
@DW_ErrorMessage IF(@Debug = 1)
BEGIN
PRINT @DW_ErrorMessage END END
IF(@Error = 0)
BEGIN
/* Insert into validated table */ exec usp_Sales_CustomerGroup_V_Insert ©Groupld,
@Name,
@DW_SourceCode
END
IF((@Counter % 1000) = 0) BEGIN
SAVE TRANSACTION Sales_CustomerGroup
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter) END
END
FETCH NEXT FROM Sales_CustomerGroup_Cursor
INTO ©Groupld,
©Name, @DW_SourceCode
END
CLOSE Sales_CustomerGroup_Cursor
DEALLOCATE Sales_CustomerGroup_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_CustomerGroup_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_OrderLines_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_OrderLines_V_Insert]
GO
CREATE PROC dbo.usp_Sales_OrderLines_V_Insert ©Orderld int,
@LineNum int,
©Product int,
©Quantity decimal(18,3), ©SalesAmount decimal(18,3),
©CostAmount decimal(18,3),
©Date datetime,
@DW_SourceCode varchar(15) AS INSERT INTO dbo.Sales_OrderLines_V( Orderld,
LineNum,
Product,
Quantity,
SalesAmount, CostAmount,
Date,
DW_SourceCode) VALUES(
©Orderld, @LineNum,
©Product,
©Quantity,
©SalesAmount,
©CostAmount, ©Date,
@DW_SourceCode) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_OrderLines_E_Insert]') and OBJECTPROPERTYfld, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_OrderLines_E_Insert]
GO
CREATE PROC dbo.usp_Sales_OrderLines_E_Insert
©Orderld int,
©LineNum int,
©Product int, ©Quantity decimal(18,3),
©SalesAmount decimal(18,3),
©CostAmount decimal(18,3),
©Date datetime,
@DW_SourceCode varchar(15), @DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS INSERT INTO dbo.Sales_OrderLines_E( Orderld,
LineNum, Product,
Quantity,
SalesAmount, CostAmount,
Date,
DW_SourceCode,
DW_Severity, DW_ErrorMessage)
VALUESC
©Orderld,
@LineNum,
©Product, ©Quantity,
@SalesAmount,
©CostAmount,
©Date,
@DW_SourceCode, @DW_Severity,
@DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_OrderLines_Clean]') and OBJECTPROPERTYfld, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_OrderLines_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_OrderLines_Clean ©Debug bit AS
SET NOCOUNT ON DECLARE ©Orderld int
DECLARE ©LineNum int
DECLARE ©Product int
DECLARE ©Quantity decimal(18,3)
DECLARE ©SalesAmount decimal(18,3) DECLARE ©CostAmount decimal(18,3)
DECLARE ©Date datetime
DECLARE ©Counter int
DECLARE ©Error int
DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO)
DECLARE @DW_WarningMessage varchar(lOOO)
DECLARE @DW_SourceCode varchar(15)
DECLARE @DW_TimeStamp smalldatetime SET ©Counter = 0 DECLARE Sales_OrderLines_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Orderld,
LineNum,
Product,
Quantity, SalesAmount,
CostAmount,
Date, DW_SourceCode FROM Sales_OrderLines_R BEGIN TRANSACTION OPEN Sales_OrderLines_Cursor FETCH NEXT FROM Sales_OrderLines_Cursor INTO ©Orderld, @LineNum, ©Product, ©Quantity, ©SalesAmount,
@CostAmount, ©Date,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = "
IF(@OrderId IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Orderld does not comply with validation rule Not empty' END
IF(@LineNum IS NULL) BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', LineNum does not comply with validation rule Not empty' END IF(@Product IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Product does not comply with validation rule Not empty' END
IF(@Quantity IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Quantity does not comply with validation rule Not empty' END
IF(@SalesAmount IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', SalesAmount does not comply with validation rule Not empty' END IF(@CostAmount IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', CostAmount does not comply with validation rule Not empty' END
IF(@Date IS NULL OR ©Date = ") BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Date does not comply with validation rule Not empty' END
IF(@Warning = 1) BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_OrderLines_E_Insert
©Orderld, ©LineNum,
©Product,
©Quantity,
©SalesAmount,
©CostAmount, ©Date,
@DW_SourceCode,
•W,
@DW_WarningMessage
IF(@Debug = 1) BEGIN
PRINT @DW_WarningMessage
END
END IF(@Error = 1)
BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_OrderLines_E_Insert ©Orderld,
@LineNum,
©Product,
©Quantity,
©SalesAmount, ©CostAmount,
©Date,
@DW_SourceCode, 1E1,
@DW_ErrorMessage IF(@Debug = 1) BEGIN PRINT @DW_ErrorMessage
END END
IF(@Error = 0) BEGIN
/* Insert into validated table */ exec usp_Sales_OrderLines_V_Insert ©Orderld,
@LineNum, ©Product,
©Quantity,
©SalesAmount,
@CostAmount,
©Date, @DW_SourceCode
END
IF((@Counter % 1000) = 0)
BEGIN
SAVE TRANSACTION Sales_OrderLines lF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END
END FETCH NEXT FROM Sales_OrderLines_Cursor
INTO ©Orderld,
@LineNum,
©Product,
©Quantity, ©SalesAmount,
©CostAmount,
©Date,
@DW_SourceCode
END CLOSE Sales_OrderLines_Cursor
DEALLOCATE Sales_OrderLines_Cursor
COMMIT TRANSACTION GO EXEC dbo.usp_Sales_OrderLines_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Orders_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Orders_V_Insert] GO
CREATE PROC dbo.usp_Sales_Orders_V_Insert
©Orderld int, ©Customer int,
©Invoiced bit,
©DeliverCountry varchar(50), @DW_SourceCode varchar(15) AS INSERT INTO dbo.Sales_Orders_V( Orderld, Customer, Invoiced, DeliverCountry, DW_SourceCode) VALUES(
©Orderld, ©Customer, ©Invoiced, ©DeliverCountry, @DW_SourceCode)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Orders_E_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Orders_E_Insert]
GO CREATE PROC dbo.usp_Sales_Orders_E_Insert
©Orderld int,
©Customer int,
©Invoiced bit,
©DeliverCountry varchar(50), @DW_SourceCode varchar(15),
@DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS
INSERT INTO dbo.Sales_Orders_E( Orderld, Customer,
Invoiced,
DeliverCountry,
DW_SourceCode,
DW_Severity, DW_ErrorMessage)
VALUES(
©Orderld,
©Customer,
©Invoiced, ©DeliverCountry,
@DW_SourceCode,
@DW_Severity, @DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Orders_Clean]1) and OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Orders_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_Orders_Clean ©Debug bit AS
SET NOCOUNT ON DECLARE ©Orderld int
DECLARE ©Customer int DECLARE ©Invoiced bit DECLARE ©DeliverCountry varchar(50) DECLARE ©Counter int DECLARE ©Error int
DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15) DECLARE @DW_TimeStamp smalldatetime
SET ©Counter = O
DECLARE Sales_Orders_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Orderld,
Customer, Invoiced,
DeliverCountry, DW_SourceCode FROM Sales_Orders_R BEGIN TRANSACTION OPEN Sales_Orders_Cursor
FETCH NEXT FROM Sales_Orders_Cursor INTO ©Orderld, ©Customer, ©Invoiced, ©DeliverCountry,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0
SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = " IF(@OrderId IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Orderld does not comply with validation rule Not empty' END IF(@Customer IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Customer does not comply with validation rule Not empty' END
IF(@Invoiced IS NULL) BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Invoiced does not comply with validation rule Not empty' END
IF(@DeliverCountry IS NULL OR @DeliverCountry = ") BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', DeliverCountry does not comply with validation rule Not empty' END
IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_Orders_E_Insert
©Orderld,
©Customer,
©Invoiced,
©DeliverCountry, @DW_SourceCode,
•W,
@DW_WarningMessage
IF(@Debug = 1)
BEGIN PRINT @DW_WarningMessage
END
END
IF(@Error = 1) BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_Orders_E_Insert
©Orderld, ©Customer,
©Invoiced,
©DeliverCountry, @DW_SourceCode, 1E1,
@DW_ErrorMessage IF(@Debug = 1) BEGIN
PRINT @DW_ErrorMessage
END
END IF(@Error = 0)
BEGIN
/* Insert into validated table */ exec usp_Sales_Orders_V_Insert
©Orderld, ©Customer,
©Invoiced,
©DeliverCountry,
@DW_SourceCode
END IF((@Counter % 1000) = 0)
BEGIN
SAVE TRANSACTION Sales_Orders
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END
END
FETCH NEXT FROM Sales_Orders_Cursor
INTO ©Orderld, ©Customer,
©Invoiced,
©DeliverCountry,
@DW_SourceCode
END CLOSE Sales_Orders_Cursor
DEALLOCATE Sales_Orders_Cursor
COMMIT TRANSACTION GO EXEC dbo.usp_Sales_Orders_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Product_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Product_V_Insert]
GO CREATE PROC dbo.usp_Sales_Product_V_Insert ©Productld int, ©ProductName varchar(50), ©ProductGroup int,
©Category int,
@DW_SourceCode varchar(15) AS INSERT INTO dbo.Sales_Product_V( Productld,
ProductName,
ProductGroup,
Category,
DW_SourceCode) VALUES(
©Productld,
©ProductName,
©ProductGroup,
©Category, @DW_SourceCode)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Product_E_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Product_E_Insert]
GO CREATE PROC dbo.usp_Sales_Product_E_Insert ©Productld int, ©ProductName varchar(50), ©ProductGroup int, ©Category int, @DW_SourceCode varchar(15),
@DW_Severity varchar(l), @DW_ErrorMessage varchar(lOOO) AS
INSERT INTO dbo.Sales_Product_E( Productld, ProductName,
ProductGroup, Category, DW_SourceCode, DW_Severity, DW_ErrorMessage)
VALUES(
©Productld, ©ProductName, ©ProductGroup, ©Category,
@DW_SourceCode, @DW_Severity, @DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Product_Clean]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Product_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_Product_Clean ©Debug bit AS
SET NOCOUNT ON DECLARE ©Productld int
DECLARE @ProductName varchar(50) DECLARE @ProductGroup int DECLARE ©Category int DECLARE ©Counter int DECLARE ©Error int
DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15) DECLARE @DW_TimeStamp smalldatetime
SET ©Counter = O
DECLARE Sales_Product_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Productld,
ProductName, ProductGroup,
Category, DW_SourceCode FROM Sales_Product_R BEGIN TRANSACTION OPEN Sales_Product_Cursor
FETCH NEXT FROM Sales_Product_Cursor INTO ©Productld,
©ProductName, ©ProductGroup, ©Category,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0
SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = " IF(@ProductId IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Productld does not comply with validation rule Not empty'
END
IF(@ProductName IS NULL OR ©ProductName = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', ProductName does not comply with validation rule Not empty' END
IF(@ProductGroup IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', ProductGroup does not comply with validation rule Not empty' END
IF(@Category IS NULL) BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Category does not comply with validation rule Not empty' END IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_Product_E_Insert ©Productld,
©ProductName,
©ProductGroup,
©Category,
@DW_SourceCode, 'W,
@DW_WarningMessage
IF(@Debug = 1)
BEGIN
PRINT @DW_WarningMessage END
END
IF(@Error = 1)
BEGIN /* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_Product_E_Insert ©Productld,
©ProductName, ©ProductGroup,
©Category,
@DW_SourceCode,
1E',
@DW_ErrorMessage lF(@Debug = 1)
BEGIN
PRINT @DW_ErrorMessage END END
IF(@Error = 0) BEGIN
/* Insert into validated table */ exec usp_Sales_Product_V_Insert ©Productld,
@ProductName, ©ProductGroup,
©Category,
@DW_SourceCode
END
IF((@Counter % 1000) = 0) BEGIN
SAVE TRANSACTION Sales_Product
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter) END
END
FETCH NEXT FROM Sales_Product_Cursor
INTO ©Productld,
©ProductName, @ProductGroup,
©Category,
@DW_SourceCode
END
CLOSE Sales_Product_Cursor DEALLOCATE Sales_Product_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_Product_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_ProductCategory_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_ProductCategory_V_Insert]
GO
CREATE PROC dbo.usp_Sales_ProductCategory_V_Insert ©Categoryld int,
©Name varchar(50), @DW_SourceCode varchar(15) AS
INSERT INTO dbo.Sales_ProductCategory_V( Categoryld, Name,
DW_SourceCode) VALUESC @CategoryId,
@Name,
@DW_SourceCode) GO if exists (select * from dbo.sysobjects where id = object_id(N1[dbo].[usp_Sales_ProductCategory_E_Insert]') and
OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_ProductCategory_E_Insert]
GO
CREATE PROC dbo.usp_Sales_ProductCategory_E_Insert
©Categoryld int, ©Name varchar(50),
@DW_SourceCode varchar(15),
@DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS INSERT INTO dbo.Sales_ProductCategory_E( Categoryld,
Name,
DW_SourceCode,
DW_Severity,
DW_ErrorMessage) VALUES(
©Categoryld,
©Name,
@DW_SourceCode,
@DW_Severity, @DW_ErrorMessage)
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_ProductCategory_Clean]') and OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_ProductCategory_Clean]
GO CREATE PROCEDURE dbo.usp_Sales_ProductCategory_Clean ©Debug bit AS
SET NOCOUNT ON
DECLARE ©Categoryld int DECLARE ©Name varchar(50)
DECLARE ©Counter int
DECLARE ©Error int
DECLARE ©Warning int
DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO)
DECLARE @DW_SourceCode varchar(15)
DECLARE @DW_TimeStamp smalldatetime SET ©Counter = 0
DECLARE Sales_ProductCategory_Cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT Categoryld,
Name, DW_SourceCode
FROM Sales_ProductCategory_R BEGIN TRANSACTION OPEN Sales_ProductCategory_Cursor FETCH NEXT FROM Sales_ProductCategory_Cursor INTO ©Categoryld, @Name,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN SET ©Counter = ©Counter + 1
SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = " IF(@CategoryId IS NULL)
BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Categoryld does not comply with validation rule Not empty' END
IF(@Name IS NULL OR ©Name = ") BEGIN
SET ©Error = 1 SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Name does not comply with validation rule Not empty' END
IF(@Warning = 1) BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_ProductCategory_E_Insert
©Categoryld, ©Name,
@DW_SourceCode,
•W,
@DW_WarningMessage
IF(@Debug = 1) BEGIN
PRINT @DW_WarningMessage
END
END IF(@Error = 1)
BEGIN /* Insert into error table */ SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_ProductCategorγ_E_Insert
©Categoryld, @Name, @DW_SourceCode,
1E',
@DW_ErrorMessage IF(@Debug = 1) BEGIN PRINT @DW_ErrorMessage
END END
IF(@Error = 0) BEGIN
/* Insert into validated table */ exec usp_Sales_ProductCategory_V_Insert @CategoryId,
©Name, @DW_SourceCode
END
IF((@Counter % 1000) = 0)
BEGIN
SAVE TRANSACTION Sales_ProductCategory lF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END
END FETCH NEXT FROM Sales_ProductCategory_Cursor
INTO ©Categoryld,
©Name,
@DW_SourceCode
END CLOSE Sales_ProductCategory_Cursor
DEALLOCATE Sales_ProductCategory_Cursor
COMMIT TRANSACTION GO EXEC dbo.usp_Sales_ProductCategory_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_ProductGroup_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[usp_Sales_ProductGroup_V_Insert]
GO CREATE PROC dbo.usp_Sales_ProductGroup_V_Insert ©Groupld int, @Name varchar(50), @DW_SourceCode varchar(15) AS INSERT INTO dbo.Sales_ProductGroup_V( Groupld,
Name, DW_SourceCode)
VALUES(
©Groupld,
©Name,
@DW_SourceCode) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_ProductGroup_E_Insert]') and OBJECTPROPERTYfld, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_ProductGroup_E_Insert]
GO
CREATE PROC dbo.usp_Sales_ProductGroup_E_Insert ©Groupld int,
@Name varchar(50), @DW_SourceCode varchar(15), @DW_Severity varchar(l), @DW_ErrorMessage varchar(lOOO) AS
INSERT INTO dbo.Sales_ProductGroup_E( Groupld,
Name,
DW_SourceCode, DW_Severity, DW_ErrorMessage)
VALUES(
©Groupld, @Name,
@DW_SourceCode, @DW_Severity,
@DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_ProductGroup_Clean]') and OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_ProductGroup_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_ProductGroup_Clean ©Debug bit AS
SET NOCOUNT ON DECLARE ©Groupld int
DECLARE @Name varchar(50) DECLARE ©Counter int DECLARE ©Error int DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15)
DECLARE @DW_TimeStamp smalldatetime SET ©Counter = O
DECLARE Sales_ProductGroup_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Groupld, Name,
DW_SourceCode FROM Sales_ProductGroup_R BEGIN TRANSACTION OPEN Sales_ProductGroup_Cursor FETCH NEXT FROM Sales_ProductGroup_Cursor INTO ©Groupld, ©Name,
@DW_SourceCode WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0 SET @DW_WarningMessage = " SET @DW_ErrorMessage = "
IF(@GroupId IS NULL) BEGIN
SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Groupld does not comply with validation rule Not empty' END
IF(@Name IS NULL OR ©Name = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Name does not comply with validation rule Not empty' END IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_ProductGroup_E_Insert ©Groupld,
©Name,
@DW_SourceCode,
•W,
@DW_WarningMessage lF(@Debug = 1)
BEGIN
PRINT @DW_WarningMessage END END
IF(@Error = 1) BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_ProductGroup_E_Insert
@GroupId, ©Name,
@DW_SourceCode,
1E1,
@DW_ErrorMessage
IF(@Debug = 1) BEGIN
PRINT @DW_ErrorMessage
END
END IF(@Error = 0)
BEGIN
/* Insert into validated table */ exec usp_Sales_ProductGroup_V_Insert
©Groupld, ©Name,
@DW_SourceCode
END
IF((@Counter % 1000) = 0)
BEGIN SAVE TRANSACTION Sales_ProductGroup
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END END
FETCH NEXT FROM Sales_ProductGroup_Cursor
INTO @GroupId,
@Name,
@DW_SourceCode END
CLOSE Sales_ProductGroup_Cursor
DEALLOCATE Sales_ProductGroup_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_ProductGroup_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Region_V_Insert]') and OBJECTPROPERTY(id, N'IsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Region_V_Insert] GO
CREATE PROC dbo.usp_Sales_Region_V_Insert ©Regionld varchar(50),
@DW_SourceCode varchar(15) AS INSERT INTO dbo.Sales_Region_V( Regionld,
DW_SourceCode) VALUESC
©Regionld,
@DW_SourceCode) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Region_E_Insert]') and OBJECTPROPERTYfld, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Region_E_Insert]
GO
CREATE PROC dbo.usp_Sales_Region_E_Insert @RegionId varchar(50), @DW_SourceCode varchar(15), @DW_Severity varchar(l),
@DW_ErrorMessage varchar(lOOO) AS INSERT INTO dbo.Sales_Region_E( Regionld,
DW_SourceCode, DW_Severity,
DW_ErrorMessage) VALUESC
©Regionld, @DW_SourceCode, @DW_Severity,
@DW_ErrorMessage) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Sales_Region_Clean]') and OBJECTPROPERTY(id, N'lsProcedure1) = 1) drop procedure [dbo].[usp_Sales_Region_Clean]
GO
CREATE PROCEDURE dbo.usp_Sales_Region_Clean ©Debug bit AS
SET NOCOUNT ON DECLARE ©Regionld varchar(50)
DECLARE ©Counter int DECLARE ©Error int DECLARE ©Warning int DECLARE @DW_ErrorMessage varchar(lOOO) DECLARE @DW_WarningMessage varchar(lOOO) DECLARE @DW_SourceCode varchar(15) DECLARE @DW_TimeStamp smalldatetime
SET ©Counter = O
DECLARE Sales_Region_Cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT Regionld,
DW_SourceCode FROM Sales_Region_R
BEGIN TRANSACTION OPEN Sales_Region_Cursor FETCH NEXT FROM Sales_Region_Cursor INTO ©Regionld, @DW_SourceCode
WHILE @@FETCH_STATUS = 0 BEGIN
SET ©Counter = ©Counter + 1 SET ©Error = 0 SET ©Warning = 0
SET @DW_WarningMessage = " SET @DW_ErrorMessage = " IF(@RegionId IS NULL OR ©Regionld = ") BEGIN SET ©Error = 1
SET @DW_ErrorMessage = ISNULL(@DW_ErrorMessage, ") + ', Regionld does not comply with validation rule Not empty' END IF(@Warning = 1)
BEGIN
/* Insert into error table */
SET @DW_WarningMessage = RIGHT(@DW_WarningMessage, LEN(@DW_WarningMessage) -2) exec usp_Sales_Region_E_Insert ©Regionld,
@DW_SourceCode,
'W,
@DW_WarningMessage
IF(@Debug = 1) BEGIN
PRINT @DW_WarningMessage
END
END IF(@Error = 1)
BEGIN
/* Insert into error table */
SET @DW_ErrorMessage = RIGHT(@DW_ErrorMessage, LEN(@DW_ErrorMessage) -2) exec usp_Sales_Region_E_Insert ©Regionld,
@DW_SourceCode,
1E', @DW_ErrorMessage IF(@Debug = 1) BEGIN
PRINT @DW_ErrorMessage END
END
IF(@Error = 0)
BEGIN /* Insert into validated table */ exec usp_Sales_Region_V_Insert ©Regionld,
@DW_SourceCode
END IF((@Counter % 1000) = 0)
BEGIN
SAVE TRANSACTION Sales_Region
IF(@Debug = 1)
BEGIN print 'Transaction saved @ ' + convert(varchar, ©Counter)
END
END
FETCH NEXT FROM Sales_Region_Cursor
INTO ©Regionld, @DW_SourceCode
END
CLOSE Sales_Region_Cursor
DEALLOCATE Sales_Region_Cursor
COMMIT TRANSACTION GO
EXEC dbo.usp_Sales_Region_Clean 0 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_Orderlines]') and OBJECTPROPERTY(id, N'IsView1) = 1) drop view [dbo].[view_Orderlines] GO CREATE VIEW dbo.view_Orderlines AS SELECT TOP 100 PERCENT
Sales_OrderLines_V.Product AS [Product],
Sales_OrderLines_V.Quantity AS [Quantity],
Sales_OrderLines_V.SalesAmount AS [SalesAmount], Sales_OrderLines_V.CostAmount AS [CostAmount],
Sales_OrderLines_V.Date AS [Date],
Sales_Orders_V.Customer AS [Customer],
Sales_Orders_V.DeliverCountry AS [DeliverCountry],
Sales_Orders_V.Invoiced AS [Invoiced] FROM
Sales_OrderLines_V INNER JOIN Sales_Orders_V ON Sales_Orders_V.OrderId = Sales_OrderLines_V.OrderId

Claims

1. A method allowing a user to generate an OLAP cube based on one or more data sources comprising a set of source tables having a set of source fields, the method comprising the steps of:
• initializing an execution script to be adapted to generate, when executed, a data warehouse holding validated data based on said one or more data sources, the execution script also being adapted to comprise execution stubs, execution stubs being predefined database query language segments consisting of one or more database manipulation commands that may depend on parameters and selections provided with relation to said data sources;
• the user forming a data selection consisting of a set of tables and a set of fields selected from the source table and source fields;
• for each table in said data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific valid table, said valid table being a table adapted to hold validated rows, validated rows being rows that belong to said each table and comply with a table-specific set of validation rules provided for said each table, a validation rule being a field-specific condition that said specific field must fulfill; o adding to the execution script a table-specific execution stub adapted to determine whether each row in said each table complies with the table- specific set of validation rules and if so, to insert said row into the table- specific valid table; • optionally creating a set of data warehouse views based on one or more of the valid tables that are to form part of said data warehouse, each data warehouse view being formed by carrying out at least the steps of: o the user selecting from the set of tables in the data selection a view-specific first view table to be used in said view; o the user selecting one or more fields from said view-specific first view table; o optionally:
■ the user selecting, from the set of tables in the data selection, a view-specific second view table to be used in said view;
■ the user selecting one or more fields from said view-specific second view table;
■ the user providing a join criterion for joining said view-specific first and second view tables; o providing a name for said data warehouse view; o adding to the execution script a view execution stub representing a creation of said data warehouse view having said name and containing said selected fields from said view-specific first view table, said view execution stub further comprising, on the condition that said view-specific second view table has been selected, statements representing:
■ an inclusion of said selected fields from said view-specific second view table into said data warehouse view;
■ the provided join criterion;
• executing the execution script to form said data warehouse; • initializing a cube generation instruction set for generating said cube, a cube generation instruction set being a set of instructions that can interact with an Application Programming Interface (API) of an OLAP cube generation software application and cause said software application to generate an OLAP cube;
• the user selecting a fact table for said cube, the fact table being either: ■ a table selected from the data warehouse; or
■ a view selected from the set data warehouse views, if created;
• the user providing one or more cube dimensions that are to form part of said cube;
• for each of said dimensions: o the user selecting a dimension-specific set of dimension tables on which said dimension shall be based, each dimension-specific table in said dimension-specific set of dimension tables forming a basis for one or more table-specific levels belonging to said dimension, the dimension-specific set of dimension tables being selected from one or both of:
■ the data warehouse; ■ the set of data warehouse views, if created; o for each dimension table in said dimension-specific set of dimension tables:
■ the user selecting, from said dimension table, a level-specific key field and a level-specific name field to form a level;
• the user providing a set of measures that are to form part of said cube, each measure being provided with a measure-specific aggregation type, each measure being one of: o a standard measure based on a measure-specific field in the fact table; o a derived measure based on one or more fields in the fact table, said derived measure being calculated based on a measure-specific formula adapted to operate on said one or more fields, derived measures being calculated before aggregation is performed on said measure in accordance with said measure-specific aggregation type; o a calculated measure based on:
■ one or more fields in the fact table; and/or ■ another standard measure or measures from the set of measures, if applicable; and/or
■ another calculated measure or measures from the set of measures, if applicable;
a calculation of a calculated measure being characterized by a multidimensional expression specific to said calculated measure;
• adding to the cube generation instruction set a set of API instructions representing at least: o said provision of said dimensions; o said provision of said fact table; o said selection of said dimension-specific sets of dimension tables; o said selection of said level-specific key fields and name fields; o said provision of said measures.
2. A method according to claim 1, the method further comprising:
• providing said instruction set to said software application and executing said software application, thereby generating said cube.
3. A method according to claim 1 or 2, the method further comprising the steps of:
• generating a staging database for holding staging data from said data sources, staging data being data that correspond to said data selection;
• for each specific table in the data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific raw table in the staging database, said raw table being a table adapted to hold rows from said specific table;
• copying, after the step of executing the execution script, each table in the data selection from its data source to said table's corresponding raw table in the staging database, optionally applying a set of table-specific selection rules during said copying of said each table, the table-specific selection rules causing said copying to transfer only a part of said each table that complies with said table-specific set of selection rules;
said table-specific raw tables and valid tables being created in the staging database.
4. A method according to any of the preceding claims, the method further comprising the steps of, for said each table:
• adding to the execution script a table-specific execution stub representing a creation of a table-specific error table, said error table being a table adapted to hold erroneous rows, erroneous rows being rows that belong to said each table but do not comply with said table-specific set of validation rules; • further adapting said table-specific execution stub adapted to determine whether each row in said each table complies with the set of validation rules, the further adapting consisting in that said table-specific execution stub will insert said row into said table-specific error table if said row does not comply with said table- specific set of validation rules.
5. A method according to any of the preceding claims, wherein said table-specific execution stub adapted to determine whether each row in said each table complies with said table-specific set of validation rules is furthermore adapted to apply to said each specific table one or both of: a table-specific set of transformation rules; a table-specific set of default rules;
a table-specific transformation rule being a field-specific operation that transforms said specific field according to a predefined field-specific scheme, said operation optionally being applied only if a set of field-specific transformation conditions are fulfilled;
a table-specific default rule being a field-specific operation that sets said specific field equal to a predefined field-specific default value, said operation optionally being applied only if a set of field-specific defaulting conditions are fulfilled.
6. A method according to any of the preceding claims, the method further comprising the step of providing an error notification if, during said execution, it is determined by one of said table-specific execution stubs adapted to determine whether each row in said each table complies with said table-specific set of validation rules, that a row in said each table does not comply with said table-specific set of validation rules.
7. A method according to claim 6, wherein the error notification is provided to relevant personnel by way of at least one of: an email; a short text message; a fax; a page; an entry in an operating system event log.
8. A method according to any of the preceding claims, wherein the execution stubs are based on the SQL query language.
9. A method according to any of claims 1-8, wherein said execution stubs are based on one of the query languages: Business System 12; Tutorial D; TQL Proposal; Hibernate Query Language (HQL); Object-oriented SQL (OSQL); Quel, C#; VB.NET; Multidimensional Expression Language (MDX); Procedural Language extensions to SQL (PL/SQL).
10. A method according to any of the preceding claims, the method further comprising the step of saving information pertaining to said OLAP cube generation in a project file on a storage medium.
5
11. A method according to claim 10, wherein the project file is human-readable.
12. A method according to any of the preceding claims, wherein the instruction set is based on one of: Analysis Management Objects (AMO); Decision Support Objects (DSO);
10 Oracle OLAP API.
13. A method according to any of the preceding claims, wherein said user's actions in at least a part of said steps are facilitated by a graphical user interface (GUI).
15 14. A method allowing a user to generate a data warehouse holding validated information based on one or more data sources comprising a set of source tables having a set of source fields, the method comprising the steps of
• initializing an execution script to be adapted to generate, when executed, said data warehouse holding validated data based on said one or more data sources, the
20 execution script also being adapted to comprise execution stubs, execution stubs being predefined database query language segments consisting of one or more database manipulation commands that may depend on parameters and selections provided with relation to said data sources;
• the user forming a data selection consisting of a set of tables and a set of fields 25 selected from the source table and source fields;
• for each table in said data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific valid table, said valid table being a table adapted to hold validated rows, validated rows being rows that belong to said each
30 table and comply with a table-specific set of validation rules provided for said each table, a validation rule being a field-specific condition that said specific field must fulfill; o adding to the execution script a table-specific execution stub adapted to determine whether each row in said each table complies with the table-
35 specific set of validation rules and if so, to insert said row into the table- specific valid table;
• optionally creating a set of data warehouse views based on one or more of the valid tables that are to form part of said data warehouse, each data warehouse view being formed by carrying out at least the steps of: o the user selecting from the set of tables in the data selection a view-specific first view table to be used in said view; o the user selecting one or more fields from said view-specific first view table; o optionally: ■ the user selecting, from the set of tables in the data selection, a view-specific second view table to be used in said view;
■ the user selecting one or more fields from said view-specific second view table;
■ the user providing a join criterion for joining said view-specific first and second view tables; o providing a name for said data warehouse view; o adding to the execution script a view execution stub representing a creation of said data warehouse view having said name and containing said selected fields from said view-specific first view table, said view execution stub further comprising, on the condition that said view-specific second view table has been selected, statements representing:
■ an inclusion of said selected fields from said view-specific second view table into said data warehouse view;
■ the provided join criterion; • executing the execution script to form said data warehouse.
15. A method according to claim 14, the method further comprising the steps of:
• generating a staging database for holding staging data from said data sources, staging data being data that correspond to said data selection;
• for each specific table in the data selection: o adding to the execution script a table-specific execution stub representing a creation of a table-specific raw table in the staging database, said raw table being a table adapted to hold rows from said specific table; • copying, after the step of executing the execution script, each table in the data selection from its data source to said table's corresponding raw table in the staging database, optionally applying a set of table-specific selection rules during said copying of said each table, the table-specific selection rules causing said copying to transfer only a part of said each table that complies with said table-specific set of selection rules;
said table-specific raw tables and valid tables being created in the staging database.
16. A method according to claim 14 or 15, the method further comprising the steps of, for said each table:
• adding to the execution script a table-specific execution stub representing a creation of a table-specific error table, said error table being a table adapted to hold erroneous rows, erroneous rows being rows that belong to said each table but do not comply with said table-specific set of validation rules;
• further adapting said table-specific execution stub adapted to determine whether each row in said each table complies with the set of validation rules, the further adapting consisting in that said table-specific execution stub will insert said row into said table-specific error table if said row does not comply with said table- specific set of validation rules.
17. A method according to any of claims 14-16, wherein said table-specific execution stub adapted to determine whether each row in said each table complies with said table-specific set of validation rules is furthermore adapted to apply to said each specific table one or both of: a table-specific set of transformation rules; a table-specific set of default rules;
a table-specific transformation rule being a field-specific operation that transforms said specific field according to a predefined field-specific scheme, said operation optionally being applied only if a set of field-specific transformation conditions are fulfilled;
a table-specific default rule being a field-specific operation that sets said specific field equal to a predefined field-specific default value, said operation optionally being applied only if a set of field-specific defaulting conditions are fulfilled.
18. A method according to any of claims 14-17, the method further comprising the step of providing an error notification if, during said execution, it is determined by one of said table-specific execution stubs adapted to determine whether each row in said each table complies with said table-specific set of validation rules, that a row in said each table does not comply with said table-specific set of validation rules.
19. A method according to claim 18, wherein the error notification is provided to relevant personnel by way of at least one of: an email; a short text message; a fax; a page; an entry in an operating system event log.
20. A method according to any of claims 14-19, wherein the execution stubs are based on the SQL query language.
21. A method according to any of claims 14-20, wherein said execution stubs are based on one of the query languages: Business System 12; Tutorial D; TQL Proposal; Hibernate Query Language (HQL); Object-oriented SQL (OSQL); Quel, C#; VB.NET; Multidimensional Expression Language (MDX); Procedural Language extensions to SQL (PL/SQL).
5
22. A method according to any of claims 14-21, the method further comprising the step of saving information pertaining to said data warehouse in a project file on a storage medium.
10 23. A method according to claim 22, wherein the project file is human-readable.
24. A method according to any of claims 14-23, wherein said user's actions in at least a part of said steps is facilitated by a graphical user interface (GUI).
15 25. A software program implementing at least one of the methods according to any of claims 1-24
26. Hardware adapted to perform at least one of the methods according to any of claims 1-24.
PCT/DK2007/050024 2006-02-24 2007-02-22 Method for generating data warehouses and olap cubes WO2007095959A1 (en)

Applications Claiming Priority (4)

Application Number Priority Date Filing Date Title
DKPA200600270 2006-02-24
US11/361,512 2006-02-24
DKPA200600270 2006-02-24
US11/361,512 US20070203933A1 (en) 2006-02-24 2006-02-24 Method for generating data warehouses and OLAP cubes

Publications (1)

Publication Number Publication Date
WO2007095959A1 true WO2007095959A1 (en) 2007-08-30

Family

ID=37898658

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/DK2007/050024 WO2007095959A1 (en) 2006-02-24 2007-02-22 Method for generating data warehouses and olap cubes

Country Status (1)

Country Link
WO (1) WO2007095959A1 (en)

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2009146558A1 (en) * 2008-06-05 2009-12-10 Gss Group Inc. System and method for building a data warehouse
EP2396753A1 (en) * 2009-02-10 2011-12-21 Zap Holdings Limited Etl builder
CN109977547A (en) * 2019-03-27 2019-07-05 北京金和网络股份有限公司 Big data bulletin generation method based on dynamic modeling

Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6604110B1 (en) * 2000-08-31 2003-08-05 Ascential Software, Inc. Automated software code generation from a metadata-based repository
EP1462957A2 (en) * 2003-03-28 2004-09-29 Microsoft Corporation Systems, methods, and apparatus for automated dimensional model definitions and builds utilizing simplified analysis heuristics
WO2005106711A1 (en) * 2004-04-30 2005-11-10 Analysoft Development Limited Method and apparatus for automatically creating a data warehouse and olap cube

Patent Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6604110B1 (en) * 2000-08-31 2003-08-05 Ascential Software, Inc. Automated software code generation from a metadata-based repository
EP1462957A2 (en) * 2003-03-28 2004-09-29 Microsoft Corporation Systems, methods, and apparatus for automated dimensional model definitions and builds utilizing simplified analysis heuristics
WO2005106711A1 (en) * 2004-04-30 2005-11-10 Analysoft Development Limited Method and apparatus for automatically creating a data warehouse and olap cube

Non-Patent Citations (5)

* Cited by examiner, † Cited by third party
Title
ALEX PAYNE: "Business Intelligence and Data Warehousing in SQL Server 2005", pages 1 - 27, XP002407949, Retrieved from the Internet <URL:http://www.microsoft.com/technet/prodtechnol/sql/2005/dwsqlsy.mspx> *
MICROSOFT: "SQL Server 2005: Analysis Services", MICROSOFT TECH-ED 2005, 30 August 2005 (2005-08-30), pages 1 - 31, XP002407950, Retrieved from the Internet <URL:http://download.microsoft.com/documents/australia/teched2005/hol/HOL029.pdf> *
ORACLE: "Oracle Warehouse Builder 10gR2 - Transforming Data Into Quality Information", January 2006 (2006-01-01), pages 1 - 16, XP002408007, Retrieved from the Internet <URL:http://www.oracle.com/technology/products/warehouse/pdf/transforming%20data%20into%20quality%20information.pdf> *
RITTMAN M: "Oracle Business Intelligence 10g - The Complete Picture", 9 January 2006 (2006-01-09), pages 1 - 19, XP002407951, Retrieved from the Internet <URL:http://web.archive.org/web/20060109234827/http://www.rittman.net/work_stuff/Oracle_BI10g_The_Complete_Picture.pdf> *
RITTMAN M: "Using Oracle Business Intelligence Discoverer with the OLAP Option", 13 December 2005 (2005-12-13), pages 1 - 20, XP002407952, Retrieved from the Internet <URL:http://web.archive.org/web/20051213131555/http://www.oracle.com/technology/pub/articles/rittman_olap.html> *

Cited By (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2009146558A1 (en) * 2008-06-05 2009-12-10 Gss Group Inc. System and method for building a data warehouse
EP2396753A1 (en) * 2009-02-10 2011-12-21 Zap Holdings Limited Etl builder
EP2396753A4 (en) * 2009-02-10 2014-05-07 Zap Holdings Ltd Etl builder
CN109977547A (en) * 2019-03-27 2019-07-05 北京金和网络股份有限公司 Big data bulletin generation method based on dynamic modeling

Similar Documents

Publication Publication Date Title
US20070203933A1 (en) Method for generating data warehouses and OLAP cubes
CN110651264B (en) Query plan generation and execution in a relational database management system with temporal relational databases
US8392464B2 (en) Easily queriable software repositories
CN105144080B (en) System for metadata management
US7257597B1 (en) Table substitution
US8352478B2 (en) Master data framework
US8386916B2 (en) Systems and methods to create a multidimensional expression calculated member in a spreadsheet cell
TWI412945B (en) Retrieving and persisting objects from/to relational databases
EP2669815B1 (en) System and method of generating in-memory models from data warehouse models
US11163616B2 (en) Systems and methods for enabling interoperation of independent software applications
Jensen et al. Converting XML DTDs to UML diagrams for conceptual data integration
US20070255741A1 (en) Apparatus and method for merging metadata within a repository
US20080215621A1 (en) Metamodel-based automatic report generation
US20080306983A1 (en) Mapping hierarchical data from a query result into a tabular format with jagged rows
US20070143321A1 (en) Converting recursive hierarchical data to relational data
US10339040B2 (en) Core data services test double framework automation tool
US9547646B2 (en) User-created members positioning for OLAP databases
US20140244671A1 (en) Semantic reflection storage and automatic reconciliation of hierarchical messages
Tseng et al. Integrating heterogeneous data warehouses using XML technologies
Blakeley et al. The ado. net entity framework: Making the conceptual level real
US20180150532A1 (en) Method and system for implementing an on-demand data warehouse
WO2007095959A1 (en) Method for generating data warehouses and olap cubes
Staudt et al. The role of metadata for data warehousing
US8543597B1 (en) Generic application persistence database
US8930426B2 (en) Distributed requests on remote data

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application
NENP Non-entry into the national phase

Ref country code: DE

32PN Ep: public notification in the ep bulletin as address of the adressee cannot be established

Free format text: NOTIFICATION OF LOSS OF RIGHTS (EPO F1205A OF 041208)

122 Ep: pct application non-entry in european phase

Ref document number: 07702532

Country of ref document: EP

Kind code of ref document: A1