數(shù)據(jù)倉庫規(guī)范_qdh

1、數(shù)據(jù)倉庫規(guī)范一 數(shù)據(jù)倉庫層次結(jié)構(gòu)規(guī)范1.1 基本分層結(jié)構(gòu)系統(tǒng)的信息模型從存儲的內(nèi)容方面可以分為，STAGE接口信息模型、ODS/DWD信息模型，MID信息模型、DM信息模型、元數(shù)據(jù)信息模型。在各個信息模型中存儲的內(nèi)容如下描述：1) SRC接口層信息模型：提供業(yè)務(wù)系統(tǒng)數(shù)據(jù)文件的臨時存儲，數(shù)據(jù)稽核，數(shù)據(jù)質(zhì)量保證，屏蔽對業(yè)務(wù)系統(tǒng)的干擾，對于主動數(shù)據(jù)采集方式，以文件的方式描述系統(tǒng)與各個專業(yè)子系統(tǒng)之間數(shù)據(jù)接口的內(nèi)容、格式等信息。與該模型對應(yīng)的數(shù)據(jù)是各個專業(yè)系統(tǒng)按照該模型的定義傳送來的數(shù)據(jù)文件。STAGE 是生產(chǎn)系統(tǒng)數(shù)據(jù)源的直接拷貝，由ETL過程對數(shù)據(jù)源進(jìn)行直接抽取，在格式和數(shù)據(jù)定義上不作任何改變。與生產(chǎn)

2、系統(tǒng)數(shù)據(jù)的唯一不同是，STAGE層數(shù)據(jù)具有時間戳。 STAGE層存在的意義在于兩點(diǎn)：（1） 對數(shù)據(jù)源作統(tǒng)一的一次性獲取，數(shù)據(jù)倉庫中其他部分都依賴于STAGE層的數(shù)據(jù)，不再重復(fù)進(jìn)行抽取，也不在生產(chǎn)系統(tǒng)上作運(yùn)算，減小生產(chǎn)系統(tǒng)的壓力；（2） 在生產(chǎn)系統(tǒng)數(shù)據(jù)已經(jīng)刷新的情況下，保存一定量的生產(chǎn)系統(tǒng)的歷史數(shù)據(jù)，以便在二次抽取過程中運(yùn)算出錯的情況下可以進(jìn)行回溯。2) ODS/DWD層（對應(yīng)原模型的ODS和DW層）信息模型：簡稱DWD層是數(shù)據(jù)倉庫的細(xì)節(jié)數(shù)據(jù)層，是對STAGE層數(shù)據(jù)進(jìn)行沉淀，減少了抽取的復(fù)雜性，同時ODS/DWD的信息模型組織主要遵循企業(yè)業(yè)務(wù)事務(wù)處理的形式，將各個專業(yè)數(shù)據(jù)進(jìn)行集中。為企業(yè)進(jìn)行經(jīng)營

3、數(shù)據(jù)的分析，系統(tǒng)將數(shù)據(jù)按分析的主題的形式存放，跟STAGE層的粒度一致，屬于分析的公共資源。3) MID 信息模型：輕度綜合層是新模型增加的數(shù)據(jù)倉庫中DWD層和DM層之間的一個過渡層次，是對DWD層的生產(chǎn)數(shù)據(jù)進(jìn)行輕度綜合和匯總統(tǒng)計(jì)。輕度綜合層與DWD的主要區(qū)別在于二者的應(yīng)用領(lǐng)域不同，DWD的數(shù)據(jù)來源于生產(chǎn)型系統(tǒng)，并為滿足一些不可預(yù)見的需求而進(jìn)行沉淀；輕度綜合層則面向分析型應(yīng)用進(jìn)行細(xì)粒度的統(tǒng)計(jì)和沉淀。4) DM信息模型：為專題經(jīng)營分析服務(wù)，系統(tǒng)將數(shù)據(jù)按分析的專題組織成多維庫表的形式存放，屬于分析目標(biāo)范疇的數(shù)據(jù)組織與匯總，屬于分析的專有資源。其信息主要來源于DWD 和MID層匯總，反映實(shí)時的經(jīng)營狀

