Patent ID: 12242451

DETAILED DESCRIPTION

In the following description, for purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of the present disclosure. It will be evident, however, to one skilled in the art that various embodiment of the present disclosure as defined by the claims may include some or all of the features in these examples alone or in combination with other features described below, and may further include modifications and equivalents of the features and concepts described herein.

Described herein are techniques for independently loading related data into data storages. In some embodiments, a data management system is configured to manage data stored in databases. In addition, the data management system can be configured to receive unprocessed data from a number of different data sources. The data management system may process unprocessed data in several loads where different loads are configured to process different attributes of the unprocessed data. When the data management system loads data into a first column of a record in a first table where the first column is related to a first column in a second table, the data management system uses a column definition for the first column in the second table to determine the value to store in the first column of the record in the first table. The column definition may specify how to determine a value for uniquely identify records in the second table. For example, the column definition may specify to use a hash function on a particular column(s) in the second table and use the output of the hash function as the determined value.

The techniques for independently loading related data into data storages described in the present application provide a number of benefits and advantages over conventional methods for loading related data into data storages. Typically, a relationship data modeling approach uses primary keys and foreign keys to form a join between data related across two tables (e.g., a fact and a dimension). If a first table does not have a particular record with a given primary key then inserts for records in the second table referencing the particular record in the first table can either fail or some complex logic may be needed to create the particular record in the first table. This causes data loads to be sequenced in a way that the first table is processed before the second table. The relationship data modeling approach generally increases the number of joins utilized, the computing time to process queries, and the complexity of the data processing. By generating the reference identifiers using data values in one or more columns in the first table breaks the dependence between the first and second tables thereby allowing data to be loaded in the first and second tables independently.

Furthermore, described herein are techniques for determining references to predefined types of records. In some embodiments, the data management system generates predefined records in tables of databases. The predefined records can serve as placeholder records that unprocessed columns of records in other tables can temporarily reference until the unprocessed columns are later processed. For instance, when the data management system detects a second column of a record in the first table for which the current load is not configured to process data and the second column in the first table is related to a second column in the second table, the data management system can determine a value that references a predefined record in the second table. In some cases, the data management system uses a column definition for the second column in the second table to determine the value to store in the second column of the record in the first table. For example, if the column definition for the second column specifies to use a hash function on a particular column(s) in the second table and use the output of the hash function as the determined value, the data management system uses a predefined value as input to the hash function. The output of the hash function is a value that references the predefined record in the second table. In a subsequent load that is configured to process data for the second column of the record in the first table, the data management system uses the actual data associated with the second column in the record as input to the hash function and then replaces the value referencing the predefined record in the second table with the new output of the hash function.

The techniques for determining references to predefined types of records described in the present application provide a number of benefits and advantages over conventional methods for determining references to predefined types of records. For instance, the techniques described here determine references to predefined types of records based on the values of columns in a table. As such, the value of the references changes based on the values of the columns in a table. On the other hand, some conventional approaches use fixed or hard-coded reference values.

FIG.1illustrates a system100for independently loading related data into data storages according to some embodiments. As shown, system100includes data sources105a-n, data management system115, and client device145. Each of the data sources105a-nis configured to provide data to data management system115for processing. Each of the data sources105a-nmay be an application, a service, a computing system, another data management system, etc. Client device145can interact with data management system115. For example, a user of client device145may send data management system115requests to create databases for data management system115to manage. In addition, a user of client device145can provide data management system115with configuration information for databases to be created by data management system115. In some cases, a user of client device145may send data management system115requests for data (e.g., reports) stored in databases managed by data management system115. In response to such requests, client device145receives the requested data from data management system115.

As illustrated inFIG.1, data management system115includes database manager120, data loading manager125, and storages130-140. Unprocessed data storage130is configured to store data that is to be processed by data management system115. For instance, such data may include data received from data sources105a-n. Database storage135stores databases. Configuration data storage140can store configuration information for the databases stored in database storage135. In some embodiments, storages130-145are implemented in a single physical storage while, in other embodiments, storages130-140may be implemented across several physical storages. WhileFIG.1shows unprocessed data storage130, database storage135, and configuration data storage140as part of data management system115, one of ordinary skill in the art will appreciate that unprocessed data storage130, database storage135, and/or configuration data storage140may be external to data management system115in some embodiments.

Database manager120is responsible for managing databases. For example, database manager120can receive from client device145a request to create a database. Along with the request to create a database, database manager120may receive configuration information for the database, which database manager120stores in configuration data storage140. Examples of configuration information may include a set of tables to include in the database, a set of columns to include in each of the tables, the type of data (e.g., integers, floating point numbers, Booleans, strings, dates, timestamps, etc.) that can be stored in each of the table columns, relationships between the tables (e.g., primary keys, foreign keys, etc.), etc. In response to the request and configuration information, database manager120generates the database and stores it in database storage135. Also, database manager120can received data from data sources105a-n, which database manager120stores in unprocessed data storage130.

Data loading manager125is configured to manage the loading of data into databases stored in database storage135. For example, data loading manager125can process unprocessed data stored in unprocessed data storage130and then load them in databases stored in database storage135. In some embodiments, data loading manager125processes unprocessed data at defined intervals (e.g., once every fifteen minutes, once an hour, once a day, etc.). In other embodiments, data loading manager125processes unprocessed data when data is received from a data source105. In some instances, data loading manager may process unprocessed data for a particular database stored in database storage135in multiple phases (also referred to as loads). For example, in some such instances, data loading manager125can process unprocessed data for a portion of the columns in a table in a database in a first load. Then, in a second load, data loading manager125can process unprocessed data for another, different portion of columns in the table of the database. Data loading manager125may repeat these operations until unprocessed data for all of the remaining columns in the table of the database are processed.

