数仓规划原则

基本原则遵循阿里三One原则，OneEntity，OneData，OneService，遇到矛盾的取舍，秉承推动数据中台实现企业数据拉通，指标统一最有利的方向做决策。

主要考虑的要素：

  1. 原生数据：基础数据统一，唯一出口；
  2. 维度数据：分析维度统一，主数据统一；
  3. 权限管理：便于按部门分配使用权限；
  4. 组织架构：适应组织架构多变；
  5. 指标通用：在指标理解和通用性方面，做平衡；

几个问题考虑背景

为什么不采用垂直按部门分模式的方式？

虽然这种模式以每个部门划分schema，非常有利于权限控制， 每个shcema里面再分ods……ads，权限管理简单了，但是会带来后期指标难以统一，全局取数困难的问题。又是各种烟囱林立，所以从大的原则出发不能采取这种模式。

为什么不采用一个公共schema里面按表名ods-dwd-dws进行划分，ads按模式划分？

虽然这种模式开发变得简单了，而且公共的部分都在一个schema里面。这种模式不利于权限控制，同时一个schema里面存在太多表，实际使用和维护的时候也不方便。

为什么ads命名没有采取带内涵的，如：yxb（营销部）命名？

常规做法确实按简拼或者英文命名比较多，但是从考虑组织架构调整，人员更替带来的理解问题数字编码更不容易带来歧义。初期适应成本会高一点点，但是对未来的扩展性要好很多。比如：yx划给大运营管了，或者某个部门就撤销了，但是数据还是需要用。数字会有更好的兼容性。

数仓分层架构

结构图：
image.png

ODS层

数据引入层ODS（Operational Data Store）
ods层划一个独立模式（Schema），汇集所有子系统进来的原始数据，数据结构和源系统数据尽量保持一致，需注意数据类型，不一定保持一致，比如：money的兼容性问题。

  • 表名：通常采用schema或者表名前缀增加来源系统标识区分，需要注意的是前缀最好带有系统厂家标识。销售系统，这种标识就不好，因为销售系统在企业里面可能有多个。
  • 字段名：完全保持和业务系统一抹一样。

DIM层

公共维度模型层DIM（Common Data Model）
dim层划一个独立模式（Schema），数据核心来源于ods的主数据，是维度数据，DIM的数据结构命名规则标准不遵循业务系统，由数仓统一构建。

  • 表名：通常采用schema或者dim_来区分；
  • 字段名：统一称为公司规范；

DWD层

明细数据层DWD（Data Warehouse Detail）
dwd层划一个独立模式（Schema），数据核心来源于ods的事实和维度数据关联，实现对业务数据明细还原，表命名规则数仓统一，字段命名尽量和业务系统一致。通过企业的业务活动事件构建数据模型。基于具体业务事件的特点，构建最细粒度的明细数据表。您可以结合企业的数据使用特点，将明细数据表的某些重要维度属性字段适当冗余，即宽表化处理。同时，也可以减少明细数据表及维度表的关联，提高明细表的易用性。

  • 业务还原比如数据回溯，union不同来源的数据相同域的数据。
  • 日期字段全部统一规范。
  • 维度信息全部统一。
  • 按照数仓总线进行构建。
  • 常用的表进行关联。

for_dws层

指标口径层，也叫DWM中间层，对应原子指标，是早见云数创新的一层，目的是在没有系统的情况下，管理指标口径。因为dws需要按对象拉通，如果全部在dws处理，dws（周期、对象的多表都需要复用）会变得很复杂。for_dws细化到最细颗粒度，以解决数据口径核对的问题。

  • case when 和where口径的核心写法在for_dws
  • 数值型字段统一空值表示，比如0；

DWS层

汇总数据层DWS（Data Warehouse Summary）
dws层划一个独立模式（Schema），核心是要对for_dws的数据进行打通和汇总，定义派生指标口径，是数仓建设口径统一的关键层。数据表，字段命名规则全局统一，是数据构建的关键公共层。

  • 以对象为主线进行所有指标关联。
  • 按维度进行汇总。


  • ADS00层

    应用数据层ADS（Application Data Service）
    这一层是针对决策层应用的，数据范围最为宽广，表命名规则可以灵活，字段命名规则全局统一。

  • 老板有关的

  • 一般为视图，除非性能问题否则不做实体

    ADS**层

    应用数据层ADS（Application Data Service）
    这一层是这对部门应用的专业层，主要包含条线需要用的数据。表命名规则可以灵活，字段命名规则全局统一。

  • 部门级的

  • 一般为视图，除非性能问题否则不做实体

