CN101944116A - Complex multi-dimensional hierarchical connection and aggregation method for data warehouse - Google Patents

Abstract

The invention relates to a method for reducing connection and aggregation operation in a data warehouse, which comprises the following steps of: 1) converting limitations on each dimension of multi-dimensional hierarchy into area query by hierarchical joint agents, and placing attribute value sets meeting condition into a temporary table; 2) sequencing result sets according to packet attributes; 3) acquiring bitmaps of each packet according to bitmap connection indexes; and 4) selecting records in a fact table according to bits set to 1 in the bitmaps of each packet, and computing the records by using an expected aggregate function. The method has the advantages of remarkably improving the connection and aggregation efficiency at the same time of processing multi-dimensional hierarchical aggregation.

Description

The connection of complicated multidimensional level and method for congregating in a kind of data warehouse

Technical field

The present invention relates to the querying method in the on-line analysis processing in a kind of data warehouse, the connection and the gathering processing that especially relate to a kind of low granularity data generate Materialized View method efficiently, belong to field of computer technology.

Background technology

Generate efficiently by the prefocus of the low granularity data in the data warehouse is handled that Materialized View is an important technology of on-line analytical processing (OLAP), and the OLAP operation generally all relates to the extemporaneous complex query of mass data.The user analyzes data by submitting the OLAP inquiry to, and aid decision making needs inquiry response speed faster usually.The performance that improves the OLAP query processing is that the key of data warehouse field studies a question.

Mainly contain MOLAP (multi-dimensional OLAP) and ROLAP (relational OLAP) dual mode at present and can be used for the realization that OLAP inquires about.In recent years, people have carried out number of research projects aspect ROLAP, and the response speed that some technology improve the ROLAP inquiry has been proposed, as new index technology, material objectization view techniques, sampling (sampling) optimisation technique etc.,, a lot of method do not support packet aggregation to operate, can only be applied to weak points such as simple particular model but all existing when using these technology to solve the OLAP query manipulation.

Summary of the invention

Technical matters to be solved by this invention provides the data processing method in a kind of data warehouse, can realize that connection on the complicated multidimensional level and aggregation operator change into the site polling on the fact table, thereby when handling the gathering of multidimensional level, improve the efficient that connects and assemble.

For solving the problems of the technologies described above, the invention provides the connection and the method for congregating of complicated multidimensional level in a kind of data warehouse.

Among the present invention, OLAP operation is in conjunction with the attribute of a plurality of dimensions, and the level associated agency of a certain concrete dimension is expanded to the situation that can be applicable to a plurality of dimensions.

It is the DAG (directed acyclic graph) of root node with ALL that a hierarchical tree H-Tree of complicated multidimensional level is one, available two tuples

Expression.π={ ALL, π wherein ₁, π ₂..., π n} is a node set among the Γ,

It is oriented line set among the Γ.

If the codomain of dimension D is