An example operation of system100will now be described by reference toFIGS.1-6. The example operation will demonstrate how data management system115independently loads related data into a database according to some embodiments.FIG.2illustrates an example database schema200according to some embodiments. Specifically, database schema200is provided by a user of client device145in this example. Upon receiving database schema200, database manager120stores it in configuration data storage140. Also, database manager120generates a database, which will be used for this example operation, based on database schema200and stores the database in database storage135.

As depicted inFIG.2, database schema200includes table definitions205-215. Table definition205is for a Sales table that includes five columns: a Root ID column configured to store a unique identifier for identifying a record in the Sales table, a Date column configured to store a date a product is sold, an Amount column configured to store an amount for which the product is sold, a Product column configured to store a reference to information associated with the product sold, and a Store column configured to store a reference to information associated with the store at which the product is sold. Table definition210is for a Products table that includes three columns: a Root ID column configured to store a unique identifier for identifying a record in the Product table, a Product ID column configured to store a unique identifier for identifying a product, and a Product Name column configured to store a name of the product. Table definition215is for a Stores table that includes three columns: a Root ID column configured to store a unique identifier for identifying a record in the Stores table, a Store ID column configured to store a unique identifier for identifying a store, and a Store Name column configured to store a name of the store. In some embodiments, a table generated based on table definition205is referred to as a fact table and tables generated based on table definitions210and215are referred to as dimension tables.

In addition, database schema200includes two relationships220and225. Relationship220specifies a relationship between the Root ID column in the Products table and the Product column in the Sales table. In some embodiments, relationship220may be implemented by defining the Root ID column in the Products table as a primary key and the Product column in the Sales table as a foreign key. Relationship225specifies a relationship between the Root ID column in the Stores table and the Store column in the Sales table. In some embodiments, relationship225may be implemented by defining the Root ID column in the Stores table as a primary key and the Store column in the Sales table as a foreign key.

In this example, table definition210includes a column definition for the Root ID column. The column definition defines how to generate a unique value for identifying a record in the Product table based on one or more values for one or more columns in table definition210. In some embodiments, the column definition in table definition210specifies to use a hash function (e.g., a Secure Hash Algorithm 1 (SHA-1) hash function, a Secure Hash Algorithm 2 (SHA-2) hash function, a Message-Digest algorithm 5 (MD5) hash function, etc.) to generate the unique value. For this example, the one or more values for the one or more columns in table definition210are provided as input to the hash function. Next, the hash function generates an output value based on the input. The output of the hash function may be used as the value for the Root ID column in the Products table. In addition, table definition215also includes a column definition for the Root ID column. This column definition defines how to generate a unique value for identifying a record in the Stores table based on one or more values for one or more columns in table definition215. In some embodiments, the column definition in table definition215specifies to use a hash function to generate the unique value. In this example, the one or more values of the one or more columns in table definition215is provided as input to the hash function. The hash function then generates an output value based on the input. The output of the hash function may be used as the value for the Root ID column in the Stores table. In some instances, the same hash function is used for both column definitions.

Referring back toFIG.1, the example operation begins by data management system115receiving a set of data from one of the data sources105a-n. In response to receiving the set of data, database manager120stores it in unprocessed data storage130.FIG.3illustrates an example of unprocessed data300according to some embodiments. For this example, unprocessed data300is included in the set of data that data management system115receives from the one data source105. As shown, unprocessed data300includes four attributes: a Date attribute, an Amount attribute, a Product attribute, and a Store attribute. Additionally, unprocessed data300includes record305that includes values for the four attributes: a value of “Jan. 1, 2022” for the Date attribute, a value of “$399” for the Amount attribute, a value of “Computer” for the Product attribute, and a value of “Acme, Inc.” for the Store attribute.

Referring now toFIG.4, an example products table400is shown according to some embodiments. Database manager120generates products table400for the database stored in database storage135in this example based on table definition210in database schema200. As such, products table400includes three columns: a Root ID column configured to store a unique identifier for identifying a record in products table400, a Product ID column configured to store a unique identifier for identifying a product, and a Product Name column configured to store a name of the product. Products table400includes record405, which includes values for the three columns: a value of “ABC” for the Root ID column, a value of “7839” for the Product ID column, and a value of “Computer” for the Product Name column. As mentioned above, table definition210includes a column definition for the Root ID column, which defines how to generate a unique value for identifying a record in the Products table based on one or more values of one or more columns in table definition210. In this example, the value of the Product Name column is used to generate the value for the Root ID column. In particular, the value of “Computer” for the Product Name column in record405is provided as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“ABC” in this example) is used as the value for the Root ID column in record405.

Referring now toFIG.5, an example stores table500is shown according to some embodiments. Database manager120generates stores table500for the database stored in database storage135in this example based on table definition215in database schema200. Accordingly, stores table500includes three columns: a Root ID column configured to store a unique identifier for identifying a record in stores table500, a Store ID column configured to store a unique identifier for identifying a store, and a Store Name column configured to store a name of the store. Stores table500includes record505, which includes values for the three columns: a value of “DEF” for the Root ID column, a value of “463” for the Store ID column, and a value of “Acme, Inc.” for the Store Name column. As explained above, table definition215includes a column definition for the Root ID column, which defines how to generate a unique value for identifying a record in the Stores table based on one or more values of one or more columns in table definition215. For this example, the value of the Store Name column is used to generate the value for the Root ID column. Specifically, the value of “Acme. Inc.” for the Store Name column in record505is provided as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“DEF” in this example) is used as the value for the Root ID column in record505.