数据表命名规则

全局命名规则

表、视图、储存过程等均采取：

  • 全部采取小写字母和数字；
  • 连接符统一为“_”;
  • 必须字母开头；
  • 储存过程规则：<函数名称>，以fn作为前缀；
  • 实时表规则：<表名>_,以t0作为后缀；
  • 所有表都必须包含技术名和业务名；

    ODS层命名规则

    这一层维护的主要场景是面向业务系统做数据抽取，所以最好和业务系统相对应保持一致。

表名规则：<子系统名简拼或英文名><可选1位流水号><子系统表名>_

规则举例：ods_xsxt_s_contract，ods_xsxt2_s_hetong，ods_xsxt_s_contract_t0（代表实时数据）

为什么要用1位流水号，主要是两个原因：一是同一个系统可能会更换，比如SAP销售系统换成明源销售系统；二是大型企业不排除同一个集团有两套销售系统。当出现重复的时候从2开始命名；

字段名规则：完全和子系统保持一致。

DIM层命名规则

这一层是使用频率最高的，通用性最高的数据，也是不容易重复的层，所以尽量以英文名称进行命名。

表名规则：<维度代码全拼><维度层级 选填，两位数字>

规则举例：
dim_yetai_05，五级业态
dim_yetai_04，四级业态
dim_yetai_03，三级业态
dim_yetai_02，二级业态
dim_yetai_01，一级业态

代码规范，严格执行

代码全拼 中文名 示例
zuzhi 组织 台易集团
faren 法人组织 上海台易网络科技有限公司
bumen 费用组织 集团-本部-营销中心-销售部
yuangong 公司员工 签订劳动合同的员工
guyuan 所有雇员 包括合同员工，外包员工，乙方驻场员工等
dikuai 地块 人民北路1#地块
xiangmu 项目 台易美好生活项目
fenqi 项目分期 台易美好生活项目-一期
loudong 楼栋 台易美好生活项目-一期-1号楼
fangyuan 房源 台易美好生活项目-一期-1号楼-8楼-801
yetai 产品类型 住宅-高层
kehu 客户 所有客户，意向客户，成交客户，租户
yezhu 业主 一手业主，二手业主
gongyingshang 供应商

DWD层命名规则

命名主要解决易于使用和权限分配的问题，整体思路上借鉴平衡积分卡的分类方法，涵盖比较全面。常见的命名规范通常只考虑了业务流程，没有考虑到全局不适合数仓建设。平衡积分卡一共有四个层面财务、客户、内部运营、学习与成长。

表名规则：<数据域代码><业务对象>_

规则举例：dwd_yx_qianyue_di，dwd_yx_qianyue_df

明细代码：di：事务型，df：周期快照型

代码规范，严格执行

序号 数据域代码 中文名 示例
1 tz 投资管理
2 cp 产品设计
3 gc 工程建造
4 cb 成本管理
5 zc 招采管理
6 yy 运营管理
7 yx 营销管理 dwd_yx_qianyue_di，dwd_yx_qianyue_df
8 kf 客户服务
9 cw 财务管理
10 kh 客户管理
11 hb 伙伴管理
12 rl 人力行政
13 xx 信息流程
14 fx 风险管理
15 wb 外部信息

DWS层命名规则

DWS将相同统计周期+统计粒度的指标集成到一起形成大宽表，因此DWS表名由统计周期与统计粒度组成。

指标表命名规则：<统计周期><统计粒度>_。
指标表规则举例：dws_yx_zx_xiangmu_yetai_05，最新项目5级业态汇总表

ADS层命名规则

表名规则：_<自定义表名>
规则举例：ads_customer，ads_sales01

其它命名规范

针对外部表，其它临时表，统一挂到独立的模式里面统一命名为：ext。
针对外部表，其它临时表，ext的外部表，和ods表名一致，实体文件名三者保持一致。

字段命名规则

DIM维度字段命名规则

命名规则：<词组>_<词组>