4、況，時間維度為天。而歷史經(jīng)營狀況的分析，時間維度一般為月，同時也具有季度、年這樣的維度。5) MDW元數(shù)據(jù)信息模型：描述數(shù)據(jù)及其環(huán)境的數(shù)據(jù)，即是對數(shù)據(jù)資源的描述,是信息共享和交換的基礎(chǔ)和前提，用于描述數(shù)據(jù)集的內(nèi)容、質(zhì)量、表示方式、空間參考、管理方式以及數(shù)據(jù)集的其他特征。一般來說,它有兩方面的用途。首先,元數(shù)據(jù)能提供基于用戶的信息,如記錄數(shù)據(jù)項(xiàng)的業(yè)務(wù)描述信息的元數(shù)據(jù)能幫助用戶使用數(shù)據(jù)。其次,元數(shù)據(jù)能支持系統(tǒng)對數(shù)據(jù)的管理和維護(hù), 元數(shù)據(jù)機(jī)制主要支持以下五類系統(tǒng)管理功能:（）描述哪些數(shù)據(jù)在數(shù)據(jù)倉庫中;（）定義要進(jìn)入數(shù)據(jù)倉庫中的數(shù)據(jù)和從數(shù)據(jù)倉庫中產(chǎn)生的數(shù)據(jù);（）記錄根據(jù)業(yè)務(wù)事件發(fā)生而隨之進(jìn)行的數(shù)據(jù)抽取

5、工作時間安排;（）記錄并檢測系統(tǒng)數(shù)據(jù)一致性的要求和執(zhí)行情況;（）衡量數(shù)據(jù)質(zhì)量。1.2 各層物理表前綴 在構(gòu)建數(shù)據(jù)倉庫時，至少應(yīng)該具備以下物理幾層：聯(lián)通數(shù)據(jù)模型規(guī)范要求的層次數(shù)據(jù)中心物理模型層次名稱物理表前綴維度數(shù)據(jù)層DIM_數(shù)據(jù)集市層(DM)展示層DM_主題域DW_F_輕度匯總層(MID)輕度匯總層DW_M_細(xì)節(jié)數(shù)據(jù)層（ODS/DWD）細(xì)節(jié)數(shù)據(jù)層DW_V_接口層（STAGE）接口層SRC_1.3數(shù)據(jù)庫對象命名規(guī)范 所有數(shù)據(jù)庫對象名稱均使用26個大寫英文字母、下劃線或數(shù)字來命名，并不得以下劃線開頭。1.3.1 用戶 用戶名和表空間的名稱應(yīng)該采用同系統(tǒng)應(yīng)用相似的英文字符或字符縮寫，表空間所對應(yīng)的一

6、個或多個物理文件名也應(yīng)有相關(guān)性。用戶創(chuàng)建的數(shù)據(jù)索引最好和數(shù)據(jù)文件分開存放在不同表空間，以減少數(shù)據(jù)爭用和提高響應(yīng)速度。 以上從邏輯上分出的各數(shù)據(jù)層應(yīng)當(dāng)在數(shù)據(jù)庫中用戶實(shí)現(xiàn)其分離，規(guī)定如下：數(shù)據(jù)層名稱用戶物理表備注接口層SRCSRC沉淀數(shù)據(jù)層DWDW_V,DW_M,DW_F數(shù)據(jù)集市層DMDM_元數(shù)據(jù)層MDWMDW_目前未設(shè)計(jì)維度數(shù)據(jù)層DIMDIM_1.3.2 數(shù)據(jù)庫表空間 數(shù)據(jù)庫表空間命名，原則上以數(shù)據(jù)倉庫的基本分層結(jié)構(gòu)為準(zhǔn)，以TBS_作前綴，為避免單個表空間數(shù)據(jù)量過大，帶來管理上的不便或者引起I/O瓶頸，對于STAG和ODS/DWD數(shù)據(jù)量比較大的層，可采用多個表空間存儲數(shù)據(jù)，單表空間容量不要太大，

7、以便于業(yè)務(wù)劃分和存儲管理為原則，建議單表空間容量控制在800G之內(nèi)，表空間數(shù)據(jù)文件建議值為4G。 數(shù)據(jù)表空間數(shù)據(jù)倉庫分層結(jié)構(gòu)用戶表空間名數(shù)據(jù)文件/裸設(shè)備STAGSRCTBS_STAG001TBS_STAGXXX2G=單設(shè)備文件=8G,建議值：4GDWDDWTBS_DWV001TBS_DWVXXX存放以dw_v開頭的表INDX_TBS_DMV存放以dw_v開頭的表的索引MIDMIDTBS_MID同上DMDMTBS_DM同上INDX_TBS_DMDM索引DIMDIMTBS_DIM同上 表名長度不能超過28個字符，表名中含有單詞全部采用單數(shù)形式，單詞選擇能夠概括表內(nèi)容的一個或多個英文單詞，多個單詞間