In this example, unprocessed data is processed in two loads: a first load where the Date attribute, the Amount attribute, and the Product attribute are processed and a second load where the Store attribute is processed. Continuing with the example, data loading manager125starts (e.g., at a defined interval, upon receiving the data from the data source105, etc.) to process the set of data, which includes unprocessed data300, received from the one data source105.FIGS.6A and6Billustrate an example sales table600according to some embodiments. In particular.FIG.6Aillustrates sales table600after data loading manager125loads data from record305into sales table600in a first load. Database manager120generates sales table600for the database stored in database storage135in this example based on table definition205in database schema200. Thus, sales table600includes five columns: a Root ID column configured to store a unique identifier for identifying a record in sales table600, a Date column configured to store a date a product is sold, an Amount column configured to store an amount for which the product is sold, a Product column configured to store a reference to information associated with the product sold, and a Store column configured to store a reference to information associated with the store at which the product is sold.

In the first load, data loading manager125processes unprocessed data300by first generating record605in sales table600in the database. Then, data loading manager125generates a unique value (e.g., by incrementing the value in the last record in sales table600by one and using the incremented value) for record605and stores it in the Root ID column of sales table600. Next, data loading manager125loads the value for the Date attribute in record305in the Date column of record605in sales table600. Similarly, data loading manager125loads the value for the Amount attribute in record305in the Amount column of record605in sales table600. To process the value of the Product attribute in record305for the Product column in sales table600, data loading manager125determines that the Product column in sales table600is related to the Root ID column in products table400based on database schema200. Next, data loading manager125uses the column definition specified for the Root ID column in table definition210to determine how to generate the value for the Product column of record605. For this example, the column definition for the Root ID column in table definition210specifies to use a hash function. Therefore, data loading manager125provides the value for the Product attribute as input to the specified hash function. The hash function generates an output value (“ABC” in this example) based on the input. Data loading manager125uses the output value as the value for the Product column of record605in sales table600and, hence, stores the output value in the Product column of record605. As shown inFIG.6A, the value of the Product column in sales table600is the same as the value of the Root ID column in record405. That is, the value of the Product column in record605in sales table600serves as a reference to record405.

FIG.6Billustrates sales table600after data loading manager125loads data from record305into sales table600in a second load. During the second load, data loading manager125processes the remaining Store attribute in record305. Data loading manager125processes the value of the Store attribute in record305for Store column in sales table600by determining that the Store column in sales table600is related to the Root ID column in stores table500based on database schema200. Data loading manager125then uses the column definition specified for the Root ID column in table definition215to determine how to generate the value for the Store column of record605. In this example, the column definition for the Root ID column in table definition215specifies to use the same hash function as the one specified in the column definition for the Root ID column in table definition210. As such, data loading manager125provides the value for the Store attribute as input to the hash function, which generates an output value (“DEF” in this example) based on the input. Data loading manager125uses the output value as the value for the Store column of record605in sales table600and stores the output value in the Store column of record605. As depicted inFIG.6B, the value of the Store column in sales table600is the same as the value of the Root ID column in record505. As such, the value of the Store column in record605of sales table600serves as a reference to record505.

The example described above shows that using a column definition to generate the values for the Root ID column in products table400and generate the values in the Product column in sales table600for referencing records in products table400allows data to be loaded in products table400and sales table600independently. Similarly, a column definition used to generate the values for the Root ID column in stores table500and generate the values in the Store column in sales table600for referencing records in stores table500allows data to be loaded in stores table500and sales table600independently. For example, unprocessed data300may be processed and loaded with valid values into sales table600even if record405is not yet created in products table400and/or record505is not yet created in stores table500. This is because the values generated for record605that reference record405and record505do not depend on records405and505being present in product table400and stores table500, respectively. Records405and505were already created in products table400and stores table500for the example operation. However, in cases where records405and505were not in products table400and stores table500data loading manager125can create records405and505for products table400and stores500, respectively, in a separate load(s) configured to detect these missing records and create them for products table400and stores table500accordingly.

FIG.7illustrates a process700for independently loading related data into data storages according to some embodiments. In some embodiments, data management system115performs process700. Process700starts by receiving, at710, a set of data for a record in a first table. The set of data comprises a set of values for a set of attributes. The first table comprises a first set of columns. A first column in the first set of columns in the first table is configured to refer to a second column in a second set of columns in a second table. Referring toFIGS.1-4and6as an example, data management system115receives unprocessed data300from one of the data sources105. Unprocessed data300is for record605in sales table600. The Product column in sales table600is related to the Root ID column in products table400based on database schema200.

Next, process700generates, at720, the record in the first table. Referring toFIGS.1and6Aas an example, data loading manager125generates record605in sales table600in a first load where the Date attribute, the Amount attribute, and the Product attribute are processed.

Then, process700generates, at730, a value for the first column in the first set of columns in the first table based on a subset of the set of values for a subset of the set of attributes. Referring toFIGS.1,3, and6Aas an example, data loading manager1253processes the value for the Product attribute in record305by determining that the Product column in sales table600is related to the Root ID column in products table400based on database schema200and using the column definition specified for the Root ID column in table definition210to determine how to generate the value for the Product column of record605. For instance, the column definition for the Root ID column in table definition210specifies to use a hash function. As such, data loading manager125provides the value for the Product attribute in record305as input to the specified hash function. The hash function generates an output value based on the input.

Finally, process700stores, at740, the value in the first column in the first set of columns of the record. Referring toFIGS.1,4, and6Aas an example, data loading manager125uses the output value generated by the hash function as the value for the Product column of record605in sales table600. Accordingly, data loading manager125stores the output value in the Product column of record605. As shown inFIG.6A, the value of the Product column in sales table600is the same as the value of the Root ID column in record405. Thus, the value of the Product column in record605of sales table600serves as a reference to record405.