规则举例：xiangmu_mc，项目名称 ；xiangmu_bm，项目编码

代码全拼 中文名 示例
zuzhi_01_mc 一级组织 如：集团
zuzhi_02_mc 二级组织 如：区域集团
zuzhi_03_mc 三级组织 如：大区
zuzhi_04_mc 四级组织 如：城市公司
zuzhi_05_mc 五级组织 如：片区
zuzhi_06_mc 六级组织 预留

原子指标命名规则

命名规则：<业务动作>_<度量>

规则示例：qianyue_je

代码规范，严格执行

序号 度量 代码标识 备注
01 金额 je 示例：年度项目签约金额
02 面积 mj 示例：年度项目签约面积
03 套数 ts 示例：年度项目签约套数
04 率 lv 示例：年度项目签约回款率
05 比 bi 示例：年度项目供销比
06 日期 rq 示例：计划开工日期
07 天数 tian 示例：逾期天数
08 月数 yue 示例：库龄月数
09 单价 dj 示例：认购单价

派生指标命名规则

命名规则：<统计周期><统计粒度><业务限定>_<原子指标>

规则举例：nd_jt_qianyue_je

统计周期代码规范，严格执行

序号 统计周期 代码标识 备注
01 年度 nd 示例：年度项目签约金额、年度项目回款金额
02 年度期初 nd_qc 示例：年度项目期初库存
03 年度期末 nd_qm 示例：年度项目期末库存
04 季度 jd 示例：季度项目签约金额、季度项目回款金额
05 季度期初 jd_qc 示例：季度期初项目库存
06 季度期末 jd_qm 示例：季度期末项目库存
07 月度 yd 示例：月度项目签约金额、月度项目回款金额
08 月度期初 yd_qc 示例：月度期初项目库存
09 月度期末 yd_qm 示例：月度期末项目库存
10 单周 dz 示例：单周项目来访人数
11 单日 dr 示例：单日集团回款金额
12 年初至统计月 yd_nc 示例：年初至统计月项目计划供应金额
13 统计月至年末 yd_nm 示例：统计月至年末项目计划供应金额
14 最新 zx 示例：最新项目动态货值金额
15 历史至今 lj 示例：历史至今项目签约金额

附录：

阿里云维度表模型图参考

image.png

中国会计科目对照表参考

中英文对照最新会计科目 北京市审计局发布

