FLATTENING MULTI-DIMENSIONAL DATA SETS INTO DE-NORMALIZED FORM

• US 20100131457A1
• Filed: 11/26/2008
• Published: 05/27/2010
• Est. Priority Date: 11/26/2008
• Status: Abandoned Application

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Heimendinger, Scott
• Application Number:
  US12/324,538
• Publication Number:
  US 20100131457A1
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Performance metrics data in a multi-dimensional structure such as a nested scorecard matrix is transformed into a flat structure or de-normalized for efficient querying of individual records. Each dimension and header is converted to a column and data values resolved at intersection of dimension levels through an iterative process covering all dimensions and headers of the data structure. A key corresponding to a tuple representation of each cell or a transform of the tuple may be used to identify rows corresponding to the resolved data in cells for further enhanced query capabilities.

Claims

1. A method to be executed at least in part in a computing device for de-normalizing multi-dimensional data, the method comprising:

receiving data from a multi-dimensional data structure at a processor;

transforming the received data to be provided in a two-dimensional data structure by;

iterating through each column dimension and row dimension hierarchy in the multi-dimensional data structure identifying each unique dimension;

iterating through each column metric and row metric in the multi-dimensional data structure identifying each unique metric;

creating a column for each identified unique dimension and metric; and

creating a value column to include data corresponding to each uniquely identified metric and dimension combination; and

outputting the data in the two-dimensional data structure.

2. The method of claim 1, further comprising:

creating a column in the output two-dimensional data structure for key values, wherein each key value is used to identify a corresponding value cell on the same row in the two-dimensional data structure.

3. The method of claim 2, wherein the key value comprises a composition of a dimensionality of the corresponding value cell in the multi-dimensional data structure.

4. The method of claim 3, further comprising:

generating a hash value from each key value; and

inserting the hash value in place of each corresponding key value.

5. The method of claim 1, wherein the multi-dimensional data structure includes a nested scorecard matrix, and wherein the two-dimensional data structure includes a table.

6. The method of claim 1, wherein transforming the received data further includes determining at least one hierarchy for each column and at least one other hierarchy for each row of the multi-dimensional data structure.

7. The method of claim 1, wherein the data in the value column includes at least one from a set of:

an alphanumeric value, a numeric value, and a graphic value.

8. The method of claim 1, wherein the output two-dimensional data structure is stored for use in at least one from a set of:

an analysis application, a forecast application, and a presentation application.

9. The method of claim 1, wherein the multi-dimensional data structure is a data cube.

10. A computer-readable storage medium with instructions stored thereon for de-normalizing multi-dimensional performance metrics data, the instructions comprising:

generating a two-dimensional output data structure;

determining each unique dimension represented in a column area and in a row area of a multi-dimensional input data structure;

determining each unique dimension hierarchy in the multi-dimensional input data structure;

creating a column for each unique dimension hierarchy in the two-dimensional output data structure;

determining each unique metric represented in the column area and in the row area of the multi-dimensional input data structure;

creating a column for each unique metric in the two-dimensional output data structure; and

creating a value column in the two-dimensional output data structure for representing metric values in the multi-dimensional input data structure.

11. The computer-readable storage medium of claim 10, wherein the columns are created and the unique dimensions and metrics are determined in an iterative process.

12. The computer-readable storage medium of claim 11, wherein the iterative process follows an order of dimensions and metrics as represented in the multi-dimensional input data structure.

13. The computer-readable storage medium of claim 10, wherein the instructions further comprise:

creating a key column in the two-dimensional output data structure for identifying each row;

generating a key value for each row of the key column based on concatenating data from the columns for the dimensions and the columns for the metrics on each row.

14. The computer-readable storage medium of claim 13, wherein the instructions further comprise:

applying a unique transformation to the data in the key column; and

replacing the key values with transformed values, wherein the transformed values are shorter than the key values.

15. The computer-readable storage medium of claim 10, wherein the dimensions include at least one from a set of:

an organizational unit, an organization geography, and a time period.

16. The computer-readable storage medium of claim 10, wherein the multi-dimensional input data structure is a collapsible nested data matrix.

17. A system for de-normalizing multi-dimensional scorecard data, the system comprising:

a data store for storing two-dimensional and multi-dimensional data;

a server including a memory and a processor coupled to the memory, the processor configured to;

iteratively transform scorecard data stored in a multi-dimensional input data structure by;

determining dimensions and metrics along a column and a row of the multi-dimensional input data structure;

creating columns for each of the dimensions and metrics in a two-dimensional output data structure; and

creating a value column in the two-dimensional output data structure representing data in cells uniquely defined by combinations of the dimensions and metrics;

a client device for executing a client application, the client application configured to;

provide input data and configuration parameters for scorecard computations;

receive at least a portion of the two-dimensional output data structure; and

perform user requested operations on the received portion of the two-dimensional output data structure.

18. The system of claim 17, wherein the processor is further configured to iteratively transform the scorecard data by:

determining whether a dimension includes a sub-dimension hierarchy; and

if the dimension includes a sub-dimension hierarchy, creating columns for each sub-dimension in the two-dimensional output data structure.

19. The system of claim 17, wherein the two-dimensional output data structure includes one of a table and a two-dimensional array.

20. The system of claim 17, wherein the data in the multi-dimensional input data structure is received from a two-dimensional data source.