As mentioned above, techniques are described herein for determining references to predefined types of records. Some advantages of these techniques for determining references to predefined types of records relate to reporting. For instance, in some data-warehousing systems, facts and dimensions may be the two main tables that are used to provide meaningful data to end-users to extract business value and patterns. Facts are quantitative and describe events like a purchase or a payment or an invoice etc. These facts are supported by multiple dimensions which provide context and describe the facts better. Fact records are usually denormalized (e.g., they have a flat structure of data and contain information from several transactional or source tables). This leads to a fact record containing several references to dimensions through foreign key references. A fact record can require multiple data loads to fully populate the fact record. Each data load populates a subset of columns in the fact record. So when a fact record has been processed by some but not all of the data loads, the fact record does not have valid references for other dimensions that are to be populated by the remaining data loads. This leaves several dimension references as null. When a report is generated based on these types of partially processed fact records, it may not make sense to the end-user and likely confuses the end-user about the completeness of the record data. Determining references to predefined types of records, such as a null record, allows the end-user to make better sense of unprocessed data in reports.

Another example operation of system100will now be described by reference toFIGS.1-3and8-13. This example operation will show how data management system115determines references to predefined types of records according to some embodiments. Similar to the previous example operation, the database that database manager120generated based on database schema200will be used for this example operation. The example operation begins by data management system115receiving a set of data from one of the data sources105a-n. In response to receiving the set of data, database manager120stores the set of data in unprocessed data storage130. In this example, unprocessed data300is included in the set of data that data management system115receives from the one data source105.

For this example, database manager120generates predefined types of records in dimension tables (e.g., tables generated based table definitions210and215) when database manager120generates the database based on database schema200. One type of predefined record is a null record. In some embodiments, a null record is a record in a table that has null values in the columns of the record. Referring now toFIG.8, another example products table800is shown according to some embodiments. In this example, database manager120generates products table800based on table definition210in database schema200when database manager120generates the database in this example. As shown, products table800includes three columns: a Root ID column configured to store a unique identifier for identifying a record in products table800, a Product ID column configured to store a unique identifier for identifying a product, and a Product Name column configured to store a name of the product. As part of the process of generating products table800, database manager120generates predefined null record805. Database manager120stores a defined value (e.g., an empty value, a null value, a value of 0, etc.) in the Product ID column and the Product Name column. Database manager120generates the value for the Root ID column of record805in the products table800based on the column definition specified for table definition210. In particular, database manager120provides the value for the Product Name column in record805as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“GHI” in this example) is used as the value for the Root ID column in record805.

Referring now toFIG.9, an example stores table900is shown according to some embodiments. For this example, database manager120generates stores table900based on table definition215in database schema200when database manager120generates the database in this example. Accordingly, stores table900includes three columns: a Root ID column configured to store a unique identifier for identifying a record in stores table900, a Store ID column configured to store a unique identifier for identifying a store, and a Store Name column configured to store a name of the store. As part of the process of generating products table900, database manager120generates predefined record905. Database manager120stores a defined value (e.g., an empty value, a null value, a value of 0, etc.) in the Store ID column and the Store Name column. Database manager120generates the value for the Root ID column of predefined record905in the stores table900based on the column definition specified for table definition215. Specifically, database manager120provides the value for the Store Name column in predefined record905as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“JKL” in this example) is used as the value for the Root ID column in predefined record905.

At some point after database manage120generates the database that includes products table800and stores table900, data loading manager125adds records405and505to products table800and stores table900, respectively (e.g., via load(s) configured to detect missing records and create them for products table400and stores table500, as mentioned above).FIG.10illustrates products table800after a record is added according to some embodiments. In particular,FIG.10depicts products table800after data loading manager125adds record405to products table800.FIG.11illustrates stores table900after a record is added according to some embodiments. Specifically,FIG.11shows stores table900after data loading manager125adds record505to stores table900.

Similar to the previous example operation, unprocessed data is processed in this operation in two loads: a first load where the Date attribute, the Amount attribute, and the Product attribute are processed and a second load where the Store attribute is processed. Continuing with the example, data loading manager125begins (e.g., at a defined interval, upon receiving the data from the data source105, etc.) to process the set of data, which includes unprocessed data300, received from the one data source105.FIGS.12A and12Billustrate another example sales table1200according to some embodiments.FIG.12Aillustrates sales table1200after data loading manager125loads data from record305into sales table1200in a first load. Database manager120generates sales table1200for the database stored in database storage135in this example based on table definition205in database schema200. As such, sales table1200includes five columns: a Root ID column configured to store a unique identifier for identifying a record in sales table1200, a Date column configured to store a date a product is sold, an Amount column configured to store an amount for which the product is sold, a Product column configured to store a reference to information associated with the product sold, and a Store column configured to store a reference to information associated with the store at which the product is sold.

In the first load, data loading manager125processes unprocessed data300by first generating record1205in sales table1200in the database. Next, data loading manager125generates a unique value (e.g., by incrementing the value in the last record in sales table1200by one and using the incremented value) for record1205and stores it in the Root ID column of record1205in sales table1200. Data loading manager125then loads the value for the Date attribute in record305in the Date column of record1205in sales table1200. Similarly, data loading manager125loads the value for the Amount attribute in record305in the Amount column of record1205in sales table1200. To process the value of the Product attribute in record305for the Product column in sales table1200, data loading manager125determines that the Product column in sales table1200is related to the Root ID column in products table800based on database schema200. Then, data loading manager125uses the column definition specified for the Root ID column in table definition210to determine how to generate the value for the Product column of record1205. In this example, the column definition for the Root ID column in table definition210specifies to use a hash function. Accordingly, data loading manager125provides the value for the Product attribute as input to the specified hash function. The hash function generates an output value (“ABC” in this example) based on the input. Data loading manager125uses the output value as the value for the Product column of record1205in sales table1200and, thus, stores the output value in the Product column of record1205. As illustrated inFIG.12A, the value of the Product column in sales table1200is the same as the value of the Root ID column in record405of products table800. Hence, the value of the Product column in record1205of sales table1200serves as a reference to record405in products table800.