The degree of depth of corresponding hierarchical tree H-Tree is designated as γ, and then it has the orderly collection of sets of γ+1 layer, is designated as П={ ξ ⁰, ξ ¹..., ξ ^γ.If λ=(χ ₁, χ ₂..., χ _m) satisfy following condition, claim that then λ is the i layer (ξ of 0≤i≤γ) of hierarchical tree H-Tree ⁱMember group:

①depth(χ _j)＝i；(1≤j≤m)

②

③ξ ⁱ＝∪0≤j≤mχ _j；

4. right

χ _q∈ ξ ⁱAnd χ _p≠ χ _q, then

Depth (χ wherein _j) be the degree of depth of χ, (1≤j≤m) brief note is j member of i layer

Obviously,

It is not overlapped to be in the represented entity set of each member on the same level.

The member

Sub-member collection be defined as: $\mathrm{children}\left({\mathrm{\χ}}_j^i\right)=\{{\mathrm{\χ}}_l^{i+1}\∈{\mathrm{\ξ}}^{i+1}|{\mathrm{\χ}}_l^{i+1}\⊆x_j^{i.}\}.$

The member Father member collection be defined as: $\mathrm{parent}\left({\mathrm{\χ}}_j^i\right)=\{{\mathrm{\χ}}_k^{i-1}\∈{\mathrm{\ξ}}^{i-1}|{\mathrm{\χ}}_k^{i-1}\⊆{\mathrm{\χ}}_j^i\}.$

If Then defining bijective function BOrd χ is: Bijective function BOrd χ is the member

Each sub-member

Give a mutually different orderly sign indicating number position, thereby defined a kind of coding mode.

In order effectively complicated level to be encoded, thereby reduce the connection of a plurality of dimension tables and the time and the space consuming of level aggregation operator, the present invention has taked the method for associated agency.

The degree of depth is that γ+1 ordered set on the hierarchical tree H-Tree of γ is ξ ⁰, ξ ¹..., ξ ^γ, ξ ⁱ(m the member of 0≤i≤γ) is designated as

Give the member

Sub-member collection

Bijective function be The member

Agency value

With his father member

Agency value

Between connection be designated as

The present invention is defined in the associated agency value that hierarchical tree H-Tree goes up each member with recursive form:

$f(H,{\mathrm{\χ}}_j^i)=\left\{\begin{array}{cc}\mathrm{BOrdparent}\left({\mathrm{\χ}}_j^i\right)\left({\mathrm{\χ}}_j^i\right),& \mathrm{if\; i}=1\\ f(H,\mathrm{parent}\left({\mathrm{\χ}}_j^i\right))\⊕\mathrm{BOrdparent}\left({\mathrm{\χ}}_j^i\right)\left({\mathrm{\χ}}_j^i\right),& \mathrm{if\; i}\≠1\end{array}\right.$

The degree of depth is that γ+1 ordered set on the hierarchical tree H-Tree of γ is ξ ⁰, ξ ¹..., ξ ^γ, ξ ⁱ(m the member of 0≤i≤γ) is designated as

The member Sub-member collection be

Order

Wherein Card is a function of getting aggregate capacity.Then our the layer span that define the i+1 layer is

If member χ _p, χ _qThe same one deck ξ that is subordinate to hierarchical tree H-Tree ⁱ, their pairing layer spans so

Must equate, thereby the member is χ _pAnd χ _qCode length be consistent.Therefore, can carry out each member's of unified management associated agency value with a kind of binary coding mode of compression.

If the member χ of t the layer of path Φ traversal hierarchical tree H-Tree ⁱ, χ ²..., χ ^t, give the member χ ⁱ(the sub-member of 1≤i≤t) collects children (χ ⁱ) bijective function be

The associated agency value that then defines path Φ is:

$f(H,\mathrm{\Φ})=f(H,{\mathrm{\χ}}^t)={\mathrm{BOrd}}_{\mathrm{parent}\left({\mathrm{\χ}}^1\right)}\left({\mathrm{\χ}}^1\right)+{\mathrm{BOrd}}_{\mathrm{parent}\left({\mathrm{\χ}}^2\right)}\left({\mathrm{\χ}}^2\right)2^{\∈i}$

${}_{\mathrm{arent}\left({\mathrm{\χ}}^t\right)}\left({\mathrm{\χ}}^t\right)\·2^{\∈i+\∈2+...+\∈t-1}$

When by each node on the said method code storage hierarchical tree H of the present invention, advantage is: can enough less and unified figure places store more data, and reduce the time overhead of searching for the record that satisfies condition, thereby improve the efficient of connection and aggregation operator.

The present invention is from optimizing the dimension table and being connected with aggregation operator of fact table having proposed a kind of being connected and aggregation algorithms JACMDH based on complicated multidimensional level.The core concept of JACMDH algorithm is:

1) constraint on each dimension of multidimensional level is converted to site polling by the level associated agency, and the attribute value set that satisfies condition is put into temporary table;

2) according to packet attributes ranking results collection;

3) connect index according to bitmap, obtain the bitmap of each grouping;

4) according to put 1 in the bitmap of each grouping, choose the record in the fact table, and calculate them by the aggregate function of expectation;

5) deletion temporary table.

The beneficial effect that the present invention reached:

The present invention is from optimizing the dimension table and being connected with aggregation operator of fact table having proposed being connected and method for congregating-JACMDH (Join and Aggregation based on the Complex Multi-Dimensional Hierarchies) based on complicated multidimensional level a kind of new data warehouse.This method has taken into full account the characteristics of complicated multidimensional level, connect at original bitmap on the basis of index (Bitmap Join Index), adopt the level associated agency (Hierarchy Combined Surrogate) and the method for packet sequencing in advance, connection on the multidimensional level of feasible complexity and aggregation operator change into the site polling on the fact table, thereby when handling the gathering of multidimensional level, improved the efficient that connects and assemble.

