CN101944116B - 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 complex multi-dimensional hierarchical 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 that especially relates to a kind of low granularity data becomes efficient Materialized View method with assembling to process next life, belongs to field of computer technology.

Background technology

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

Mainly contain at present the realization that MOLAP (multi-dimensional OLAP) and ROLAP (relational OLAP) dual mode can be used for the OLAP inquiry.In recent years, people have carried out a large amount of research work aspect ROLAP, and the response speed that some technology improve the ROLAP inquiry has been proposed, such as new index technology, material objectization view techniques, sampling (sampling) optimisation technique etc., do not support packet aggregation to operate, can only be applied to the weak points such as simple particular model but all exist when a lot of method uses 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 dimension hierarchical and aggregation operator change into the site polling on the fact table, thereby when processing the dimension hierarchical gathering, improve the efficient that connects and assemble.

For solving the problems of the technologies described above, the invention provides connection and the method for congregating of complex multi-dimensional hierarchical in a kind of data warehouse.

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

A hierarchical tree H-Tree of complex multi-dimensional hierarchical is a DAG (directed acyclic graph) take ALL as root node, available two tuples

Expression.π={ ALL, π wherein ₁, π ₂..., π n} is 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,

Be in the entity set that each members on the same level represents not overlapped.

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

Every sub-member

Give a mutually different orderly code bit, 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 time and the space consuming of level aggregation operator, the present invention has taked the method for associated agency.

The degree of depth be on the hierarchical tree H-Tree of γ γ+1 ordered set 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 upper each member's of hierarchical tree H-Tree associated agency value 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{ifi}=1\\ f(H,\mathrm{parent}\left({\mathrm{\χ}}_j^i\right))\⊕\mathrm{BOrdparent}\left({\mathrm{\χ}}_j^i\right)\left({\mathrm{\χ}}_j^i\right),& \mathrm{ifi}\≠1\end{array}\right.$

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

The member

Sub-member collection be

Order

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

If the member is χ _p, χ _qThe same layer ξ that is subordinate to hierarchical tree H-Tree ⁱ, their corresponding layer spans so Must equate, thereby the member is χ _pAnd χ _qCode length be consistent.Therefore, can carry out with a kind of binary coding mode of compression each member's of unified management associated agency value.

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}$

$\left({\mathrm{\χ}}^t\right)_{\mathrm{arent}\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 connected connection and has been proposed a kind of connection based on complex multi-dimensional hierarchical and aggregation algorithms JACMDH with aggregation operator from optimizing the dimension table with fact table.The core concept of JACMDH algorithm is:

1) constraint on each dimension of dimension hierarchical is converted to site polling by hierarchy combined surrogate, and the attribute value set that satisfies condition is put into temporary table;

2) according to packet attributes ranking results collection;

3) according to bit join index, 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 reaches:

The present invention is connected connection and aggregation operator and has been proposed a kind of new data warehouse connection and method for congregating-JACMDH (Join and Aggregation based on the Complex Multi-Dimensional Hierarchies) based on complex multi-dimensional hierarchical from optimizing the dimension table with fact table.This method has taken into full account the characteristics of complex multi-dimensional hierarchical, on the basis of original bit join index (Bitmap Join Index), adopt hierarchy combined surrogate (Hierarchy Combined Surrogate) and the method for packet sequencing in advance, so that the connection on the complicated dimension hierarchical and aggregation operator change into the site polling on the fact table, thereby when processing the dimension hierarchical gathering, 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.

The below provides the concrete steps of the inventive method:

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, bit join index

The gathering attribute is Aggr (A);

Output: the 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 corresponding 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, come packet sequencing temporary table Temp with the K_ary merge algorithm _j

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

(251) according to bit join index

To each the group in every the record corresponding

In row carry out the OR operation, thereby the bitmap Bm that is respectively divided into groups _Jk

(252) with packet attributes GA _jEach minute class value and the bitmap Bm of each grouping _JkTuple (the GA that consists of _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 corresponding 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 by the aggregate function of expectation, and structure is inserted among the cluster metric Table A gg_Mes_table;

(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 based on complex multi-dimensional hierarchical, and carried out algorithm experimental.The used environment of this example is PIII667 (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 in two kinds of situation in this example the performance of the inventive method.

(1) in 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 Ratio of epidemic algorithms.When aggregate function was record counter COUNT, because the JACMDH algorithm only needs bit join index execution OR and AND operation are got final product, this moment, the performance raising was the most obvious, was about 42%.

(2) in 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 Ratio.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 to this.So, if those skilled in the art or researchist are enlightened by it, in the situation that do not break away from the invention aim, adopt other similar method all should belong to protection scope of the present invention.

Claims (3)

1. connection and the method for congregating of complex multi-dimensional hierarchical in the data warehouse is characterized in that, may further comprise the steps:

1) constraint on each dimension of dimension hierarchical is converted to site polling by hierarchy combined surrogate, and the attribute value set that satisfies condition is put into temporary table;

2) according to packet attributes ranking results collection;

3) according to bit join index, 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;

Its described step 1) be: 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, bit join 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;

Its described step 2) be:

21) j is when 1 arrives in the m scope, and inquiry Q is carried out in circulation _jGet querying condition Cq _j, search coded file CS _jMust this condition field corresponding associated agency coding ω;

22) arrive l_m (CS for i 1 _j)-l_o (CS _j) when scope was interior, following two statements were carried out in circulation:

With the attended operation assignment of ω and character " 0 " to ω #;

With the attended operation assignment of ω and character " 1 " to ω ##;

Wherein, l_m (CSj) and l_o (CSj) are respectively the maximum code length of coded file CSj and the code length of associated agency coding ω;

23) record of selecting all to be coded between ω # and the ω ## is inserted into temporary table Temp _jIn;

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

25) arrive Temp for k 1 _jWhen the group of middle grouping is counted in the scope, circulation executive basis bit join index

To each the group in every the record corresponding In row carry out the OR operation, thereby the bitmap Bm that is respectively divided into groups _JkOtherwise with packet attributes GA _jEach minute class value and the bitmap Bm of each grouping _JkTuple (the GA that consists of _j, Bm _Jk) be inserted into temporary table #Temp _jIn.

2. connection and the method for congregating of complex multi-dimensional hierarchical in a kind of data warehouse according to claim 1, it is characterized in that, in described step 3) in, according to the PsJoin join algorithm to m temporary table #Temp1, packet attributes among the #Tempm connects, and their corresponding bitmaps are carried out the AND operation, and to delete those bitmap vectors be 0 tuple entirely, obtain a new table Grp_Agg_tab (GA1,, GAm, Grp_Bitmap).

3. connection and the method for congregating of complex multi-dimensional hierarchical in a kind of data warehouse according to claim 2, it is characterized in that, in described step 4) in, 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 structure is inserted among the cluster metric Table A gg_Mes_table.

Images