In the first load, data loading manager125also determines references to predefined types of records for columns in sales table1200for which the first load is not configured to process unprocessed data. For each such a column that data loading manager125detects, data loading manager125determines whether the column is related to another column in another table based on the database schema of the database. If so, data loading manager125determines a reference to a predefined type of record based on the column definition specified for the other column in the other table. For instance, in this example, data loading manager125detects that the Store column in sales table1200is a column for which the first load is not configured to process unprocessed data. Next, data loading manager125determines that the Product column in sales table1200is related to the Root ID column in stores table900based on database schema200. Data loading manager125then uses the column definition specified for the Root ID column in table definition215to determine how to generate the value for the Store column of record1205. Since data loading manager125does not have a value for the Store column, data loading manager125uses the defined value (e.g., an empty value, a null value, a value of 0, etc.) that is configured to be stored in the Store column of the predefined record905and provides the defined value as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“JKL” in this example) is used as the value for the Root ID column in record905. Data loading manager125uses the output value as the value for the Store column of record1205in sales table1200and, therefore, stores the output value in the Store column of record1205. As depicted inFIG.12A, the value of the Store column in record1205of sales table1200is the same as the value of the Root ID column in record905. Accordingly, the value of the Store column in record1205in sales table1200serves as a reference to record905.

FIG.12Billustrates sales table1200after data loading manager125loads data from record305into sales table1200in a second load. During the second load, data loading manager125processes the remaining Store attribute in record305. Data loading manager125processes the value of the Store attribute in record305for Store column in sales table1200by determining that the Store column in sales table1200is related to the Root ID column in stores table500based on database schema200. Data loading manager125then uses the column definition specified for the Root ID column in table definition215to determine how to generate the value for the Store column of record1205. In this example, the column definition for the Root ID column in table definition215specifies to use the same hash function as the one specified in the column definition for the Root ID column in table definition210. As such, data loading manager125provides the value for the Store attribute as input to the hash function, which generates an output value (“DEF” in this example) based on the input. Data loading manager125uses the output value as the value for the Store column of sales table1200and replaces the “JKL” value with the output value in the Store column of record1205. As depicted inFIG.12B, the value of the Store column in sales table1200is the same as the value of the Root ID column in record505. As such, the value of the Store column in sales table1200serves as a reference to record505in stores table900.

The example operation described above illustrates how data management system115determines references to predefined types of records. Some of the many advantages of this feature is for reporting purposes. For instance, when data management system115receives a request (e.g., from client device145) for a report that includes data from sales table1200and the request is received after the first load but before the second load, data management system115can detect (e.g., during a join between sales table1200and stores table900) that record1205refers to a predefined record in the stores table900. Data management system115can annotate the Stores column of record1205(e.g., “unprocessed data”, “unclassified data”, etc.) to indicate that data for that column is not yet processed. Without this feature, the Store column may be ambiguously blank or record1205may not even make it into the report for lack of completeness.

FIG.13illustrates a process1300for determining references to predefined types of records according to some embodiments. In some embodiments, data management system115performs process1300. Process1300starts by receiving, at1310, a set of data for a first record in a first table. The set of data comprises a set of values for a set of attributes. The first table comprises a first set of columns. A first column in the first set of columns in the first table is configured to refer to a second column in a second set of columns in a second table. Referring toFIGS.1-3,8, and12as an example, data management system115receives unprocessed data300from one of the data sources105. Unprocessed data300is for record1205in sales table1200. The Product column in sales table1200is related to the Root ID column in products table800based on database schema200.

In a data loading process configured to load a subset of the set of data into a subset of the first set of columns in the first table, process1300determines, at1320, that the first column in the first set of columns in the first table does not belong in the subset of the first set of columns in the first table. Referring toFIGS.1,3, and12Aas an example, data loading manager125determines that the Store column in sales table1200is a column for which the first load is not configured to process unprocessed data. That is, the first load is configured to process the Date attribute, the Amount attribute, and the Product attribute in unprocessed data300for the Data column, the Amount column, and the Product column in sales table1200. Hence, data loading manager125determines that the Store column in sales table1200is not a column for which the first load is configured to process unprocessed data300.

Next, process1300generates, at1330, the first record in the first table. Referring toFIGS.1and12Aas an example, data loading manager125generates record1205for sale table1200. Process1300then generates, at1340, a value for the first column in the first set of columns in the first table. The value refers to a second record in the second table configured to represent a defined type of record. Referring toFIGS.1-3,9, and12Aas an example, data loading manager125determines that the Product column in sales table1200is related to the Root ID column in stores table90based on database schema200. As such, data loading manager125uses the column definition specified for the Root ID column in table definition215to determine how to generate the value for the Store column of record1205. Because data loading manager125does not have a value for the Store column, data loading manager125uses the defined value (e.g., an empty value, a null value, a value of 0, etc.) that is configured to be stored in the Store column of the predefined record905and provides the defined value as input to a hash function, which generates an output value based on the input. The output value generated by the hash function (“JKL” in this example) is used as the value for the Root ID column in record905.

Finally, process1300stores, at1350, the value in the first column in the first set of columns of the first record. Referring toFIGS.1and12Aas an example, data loading manager125uses the output value generated by the hash function as the value for the Store column of record1205in sales table1200. Accordingly, data loading manager125stores the output value in the Store column of record1205. As shown inFIG.12A, the value of the Store column in record1205of sales table1200is the same as the value of the Root ID column in record905. Accordingly, the value of the Store column in record1205in sales table1200serves as a reference to record905.

