WO1999057658A1 - Systeme et procede de mise a jour d'une base de donnees multidimensionnelle - Google Patents

Systeme et procede de mise a jour d'une base de donnees multidimensionnelle Download PDF

Info

Publication number
WO1999057658A1
WO1999057658A1 PCT/US1999/009633 US9909633W WO9957658A1 WO 1999057658 A1 WO1999057658 A1 WO 1999057658A1 US 9909633 W US9909633 W US 9909633W WO 9957658 A1 WO9957658 A1 WO 9957658A1
Authority
WO
WIPO (PCT)
Prior art keywords
data
database
user
cache
command
Prior art date
Application number
PCT/US1999/009633
Other languages
English (en)
Inventor
Jay Thomas Drayton
Michael York
Original Assignee
Information Advantage
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
Application filed by Information Advantage filed Critical Information Advantage
Publication of WO1999057658A1 publication Critical patent/WO1999057658A1/fr

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 generally to database management, and more particularly to a system and method for manipulating facts in multi-dimensional databases.
  • a data warehouse stores a company's operational and historical data in an integrated relational database for decision support applications, business data access and reporting. Decision support systems access such databases to analyze and summarize corporate performance.
  • Data warehouses employ relational database management systems that use a language such as SQL to retrieve rows and columns of numeric data.
  • the systems may also permit access to textual files such as documents.
  • Data may be accessed directly via user-generated SQL commands, or indirectly, via an interface which generates the desired SQL commands.
  • Applications such as Business Objects from Business Objects S.A., France, (http://www.businessobjects.com), Forest and Trees from Platinum Technology Inc., Oakbrook Terrace, IL, (http://www.platinum.com), and Pilot's Lightship from Pilot Software Inc., Cambridge, MA, (http://www.pilotsw.com) are typical of off-the-shelf applications which use browse windows under the control of end-users to generate the SQL code needed to analyze the data in the data warehouse.
  • OLAP OnLine Analytic Processing
  • Multidimensional analysis systems have been available for over 15 years, first on mainframes and then on client/servers.
  • data is divided into the dimensions and facts needed to manage the business.
  • Dimensions for marketing applications may include 2 products, markets, distribution channels and time periods. The dimensions are used to reference specific points in a database. What that point represents is called a fact. As examples, units sold, revenue, and price are all facts.
  • Dimensions are further described by attributes, such as size, flavor, location or fiscal year. Attributes also describe hierarchies within a dimension, even overlapping and inconsistent hierarchies. These hierarchies determine the vertical relationships within a dimension.
  • a standard hierarchy is year -> quarter -> month -> week -> day.
  • OLAP applications it becomes possible for OLAP applications to automatically shift their 'view' up or down a hierarchy. This is commonly referred to as 'drilling' within this application space, an example of this would be shifting an annual report's total 1997 data down to view the individual quarter's numbers.
  • Multidimensional analysis allows users to select, summarize, calculate, format and report by dimensions and by attributes within dimensions. It can be used to support virtually any time-series decision support application including reporting, analysis, forecasting and budgeting.
  • decision support systems must support analysis based not only on historical data but also on projections for future activities. For instance, marketing may project sales for the next three months. These figures may then be introduced into a model used to tune manufacturing output over that period of time. To date, such analysis has been performed using multi-dimensional databases having fixed locations. What is needed is a system and method of extracting and modifying information from an existing database which can be applied to a relational database in order to free the organization from the space limitations of multidimensional databases. In addition, what is needed is a system and method capable of creating reports not only based on existing information but also on projections of future activities.
  • the present invention is a data warehouse system and method.
  • the data warehouse includes a memory and a processor.
  • the memory includes a database having a plurality of data entries.
  • the processor includes a cache and an override engine, wherein the cache includes a subset of the plurality of data entries and wherein the override engine extracts data from the cache for viewing by a user, modifies the data in response to one or more user commands and saves the user commands to a file for later application to the database.
  • a data warehouse includes a plurality of workstations connected to a memory by a server.
  • the memory includes a database having a plurality of data entries.
  • the server includes a cache and an override engine, wherein the server operates in response to user commands to store a subset of the plurality of data entries in the cache, wherein the override engine extracts data from the cache and sends the data extracted from the cache to the client workstation for viewing by a user, modifies the data in response to one or more of the user commands, saves the user commands to a file and operates in response to a commit command to modifies the database based on the user commands stored in the file.
  • a method of reporting data from a data warehouse in which the steps are providing a server and a memory device, storing a database in the memory device, wherein the database includes a plurality of data entries, extracting a subset of data entries from the database, storing the subset of data entries on the server, modifying the data entries stored on the server in response to user commands, reading data from the modified data entries stored on the server and displaying the data to the user.
  • a method of forecasting based on data in a data warehouse in which the steps are providing a server and a memory device, storing a database in the memory device, wherein the database includes a plurality of data entries, extracting a subset of data entries from the database, storing the subset of data entries on the server, modifying the data entries stored on the server in response to user commands, storing the user commands, reading data from the modified data entries stored on the server, displaying the data to the user and modifying the database based on the stored user commands.
  • a method of increasing the speed in which changes to a relational database are reflected back to the user in which the steps are extracting a subset of data from the database, wherein the step of extracting includes the steps of displaying a representation of the subset of data to the user and storing the subset of data in a cache, receiving a data modification command, storing the data modification command in a file and applying the data modification command against the subset of data stored in the cache, wherein the step of applying the data modification command includes the step of modifying the subset of data to reflect application of the data modification command.
  • Figure 1 shows a data warehouse decision support system according to the present invention
  • Figure 2 shows a more detailed implementation of the data warehouse decision support system of Figure 1;
  • Figure 3 illustrates a star schema implementation of a data warehouse according to the present invention
  • Figure 4 is a more detailed description of a data warehouse according to the present invention
  • Figure 5 shows an alternate embodiment of data warehouse and decision support system according to the present invention.
  • Figures 6a and 6b illustrate distribution of adjusted data across levels in an unlocked and a locked system, respectively.
  • Figure 1 illustrates a computer system 10 having an enhanced capacity to extract and modify data stored in a database.
  • Computer system 10 includes a memory 12 connected to a processor 14.
  • Processor 14 includes a cache 16, a reporting tool 19 and an override engine 18.
  • Memory 12 is used to store a database 20.
  • Database 20 includes a plurality of data entries 26.
  • processor 14 also includes an instruction application process 34 for permanently applying the modifications made within override engine 18 to data entries 26 stored in database 20.
  • database 20 is a data warehouse and override engine 18 is implemented as a multidimensional data entry/edit software engine process which supports the creation and adjustment of data points in the data warehouse. 5
  • cache 16 is implemented as a persistent cache which stores a subset of the plurality of data entries 26.
  • override engine 18 extracts data from the persistent cache for viewing by a user, modifies the data in response to one or more user commands and saves the user commands to an instruction file 30 for later application to database 20.
  • a user sends commands to processor 14 over command interface 22 and receives reports based on data extracted from database 20 through report interface 24.
  • command interface 22 and report interface 24 are implemented as a single graphical user interface (GUI) 32.
  • GUI graphical user interface
  • User action within system 10 is basically an adjustment process of data representing future periods in time. In one embodiment, this data will have been pregenerated by other systems and stored in the database 20. An example of this would be a statistically created forecast of future volume. A user can then "override" or adjust the values (i.e., facts) that they consider to be inaccurate.
  • the combination of the adjustment capability with the OLAP ability to present the data in virtually any level of granularity allows business professionals to review data within a familiar business context and use their knowledge of the business to refine the data warehouse values.
  • System 10 allows end users and administrators to define reports to organize and present data. These reports are created by selecting the desired dimensional identifiers as well as the facts that contain the data needed to support the decision or planning process. As an example, a report may contain facts such as 'Annual Plan,' 'Statistical Forecast,' and 'Last Years Actuals' with dimensions 'Central Region,' 'Corn Syrup,' and ' May 98, June 98, and July 98.'
  • All the data the user sees on the report is stored in a work file that was created for that adjustment cycle.
  • the process administrator creates one work file for each adjustment cycle. For example, in a monthly planning cycle there would be one work file for the May 1996 cycle, one for the June 1996 cycle, and so on.
  • the process administrator updates the database with the new forecast data.
  • Statistical forecast data 1. Users create reports using Preliminary reports. tactical work file, filters and dimensions. tactical fact. They work only with the assigned products/ markets/periods.
  • the override function is used in a forecasting application
  • the following chart summarizes a subjective management forecasting cycle. It identifies the required input, the steps, and the results.
  • Management work file 1. Users create reports using Preliminary reports. management fact. filters and dimensions. They work only with the assigned products/ markets/periods.
  • the process administrator prepares the data for the next cycle. This involves saving the data to the database, preparing the work file for the next cycle, and setting up the single adjustable fact for that cycle.
  • the adjustable fact is the fact that can be changed or overridden.
  • the Forecasting administrator updates the database with the consensus tactical forecast and sets up the next planning cycle, in this case a consensus management forecast.
  • the following chart lists the steps. Input to the Cycle Steps taken by the Forecasting Results of the Cycle administrator
  • a forecast override application such as system 10 allows users to apply qualitative methods (i.e., business knowledge) to the forecasting process. Users can change values generated by purely statistical methods to reflect changing business conditions.
  • system 10 maintains a master activity file.
  • the master activity file consists of the full set of dimension information user information, and security information. It also contains detailed information about all of the facts that were chosen by the process administrator to support the planning or adjustment process. It is stored in only one place.
  • the data structure has pointers to all of the data that the master structure points to.
  • a system end user can view and modify any data that he has permission to see.
  • the data that they are allowed to see can be configured on a user by user basis to ensure that they see data they are authorized to access and in a usable context.
  • a lock file is created while the work file is being processed by a user to ensure atomic usage of the data in the file.
  • reports are created from a template.
  • the template is like a blueprint for the report.
  • system 10 saves the blueprint.
  • report templates are shared among users. Each user can then create their own version of the report. For example, they can run a new report, modify the 9 report template, or "drill" to create a different version of the report. By drilling, they are able to review and adjust the data anew, with more discreet levels of detail. As an example, instead of simply adjusting the fact 'Planned Budget' up by 25% at the annual level, the end user may 'drill down' to the monthly level and review and adjust specific months values. All reports contain one or more columns and one or more rows. Each report can consist of multiple sections. If a report contains multiple sections, each section contains the same type of row and column information.
  • the section, column, and row names in a report reflect the dimensions of database 20. These dimensions give meaning to the values in database 20. It is, therefore, important to understand how the dimensions work together.
  • Every data value in database 20 is defined by one or more dimensions. To fully describe each value, a report must contain at least one element from each of the available dimensions.
  • Period dimensions are time intervals used for identifying and consolidating the data, such as weekly, monthly, quarterly, and yearly intervals.
  • Non-period dimensions describe other aspects of your data.
  • Non-period dimensions may include, for example, Geo-Political, Product, and Business Org.
  • Each dimension can consist of hierarchical levels.
  • product may be a dimension.
  • Brand and SKU are associated product dimension levels. The levels represent differing degrees of detail and the paths used when you drill.
  • database 20 is a data warehouse stored in one or more work files. Each work file can contain an unlimited number of data points. (That is, the work file size is limited only by the amount of physical storage.)
  • database 20 is represented as a multidimensional database.
  • Override engine 18 can modify and view data points at any combination of levels within the multidimensional database and, as values are changed, the new values are allocated to all levels of the dimension hierarchies. This ensures that summary levels of the data still total correctly.
  • User instructions to the override engine 18 are not applied to the data points. Instead the instructions are stored in instruction file 30. Since a single instruction may change tens of thousands of rows, just storing the instructions is much faster and less expensive than storing all the changed values.
  • Once editing is complete the edits can be applied to the work file by executing instruction application process 34. In this way, large scale data warehouse updates can be performed offline while the user is working on other tasks. 10
  • computer system 10 can present the user with the results of the desired changes more quickly than in previous data warehouse analysis products.
  • system 10 can handle data warehouses which are larger in size than current database products.
  • override engine 18 extracts the subset of data entries 26 from database 20 and stores the extracted data into cache 16.
  • override engine 18 employs the same mechanism used by reporting tool 19 to extract data from database 20. For instance, in one such embodiment, both override engine 18 and reporting tool 19 determine the appropriate levels of data to extract from database 20 by querying an OLAP object running on a standard request broker. Similarly, both override engine 18 and reporting tool 19 look to a Metadata table such as Metadata table 36 to identify fact tables, determine the levels they need in each fact table, and to extract the data from each required fact table. Override engine 18 then takes the report generated and pushes it into cache 16.
  • override engine 18 receives a command, executes the command, and presents the data to the user.
  • override engine 18 stores the command in instruction file 30 so that if one wants to rerun that report later, all that has to happen is that the commands are reapplied to the known starting point.
  • override engine 18 provides the user or users with a mechanism for quickly adjusting data stored in the data warehouse during a subjective fact adjustment session.
  • data stored in the data warehouse can be in any of three states during such an adjustment cycle. Data that came out of database 20 is "untouched.” Commands to alter the data are stored to instruction file 34 and are used to change the data being displayed to the user. The commands do not, however, change the data in either cache 16 or database 20.
  • Data that's been stored back to the data warehouse is "adjusted” or “written” data.
  • Data in database 20 is modified using the instructions stored in instruction file 34 via a "commit" command.
  • data stored in persistent cache 16 is invalidated during execution of the "commit” command.
  • Data which has been adjusted in cache 16 but which has not yet been written to database 20 is in that more nebulous state in the middle where it is adjusted but not committed. And that's the situation where cache 16 does not match what's in the data warehouse anymore.
  • the data gets to that state by executing a 11
  • the "release” command causes override engine 18 to apply the commands not only to the data presented to the screen but also to the data within cache 16 itself. In one such embodiment, commands must be “released” before the resulting changes are “committed” to database 20. Once a set of commands are “released”, however, the original data is gone.
  • override engine 18 maintains a stack of old states and the user can scroll back through a list of releases to recover an older state of cache 16.
  • system 10 only extracts data from database 20 at the start of the forecasting process. In such an embodiment, system 10 only extracts data from database 20 at the start of the forecasting process.
  • System 10 applies a three-step process: work file generation, interactive overriding, and database storage. Work file generation is accomplished using the extract phase. Extract reads the base statistical forecast data from database 20 and stores the data in a Work File (cache 16) in the UNIX file system of processor 14. The remaining phases (override, release, and commit) use the data in the Work file to generate adjusted fact data.
  • System 10 allows the user to interactively change individual product or market values to reflect changing business conditions. Each adjustment is allocated up and down the dimension hierarchy (aggregation levels).
  • Allocations are performed by using the dimension's drill hierarchy to identify lower level components of the data point adjusted. These lower level components are then given the new value that maintains their relative contribution to adjusted value. Users may also lock or unlock individual values so that the values do not change during the adjustment process (directly or indirectly).
  • the adjustment is checked- in to the work file.
  • the check-in process ensures that two adjustors do not adjust the same values and, in one embodiment, it allows a process administrator to review the work before commit.
  • the final phase of the override process is the database storage phase, or commit. Once the user or administrator is satisfied with the generated overrides, the overrides must be committed to the database. This is accomplished in the storage phase.
  • extraction is the first phase of the override process. It is the generation of the work file. Extraction builds a snapshot of database 20 and stores the snapshot in the Work File in the UNIX file system of processor 14. Extraction is generally 12 done once because it is a time-consuming process and you generally do not want or need a user to see that happen.
  • the extraction process is performed by an agent executing within reporting tool 19. The agent will typically be set up to perform the extraction process after database 20 is refreshed.
  • the extraction agent operates in response to user commands to extract the correct level of data from database 20. In one such embodiment, one could set up system 10 to respond to a command such as "I want these markets by these products for this period on the horizon, by channel" (or whatever your other dimensions are). Another command syntax might be "Give me all my products and give me all my markets at the region level over a selected forecast horizon" (e.g., current to current-plus- 12).
  • refresh of the persistent cache is triggered by the current flag moving forward one month. For example, if the current month switches from January to February, a subset of data is read from database 20 and a combination of facts from database 20 and calculated facts formed as a function of data in database 20 are stored in cache 16. Any data resident in persistent cache 16 at the time of the refresh that is unsaved or uncommitted is simply overwritten.
  • the system 10 To build the Work File, the system 10 must determine which dimensions are drillable and at which aggregation levels the non-drillable dimensions will be forecast. In one embodiment, this information is stored in the Category table of the Metadata. For drillable dimensions, data is stored in the Work File at the lowest level defined in the drill hierarchy for that dimension. For non-drillable dimensions, data is stored only at the level of aggregation specified in the Metadata for that dimension.
  • the Override phase allows the user to interactively adjust the base statistical forecast data. Data is read from the Work File generated by the extraction phase. The user is allowed to adjust values, lock and unlock values, drill up and down the drillable dimension's hierarchy, as well as many other functions.
  • the commit phase performs the commit for the Override process. First, it reads the Work File. Next, the data values of the lowest-level decomposed data are read from the warehouse. These values are used to properly proportion the data as it is decomposed to the 13 lowest level in each dimension's hierarchy. Finally, the adjusted and decomposed data is written to the database.
  • the data in the Work File is stored at the regional (REG) level of the Market dimension. Further suppose that the lowest level in the Market hierarchy is market (MKT). Store will read the REG level data from the work file, decompose the REG level data to the MKT level, and finally write the MKT level data to the database.
  • REG regional
  • MKT market
  • database 20 is implemented as a non-normalized star schema.
  • a simplified version of a star schema 38 is shown generally in Fig. 3.
  • facts are stored as data in fact tables.
  • the fact tables are indexed by a multi-part key made up of the individual keys of each dimension.
  • dimension information is stored in dimension tables.
  • DimTable 40 is a dimension table while the tables labeled "Warehouse Product”, “Warehouse Market”, “Warehouse Period” and “Warehouse Fact” are fact tables 42, 44, 46 and 48, respectively.
  • Non-normalized star schemas are designed for very fast data aggregation and calculation. Such speed can be very advantageous in building the subset of data to be stored in cache 16.
  • Such systems can, however, bog down considerably when required to perform incremental updates (e.g., as is the case during a forecasting session). That is where the use of cache 16 and instruction file 30 are instrumental in supporting such forecasting sessions.
  • the downside of a star schema approach is that you end up with widespread replication of data across tables. At the same time, however, this replication of data give you the ability to get your keys from the data warehouse with very small queries that database 20 certainly can handle quickly. So a star schema approach is very well optimized for queries that pull a large number of rows out of database 20.
  • Standard OLAP reporting tools do not have to understand multiple levels within a product or market hierarchy of a multi-tiered data warehouse. This is a key difference between override engine 18 and a standard OLAP reporting tool.
  • Override engine 18 must understand multiple levels at the same time because for the purposes of an adjustment, there may be interdependencies on the levels themselves for a reporting tool that essentially say, "O.K., here's data at this level,” or "Here's data at the combination of these two levels.”
  • override engine extracts that data, performs calculations where necessary and stores the modified data to cache 16.
  • override engine 18 and instruction application process 34 In order to operate correctly, however, with database 20, override engine 18 and instruction application process 34 must understand the structure of database 20. In one embodiment, as is shown in Figure 3, override engine 18 and instruction application process 34 extract the structure of database 20 by reviewing the contents of Metadata table 36.
  • metadata table 36 includes a period dimension table 40 and a fact table 46.
  • each dimension table includes a unique key, unique description field, a level column and a hierarchy level column.
  • period dimension table includes a unique sequence number column, a sequence within year column and a current period column.
  • Fact table 46 includes keys which are identical in type and structure to the keys listed in dimension table 40.
  • the metadata table can be used to drive a drill hierarchy that tells database 20 that days make up weeks and weeks make up months and months make up quarters, quarters make up halves, halves make up years. Or that four quarters make up a year, so that the user can jump and skip things. Same thing in the product hierarchy.
  • Override engine 18 and reporting engine 19 understand the Metadata structure and use the data stored in the dimension tables to extract data from database 20.
  • a certain table may include a category of sales by total U.S. And maybe it is category, manufacturer, brand. And then over in another table are lists of SKUs by store.
  • Another approach is to have a separate fact table for each month's data. That way when the next month's data arrive, it gets placed into a new fact table and stored in database 20.
  • An advantage of such an approach is that if one of the fact tables gets lost or corrupted, you can reload that month.
  • database 20 gets bigger and bigger, its value to the company increases and so does the cost of maintaining the database. Relational databases can scale into much larger data sizes and are much more maintainable than a corresponding multi- dimensional database.
  • database 20 may be distributed across a number of computers.
  • computer system 50 includes a server 52 connected to a processor 58 and a processor 62 by a network 56.
  • server 52 is connected to a plurality 1 through N of workstations 62.
  • Processor 58 and processor 62 store portions of database 20 in memories 60 and 64, respectively.
  • server 52 maintains a separate instantiation 54 of override engine 18, reporting tool 19 and cache 16 for each user performing the forecasting function.
  • a single cache 16 is used across all users. In either case, changes to cache 16 are maintained in a separate instruction file 30 for each instantiation 52 and are only applied to the cache on receipt of a "release" command.
  • processor 58 is a multiple processor machine such as the Hewlett Packard HP9000 running an Oracle database application while processor 62 is a Tandem Himalaya 128.
  • aggregated data is stored in the Oracle database on the HP machine while the lowest level data is stored in the Himalaya machine.
  • the Metadata tells the program the appropriate processor to which a query should be addressed. For example, if data is stored by category by store in processor 62, override engine 18 can determine this be looking at the Metadata. If, on the other hand, the user is storing aggregate data in cache 16, it will determine from the Metadata table that it should extract such data from the Oracle database on processor 58.
  • override engine 18 and reporting tool 19 run as a relatively thin windows client that essentially just provides GUI 32. All the analytics, all the storage, and all the real processing is done on UNIX server 52.
  • override engine includes the ability to lock values in database 20.
  • the changes at one level of the hierarchy are pushed down to each of the sublevels of the hierarchy. For example, if production is increased across the board by 10,000 units, the 10,000 units are distributed proportionally across each of the entities at the lowest level of the hierarchy.
  • override engine 18 includes a locking mechanism which can be used to lock the output of any of the manufacturing plants to a certain value. Any increases are then distributed proportionally across the remaining, unlocked, manufacturing plants.
  • the adjustment report displays the values of the dimensions the user selects on a Template dialog screen. The only values that can be adjusted for the 16 forecast are found in the unshaded cells. Locked values are shaded red and non-adjustable values are shaded yellow.
  • the user can adjust a value at any level and the effects ripple through the rest of the product hierarchy. For example, if he or she adjusts a SKU value, the values of products at higher levels change to account for the lower level adjustment. Or, if the user adjusts a higher level value, the values for the lower level products change as needed.
  • Fig. 6a is an example of decomposition or proportional fitting based on current forecast data.
  • the current adjustment data is retrieved at manufacture level 70 and, after an adjustment, is proportionally propagated through Brand level 72 to SKU level 74.
  • both reporting tool 19 and override engine 18 have the ability to limit the effect of commands such that the data returned from database 20 does not include the entire hierarchy for all dimensions. For instance, one may care about multiple levels of product dimension, but would like to limit the market adjustments to a particular level (e.g., make the market adjustments at a total U.S. ). In one such embodiment, the user can select the level at which data is adjusted.
  • override engine 18 For example, one would tell override engine 18 to, "Make your product dimension drillable, make your market dimension non- drillable, make your period dimension non-drillable.” Adjustments then must only be driven down the product hierarchy for display; the override engine does not have to try to drive it down the other hierarchies. As you add multiple drillable dimensions, the amount of work that has to happen when you make adjustments expands exponentially. Not only does it go down product, but it has to go down product for every market, or every submarket, or every submarket of every submarket and you end up with a huge matrix of numbers to keep track of. Drilling allows the user to display the fact values at different product levels, both up and down the product hierarchy. With drilling, one can see the aggregate values of a group of products, or can identify the specific values that went into the aggregate amount. 17
  • Drilling up works with any product where there are other products at a higher level. For example, one can drill up on a value if he or she is at SKU level 74 shown in Fig. 6a. Finally, a user can drill down with any product if he or she is at a level above the lowest level, SKU. For example, one can drill down if at manufacture level 70 in Fig. 6a. If, for instance, you want to adjust the amount of a particular soft drink sold in a particular city, you could either make that a drillable thing or a non-drillable dimension. You can do it in one of two ways. One is you can set your market dimension as drillable, and then poll the whole market hierarchy in order to drill down to the city and make the change. If, however, you did not need that flexibility in drilling and the associated performance impact of allocating the changes through that dimension, you could make the market dimension
  • override engine drives the numbers down to the bottom of all the hierarchies by modifying each number as a percent of contribution to the total.
  • override engine 18 initiates a transfer of data at the atomic level, receives the data and pushes it up to the regional level.
  • the aggregated regional data is then stored in cache 16 and commands from the user are applied against that aggregate data.
  • a "commit" command is executed and the modifications get drilled down to the atomic level in database 20.
  • the result is that user commands get executed quickly at the regional level and get stored accurately at the atomic level. For example, one might determine what would happen if volume increased by 10% in a region such as the Great Lakes territory. The change would be made at the territory level and, when committed, it is distributed to the cities as a function of the percentage they contribute to the regional total.
  • Override engine 18 understands all of reporting tool 19's filters and calculated facts; it also understands the hierarchies of system 10. Essentially everything in the Metadata that can be defined, is read by reporting tool 19 and override engine 18 and they will use those definition. So you can create a calculated fact in reporting tool 19 and override engine 18 will automatically see it and know how to use it. (An example of a calculated fact would be gross revenue. Gross revenue is calculated as units times price. You would not, therefore store gross revenue.
  • forecasts are stored as separate databases within database 20. For instance, one could include a dimension labeled "scenario" into the warehouse. It can, therefore, be critical to have the ability to add extra dimensions to systems 10 and 50.
  • cache 16 is a persistent cache stored as a B-tree in a UNIX file system on processor 14.
  • the B-tree allows you to essentially go with a much more compact data storage in situations where the matrix of data is populated sparsely (such as in, for example, customer centric databases) yet, at the same time, a B-tree implementation does allow you to get reasonably fast access times.
  • the persistent cache is implemented using standard Rogue
  • Wave B-tree code The performance of the Rogue Wave B-tree code can be enhanced by replacing the standard I/O stream (which is write character by character) with an I/O stream which writes data in large blocks of data.
  • the structure of the B-tree is optimized for size over speed.
  • cache 16 can be implemented as a set of file- based arrays. Such arrays are very fast, requiring only simple calculations to get to the right locations. They can be, however, very inefficient space- wise. Typically, if one of the dimensions of the data warehouse is customer, and the number of potential customers is large, you are better off selecting a B-tree implementation for cache 16.
  • Override engine 18 allows the user to grab slices of data from a database that may be too big to comprehend in its entirety. Override engine 18 allows you to slice a portion of the database out, which sometimes in the industry they'll refer to as a datamark, automatically and intelligently through the user of metadata. The portion extracted can be dealt with either within or outside the database quickly and efficiently. Once the changes are in place, override engine 18 automatically and seamlessly pushes them back into database 20. The result is a database tool which eliminates the query traffic bottleneck of traditional approaches to relational database management systems.

Abstract

L'invention concerne un système et un procédé d'entreposage de données. Ce système d'entreposage de données comprend une mémoire et un processeur. La mémoire comporte une base de données possédant plusieurs entrées de données. Le processeur comprend une antémémoire ainsi qu'un moteur prioritaire. L'antémémoire comporte un sous-ensemble de plusieurs entrées de données, et le moteur prioritaire extrait des données à partir de l'antémémoire, aux fins de visualisation par un utilisateur, modifie les données en réponse à une ou plusieurs commandes de l'utilisateur et conserve les commandes de l'utilisateur dans un fichier, aux fins d'application ultérieure à la base de données.
PCT/US1999/009633 1998-05-01 1999-05-03 Systeme et procede de mise a jour d'une base de donnees multidimensionnelle WO1999057658A1 (fr)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
US7128398A 1998-05-01 1998-05-01
US09/071,283 1998-05-01

Publications (1)

Publication Number Publication Date
WO1999057658A1 true WO1999057658A1 (fr) 1999-11-11

Family

ID=22100375

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/US1999/009633 WO1999057658A1 (fr) 1998-05-01 1999-05-03 Systeme et procede de mise a jour d'une base de donnees multidimensionnelle

Country Status (1)

Country Link
WO (1) WO1999057658A1 (fr)

Cited By (11)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2001080095A2 (fr) * 2000-04-17 2001-10-25 Brio Technology, Inc. Serveur analytique comprenant un moteur de mesures
US6643661B2 (en) 2000-04-27 2003-11-04 Brio Software, Inc. Method and apparatus for implementing search and channel features in an enterprise-wide computer system
US6732115B2 (en) 2000-04-27 2004-05-04 Hyperion Solutions Corporation Chameleon measure and metric calculation
US6748394B2 (en) 2000-04-27 2004-06-08 Hyperion Solutions Corporation Graphical user interface for relational database
EP1505516A1 (fr) * 2003-08-04 2005-02-09 Sap Ag Requête basée sur des méta-donnés
US6941311B2 (en) 2000-04-27 2005-09-06 Hyperion Solutions Corporation Aggregate navigation system
US7072897B2 (en) 2000-04-27 2006-07-04 Hyperion Solutions Corporation Non-additive measures and metric calculation
US7080090B2 (en) 2000-04-27 2006-07-18 Hyperion Solutions Corporation Allocation measures and metric calculations in star schema multi-dimensional data warehouse
US7167859B2 (en) 2000-04-27 2007-01-23 Hyperion Solutions Corporation Database security
US7730031B2 (en) 2000-03-01 2010-06-01 Computer Associates Think, Inc. Method and system for updating an archive of a computer file
US8082182B1 (en) 2001-04-09 2011-12-20 Priceline.Com Incorporated Apparatus, method and system for demand reporting and affectation

Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
EP0420419A1 (fr) * 1989-09-20 1991-04-03 Hitachi, Ltd. Procédé et dispositif de traitement en ligne de données transactionnelles
WO1998009238A1 (fr) * 1996-08-27 1998-03-05 At & T Corp. Procede et systeme utilisant des vues materialisees pour evaluer des requetes faisant intervenir une logique d'agregation
US5745904A (en) * 1996-01-12 1998-04-28 Microsoft Corporation Buffered table user index

Patent Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
EP0420419A1 (fr) * 1989-09-20 1991-04-03 Hitachi, Ltd. Procédé et dispositif de traitement en ligne de données transactionnelles
US5745904A (en) * 1996-01-12 1998-04-28 Microsoft Corporation Buffered table user index
WO1998009238A1 (fr) * 1996-08-27 1998-03-05 At & T Corp. Procede et systeme utilisant des vues materialisees pour evaluer des requetes faisant intervenir une logique d'agregation

Non-Patent Citations (2)

* Cited by examiner, † Cited by third party
Title
"Oracle announces Beta of Oracle Discoverer 3.1", BITECOMM RELEASES, 26 March 1998 (1998-03-26), pages 1 - 2, XP002115172, Retrieved from the Internet <URL:http://www.bitecomm.co.uk/releases/oracle/98-2-26-18897.html> [retrieved on 19990913] *
CHAUDHURI S ET AL: "An overview of data warehousing and OLAP technology", SIGMOD RECORD, MARCH 1997, ACM, USA, vol. 26, no. 1, pages 65 - 74, XP002115173, ISSN: 0163-5808 *

Cited By (20)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7730031B2 (en) 2000-03-01 2010-06-01 Computer Associates Think, Inc. Method and system for updating an archive of a computer file
WO2001080095A2 (fr) * 2000-04-17 2001-10-25 Brio Technology, Inc. Serveur analytique comprenant un moteur de mesures
WO2001080095A3 (fr) * 2000-04-17 2002-11-28 Brio Technology Inc Serveur analytique comprenant un moteur de mesures
US7890546B2 (en) 2000-04-17 2011-02-15 Hyperion Solutions Corporation Analytical server including metrics engine
US6662174B2 (en) 2000-04-17 2003-12-09 Brio Software, Inc. Analytical server including metrics engine
US7031953B2 (en) 2000-04-17 2006-04-18 Hyperion Solutions Corporation Analytical server including metrics engine
US6941311B2 (en) 2000-04-27 2005-09-06 Hyperion Solutions Corporation Aggregate navigation system
US7080090B2 (en) 2000-04-27 2006-07-18 Hyperion Solutions Corporation Allocation measures and metric calculations in star schema multi-dimensional data warehouse
US8261271B2 (en) 2000-04-27 2012-09-04 Oracle International Corporation Method and apparatus for processing jobs on an enterprise-wide computer system
US6643661B2 (en) 2000-04-27 2003-11-04 Brio Software, Inc. Method and apparatus for implementing search and channel features in an enterprise-wide computer system
US6832263B2 (en) 2000-04-27 2004-12-14 Hyperion Solutions Corporation Method and apparatus for implementing a dynamically updated portal page in an enterprise-wide computer system
US6748394B2 (en) 2000-04-27 2004-06-08 Hyperion Solutions Corporation Graphical user interface for relational database
US7072897B2 (en) 2000-04-27 2006-07-04 Hyperion Solutions Corporation Non-additive measures and metric calculation
US6732115B2 (en) 2000-04-27 2004-05-04 Hyperion Solutions Corporation Chameleon measure and metric calculation
US7167859B2 (en) 2000-04-27 2007-01-23 Hyperion Solutions Corporation Database security
US7266821B2 (en) 2000-04-27 2007-09-04 Hyperion Solutions Corporation Method and apparatus for processing jobs on an enterprise-wide computer system
US8082182B1 (en) 2001-04-09 2011-12-20 Priceline.Com Incorporated Apparatus, method and system for demand reporting and affectation
EP1505516A1 (fr) * 2003-08-04 2005-02-09 Sap Ag Requête basée sur des méta-donnés
WO2005015435A3 (fr) * 2003-08-04 2005-05-06 Sap Ag Procede de transfert de donnees d'une source de donnees a un collecteur de donnees
WO2005015435A2 (fr) * 2003-08-04 2005-02-17 Sap Ag Procede de transfert de donnees d'une source de donnees a un collecteur de donnees

Similar Documents

Publication Publication Date Title
US6581068B1 (en) System and method for instant consolidation, enrichment, delegation and reporting in a multidimensional database
US5940818A (en) Attribute-based access for multi-dimensional databases
US7015911B2 (en) Computer-implemented system and method for report generation
US8364724B2 (en) Computer systems and methods for visualizing data
US6122636A (en) Relational emulation of a multi-dimensional database index
US7660823B2 (en) Computer-implemented system and method for visualizing OLAP and multidimensional data in a calendar format
US6205447B1 (en) Relational database management of multi-dimensional data
US7505888B2 (en) Reporting model generation within a multidimensional enterprise software system
US5905985A (en) Relational database modifications based on multi-dimensional database modifications
US9639814B2 (en) Automated default dimension selection within a multidimensional enterprise software system
US5978796A (en) Accessing multi-dimensional data by mapping dense data blocks to rows in a relational database
US7610300B2 (en) Automated relational schema generation within a multidimensional enterprise software system
US9075859B2 (en) Parameterized database drill-through
US20090024660A1 (en) Automatically moving annotations associated with multidimensional data between live datacubes
US7243106B2 (en) Static drill-through modelling
US20120005151A1 (en) Methods and systems of content development for a data warehouse
US7937415B2 (en) Apparatus and method for stripping business intelligence documents of references to unused data objects
US20010037228A1 (en) System and method for using metadata to flexibly analyze data
EP1450274A2 (fr) Cube partitionné à partir de dimensions
Dinter et al. The OLAP market: state of the art and research issues
WO1999057658A1 (fr) Systeme et procede de mise a jour d&#39;une base de donnees multidimensionnelle
Milosevic et al. Big data management processes in business intelligence systems
Breitner Data Warehousing and OLAP: Delivering Just-In-Time Information for Decision Support
Mangaiyarkkarasi On Line Analytical Processing–An Overview
Anagha et al. Design and Development of Data Warehousing for Bookstore Using Pentaho BI Tools

Legal Events

Date Code Title Description
AL Designated countries for regional patents

Kind code of ref document: A1

Designated state(s): AT BE CH CY DE DK ES FI FR GB GR IE IT LU MC NL PT SE

121 Ep: the epo has been informed by wipo that ep was designated in this application
DFPE Request for preliminary examination filed prior to expiration of 19th month from priority date (pct application filed before 20040101)
122 Ep: pct application non-entry in european phase