Description of drawings

Fig. 1 is the list structure figure of example of the present invention;

The performance comparison diagram of JACMDH algorithm and present epidemic algorithms when Fig. 2 changes for dimension table record number;

The performance comparison diagram of JACMDH algorithm and present epidemic algorithms when Fig. 3 fact table record number changes.

Embodiment

Below in conjunction with specific embodiment the inventive method is elaborated.

Provide the concrete steps of the inventive method below:

Input: fact table FT, dimension table DT ₁..., DT _m, packet attributes GA ₁..., GA _m, the associated agency coded file CS of hierarchical tree H-Tree ₁..., CS _m, bitmap connects index

The gathering attribute is Aggr (A);

Output: cluster metric Table A gg_Mes_table (GA with packet attributes ₁..., GA _m, M ₁..., M _v);

(1) initial query Q is resolved into one-dimensional inquiry Q ₁..., Q _m, Q wherein _j(1≤j≤m) is to dimension table DT _jSimple queries, only comprise among the former inquiry Q and dimension table DT _jRelevant querying condition and relevant field;

(2)For?j＝l?to?m

(21) for inquiry Q _jGet querying condition Cq _j, search coded file CS _jMust this condition field pairing associated agency coding ω;

(22)For?i＝1?to?(l_m(CS _j)-l_o(CS _j))

(221)ω ^#＝ω||″0″；

(222)ω ^##＝ω||″l″；

(23) select all to be coded in ω ^#And ω ^##Between record be inserted into temporary table Temp _jIn;

(24) according to inquiry Q _jIn packet attributes GA _j, use the K_ary merge algorithm to come packet sequencing temporary table Temp _j

(25) For k=1 to Comp _j//Comp equals Temp _jThe group number of middle grouping

(251) connect index according to bitmap

To each the group in every the record pairing In row carry out the OR operation, thereby obtain the bitmap Bm of each grouping _Jk

(252) with packet attributes GA _jEach minute class value and the bitmap Bm of each grouping _JkTuple (the GA that constitutes _j, Bm _Jk) be inserted into temporary table #Tem _jIn;

(3) according to the PsJoin join algorithm to m temporary table #tem ₁..., #tem _mIn packet attributes connect, and their pairing bitmaps are carried out the AND operation, and to delete those bitmap vectors be 0 tuple entirely, obtain a new table Grp_Agg_tab (GA ₁..., GA _m, Grp_Bitmap);

(4) according to put 1 in the bitmap of each grouping, choose the record in the fact table, and calculate them, and structure is inserted among the cluster metric Table A gg_Mes_table by the aggregate function of expectation;

(5) deletion temporary table Temp ₁..., Temp _m, #Temp ₁..., #Temp _m, Grp_Agg_tab.

In the research of OLAP query manipulation, realized connection and aggregation algorithms JACMDH, and carried out the algorithm experiment based on complicated multidimensional level.The used environment of this example is PIII667 (a 128M internal memory), and what database used is the Oracle9i system.

The list structure of using in this example as shown in Figure 1.The record size of dimension table and fact table is respectively 106 and 148 bytes, their record number is respectively 800000 and 6000000, selectance Sel_r=0.05, the participation rate Par_r=0.8 of connection, disk block size Siz_bk=4Kbytes, the disk block that divides into groups that is used for sorting is counted Num_s=100.

OLAP query script with aggregation operator is as follows:

SELECT P.brand，St.name，C.income，T.month，Sum(S.cost)

FROM Product?p，Store?St，Customer?C，Time?T，Sales?S

WHERE (P.product_k＝S.product_k)AND(St.store_k＝S.store_k)

AND(C.customer_k＝S.customer_k)AND(T.time_k＝S.time_k)

AND(P.category＝‘Food’)AND(St.manager＝‘Smith’)

AND(C.education＝‘College’)

GROUP?BY P.brand，St.name，C.income，T.month

Assess the performance of the inventive method in this example in two kinds of situation.

(1) under the constant situation of fact table record number (6000000), dimension table record number increases to 800000 from 100000, and accompanying drawing 2 has shown JACMDH algorithm and at present than the performance of epidemic algorithms relatively.When aggregate function is record counter COUNT, because only needing the bitmap connecting strand drawn, the JACMDH algorithm carries out OR and the AND operation get final product, this moment, the performance raising was the most obvious, was about 42%.