FIG.14illustrates an exemplary computer system1400for implementing various embodiments described above. For example, computer system1400may be used to implement data sources105a-n, data management system115, and client device145. Computer system1400may be a desktop computer, a laptop, a server computer, or any other type of computer system or combination thereof. Some or all elements of database manager120, data loading manager125, or combinations thereof can be included or implemented in computer system1400. In addition, computer system1400can implement many of the operations, methods, and/or processes described above (e.g., process700and process1300). As shown inFIG.14, computer system1400includes processing subsystem1402, which communicates, via bus subsystem1426, with input/output (I/O) subsystem1408, storage subsystem1410and communication subsystem1424.

Bus subsystem1426is configured to facilitate communication among the various components and subsystems of computer system1400. While bus subsystem1426is illustrated inFIG.14as a single bus, one of ordinary skill in the art will understand that bus subsystem1426may be implemented as multiple buses. Bus subsystem1426may be any of several types of bus structures (e.g., a memory bus or memory controller, a peripheral bus, a local bus, etc.) using any of a variety of bus architectures. Examples of bus architectures may include an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MCA) bus, an Extended ISA (EISA) bus, a Video Electronics Standards Association (VESA) local bus, a Peripheral Component Interconnect (PCI) bus, a Universal Serial Bus (USB), etc.

Processing subsystem1402, which can be implemented as one or more integrated circuits (e.g., a conventional microprocessor or microcontroller), controls the operation of computer system1400. Processing subsystem1402may include one or more processors1404. Each processor1404may include one processing unit1406(e.g., a single core processor such as processor1404-1) or several processing units1406(e.g., a multicore processor such as processor1404-2). In some embodiments, processors1404of processing subsystem1402may be implemented as independent processors while, in other embodiments, processors1404of processing subsystem1402may be implemented as multiple processors integrate into a single chip or multiple chips. Still, in some embodiments, processors1404of processing subsystem1402may be implemented as a combination of independent processors and multiple processors integrated into a single chip or multiple chips.

In some embodiments, processing subsystem1402can execute a variety of programs or processes in response to program code and can maintain multiple concurrently executing programs or processes. At any given time, some or all of the program code to be executed can reside in processing subsystem1402and/or in storage subsystem1410. Through suitable programming, processing subsystem1402can provide various functionalities, such as the functionalities described above by reference to process700, process1300, etc.

I/O subsystem1408may include any number of user interface input devices and/or user interface output devices. User interface input devices may include a keyboard, pointing devices (e.g., a mouse, a trackball, etc.), a touchpad, a touch screen incorporated into a display, a scroll wheel, a click wheel, a dial, a button, a switch, a keypad, audio input devices with voice recognition systems, microphones, image/video capture devices (e.g., webcams, image scanners, barcode readers, etc.), motion sensing devices, gesture recognition devices, eye gesture (e.g., blinking) recognition devices, biometric input devices, and/or any other types of input devices.

User interface output devices may include visual output devices (e.g., a display subsystem, indicator lights, etc.), audio output devices (e.g., speakers, headphones, etc.), etc. Examples of a display subsystem may include a cathode ray tube (CRT), a flat-panel device (e.g., a liquid crystal display (LCD), a plasma display, etc.), a projection device, a touch screen, and/or any other types of devices and mechanisms for outputting information from computer system1400to a user or another device (e.g., a printer).

As illustrated inFIG.14, storage subsystem1410includes system memory1412, computer-readable storage medium1420, and computer-readable storage medium reader1422. System memory1412may be configured to store software in the form of program instructions that are loadable and executable by processing subsystem1402as well as data generated during the execution of program instructions. In some embodiments, system memory1412may include volatile memory (e.g., random access memory (RAM)) and/or non-volatile memory (e.g., read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, etc.). System memory1412may include different types of memory, such as static random access memory (SRAM) and/or dynamic random access memory (DRAM). System memory1412may include a basic input/output system (BIOS), in some embodiments, that is configured to store basic routines to facilitate transferring information between elements within computer system1400(e.g., during start-up). Such a BIOS may be stored in ROM (e.g., a ROM chip), flash memory, or any other type of memory that may be configured to store the BIOS.

As shown inFIG.14, system memory1412includes application programs1414, program data1416, and operating system (OS)1418. OS1418may be one of various versions of Microsoft Windows, Apple Mac OS, Apple OS X, Apple macOS, and/or Linux operating systems, a variety of commercially-available UNIX or UNIX-like operating systems (including without limitation the variety of GNU/Linux operating systems, the Google Chrome® OS, and the like) and/or mobile operating systems such as Apple iOS, Windows Phone, Windows Mobile, Android, BlackBerry OS, Blackberry 10, and Palm OS, WebOS operating systems.

Computer-readable storage medium1420may be a non-transitory computer-readable medium configured to store software (e.g., programs, code modules, data constructs, instructions, etc.). Many of the components (e.g., database manager120and data loading manager125) and/or processes (e.g., process700and process1300) described above may be implemented as software that when executed by a processor or processing unit (e.g., a processor or processing unit of processing subsystem1402) performs the operations of such components and/or processes. Storage subsystem1410may also store data used for, or generated during, the execution of the software.

Storage subsystem1410may also include computer-readable storage medium reader1422that is configured to communicate with computer-readable storage medium1420. Together and, optionally, in combination with system memory1412, computer-readable storage medium1420may comprehensively represent remote, local, fixed, and/or removable storage devices plus storage media for temporarily and/or more permanently containing, storing, transmitting, and retrieving computer-readable information.

Computer-readable storage medium1420may be any appropriate media known or used in the art, including storage media such as volatile, non-volatile, removable, non-removable media implemented in any method or technology for storage and/or transmission of information. Examples of such storage media includes RAM. ROM, EEPROM, flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disk (DVD), Blu-ray Disc (BD), magnetic cassettes, magnetic tape, magnetic disk storage (e.g., hard disk drives), Zip drives, solid-state drives (SSDs), flash memory card (e.g., secure digital (SD) cards, CompactFlash cards, etc.), USB flash drives, or any other type of computer-readable storage media or device.