8、使用下劃線分割,單詞如果過長可以使用縮寫形式。命名規(guī)則如下：表的類型前綴層次說明示例維度表DIM_DIMDIM_+主鍵客戶視圖DW_V_USER_DW_M_USERDW三個視圖產(chǎn)品視圖DW_V_PROD_DW_M_PROD_DW市場營銷視圖DW_V_MART_DW_M_MART_DW發(fā)展域DW_F_DEV_DW8個主題域根據(jù)業(yè)務(wù)具體分為：移動業(yè)務(wù)DW_F_DEV_M_智能網(wǎng)DW_F_DEV_I_數(shù)固DW_F_DEV_D_收入域DW_F_INCO_DW同上使用域DW_F_USE_DW同上增值域DW_F_INC_DW成本域DW_F_COST_DW資源域DW_F_RES_DW競爭域DW_F_COMP

9、_DW服務(wù)域DW_F_SERV_DW1.3.4 數(shù)據(jù)庫分區(qū)表規(guī)范對于海量數(shù)據(jù)表要考慮設(shè)計(jì)為分區(qū)表。1. 三戶日資料保存一個月，按日期主分區(qū)按地區(qū)子分區(qū)，2. 主分區(qū)命名為：P兩位日期編碼(如P01)，3. 子分區(qū)命名為：P兩位日期編碼_SP地區(qū)編碼(如 P01_SP188),并且必須加上月份字段否則沒法區(qū)分是那月的資料。2.三戶月資料按帳期主分區(qū)按地區(qū)子分區(qū)，主分區(qū)命名為：P帳期編碼(如P)，子分區(qū)命名為：P帳期編碼_SP地區(qū)編碼(如 P_SP188)。3.視圖級日數(shù)據(jù)表按帳期和地區(qū)主分區(qū)按日期子分區(qū)，主分區(qū)命名為：P帳期編碼_地區(qū)編碼(如P_188)，子分區(qū)命名為：P帳期編碼_地區(qū)編碼_SP

10、兩位日期編碼(如 P_188_SP01)。4.視圖級月數(shù)據(jù)表按帳期主分區(qū)按地區(qū)子分區(qū)，主分區(qū)命名為：P帳期編碼 (如P)，子分區(qū)命名為：P帳期編碼_SP地區(qū)編碼(如 P_SP188)。5.主體域級數(shù)據(jù)按帳期主分區(qū)按日期子分區(qū)，主分區(qū)格式為：P帳期(如 P)，子分區(qū)格式為：P帳期_SP兩位日期編碼(如 P_SP01)。老楊讓把主題域建表分區(qū)規(guī)范改為：主體域級數(shù)據(jù)按帳期和地區(qū)主分區(qū)按日期子分區(qū)，主分區(qū)命名為：P帳期編碼_地區(qū)編碼(如P_188)，子分區(qū)命名為：P帳期編碼_地區(qū)編碼_SP兩位日期編碼(如 P_188_SP01)1.3.5 數(shù)據(jù)庫表索引命名以IDX+表名+一位流水號.例：IDX_ODS

11、_BUSI_USER_1;如果表名過長可以使用縮寫形式1.3.6 數(shù)據(jù)庫表鍵值 主鍵命名以PK+表名+一位流水號(19).例: PK_DEPT_1 ;如果表名過長可以使用縮寫形式 外鍵命名以FK+表名+一位流水號(19).例: FK_DEPT_1;如果表名過長可以使用縮寫形式1.3.7 數(shù)據(jù)庫字段命名規(guī)范數(shù)據(jù)庫字段名中含有單詞選擇能夠概括表內(nèi)容的一個或多個英文單詞，多個單詞間使用下劃線分割,單詞如果過長可以使用縮寫形式。一些基本字段名示例：用戶id USER_NO用戶數(shù)USER_COUNTS話單數(shù)CDR_NUM通話時長CALL_DURATION計(jì)費(fèi)次數(shù)MOBILE_TIMES每個字段必須有注釋