顺序号 编号 会计科目名称 适用范围 英文表达法
一、资产类
1 1001 库存现金 Cash on Hand
2 1002 银行存款 Cash in bank
3 1003 存放中央银行款项 银行专用 Deposit in the Central Bank
4 1011 存放同业 银行专用 Due from Placements with Banks and Other Financial Institutions
5 1015 其他货币资金 Other Monetary Capital
6 1021 结算备付金 证券专用 Deposit Reservation for Balance
7 1031 存出保证金 金融共用 Deposit for Recognizance
8 1051 拆出资金 金融共用 Lendings to Banks and Other Financial Institutions
9 1101 交易性金融资产 Transactional Monetary Capital
10 1111 买入返售金融资产 金融共用 Redemptory Monetary Capital for Sale
11 1121 应收票据 Notes Receivable
12 1122 应收账款 Accounts Receivable
13 1123 预付账款 Advance to suppliers
14 1131 应收股利 Dividend Receivable
15 1132 应收利息 Accrued Interest Receivable
16 1211 应收保户储金 保险专用 Receivable Deposit from the Insured
17 1221 应收代位追偿款 保险专用 Subrogation Receivables
18 1222 应收分保账款 保险专用 Reinsurance Accounts Receivable
19 1223 应收分保未到期责任准备金 保险专用 Receivable Deposit for Undue Duty of Reinsurance
20 1224 应收分保保险责任准备金 保险专用 Receivable Deposit for Duty of Reinsurance
21 1231 其他应收款 Other Receivable
22 1241 坏账准备 Provision for bad debts
23 1251 贴现资产 银行专用 Deposit of Capital Discounted
24 1301 贷款 银行和保险共用 Loans
25 1302 贷款损失准备 银行和保险共用 Loans Impairment Reserve
26 1311 代理兑付证券 银行和证券共用 Vicariously Cashed Securities
27 1321 代理业务资产 Capital in Vicarious Business
28 1401 材料采购 Procurement of Materials
29 1402 在途物资 Materials in Transit
30 1403 原材料 Raw Materials
31 1404 材料成本差异 Balance of Materials
32 1406 库存商品 Commodity Stocks
33 1407 发出商品 Goods in Transit
34 1410 商品进销差价 Difference between Purchase and Sales of Commodities
35 1411 委托加工物资 Materials for Consigned Processing
36 1412 包装物及低值易耗品 Wrappage and Easily Wornout Inexpensive Articles
37 1421 消耗性生物资产 农业专用 Consumptive Biological Assets
38 1431 周转材料 建造承包商专用 Revolving Materials
39 1441 贵金属 银行专用 Expensive Metals
40 1442 抵债资产 金融共用 Capital for Debt Payment
41 1451 损余物资 保险专用 Salvage Value Of Insured Properties
42 1461 存货跌价准备 Reserve For Stock Depreciation
43 1501 待摊费用 Unamortized Expenditures
44 1511 独立账户资产 保险专用 Capital in Independent Accounts
45 1521 持有至到期投资 Held-To-Maturity Investment
46 1522 持有至到期投资减值准备 Reserve for Held-To-Maturity Investment Impairment
47 1523 可供出售金融资产 Financial Assets Available for Sale
48 1524 长期股权投资 Long-term Equity Investment
49 1525 长期股权投资减值准备 Reserve for Long-term Equity Investment Impairment
50 1526 投资性房地产 Investment Real Estate
51 1531 长期应收款 Long-term Accounts Receivable
52 1541 未实现融资收益 Unrealized Financing Profits
53 1551 存出资本保证金 保险专用 Deposit for Capital Recognizance
54 1601 固定资产 Fixed assets
55 1602 累计折旧 Accumulative Depreciation
56 1603 固定资产减值准备 Provision for impairment of fixed assets
57 1604 在建工程 Construction in Process
58 1605 工程物资 Engineer Material
59 1606 固定资产清理 Disposal of Fixed Assets
60 1611 融资租赁资产 租赁专用 Financial Leasing Assets
61 1612 未担保余值 租赁专用 Unguaranteed Residual Value
62 1621 生产性生物资产 农业专用 Productive Biological Assets
63 1622 生产性生物资产累计折旧 农业专用 Accumulative Depreciation of Productive Biological Assets
64 1623 公益性生物资产 农业专用 Biological Assets for Commonweal
65 1631 油气资产 石油天然气开采专用 Oil and Gas Assets
66 1632 累计折耗 石油天然气开采专用 Accumulated Depletion
67 1701 无形资产 Intangible Assets
68 1702 累计摊销 Accumulated Amortization
69 1703 无形资产减值准备 Reserve for Intangible Assets Impairment
70 1711 商誉 Business Reputation
71 1801 长期待摊费用 Long-term Deferred Expenses
72 1811 递延所得税资产 Deferred Income Tax Assets
73 1901 待处理财产损溢 Profit & loss of assets pending disposal
二、负债类
74 2001 短期借款 Short-term Borrowings
75 2002 存入保证金 金融共用 Deposit Received for Recognizance
76 2003 拆入资金 金融共用 Borrowings from Banks and Other Financial Institutions
77 2004 向中央银行借款 银行专用 Borrowings from the Central Bank
78 2011 同业存放 银行专用 Due to Placements with Banks and Other Financial Institutions
79 2012 吸收存款 银行专用 Savings Absorption
80 2021 贴现负债 银行专用 Liabilities of Capital Discounted
81 2101 交易性金融负债 Transactional Moneytary Liabilities
82 2111 卖出回购金融资产款 金融共用 Financial Assets Sold for Repurchase
83 2201 应付票据 Notes Payable
84 2202 应付账款 Accounts Payable
85 2205 预收账款 Advance from customers
86 2211 应付职工薪酬 Payroll Payable
87 2221 应交税费 Taxes Payable
88 2231 应付股利 Dividend Payable
89 2232 应付利息 Accrued Interest Payable
90 2241 其他应付款 Other Accounts Payable
91 2251 应付保户红利 保险专用 Dividend Payable for The Insured
92 2261 应付分保账款 保险专用 Dividend Payable for Reinsurance
93 2311 代理买卖证券款 证券专用 Receivings from Vicariously Traded Securities
94 2312 代理承销证券款 证券和银行共用 Receivings from Vicariously Sold Securities
95 2313 代理兑付证券款 证券和银行共用 Receivings from Vicariously Cashed Securities
96 2314 代理业务负债 Liabilities from Vicarious Business
97 2401 预提费用 Accrued expensess
98 2411 预计负债 Estimated Liabilities
99 2501 递延收益 Deferred Profits
100 2601 长期借款 Long-term loans
101 2602 长期债券 Long-term Bonds
102 2701 未到期责任准备金 保险专用 Deposit for Undue Duty of Reinsurance
103 2702 保险责任准备金 保险专用 Deposit for Duty of Reinsurance
104 2711 保户储金 保险专用 Deposit of the Insured
105 2721 独立账户负债 保险专用 Liabilities of Independent Accounts
106 2801 长期应付款 Long-term Payable
107 2802 未确认融资费用 Unsettled Financing Expenses
108 2811 专项应付款 Special Accounts Payable
109 2901 递延所得税负债 Deferred Income Tax Liabilities
三、共同类
110 3001 清算资金往来 银行专用 Liquidation of Inter Bank Business
111 3002 外汇买卖 金融共用 Foreign Exchange Buy and Sale
112 3101 衍生工具 Derivative Tools
113 3201 套期工具 Arbitrage Tools
114 3202 被套期项目 Arbitraged Items
四、所有者权益类
115 4001 实收资本 Paid-in Capital
116 4002 资本公积 Capital Reserve
117 4101 盈余公积 Surplus Reserve
118 4102 一般风险准备 金融共用 Generic Risk Reserve
119 4103 本年利润 Profit & loss summary
120 4104 利润分配 Distribution profit
121 4201 库存股 Treasury Share
五、成本类
122 5001 生产成本 Production Costs
123 5101 制造费用 Manufacturing Expenditures
124 5201 劳务成本 Service Costs
125 5301 研发支出 Research and Development Expenditures
126 5401 工程施工 建造承包商专用 Engineering Constructon
127 5402 工程结算 建造承包商专用 Engineering Settlement
128 5403 机械作业 建造承包商专用 Mechanical Operations
六、损益类
129 6001 主营业务收入 Sales revenue
130 6011 利息收入 金融共用 Interest Income
131 6021 手续费收入 金融共用 Commission Income
132 6031 保费收入 保险专用 Premium Income
133 6032 分保费收入 保险专用 Reinsurance Premium Income
134 6041 租赁收入 租赁专用 Leasehold Income
135 6051 其他业务收入 Revenues from other operationse
136 6061 汇兑损益 金融专用 Exchange Gain or Loss
137 6101 公允价值变动损益 Profit and Loss from Fair Value Changes
138 6111 投资收益 Income on Investment
139 6201 摊回保险责任准备金 保险专用 Amortized Deposit for Duty
140 6202 摊回赔付支出 保险专用 Amortized Compensation Expenses
141 6203 摊回分保费用 保险专用 Amortized Reinsurance Expenditures
142 6301 营业外收入 Non-operating profit
143 6401 主营业务成本 Cost of sales
144 6402 其他业务成本 Cost of other operations
145 6405 营业税金及附加 Business Tax and Surcharges
146 6411 利息支出 金融共用 Interest Expenses
147 6421 手续费支出 金融共用 Commission Expenses
148 6501 提取未到期责任准备金 保险专用 Appropriation of Deposit for Undue Duty
149 6502 提取保险责任准备金 保险专用 Appropriation of Deposit for Duty
150 6511 赔付支出 保险专用 Compensation Expenses
151 6521 保户红利支出 保险专用 Dividend Expenses for The Insured
152 6531 退保金 保险专用 Loan Value
153 6541 分出保费 保险专用 Reinsurance Premium
154 6542 分保费用 保险专用 Reinsurance Expenses
155 6601 销售费用 Marketing Costs
156 6602 管理费用 Managing Costs
157 6603 财务费用 Financing Costs
158 6604 勘探费用 Prospecting Costs
159 6701 资产减值损失 Assets Devaluation
160 6711 营业外支出 Non-operating expenses
161 6801 所得税 Income Tax
162 6901 以前年度损益调整 Prior period profit & loss adjustment