Communication subsystem1424serves as an interface for receiving data from, and transmitting data to, other devices, computer systems, and networks. For example, communication subsystem1424may allow computer system1400to connect to one or more devices via a network (e.g., a personal area network (PAN), a local area network (LAN), a storage area network (SAN), a campus area network (CAN), a metropolitan area network (MAN), a wide area network (WAN), a global area network (GAN), an intranet, the Internet, a network of any number of different types of networks, etc.). Communication subsystem1424can include any number of different communication components. Examples of such components may include radio frequency (RF) transceiver components for accessing wireless voice and/or data networks (e.g., using cellular technologies such as 2G, 3G, 4G, 5G, etc., wireless data technologies such as Wi-Fi, Bluetooth, ZigBee, etc., or any combination thereof), global positioning system (GPS) receiver components, and/or other components. In some embodiments, communication subsystem1424may provide components configured for wired communication (e.g., Ethernet) in addition to or instead of components configured for wireless communication.

One of ordinary skill in the art will realize that the architecture shown inFIG.14is only an example architecture of computer system1400, and that computer system1400may have additional or fewer components than shown, or a different configuration of components. The various components shown inFIG.14may be implemented in hardware, software, firmware or any combination thereof, including one or more signal processing and/or application specific integrated circuits.

FIG.15illustrates an exemplary computing device1500for implementing various embodiments described above. For example, computing device1500may be used to implement data sources105a-nand client device145. Computing device1500may be a cellphone, a smartphone, a wearable device, an activity tracker or manager, a tablet, a personal digital assistant (PDA), a media player, or any other type of mobile computing device or combination thereof. As shown inFIG.15, computing device1500includes processing system1502, input/output (I/O) system1508, communication system1518, and storage system1520. These components may be coupled by one or more communication buses or signal lines.

Processing system1502, which can be implemented as one or more integrated circuits (e.g., a conventional microprocessor or microcontroller), controls the operation of computing device1500. As shown, processing system1502includes one or more processors1504and memory1506. Processors1504are configured to run or execute various software and/or sets of instructions stored in memory1506to perform various functions for computing device1500and to process data.

Each processor of processors1504may include one processing unit (e.g., a single core processor) or several processing units (e.g., a multicore processor). In some embodiments, processors1504of processing system1502may be implemented as independent processors while, in other embodiments, processors1504of processing system1502may be implemented as multiple processors integrated into a single chip. Still, in some embodiments, processors1504of processing system1502may be implemented as a combination of independent processors and multiple processors integrated into a single chip.

Memory1506may be configured to receive and store software (e.g., operating system1522, applications1524, I/O module1526, communication module1528, etc. from storage system1520) in the form of program instructions that are loadable and executable by processors1504as well as data generated during the execution of program instructions. In some embodiments, memory1506may include volatile memory (e.g., random access memory (RAM)), non-volatile memory (e.g., read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, etc.), or a combination thereof.

I/O system1508is responsible for receiving input through various components and providing output through various components. As shown for this example, I/O system1508includes display1510, one or more sensors1512, speaker1514, and microphone1516. Display1510is configured to output visual information (e.g., a graphical user interface (GUI) generated and/or rendered by processors1504). In some embodiments, display1510is a touch screen that is configured to also receive touch-based input. Display1510may be implemented using liquid crystal display (LCD) technology, light-emitting diode (LED) technology, organic LED (OLED) technology, organic electro luminescence (OEL) technology, or any other type of display technologies. Sensors1512may include any number of different types of sensors for measuring a physical quantity (e.g., temperature, force, pressure, acceleration, orientation, light, radiation, etc.). Speaker1514is configured to output audio information and microphone1516is configured to receive audio input. One of ordinary skill in the art will appreciate that I/O system1508may include any number of additional, fewer, and/or different components. For instance, I/O system1508may include a keypad or keyboard for receiving input, a port for transmitting data, receiving data and/or power, and/or communicating with another device or component, an image capture component for capturing photos and/or videos, etc.

Communication system1518serves as an interface for receiving data from, and transmitting data to, other devices, computer systems, and networks. For example, communication system1518may allow computing device1500to connect to one or more devices via a network (e.g., a personal area network (PAN), a local area network (LAN), a storage area network (SAN), a campus area network (CAN), a metropolitan area network (MAN), a wide area network (WAN), a global area network (GAN), an intranet, the Internet, a network of any number of different types of networks, etc.). Communication system1518can include any number of different communication components. Examples of such components may include radio frequency (RF) transceiver components for accessing wireless voice and/or data networks (e.g., using cellular technologies such as 2G, 3G, 4G, 5G, etc., wireless data technologies such as Wi-Fi, Bluetooth, ZigBee, etc., or any combination thereof), global positioning system (GPS) receiver components, and/or other components. In some embodiments, communication system1518may provide components configured for wired communication (e.g., Ethernet) in addition to or instead of components configured for wireless communication.

Storage system1520handles the storage and management of data for computing device1500. Storage system1520may be implemented by one or more non-transitory machine-readable mediums that are configured to store software (e.g., programs, code modules, data constructs, instructions, etc.) and store data used for, or generated during, the execution of the software.

In this example, storage system1520includes operating system1522, one or more applications1524, I/O module1526, and communication module1528. Operating system1522includes various procedures, sets of instructions, software components and/or drivers for controlling and managing general system tasks (e.g., memory management, storage device control, power management, etc.) and facilitates communication between various hardware and software components. Operating system1522may be one of various versions of Microsoft Windows, Apple Mac OS, Apple OS X. Apple macOS, and/or Linux operating systems, a variety of commercially-available UNIX or UNIX-like operating systems (including without limitation the variety of GNU/Linux operating systems, the Google Chrome® OS, and the like) and/or mobile operating systems such as Apple iOS, Windows Phone, Windows Mobile. Android, BlackBerry OS. Blackberry 10, and Palm OS, WebOS operating systems.