12、，并且在生成SQL腳本時一并生成，創(chuàng)建表時必須創(chuàng)建注釋。保持字段名和類型的一致性，同一字段名在不同表中必需保持同一數(shù)據(jù)類型。數(shù)據(jù)類型長度在定義時應(yīng)稍大于目前標(biāo)準(zhǔn)的長度，用空間來換取將來變更帶來的不便。1.3.8 數(shù)據(jù)庫存儲過程規(guī)范（1）存儲過程命名規(guī)則：P_目標(biāo)表。（2）存儲過程要求有注釋，注釋內(nèi)容為：列出創(chuàng)建人，創(chuàng)建用途，創(chuàng)建時間。（3）存儲過程日志規(guī)范：每一存儲過程均應(yīng)記錄執(zhí)行存儲過程的日志信息。必須調(diào)用專用寫日志的存儲過程，同時有exception時的處理機(jī)制。（4）存儲過程修改規(guī)范修改時應(yīng)注釋清楚修改人，修改日期，修改原因和修改內(nèi)容。1.3.9 數(shù)據(jù)庫函數(shù)命名規(guī)范函數(shù)命名規(guī)則F_功能，

13、比如F_TRAN_AREA。1.3.10 據(jù)庫觸發(fā)器的命名規(guī)范觸發(fā)器以TR作為前綴，觸發(fā)器名為相應(yīng)的表的別名加上后綴，INSERT觸發(fā)器加_INSERT，Delete觸發(fā)器加_DELETE，Update觸發(fā)器加_UPDATE，如：TR_CUST_INSERT。1.3.11 序列命名規(guī)范 序列以S作為前綴，序列命名規(guī)則為S_字段別名。 二 實(shí)施流程規(guī)范（完善中。）(1)規(guī)劃對實(shí)施計(jì)劃的規(guī)劃.(2)設(shè)計(jì)設(shè)計(jì)實(shí)施方案(包括統(tǒng)一模型的修改)。(3)實(shí)施具體實(shí)施過程。(4)測試對實(shí)施結(jié)果測試。(5)反饋對實(shí)施過程中收集到的相關(guān)信息(系統(tǒng)需求、實(shí)施中遇到的問題和測試結(jié)果等)反饋到相關(guān)部門和人員。三 數(shù)據(jù)庫

14、安全管理規(guī)范為了規(guī)范管理，做好經(jīng)營分析數(shù)據(jù)倉庫的安全管理工作，實(shí)現(xiàn)不同的責(zé)任人不同的層次，將用戶權(quán)限盡可能的管理起來同時又不影響正常工作，需要對數(shù)據(jù)庫進(jìn)行安全管理。數(shù)據(jù)庫安全管理從以下幾個方面來進(jìn)行：3.1.用戶組管理對用戶進(jìn)行分類，目前經(jīng)營分析應(yīng)用用戶可以分為如下幾部分 前臺程序開發(fā)人員 數(shù)據(jù)庫開發(fā)人員 數(shù)據(jù)庫管理員 外部使用人員數(shù)據(jù)庫管理人員由項(xiàng)目經(jīng)理和數(shù)據(jù)經(jīng)理來掌控，一般情況下不得使用DBA角色登陸數(shù)據(jù)庫。數(shù)據(jù)人員使用數(shù)據(jù)庫開發(fā)人員角色登陸，每個數(shù)據(jù)人員一個用戶，歸屬數(shù)據(jù)庫開發(fā)人員組。前臺程序開發(fā)人員，由界面開發(fā)人員使用，可以查看所有的表，但是無法進(jìn)行DDL操作。外部使用人員，主要是面向

15、聯(lián)通用戶和臨時用戶3.2.用戶權(quán)限設(shè)定對不同的用戶組，在不影響正常工作的情況下，對用戶組及用戶權(quán)限的設(shè)定原則為權(quán)限越小越好。3.3.用戶密碼管理對用戶密碼進(jìn)行限制，必須由2位以上數(shù)字，2位以上字符，2位以上特殊字符組成不允許用戶密碼和用戶名同名不允許用戶密碼和用戶名相似3.4.用戶資源管理除了系統(tǒng)使用的用戶（SRC/ODS/DW）等外對用戶使用的系統(tǒng)資源進(jìn)行限定限定用戶使用表空間限定用戶使用臨時表空間限定用戶使用回滾斷限定用戶使用內(nèi)存3.5.IP限定對于普通用戶，實(shí)行IP和用戶名綁定的策略對于外部開放用戶，要進(jìn)行IP申請，由數(shù)據(jù)經(jīng)理或者項(xiàng)目經(jīng)理審核通過后予以開通3.6.數(shù)據(jù)庫監(jiān)控?cái)?shù)據(jù)庫監(jiān)控，主