(2) under the constant situation of dimension table record number (800000), the fact table record increases to 6000000 from 1000000, accompanying drawing 3 shown the JACMDH algorithm and at present epidemic algorithms performance relatively.When aggregate function was record counter COUNT, the JACMDH algorithm improved 18% than current algorithm performance.

Described K_ary merge algorithm is seen: SHEN Xiao-jun and HU Qing.Efficient embedding k-ary complete trees into hypercubes parallel proceaaing Symposium.Proc.of the 9 int ' 1 Conf.on data Engineeing Los Alamitos:IEEE computerSociety press, 1996:24-31.

Described PsJoin join algorithm is seen:

K?SIN?HT，K?YUN-HT，K?SANG-W?OOK，et?al.Improving?the?Proccessing?of?Queries?in?Data?Warehousing?Environment.Proc.of?the?9?int’1?Conf.ondatabase?and?Expert Systems?Applications.?New York：Springer2002：669-675.

Below schematically embodiment of the present invention is set forth, this elaboration does not have limitation.Shown in the accompanying drawing also is basic embodiment of the present invention, is not limited thereto.So,, under the situation that does not break away from the invention aim, adopt other similar method all should belong to protection scope of the present invention if those skilled in the art or researchist are enlightened by it.

Claims (5)

1. the connection and the method for congregating of complicated multidimensional level in the data warehouse is characterized in that, may further comprise the steps:

1) constraint on each dimension of multidimensional level is converted to site polling by the level associated agency, and the attribute value set that satisfies condition is put into temporary table;

2) according to packet attributes ranking results collection;

3) connect index according to bitmap, obtain the bitmap of each grouping;

4) according to put 1 in the bitmap of each grouping, choose the record in the fact table, and calculate them by the aggregate function of expectation;

5) deletion temporary table.

2. the connection and the method for congregating of complicated multidimensional level is characterized in that in a kind of data warehouse according to claim 1, in described step 1),

For fact table FT, dimension table DT ₁..., DT _m, packet attributes GA ₁..., GA _m, the associated agency coded file CS of hierarchical tree H-Tree ₁..., CS _m, bitmap connects index Assemble attribute Aggr (A), initial query Q resolved into one-dimensional inquiry Q1 ..., Qm, wherein (1≤j≤m) is to the simple queries of dimension table DTj to Qj, only comprises querying condition and the relevant field relevant with dimension table DTj among the former inquiry Q.

3. the connection and the method for congregating of complicated multidimensional level is characterized in that in a kind of data warehouse according to claim 2, in described step 2) in,

For?j＝1?to?m

1) gets querying condition Cqj for inquiry Qj, search must this condition field pairing associated agency coding of coded file CSj ω;

2)For?i＝l?to?(1_m(CSj)-1_o(CSj))

21)ω#＝ω||″0″；

22)ω##＝ω||″1″；

3) record of selecting all to be coded between ω # and the ω ## is inserted among the temporary table Tempj;

4), use the K_ary merge algorithm to come packet sequencing temporary table Tempj according to the packet attributes GAj among the inquiry Qj;

5) For k=l to Compj // group number that Comp equals to divide into groups among the Tempj,

51) connect index according to bitmap

To each the group in every the record pairing In row carry out the OR operation, thereby obtain the bitmap Bmjk of each grouping;

52) with packet attributes GAj each minute class value and the tuple that constitutes of the bitmap Bmjk of each grouping (GAj Bmjk) is inserted among the temporary table #Temj.

4. the connection and the method for congregating of complicated multidimensional level in a kind of data warehouse according to claim 3, it is characterized in that, in described step 3), according to the PsJoin join algorithm to m temporary table #teml, packet attributes among the #temm connects, and their pairing bitmaps are carried out the AND operation, and to delete those bitmap vectors be 0 tuple entirely, obtain a new table Grp_Agg_tab (GAl,, GAm, Grp_Bitmap).

5. the connection and the method for congregating of complicated multidimensional level in a kind of data warehouse according to claim 4, it is characterized in that, in described step 4), according to put 1 in the bitmap of each grouping, choose the record in the fact table, and calculate them by the aggregate function of expectation, and just structure is inserted among the cluster metric Table A gg_Mes_table.

Images