三招設(shè)計復雜ERP報表

　　有一次，一家企業(yè)向我提出了如下需求：他們希望能夠出一份報表，報表的內(nèi)容包括四個部分。一是成品零件的用量、零件的最小采購量等信息;二是當月零件的采購量信息(詳細的采購訂單等資料);三是當月零件的出庫信息(詳細的出貨記錄);四是零件的安全庫存信息。然后，根據(jù)這些信息計算出當月需要補下的滿足安全庫存的數(shù)量。從這個需求中可以看出，其主要設(shè)計到產(chǎn)品基本資料、采購、倉庫等三個模塊的內(nèi)容。這么復雜的報表，從技術(shù)上來說，實現(xiàn)的難度并不是很大。但是從實用性角度考慮，或者從準確性來看，又會有什么結(jié)果呢?

　　一、報表越復雜，準確性越難以把握

　　一般來說，報表越復雜，其準確性余越難以把握。其實拋開ERP系統(tǒng)，從統(tǒng)計學的角度，我們也可以得出這個結(jié)論。如下圖所示，現(xiàn)在有三個抽屜。每個抽屜中都有0-9十個數(shù)字。如果現(xiàn)在從每個抽屜中隨意抽出兩個數(shù)字，最后組成一個三位數(shù)。那么最后有幾種結(jié)構(gòu)呢?這是一個排列組合的問題。

　　再回過頭來看一下這個表單的內(nèi)容�，F(xiàn)在這個表單有三個模塊的數(shù)據(jù)構(gòu)成。就好像這三個抽屜。當然其抽屜中的數(shù)據(jù)遠比10個數(shù)字要負載的多。我們設(shè)想一下，從單個模塊來看�？赡芷髽I(yè)允許的誤差率是5%。即100條記錄中，允許有5條記錄與實際有偏差�，F(xiàn)在三部分信息共同組成的一張報表，而且最后需要根據(jù)三部分信息的內(nèi)容計算出一個值，那么這個出現(xiàn)錯誤的記錄會有多少呢?這又是一個排列組合的問題。如假設(shè)每部分信息中，都有5條件有偏差，那么最后理論上的錯誤記錄是125條。顯然這個錯誤率比較大。同時也可以看出，當涉及到的基礎(chǔ)表數(shù)量越多，涉及到的模塊越多，其最后結(jié)果的準確性就越難以保障。而當數(shù)據(jù)的準確性不高時，其實用性也就相應的降低。

　　二、報表關(guān)聯(lián)越多，其性能也會直線下降

　　報表越復雜，其涉及到的后臺數(shù)據(jù)庫基礎(chǔ)表也就越多。雖然多表之間的關(guān)聯(lián)查詢是允許的，但是關(guān)聯(lián)的關(guān)鍵字越多，其查詢的效率也就越低。特別是在關(guān)聯(lián)條件中，有時候采用的并不是關(guān)鍵字之間的關(guān)聯(lián)。如有可能日期(字符數(shù)據(jù)類型的關(guān)聯(lián))之間的關(guān)聯(lián)，此時查詢的效率會更低。再加上比較復雜的Where邏輯判斷語句，復雜報表的查詢時間會很長。如筆者測試過，按照上面這個用戶的需求，設(shè)計出的報表其查詢的時間需要近三分鐘，而且是已經(jīng)優(yōu)化過的查詢。另外，這個報表的查詢由于涉及到眾多的基礎(chǔ)表，數(shù)據(jù)庫基本上需要訪問硬盤上的數(shù)據(jù)文件，而不能夠使用緩存。這就有可能會導致比較嚴重的硬盤I/O沖突。從而影響到其它數(shù)據(jù)的查詢效率。

　　故從數(shù)據(jù)庫與應用軟件的整體性能考慮，也不建議采用比較復雜的報表視圖。畢竟性能降低、查詢的時間比較長時，報表的實用性也在降低。

　　三、設(shè)計復雜報表的注意事項

　　為此，從原則上是禁止設(shè)計超過兩個模塊的數(shù)據(jù)報表，最好是將報表的范圍限制在單個模塊下。如此的話，無論從性能還是從數(shù)據(jù)的準確性上都會有所保障。但是，如果用戶確實有需要實現(xiàn)比較復雜的報表，在這種情況下，該如何處理呢?為此筆者根據(jù)自己的項目經(jīng)驗，提出了以下幾個建議。