16、要對以下幾個方面進(jìn)行監(jiān)控：3.6.1. 數(shù)據(jù)庫空間占用率select a.tablespace_name, free, total, round(b.total-a.free)/b.total),2) 剩余占比from ( select tablespace_name,round(sum(bytes)/power(1024,3),2) free from dba_free_space group by tablespace_name ) a, ( select tablespace_name,round(sum(bytes)/power(1024,3),2) total from dba_da

17、ta_files group by tablespace_name ) bwhere a.tablespace_name = b.tablespace_name;3.6.2. 會話情況select *from v$session a, v$sql bwhere a.sql_address = b.address;3.6.3. aix操作系統(tǒng)中殺掉一些進(jìn)程的腳本select kill -9 | p.spid|,s.sidfrom v$session s,v$process pwhere s.paddr = p.addrand s.username is not nulland s.sid = 5

18、43.6.4. 查看JOBSELECT *FROM User_Jobs3.6.5. 分區(qū)操作查看分區(qū)子分區(qū)SELECT *FROM ALL_TAB_PARTITIONSWHERE TABLE_NAME = DW_V_USER_MOBILEUSER；SELECT *FROM ALL_TAB_SUBPARTITIONSWHERE TABLE_NAME = DW_V_USER_MOBILEUSER;增加分區(qū)格式：alter table 表名 add partition分區(qū)名 values less than (值)如： alter table dm_reinnet_user add partitio

19、n p values less than ()alter table dm_reinnet_user add subpartition p_SP001 values (002)刪除分區(qū)格式：alter table 表名 drop partition partition 分區(qū)名如： alter table dm_reinnet_user drop partition alter table dm_reinnet_user drop subpartition p_SP0013.6.6. 數(shù)據(jù)庫的無效索引查看目前數(shù)據(jù)庫中的索引情況3.6.7. 數(shù)據(jù)庫的無效對象查看目前數(shù)據(jù)庫的對象有效性，主要針對腳本

20、3.6.8. 數(shù)據(jù)庫表分區(qū)的是否到達(dá)限額查看是否有表分區(qū)不滿足需求的情況，這項(xiàng)監(jiān)控根據(jù)具體需求來3.6.9. 數(shù)據(jù)庫內(nèi)存占用情況查看目前數(shù)據(jù)庫內(nèi)存的占用情況3.6.10. DDL語句的監(jiān)控查看各種DDL語句的使用情況，記錄操作者的IP，時間，用戶名等情況3.6.11. 特定DML語句的監(jiān)控對某些特定的重要的數(shù)據(jù)庫對象，記錄DML操作的歷史3.7.數(shù)據(jù)庫審計(jì)3.7.1.語句審計(jì)(Statement Auditing) 對預(yù)先指定的某些SQL語句進(jìn)行審計(jì)。這里從SQL語句的角度出發(fā)，進(jìn)行指定。 審計(jì)只關(guān)心執(zhí)行的語句。 例如，audit CREATE TABLE;命令，就表明對create table語句的執(zhí)行進(jìn)行記錄。 不管這語句是否是針對某個對象的操作3.7.2.權(quán)限審計(jì)(Privilege Auditing) 對涉及某些權(quán)限的操作進(jìn)行審計(jì)。這里強(qiáng)調(diào)涉及權(quán)限 例如，audit CREATE TABLE;命令，又可以表明對涉及CREATE TABLE權(quán)限的操作進(jìn)行審計(jì),所以說，在這 種命令的情況下，既產(chǎn)生一個語句審計(jì)，又產(chǎn)生了一個權(quán)限審計(jì)。有時候語句審計(jì)和權(quán)限審計(jì)的相 互重復(fù)的。這一點(diǎn)可以后面證明。3.7.3.對象審計(jì)(Object