Applications1524can include any number of different applications installed on computing device1500. Examples of such applications may include a browser application, an address book application, a contact list application, an email application, an instant messaging application, a word processing application, JAVA-enabled applications, an encryption application, a digital rights management application, a voice recognition application, location determination application, a mapping application, a music player application, etc.

I/O module1526manages information received via input components (e.g., display1510, sensors1512, and microphone1516) and information to be outputted via output components (e.g., display1510and speaker1514). Communication module1528facilitates communication with other devices via communication system1518and includes various software components for handling data received from communication system1518.

One of ordinary skill in the art will realize that the architecture shown inFIG.15is only an example architecture of computing device1500, and that computing device1500may have additional or fewer components than shown, or a different configuration of components. The various components shown inFIG.15may be implemented in hardware, software, firmware or any combination thereof, including one or more signal processing and/or application specific integrated circuits.

FIG.16illustrates an exemplary system1600for implementing various embodiments described above. For example, client devices1602-1608may be used to implement data sources105a-nand client device145and cloud computing system1612may be used to implement data management system115. As shown, system1600includes client devices1602-1608, one or more networks1610, and cloud computing system1612. Cloud computing system1612is configured to provide resources and data to client devices1602-1608via networks1610. In some embodiments, cloud computing system1612provides resources to any number of different users (e.g., customers, tenants, organizations, etc.). Cloud computing system1612may be implemented by one or more computer systems (e.g., servers), virtual machines operating on a computer system, or a combination thereof.

As shown, cloud computing system1612includes one or more applications1614, one or more services1616, and one or more databases1618. Cloud computing system1612may provide applications1614, services1616, and databases1618to any number of different customers in a self-service, subscription-based, elastically scalable, reliable, highly available, and secure manner.

In some embodiments, cloud computing system1612may be adapted to automatically provision, manage, and track a customer's subscriptions to services offered by cloud computing system1612. Cloud computing system1612may provide cloud services via different deployment models. For example, cloud services may be provided under a public cloud model in which cloud computing system1612is owned by an organization selling cloud services and the cloud services are made available to the general public or different industry enterprises. As another example, cloud services may be provided under a private cloud model in which cloud computing system1612is operated solely for a single organization and may provide cloud services for one or more entities within the organization. The cloud services may also be provided under a community cloud model in which cloud computing system1612and the cloud services provided by cloud computing system1612are shared by several organizations in a related community. The cloud services may also be provided under a hybrid cloud model, which is a combination of two or more of the aforementioned different models.

In some instances, any one of applications1614, services1616, and databases1618made available to client devices1602-1608via networks1610from cloud computing system1612is referred to as a “cloud service.” Typically, servers and systems that make up cloud computing system1612are different from the on-premises servers and systems of a customer. For example, cloud computing system1612may host an application and a user of one of client devices1602-1608may order and use the application via networks1610.

Applications1614may include software applications that are configured to execute on cloud computing system1612(e.g., a computer system or a virtual machine operating on a computer system) and be accessed, controlled, managed, etc. via client devices1602-1608. In some embodiments, applications1614may include server applications and/or mid-tier applications (e.g., HTTP (hypertext transfer protocol) server applications, FTP (file transfer protocol) server applications, CGI (common gateway interface) server applications, JAVA server applications, etc.). Services1616are software components, modules, application, etc. that are configured to execute on cloud computing system1612and provide functionalities to client devices1602-1608via networks1610. Services1616may be web-based services or on-demand cloud services.

Databases1618are configured to store and/or manage data that is accessed by applications1614, services1616, and/or client devices1602-1608. For instance, storages130-140may be stored in databases1618. Databases1618may reside on a non-transitory storage medium local to (and/or resident in) cloud computing system1612, in a storage-area network (SAN), on a non-transitory storage medium local located remotely from cloud computing system1612. In some embodiments, databases1618may include relational databases that are managed by a relational database management system (RDBMS). Databases1618may be a column-oriented databases, row-oriented databases, or a combination thereof. In some embodiments, some or all of databases1618are in-memory databases. That is, in some such embodiments, data for databases1618are stored and managed in memory (e.g., random access memory (RAM)).

Client devices1602-1608are configured to execute and operate a client application (e.g., a web browser, a proprietary client application, etc.) that communicates with applications1614, services1616, and/or databases1618via networks1610. This way, client devices1602-1608may access the various functionalities provided by applications1614, services1616, and databases1618while applications1614, services1616, and databases1618are operating (e.g., hosted) on cloud computing system1612. Client devices1602-1608may be computer system1400or computing device1500, as described above by reference toFIGS.14and15, respectively. Although system1600is shown with four client devices, any number of client devices may be supported.

Networks1610may be any type of network configured to facilitate data communications among client devices1602-1608and cloud computing system1612using any of a variety of network protocols. Networks1610may be a personal area network (PAN), a local area network (LAN), a storage area network (SAN), a campus area network (CAN), a metropolitan area network (MAN), a wide area network (WAN), a global area network (GAN), an intranet, the Internet, a network of any number of different types of networks, etc.

The above description illustrates various embodiments of the present disclosure along with examples of how aspects of the present disclosure may be implemented. The above examples and embodiments should not be deemed to be the only embodiments, and are presented to illustrate the flexibility and advantages of various embodiments of the present disclosure as defined by the following claims. Based on the above disclosure and the following claims, other arrangements, embodiments, implementations and equivalents will be evident to those skilled in the art and may be employed without departing from the spirit and scope of the present disclosure as defined by the claims.