　　一，是可以使用固化視圖來改善數(shù)據(jù)庫的性能。復雜報表所導致的不利影響，其首當其沖的是報表查詢時速度會很慢，性能很低。為此在涉及到復雜報表時，開發(fā)人員可以考慮采用固化視圖來改善數(shù)據(jù)庫的性能。如在Oracle數(shù)據(jù)庫中，固化視圖又叫做物化視圖。通固化視圖，可以用于預先計算并保存表連接或者聚集等耗時比較多的操作結(jié)果。簡單的說，就將某個報表的查詢結(jié)果存儲在一張單獨的表中。如此的話，在執(zhí)行查詢時，就可以避免使用這些耗時的操作，同時減少磁盤的I/O沖突，從而以最短的時間得到用戶想要的結(jié)果。一般來說，固化視圖對于復雜的報表來說，能夠提供三方面的作用。如可以提高查詢的性能。如固化視圖對于應用來說是透明的，增加和刪除物化視圖不會影響應用程序中SQL語句的正確性和有效性。如當基表發(fā)生變化時，物化視圖也會同時更新。不過需要注意的是，物化視圖也會帶來一些負面影響。如物化視圖的數(shù)據(jù)會保存在硬盤中，為此就會占用額外的存儲空間等�？傊�，在設(shè)計比較復雜的報表時，開發(fā)人員可以與數(shù)據(jù)庫工程師商量，能夠采用固化視圖。如果可以的話，需要盡量采用固化視圖。

　　二，是復雜的報表當設(shè)計到多表時，最好采用模塊化的設(shè)計。如上面這個視圖，其涉及到的基表有近20張。那么在設(shè)計視圖時，要避免將其放在一個SQL語句中。而應該借鑒應用程序的模塊化設(shè)計，將其設(shè)計成不同層次的視圖，然后再進行連接查詢。如上面這個案例，至少可以將其分為四層。最基層是基本數(shù)據(jù)表，第二層是零件出庫信息、當月采購信息等數(shù)據(jù)，第三層是根據(jù)第二層的數(shù)據(jù)進行計算分析;第三層視圖再將這些視圖進行連接。這么操作的話，方便后續(xù)的維護與查詢。同時也可以提高查詢的速度。為什么這么說呢?如在第二層視圖設(shè)計中，可以對基礎(chǔ)表的數(shù)據(jù)進行過濾。此時由于基礎(chǔ)數(shù)據(jù)少，那么后續(xù)的報表查詢速度也會加快。為此對于比較復雜報表的設(shè)計，要考慮分層設(shè)計的思路。以提高報表的查詢性能與靈活性。

　　三，是要考慮數(shù)據(jù)核對的需要。比較復雜的報表，其可能會涉及到多個不同的部門。如上面?zhèn)�報表，其涉及到倉庫、采購、銷售、開發(fā)等多個部門。而且最后的計算結(jié)果需要根據(jù)這些部門的信息得出。為此為了提高數(shù)據(jù)的準確性，就需要多個部分進行積極的配合。那么該如何來做到這一點呢?筆者認為，可以將這些視圖分模塊化設(shè)計。如將涉及到不同的部門的信息先設(shè)計成不同的報表。在某個特定的時刻，如月末，先讓各個部門的人員核對相關(guān)的數(shù)據(jù)。核對完成沒有錯誤之后，再對相關(guān)的數(shù)據(jù)進行運算。而不是一開始就將所有數(shù)據(jù)在一張報表上顯示。這會導致各個部門數(shù)據(jù)核對的麻煩，即各個部門不利于核對與自己相關(guān)的數(shù)據(jù)。其實這一點跟上面提到的視圖分層化設(shè)計類似。在ERP上，報表的內(nèi)容也要分不同的模塊進行體現(xiàn)。這有利于用戶對數(shù)據(jù)進行核對與確認。然后再將它們整合起來。這種各個擊破的方式，就有利于提高數(shù)據(jù)的準確性。

　　可見，對于比較復雜的報表視圖，原則上還是少見為妙。因為其在性能或者數(shù)據(jù)的準確性上都很難控制。如果真的要建立復雜視圖的時候，那么在設(shè)計與開發(fā)時，顧問需要聽取數(shù)據(jù)庫工程師的意見，考慮如何提高數(shù)據(jù)的查詢性能，并采取措施提高數(shù)據(jù)的準確性。

服務(wù)器租用/服務(wù)器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]