{"id":451,"date":"2012-12-30T20:00:00","date_gmt":"2012-12-30T12:00:00","guid":{"rendered":"http:\/\/note.systw.net\/note\/?p=451"},"modified":"2023-11-02T20:00:53","modified_gmt":"2023-11-02T12:00:53","slug":"data-warehouse-and-olap","status":"publish","type":"post","link":"https:\/\/systw.net\/note\/archives\/451","title":{"rendered":"Data Warehouse and OLAP"},"content":{"rendered":"\n

DW(data warehouse,\u8cc7\u6599\u5009\u5132)<\/strong>
\u76ee\u5730:\u5c07\u4e0d\u540c\u4f86\u6e90\u8cc7\u6599\u6574\u5408\u8d77\u4f86,\u627e\u51fa\u5171\u901a\u6027\u4ee5\u5354\u52a9\u505a\u6c7a\u7b56
DW\u662f\u4e00\u7a2e\u8cc7\u6599\u5eab\uff0c\u4e14\u7b26\u5408\u4ee5\u4e0b\u56db\u500b\u7279\u6027
1.Subject-Oriented(\u4e3b\u984c\u5c0e\u5411)<\/strong>:\u4f8b\u5982\u7db2\u8def\u6d41\u91cf\u3001\u5546\u54c1\u8cc7\u8a0a
2.Integrated(\u6574\u5408\u6027)<\/strong>\uff1a\u7d50\u5408\u4e0d\u540c\u4f86\u6e90\u7684\u8cc7\u6599\uff0c\u4f8b\u5982\u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\u3001\u6587\u5b57\u6a94
3.Non-Volatile(\u975e\u63ee\u767c\u6027)<\/strong>\uff1a\u820a\u8cc7\u6599\u4e0d\u6703\u88ab\u53d6\u4ee3\uff0c\u53ef\u5206\u6790\u6b77\u53f2\u8cc7\u6599
4.Time-Variant(\u6642\u9593\u8b8a\u7570\u6027)<\/strong>\uff1a\u65b0\u8cc7\u6599\u6703\u4e0d\u65b7\u88ab\u8f09\u5165\uff0c\u56e0\u6b64\u5177\u6709\u6642\u9593\u7684\u5c6c\u6027
(by Inmon,2005)<\/p>\n\n\n\n

ps:
DW\u548cODS(operation database systems)\u5dee\u5225
DW\u4e3b\u8981\u4f7f\u7528OLAP,\u7528\u65bc\u5206\u6790
ODS\u4e3b\u8981\u4f7f\u7528OLTP,\u7528\u65bc\u65e5\u5e38\u4f5c\u696d\u8a18\u9304<\/p>\n\n\n\n

DW schema<\/strong>
\u5e38\u898b\u7684\u6709\u4ee5\u4e0b
star-schema<\/strong>:\u6700\u5e38\u898b\u7684model
\u3000\u4e00\u500bfact table,\u4e26\u9023\u63a5\u591a\u500bdimension table
snowflake schema<\/strong>:
\u3000\u985e\u4f3cstar-schema,\u4f46dimension table\u5167\u53c8\u9023\u63a5dimension table
fact constellation schema\/galaxy schema<\/strong>:\u53ef\u8868\u9054\u8907\u96dc\u7684\u95dc\u4fc2
\u3000\u591a\u500bfact table,\u4ee5\u53ca\u9023\u63a5\u591a\u500bdimension table<\/p>\n\n\n\n

DW Models<\/strong>
\u5e38\u898b\u7684\u4e3b\u8981\u6709
enterprise warehouse
data mart
virtual warehouse<\/p>\n\n\n\n

Metadata Repository<\/strong>
\u7528\u4f86\u5b9a\u7fa9DW\u88e1\u7684\u5404\u7269\u4ef6\u7684\u8cc7\u6599,\u4e3b\u8981\u5132\u5b58\u4ee5\u4e0b\u8cc7\u8a0a
Description of the structure of the data warehouse
Operational meta-data
The algorithms used for summarization
The mapping from operational environment to the data warehouse
Data related to system performance
Business data<\/p>\n\n\n\n

………………………………………………………………………………………………<\/p>\n\n\n\n

Data Cube(\u8cc7\u6599\u65b9\u584a)<\/strong>
1.OLAP\u4e2d\u6700\u57fa\u672c\u7684\u5efa\u69cb\u55ae\u5143
2.\u63d0\u4f9b\u5feb\u901f\u56de\u61c9\u67e5\u8a62\u8cc7\u6599\u7684\u6a5f\u5236
3.DW\u5b50\u96c6\u5408\u6240\u5efa\u7acb\u7684\u8cc7\u6599\u96c6\u5408\uff0c\u7531Dimension\u8207Measure\u6240\u5b9a\u7fa9\u7684Multi-dimensional Structure\uff0c\u6b64\u67b6\u69cb\u53ef\u63d0\u4f9b\u4f7f\u7528\u8005\u5feb\u901f\u800c\u8907\u96dc\u7684\u67e5\u8a62<\/p>\n\n\n\n

Data Cube\u662f\u7531\u8cc7\u6599\u5009\u5132\u5b50\u96c6\u5408\u6240\u5efa\u7acb\u7684\u8cc7\u6599\u96c6\u5408\uff0c\u4e3b\u8981\u7531Dimension(\u7dad\u5ea6)\u8207Measure(\u91cf\u503c)\u6240\u5b9a\u7fa9\u7684Multi-dimensional Structure(\u591a\u7dad\u5ea6\u7d50\u69cb)\uff0cDimension\u6982\u5ff5\u5c31\u662f\u5206\u6790\u6642\u7684\u89d2\u5ea6\uff0c\u4f8b\u5982\u6642\u9593\u3001\u4f86\u6e90\u7b49\uff0c\u7dad\u5ea6\u4e00\u822c\u6703\u9084\u5728\u5b9a\u968e\u5c64\uff0c\u4f8b\u5982\u6642\u9593\u53ef\u5206\u70ba\u5e74\u3001\u6708\u3001\u65e5\uff0c\u800cMeasure\u5247\u611f\u8208\u8da3\u7684\u8cc7\u6599\uff0c\u4f8b\u5982\u6d41\u91cf\u3001\u5c01\u5305\u6578\u7b49\u7d71\u8a08\u6578\u64da\u3002<\/p>\n\n\n\n

ps:
A Star-Net Query Model
\u89e3\u6c7a\u4ee5cube\u53ea\u80fd\u8868\u793a3\u500b\u7dad\u5ea6\u4e4b\u554f\u984c
star-net\u53ef\u4ee5\u8868\u793a\u5b8c\u6574\u7dad\u5ea6<\/p>\n\n\n\n

Measures of Data Cube\/aggregation function<\/strong>
\u6709\u4ee5\u4e0b\u4e09\u7a2e\u985e\u578b
distributive(\u53ef\u5206\u6563\u578b) ex:sum,max,min,count
algebraic(\u4ee3\u6578\u578b) ex:avg,std
holistic(\u6574\u9ad4\u8a08\u7b97\u578b)\u4e0d\u5177\u5099\u53ef\u5206\u6563\u578b\u548c\u4ee3\u6578\u578b\u7684\u7279\u6027 ex:rank,median,mode<\/p>\n\n\n\n


data cube\u7dad\u5ea6\u7684\u6578\u91cf<\/strong>
Li=\u7b2ci\u500b\u7dad\u5ea6\u7684\u968e\u5c64\u6578
n=\u7dad\u5ea6\u6578
total numbe rof cuboids=(L1+1)(L2+1)..(Ln+1)<\/p>\n\n\n\n

………………………………………………………………………………………………………………………<\/p>\n\n\n\n


OLAP(On-line Analytical Processing,\u7dda\u4e0a\u5206\u6790\u8655\u7406)<\/strong>
\u7528\u9014:\u5354\u52a9\u5206\u6790\u8005\u7531\u591a\u500b\u7dad\u5ea6\u89c0\u5bdf\u8b8a\u5316<\/p>\n\n\n\n

DATA CUBE\u900f\u904eOLAP\u9032\u884c\u64cd\u4f5c\uff0cOLAP\u662f\u4e00\u7a2e\u5c55\u793amultidimensional data\u7684\u6280\u8853\uff0c\u53ef\u8b93\u4f7f\u7528\u8005\u66f4\u65b9\u4fbf\u7684\u7528\u4e0d\u540c\u7684\u9762\u5411\u6aa2\u8996\u8cc7\u6599\uff0c\u4ee5\u5206\u6790\u8a18\u9304\u8cc7\u8a0a\u5feb\u901f\u5236\u5b9a\u7b56\u7565\uff0c\u5e38\u904b\u7528\u5728DW\u524d\u7aef\u4ecb\u9762\u4e4b\u5de5\u5177\uff0c\u8b93\u53d6\u5f97\u3001\u67e5\u8a62\u3001\u5206\u6790\u8cc7\u6599\u6642\u64c1\u6709\u6700\u5927\u7684\u5f48\u6027\u3002
ps:
\u6b64\u4e00\u6982\u5ff5\u6700\u65e9\u7531\u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\u4e4b\u7236E.F.Codd\u65bc1993\u5e74\u63d0\u51fa\uff0c\u540c\u6642\u4ed6\u4e5f\u63d0\u51fa\u4e86\u591a\u905412\u9805\u7684OLAP\u6e96\u5247<\/p>\n\n\n\n

\u6d41\u7a0b:<\/strong>
1.\u5148\u5efa\u7f6e\u4e00\u500b\u6709\u76f8\u95dc\u6578\u64da\u5206\u6790\u7684Data Cube\uff0c\u63d0\u4f9b\u5408\u9069\u7684Dimension\u8207\u968e\u5c64
2.\u4f7f\u7528OLAP\u591a\u7dad\u5ea6\u5206\u6790\u64cd\u4f5c,\u8b93\u4e0d\u540c\u9700\u6c42\u7684\u4eba\u5c0dDATA CUBE\u9032\u884c\u591a\u7dad\u5ea6\u5206\u6790\u64cd\u4f5c<\/p>\n\n\n\n

ps:
OLAP\u8207Data Mining\u5dee\u5225
OLAP
\u5e6b\u52a9\u5c08\u696d\u7d93\u7406\u4eba\u9a57\u8b49\u5047\u8a2d\u7684\u5de5\u5177\uff0c\u8b93\u4f7f\u7528\u8005\u91dd\u5c0d\u5546\u696d\u554f\u984c\u9032\u884c\u8ffd\u8e64\u8207\u63a2\u8a0e\uff0c\u900f\u904eOLAP\u5de5\u5177\u5c07\u5206\u6790\u7684\u7d50\u679c\u5448\u73fe\u5728\u4f7f\u7528\u8005\u9762\u524d\uff0c\u4ee5\u5354\u52a9\u505a\u51fa\u66f4\u5408\u9069\u7684\u6c7a\u7b56\u3002
Data Mining
\u4e3b\u8981\u5206\u6790\u8cc7\u6599\u4e2d\u898f\u5f8b\u578b\u614b\uff0c\u4ea6\u5373\u627e\u51fa\u8cc7\u6599\u4e2d\u7684Hidden Patterns\uff0c\u4e26\u63d0\u51fa\u53ef\u80fd\u6027\u7684\u5047\u8a2d\u3002<\/p>\n\n\n\n

……<\/p>\n\n\n\n

OLAP\u7dad\u5ea6\u5206\u6790\u64cd\u4f5c\u57fa\u672c\u6a21\u5f0f<\/strong>
\u5982\u4e0b
1.Slice(\u5207\u7247)
2.Dice(\u5207\u584a)
3.Drill-Down(\u5411\u4e0b\u64f7\u53d6)
4.Roll-Up(\u5411\u4e0a\u64f7\u53d6)
5.Pivot(\u65cb\u8f49\u900f\u8996)
6.Drill Across(\u6a6b\u5411\u947d\u53d6)
7.Drill Through(\u7a7f\u900f\u947d\u53d6),\u985e\u4f3cdrill-down,\u4f46\u662f\u5f80\u4e0b\u770b\u7684\u8cc7\u6599\u662f\u5176\u4ed6cube\u7684<\/p>\n\n\n\n

olap in the multidimensional data model<\/strong>
\u5047\u8a2d\u8a72data model\u67093\u500b\u7dad\u5ea6,\u5206\u5225\u70ba\u9805\u76ee,\u5730\u9ede,\u6642\u9593
\u800c\u9805\u76ee\u7684\u968e\u5c64\u53ef\u8a2d\u6210product,category,industry
\u5730\u9ede\u7684\u968e\u5c64\u53ef\u8a2d\u6210street,city,country
\u6642\u9593\u7684\u968e\u5c64\u53ef\u8a2d\u6210day,month,year<\/p>\n\n\n\n

…<\/p>\n\n\n\n

\u5047\u8a2d\u76ee\u524ddata model\u53ef\u770b\u5230\u7684\u8cc7\u8a0a\u5982\u4e0b
\u9805\u76ee:category
\u5730\u9ede:city
\u6642\u9593:month<\/p>\n\n\n\n

drill-down<\/strong>
\u53d6\u5f97\u66f4\u8a73\u7d30\u7684\u6642\u9593\u8cc7\u8a0amonth-(drill-down)->day
\u56e0\u6b64\u53ef\u5373\u53ef\u770b\u5230\u8cc7\u8a0a\u589e\u591a\u5982\u4e0b
\u9805\u76ee:category
\u5730\u9ede:city
\u6642\u9593:day
ps:\u82e5\u66f4\u8a73\u7d30\u7684\u8cc7\u6599\u6c92\u53e6\u5916\u5132\u5b58,\u5247drill-down\u4e5f\u770b\u4e0d\u5230
drill-up<\/strong>
\u53d6\u5f97\u5927\u6982\u7684\u5730\u9ede\u8cc7\u8a0acity-(drill-up)->country
\u56e0\u6b64\u53ef\u5373\u53ef\u770b\u5230\u8cc7\u8a0a\u8b8a\u7cbe\u7c21\u5982\u4e0b
\u9805\u76ee:category
\u5730\u9ede:country
\u6642\u9593:month
Slice<\/strong>
\u53d6\u5f97\u90e8\u4efd\u7684\u9805\u76ee\u8cc7\u8a0a,\u6982\u5ff5\u985e\u4f3csql\u7684where
category-(slice)->category=cat1
\u56e0\u6b64\u5373\u53ef\u770b\u5230\u8cc7\u8a0a\u8b8a\u5c11\u5982\u4e0b
\u9805\u76ee:category for cat1 ,\u5728category\u53ea\u5217\u51facat1\u7684\u90e8\u4efd
\u5730\u9ede:country
\u6642\u9593:month
Dice<\/strong>
\u53d6\u5f97\u90e8\u4efd\u7684\u9805\u76ee\u8cc7\u8a0a,\u6982\u5ff5\u985e\u4f3csql\u7684where
category-(dice)->category=cat1 and country=tw and month=1
\u56e0\u6b64\u5373\u53ef\u770b\u5230\u8cc7\u8a0a\u5728\u5404\u7dad\u5ea6\u8b8a\u5c11\u5982\u4e0b
\u9805\u76ee:category for cat1 ,\u5728category\u53ea\u5217\u51facat1\u7684\u90e8\u4efd
\u5730\u9ede:country for tw
\u6642\u9593:month for 1
Pivot<\/strong>
\u5c07slice\u8cc7\u8a0a\u4e4b\u6b04\u5217\u505a\u66ff\u63db<\/p>\n\n\n\n


………<\/p>\n\n\n\n


OLAP\u4f9d\u8cc7\u6599\u5132\u5b58\u65b9\u5f0f\u4e0d\u540c\u5206\u985e<\/strong>
\u53ef\u5728\u5206\u70ba\u4ee5\u4e0b
ROLAP(\u95dc\u806f\u5f0f\u7dda\u4e0a\u5206\u6790)<\/strong>
\u3000\u57fa\u65bc\u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\u4f86\u5be6\u73feOLAP\uff0c\u4ee5\u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\u70ba\u6838\u5fc3\uff0c\u4e26\u85c9\u7531\u95dc\u806f\u5f0f\u8cc7\u6599\u7d50\u69cb\u9032\u884c\u591a\u7dad\u5ea6\u8cc7\u6599\u7684\u5c55\u73fe\u8207\u5132\u5b58
MOLAP(\u591a\u7dad\u5ea6\u7dda\u4e0a\u5206\u6790)<\/strong>
\u3000\u57fa\u65bc\u591a\u7dad\u5ea6\u8cc7\u6599\u7d44\u7e54\u4f86\u5be6\u73feOLAP\uff0c\u4ee5\u591a\u7dad\u8cc7\u6599\u7d44\u7e54\u65b9\u5f0f\u70ba\u6838\u5fc3\uff0c\u4e26\u85c9\u7531\u591a\u7dad\u5ea6\u9663\u5217\u4f86\u5132\u5b58\u8cc7\u6599
HOLAP(\u6df7\u5408\u578b\u7dda\u4e0a)<\/strong>
\u3000\u57fa\u65bc\u6df7\u5408\u8cc7\u6599\u7d44\u7e54\u4f86\u5be6\u73feOLAP\uff0c\u6df7\u5408\u4e0aROLAP\u548cMOLAP<\/p>\n","protected":false},"excerpt":{"rendered":"

DW(data warehouse,\u8cc7\u6599\u5009\u5132)\u76ee\u5730:\u5c07\u4e0d\u540c\u4f86 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"","fifu_image_alt":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13],"tags":[],"class_list":["post-451","post","type-post","status-publish","format-standard","hentry","category-dataanalysis"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/451","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/comments?post=451"}],"version-history":[{"count":0,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/posts\/451\/revisions"}],"wp:attachment":[{"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/media?parent=451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/categories?post=451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/systw.net\/note\/wp-json\/wp\/v2\/tags?post=451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}